From: <pau...@us...>
<pau...@us...> - 2008-07-14 07:51:50
|
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 |
From: <pau...@us...>
<pau...@us...> - 2008-08-19 11:24:53
|
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 |