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