[Everydevel-opers] More database stuff, list nodetype and random thoughts

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

Hello,

I've mentioned before how I want to look at changing the schema.  In 
particular, I want to normalise it a bit and, for example, put the 
permissions and dynamic permissions into their own table.  The node 
table is definitely not in 3rd normal form, but I don't think it's in 
1st normal form either given that the permissions columns are 'repeating 
groups', IMHO.  I also don't think it's in 2nd NF because most 
permissions values are repeated, this means that they are not dependent 
on the primary key, but on something else.  In fact, they're dependent 
on the nodetype and hence on a relation.  Pushing them out into their 
own table(s) means that we can also better ensure data consistency.

Speaking of data consistency....

In my last email I mentioned trying to get SQL totally out of nodeballs, 
or at least out of ecore... (if nodeball authors want to tie their 
nodeballs to one DB, it's none of my business).

I realised that I couldn't do this before writing some tests that test 
against a live DB.  All our DB tests currently use mocking heavily, 
which is OK as far as it goes, but when push comes to shove you need to 
know that the database dependent sql you're sending to the DB actually 
works.

As, I've been doing this, I've come up against that data consistency 
issue again.  For example, each row on the group table points to two 
rows in the node table.  This means that they're foreign keys, yet no 
foreign key constraint is enforced.  As a result you can get hanging 
group table entries....I have two currently on one site, I'm running.  
On deletion of the relevant nodes, those rows in the group table should 
have been deleted.  I don't know why they weren't, but they weren't.  
"ON DELETE CASCADE" would not have allowed these hanging foreign keys to 
exist. There are more examples of this sort of thing.

I think when Everything is installed on Pg, these constraints should be 
enforced.  As for MySQL, I think we should install as InnoDB and if 
someone wants to convert to MyISAM let them do it, but Everydevel should 
aim for some transactional integrity.

So on to nodetypes...

One table that is clearly not normalised is the setting table. Settings 
can include literal values that may either be integers or strings or 
they may a reference to an entry in the node table (i.e. a foreign 
key).  That's three datatypes which are two too many for an easily 
workable solution.

However, recently, I've tried to think of a way of implementing 
blog-style comment threads.  The obvious way to do this would be to make 
each comment a 'node'.  However, it didn't feel right and the reason was 
because the philosophy behind a node is that the author has some 
ownership over it and can edit it.  Blog-style comments don't have this 
attribute.  I then looked at one of the comment modules on Drupal did.

The solution seems to be to have a 'comment thread' nodetype. Any node, 
can have a 'comment thread' attached to it.  We don't know how long a 
comment thread is so it's an aggregation of a list of data.  Actually, 
my comment thread is reasonably complex as I have three tables. One is 
the comment itself, one is the poster who is identified by his/her email 
and the third is a blacklist of IP addresses from which no posting is 
allowed.

Which brings me back to settings.  Settings are lists of data values, so 
they are a list type or a subclass of list.  Don't you think?

Paul