[geeklog-devtalk] geeklog-devel digest, Vol 1 #457 - 7 msgs

[geeklog-devel-request at lists.geeklog.net]

Send geeklog-devel mailing list submissions to
	geeklog-devel at lists.geeklog.net

To subscribe or unsubscribe via the World Wide Web, visit
	http://lists.geeklog.net/listinfo/geeklog-devel
or, via email, send a message with subject or body 'help' to
	geeklog-devel-request at lists.geeklog.net

You can reach the person managing the list at
	geeklog-devel-admin at lists.geeklog.net

When replying, please edit your Subject line so it is more specific
than "Re: Contents of geeklog-devel digest..."


Today's Topics:

   1. Re: Draft of schema for GL2 (Tony Bibbs)
   2. Re: Draft of schema for GL2 (Tony Bibbs)
   3. Re: Draft of schema for GL2 (dwight at trumbower.com)
   4. Re: Draft of schema for GL2 (Tony Bibbs)
   5. Re: Draft of schema for GL2 (Vincent Furia)
   6. Re: Draft of schema for GL2 (Tony Bibbs)
   7. Actual Working Schema for GL2 (Tony Bibbs)

--__--__--

Message: 1
Date: Thu, 16 Dec 2004 13:52:27 -0600
From: Tony Bibbs <tony at tonybibbs.com>
To: geeklog-devel at lists.geeklog.net
Subject: Re: [geeklog-devel] Draft of schema for GL2
Reply-To: geeklog-devel at lists.geeklog.net

In the table on this page:

http://vinny.furiafamily.com/article.php?story=20041129154258296

How does a new comment get it's rht, lft values?  I'm fuzzy on that.  
Anyway, it looks good to me.  For column names in GL2 how about 
leftIndex and rightIndex?

--Tony


How do you know what the rht
Vincent Furia wrote:


>I didn't look at slashcode.  But most other CMSs I looked at just grab

>all the comments from the DB and use a recursive php algorithm.  I

>didn't see any that had an efficient (or smart) way of grabbing

>comments.

>

>The implementation is original, the algorithm was spelled out in a

>couple different places with slight variations in the details.

>

>-Vinny

>

>

>On Thu, 16 Dec 2004 13:35:07 -0600, Tony Bibbs <tony at tonybibbs.com> wrote:

>  

>

>>Vincent Furia wrote:

>>

>>    

>>

>>>Yes.  If you look in the geeklog code for calls to SEC_inGroup() it is

>>>really only used for the  Root group.  Most of the other instance

>>>where it is used it is used against a group id variable.  The

>>>remainder I would argue are redundant if we better respected

>>>privileges in 1.3.x.  I'd recommend a "isRoot" function, but I think

>>>"inGroup" is really not needed except when actually administering

>>>group membership.

>>>

>>>

>>>      

>>>

>>Ok, I say we yank it...adding that column in if deemed appropriate isn't

>>too hard.

>>

>>    

>>

>>>Better names for those columns wouldn't hurt, I just couldn't come up

>>>with anything better when I was implementing it in 1.3.x.  I looked

>>>around at other algorithms when I was first looking into improving the

>>>performance of comments and didn't find anything better.  If you do

>>>find something let me know.  This was, by far, the best I found.  The

>>>biggest downside is that I had to lock the table during inserts, but

>>>that is really OBE since we can use transactions.

>>>

>>>

>>>      

>>>

>>Was this an original implementation or was it one you borrowed from

>>someplace?  WTF does slashcode use...you'd think they'd have to have

>>this issue nailed down by now.

>>

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

>  

>



--__--__--

Message: 2
Date: Thu, 16 Dec 2004 13:54:12 -0600
From: Tony Bibbs <tony at tonybibbs.com>
To: geeklog-devel at lists.geeklog.net
Subject: Re: [geeklog-devel] Draft of schema for GL2
Reply-To: geeklog-devel at lists.geeklog.net

