1 -- $Id: psql.sql,v 1.7 2004-04-15 16:42:07 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 \echo Dropping :page_tbl
68 \echo Creating :page_tbl
69 CREATE TABLE :page_tbl (
71 pagename VARCHAR(100) NOT NULL,
72 hits INT NOT NULL DEFAULT 0,
73 pagedata TEXT NOT NULL DEFAULT ''
75 CREATE UNIQUE INDEX :page_id ON :page_tbl (id);
76 CREATE UNIQUE INDEX :page_nm ON :page_tbl (pagename);
78 \echo Dropping :version_tbl
79 DROP TABLE :version_tbl;
80 \echo Creating :version_tbl
81 CREATE TABLE :version_tbl (
85 --FIXME: should use boolean, but that returns 't' or 'f'. not 0 or 1.
86 minor_edit INT2 DEFAULT 0,
87 content TEXT NOT NULL DEFAULT '',
88 versiondata TEXT NOT NULL DEFAULT ''
90 CREATE UNIQUE INDEX :vers_id ON :version_tbl (id,version);
91 CREATE INDEX :vers_mtime ON :version_tbl (mtime);
93 \echo Dropping :recent_tbl
94 DROP TABLE :recent_tbl;
95 \echo Creating :recent_tbl
96 CREATE TABLE :recent_tbl (
102 CREATE UNIQUE INDEX :recent_id ON :recent_tbl (id);
105 \echo Dropping :nonempty_tbl
106 DROP TABLE :nonempty_tbl;
107 \echo Creating :nonempty_tbl
108 CREATE TABLE :nonempty_tbl (
111 CREATE UNIQUE INDEX :nonmt_id
112 ON :nonempty_tbl (id);
114 \echo Dropping :link_tbl
115 DROP TABLE :link_tbl;
116 \echo Creating :link_tbl
117 CREATE TABLE :link_tbl (
118 linkfrom INT NOT NULL,
121 CREATE INDEX :link_from ON :link_tbl (linkfrom);
122 CREATE INDEX :link_to ON :link_tbl (linkto);
124 \echo Dropping :session_tbl
125 DROP TABLE :session_tbl;
126 \echo Creating :session_tbl
127 CREATE TABLE :session_tbl (
128 sess_id CHAR(32) NOT NULL DEFAULT '',
129 sess_data TEXT NOT NULL,
131 sess_ip CHAR(15) NOT NULL
133 CREATE UNIQUE INDEX :sess_id ON :session_tbl (sess_id);
134 CREATE INDEX :sess_date ON :session_tbl (sess_date);
135 CREATE INDEX :sess_ip ON :session_tbl (sess_ip);
137 -- Optional DB Auth and Prefs
138 -- For these tables below the default table prefix must be used
139 -- in the DBAuthParam SQL statements also.
141 \echo Dropping :pref_tbl
142 DROP TABLE :pref_tbl;
143 \echo Creating :pref_tbl
144 CREATE TABLE :pref_tbl (
145 userid CHAR(48) NOT NULL,
146 prefs TEXT NULL DEFAULT ''
148 CREATE UNIQUE INDEX :pref_id ON :pref_tbl (userid);
150 GRANT ALL ON :page_tbl TO :httpd_user;
151 GRANT ALL ON :version_tbl TO :httpd_user;
152 GRANT ALL ON :recent_tbl TO :httpd_user;
153 GRANT ALL ON :nonempty_tbl TO :httpd_user;
154 GRANT ALL ON :link_tbl TO :httpd_user;
155 GRANT ALL ON :session_tbl TO :httpd_user;
156 GRANT ALL ON :pref_tbl TO :httpd_user;