[Everydevel-opers] Database issues

[<pau...@us...> <pau...@us...>]

Hello all,

The changes I've recently checked in now ensure that Everydevel is xhtml 
compliant for all nodes with displaytype 'display'.

I've also checked in some code that allows people to download nodeballs 
from the location /repositories/nodeballs/<node_id>.  It's a bit slow 
and could deal with some caching after the first request. The 'Caching' 
I suggest is pretty simple, in that the temp file used persists until 
the nodeball itself is updated at which point we rebuild it.  On a 
slightly related point, it might be a good idea to export nodeballs with 
the ending tgz rather than nbz, as many archive browsing programs 
complain that nbz is not a file type they support.

Anyway, onto database issues.

One issue with everything is the way that nodeballs contain sql.  This 
sql is naturally database dependent and if I develop a nobeball in say 
sqlite and then want to import it into a pg database, I have trouble. 
The solution is a layer of abstraction so that the DB layer decides on 
the db schema and not the nodeball.  An advantage that flows from this 
is that we can then optimise the db schema without breaking the front-end.

The first step is to export enough data into the nodeball to allow the 
db tables to reconstructed.  In order to do this I plan to add a new XML 
tag to nodetype nodes when they are exported. I suggest calling it 
classdata. So it would be something like this:

<classdata type="attribute">
   <attribute type="name">attribute_name</attribute>
   <attribute type="type">int</attribute>
   <attribute type="length">8</attribute>
   <attribute type="nullable">no</attribute>
</classdata>
<classdata type="constraint">
   <constraint type="primary_key">attribute_id</constraint>
   <constraint type="foreign_key" 
references="other_table">other_id</constraint>
</classdata>
So, for example here we've got enough data to recreate a column in sql:
   attribute_name int8 NOT NULL
And then later on:

   alter table table_name add primary key(attribute_id);
   alter table table_name add constraint foreign key(other_id) 
references ....

and so on.

This allows us to export more data than we need.  For example, foreign 
key constraints, which at the option of the DB layer may be ignored or 
not depending on the schema and db.

To construct such fields, DBI provides the column_info method which 
returns way more than we need for this.  For sqlite, column_info isn't 
supported but we can get the same sort of data from the PRAGMA command.

One more thing on NOT NULL constraints.  Everything is currently 
initialised with almost all fields constrained with NOT NULL.  I can 
understand why this is in that it is really painful for the apache log 
to be littered with 'uninitialized value' warnings when it pulls a null 
value from the database.  However, I believe it is for the front-end to 
deal with this scenario (by converting nulls to '' or throwing them away 
or whatever) and not for the DB layer.  NULL is a valuable value.  I've 
already come across a situation where it would be better for the 
modified field to default to NULL, rather than 0000-00-00 or -infinity, 
to mean 'never modified'.

Besides that there is an important distinction between NULL and ''. One 
which means the value is not set and the other that it has been set to 
an empty string. The front-end may need to handle these to situations 
differently. So, I intend to remove all the NOT NULL constraints in the 
not too distant future.

Any thoughts?

Cheers

Paul