Sweet. 

--Tony

dwight at trumbower.com wrote:


>We are fine as long as you don't use foreign key specific functions. LIke

>Cascade deletes and updates. Which it sounds like Propel will handle this.

>myisam will just ignore the ddl for foreign keys.

>

>Dwight

>

>>From MySql:

>In MySQL Server 3.23.44 and up, the InnoDB storage engine supports

>checking of foreign key constraints, including CASCADE, ON DELETE, and ON

>UPDATE. See section 15.7.4 FOREIGN KEY Constraints.

>

>For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY

>syntax in CREATE TABLE statements, but does not use or store it. In the

>future, the implementation will be extended to store this information in

>the table specification file so that it may be retrieved by mysqldump and

>ODBC. At a later stage, foreign key constraints will be implemented for

>MyISAM tables as well.

>

>

>

>

>  

>

>>I think we are fine.  Why?  Well, if we develop this using foreign keys

>>I think we cover our arses in the case someone doesn't use INNODB.

>>That's because I'm pretty sure that if a MySQL Server that doesn't have

>>INNODB support compiled in and somenoe imports DDL for a database that

>>tries to use INNODB, it will simply convert them to MyISAM and ignore

>>any FOREIGN KEY constraints (as opposed to creating an error and dying).

>>

>>As I said, the Propel classes enforce foreign key constraints natively

>>(assuming the schema.xml is right) so in the MyISAM case we are still

>>covered.  Only exception to this is obviously GL2 programmers can issue

>>raw SQL so there is the possiblity of orphaned children.  But again, if

>>all developers use INNODB the database during coding, we would catch any

>>potential errors regardless.

>>

>>So, in short, I don't think MyISAM users will be effected.  Of course

>>someone will need to test this...

>>

