1 -- $Id: psql-initialize.sql,v 1.2 2005-02-27 09:33:05 rurban Exp $
6 --================================================================
7 -- Prefix for table names.
9 -- You should set this to the same value you specify for
10 -- $DBParams['prefix'] in index.php.
14 --================================================================
15 -- Which postgres user gets access to the tables?
17 -- You should set this to the name of the postgres
18 -- user who will be accessing the tables.
20 -- Commonly, connections from php are made under
21 -- the user name of 'nobody', 'apache' or 'www'.
23 \set httpd_user 'rurban'
25 --================================================================
27 -- Don't modify below this point unless you know what you are doing.
29 --================================================================
31 \set qprefix '\'' :prefix '\''
32 \set qhttp_user '\'' :httpd_user '\''
33 \echo Initializing PhpWiki tables with:
34 \echo ' prefix = ' :qprefix
35 \echo ' httpd_user = ' :qhttp_user
37 \echo 'Expect some \'Relation \'*\' does not exists\' errors unless you are'
38 \echo 'overwriting existing tables.'
40 \set page_tbl :prefix 'page'
41 \set page_id :prefix 'page_id'
42 \set page_nm :prefix 'page_nm'
44 \set version_tbl :prefix 'version'
45 \set vers_id :prefix 'vers_id'
46 \set vers_mtime :prefix 'vers_mtime'
48 \set recent_tbl :prefix 'recent'
49 \set recent_id :prefix 'recent_id'
51 \set nonempty_tbl :prefix 'nonempty'
52 \set nonmt_id :prefix 'nonmt_id'
54 \set link_tbl :prefix 'link'
55 \set link_from :prefix 'link_from'
56 \set link_to :prefix 'link_to'
58 \set session_tbl :prefix 'session'
59 \set sess_id :prefix 'sess_id'
60 \set sess_date :prefix 'sess_date'
61 \set sess_ip :prefix 'sess_ip'
63 \set pref_tbl :prefix 'pref'
64 \set pref_id :prefix 'pref_id'
66 \set rating_tbl :prefix 'rating'
68 \set accesslog_tbl :prefix 'accesslog'
69 \set accesslog_time :prefix 'log_time'
70 \set accesslog_host :prefix 'log_host'
72 \echo Creating :page_tbl
73 CREATE TABLE :page_tbl (
75 pagename VARCHAR(100) NOT NULL,
76 hits INT NOT NULL DEFAULT 0,
77 pagedata TEXT NOT NULL DEFAULT '',
78 cached_html TEXT DEFAULT '' -- added with 1.3.11
80 CREATE UNIQUE INDEX :page_id ON :page_tbl (id);
81 CREATE UNIQUE INDEX :page_nm ON :page_tbl (pagename);
83 \echo Creating :version_tbl
84 CREATE TABLE :version_tbl (
88 --FIXME: should use boolean, but that returns 't' or 'f'. not 0 or 1.
89 minor_edit INT2 DEFAULT 0,
90 content TEXT NOT NULL DEFAULT '',
91 versiondata TEXT NOT NULL DEFAULT ''
93 CREATE UNIQUE INDEX :vers_id ON :version_tbl (id,version);
94 CREATE INDEX :vers_mtime ON :version_tbl (mtime);
96 \echo Creating :recent_tbl
97 CREATE TABLE :recent_tbl (
103 CREATE UNIQUE INDEX :recent_id ON :recent_tbl (id);
106 \echo Creating :nonempty_tbl
107 CREATE TABLE :nonempty_tbl (
110 CREATE UNIQUE INDEX :nonmt_id
111 ON :nonempty_tbl (id);
113 \echo Creating :link_tbl
114 CREATE TABLE :link_tbl (
115 linkfrom INT NOT NULL,
118 CREATE INDEX :link_from ON :link_tbl (linkfrom);
119 CREATE INDEX :link_to ON :link_tbl (linkto);
121 \echo Creating :session_tbl
122 CREATE TABLE :session_tbl (
123 sess_id CHAR(32) NOT NULL DEFAULT '',
124 sess_data TEXT NOT NULL,
126 sess_ip CHAR(15) NOT NULL
128 CREATE UNIQUE INDEX :sess_id ON :session_tbl (sess_id);
129 CREATE INDEX :sess_date ON :session_tbl (sess_date);
130 CREATE INDEX :sess_ip ON :session_tbl (sess_ip);
132 -- Optional DB Auth and Prefs
133 -- For these tables below the default table prefix must be used
134 -- in the DBAuthParam SQL statements also.
136 \echo Creating :pref_tbl
137 CREATE TABLE :pref_tbl (
138 userid CHAR(48) NOT NULL,
139 prefs TEXT NULL DEFAULT ''
141 CREATE UNIQUE INDEX :pref_id ON :pref_tbl (userid);
143 -- Use the user and member tables - if you need them - from the other schemas
144 -- and adjust your DBAUTH_AUTH_ SQL statements
146 -- if you plan to use the wikilens theme
147 \echo Creating :rating_tbl
148 CREATE TABLE :rating_tbl (
149 dimension NUMBER(4) NOT NULL,
150 raterpage NUMBER(11) NOT NULL,
151 rateepage NUMBER(11) NOT NULL,
152 ratingvalue FLOAT NOT NULL,
153 rateeversion NUMBER(11) NOT NULL,
154 tstamp TIMESTAMP NOT NULL
156 CREATE UNIQUE INDEX :rating_id ON :rating_tbl (dimension, raterpage, rateepage);
158 -- if ACCESS_LOG_SQL > 0
159 -- only if you need fast log-analysis (spam prevention, recent referrers)
160 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
161 \echo Creating :accesslog_tbl
162 CREATE TABLE :accesslog_tbl (
163 time_stamp INT UNSIGNED,
164 remote_host VARCHAR(50),
165 remote_user VARCHAR(50),
166 request_method VARCHAR(10),
167 request_line VARCHAR(255),
168 request_args VARCHAR(255),
169 request_file VARCHAR(255),
170 request_uri VARCHAR(255),
171 request_time CHAR(28),
172 status SMALLINT UNSIGNED,
173 bytes_sent SMALLINT UNSIGNED,
174 referer VARCHAR(255),
176 request_duration FLOAT
178 CREATE INDEX :accesslog_time ON :accesslog_tbl (time_stamp);
179 CREATE INDEX :accesslog_host ON :accesslog_tbl (remote_host);
180 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)
182 GRANT SELECT,INSERT,UPDATE,DELETE ON :page_tbl TO :httpd_user;
183 GRANT SELECT,INSERT,UPDATE,DELETE ON :version_tbl TO :httpd_user;
184 GRANT SELECT,INSERT,UPDATE,DELETE ON :recent_tbl TO :httpd_user;
185 GRANT SELECT,INSERT,UPDATE,DELETE ON :nonempty_tbl TO :httpd_user;
186 GRANT SELECT,INSERT,UPDATE,DELETE ON :link_tbl TO :httpd_user;
188 GRANT SELECT,INSERT,UPDATE,DELETE ON :session_tbl TO :httpd_user;
189 -- you may want to fine tune this:
190 GRANT SELECT,INSERT,UPDATE,DELETE ON :pref_tbl TO :httpd_user;
191 -- GRANT SELECT ON :user_tbl TO :httpd_user;
192 -- GRANT SELECT ON :member_tbl TO :httpd_user;
193 GRANT SELECT,INSERT,UPDATE,DELETE ON :rating_tbl TO :httpd_user;
194 GRANT SELECT,INSERT,UPDATE,DELETE ON :accesslog_tbl TO :httpd_user;