1 -- $Id: oci8-initialize.sql,v 1.3 2005-05-04 05:08:13 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.
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
57 define session_tbl=&prefix.session
58 define sess_id=&prefix.sess_id
59 define sess_date=&prefix.sess_date
60 define sess_ip=&prefix.sess_ip
62 define pref_tbl=&prefix.pref
63 define pref_id=&prefix.pref_id
65 define user_tbl=&prefix.user
66 define user_id=&prefix.user_id
68 define member_tbl=&prefix.member
69 define member_userid=&prefix.member_userid
70 define member_groupname=&prefix.member_groupname
72 define rating_tbl=&prefix.rating
73 define rating_id=&prefix.rating_id
74 define rating_dimension=&prefix.rating_dimension
75 define rating_raterpage=&prefix.rating_raterpage
76 define rating_rateepage=&prefix.rating_rateepage
78 define accesslog_tbl=&prefix.accesslog
79 define accesslog_time=&prefix.log_time
80 define accesslog_host=&prefix.log_host
82 prompt Creating &page_tbl
83 CREATE TABLE &page_tbl (
85 pagename VARCHAR(100) NOT NULL,
86 hits INT DEFAULT 0 NOT NULL,
87 pagedata CLOB DEFAULT '',
88 cached_html CLOB DEFAULT '', -- added with 1.3.11
89 CONSTRAINT &page_id PRIMARY KEY (id),
90 CONSTRAINT &page_nm UNIQUE (pagename)
93 prompt Creating &version_tbl
94 CREATE TABLE &version_tbl (
98 minor_edit INT DEFAULT 0,
99 content CLOB DEFAULT '',
100 versiondata CLOB DEFAULT '',
101 CONSTRAINT &vers_id PRIMARY KEY (id,version)
103 CREATE INDEX &vers_mtime ON &version_tbl (mtime);
105 prompt Creating &recent_tbl
106 CREATE TABLE &recent_tbl (
111 CONSTRAINT &recent_id PRIMARY KEY (id)
114 prompt Creating &nonempty_tbl
115 CREATE TABLE &nonempty_tbl (
117 CONSTRAINT &nonempty_tbl PRIMARY KEY (id)
120 prompt Creating &link_tbl
121 CREATE TABLE &link_tbl (
122 linkfrom INT NOT NULL,
125 CREATE INDEX &link_from ON &link_tbl (linkfrom);
126 CREATE INDEX &link_to ON &link_tbl (linkto);
128 prompt Creating &session_tbl
129 CREATE TABLE &session_tbl (
130 sess_id CHAR(32) DEFAULT '',
133 sess_ip CHAR(15) NOT NULL,
134 CONSTRAINT &sess_id PRIMARY KEY (sess_id)
136 CREATE INDEX &sess_date ON &session_tbl (sess_date);
137 CREATE INDEX &sess_ip ON &session_tbl (sess_ip);
139 -- Optional DB Auth and Prefs
140 -- For these tables below the default table prefix must be used
141 -- in the DBAuthParam SQL statements also.
143 prompt Creating &pref_tbl
144 CREATE TABLE &pref_tbl (
145 userid CHAR(48) NOT NULL,
146 prefs CLOB DEFAULT '',
147 CONSTRAINT &pref_id PRIMARY KEY (userid)
150 -- better use the extra pref table where such users can be created easily
153 prompt Creating &user_tbl
154 CREATE TABLE &user_tbl (
155 userid CHAR(48) NOT NULL,
156 passwd CHAR(48) DEFAULT '',
157 -- prefs CLOB DEFAULT '',
158 -- groupname CHAR(48) DEFAULT 'users',
159 CONSTRAINT &user_id PRIMARY KEY (userid)
162 prompt Creating &member_tbl
163 CREATE TABLE &member_tbl (
164 userid CHAR(48) NOT NULL,
165 groupname CHAR(48) DEFAULT 'users' NOT NULL
167 CREATE INDEX &member_userid ON &member_tbl (userid);
168 CREATE INDEX &member_groupname ON &member_tbl (groupname);
170 -- if you plan to use the wikilens theme
171 prompt Creating &rating_tbl
172 CREATE TABLE &rating_tbl (
173 dimension NUMBER(4) NOT NULL,
174 raterpage NUMBER(11) NOT NULL,
175 rateepage NUMBER(11) NOT NULL,
176 ratingvalue FLOAT NOT NULL,
177 rateeversion NUMBER(11) NOT NULL,
178 tstamp TIMESTAMP NOT NULL,
179 CONSTRAINT &rating_id PRIMARY KEY (dimension, raterpage, rateepage)
181 CREATE INDEX &rating_dimension ON &rating_tbl (dimension);
182 CREATE INDEX &rating_raterpage ON &rating_tbl (raterpage);
183 CREATE INDEX &rating_rateepage ON &rating_tbl (rateepage);
185 -- if ACCESS_LOG_SQL > 0
186 -- only if you need fast log-analysis (spam prevention, recent referrers)
187 -- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
188 prompt Creating &accesslog_tbl
189 CREATE TABLE &accesslog_tbl (
190 -- for OCI 9i+ use: time_stamp TIMESTAMP,
192 remote_host VARCHAR2(50),
193 remote_user VARCHAR2(50),
194 request_method VARCHAR2(10),
195 request_line VARCHAR2(255),
196 request_args VARCHAR2(255),
197 request_file VARCHAR2(255),
198 request_uri VARCHAR2(255),
201 bytes_sent NUMBER(4),
202 referer VARCHAR(255),
204 request_duration FLOAT
206 CREATE INDEX &accesslog_time ON &accesslog_tbl (time_stamp);
207 CREATE INDEX &accesslog_host ON &accesslog_tbl (remote_host);