>>If I am right (which I'm pretty sure I am), we will want to put a stern

>>warning about the possible effects of not using INNODB.  Also, this make

>>the table-locking issue more complicated since we have to cover the

>>MyISAM side of things as well.  Or does it?  Table locking should only

>>occur on high traffic sites and, IMHO, you get what you get for using

>>MyISAM on a wildly popular website.

>>

>>--Tony

>>

>>dwight at trumbower.com wrote:

>>

>>    

>>

>>>Moving to INNODB is moving closer to a real DBMS and requires more

>>>

>>>administration. For one, you can't do backups unless you take the

>>>database

>>>off line or spend $500 for a product. I don't see ISP switching. Most

>>>can't handle standard mysql properly.

>>>

>>>I found this out when Dirk started making GL 1.3.x default to innodb. He

>>>changed it back to myisam with an option to make it innodb.

>>>

>>>

>>>I don't have a problem with forcing INNODB, just bringing up issues as I

>>>see them. The target user for GL2 will be much smaller than it is today.

>>>

>>>Dwight

>>>_______________________________________________

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

>  

>



--__--__--

Message: 3
Date: Thu, 16 Dec 2004 14:56:49 -0500 (EST)
Subject: Re: [geeklog-devel] Draft of schema for GL2
From: dwight at trumbower.com
To: geeklog-devel at lists.geeklog.net
Reply-To: geeklog-devel at lists.geeklog.net

SQL Trees and such,"comments"

One of the "fathers" of SQL trees - Joe Celko.
http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

Another decent article.
http://www.sqlteam.com/item.asp?ItemID=8866



> I didn't look at slashcode.  But most other CMSs I looked at just grab

> all the comments from the DB and use a recursive php algorithm.  I

> didn't see any that had an efficient (or smart) way of grabbing

> comments.

>

> The implementation is original, the algorithm was spelled out in a

> couple different places with slight variations in the details.

>

> -Vinny

>

>

> On Thu, 16 Dec 2004 13:35:07 -0600, Tony Bibbs <tony at tonybibbs.com> wrote:

>> Vincent Furia wrote:

>>

>> >Yes.  If you look in the geeklog code for calls to SEC_inGroup() it is

>> >really only used for the  Root group.  Most of the other instance

>> >where it is used it is used against a group id variable.  The

>> >remainder I would argue are redundant if we better respected

>> >privileges in 1.3.x.  I'd recommend a "isRoot" function, but I think

>> >"inGroup" is really not needed except when actually administering

>> >group membership.

>> >

>> >

>> Ok, I say we yank it...adding that column in if deemed appropriate isn't

>> too hard.

>>

>> >Better names for those columns wouldn't hurt, I just couldn't come up

>> >with anything better when I was implementing it in 1.3.x.  I looked

>> >around at other algorithms when I was first looking into improving the

>> >performance of comments and didn't find anything better.  If you do

>> >find something let me know.  This was, by far, the best I found.  The

>> >biggest downside is that I had to lock the table during inserts, but

>> >that is really OBE since we can use transactions.

>> >

>> >

>> Was this an original implementation or was it one you borrowed from

>> someplace?  WTF does slashcode use...you'd think they'd have to have

>> this issue nailed down by now.

>>

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

>



--__--__--

Message: 4
Date: Thu, 16 Dec 2004 14:00:12 -0600
From: Tony Bibbs <tony at tonybibbs.com>
To: geeklog-devel at lists.geeklog.net
Subject: Re: [geeklog-devel] Draft of schema for GL2
Reply-To: geeklog-devel at lists.geeklog.net

Excellent, looks like Vinny more than did his homework on this one (not 
that I doubted him).  I say because of this we should make the comments 
part of the core...that way we can hide the complexity of the 
inserts/deletes from plugin authors.

--Tony

dwight at trumbower.com wrote:


>SQL Trees and such,"comments"

>

>One of the "fathers" of SQL trees - Joe Celko.

>http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

>

>Another decent article.

>http://www.sqlteam.com/item.asp?ItemID=8866

>

>

>  

>

>>I didn't look at slashcode.  But most other CMSs I looked at just grab

>>all the comments from the DB and use a recursive php algorithm.  I

>>didn't see any that had an efficient (or smart) way of grabbing

>>comments.

>>

>>The implementation is original, the algorithm was spelled out in a

>>couple different places with slight variations in the details.

>>

>>-Vinny

>>

>>

>>On Thu, 16 Dec 2004 13:35:07 -0600, Tony Bibbs <tony at tonybibbs.com> wrote:

>>    

>>

>>>Vincent Furia wrote:

>>>

>>>      

>>>

>>>>Yes.  If you look in the geeklog code for calls to SEC_inGroup() it is

>>>>really only used for the  Root group.  Most of the other instance

>>>>where it is used it is used against a group id variable.  The

>>>>remainder I would argue are redundant if we better respected

>>>>privileges in 1.3.x.  I'd recommend a "isRoot" function, but I think

>>>>"inGroup" is really not needed except when actually administering

>>>>group membership.

>>>>

>>>>

>>>>        

>>>>

>>>Ok, I say we yank it...adding that column in if deemed appropriate isn't

>>>too hard.

>>>

>>>      

>>>

>>>>Better names for those columns wouldn't hurt, I just couldn't come up

>>>>with anything better when I was implementing it in 1.3.x.  I looked

>>>>around at other algorithms when I was first looking into improving the

>>>>performance of comments and didn't find anything better.  If you do

>>>>find something let me know.  This was, by far, the best I found.  The

>>>>biggest downside is that I had to lock the table during inserts, but

>>>>that is really OBE since we can use transactions.

>>>>

>>>>

>>>>        

>>>>

>>>Was this an original implementation or was it one you borrowed from

>>>someplace?  WTF does slashcode use...you'd think they'd have to have

>>>this issue nailed down by now.

>>>

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

>  

>



--__--__--

Message: 5
Date: Thu, 16 Dec 2004 15:07:53 -0500
From: Vincent Furia <vfuria at gmail.com>
To: geeklog-devel at lists.geeklog.net
Subject: Re: [geeklog-devel] Draft of schema for GL2
Reply-To: geeklog-devel at lists.geeklog.net

Whatever names you think best.  I just have a continuing giant brain
fart whenever I try to think of new names for those...

For a new comment, the value of leftIndex is the parent comments
rightIndex.  The value of rightIndex is the parent comments rightIndex
+ 1.  All Indexes (left and right) for all comments that have a value
greater than the new comments leftIndex are increased by two.  I guess
I need to explain that better in my article.

You might want to draw some pictures.  I had to do that a lot to prove
to myself that inserts and deletes would always work.

Also if you use this algorithm you technically don't need the pid
column (the parent id can be found easily given the left and right
indexes of a child comment).  I left the pid column in 1.3.x so I
wouldn't need to rewrite even more comment code.

-Vinny


On Thu, 16 Dec 2004 13:52:27 -0600, Tony Bibbs <tony at tonybibbs.com> wrote:

> In the table on this page:

> 

> http://vinny.furiafamily.com/article.php?story=20041129154258296

> 

> How does a new comment get it's rht, lft values?  I'm fuzzy on that.

> Anyway, it looks good to me.  For column names in GL2 how about

> leftIndex and rightIndex?

> 

> --Tony

> 

> How do you know what the rht

> Vincent Furia wrote:

> 

> >I didn't look at slashcode.  But most other CMSs I looked at just grab

> >all the comments from the DB and use a recursive php algorithm.  I

> >didn't see any that had an efficient (or smart) way of grabbing

> >comments.

> >

> >The implementation is original, the algorithm was spelled out in a

> >couple different places with slight variations in the details.

> >

> >-Vinny

> >

> >

> >On Thu, 16 Dec 2004 13:35:07 -0600, Tony Bibbs <tony at tonybibbs.com> wrote:

> >

> >

> >>Vincent Furia wrote:

> >>

> >>

> >>

> >>>Yes.  If you look in the geeklog code for calls to SEC_inGroup() it is

> >>>really only used for the  Root group.  Most of the other instance

> >>>where it is used it is used against a group id variable.  The

> >>>remainder I would argue are redundant if we better respected

> >>>privileges in 1.3.x.  I'd recommend a "isRoot" function, but I think

> >>>"inGroup" is really not needed except when actually administering

> >>>group membership.

> >>>

> >>>

> >>>

> >>>

> >>Ok, I say we yank it...adding that column in if deemed appropriate isn't

> >>too hard.

> >>

> >>

> >>

> >>>Better names for those columns wouldn't hurt, I just couldn't come up

> >>>with anything better when I was implementing it in 1.3.x.  I looked

> >>>around at other algorithms when I was first looking into improving the

> >>>performance of comments and didn't find anything better.  If you do

> >>>find something let me know.  This was, by far, the best I found.  The

> >>>biggest downside is that I had to lock the table during inserts, but

> >>>that is really OBE since we can use transactions.

> >>>

> >>>

> >>>

> >>>

> >>Was this an original implementation or was it one you borrowed from

> >>someplace?  WTF does slashcode use...you'd think they'd have to have

> >>this issue nailed down by now.

> >>

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

>


--__--__--

Message: 6
Date: Thu, 16 Dec 2004 14:26:51 -0600
From: Tony Bibbs <tony at tonybibbs.com>
To: geeklog-devel at lists.geeklog.net
Subject: Re: [geeklog-devel] Draft of schema for GL2
Reply-To: geeklog-devel at lists.geeklog.net

Gotcha.  Ok, so I'll yank the parent_id out altogether.

This is sweet.  Yeah, drawing a picture would probably help.

--Tony

Vincent Furia wrote:


>Whatever names you think best.  I just have a continuing giant brain

>fart whenever I try to think of new names for those...

>

>For a new comment, the value of leftIndex is the parent comments

>rightIndex.  The value of rightIndex is the parent comments rightIndex

>+ 1.  All Indexes (left and right) for all comments that have a value

>greater than the new comments leftIndex are increased by two.  I guess

>I need to explain that better in my article.

>

>You might want to draw some pictures.  I had to do that a lot to prove

>to myself that inserts and deletes would always work.

>

>Also if you use this algorithm you technically don't need the pid

>column (the parent id can be found easily given the left and right

>indexes of a child comment).  I left the pid column in 1.3.x so I

>wouldn't need to rewrite even more comment code.

>

>-Vinny

>

>

>On Thu, 16 Dec 2004 13:52:27 -0600, Tony Bibbs <tony at tonybibbs.com> wrote:

>  

>

>>In the table on this page:

>>

>>http://vinny.furiafamily.com/article.php?story=20041129154258296

>>

>>How does a new comment get it's rht, lft values?  I'm fuzzy on that.

>>Anyway, it looks good to me.  For column names in GL2 how about

>>leftIndex and rightIndex?

>>

>>--Tony

>>

>>How do you know what the rht

>>Vincent Furia wrote:

>>

>>    

>>

>>>I didn't look at slashcode.  But most other CMSs I looked at just grab

>>>all the comments from the DB and use a recursive php algorithm.  I

>>>didn't see any that had an efficient (or smart) way of grabbing

>>>comments.

>>>

>>>The implementation is original, the algorithm was spelled out in a

>>>couple different places with slight variations in the details.

>>>

>>>-Vinny

>>>

>>>

>>>On Thu, 16 Dec 2004 13:35:07 -0600, Tony Bibbs <tony at tonybibbs.com> wrote:

>>>

>>>

>>>      

>>>

>>>>Vincent Furia wrote:

>>>>

>>>>

>>>>

>>>>        

>>>>

>>>>>Yes.  If you look in the geeklog code for calls to SEC_inGroup() it is

>>>>>really only used for the  Root group.  Most of the other instance

>>>>>where it is used it is used against a group id variable.  The

>>>>>remainder I would argue are redundant if we better respected

>>>>>privileges in 1.3.x.  I'd recommend a "isRoot" function, but I think

>>>>>"inGroup" is really not needed except when actually administering

>>>>>group membership.

>>>>>

>>>>>

>>>>>

>>>>>

>>>>>          

>>>>>

>>>>Ok, I say we yank it...adding that column in if deemed appropriate isn't

>>>>too hard.

>>>>

>>>>

>>>>

>>>>        

>>>>

>>>>>Better names for those columns wouldn't hurt, I just couldn't come up

>>>>>with anything better when I was implementing it in 1.3.x.  I looked

>>>>>around at other algorithms when I was first looking into improving the

>>>>>performance of comments and didn't find anything better.  If you do

>>>>>find something let me know.  This was, by far, the best I found.  The

>>>>>biggest downside is that I had to lock the table during inserts, but

>>>>>that is really OBE since we can use transactions.

>>>>>

>>>>>

>>>>>

>>>>>

>>>>>          

>>>>>

>>>>Was this an original implementation or was it one you borrowed from

>>>>someplace?  WTF does slashcode use...you'd think they'd have to have

>>>>this issue nailed down by now.

>>>>

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

>>

>>    

>>

>_______________________________________________

>geeklog-devel mailing list

>geeklog-devel at lists.geeklog.net

>http://lists.geeklog.net/listinfo/geeklog-devel

>  

>



--__--__--

Message: 7
Date: Thu, 16 Dec 2004 15:43:51 -0600
From: Tony Bibbs <tony at tonybibbs.com>
To: geeklog-devel at lists.geeklog.net
Subject: [geeklog-devel] Actual Working Schema for GL2
Reply-To: geeklog-devel at lists.geeklog.net

This is a multi-part message in MIME format.
--------------000804000304080406010307
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ok, I've attached a working version of the schema.  Worth noting, is 
anywhere in the old schema where we had parent_id fields I replaced them 
with the left_index, right_index.  Seems to me that if it is good enough 
for comment performance, it's good enough for everyone else.

Next up is to build the first set of Propel classes from this schema.  
Cross your fingers.

--Tony

--------------000804000304080406010307
Content-Type: text/plain;
 name="create.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="create.sql"

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;

CREATE TABLE gl2_action (
  action_id int unsigned NOT NULL auto_increment,
  plugin_id int unsigned,
  action_name varchar(40) NOT NULL,
  description varchar (255) NOT NULL,
  PRIMARY KEY(action_id),
  INDEX (plugin_id),
  FOREIGN KEY (plugin_id) REFERENCES gl2_plugin(plugin_id),
  INDEX(action_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;

CREATE TABLE gl2_actionListener (
  action_id int unsigned NOT NULL,
  plugin_id int unsigned NOT NULL,
  PRIMARY KEY(action_id, plugin_id),
  INDEX (action_id),
  FOREIGN KEY (action_id) REFERENCES gl2_action(action_id),
  INDEX (plugin_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,
  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,
  signature varchar(128),
  biography text,
  first_name varchar(30),
  last_name varchar(30),
  homepage varchar(50),
  PRIMARY KEY(user_id),
  INDEX (user_name),
  INDEX (enabled),
  INDEX (email),
  INDEX (comment_mode_id),
  FOREIGN KEY (comment_mode_id) REFERENCES gl2_list_of_values(lov_id),
  INDEX (comment_order_id),
  FOREIGN KEY (comment_order_id) REFERENCES gl2_list_of_values(lov_id),
  INDEX (date_format_id),
  FOREIGN KEY (date_format_id) REFERENCES gl2_list_of_values(lov_id)
) TYPE=INNODB;

CREATE TABLE gl2_group (
  group_id int unsigned NOT NULL auto_increment,
  display_name varchar(50) NOT NULL,
  description varchar(255) NOT NULL,
  PRIMARY KEY (group_id)
) TYPE=INNODB;

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),
  INDEX (user_id),
  FOREIGN KEY(user_id) REFERENCES gl2_user(user_id),
  INDEX (group_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,
  INDEX(main_group_id),
  FOREIGN KEY(main_group_id) REFERENCES gl2_group(group_id),
  INDEX(assigned_user_id),
  FOREIGN KEY(assigned_user_id) REFERENCES gl2_user(user_id),
  INDEX(assigned_group_id),
  FOREIGN KEY(assigned_group_id) REFERENCES gl2_group(group_id)
) TYPE=INNODB;

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),
  INDEX (type_id),
  FOREIGN KEY(type_id) REFERENCES gl2_list_of_values(lov_id)
) TYPE=INNODB;

CREATE TABLE gl2_item (
  item_id int unsigned NOT NULL auto_increment,
  type_id int unsigned NOT NULL,
  user_id int unsigned NOT NULL,
  enabled tinyint unsigned NOT NULL DEFAULT 1,
  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,
  num_ratings mediumint unsigned NOT NULL DEFAULT 0,
  rating_sum int unsigned NOT NULL DEFAULT 0,
  expiration_date int unsigned DEFAULT NULL,
  left_index mediumint unsigned NOT NULL,
  right_index mediumint unsigned NOT NULL,
  INDEX (left_index),
  INDEX (right_index),
  PRIMARY KEY(item_id),
  INDEX (type_id),
  FOREIGN KEY(type_id) REFERENCES gl2_list_of_values(lov_id),
  INDEX (user_id),
  FOREIGN KEY(user_id) REFERENCES gl2_user(user_id),
  INDEX (state_id),
  FOREIGN KEY(state_id) REFERENCES gl2_item_type_state(state_id)
) TYPE=INNODB;

CREATE TABLE gl2_category (
  category_id int unsigned NOT NULL,
  catalog_id int unsigned NOT NULL,
  category_name varchar(50) NOT NULL,
  left_index mediumint unsigned NOT NULL,
  right_index mediumint unsigned NOT NULL,
  image_url VARCHAR(128) DEFAULT 'NULL',
  sort_num tinyint unsigned DEFAULT NULL,
  enabled tinyint unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY(category_id),
  FOREIGN KEY(category_id) REFERENCES gl2_item(item_id),
  INDEX (catalog_id),
  FOREIGN KEY (catalog_id) REFERENCES gl2_list_of_values(lov_id),
  INDEX (enabled)
) TYPE=INNODB;

CREATE TABLE gl2_item_category (
  item_id int unsigned NOT NULL,
  category_id int unsigned NOT NULL,
  INDEX (item_id),
  INDEX (category_id),
  PRIMARY KEY (item_id,category_id),
  FOREIGN KEY(item_id) REFERENCES gl2_item(item_id),
  INDEX (category_id),
  FOREIGN KEY(category_id) REFERENCES gl2_category(category_id)
) TYPE=INNODB;

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),
  INDEX (item_id),
  FOREIGN KEY(item_id) REFERENCES gl2_item(item_id),
  INDEX (user_id),
  FOREIGN KEY(user_id) REFERENCES gl2_user(user_id),
  INDEX (group_id),
  FOREIGN KEY(group_id) REFERENCES gl2_group(group_id)
) TYPE=INNODB;

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,
  left_index mediumint unsigned NOT NULL,
  right_index mediumint unsigned NOT NULL,
  ip_address varchar(11) NOT NULL,
  PRIMARY KEY(comment_id),
  INDEX (item_id),
  FOREIGN KEY(item_id) REFERENCES gl2_item(item_id),
  INDEX (left_index),
  INDEX (right_index)
) TYPE=INNODB;

