[geeklog-devel] Draft of schema for GL2
Tony Bibbs
tony at tonybibbs.com
Thu Dec 16 12:43:29 EST 2004
I think we have to require INNODB. Without it you don't get foreign
keys nor transaction support. Two things, IMHO, I don't want to live
without. I think eventually ISP's will get their act together and start
supporting versions of MySQL that support INNODB (why that wouldn't be
the default table type moving forward is beyond me).
Vinny, to answer your question, the compilation of MySQL must have the
innodb support added to the ./configure. So that would be a
distribution specific thing. To your point, though, I think it would be
really dumb for distro's not to support INNODB by default.
--Tony
Vincent Furia wrote:
>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
>>
>>
>>
>_______________________________________________
>geeklog-devel mailing list
>geeklog-devel at lists.geeklog.net
>http://lists.geeklog.net/listinfo/geeklog-devel
>
>
More information about the geeklog-devel
mailing list