4 -- ================================================================
5 -- Prefix for table names.
7 -- You should set this to the same value you specify for
8 -- DATABASE_PREFIX in config.ini
10 -- You have to use a prefix, because some phpWiki tablenames are
11 -- Oracle reserved words!
13 define prefix=phpwiki_
15 -- ================================================================
17 -- Don't modify below this point unless you know what you are doing.
19 -- ================================================================
21 -- ================================================================
22 -- Note on Oracle datatypes...
24 -- Most of the 'NOT NULL' constraints on the character columns have been
25 -- dropped since they can contain empty strings which are seen by
27 -- Oracle CLOBs are used for TEXTs/MEDUIMTEXTs columns.
30 prompt Initializing PhpWiki tables with:
31 prompt prefix = &prefix
33 prompt Expect some 'ORA-00942: table or view does not exist' unless you are
34 prompt overwriting existing tables.
37 define page_tbl=&prefix.page
38 define page_id=&prefix.page_id
39 define page_nm=&prefix.page_nm
41 define version_tbl=&prefix.version
42 define vers_id=&prefix.vers_id
43 define vers_mtime=&prefix.vers_mtime
45 define recent_tbl=&prefix.recent
46 define recent_id=&prefix.recent_id
48 define nonempty_tbl=&prefix.nonempty
49 define nonmt_id=&prefix.nonmt_id
51 define link_tbl=&prefix.link
52 define link_from=&prefix.link_from
53 define link_to=&prefix.link_to
54 define link_rel=&prefix.link_rel
56 define session_tbl=&prefix.session
57 define sess_id=&prefix.sess_id
58 define sess_date=&prefix.sess_date
59 define sess_ip=&prefix.sess_ip
61 define pref_tbl=&prefix.pref
62 define pref_id=&prefix.pref_id
64 -- define user_tbl=&prefix.user
65 -- define user_id=&prefix.user_id
67 define member_tbl=&prefix.member
68 define member_userid=&prefix.member_userid
69 define member_groupname=&prefix.member_groupname
71 define rating_tbl=&prefix.rating
72 define rating_id=&prefix.rating_id
73 define rating_dimension=&prefix.rating_dimension
74 define rating_raterpage=&prefix.rating_raterpage
75 define rating_rateepage=&prefix.rating_rateepage
77 define accesslog_tbl=&prefix.accesslog
78 define accesslog_time=&prefix.log_time
79 define accesslog_host=&prefix.log_host
81 prompt Creating &page_tbl
82 CREATE TABLE &page_tbl (
84 pagename VARCHAR(100) NOT NULL,
85 hits INT DEFAULT 0 NOT NULL,
86 pagedata CLOB DEFAULT '',
87 cached_html CLOB DEFAULT '', -- added with 1.3.11
88 CONSTRAINT &page_id PRIMARY KEY (id),
89 CONSTRAINT &page_nm UNIQUE (pagename)
92 -- we use 0 <=> global_data to satisfy the relation = 0 constraint
93 INSERT INTO &page_tbl VALUES (0,'global_data',0,'','');
95 prompt Creating &version_tbl
96 CREATE TABLE &version_tbl (
100 minor_edit INT DEFAULT 0,
101 content CLOB DEFAULT '',
102 versiondata CLOB DEFAULT '',
103 CONSTRAINT &vers_id PRIMARY KEY (id,version)
105 CREATE INDEX &vers_mtime ON &version_tbl (mtime);
107 prompt Creating &recent_tbl
108 CREATE TABLE &recent_tbl (
113 CONSTRAINT &recent_id PRIMARY KEY (id)
116 prompt Creating &nonempty_tbl
117 CREATE TABLE &nonempty_tbl (
119 CONSTRAINT &nonempty_tbl PRIMARY KEY (id)
122 prompt Creating &link_tbl
123 CREATE TABLE &link_tbl (
124 linkfrom INT NOT NULL,
128 CREATE INDEX &link_from ON &link_tbl (linkfrom);
129 CREATE INDEX &link_to ON &link_tbl (linkto);
130 CREATE INDEX &link_rel ON &link_tbl (relation);
132 prompt Creating &session_tbl
133 CREATE TABLE &session_tbl (
134 sess_id CHAR(32) DEFAULT '',
137 sess_ip CHAR(40) NOT NULL,
138 CONSTRAINT &sess_id PRIMARY KEY (sess_id)
140 CREATE INDEX &sess_date ON &session_tbl (sess_date);
141 CREATE INDEX &sess_ip ON &session_tbl (sess_ip);
143 -- Optional DB Auth and Prefs
144 -- For these tables below the default table prefix must be used
145 -- in the DBAuthParam SQL statements also.
147 prompt Creating &pref_tbl
148 CREATE TABLE &pref_tbl (
149 userid CHAR(48) NOT NULL,
150 prefs CLOB DEFAULT '',
151 passwd CHAR(48) DEFAULT '',
152 groupname CHAR(48) DEFAULT 'users',
153 CONSTRAINT &pref_id PRIMARY KEY (userid)
156 -- better use the extra pref table where such users can be created easily
158 -- prompt Creating &user_tbl
159 -- CREATE TABLE &user_tbl (
160 -- userid CHAR(48) NOT NULL,
161 -- passwd CHAR(48) DEFAULT '',
162 -- prefs CLOB DEFAULT '',
163 -- groupname CHAR(48) DEFAULT 'users',
164 -- CONSTRAINT &user_id PRIMARY KEY (userid)
167 prompt Creating &member_tbl
168 CREATE TABLE &member_tbl (
169 userid CHAR(48) NOT NULL,
170 groupname CHAR(48) DEFAULT 'users' NOT NULL
172 CREATE INDEX &member_userid ON &member_tbl (userid);
173 CREATE INDEX &member_groupname ON &member_tbl (groupname);
175 -- if you plan to use the wikilens theme
176 prompt Creating &rating_tbl
177 CREATE TABLE &rating_tbl (
178 dimension NUMBER(4) NOT NULL,
179 raterpage NUMBER(11) NOT NULL,
180 rateepage NUMBER(11) NOT NULL,
181 ratingvalue FLOAT NOT NULL,
182 rateeversion NUMBER(11) NOT NULL,
183 tstamp TIMESTAMP NOT NULL,
184 CONSTRAINT &rating_id PRIMARY KEY (dimension, raterpage, rateepage)
186 CREATE INDEX &rating_dimension ON &rating_tbl (dimension);
187 CREATE INDEX &rating_raterpage ON &rating_tbl (raterpage);
188 CREATE INDEX &rating_rateepage ON &rating_tbl (rateepage);
190 -- if ACCESS_LOG_SQL > 0
191 -- only if you need fast log-analysis (spam prevention, recent referrers)
192 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
193 prompt Creating &accesslog_tbl
194 CREATE TABLE &accesslog_tbl (
195 -- for OCI 9i+ use: time_stamp TIMESTAMP,
197 remote_host VARCHAR2(100),
198 remote_user VARCHAR2(50),
199 request_method VARCHAR2(10),
200 request_line VARCHAR2(255),
201 request_args VARCHAR2(255),
202 request_file VARCHAR2(255),
203 request_uri VARCHAR2(255),
204 request_time VARCHAR2(28),
207 referer VARCHAR(255),
209 request_duration FLOAT
211 CREATE INDEX &accesslog_time ON &accesslog_tbl (time_stamp);
212 CREATE INDEX &accesslog_host ON &accesslog_tbl (remote_host);