CREATE TABLE gl2_block (
  block_id int unsigned NOT NULL,
  name varchar(30) NOT NULL,
  title varchar(50) NOT NULL,
  is_configurable tinyint unsigned NOT NULL DEFAULT 0,
  is_collapsable tinyint unsigned NOT NULL DEFAULT 0,
  is_undockable tinyint unsigned NOT NULL DEFAULT 0,
  type_id int unsigned NOT NULL,
  location_id int unsigned,
  rdf_url varchar(128),
  last_rdf_update int unsigned,
  function_name varchar(50),
  content text,
  sort_num tinyint unsigned,
  PRIMARY KEY(block_id),
  FOREIGN KEY(block_id) REFERENCES gl2_item(item_id),
  INDEX (location_id),
  FOREIGN KEY(location_id) REFERENCES gl2_list_of_values(lov_id),
  INDEX (type_id),
  FOREIGN KEY(type_id) REFERENCES gl2_list_of_values(lov_id),
  INDEX(sort_num)
) TYPE=INNODB;

CREATE TABLE gl2_user_block (
  block_id int unsigned NOT NULL,
  user_id int unsigned NOT NULL,
  state_id int unsigned NOT NULL,
  location_id int unsigned NOT NULL,
  sort_order tinyint unsigned NOT NULL,
  INDEX (block_id),
  INDEX (user_id),
  PRIMARY KEY(block_id, user_id),
  FOREIGN KEY(block_id) REFERENCES gl2_block(block_id),
  INDEX (user_id),
  FOREIGN KEY(user_id) REFERENCES gl2_user(user_id),
  INDEX (location_id),
  FOREIGN KEY(location_id) REFERENCES gl2_list_of_values(lov_id)
) TYPE=INNODB;
--------------000804000304080406010307--


--__--__--

_______________________________________________
geeklog-devel mailing list
geeklog-devel at lists.geeklog.net
http://lists.geeklog.net/listinfo/geeklog-devel


End of geeklog-devel Digest