[geeklog-devel] Draft of schema for GL2

[Vincent Furia]

Comments in line, hopefully this method of commenting will work...

On Wed, 15 Dec 2004 16:38:22 -0600, Tony Bibbs <tony at tonybibbs.com> wrote:
> CREATE TABLE gl2_list_of_values (
>   lov_id int unsigned NOT NULL auto_increment,
>   group_name varchar(30) NOT NULL,
>   short_name varchar(40) NOT NULL,
>   description varchar(128),
>   enabled tinyint NOT NULL DEFAULT 1,
>   sort_order mediumint NOT NULL DEFAULT '0',
>   PRIMARY KEY (lov_id),
>   INDEX (group_name),
>   INDEX (short_name),
>   INDEX (enabled)
> ) TYPE=INNODB;
I just don't like the name, but I can't come up with anything better
(how is that for useless input?).

> CREATE TABLE gl2_event (
Another name issue, this one with a suggestion and a valid reason:
What about "action" instead of "event" the problem here is a language
domain collision between our idea of gl2 events vs. calendar events. 
"action" is a possible replacement, so is "act" or any synonym that
makes sense.  I'm open to suggestions, but I think "event" needs to
change.  (And I know, I'm the one who called them "events" to begin
with).

>   event_id int unsigned NOT NULL auto_increment,
>   event_name varchar(40) NOT NULL,
NEW COLUMN: event_plugin_id int unsigned NOT NULL DESC force events to
be associated with a specific plugin

>   description varchar (255) NOT NULL,
>   PRIMARY KEY(event_id),
>   INDEX(event_name)
> ) TYPE=INNODB;
> 
> CREATE TABLE gl2_plugin (
>   plugin_id int unsigned NOT NULL auto_increment,
>   plugin_name varchar(128) NOT NULL,
>   version varchar(20) NOT NULL,
>   homepage varchar(128) NOT NULL,
>   enabled tinyint unsigned NOT NULL DEFAULT 1,
>   PRIMARY KEY(plugin_id),
>   INDEX(plugin_name),
>   INDEX(enabled)
> ) TYPE=INNODB;
We might want to require that version be a float (or int?) so that we
can do version dependency checking with some reasonable results. 
(i.e. check for version of forum >= 2.3).

> 
> CREATE TABLE gl2_eventListener (
>   event_id int unsigned NOT NULL,
>   plugin_id int unsigned NOT NULL,
>   PRIMARY KEY(event_id, plugin_id),
>   FOREIGN KEY (event_id) REFERENCES gl2_event(event_id),
>   FOREIGN KEY (plugin_id) REFERENCES gl2_plugin(plugin_id)
> ) TYPE=INNODB;
> 
> CREATE TABLE gl2_user (
>   user_id int unsigned NOT NULL auto_increment,
>   registration_date int unsigned NOT NULL,
>   profile_views mediumint unsigned NOT NULL DEFAULT 0,
we should consider keeping counts in a separate table, hopefully this
will prevent the locking issues previously experienced with article
view counts.  We could recommend that plugins use the same table as
needed.

>   items_per_page tinyint unsigned NOT NULL DEFAULT 10,
>   language_id int unsigned NOT NULL,
>   user_name varchar(25) NOT NULL,
>   password varchar(35) NOT NULL,
>   enabled tinyint unsigned NOT NULL,
>   email VARCHAR(128) NOT NULL,
>   comment_mode_id int unsigned NOT NULL,
>   comment_order_id int unsigned NOT NULL,
>   comment_limit tinyint unsigned NOT NULL default '0',
>   cookie_timeout int unsigned NOT NULL,
>   locale varchar(3),
>   date_format_id int unsigned NOT NULL,
>   blocks_enabled tinyint unsigned DEFAULT 1,
>   PRIMARY KEY(user_id),
>   INDEX (user_name),
>   INDEX (enabled),
>   INDEX (email),
>   FOREIGN KEY (comment_mode_id) REFERENCES gl2_list_of_values(lov_id),
>   FOREIGN KEY (comment_order_id) REFERENCES gl2_list_of_values(lov_id),
>   FOREIGN KEY (date_format_id) REFERENCES gl2_list_of_values(lov_id)
> ) TYPE=INNODB;
> 
> CREATE TABLE gl2_user_supp (
>   user_id int unsigned NOT NULL,
>   signature varchar(128),
>   biography text,
>   first_name varchar(30),
>   last_name varchar(30),
>   homepage varchar(50),
>   PRIMARY KEY(user_id),
>   FOREIGN KEY(user_id) REFERENCES gl2_user(user_id),
> ) TYPE=INNODB;
I think this table should be combined with the gl2_user.  I like the
idea of having a plugin that will handle supplemental user
information.

