[geeklog-devel] Draft of schema for GL2

[dwight at trumbower.com]

Foreign Keys are a good thing, but I wouldn't plan on any db specific
functionality with them. MYISAM doesn't work with foreign keys and 90% of
the users won't be using innodb.

> 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
>
>
>