[r4315]: / wiki / branches / pitpalme / jamwiki-core / src / main / resources / sql / sql.hsql.properties  Maximize  Restore  History

Download this file

294 lines (293 with data), 14.8 kB

  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
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
# FIXME - need a validation query
STATEMENT_CONNECTION_VALIDATION_QUERY =
# table needs to be cached for file system storage
# use VARCHAR_IGNORECASE for username since it must be unique in a case-insensitive way
STATEMENT_CREATE_AUTHORITIES_TABLE = \
CREATE CACHED TABLE jam_authorities ( \
username VARCHAR_IGNORECASE(100) NOT NULL, \
authority VARCHAR(30) NOT NULL, \
CONSTRAINT jam_u_auth UNIQUE (username, authority), \
CONSTRAINT jam_f_auth_username FOREIGN KEY (username) REFERENCES jam_users(username), \
CONSTRAINT jam_f_auth_authority FOREIGN KEY (authority) REFERENCES jam_role(role_name) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_CATEGORY_TABLE = \
CREATE CACHED TABLE jam_category ( \
child_topic_id INTEGER NOT NULL, \
category_name VARCHAR(200) NOT NULL, \
sort_key VARCHAR(200), \
CONSTRAINT jam_p_category PRIMARY KEY (child_topic_id, category_name), \
CONSTRAINT jam_f_cat_child_id FOREIGN KEY (child_topic_id) REFERENCES jam_topic(topic_id) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_CONFIGURATION_TABLE = \
CREATE CACHED TABLE jam_configuration ( \
config_key VARCHAR(50) NOT NULL, \
config_value VARCHAR(500) NOT NULL, \
CONSTRAINT jam_p_config PRIMARY KEY (config_key) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_GROUP_AUTHORITIES_TABLE = \
CREATE CACHED TABLE jam_group_authorities ( \
group_id INTEGER NOT NULL, \
authority VARCHAR(30) NOT NULL, \
CONSTRAINT jam_u_gauth UNIQUE (group_id, authority), \
CONSTRAINT jam_f_gauth_group FOREIGN KEY (group_id) REFERENCES jam_group(group_id), \
CONSTRAINT jam_f_gauth_authority FOREIGN KEY (authority) REFERENCES jam_role(role_name) \
)
# table needs to be cached for file system storage
# use VARCHAR_IGNORECASE for group name since it must be unique in a case-insensitive way
STATEMENT_CREATE_GROUP_MEMBERS_TABLE = \
CREATE CACHED TABLE jam_group_members ( \
id INTEGER NOT NULL, \
username VARCHAR_IGNORECASE(100) NOT NULL, \
group_id INTEGER NOT NULL, \
CONSTRAINT jam_p_gmemb PRIMARY KEY (id), \
CONSTRAINT jam_f_gmemb_username FOREIGN KEY (username) REFERENCES jam_users(username), \
CONSTRAINT jam_f_gmemb_group FOREIGN KEY (group_id) REFERENCES jam_group(group_id) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_GROUP_TABLE = \
CREATE CACHED TABLE jam_group ( \
group_id INTEGER NOT NULL, \
group_name VARCHAR(30) NOT NULL, \
group_description VARCHAR(200), \
CONSTRAINT jam_p_group PRIMARY KEY (group_id), \
CONSTRAINT jam_u_group_name UNIQUE (group_name) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_INTERWIKI_TABLE = \
CREATE CACHED TABLE jam_interwiki ( \
interwiki_prefix VARCHAR(30) NOT NULL, \
interwiki_pattern VARCHAR(200) NOT NULL, \
interwiki_display VARCHAR(30) NOT NULL, \
interwiki_type INTEGER NOT NULL, \
CONSTRAINT jam_p_interw PRIMARY KEY (interwiki_prefix) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_LOG_TABLE = \
CREATE CACHED TABLE jam_log ( \
log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, \
virtual_wiki_id INTEGER NOT NULL, \
wiki_user_id INTEGER, \
display_name VARCHAR(200) NOT NULL, \
topic_id INTEGER, \
topic_version_id INTEGER, \
log_type INTEGER NOT NULL, \
log_sub_type INTEGER, \
log_comment VARCHAR(200), \
log_params VARCHAR(500), \
CONSTRAINT jam_f_log_wuser FOREIGN KEY (wiki_user_id) REFERENCES jam_wiki_user(wiki_user_id), \
CONSTRAINT jam_f_log_topic_ver FOREIGN KEY (topic_version_id) REFERENCES jam_topic_version(topic_version_id), \
CONSTRAINT jam_f_log_topic FOREIGN KEY (topic_id) REFERENCES jam_topic(topic_id), \
CONSTRAINT jam_f_log_vwiki FOREIGN KEY (virtual_wiki_id) REFERENCES jam_virtual_wiki(virtual_wiki_id) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_NAMESPACE_TABLE = \
CREATE TABLE jam_namespace ( \
namespace_id INTEGER NOT NULL, \
namespace VARCHAR(200) NOT NULL, \
main_namespace_id INTEGER, \
CONSTRAINT jam_p_namesp PRIMARY KEY (namespace_id), \
CONSTRAINT jam_u_namesp_namesp UNIQUE (namespace), \
CONSTRAINT jam_f_namesp_namesp FOREIGN KEY (main_namespace_id) REFERENCES jam_namespace(namespace_id) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_NAMESPACE_TRANSLATION_TABLE = \
CREATE TABLE jam_namespace_translation ( \
namespace_id INTEGER NOT NULL, \
virtual_wiki_id INTEGER NOT NULL, \
namespace VARCHAR(200) NOT NULL, \
CONSTRAINT jam_p_namesptr PRIMARY KEY (namespace_id, virtual_wiki_id), \
CONSTRAINT jam_f_namesptr_namesp FOREIGN KEY (namespace_id) REFERENCES jam_namespace(namespace_id), \
CONSTRAINT jam_f_namesptr_vwiki FOREIGN KEY (virtual_wiki_id) REFERENCES jam_virtual_wiki(virtual_wiki_id), \
CONSTRAINT jam_u_namesptr_namesp UNIQUE (virtual_wiki_id, namespace) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_RECENT_CHANGE_TABLE = \
CREATE CACHED TABLE jam_recent_change ( \
topic_version_id INTEGER, \
previous_topic_version_id INTEGER, \
topic_id INTEGER, \
topic_name VARCHAR(200), \
change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, \
change_comment VARCHAR(200), \
wiki_user_id INTEGER, \
display_name VARCHAR(200) NOT NULL, \
edit_type INTEGER, \
log_type INTEGER, \
log_sub_type INTEGER, \
log_params VARCHAR(500), \
virtual_wiki_id INTEGER NOT NULL, \
virtual_wiki_name VARCHAR(100) NOT NULL, \
characters_changed INTEGER, \
CONSTRAINT jam_f_rc_topic_ver FOREIGN KEY (topic_version_id) REFERENCES jam_topic_version(topic_version_id), \
CONSTRAINT jam_f_rc_p_topic_v FOREIGN KEY (previous_topic_version_id) REFERENCES jam_topic_version(topic_version_id), \
CONSTRAINT jam_f_rc_topic FOREIGN KEY (topic_id) REFERENCES jam_topic(topic_id), \
CONSTRAINT jam_f_rc_wuser FOREIGN KEY (wiki_user_id) REFERENCES jam_wiki_user(wiki_user_id), \
CONSTRAINT jam_f_rc_vwiki FOREIGN KEY (virtual_wiki_id) REFERENCES jam_virtual_wiki(virtual_wiki_id) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_ROLE_TABLE = \
CREATE CACHED TABLE jam_role ( \
role_name VARCHAR(30) NOT NULL, \
role_description VARCHAR(200), \
CONSTRAINT jam_p_role PRIMARY KEY (role_name) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_TOPIC_TABLE = \
CREATE CACHED TABLE jam_topic ( \
topic_id INTEGER NOT NULL, \
virtual_wiki_id INTEGER NOT NULL, \
namespace_id INTEGER DEFAULT 0 NOT NULL, \
topic_name VARCHAR(200) NOT NULL, \
page_name VARCHAR(200) NOT NULL, \
page_name_lower VARCHAR(200) NOT NULL, \
delete_date TIMESTAMP, \
topic_read_only INTEGER DEFAULT 0 NOT NULL, \
topic_admin_only INTEGER DEFAULT 0 NOT NULL, \
current_version_id INTEGER, \
topic_type INTEGER NOT NULL, \
redirect_to VARCHAR(200), \
CONSTRAINT jam_p_topic PRIMARY KEY (topic_id), \
CONSTRAINT jam_f_topic_vwiki FOREIGN KEY (virtual_wiki_id) REFERENCES jam_virtual_wiki(virtual_wiki_id), \
CONSTRAINT jam_f_topic_namesp FOREIGN KEY (namespace_id) REFERENCES jam_namespace(namespace_id), \
CONSTRAINT jam_u_topic_name UNIQUE (topic_name, virtual_wiki_id, delete_date) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_TOPIC_LINKS_TABLE = \
CREATE CACHED TABLE jam_topic_links ( \
topic_id INTEGER NOT NULL, \
link_topic_namespace_id INTEGER DEFAULT 0 NOT NULL, \
link_topic_page_name VARCHAR(200) NOT NULL, \
CONSTRAINT jam_p_topic_links PRIMARY KEY (topic_id, link_topic_namespace_id, link_topic_page_name), \
CONSTRAINT jam_f_tlink_namesp FOREIGN KEY (link_topic_namespace_id) REFERENCES jam_namespace(namespace_id) \
)
# table needs to be cached for file system storage
# use LONGVARCHAR since there is no TEXT type
STATEMENT_CREATE_TOPIC_VERSION_TABLE = \
CREATE CACHED TABLE jam_topic_version ( \
topic_version_id INTEGER NOT NULL, \
topic_id INTEGER NOT NULL, \
edit_comment VARCHAR(200), \
version_content LONGVARCHAR, \
wiki_user_id INTEGER, \
wiki_user_display VARCHAR(100) NOT NULL, \
edit_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, \
edit_type INTEGER NOT NULL, \
previous_topic_version_id INTEGER, \
characters_changed INTEGER, \
version_params VARCHAR(500), \
CONSTRAINT jam_p_topic_ver PRIMARY KEY (topic_version_id), \
CONSTRAINT jam_f_topicv_topic FOREIGN KEY (topic_id) REFERENCES jam_topic(topic_id), \
CONSTRAINT jam_f_topicv_wuser FOREIGN KEY (wiki_user_id) REFERENCES jam_wiki_user(wiki_user_id), \
CONSTRAINT jam_f_topicv_pver FOREIGN KEY (previous_topic_version_id) REFERENCES jam_topic_version(topic_version_id) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_USER_BLOCK_TABLE = \
CREATE CACHED TABLE jam_user_block ( \
user_block_id INTEGER NOT NULL, \
wiki_user_id INTEGER, \
ip_address VARCHAR(39), \
block_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, \
block_end_date TIMESTAMP, \
block_reason VARCHAR(200), \
blocked_by_user_id INTEGER NOT NULL, \
unblock_date TIMESTAMP, \
unblock_reason VARCHAR(200), \
unblocked_by_user_id INTEGER, \
CONSTRAINT jam_u_ublock PRIMARY KEY (user_block_id), \
CONSTRAINT jam_f_ublock_userid FOREIGN KEY (wiki_user_id) REFERENCES jam_wiki_user(wiki_user_id), \
CONSTRAINT jam_f_ublock_blkid FOREIGN KEY (blocked_by_user_id) REFERENCES jam_wiki_user(wiki_user_id), \
CONSTRAINT jam_f_ublock_unblkid FOREIGN KEY (unblocked_by_user_id) REFERENCES jam_wiki_user(wiki_user_id) \
)
# table needs to be cached for file system storage
# use VARCHAR_IGNORECASE for username since it must be unique in a case-insensitive way
STATEMENT_CREATE_USERS_TABLE = \
CREATE CACHED TABLE jam_users ( \
username VARCHAR_IGNORECASE(100) NOT NULL, \
password VARCHAR(150) NOT NULL, \
enabled INTEGER DEFAULT 1 NOT NULL, \
challenge_value varchar(100), \
challenge_date timestamp, \
challenge_ip varchar(39), \
challenge_tries integer DEFAULT 0 NOT NULL, \
CONSTRAINT jam_p_users PRIMARY KEY (username) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_VIRTUAL_WIKI_TABLE = \
CREATE CACHED TABLE jam_virtual_wiki ( \
virtual_wiki_id INTEGER NOT NULL, \
virtual_wiki_name VARCHAR(100) NOT NULL, \
default_topic_name VARCHAR(200), \
create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, \
logo_image_url VARCHAR(200), \
site_name VARCHAR(200), \
meta_description VARCHAR(500), \
CONSTRAINT jam_p_vwiki PRIMARY KEY (virtual_wiki_id), \
CONSTRAINT jam_u_vwiki_name UNIQUE (virtual_wiki_name) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_WATCHLIST_TABLE = \
CREATE CACHED TABLE jam_watchlist ( \
wiki_user_id INTEGER NOT NULL, \
topic_name VARCHAR(200), \
virtual_wiki_id INTEGER NOT NULL, \
CONSTRAINT jam_p_watchlist PRIMARY KEY (wiki_user_id, topic_name, virtual_wiki_id), \
CONSTRAINT jam_f_wlist_userid FOREIGN KEY (wiki_user_id) REFERENCES jam_wiki_user(wiki_user_id), \
CONSTRAINT jam_f_wlist_vwiki FOREIGN KEY (virtual_wiki_id) REFERENCES jam_virtual_wiki(virtual_wiki_id) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_WIKI_FILE_TABLE = \
CREATE CACHED TABLE jam_file ( \
file_id INTEGER NOT NULL, \
virtual_wiki_id INTEGER NOT NULL, \
file_name VARCHAR(200) NOT NULL, \
delete_date TIMESTAMP, \
file_read_only INTEGER DEFAULT 0 NOT NULL, \
file_admin_only INTEGER DEFAULT 0 NOT NULL, \
file_url VARCHAR(200) NOT NULL, \
mime_type VARCHAR(100) NOT NULL, \
topic_id INTEGER NOT NULL, \
file_size INTEGER NOT NULL, \
CONSTRAINT jam_p_file PRIMARY KEY (file_id), \
CONSTRAINT jam_f_file_vwiki FOREIGN KEY (virtual_wiki_id) REFERENCES jam_virtual_wiki(virtual_wiki_id), \
CONSTRAINT jam_f_file_topic FOREIGN KEY (topic_id) REFERENCES jam_topic(topic_id), \
CONSTRAINT jam_u_file_url UNIQUE (file_url), \
CONSTRAINT jam_u_file_topic UNIQUE (virtual_wiki_id, topic_id) \
)
# table needs to be cached for file system storage
STATEMENT_CREATE_WIKI_FILE_VERSION_TABLE = \
CREATE CACHED TABLE jam_file_version ( \
file_version_id INTEGER NOT NULL, \
file_id INTEGER NOT NULL, \
upload_comment VARCHAR(200), \
file_url VARCHAR(200) NOT NULL, \
wiki_user_id INTEGER, \
wiki_user_display VARCHAR(100) NOT NULL, \
upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, \
mime_type VARCHAR(100) NOT NULL, \
file_size INTEGER NOT NULL, \
CONSTRAINT jam_p_file_ver PRIMARY KEY (file_version_id), \
CONSTRAINT jam_f_filev_file FOREIGN KEY (file_id) REFERENCES jam_file(file_id), \
CONSTRAINT jam_f_filev_wuser FOREIGN KEY (wiki_user_id) REFERENCES jam_wiki_user(wiki_user_id), \
CONSTRAINT jam_u_filev_url UNIQUE (file_url) \
)
# since the login type is VARCHAR_IGNORECASE there is no need for lower(login) in the constraint
STATEMENT_CREATE_WIKI_USER_LOGIN_INDEX = \
CREATE UNIQUE INDEX jam_u_wuser_login on jam_wiki_user (login)
# table needs to be cached for file system storage
# use VARCHAR_IGNORECASE for login since it must be unique in a case-insensitive way
STATEMENT_CREATE_WIKI_USER_TABLE = \
CREATE CACHED TABLE jam_wiki_user ( \
wiki_user_id INTEGER NOT NULL, \
login VARCHAR_IGNORECASE(100) NOT NULL, \
display_name VARCHAR(100), \
create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, \
last_login_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, \
create_ip_address VARCHAR(39) NOT NULL, \
last_login_ip_address VARCHAR(39) NOT NULL, \
email VARCHAR(100), \
CONSTRAINT jam_p_wuser PRIMARY KEY (wiki_user_id), \
CONSTRAINT jam_f_wuser_users FOREIGN KEY (login) REFERENCES jam_users(username) \
)