> 
> CREATE TABLE gl2_group (
>   group_id int unsigned NOT NULL auto_increment,
>   logical_name varchar(30) NOT NULL,
>   display_name varchar(50) NOT NULL,
>   description varchar(255) NOT NULL,
> ) TYPE=INNODB;
What is the difference between a "logical name" and a "display name"? 
Is differentiating them necessary?

> 
> CREATE TABLE gl2_privilege (
>   code varchar(30) NOT NULL,
>   description varchar(255) NOT NULL,
>   PRIMARY KEY(code)
> ) TYPE=INNODB;
> 
> CREATE TABLE gl2_privilege_access (
>   code varchar(30) NOT NULL,
>   user_id int unsigned,
>   group_id int unsigned,
>   INDEX(code),
>   FOREIGN KEY(user_id) REFERENCES gl2_user(user_id),
>   FOREIGN KEY(group_id) REFERENCES gl2_group(group_id)
> ) TYPE=INNODB;
> 
> CREATE TABLE gl2_group_assignment (
>   main_group_id int unsigned NOT NULL,
>   assigned_user_id int unsigned,
>   assigned_group_id int unsigned,
>   FOREIGN KEY(main_group_id) REFERENCES gl2_group(group_id),
>   FOREIGN KEY(assigned_user_id) REFERENCES gl2_user(user_id),
>   FOREIGN KEY(assigned_group_id) REFERENCES gl2_group(group_id)
> ) TYPE=INNODB;
> 
> CREATE TABLE gl2_ban (
>   ban_id int unsigned NOT NULL auto_increment,
>   user_id int unsigned,
>   ip_address varchar(11),
>   start_date int unsigned NOT NULL,
>   end_date int unsigned NOT NULL,
>   reason_id int unsigned NOT NULL,
>   PRIMARY KEY(ban_id),
>   FOREIGN KEY(user_id) REFERENCES gl2_user(user_id),
>   FOREIGN KEY(reason_id) REFERENCES gl2_list_of_values(lov_id)
> ) TYPE=INNODB;
I think ban should be a plugin...

> 
> CREATE TABLE gl2_catalog (
>   catalog_id mediumint unsigned NOT NULL auto_increment,
>   catalog_name varchar(50) NOT NULL
>   date_created int unsigned NOT NULL
> ) TYPE=INNODB;
A catalog is just a set of categories correct?  Why not keep that info
in the gl2_category table?  (i.e. what's the justification for a
separate table).  Alternatively should it just go in the
list_of_values table?  Why keep the date created?

