[geeklog-devel] GL2 Schema
Blaine Lang
langmail at sympatico.ca
Sun Jan 19 19:55:19 EST 2003
Tony,
A very thorough and flexible schema.
As I was going through it I had a few thoughts - So thinking out loud here
are a few questions:
What about adding some date fields to some of these tables to catpure date
record was added.
Example tables: item_watch, user_watch, link, block, poll, poll_vote,
I know we have a number of other fields in the article or storyu tables
today
- icon, post mode, featured, frontpage, draft etc ...
Will topics or sections be just another item?
Do you need fields in the item table for order and maybe a suborder - that
would be used for sorting or controlling display placement?
Will we use tables to store GL2 config data
What about a table to track what users have seen or accessed. Can add a load
but it would be nice to be able to track this and allow the site to know
what is new for you. Possibly an additional table for summary level stats.
Plugins can hook in as well.\
Thanks,
Blaine
----- Original Message -----
From: "Tony Bibbs" <tony at tonybibbs.com>
To: "Dwight Trumbower" <dwight at trumbower.com>;
<geeklog-devel at lists.geeklog.net>
Sent: Friday, January 17, 2003 10:50 PM
Subject: [geeklog-devel] GL2 Schema
> Dwight,
>
> As promised, here are bits and pieces of a schema I started a while
> back. You will have a lot of questions (if not I'll be worried) as this
> schema has major holes and areas for improvement. Here are my concerns
> at this time:
>
> 1) This schema was the cart that came before the horse. Since starting
> this, we have started to step back and do detailed requirements
> gathering for various sub systems. As a result the schema will change
> radically and we'll need your help to keep it up-to-date for the three
> supported DBMS's (MySQL 4, PostgreSQL and MS SQL Server)
>
> 2) The only table fairly nailed down will be the session table which is
> below. Please ignore the session table in the attached schema.
>
> CREATE TABLE gl_sessions (
> session_id varchar(32) NOT NULL default '',
> expiration int(11) unsigned NOT NULL default '0',
> value text NOT NULL,
> PRIMARY KEY (session_id)
> ) TYPE=MyISAM;
>
> 3) I have parts of the A&A system done. Here is the SQL for that
> section so far:
>
> http://cvs.geeklog.net/chora/co.php/A_and_A/server/sql/mysql.sql
>
> 4) As I mentioned we will support MySQL 4. That version supports a
> number of common features found in 'real' DBMS's...particularly foriegn
> keys. The schema attached was written for MySQL 3.x so the syntax will
> need to change. Also, we'll need to avoid using MyISAM and instead use
> InnoDB table types. I know probably as much about PostgreSQL and MS SQL
> Server as the next idiot. We'll depend on you to manage this properly.
>
> You next step should be to combine all these into one file and then
> create files for each DBMS. From there we will start including you in
> on the development requirements gathering process so you can get
> familiar with the needs the database will need to accomodate. Then with
> myself and the other developers design the right data structures and
> overall design needed to start implementation.
>
> First on the list will be localization. We'll be in touch once the
> localization requirements are done which I hope will be as early as the
> middle of next week.
>
> Thanks for volunteering. I hope this works out as we need a competent
> DBA so us developers stop acting like we know it all ;-)
>
> --Tony
>
>
> Dwight Trumbower wrote:
> > At 03:58 PM 1/14/2003 -0600, you wrote:
> >
> >> > >As a starting point I will send over the latest GL2 schema. No
> >> physical
> >> > >database exists, this just shows what we see needed initially. As
> >> we dig
> >> > >into requirements we will review and modify as necessary (which you
> >> would
> >> > >steward). So as not to get ahead of ourselves. I will send over
that
> >> > >schema and let you start asking questions. Then I'd like to see
this
> >> > >converted over for MS SQL Server and PostgresSQL. If you get that
far
> >> > >odds are you are hired ;-)
> >> >
> >
> >
> > Did you send the schema and I missed it?
> >
> >
> >
> > Dwight
> > dwight at trumbower.com
>
>
> --
> +-------------------+--------------------------------------------------+
> |Tony Bibbs |[R]egardless of what you may think of our penal |
> |tony at tonybibbs.com |system, the fact is that every man in jail is one |
> | |less potential fisherman to clutter up your |
> | |favorite pool or pond. --Ed Zern |
>
> +-------------------+--------------------------------------------------+
>
----------------------------------------------------------------------------
----
> # This is the generic item table. All 'things' in geeklog are stored
here.
> CREATE TABLE item (
> it_item_id mediumint(10) unsigned NOT NULL auto_increment,
> it_type_id mediumint(10) unsigned NOT NULL,
> it_category_id mediumint(10) unsigned NOT NULL,
> it_uid mediumint(10) unsigned NOT NULL,
> it_parent_item_id mediumint(10) unsigned DEFAULT NULL,
> it_date int NOT NULL,
> it_state tinyint(1) unsigned NOT NULL DEFAULT '1',
> it_views mediumint(10) unsigned NOT NULL DEFAULT '0',
> it_expire_date int DEFAULT NULL,
> it_emails mediumint(10) unsigned NOT NULL DEFAULT '0',
> it_num_ratings mediumint(10) unsigned NOT NULL DEFAULT '0',
> it_rating_sum mediumint(10) unsigned NOT NULL DEFAULT '0',
> it_owner_id mediumint(10) unsigned NOT NULL,
> it_group_id mediumint(10) unsigned NOT NULL,
> it_perm_owner tinyint(1) unsigned NOT NULL,
> it_perm_group tinyint(1) unsigned NOT NULL,
> it_perm_members tinyint(1) unsigned NOT NULL,
> it_perm_anon tinyint(1) unsigned NOT NULL,
> INDEX(it_category_id),
> INDEX(it_uid),
> INDEX(it_parent_item_id),
> PRIMARY KEY(it_item_id)
> );
>
> # This holds all the valid item types
> CREATE TABLE item_type (
> itt_type_id mediumint(10) unsigned NOT NULL auto_increment,
> itt_type_name varchar(50) NOT NULL,
> PRIMARY KEY(itt_type_id)
> );
>
> # this allows support for multiple item states
> # 0 is reserved for 'disabled'
> # 1 is reserved for 'enabled'
> # 2 is reserved for 'submission'
> CREATE TABLE item_state (
> is_state_id tinyint(2) unsigned NOT NULL auto_increment,
> is_state_name varchar(50) NOT NULL,
> is_description varchar(255) NOT NULL
> );
>
> CREATE TABLE category (
> cat_category_id mediumint(10) unsigned NOT NULL auto_increment,
> cat_name varchar(50) NOT NULL,
> cat_sort_num tinyint(4) unsigned DEFAULT NULL,
> cat_image_url varchar(128) DEFAULT NULL,
> cat_parent_category_id mediumint(10) unsigned NOT NULL,
> cat_owner_id mediumint(10) unsigned NOT NULL,
> cat_group_id mediumint(10) unsigned NOT NULL,
> cat_perm_owner tinyint(1) unsigned NOT NULL,
> cat_perm_group tinyint(1) unsigned NOT NULL,
> cat_perm_members tinyint(1) unsigned NOT NULL,
> cat_perm_anon tinyint(1) unsigned NOT NULL,
> PRIMARY KEY(cat_category_id)
> );
>
> # Base user information
> CREATE TABLE user (
> u_uid mediumint(10) unsigned NOT NULL auto_increment,
> u_username varchar(25) NOT NULL,
> u_password varchar(35) NOT NULL,
> u_enabled tinyint(1) unsigned NOT NULL,
> u_email varchar(128) NOT NULL,
> u_reg_date int NOT NULL,
> u_profile_views mediumint(10) unsigned NOT NULL DEFAULT '0',
> u_date_format_id tinyint(4) unsigned,
> u_locale varchar(3),
> u_cookie_timeout mediumint(8) unsigned,
> u_items_per_page tinyint(2) unsigned NOT NULL DEFAULT '10',
> u_lang_id tinyint(4) unsigned NOT NULL DEFAULT '1',
> u_blocks_enabled tinyint(1) unsigned DEFAULT '1',
> u_comment_mode_id tinyint(1) unsigned,
> u_comment_order_id tinyint(1) unsigned,
> u_comment_limit mediumint(10) unsigned DEFAULT NULL,
> INDEX(u_username),
> PRIMARY KEY(u_uid)
> );
>
> CREATE TABLE user_block (
> ub_uid mediumint(10) unsigned NOT NULL auto_increment,
> ub_block_item_id mediumint(10) unsigned NOT NULL,
> PRIMARY KEY(ub_uid, ub_block_item_id)
> );
>
> CREATE TABLE user_category (
> uc_uid mediumint(10) unsigned NOT NULL auto_increment,
> uc_category_id mediumint(10) unsigned NOT NULL,
> PRIMARY KEY(uc_uid, uc_category_id)
> );
>
> # Supplemental user information
> CREATE TABLE user_supp (
> us_uid mediumint(10) unsigned NOT NULL auto_increment,
> us_fname varchar(40) DEFAULT NULL,
> us_lname varchar(40) DEFAULT NULL,
> us_homepage varchar(128) DEFAULT NULL,
> us_signature varchar(160) DEFAULT NULL,
> us_biography text DEFAULT NULL,
> us_filequota_mb float(6),
> PRIMARY KEY(us_uid)
> );
>
> # Watch table, used to let users select items
> # for which they want to receive emails when someone
> # posts to it
> CREATE TABLE item_watch (
> iw_item_id mediumint(10) unsigned NOT NULL auto_increment,
> iw_uid mediumint(10) unsigned NOT NULL,
> PRIMARY KEY(iw_item_id,iw_uid)
> );
>
> # users can also watch other users
> CREATE TABLE user_watch (
> uw_uid mediumint(10) unsigned NOT NULL,
> uw_watch_uid mediumint(10) unsigned NOT NULL,
> PRIMARY KEY(uw_uid,uw_watch_uid)
> );
>
> # Allows users to have buddy lists
> CREATE TABLE user_buddy (
> ub_uid mediumint(10) unsigned NOT NULL,
> ub_buddy_uid mediumint(10) unsigned NOT NULL,
> PRIMARY KEY(ub_uid,ub_buddy_uid)
> );
>
> CREATE TABLE language (
> lng_lang_id tinyint(4) unsigned NOT NULL DEFAULT '1' auto_increment,
> lng_name varchar(50),
> PRIMARY KEY(lng_lang_id)
> );
>
> CREATE TABLE ban_list (
> ban_id mediumint(10) unsigned NOT NULL auto_increment,
> ban_uid mediumint(10) unsigned DEFAULT NULL,
> ban_ip varchar(15) DEFAULT NULL,
> ban_start int DEFAULT NULL,
> ban_end int DEFAULT NULL,
> ban_reason_id mediumint(10) default NULL,
> INDEX(ban_uid),
> PRIMARY KEY (ban_id)
> );
>
> CREATE TABLE ban_reason (
> br_reason_id mediumint(10) unsigned NOT NULL auto_increment,
> br_name varchar(64) NOT NULL,
> br_description varchar(255),
> PRIMARY KEY(br_reason_id)
> );
>
> # article table, art_item_id tied to item table
> CREATE TABLE article (
> art_item_id mediumint(10) unsigned NOT NULL,
> art_title varchar(128) NOT NULL,
> art_pages tinyint(2) unsigned NOT NULL DEFAULT '1',
> art_intro text,
> PRIMARY KEY(art_item_id)
> );
>
> # when an article is archived, all the pages are compiled into a single
page and
> # all the comments attached to the bottom of it
> CREATE TABLE article_archive (
> aa_item_id mediumint(10) unsigned NOT NULL,
> aa_title varchar(128) NOT NULL,
> aa_text text NOT NULL,
> PRIMARY KEY(aa_item_id)
> );
>
> # holds pages of an article
> CREATE TABLE article_page (
> ap_item_id mediumint(10) unsigned NOT NULL,
> ap_page_number tinyint(2) unsigned NOT NULL,
> ap_text text NOT NULL,
> PRIMARY KEY(ap_item_id,ap_page_number)
> );
>
> # article table, cmt_item_id tied to item table
> CREATE TABLE comment (
> cmt_item_id mediumint(10) unsigned NOT NULL,
> cmt_title varchar(128),
> cmt_text text NOT NULL,
> cmt_parent_id mediumint(10),
> PRIMARY KEY(cmt_item_id)
> );
>
> # ratings table
> CREATE TABLE rating (
> rt_value tinyint(4) NOT NULL,
> rt_label varchar(25) NOT NULL,
> PRIMARY KEY(rt_value)
> );
>
> # generic media table, f_item_id tied to item table
> CREATE TABLE file (
> f_item_id mediumint(10) unsigned NOT NULL,
> f_file_type_id tinyint(2) unsigned NOT NULL,
> f_size varchar(10) NOT NULL,
> f_description varchar(255),
> PRIMARY KEY(f_item_id)
> );
>
> # Valid file types
> CREATE TABLE file_type (
> ft_file_type_id tinyint(2) NOT NULL,
> ft_description varchar(255) NOT NULL,
> ft_extensions varchar(255),
> PRIMARY KEY(ft_file_type_id)
> );
>
> # link, lnk_item_id tied to item table
> CREATE TABLE link (
> lnk_item_id mediumint(10) unsigned NOT NULL,
> lnk_url varchar(128) NOT NULL,
> lnk_title varchar(128) NOT NULL,
> lnk_description text,
> PRIMARY KEY(lnk_item_id)
> );
>
> # blocks, blk_item_id tied to item table, note can now specify if a block
> # opens links in new windows.
> CREATE TABLE block (
> blk_item_id mediumint(10) unsigned NOT NULL,
> blk_name varchar(50) NOT NULL,
> blk_block_type tinyint(1) unsigned NOT NULL,
> blk_title varchar(50) NOT NULL,
> blk_category_id mediumint(10) unsigned NOT NULL,
> blk_location_id tinyint(1) unsigned NOT NULL,
> blk_user_configurable tinyint(1) unsigned NOT NULL DEFAULT '0',
> blk_collapsable tinyint(1) unsigned NOT NULL DEFAULT '0',
> blk_new_windows tinyint(1) unsigned NOT NULL DEFAULT '1',
> blk_max_items tinyint(2) unsigned DEFAULT NULL,
> blk_sort_num tinyint(2) unsigned,
> blk_content text,
> blk_rdf_url varchar(128),
> blk_rdf_updated int,
> blk_block_fn varchar(64),
> PRIMARY KEY(blk_item_id)
> );
>
> # poll questions
> CREATE TABLE poll (
> p_item_id mediumint(10) unsigned NOT NULL,
> p_question varchar(255) NOT NULL,
> p_votes mediumint(10) unsigned NOT NULL,
> PRIMARY KEY(p_item_id)
> );
>
> # valid answers for a poll
> CREATE TABLE poll_answer(
> pa_answer_id mediumint(10) unsigned NOT NULL auto_increment,
> pa_item_id mediumint(10) unsigned NOT NULL,
> pa_answer varchar(255) NOT NULL,
> pa_votes mediumint(10) unsigned NOT NULL,
> PRIMARY KEY(pa_answer_id)
> );
>
> # voting log. this will tie a vote to a user or, if they are not logged
in
> # to an IP. If the IP is used, all other users are screwed unless they
log in!
> CREATE TABLE poll_vote(
> pv_vote_id mediumint(10) unsigned NOT NULL,
> pv_item_id mediumint(10) unsigned NOT NULL,
> pv_uid mediumint(10) unsigned,
> pv_ip_address varchar(15) NOT NULL,
> INDEX(pv_uid),
> INDEX(pv_ip_address),
> INDEX(pv_item_id),
> PRIMARY KEY(pv_vote_id)
> );
>
> # Session table (OUT-OF-DATE)
> CREATE TABLE session (
> s_session_id int(10) unsigned NOT NULL,
> s_start int(10) unsigned NOT NULL,
> s_ip_address varchar(15) NOT NULL,
> s_uid mediumint(8) NOT NULL,
> PRIMARY KEY(s_session_id)
> );
>
> # This table defines all groups (OUT-OF-DATE)
> CREATE TABLE groups (
> grp_group_id mediumint(10) unsigned NOT NULL,
> grp_name varchar(50) NOT NULL,
> grp_description varchar(255),
> grp_core_flag tinyint(1) unsigned NOT NULL DEFAULT '0',
> grp_allowed_bbcode varchar(255),
> PRIMARY KEY(grp_group_id)
> );
>
> # This table defines all valid features (OUT-OF-DATE)
> CREATE TABLE feature (
> fea_feature_id mediumint(10) unsigned NOT NULL,
> fea_name varchar(30) NOT NULL,
> fea_description varchar(255),
> fea_core_flag tinyint(1) unsigned NOT NULL DEFAULT '0',
> PRIMARY KEY(fea_feature_id)
> );
>
> # This table gives a group access to a feature (OUT-OF-DATE)
> CREATE TABLE access (
> acc_feature_id mediumint(10) unsigned NOT NULL,
> acc_group_id mediumint(10) unsigned NOT NULL,
> PRIMARY KEY(acc_feature_id, acc_group_id)
> );
>
> # This table assigns a user or group to a group (OUT-OF-DATE
> CREATE TABLE group_assignment (
> ga_main_group_id mediumint(10) unsigned NOT NULL,
> ga_uid mediumint(10) unsigned,
> ga_group_id mediumint(10) unsigned,
> UNIQUE(ga_main_group_id,ga_uid,ga_group_id)
> );
>
> # Handy table for custom hacks
> CREATE TABLE var (
> var_name varchar(20) NOT NULL,
> var_value varchar(128) NOT NULL,
> var_description varchar(255),
> PRIMARY KEY(var_name)
> );
>
> CREATE TABLE private_messages (
> pm_message_id mediumint(10) NOT NULL auto_increment,
> pm_from_uid mediumint(10) NOT NULL,
> pm_to_uid mediumint(10) NOT NULL,
> pm_date int NOT NULL,
> pm_state_id tinyint(1) unsigned NOT NULL,
> pm_subject varchar(128),
> pm_text text NOT NULL,
> INDEX(pm_from_uid),
> INDEX(pm_to_uid),
> INDEX(pm_state_id),
> PRIMARY KEY(pm_message_id)
> );
>
> CREATE TABLE private_message_state (
> pms_state_id tinyint(1) unsigned NOT NULL auto_increment,
> pms_name varchar(128) NOT NULL,
> PRIMARY KEY(pms_state_id)
> );
>
> CREATE TABLE modules (
> mod_module_id mediumint(10) NOT NULL,
> mod_name varchar(30) NOT NULL,
> mod_enabled tinyint(1) unsigned NOT NULL DEFAULT '1',
> mod_admin_sort_num tinyint(2) unsigned,
> mod_user_sort_num tinyint(2) unsigned,
> INDEX(mod_enabled),
> PRIMARY KEY(mod_module_id)
> );
More information about the geeklog-devel
mailing list