Home » Database » DB_Table » Bug #5479

[2005-09-21 23:56 UTC] bhill at sims dot berkeley dot edu

Description:
------------
An extension of DB_Table that is an even greater abstraction:

All the user does is write an XML file (based on a defined DTD) that completely describes a schema. The DB_Table package uses the XML file to create, populate, etc the database and QuickForms. Any additional customizations can be done the same way they are done now, but I think that this would eliminate the majority of busywork when using DB_Table, remain completely database agnostic, and not require code editing.

Example (notice "ref" elements that are a one-to-many link) in test script.

I think that DB_Table is very close to this (at least, the closest I've found!)

Test script:
---------------
db.xml

<db prefix="wmap">
<table name="content" description="sighting of an item at a location/time">
<ref name="location" display="name" />
<ref name="item" display="name" />
<col name="html" type="varchar" length="255" description="content" />
<col name="date" type="datetime" />
<col name="author" type="varchar" length="255" />
</table>
<table name="item" description="mappable thing">
<col name="name" type="varchar" length="255" description="name of mappable thing" />
</table>
<table name="location" description="location of a mappable thing">
<col name="name" type="varchar" length="255" description="location and address" />
<col name="lat" type="real" length="30" visibility="false" />
<col name="lng" type="real" length="30" visibility="false" />
</table>
</db>

test.php

// connect using uri, verifies all tables once per session, default to safe creation of tables.
DB_Table_Rails::connect($dsn, "db.xml");

// Create and use
$content =& DB_Table_Rails::getTable('content');
$content->displayInsert();

[2005-09-24 22:00 UTC] bhill at sims dot berkeley dot edu

"ref" elements - a foreign key to the other table.

Like if you have tables "book", "user"
you could have

<table name(type id)="book">
<ref table(type idref)="user" name="author" displaycol="name_first"/>
<ref table(type idref)="user" name="owner" displaycol="name_first"/>
<col name="title" ...

Which would create foreign keys automagically. (Which would then create drop-downs in the QuickForm just as automagically? I think that would be the bigest win.)

I agree that DB_Table is per-table, but allowing the flexibility of defining multiple tables in a db.xml file allows you to define an entire program's schema (and prefix) in one file, and provides referential integritity in the XML file's IDREF tags. You could always choose to define just one table in an XML file.

> And, I would not add a new class like DB_Table_Rails

I agree.

> Is this what you like to get?
this is exactly what I was hoping for - a one-stop shop where you write a small XML file that defines an entire schema along with some display hints, and whammo, you have automagically generated cross-database compatible forms with foreign-key dropdowns, with no SQL writing. And it seems that DB_table is just inches away! (I had started writing a big chunk of code before discovering DB_Table and finding out it did everything I had, but better)

[2005-09-25 21:51 UTC] bhill at sims dot berkeley dot edu

> As MySQL doesn't know foreign keys I would not add support for foreign keys into DB_Table at the moment.

True, the database doesn't support referential integrity, but any system that has dropdowns is using a foreign key RDBMS design, yes? It just is done in the software layer.

[2005-09-26 18:07 UTC] post at mark-wiesemann dot de

Paul, you are right, a new class like DB_Table_Join would be even better. I'm currently unsure, how this class would be used in user's scripts, but I really like the idea.

"bhill" and Paul, if you have further comments or ideas on this, please post it here. Otherwise, I will get back to both of you, when I find the time for it.

[2006-09-21 16:46 UTC] benjaminhill at gmail dot com

After watching the dabble-db demo at http://dabbledb.com/explore/7minutedemo/ I went and looked up this feature again. Glad to hear it is moved to the new MDB2_Table feature request list!