> 
> CREATE TABLE gl2_category (
>   category_id mediumint unsigned NOT NULL,
>   catalog_id mediumint unsigned NOT NULL,
>   category_name varchar NOT NULL,
>   parent_category_id mediumint unsigned NOT NULL,
>   image_url VARCHAR(128) DEFAULT 'NULL',
>   sort_num tinyint unsigned DEFAULT NULL,
>   enabled tinyint unsigned NOT NULL DEFAULT 1,
>   owner_id int unsigned NOT NULL,
>   group_id mediumint unsigned NOT NULL,
>   owner_permissions tinyint unsigned NOT NULL,
>   anon_permissions tinyint unsigned NOT NULL,
>   member_permissions tinyint NOT NULL,
>   group_permissions tinyint NOT NULL,
>   FOREIGN KEY (catalog_id) REFERENCES gl2_catalog(catalog_id),
>   INDEX (enabled)
> ) TYPE=INNODB;
Should we use the modified preorder traversal numbering to store the
hierarchical structure of the categories within a catalog (i.e. like
comments in 1.3.10)?  All the owner/group id and permissions stuff
should be removed...just use the acl table.  We don't want to deal
with two different systems for determining permissions.  In fact, why
not make categories "items"?  (then categories can be in categories,
won't that be cool?)

> 
> CREATE TABLE gl2_item (
>   item_id int unsigned NOT NULL auto_increment,
>   type_id int unsigned NOT NULL,
>   user_id int unsigned NOT NULL,
>   category_id INT unsigned NOT NULL,
>   date_created int unsigned NOT NULL,
>   num_views mediumint unsigned NOT NULL DEFAULT 0,
>   state_id int unsigned NOT NULL,
>   num_emails mediumint unsigned NOT NULL DEFAULT 0,
I think the num_views and num_emails should be moved to a separate
table (see gl2_user table above).

>   num_ratings mediumint unsigned NOT NULL DEFAULT 0,
>   rating_sum int unsigned NOT NULL DEFAULT 0,
I think ratings should be handled by a plugin.

>   expiration_date int unsigned DEFAULT NULL,
>   parent_item_id int unsigned DEFAULT NULL,
NEW COLUMN: enabled tinyint DEFAULT 1 DESC have the ability to "switch
off" items.

>   PRIMARY KEY(item_id),
>   FOREIGN KEY(type_id) REFERENCES gl2_list_of_values(lov_id),
>   FOREIGN KEY(user_id) REFERENCES gl2_user(user_id),
>   FOREIGN KEY(category_id) REFERENCES gl2_catgory(category_id),
>   FOREIGN KEY(state_id) REFERENCES gl2_item_type_state(state_id),
>   FOREIGN KEY(parent_item_id) REFERENCES gl2_item(item_id)
> ) TYPE=INNODB;
Do we want an association table between items and categories so items
can be in multiple categories?

> 
> CREATE TABLE gl2_item_acl (
>   acl_id int unsigned NOT NULL auto_increment,
>   item_id int unsigned NOT NULL,
>   user_id int unsigned,
>   group_id int unsigned,
>   rights smallint unsigned NOT NULL,
>   PRIMARY KEY(acl_id),
>   FOREIGN KEY(item_id) REFERENCES gl2_item(item_id),
>   FOREIGN KEY(user_id) REFERENCES gl2_user(user_id),
>   FOREIGN KEY(group_id) REFERENCES gl2_group(group_id)
> ) TYPE=INNODB;
Looks good

> 
> CREATE TABLE gl2_comment (
>   comment_id int unsigned NOT NULL auto_increment,
>   item_id int unsigned NOT NULL,
>   subject varchar(50) NOT NULL,
>   comment_text text NOT NULL,
>   parent_id int unsigned,
>   ip_address varchar(11) NOT NULL
>   PRIMARY KEY(comment_id),
>   FOREIGN KEY(item_id) REFERENCES gl2_item(item_id),
>   FOREIGN KEY(parent_id) REFERENCES gl2_comment(comment_id)
> ) TYPE=INNODB;
Should comments be a plugin?  That way if you want a "forum like"
comment vs traditional system you can just switch out the plugin?  If
you really (REALLY) want this do you want to keep the hierarchical
comment structure we added to 1.3.10 (modified preorder traversal)

> 
> CREATE TABLE gl2_item_type_state (
>   state_id int unsigned NOT NULL auto_increment,
>   type_id int unsigned NOT NULL,
>   state_name varchar(30) NOT NULL,
>   description varchar(255),
>   PRIMARY KEY(state_id),
>   FOREIGN KEY(type_id) REFERENCES gl2_list_of_values(lov_id)
> ) TYPE=INNODB;
If these are going to be different for different items, shouldn't it
be kept by the individual plugin managing the item?