1 -- $Id: psql-initialize.sql,v 1.3 2005-04-07 06:13:57 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'
67 \set rating_id :prefix 'rating_id'
69 \set accesslog_tbl :prefix 'accesslog'
70 \set accesslog_time :prefix 'log_time'
71 \set accesslog_host :prefix 'log_host'
73 \echo Creating :page_tbl
74 CREATE TABLE :page_tbl (
76 pagename VARCHAR(100) NOT NULL,
77 hits INT NOT NULL DEFAULT 0,
78 pagedata TEXT NOT NULL DEFAULT '',
79 cached_html TEXT DEFAULT '' -- added with 1.3.11
81 CREATE UNIQUE INDEX :page_id ON :page_tbl (id);
82 CREATE UNIQUE INDEX :page_nm ON :page_tbl (pagename);
84 \echo Creating :version_tbl
85 CREATE TABLE :version_tbl (
89 --FIXME: should use boolean, but that returns 't' or 'f'. not 0 or 1.
90 minor_edit INT2 DEFAULT 0,
91 content TEXT NOT NULL DEFAULT '',
92 versiondata TEXT NOT NULL DEFAULT ''
94 CREATE UNIQUE INDEX :vers_id ON :version_tbl (id,version);
95 CREATE INDEX :vers_mtime ON :version_tbl (mtime);
97 \echo Creating :recent_tbl
98 CREATE TABLE :recent_tbl (
104 CREATE UNIQUE INDEX :recent_id ON :recent_tbl (id);
107 \echo Creating :nonempty_tbl
108 CREATE TABLE :nonempty_tbl (
111 CREATE UNIQUE INDEX :nonmt_id
112 ON :nonempty_tbl (id);
114 \echo Creating :link_tbl
115 CREATE TABLE :link_tbl (
116 linkfrom INT NOT NULL,
119 CREATE INDEX :link_from ON :link_tbl (linkfrom);
120 CREATE INDEX :link_to ON :link_tbl (linkto);
122 \echo Creating :session_tbl
123 CREATE TABLE :session_tbl (
124 sess_id CHAR(32) NOT NULL DEFAULT '',
125 sess_data TEXT NOT NULL,
127 sess_ip CHAR(15) NOT NULL
129 CREATE UNIQUE INDEX :sess_id ON :session_tbl (sess_id);
130 CREATE INDEX :sess_date ON :session_tbl (sess_date);
131 CREATE INDEX :sess_ip ON :session_tbl (sess_ip);
133 -- Optional DB Auth and Prefs
134 -- For these tables below the default table prefix must be used
135 -- in the DBAuthParam SQL statements also.
137 \echo Creating :pref_tbl
138 CREATE TABLE :pref_tbl (
139 userid CHAR(48) NOT NULL,
140 prefs TEXT NULL DEFAULT ''
142 CREATE UNIQUE INDEX :pref_id ON :pref_tbl (userid);
144 -- Use the user and member tables - if you need them - from the other schemas
145 -- and adjust your DBAUTH_AUTH_ SQL statements
147 -- if you plan to use the wikilens theme
148 \echo Creating :rating_tbl
149 CREATE TABLE :rating_tbl (
150 dimension INTEGER NOT NULL,
151 raterpage BIGINT NOT NULL,
152 rateepage BIGINT NOT NULL,
153 ratingvalue FLOAT NOT NULL,
154 rateeversion BIGINT NOT NULL,
155 tstamp TIMESTAMP NOT NULL
157 CREATE UNIQUE INDEX :rating_id ON :rating_tbl (dimension, raterpage, rateepage);
159 -- if ACCESS_LOG_SQL > 0
160 -- only if you need fast log-analysis (spam prevention, recent referrers)
161 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
162 \echo Creating :accesslog_tbl
163 CREATE TABLE :accesslog_tbl (
165 remote_host VARCHAR(50),
166 remote_user VARCHAR(50),
167 request_method VARCHAR(10),
168 request_line VARCHAR(255),
169 request_args VARCHAR(255),
170 request_file VARCHAR(255),
171 request_uri VARCHAR(255),
172 request_time CHAR(28),
175 referer VARCHAR(255),
177 request_duration FLOAT
179 CREATE INDEX :accesslog_time ON :accesslog_tbl (time_stamp);
180 CREATE INDEX :accesslog_host ON :accesslog_tbl (remote_host);
181 -- create extra indices on demand (usually referer. see plugin/AccessLogSql)
183 GRANT SELECT,INSERT,UPDATE,DELETE ON :page_tbl TO :httpd_user;
184 GRANT SELECT,INSERT,UPDATE,DELETE ON :version_tbl TO :httpd_user;
185 GRANT SELECT,INSERT,UPDATE,DELETE ON :recent_tbl TO :httpd_user;
186 GRANT SELECT,INSERT,UPDATE,DELETE ON :nonempty_tbl TO :httpd_user;
187 GRANT SELECT,INSERT,UPDATE,DELETE ON :link_tbl TO :httpd_user;
189 GRANT SELECT,INSERT,UPDATE,DELETE ON :session_tbl TO :httpd_user;
190 -- you may want to fine tune this:
191 GRANT SELECT,INSERT,UPDATE,DELETE ON :pref_tbl TO :httpd_user;
192 -- GRANT SELECT ON :user_tbl TO :httpd_user;
193 -- GRANT SELECT ON :member_tbl TO :httpd_user;
194 GRANT SELECT,INSERT,UPDATE,DELETE ON :rating_tbl TO :httpd_user;
195 GRANT SELECT,INSERT,UPDATE,DELETE ON :accesslog_tbl TO :httpd_user;