1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
|
-- $Id: oci8-initialize.sql,v 1.5 2005/10/12 06:08:37 rurban Exp $
set verify off
set feedback off
--================================================================
-- Prefix for table names.
--
-- You should set this to the same value you specify for
-- DATABASE_PREFIX in config.ini
--
-- You have to use a prefix, because some phpWiki tablenames are
-- Oracle reserved words!
define prefix=phpwiki_
--================================================================
--
-- Don't modify below this point unless you know what you are doing.
--
--================================================================
--================================================================
-- Note on Oracle datatypes...
--
-- Most of the 'NOT NULL' constraints on the character columns have been
-- dropped since they can contain empty strings which are seen by
-- Oracle as NULL.
-- Oracle CLOBs are used for TEXTs/MEDUIMTEXTs columns.
prompt Initializing PhpWiki tables with:
prompt prefix = &prefix
prompt
prompt Expect some 'ORA-00942: table or view does not exist' unless you are
prompt overwriting existing tables.
prompt
define page_tbl=&prefix.page
define page_id=&prefix.page_id
define page_nm=&prefix.page_nm
define version_tbl=&prefix.version
define vers_id=&prefix.vers_id
define vers_mtime=&prefix.vers_mtime
define recent_tbl=&prefix.recent
define recent_id=&prefix.recent_id
define nonempty_tbl=&prefix.nonempty
define nonmt_id=&prefix.nonmt_id
define link_tbl=&prefix.link
define link_from=&prefix.link_from
define link_to=&prefix.link_to
define session_tbl=&prefix.session
define sess_id=&prefix.sess_id
define sess_date=&prefix.sess_date
define sess_ip=&prefix.sess_ip
define pref_tbl=&prefix.pref
define pref_id=&prefix.pref_id
--define user_tbl=&prefix.user
--define user_id=&prefix.user_id
define member_tbl=&prefix.member
define member_userid=&prefix.member_userid
define member_groupname=&prefix.member_groupname
define rating_tbl=&prefix.rating
define rating_id=&prefix.rating_id
define rating_dimension=&prefix.rating_dimension
define rating_raterpage=&prefix.rating_raterpage
define rating_rateepage=&prefix.rating_rateepage
define accesslog_tbl=&prefix.accesslog
define accesslog_time=&prefix.log_time
define accesslog_host=&prefix.log_host
prompt Creating &page_tbl
CREATE TABLE &page_tbl (
id INT NOT NULL,
pagename VARCHAR(100) NOT NULL,
hits INT DEFAULT 0 NOT NULL,
pagedata CLOB DEFAULT '',
cached_html CLOB DEFAULT '', -- added with 1.3.11
CONSTRAINT &page_id PRIMARY KEY (id),
CONSTRAINT &page_nm UNIQUE (pagename)
);
prompt Creating &version_tbl
CREATE TABLE &version_tbl (
id INT NOT NULL,
version INT NOT NULL,
mtime INT NOT NULL,
minor_edit INT DEFAULT 0,
content CLOB DEFAULT '',
versiondata CLOB DEFAULT '',
CONSTRAINT &vers_id PRIMARY KEY (id,version)
);
CREATE INDEX &vers_mtime ON &version_tbl (mtime);
prompt Creating &recent_tbl
CREATE TABLE &recent_tbl (
id INT NOT NULL,
latestversion INT,
latestmajor INT,
latestminor INT,
CONSTRAINT &recent_id PRIMARY KEY (id)
);
prompt Creating &nonempty_tbl
CREATE TABLE &nonempty_tbl (
id INT NOT NULL,
CONSTRAINT &nonempty_tbl PRIMARY KEY (id)
);
prompt Creating &link_tbl
CREATE TABLE &link_tbl (
linkfrom INT NOT NULL,
linkto INT NOT NULL
);
CREATE INDEX &link_from ON &link_tbl (linkfrom);
CREATE INDEX &link_to ON &link_tbl (linkto);
prompt Creating &session_tbl
CREATE TABLE &session_tbl (
sess_id CHAR(32) DEFAULT '',
sess_data CLOB,
sess_date INT,
sess_ip CHAR(40) NOT NULL,
CONSTRAINT &sess_id PRIMARY KEY (sess_id)
);
CREATE INDEX &sess_date ON &session_tbl (sess_date);
CREATE INDEX &sess_ip ON &session_tbl (sess_ip);
-- Optional DB Auth and Prefs
-- For these tables below the default table prefix must be used
-- in the DBAuthParam SQL statements also.
prompt Creating &pref_tbl
CREATE TABLE &pref_tbl (
userid CHAR(48) NOT NULL,
prefs CLOB DEFAULT '',
passwd CHAR(48) DEFAULT '',
groupname CHAR(48) DEFAULT 'users',
CONSTRAINT &pref_id PRIMARY KEY (userid)
);
-- ALTER TABLE pref ADD passwd CHAR(48) DEFAULT '';
-- ALTER TABLE pref ADD groupname CHAR(48) DEFAULT 'users';
-- better use the extra pref table where such users can be created easily
-- without password.
--prompt Creating &user_tbl
--CREATE TABLE &user_tbl (
-- userid CHAR(48) NOT NULL,
-- prefs CLOB DEFAULT '',
-- passwd CHAR(48) DEFAULT '',
-- groupname CHAR(48) DEFAULT 'users',
-- CONSTRAINT &user_id PRIMARY KEY (userid)
--);
prompt Creating &member_tbl
CREATE TABLE &member_tbl (
userid CHAR(48) NOT NULL,
groupname CHAR(48) DEFAULT 'users' NOT NULL
);
CREATE INDEX &member_userid ON &member_tbl (userid);
CREATE INDEX &member_groupname ON &member_tbl (groupname);
-- if you plan to use the wikilens theme
prompt Creating &rating_tbl
CREATE TABLE &rating_tbl (
dimension NUMBER(4) NOT NULL,
raterpage NUMBER(11) NOT NULL,
rateepage NUMBER(11) NOT NULL,
ratingvalue FLOAT NOT NULL,
rateeversion NUMBER(11) NOT NULL,
tstamp TIMESTAMP NOT NULL,
CONSTRAINT &rating_id PRIMARY KEY (dimension, raterpage, rateepage)
);
CREATE INDEX &rating_dimension ON &rating_tbl (dimension);
CREATE INDEX &rating_raterpage ON &rating_tbl (raterpage);
CREATE INDEX &rating_rateepage ON &rating_tbl (rateepage);
-- if ACCESS_LOG_SQL > 0
-- only if you need fast log-analysis (spam prevention, recent referrers)
-- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
prompt Creating &accesslog_tbl
CREATE TABLE &accesslog_tbl (
-- for OCI 9i+ use: time_stamp TIMESTAMP,
time_stamp DATE,
remote_host VARCHAR2(50),
remote_user VARCHAR2(50),
request_method VARCHAR2(10),
request_line VARCHAR2(255),
request_args VARCHAR2(255),
request_file VARCHAR2(255),
request_uri VARCHAR2(255),
request_time DATE,
status NUMBER(4),
bytes_sent NUMBER(4),
referer VARCHAR(255),
agent VARCHAR(255),
request_duration FLOAT
);
CREATE INDEX &accesslog_time ON &accesslog_tbl (time_stamp);
CREATE INDEX &accesslog_host ON &accesslog_tbl (remote_host);
|
