6 --================================================================
7 -- Prefix for table names.
9 -- You should set this to the same value you specify for
10 -- DATABASE_PREFIX in config.ini
12 -- You have to use a prefix, because some phpWiki tablenames are
13 -- Oracle reserved words!
15 define prefix=phpwiki_
17 --================================================================
19 -- Don't modify below this point unless you know what you are doing.
21 --================================================================
23 --================================================================
24 -- Note on Oracle datatypes...
26 -- Most of the 'NOT NULL' constraints on the character columns have been
27 -- dropped since they can contain empty strings which are seen by
29 -- Oracle CLOBs are used for TEXTs/MEDUIMTEXTs columns.
32 prompt Initializing PhpWiki tables with:
33 prompt prefix = &prefix
35 prompt Expect some 'ORA-00942: table or view does not exist' unless you are
36 prompt overwriting existing tables.
39 define page_tbl=&prefix.page
40 define page_id=&prefix.page_id
41 define page_nm=&prefix.page_nm
43 define version_tbl=&prefix.version
44 define vers_id=&prefix.vers_id
45 define vers_mtime=&prefix.vers_mtime
47 define recent_tbl=&prefix.recent
48 define recent_id=&prefix.recent_id
50 define nonempty_tbl=&prefix.nonempty
51 define nonmt_id=&prefix.nonmt_id
53 define link_tbl=&prefix.link
54 define link_from=&prefix.link_from
55 define link_to=&prefix.link_to
56 define link_rel=&prefix.link_rel
58 define session_tbl=&prefix.session
59 define sess_id=&prefix.sess_id
60 define sess_date=&prefix.sess_date
61 define sess_ip=&prefix.sess_ip
63 define pref_tbl=&prefix.pref
64 define pref_id=&prefix.pref_id
66 --define user_tbl=&prefix.user
67 --define user_id=&prefix.user_id
69 define member_tbl=&prefix.member
70 define member_userid=&prefix.member_userid
71 define member_groupname=&prefix.member_groupname
73 define rating_tbl=&prefix.rating
74 define rating_id=&prefix.rating_id
75 define rating_dimension=&prefix.rating_dimension
76 define rating_raterpage=&prefix.rating_raterpage
77 define rating_rateepage=&prefix.rating_rateepage
79 define accesslog_tbl=&prefix.accesslog
80 define accesslog_time=&prefix.log_time
81 define accesslog_host=&prefix.log_host
83 prompt Creating &page_tbl
84 CREATE TABLE &page_tbl (
86 pagename VARCHAR(100) NOT NULL,
87 hits INT DEFAULT 0 NOT NULL,
88 pagedata CLOB DEFAULT '',
89 cached_html CLOB DEFAULT '', -- added with 1.3.11
90 CONSTRAINT &page_id PRIMARY KEY (id),
91 CONSTRAINT &page_nm UNIQUE (pagename)
94 -- we use 0 <=> global_data to satisfy the relation = 0 constraint
95 INSERT INTO &page_tbl VALUES (0,'global_data',0,'','');
97 prompt Creating &version_tbl
98 CREATE TABLE &version_tbl (
100 version INT NOT NULL,
102 minor_edit INT DEFAULT 0,
103 content CLOB DEFAULT '',
104 versiondata CLOB DEFAULT '',
105 CONSTRAINT &vers_id PRIMARY KEY (id,version)
107 CREATE INDEX &vers_mtime ON &version_tbl (mtime);
109 prompt Creating &recent_tbl
110 CREATE TABLE &recent_tbl (
115 CONSTRAINT &recent_id PRIMARY KEY (id)
118 prompt Creating &nonempty_tbl
119 CREATE TABLE &nonempty_tbl (
121 CONSTRAINT &nonempty_tbl PRIMARY KEY (id)
124 prompt Creating &link_tbl
125 CREATE TABLE &link_tbl (
126 linkfrom INT NOT NULL,
130 CREATE INDEX &link_from ON &link_tbl (linkfrom);
131 CREATE INDEX &link_to ON &link_tbl (linkto);
132 CREATE INDEX &link_rel ON &link_tbl (relation);
134 prompt Creating &session_tbl
135 CREATE TABLE &session_tbl (
136 sess_id CHAR(32) DEFAULT '',
139 sess_ip CHAR(40) NOT NULL,
140 CONSTRAINT &sess_id PRIMARY KEY (sess_id)
142 CREATE INDEX &sess_date ON &session_tbl (sess_date);
143 CREATE INDEX &sess_ip ON &session_tbl (sess_ip);
145 -- Optional DB Auth and Prefs
146 -- For these tables below the default table prefix must be used
147 -- in the DBAuthParam SQL statements also.
149 prompt Creating &pref_tbl
150 CREATE TABLE &pref_tbl (
151 userid CHAR(48) NOT NULL,
152 prefs CLOB DEFAULT '',
153 passwd CHAR(48) DEFAULT '',
154 groupname CHAR(48) DEFAULT 'users',
155 CONSTRAINT &pref_id PRIMARY KEY (userid)
158 -- better use the extra pref table where such users can be created easily
160 --prompt Creating &user_tbl
161 --CREATE TABLE &user_tbl (
162 -- userid CHAR(48) NOT NULL,
163 -- passwd CHAR(48) DEFAULT '',
164 -- prefs CLOB DEFAULT '',
165 -- groupname CHAR(48) DEFAULT 'users',
166 -- CONSTRAINT &user_id PRIMARY KEY (userid)
169 prompt Creating &member_tbl
170 CREATE TABLE &member_tbl (
171 userid CHAR(48) NOT NULL,
172 groupname CHAR(48) DEFAULT 'users' NOT NULL
174 CREATE INDEX &member_userid ON &member_tbl (userid);
175 CREATE INDEX &member_groupname ON &member_tbl (groupname);
177 -- if you plan to use the wikilens theme
178 prompt Creating &rating_tbl
179 CREATE TABLE &rating_tbl (
180 dimension NUMBER(4) NOT NULL,
181 raterpage NUMBER(11) NOT NULL,
182 rateepage NUMBER(11) NOT NULL,
183 ratingvalue FLOAT NOT NULL,
184 rateeversion NUMBER(11) NOT NULL,
185 tstamp TIMESTAMP NOT NULL,
186 CONSTRAINT &rating_id PRIMARY KEY (dimension, raterpage, rateepage)
188 CREATE INDEX &rating_dimension ON &rating_tbl (dimension);
189 CREATE INDEX &rating_raterpage ON &rating_tbl (raterpage);
190 CREATE INDEX &rating_rateepage ON &rating_tbl (rateepage);
192 -- if ACCESS_LOG_SQL > 0
193 -- only if you need fast log-analysis (spam prevention, recent referrers)
194 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
195 prompt Creating &accesslog_tbl
196 CREATE TABLE &accesslog_tbl (
197 -- for OCI 9i+ use: time_stamp TIMESTAMP,
199 remote_host VARCHAR2(100),
200 remote_user VARCHAR2(50),
201 request_method VARCHAR2(10),
202 request_line VARCHAR2(255),
203 request_args VARCHAR2(255),
204 request_file VARCHAR2(255),
205 request_uri VARCHAR2(255),
206 request_time VARCHAR2(28),
209 referer VARCHAR(255),
211 request_duration FLOAT
213 CREATE INDEX &accesslog_time ON &accesslog_tbl (time_stamp);
214 CREATE INDEX &accesslog_host ON &accesslog_tbl (remote_host);