1 -- $Id: sqlsrv-initialize.sql,v 1.5 2006/12/08 08:04:10 rurban Exp $
4 -- the CREATE FUNCTION section must be run as a seperate query. cut/paste run before executing the remainder
5 -- of the contents of this file
6 CREATE FUNCTION hasContent
7 (@ContentField varchar(max))
12 IF @ContentField NOT LIKE ''
17 -- end seperate section
22 pagename VARCHAR(100) NOT NULL,
23 hits INT NOT NULL DEFAULT 0,
24 pagedata TEXT NOT NULL DEFAULT '',
25 cached_html TEXT NOT NULL DEFAULT '', -- added with 1.3.11
29 --SET IDENTITY_INSERT page ON;
31 CREATE TABLE version (
35 minor_edit TINYINT DEFAULT 0,
36 content varchar(max) NOT NULL DEFAULT '',--can't be text
37 versiondata TEXT NOT NULL DEFAULT '',
38 PRIMARY KEY (id,version)
40 CREATE INDEX version_mtime ON version (mtime);
50 CREATE TABLE nonempty (
56 linkfrom INT NOT NULL,
60 CREATE INDEX linkfrom ON link (linkfrom);
61 CREATE INDEX linkto ON link (linkto);
63 CREATE TABLE session (
64 sess_id CHAR(32) NOT NULL DEFAULT '',
65 sess_data IMAGE NOT NULL,
66 sess_date BIGINT NOT NULL,
67 sess_ip CHAR(40) NOT NULL,
70 CREATE INDEX sessdate_index ON session (sess_date);
71 CREATE INDEX sessip_index ON session (sess_ip);
73 -- Optional DB Auth and Prefs
74 -- For these tables below the default table prefix must be used
75 -- in the DBAuthParam SQL statements also.
78 userid CHAR(48) NOT NULL,
79 prefs TEXT NULL DEFAULT '',
80 passwd CHAR(48) DEFAULT '',
81 groupname CHAR(48) DEFAULT 'users',
85 -- update to 1.3.12: (see lib/upgrade.php)
86 -- ALTER TABLE pref ADD passwd CHAR(48) BINARY DEFAULT '';
87 -- ALTER TABLE pref ADD groupname CHAR(48) BINARY DEFAULT 'users';
89 -- deprecated since 1.3.12. only useful for seperate databases.
90 -- better use the extra pref table where such users can be created easily
93 -- userid CHAR(48) NOT NULL,
94 -- passwd CHAR(48) DEFAULT '',
95 -- prefs TEXT NULL DEFAULT '',
96 -- groupname CHAR(48) DEFAULT 'users'
99 -- Use the member table, if you need it for n:m user-group relations,
100 -- and adjust your DBAUTH_AUTH_ SQL statements.
101 CREATE TABLE member (
102 userid CHAR(48) NOT NULL,
103 groupname CHAR(48) NOT NULL DEFAULT 'users'
105 CREATE INDEX member_userid ON member (userid);
106 CREATE INDEX member_groupname ON member (groupname);
108 -- only if you plan to use the wikilens theme
109 CREATE TABLE rating (
110 dimension smallINT NOT NULL,
111 raterpage INT NOT NULL,
112 rateepage INT NOT NULL,
113 ratingvalue FLOAT NOT NULL,
114 rateeversion INT NOT NULL,
115 tstamp bigint NOT NULL,
116 PRIMARY KEY (dimension, raterpage, rateepage)
118 CREATE INDEX rating_dimension ON rating (dimension);
119 CREATE INDEX rating_raterpage ON rating (raterpage);
120 CREATE INDEX rating_rateepage ON rating (rateepage);
122 -- if ACCESS_LOG_SQL > 0
123 -- only if you need fast log-analysis (spam prevention, recent referrers)
124 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
125 CREATE TABLE accesslog (
127 remote_host VARCHAR(255),
128 remote_user VARCHAR(50),
129 request_method VARCHAR(10),
134 request_time CHAR(28),
139 request_duration FLOAT
141 CREATE INDEX log_time ON accesslog (time_stamp);
142 CREATE INDEX log_host ON accesslog (remote_host);
143 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)