[geeklog-devel] Draft of schema for GL2

[Vincent Furia]

I thought we agreed previously that GL2 would force the use of INNODB
under mysql for a variety of reasons.  Among them the ability to use
foreign keys.  Is there a distribution of mysql in common use that
doesn't support INNODB?  Should we work to support MYISAM tables?

-Vinny

P.S. Dwight, I (and Tony too I think) really appreciated your input on
the DB issues.  I think you're by far the most experienced DB person
reading the list.

On Thu, 16 Dec 2004 12:33:24 -0500 (EST), dwight at trumbower.com
<dwight at trumbower.com> wrote:
> 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
> >
> >
> >
> 
> _______________________________________________
> geeklog-devel mailing list
> geeklog-devel at lists.geeklog.net
> http://lists.geeklog.net/listinfo/geeklog-devel
>