[Everydevel-opers] Database issues

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

Hello all,

Anyone got any thoughts on this:

Writing those live database tests has been very good because it has got 
me to focus on what the database is actually doing and how we construct 
nodes.

What's also clear to me is that Everything is using default settings as 
substituting for NULL.  What this means is that we've in effect got a 
lot of nullable columns that don't really apply to all nodes.  Many of 
these should be split out into their own tables and "left joins" used so 
that when an entry does't exist we get NULL returned.

So, I think the following changes would be good:

* delete 'hits' and 'reputation'

   We don't used these two at all in ecore.  They seem redundant, I 
mean, does a container node really need a reputation?  Do we need to 
count the hits when an admin is tweaking an htmlpage node?

   These should be added as part of a module hence I will add these to 
the TODO file

* modified, loc_location and lockedby_user/locktime should be removed 
from the node table and reimplemented like this:

   CREATE TABLE modified (
   	 node_id int8,
          date timestamp,
          FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE
   	 CASCADE
          )

   CREATE TABLE location (
   node_id int4,
   loc_location int4
   FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE,
   FOREIGN KEY (loc_location) REFEFENCES node(node_id) ON DELETE
   RESTRICT
   )

   CREATE TABLE lock (
   node_id int4,
   lockedby_user int4,
   locktime timestamp,
   FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE,
   FOREIGN KEY (lockedby_user) REFERENCES node(node_id) ON DELETE RESTRICT,	
)

* group_usergroup

This is usually left at its default value '-1', therefore, IMHO should 
be shifted out to it own table

* author_user

This is one I'm confused about.  Does a node really need an author? 
What if there is none? Do we default to root? If yes, do we enforce that 
on the DB level or on perl logic level.

In other words if the author_user field is properly nullable it should 
be moved into its own table.  If not, then it can stay where it is.

At the moment, I'm leaving it where it is, only because deciding where 
it goes makes my brain hurt.

* dynamic permissions are hardly ever used (although they are extremely 
cool).  I think they should be deleted from the node table and 
reimplemented in their own table

* permissions

This has given me the greatest trouble. Each permission has three values 
(inherit, enable (overriding inherit), disable (overriding inherit)).

At the moment there are four permission fields author, group, other, 
guest. Each have four permissions read, write, execute and delete. In 
addition there is a 'create' permission for group, other and delete.

This create permission is obviously only relevant to nodetype nodes 
(i.e. such and such user has permission to create nodes of this type). 
Because of this, it should be implemented differently, because a node 
instance inheriting a 'create' permission makes no sense, imo.

At the moment permissions are stored as a string. 'i' meaning inherited, 
and then one of r,w,x,d or c for enabled and '-' for disabled.

I've been wondering whether there is a more compact way of implementing 
this.  Because each permission has three possible values, first I 
thought of some modulo ternary arithmetic could do it so that each 
permission could be a series of ternary digits.  2021, (decimal 61) 
would mean that a node could be read and executed, but not deleted, and 
would there for be the equivalent of rix-, under the current system.

So the permissions table would look something like this (excuse the 
pseudo-SQL):

    permissionclass char(5), #one of author, group, other, guest
    node_id int references node(node_id),
    permission int #the decimal number representing the ternary 
permissions
    PRIMARY KEY( node_id, permissionclass)

But this means implementing ternary logic and some ternary to decimal 
conversion tools.

Another way to do it would involve more joins (again please excuse the 
pseudo-SQL):

    permissionclass char(5), #one of author, group, other, guest
    permissiontype  char(1) # one of r, w, d, x
    permission tinyint # either NULL, 1 or 0
    node_id int references node(node_id),
    PRIMARY KEY( node_id, permissionclass, permissiontype)

This would, of course, require more joins.  The permssion field is a 
'trit'.  NULL means inherit, 0 'disable' and '1' enable.

So, we still need ternary logic, but don't need the ternary to decimal 
converson (yay!), but need more joins (boo!).

I have no idea how to proceed on this one.  Any feedback more than welcome.


Thanks

Paul