1 -- $Id: mysql-initialize.sql,v 1.16 2007-08-10 17:09:26 rurban Exp $
4 id INT NOT NULL AUTO_INCREMENT,
5 -- for mysql => 4.1 define the charset here
6 -- this is esp. needed for mysql 4.1.0 up to 4.1.6.
7 -- not yet confirmed, at least since 4.1.8 it's okay with binary.
8 -- pagename VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
9 -- otherwise use the old syntax to do case-sensitive comparison
10 pagename VARCHAR(100) BINARY NOT NULL,
11 hits INT NOT NULL DEFAULT 0,
12 pagedata MEDIUMTEXT NOT NULL DEFAULT '',
13 -- dont add that by hand, better let action=upgrade convert your data
14 cached_html MEDIUMBLOB,
19 CREATE TABLE version (
23 minor_edit TINYINT DEFAULT 0,
24 content MEDIUMTEXT NOT NULL DEFAULT '',
25 versiondata MEDIUMTEXT NOT NULL DEFAULT '',
26 PRIMARY KEY (id,version),
38 CREATE TABLE nonempty (
44 linkfrom INT NOT NULL,
46 relation INT DEFAULT 0,
52 CREATE TABLE session (
53 sess_id CHAR(32) NOT NULL DEFAULT '',
54 sess_data BLOB NOT NULL,
55 sess_date INT UNSIGNED NOT NULL,
56 sess_ip CHAR(40) NOT NULL,
57 PRIMARY KEY (sess_id),
61 -- upgrade to 1.3.8: (see lib/upgrade.php)
62 -- ALTER TABLE session ADD sess_ip CHAR(15) NOT NULL;
63 -- CREATE INDEX sess_date on session (sess_date);
64 -- update to 1.3.10: (see lib/upgrade.php)
65 -- ALTER TABLE page CHANGE id id INT NOT NULL AUTO_INCREMENT;
66 -- update to 1.3.11: (see lib/upgrade.php)
67 -- ALTER TABLE page ADD cached_html MEDIUMBLOB;
68 -- ALTER TABLE session CHANGE sess_ip sess_ip CHAR(40) NOT NULL;
70 -- Optional DB Auth and Prefs
71 -- For these tables below the default table prefix must be used
72 -- in the DBAuthParam SQL statements also.
75 userid VARCHAR(48) BINARY NOT NULL UNIQUE,
76 prefs TEXT NULL DEFAULT '',
77 passwd VARCHAR(48) BINARY DEFAULT '',
78 groupname VARCHAR(48) BINARY DEFAULT 'users',
82 -- update to 1.3.12: (see lib/upgrade.php)
84 -- ALTER TABLE pref ADD passwd CHAR(48) BINARY DEFAULT '';
85 -- ALTER TABLE pref ADD groupname CHAR(48) BINARY DEFAULT 'users';
87 -- deprecated since 1.3.12. only useful for seperate databases.
88 -- better use the extra pref table where such users can be created easily
91 -- CREATE TABLE user (
92 -- userid CHAR(48) BINARY NOT NULL UNIQUE,
93 -- passwd CHAR(48) BINARY DEFAULT '',
94 -- prefs TEXT NULL DEFAULT '',
95 -- groupname CHAR(48) BINARY DEFAULT 'users',
96 -- PRIMARY KEY (userid)
99 -- Use the member table, if you need it for n:m user-group relations,
100 -- and adjust your DBAUTH_AUTH_ SQL statements.
102 CREATE TABLE member (
103 userid CHAR(48) BINARY NOT NULL,
104 groupname CHAR(48) BINARY NOT NULL DEFAULT 'users',
109 -- only if you plan to use the wikilens theme
110 CREATE TABLE rating (
111 dimension INT(4) NOT NULL,
112 raterpage INT(11) NOT NULL,
113 rateepage INT(11) NOT NULL,
114 ratingvalue FLOAT NOT NULL,
115 rateeversion INT(11) NOT NULL,
116 tstamp TIMESTAMP(14) NOT NULL,
117 PRIMARY KEY (dimension, raterpage, rateepage)
119 -- for empty dimensions use extra indices. see lib/wikilens/RatingsDb.php
120 CREATE INDEX rating_dimension ON rating (dimension);
121 CREATE INDEX rating_raterpage ON rating (raterpage);
122 CREATE INDEX rating_rateepage ON rating (rateepage);
124 -- if ACCESS_LOG_SQL > 0
125 -- only if you need fast log-analysis (spam prevention, recent referrers)
126 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
127 CREATE TABLE accesslog (
128 time_stamp INT UNSIGNED,
129 remote_host VARCHAR(100),
130 remote_user VARCHAR(50),
131 request_method VARCHAR(10),
132 request_line VARCHAR(255),
133 request_args VARCHAR(255),
134 request_file VARCHAR(255),
135 request_uri VARCHAR(255),
136 request_time CHAR(28),
137 status SMALLINT UNSIGNED,
138 bytes_sent SMALLINT UNSIGNED,
139 referer VARCHAR(255),
141 request_duration FLOAT
143 CREATE INDEX log_time ON accesslog (time_stamp);
144 CREATE INDEX log_host ON accesslog (remote_host);
145 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)
147 -- upgrade to 1.3.13: ( forgotten in lib/upgrade.php! )
148 -- ALTER TABLE accesslog CHANGE remote_host VARCHAR(100);
149 -- ALTER TABLE link ADD relation INT DEFAULT 0;
150 -- CREATE INDEX relation ON link (relation);