1 -- http://www.hezmatt.org/~mpalmer/sqlite-phpwiki/sqlite.sql
3 -- $Id: sqlite-initialize.sql,v 1.6 2006-12-08 08:04:10 rurban Exp $
6 id INTEGER PRIMARY KEY,
7 pagename VARCHAR(100) NOT NULL,
8 hits INTEGER NOT NULL DEFAULT 0,
9 pagedata MEDIUMTEXT NOT NULL DEFAULT '',
10 cached_html MEDIUMTEXT -- added with 1.3.11
12 CREATE UNIQUE INDEX page_index ON page (pagename);
14 CREATE TABLE version (
16 version INTEGER NOT NULL,
17 mtime INTEGER NOT NULL,
18 minor_edit TINYINTEGER DEFAULT 0,
19 content MEDIUMTEXT NOT NULL DEFAULT '',
20 versiondata MEDIUMTEXT NOT NULL DEFAULT '',
21 PRIMARY KEY (id,version)
23 CREATE INDEX version_index ON version (mtime);
26 id INTEGER NOT NULL PRIMARY KEY,
27 latestversion INTEGER,
32 CREATE TABLE nonempty (
35 CREATE INDEX nonempty_index ON nonempty (id);
38 linkfrom INTEGER NOT NULL,
39 linkto INTEGER NOT NULL
41 CREATE INDEX linkfrom_index ON link (linkfrom);
42 CREATE INDEX linkto_index ON link (linkto);
44 CREATE TABLE session (
45 sess_id CHAR(32) NOT NULL DEFAULT '' PRIMARY KEY,
46 sess_data MEDIUMTEXT NOT NULL,
47 sess_date INTEGER UNSIGNED NOT NULL,
48 sess_ip CHAR(40) NOT NULL
50 CREATE INDEX sessdate_index ON session (sess_date);
51 CREATE INDEX sessip_index ON session (sess_ip);
53 -- Optional DB Auth and Prefs
54 -- For these tables below the default table prefix must be used
55 -- in the DBAuthParam SQL statements also.
58 userid CHAR(48) NOT NULL PRIMARY KEY,
59 prefs MEDIUMTEXT NULL DEFAULT '',
60 passwd CHAR(48) DEFAULT '',
61 groupname CHAR(48) DEFAULT 'users',
64 -- Use the member table, if you need it for n:m user-group relations,
65 -- and adjust your DBAUTH_AUTH_ SQL statements.
67 userid CHAR(48) NOT NULL,
68 groupname CHAR(48) NOT NULL DEFAULT 'users'
70 CREATE INDEX member_userid ON member (userid);
71 CREATE INDEX member_groupname ON member (groupname);
73 -- only if you plan to use the wikilens theme
75 dimension TINYINTEGER NOT NULL,
76 raterpage INTEGER NOT NULL,
77 rateepage INTEGER NOT NULL,
78 ratingvalue FLOAT NOT NULL,
79 rateeversion INTEGER NOT NULL,
80 tstamp INTEGER UNSIGNED NOT NULL,
81 PRIMARY KEY (dimension, raterpage, rateepage)
83 CREATE INDEX rating_dimension ON rating (dimension);
84 CREATE INDEX rating_raterpage ON rating (raterpage);
85 CREATE INDEX rating_rateepage ON rating (rateepage);
87 -- if ACCESS_LOG_SQL > 0
88 -- only if you need fast log-analysis (spam prevention, recent referrers)
89 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
90 CREATE TABLE accesslog (
91 time_stamp INTEGER UNSIGNED,
92 remote_host VARCHAR(100),
93 remote_user VARCHAR(50),
94 request_method VARCHAR(10),
95 request_line VARCHAR(255),
96 request_args VARCHAR(255),
97 request_file VARCHAR(255),
98 request_uri VARCHAR(255),
99 request_time CHAR(28),
100 status TINYINTEGER UNSIGNED,
101 bytes_sent TINYINTEGER UNSIGNED,
102 referer VARCHAR(255),
104 request_duration FLOAT
106 CREATE INDEX log_time ON accesslog (time_stamp);
107 CREATE INDEX log_host ON accesslog (remote_host);
108 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)