Data Model for Drupal 4.7 core

Submitted by Frederic Marand on
Grokking Drupal

Wouldn't you like a diagram of Drupal 4.7 with complete data types and referential integrity constraints ? Here's one.

I've long found it annoying to have Drupal use the non-specific data types required by MySQL, instead of the strong typing allowed in ANSI SQL, because it hinders comprehension of the data model by new developers.

Drupal 4.7 core diagramSo the next step was to redo the Drupal database schema with a DBMS that understood ANSI SQL domains and referential integrity. Considering my work on Interbase / Firebird, the logical choice was Firebird. So here is the diagram for 4.7 core with Primary Keys (PK) and Foreign Keys (FK) relationships, done with the free-as-beer IBUtils.

I've attempted to group tables in logical packages to ease understanding of the model, hence the clashing colors.

Of course, what this means is that there also exist a database.firebird.inc somewhere. But I don't feel it's ready for prime time yet, so I'm not publishing it at the moment. On the other hand, if you want it, just ask me.

A word of warning, though: you'll notice the tnode_access_realm table. This is an Interbase / Firebird artefact, to work around the key size limitation in IB/FB. It might be a good idea to backport it into the standard schemas, though, because it tends to make node access more efficient anyway.

This seems to be missing the term_node table which links terms to nodes; actually it's missing a couple of taxonomy tables but I think term_node is a critical one. At least, it's one I use a lot.

Silly me, you're right: I thought I had done the full schema, but not yet. It's still missing several tables: I'll try to add them whenever I find moment. Unless you want to do it yourself ? I could post the .SQL file generating the Firebird implementation somewhere on drupal.org, and the .MDL for the data model. But where ?

Great work I was just writing an entry about this very idea - so I nicked your title, but credited you ;-). I have been trying to import the original MySQL and PostgreSQL modules into MagicDraw UML but the FKs are missing, so it's pretty useless. How did you get the relations, did you redo them manually by linking the 'uid'-type fields with their referrents? Do you have a UML model of the diagram? Find the discussion on my blog. Thanks, K.

What I did was :

  • adapt the MySQL code for 4.7 to Firebird syntax,
  • create all the SQL domains on the fly based on what I now know they represent in order to obtain abstract data types,
  • create all the FKs based on the actual joins performed in Drupal core and the now-defined domains
  • all of this using IBExpert, which I find most convenient for this type of work
  • the open the database in IBUtils which I'm accustomed to using for Firebird data models: it's fast and easy to use. The color coding allows meta information like a notion of packages to be evidenced.

So, as you see, it's really a result of the Firebird port for Drupal.

One aspect I consider very important is the use of the SQL domains: it's a very important feature for me in Interbase/Firebird as opposed to MySQL because it allows, say, a UID to be of DUID type, not just INT(11). And with this, you'll only link it to other DUID fields, not to any other INT(11) because they happen to be of the same size. It's a especially relevant feature when trying to understand the core data model: there are many IDs, and lots of them share the same name: aid and sid spring to mind, but I think there are others. Using domaines differentiates them cleanly while keeping the Drupal naming convention of "one letter + 'id'" for such keys.

There's no UML version because my UML diagramming tool (ModelMaker) don't include a DB modeling feature and, AFAIK, E/R modeling has not been standardized in UML.

I see that your model seems to use various stereotypes for this: "table", "PK", "index", and you're using UML method representation for indexes, which might be cleaner than using a straight "index" stereotype on the considered property (as I do on the UML models I've built for other parts of drupal) because it allows for more exact index definitions.

I know there's another diagram a bit like yours somewhere on drupal.org, but it seems to have been there but have been (re)moved recently.

There's more info in the drupal CVS "docs/developer" section.

Jerram Clulow (not verified)

Wed, 2006-10-18 05:20

Permalink

Hi,

I was wondering if you would be able to advise me what I need/should do to use Firebird as a backend to Drupal. Are we able to use your database.firebird.inc or can something like the ADODB database layer written in PHP work?

Thanks,
>> Jerram <<

Two things are needed for a 4.7 port:

  • complete the .inc file : as you can see, it is not complete, a few tables are missing from the diagram for core
  • write the DB-specific functions

IBM's latest article about how they ported Drupal to DB2 gives all the information needed for this type of port, I think. Check it out on Developer Works.

From the look of things there could be a few problems with a couple of reserved Firebase words that are used in Mysql, such as cache and type. Any idea of an easy way around that? Also do you know whether Drupal is case sensitive when querying the Database? Thanks, >> Jerram <<

I think this is partly what the database independence layer will help you achieve.

For instance, redefining db_prefix_tables allows you to intervene when the curly braces wrapping table names are processed, at which point you can take advantage of the fact that they've just been identified to rename them to non-conflicting names: instead of just using strtr as default code does, you could also match on the reserved words to replace them before removing the braces. There might be a better way, though: I'm no expert with this, and I suspect looking at the DB2 version put out by IBM will be enlightening.