schema.sql
changeset 218 e0ec986c0af3
parent 213 0fe1f610698d
child 221 956263992d48
equal deleted inserted replaced
217:5bcdee999015 218:e0ec986c0af3
    12 
    12 
    13 CREATE TABLE {{TABLE_PREFIX}}categories(
    13 CREATE TABLE {{TABLE_PREFIX}}categories(
    14   page_id varchar(64),
    14   page_id varchar(64),
    15   namespace varchar(64),
    15   namespace varchar(64),
    16   category_id varchar(64)
    16   category_id varchar(64)
    17 ) CHARACTER SET `utf8`;
    17 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
    18 
    18 
    19 CREATE TABLE {{TABLE_PREFIX}}comments(
    19 CREATE TABLE {{TABLE_PREFIX}}comments(
    20   comment_id int(12) NOT NULL auto_increment,
    20   comment_id int(12) NOT NULL auto_increment,
    21   page_id text,
    21   page_id text,
    22   namespace text,
    22   namespace text,
    25   name text,
    25   name text,
    26   approved tinyint(1) default 1,
    26   approved tinyint(1) default 1,
    27   user_id mediumint(8) NOT NULL DEFAULT -1,
    27   user_id mediumint(8) NOT NULL DEFAULT -1,
    28   time int(12) NOT NULL DEFAULT 0,
    28   time int(12) NOT NULL DEFAULT 0,
    29   PRIMARY KEY ( comment_id )
    29   PRIMARY KEY ( comment_id )
    30 ) CHARACTER SET `utf8`;
    30 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
    31 
    31 
    32 CREATE TABLE {{TABLE_PREFIX}}config(
    32 CREATE TABLE {{TABLE_PREFIX}}config(
    33   config_name varchar(63),
    33   config_name varchar(63),
    34   config_value text
    34   config_value text
    35 ) CHARACTER SET `utf8`;
    35 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
    36 
    36 
    37 CREATE TABLE {{TABLE_PREFIX}}logs(
    37 CREATE TABLE {{TABLE_PREFIX}}logs(
    38   log_type varchar(16),
    38   log_type varchar(16),
    39   action varchar(16),
    39   action varchar(16),
    40   time_id int(12) NOT NULL default '0',
    40   time_id int(12) NOT NULL default '0',
    44   page_text text,
    44   page_text text,
    45   char_tag varchar(40),
    45   char_tag varchar(40),
    46   author varchar(63),
    46   author varchar(63),
    47   edit_summary text,
    47   edit_summary text,
    48   minor_edit tinyint(1)
    48   minor_edit tinyint(1)
    49 ) CHARACTER SET `utf8`;
    49 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
    50 
    50 
    51 CREATE TABLE {{TABLE_PREFIX}}page_text(
    51 CREATE TABLE {{TABLE_PREFIX}}page_text(
    52   page_id varchar(63),
    52   page_id varchar(63),
    53   namespace varchar(16) NOT NULL default 'Article',
    53   namespace varchar(16) NOT NULL default 'Article',
    54   page_text text,
    54   page_text text,
    67   protected tinyint(1) NOT NULL DEFAULT 0,
    67   protected tinyint(1) NOT NULL DEFAULT 0,
    68   wiki_mode tinyint(1) NOT NULL DEFAULT 2,
    68   wiki_mode tinyint(1) NOT NULL DEFAULT 2,
    69   delvotes int(10) NOT NULL default 0,
    69   delvotes int(10) NOT NULL default 0,
    70   password varchar(40) NOT NULL DEFAULT '',
    70   password varchar(40) NOT NULL DEFAULT '',
    71   delvote_ips text DEFAULT NULL
    71   delvote_ips text DEFAULT NULL
    72 ) CHARACTER SET `utf8`;
    72 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
    73 
    73 
    74 CREATE TABLE {{TABLE_PREFIX}}session_keys(
    74 CREATE TABLE {{TABLE_PREFIX}}session_keys(
    75   session_key varchar(32),
    75   session_key varchar(32),
    76   salt varchar(32),
    76   salt varchar(32),
    77   user_id mediumint(8),
    77   user_id mediumint(8),
    78   auth_level tinyint(1) NOT NULL default '0',
    78   auth_level tinyint(1) NOT NULL default '0',
    79   source_ip varchar(10) default '0x7f000001',
    79   source_ip varchar(10) default '0x7f000001',
    80   time bigint(15) default '0'
    80   time bigint(15) default '0'
    81 ) CHARACTER SET `utf8`;
    81 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
    82 
    82 
    83 CREATE TABLE {{TABLE_PREFIX}}themes(
    83 CREATE TABLE {{TABLE_PREFIX}}themes(
    84   theme_id varchar(63),
    84   theme_id varchar(63),
    85   theme_name text,
    85   theme_name text,
    86   theme_order smallint(5) NOT NULL default '1',
    86   theme_order smallint(5) NOT NULL default '1',
    87   default_style varchar(63) NOT NULL DEFAULT '',
    87   default_style varchar(63) NOT NULL DEFAULT '',
    88   enabled tinyint(1) NOT NULL default '1'
    88   enabled tinyint(1) NOT NULL default '1'
    89 ) CHARACTER SET `utf8`;
    89 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
    90 
    90 
    91 CREATE TABLE {{TABLE_PREFIX}}users(
    91 CREATE TABLE {{TABLE_PREFIX}}users(
    92   user_id mediumint(8) NOT NULL auto_increment,
    92   user_id mediumint(8) NOT NULL auto_increment,
    93   username text,
    93   username text,
    94   password varchar(255),
    94   password varchar(255),
   104   old_encryption tinyint(1) NOT NULL DEFAULT 0,
   104   old_encryption tinyint(1) NOT NULL DEFAULT 0,
   105   temp_password text,
   105   temp_password text,
   106   temp_password_time int(12) NOT NULL DEFAULT 0,
   106   temp_password_time int(12) NOT NULL DEFAULT 0,
   107   user_coppa tinyint(1) NOT NULL DEFAULT 0,
   107   user_coppa tinyint(1) NOT NULL DEFAULT 0,
   108   PRIMARY KEY  (user_id)
   108   PRIMARY KEY  (user_id)
   109 ) CHARACTER SET `utf8`;
   109 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   110 
   110 
   111 CREATE TABLE {{TABLE_PREFIX}}users_extra(
   111 CREATE TABLE {{TABLE_PREFIX}}users_extra(
   112   user_id mediumint(8) NOT NULL,
   112   user_id mediumint(8) NOT NULL,
   113   user_aim varchar(63),
   113   user_aim varchar(63),
   114   user_yahoo varchar(63),
   114   user_yahoo varchar(63),
   118   user_location text,
   118   user_location text,
   119   user_job text,
   119   user_job text,
   120   user_hobbies text,
   120   user_hobbies text,
   121   email_public tinyint(1) NOT NULL DEFAULT 0,
   121   email_public tinyint(1) NOT NULL DEFAULT 0,
   122   PRIMARY KEY ( user_id ) 
   122   PRIMARY KEY ( user_id ) 
   123 ) CHARACTER SET `utf8`;
   123 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   124 
   124 
   125 CREATE TABLE {{TABLE_PREFIX}}banlist(
   125 CREATE TABLE {{TABLE_PREFIX}}banlist(
   126   ban_id mediumint(8) NOT NULL auto_increment,
   126   ban_id mediumint(8) NOT NULL auto_increment,
   127   ban_type tinyint(1),
   127   ban_type tinyint(1),
   128   ban_value varchar(64),
   128   ban_value varchar(64),
   129   is_regex tinyint(1) DEFAULT 0,
   129   is_regex tinyint(1) DEFAULT 0,
   130   reason text,
   130   reason text,
   131   PRIMARY KEY ( ban_id ) 
   131   PRIMARY KEY ( ban_id ) 
   132 ) CHARACTER SET `utf8`;
   132 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   133 
   133 
   134 CREATE TABLE {{TABLE_PREFIX}}files(
   134 CREATE TABLE {{TABLE_PREFIX}}files(
   135   file_id int(12) NOT NULL auto_increment,
   135   file_id int(12) NOT NULL auto_increment,
   136   time_id int(12) NOT NULL,
   136   time_id int(12) NOT NULL,
   137   page_id varchar(63) NOT NULL,
   137   page_id varchar(63) NOT NULL,
   139   size bigint(15) NOT NULL,
   139   size bigint(15) NOT NULL,
   140   mimetype varchar(63) default NULL,
   140   mimetype varchar(63) default NULL,
   141   file_extension varchar(8) default NULL,
   141   file_extension varchar(8) default NULL,
   142   file_key varchar(32) NOT NULL,
   142   file_key varchar(32) NOT NULL,
   143   PRIMARY KEY (file_id) 
   143   PRIMARY KEY (file_id) 
   144 ) CHARACTER SET `utf8`;
   144 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   145 
   145 
   146 CREATE TABLE {{TABLE_PREFIX}}buddies(
   146 CREATE TABLE {{TABLE_PREFIX}}buddies(
   147   buddy_id int(15) NOT NULL auto_increment,
   147   buddy_id int(15) NOT NULL auto_increment,
   148   user_id mediumint(8),
   148   user_id mediumint(8),
   149   buddy_user_id mediumint(8),
   149   buddy_user_id mediumint(8),
   150   is_friend tinyint(1) NOT NULL default '1',
   150   is_friend tinyint(1) NOT NULL default '1',
   151   PRIMARY KEY  (buddy_id) 
   151   PRIMARY KEY  (buddy_id) 
   152 ) CHARACTER SET `utf8`;
   152 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   153 
   153 
   154 CREATE TABLE {{TABLE_PREFIX}}privmsgs(
   154 CREATE TABLE {{TABLE_PREFIX}}privmsgs(
   155   message_id int(15) NOT NULL auto_increment,
   155   message_id int(15) NOT NULL auto_increment,
   156   message_from varchar(63),
   156   message_from varchar(63),
   157   message_to varchar(255),
   157   message_to varchar(255),
   159   subject varchar(63),
   159   subject varchar(63),
   160   message_text text,
   160   message_text text,
   161   folder_name varchar(63),
   161   folder_name varchar(63),
   162   message_read tinyint(1) NOT NULL DEFAULT 0,
   162   message_read tinyint(1) NOT NULL DEFAULT 0,
   163   PRIMARY KEY  (message_id) 
   163   PRIMARY KEY  (message_id) 
   164 ) CHARACTER SET `utf8`;
   164 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   165 
   165 
   166 CREATE TABLE {{TABLE_PREFIX}}sidebar(
   166 CREATE TABLE {{TABLE_PREFIX}}sidebar(
   167   item_id smallint(3) NOT NULL auto_increment,
   167   item_id smallint(3) NOT NULL auto_increment,
   168   item_order smallint(3) NOT NULL DEFAULT 0,
   168   item_order smallint(3) NOT NULL DEFAULT 0,
   169   item_enabled tinyint(1) NOT NULL DEFAULT 1,
   169   item_enabled tinyint(1) NOT NULL DEFAULT 1,
   170   sidebar_id smallint(3) NOT NULL DEFAULT 1,
   170   sidebar_id smallint(3) NOT NULL DEFAULT 1,
   171   block_name varchar(63) NOT NULL,
   171   block_name varchar(63) NOT NULL,
   172   block_type tinyint(1) NOT NULL DEFAULT 0,
   172   block_type tinyint(1) NOT NULL DEFAULT 0,
   173   block_content text,
   173   block_content text,
   174   PRIMARY KEY ( item_id )
   174   PRIMARY KEY ( item_id )
   175 ) CHARACTER SET `utf8`;
   175 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   176 
   176 
   177 CREATE TABLE {{TABLE_PREFIX}}hits(
   177 CREATE TABLE {{TABLE_PREFIX}}hits(
   178   hit_id bigint(20) NOT NULL auto_increment,
   178   hit_id bigint(20) NOT NULL auto_increment,
   179   username varchar(63) NOT NULL,
   179   username varchar(63) NOT NULL,
   180   time int(12) NOT NULL DEFAULT 0,
   180   time int(12) NOT NULL DEFAULT 0,
   181   page_id varchar(63),
   181   page_id varchar(63),
   182   namespace varchar(63),
   182   namespace varchar(63),
   183   PRIMARY KEY ( hit_id ) 
   183   PRIMARY KEY ( hit_id ) 
   184 ) CHARACTER SET `utf8`;
   184 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   185 
   185 
   186 CREATE TABLE {{TABLE_PREFIX}}search_index(
   186 CREATE TABLE {{TABLE_PREFIX}}search_index(
   187   word varbinary(64) NOT NULL,
   187   word varbinary(64) NOT NULL,
   188   page_names text,
   188   page_names text,
   189   PRIMARY KEY ( word ) 
   189   PRIMARY KEY ( word ) 
   190 ) CHARACTER SET `utf8`;
   190 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   191 
   191 
   192 CREATE TABLE {{TABLE_PREFIX}}groups(
   192 CREATE TABLE {{TABLE_PREFIX}}groups(
   193   group_id mediumint(5) UNSIGNED NOT NULL auto_increment,
   193   group_id mediumint(5) UNSIGNED NOT NULL auto_increment,
   194   group_name varchar(64),
   194   group_name varchar(64),
   195   group_type tinyint(1) NOT NULL DEFAULT 1,
   195   group_type tinyint(1) NOT NULL DEFAULT 1,
   196   PRIMARY KEY ( group_id ),
   196   PRIMARY KEY ( group_id ),
   197   system_group tinyint(1) NOT NULL DEFAULT 0 
   197   system_group tinyint(1) NOT NULL DEFAULT 0 
   198 ) CHARACTER SET `utf8`;
   198 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   199 
   199 
   200 CREATE TABLE {{TABLE_PREFIX}}group_members(
   200 CREATE TABLE {{TABLE_PREFIX}}group_members(
   201   member_id int(12) UNSIGNED NOT NULL auto_increment,
   201   member_id int(12) UNSIGNED NOT NULL auto_increment,
   202   group_id mediumint(5) UNSIGNED NOT NULL,
   202   group_id mediumint(5) UNSIGNED NOT NULL,
   203   user_id int(12) NOT NULL,
   203   user_id int(12) NOT NULL,
   204   is_mod tinyint(1) NOT NULL DEFAULT 0,
   204   is_mod tinyint(1) NOT NULL DEFAULT 0,
   205   pending tinyint(1) NOT NULL DEFAULT 0,
   205   pending tinyint(1) NOT NULL DEFAULT 0,
   206   PRIMARY KEY ( member_id ) 
   206   PRIMARY KEY ( member_id ) 
   207 ) CHARACTER SET `utf8`;
   207 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   208 
   208 
   209 CREATE TABLE {{TABLE_PREFIX}}acl(
   209 CREATE TABLE {{TABLE_PREFIX}}acl(
   210   rule_id int(12) UNSIGNED NOT NULL auto_increment,
   210   rule_id int(12) UNSIGNED NOT NULL auto_increment,
   211   target_type tinyint(1) UNSIGNED NOT NULL,
   211   target_type tinyint(1) UNSIGNED NOT NULL,
   212   target_id int(12) UNSIGNED NOT NULL,
   212   target_id int(12) UNSIGNED NOT NULL,
   213   page_id varchar(255),
   213   page_id varchar(255),
   214   namespace varchar(24),
   214   namespace varchar(24),
   215   rules text,
   215   rules text,
   216   PRIMARY KEY ( rule_id ) 
   216   PRIMARY KEY ( rule_id ) 
   217 ) CHARACTER SET `utf8`;
   217 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   218 
       
   219 CREATE TABLE {{TABLE_PREFIX}}search_cache(
       
   220   search_id int(15) NOT NULL auto_increment,
       
   221   search_time int(11) NOT NULL,
       
   222   query text,
       
   223   results longblob,
       
   224   PRIMARY KEY ( search_id )
       
   225 ) CHARACTER SET `utf8`;
       
   226 
   218 
   227 -- Added in 1.0.1
   219 -- Added in 1.0.1
   228 
   220 
   229 CREATE TABLE {{TABLE_PREFIX}}page_groups(
   221 CREATE TABLE {{TABLE_PREFIX}}page_groups(
   230   pg_id mediumint(8) NOT NULL auto_increment,
   222   pg_id mediumint(8) NOT NULL auto_increment,
   231   pg_type tinyint(2) NOT NULL DEFAULT 1,
   223   pg_type tinyint(2) NOT NULL DEFAULT 1,
   232   pg_name varchar(255) NOT NULL DEFAULT '',
   224   pg_name varchar(255) NOT NULL DEFAULT '',
   233   pg_target varchar(255) DEFAULT NULL,
   225   pg_target varchar(255) DEFAULT NULL,
   234   PRIMARY KEY ( pg_id )
   226   PRIMARY KEY ( pg_id )
   235 ) CHARACTER SET `utf8`;
   227 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   236 
   228 
   237 -- Added in 1.0.1
   229 -- Added in 1.0.1
   238 
   230 
   239 CREATE TABLE {{TABLE_PREFIX}}page_group_members(
   231 CREATE TABLE {{TABLE_PREFIX}}page_group_members(
   240   pg_member_id int(12) NOT NULL auto_increment,
   232   pg_member_id int(12) NOT NULL auto_increment,
   241   pg_id mediumint(8) NOT NULL,
   233   pg_id mediumint(8) NOT NULL,
   242   page_id varchar(63) NOT NULL,
   234   page_id varchar(63) NOT NULL,
   243   namespace varchar(63) NOT NULL DEFAULT 'Article',
   235   namespace varchar(63) NOT NULL DEFAULT 'Article',
   244   PRIMARY KEY ( pg_member_id )
   236   PRIMARY KEY ( pg_member_id )
   245 ) CHARACTER SET `utf8`;
   237 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   246 
   238 
   247 -- Added in 1.0.1
   239 -- Added in 1.0.1
   248 
   240 
   249 CREATE TABLE {{TABLE_PREFIX}}tags(
   241 CREATE TABLE {{TABLE_PREFIX}}tags(
   250   tag_id int(12) NOT NULL auto_increment,
   242   tag_id int(12) NOT NULL auto_increment,
   251   tag_name varchar(63) NOT NULL DEFAULT 'bla',
   243   tag_name varchar(63) NOT NULL DEFAULT 'bla',
   252   page_id varchar(255) NOT NULL,
   244   page_id varchar(255) NOT NULL,
   253   namespace varchar(255) NOT NULL,
   245   namespace varchar(255) NOT NULL,
   254   user mediumint(8) NOT NULL DEFAULT 1,
   246   user mediumint(8) NOT NULL DEFAULT 1,
   255   PRIMARY KEY ( tag_id )
   247   PRIMARY KEY ( tag_id )
   256 ) CHARACTER SET `utf8`;
   248 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
   257 
   249 
   258 INSERT INTO {{TABLE_PREFIX}}config(config_name, config_value) VALUES
   250 INSERT INTO {{TABLE_PREFIX}}config(config_name, config_value) VALUES
   259   ('site_name', '{{SITE_NAME}}'),
   251   ('site_name', '{{SITE_NAME}}'),
   260   ('main_page', 'Main_Page'),
   252   ('main_page', 'Main_Page'),
   261   ('site_desc', '{{SITE_DESC}}'),
   253   ('site_desc', '{{SITE_DESC}}'),