[geeklog-devel] Draft of schema for GL2

[Tony Bibbs]

New schema is attached.  I added the gl2_block table...this may have 
been a bit premature since we are still discussing it.  You can choose 
to ignore it pending the outcome of that discussion.

Here's what I did:
1) Any referenct to 'event' was changed to 'action'
2) I added a plugin_id to the action table, only outstanding issue is 
how are we representing kernel events?
3) Versions can't be floats if they have two decimal points can they?  I 
think the validation on this needs to happen via the save's validation 
logic and I think we need to standardize that all version numbers are in 
the format x.y.z
4) I didn't do anything with count fields...that's pending further 
discussion
5) The supplemental user stuff was added to the gl2_user table
6) gl2_ban table was removed.  I agree this can be doing via a plugin
7) I didn't see any datastructures related to preoder traversal stuff in 
categories.  I looked in the latest mysql_tables_and_data.php file on 
the comments table and didn't see a field that looked like what I was 
expecting.  What am I missing, Vinny?
8) Added enabled flag to gl2_item
9) removed the security related fields form the category stuff, then 
changed category_id to be item_id so we don't have to worry about 
category_id/item_id collisions on ACL table.
10) added gl2_item_category which is an association table between items 
and categories.

Please review the outstanding issues in my previous email and get back 
to me ASAP.  NOTE, this still won't import into MySQL yet.  I'll work on 
that tonight.

--Tony



Tony Bibbs wrote:

> Great feedback.  My comments below
>
> Vincent Furia wrote:
>
>> I just don't like the name, but I can't come up with anything better
>> (how is that for useless input?).
>>  
>>
> Lol, yeah, pretty useless.  I'm open to suggestions should one hit you.
>
>> 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).
>>  
>>
> gl2_action is fine by me.  However, just as we have namespace issues 
> in the code with class names we could have similar issues with table 
> names between plugins. I think we should try to address this with some 
> sort of standard, if possible.
>
>> NEW COLUMN: event_plugin_id int unsigned NOT NULL DESC force events to
>> be associated with a specific plugin
>>  
>>
> I thought the same thing.  Only question I have is what about 
> kernel-based actions?
>
>> 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).
>>  
>>
> Yeah, makes sense.
>
>> 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.
>>  
>>
> Where's the DBMS trigger feature when you need it?  Are the locking 
> issues still an issue with INNODB table types?  Does moving them to a 
> separate table really get rid of the problem?  Seem you'd still have a 
> problem with locking, though, not as much.
>
>> 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.
>>  
>>
> I'm OK with this.  I don't like all the different user tables in 1.3.x.
>
>> What is the difference between a "logical name" and a "display name"? 
>> Is differentiating them necessary?
>>  
>>
> Logical name would be what you use in security checks.  Something like 
> today's SEC_inGroup('story_admin').  story_admin would be the logical 
> name.  However when you show the name of the group, say, on a group 
> administration page you would see Story Admin.  By splitting the two 
> you could, in theory, change the name of the group without breaking 
> your code granted you keep the logical name the smae.
>
>> I think ban should be a plugin...
>>  
>>
> Hrm, never really thought of that.  Any implications against doing this?
>
>> 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?
>>  
>>
> The idea here is that plugins can quickly reuse these structures 
> should they have a need to categorize some amount of data.  Thus, the 
> links plugin would have it's own catalog different form the story 
> plugin's categories.  Putting the catalogs in the list of values makes 
> sense.
>
>> 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)?
>
> Hrm, haven't even looked at the .10 code.  If I hear you what you are 
> saying is we essentially cache the hierarchy instead of having 
> expensive recursive method calls to build it in real time.  I'd be 
> fine with that. I'll look at the .10 schema and make needed changes.
>
>> 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?)
>>  
>>
> Yeah, not sure how that snuck in there.
>
>> I think the num_views and num_emails should be moved to a separate
>> table (see gl2_user table above).
>>  
>>
> I agree we should handle counts in a consistent manner.  The locking 
> issues are all I'm interested in figuring out before we decide on a 
> direction.
>
>> I think ratings should be handled by a plugin.
>>  
>>
> Could, but I think this is a basic need for almost all plugins.  In 
> fact, this could even be expanded to users.  I could be convinced 
> otherwise.  We all want the GL2 kernel to be as lean and mean as 
> possible but I think we also want to ensure that anything in the 
> kernel is truly a resource that will be needed other plugins.
>
>> NEW COLUMN: enabled tinyint DEFAULT 1 DESC have the ability to "switch
>> off" items.
>>  
>>
> Agreed.
>
>> Do we want an association table between items and categories so items
>> can be in multiple categories?
>>  
>>
> Good question. It gives more flexibility...a tad bit more complexity.  
> Any objections?
>
>> Looks good
>>  
>>
> Should be, you created th ACL table ;-)
>
>> 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)
>>
>>  
>>
> I lump this in with the same issue with the ban plugin.  It's a basic 
> decision as to what should be considered part of the kernel versus 
> something that isn't.  I think this begs the need for a basic set of 
> criteria that we agree on that should help us determine if something 
> should be a plugin or in the kernel code.
>
>> If these are going to be different for different items, shouldn't it
>> be kept by the individual plugin managing the item?
>>  
>>
> What I was trying to achieve here is a plugin would insert a new set 
> of item types (1 or more).  Each type can have their own set of states 
> as this allows for customized work-ish tasks that are item-type 
> specific.  So to answer your question, yes, the plugin would insert 
> their data, we are simply providing the structures.
>
> Thanks for the input.  Are there any table that are missing.  I feel 
> good about the dialog on what was there but I'm a bit worried I may be 
> missing something that may be a critical need for the kernel.
>
> --Tony
> _______________________________________________
> geeklog-devel mailing list
> geeklog-devel at lists.geeklog.net
> http://lists.geeklog.net/listinfo/geeklog-devel


-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: create.sql
URL: <https://pairlist8.pair.net/pipermail/geeklog-devel/attachments/20041216/c8956489/attachment.ksh>