Fixed bug #11505: TYPO3 4.3 is slower than 4.2 -> re-implement native db caching...
[Packages/TYPO3.CMS.git] / t3lib / stddb / tables.sql
1 #
2 # TYPO3 SVN ID: $Id$
3 #
4
5 #
6 # Table structure for table 'be_groups'
7 #
8 CREATE TABLE be_groups (
9 uid int(11) unsigned NOT NULL auto_increment,
10 pid int(11) unsigned DEFAULT '0' NOT NULL,
11 tstamp int(11) unsigned DEFAULT '0' NOT NULL,
12 title varchar(50) DEFAULT '' NOT NULL,
13 non_exclude_fields text,
14 explicit_allowdeny text,
15 allowed_languages varchar(255) DEFAULT '' NOT NULL,
16 custom_options text,
17 db_mountpoints varchar(255) DEFAULT '' NOT NULL,
18 pagetypes_select varchar(255) DEFAULT '' NOT NULL,
19 tables_select text,
20 tables_modify text,
21 crdate int(11) unsigned DEFAULT '0' NOT NULL,
22 cruser_id int(11) unsigned DEFAULT '0' NOT NULL,
23 groupMods text,
24 file_mountpoints varchar(255) DEFAULT '' NOT NULL,
25 fileoper_perms tinyint(4) DEFAULT '0' NOT NULL,
26 hidden tinyint(1) unsigned DEFAULT '0' NOT NULL,
27 inc_access_lists tinyint(3) unsigned DEFAULT '0' NOT NULL,
28 description text,
29 lockToDomain varchar(50) DEFAULT '' NOT NULL,
30 deleted tinyint(1) unsigned DEFAULT '0' NOT NULL,
31 TSconfig text,
32 subgroup varchar(255) DEFAULT '' NOT NULL,
33 hide_in_lists tinyint(4) DEFAULT '0' NOT NULL,
34 workspace_perms tinyint(3) DEFAULT '1' NOT NULL,
35 PRIMARY KEY (uid),
36 KEY parent (pid)
37 );
38
39 #
40 # Table structure for table 'be_sessions'
41 #
42 CREATE TABLE be_sessions (
43 ses_id varchar(32) DEFAULT '' NOT NULL,
44 ses_name varchar(32) DEFAULT '' NOT NULL,
45 ses_iplock varchar(39) DEFAULT '' NOT NULL,
46 ses_hashlock int(11) DEFAULT '0' NOT NULL,
47 ses_userid int(11) unsigned DEFAULT '0' NOT NULL,
48 ses_tstamp int(11) unsigned DEFAULT '0' NOT NULL,
49 ses_data longtext,
50 ses_backuserid int(11) NOT NULL default '0',
51 PRIMARY KEY (ses_id,ses_name)
52 );
53
54 #
55 # Table structure for table 'be_users'
56 #
57 CREATE TABLE be_users (
58 uid int(11) unsigned NOT NULL auto_increment,
59 pid int(11) unsigned DEFAULT '0' NOT NULL,
60 tstamp int(11) unsigned DEFAULT '0' NOT NULL,
61 username varchar(50) DEFAULT '' NOT NULL,
62 password varchar(40) DEFAULT '' NOT NULL,
63 admin tinyint(4) unsigned DEFAULT '0' NOT NULL,
64 usergroup varchar(255) DEFAULT '' NOT NULL,
65 disable tinyint(1) unsigned DEFAULT '0' NOT NULL,
66 starttime int(11) unsigned DEFAULT '0' NOT NULL,
67 endtime int(11) unsigned DEFAULT '0' NOT NULL,
68 lang char(2) DEFAULT '' NOT NULL,
69 email varchar(80) DEFAULT '' NOT NULL,
70 db_mountpoints varchar(255) DEFAULT '' NOT NULL,
71 options tinyint(4) unsigned DEFAULT '0' NOT NULL,
72 crdate int(11) unsigned DEFAULT '0' NOT NULL,
73 cruser_id int(11) unsigned DEFAULT '0' NOT NULL,
74 realName varchar(80) DEFAULT '' NOT NULL,
75 userMods varchar(255) DEFAULT '' NOT NULL,
76 allowed_languages varchar(255) DEFAULT '' NOT NULL,
77 uc text,
78 file_mountpoints varchar(255) DEFAULT '' NOT NULL,
79 fileoper_perms tinyint(4) DEFAULT '0' NOT NULL,
80 workspace_perms tinyint(3) DEFAULT '1' NOT NULL,
81 lockToDomain varchar(50) DEFAULT '' NOT NULL,
82 disableIPlock tinyint(1) unsigned DEFAULT '0' NOT NULL,
83 deleted tinyint(1) unsigned DEFAULT '0' NOT NULL,
84 TSconfig text,
85 lastlogin int(10) unsigned DEFAULT '0' NOT NULL,
86 createdByAction int(11) DEFAULT '0' NOT NULL,
87 usergroup_cached_list varchar(255) DEFAULT '' NOT NULL,
88 workspace_id int(11) DEFAULT '0' NOT NULL,
89 workspace_preview tinyint(3) DEFAULT '1' NOT NULL,
90 PRIMARY KEY (uid),
91 KEY parent (pid),
92 KEY username (username)
93 );
94
95 #
96 # Table structure for table 'cache_extensions'
97 #
98 CREATE TABLE cache_extensions (
99 extkey varchar(60) NOT NULL default '',
100 version varchar(10) NOT NULL default '',
101 alldownloadcounter int(11) unsigned NOT NULL default '0',
102 downloadcounter int(11) unsigned NOT NULL default '0',
103 title varchar(150) NOT NULL default '',
104 description mediumtext,
105 state int(4) NOT NULL default '0',
106 reviewstate int(4) NOT NULL default '0',
107 category int(4) NOT NULL default '0',
108 lastuploaddate int(11) unsigned NOT NULL default '0',
109 dependencies mediumtext,
110 authorname varchar(100) NOT NULL default '',
111 authoremail varchar(100) NOT NULL default '',
112 ownerusername varchar(50) NOT NULL default '',
113 t3xfilemd5 varchar(35) NOT NULL default '',
114 uploadcomment mediumtext,
115 authorcompany varchar(100) NOT NULL default '',
116 intversion int(11) NOT NULL default '0',
117 lastversion int(3) NOT NULL default '0',
118 lastreviewedversion int(3) NOT NULL default '0',
119 PRIMARY KEY (extkey,version)
120 );
121
122 #
123 # Table structure for table 'cache_hash'
124 #
125 CREATE TABLE cache_hash (
126 id int(11) unsigned NOT NULL auto_increment,
127 hash varchar(32) DEFAULT '' NOT NULL,
128 content mediumtext,
129 tstamp int(11) unsigned DEFAULT '0' NOT NULL,
130 ident varchar(32) DEFAULT '' NOT NULL,
131 PRIMARY KEY (id)
132 KEY hash (hash)
133 ) ENGINE=InnoDB;
134
135
136 #
137 # Table structure for table 'cachingframework_cache_hash'
138 #
139 CREATE TABLE cachingframework_cache_hash (
140 id int(11) unsigned NOT NULL auto_increment,
141 identifier varchar(250) DEFAULT '' NOT NULL,
142 crdate int(11) unsigned DEFAULT '0' NOT NULL,
143 content mediumtext,
144 tags mediumtext,
145 lifetime int(11) unsigned DEFAULT '0' NOT NULL,
146 PRIMARY KEY (id),
147 KEY cache_id (identifier)
148 ) ENGINE=InnoDB;
149
150 #
151 # Table structure for table 'cache_imagesizes'
152 #
153 CREATE TABLE cache_imagesizes (
154 md5hash varchar(32) DEFAULT '' NOT NULL,
155 md5filename varchar(32) DEFAULT '' NOT NULL,
156 tstamp int(11) DEFAULT '0' NOT NULL,
157 filename varchar(255) DEFAULT '' NOT NULL,
158 imagewidth mediumint(11) unsigned DEFAULT '0' NOT NULL,
159 imageheight mediumint(11) unsigned DEFAULT '0' NOT NULL,
160 PRIMARY KEY (md5filename)
161 ) ENGINE=InnoDB;
162
163 #
164 # Table structure for table 'pages'
165 #
166 CREATE TABLE pages (
167 uid int(11) NOT NULL auto_increment,
168 pid int(11) DEFAULT '0' NOT NULL,
169 t3ver_oid int(11) DEFAULT '0' NOT NULL,
170 t3ver_id int(11) DEFAULT '0' NOT NULL,
171 t3ver_wsid int(11) DEFAULT '0' NOT NULL,
172 t3ver_label varchar(255) DEFAULT '' NOT NULL,
173 t3ver_state tinyint(4) DEFAULT '0' NOT NULL,
174 t3ver_stage tinyint(4) DEFAULT '0' NOT NULL,
175 t3ver_count int(11) DEFAULT '0' NOT NULL,
176 t3ver_tstamp int(11) DEFAULT '0' NOT NULL,
177 t3ver_swapmode tinyint(4) DEFAULT '0' NOT NULL,
178 t3ver_move_id int(11) DEFAULT '0' NOT NULL,
179 t3_origuid int(11) DEFAULT '0' NOT NULL,
180 tstamp int(11) unsigned DEFAULT '0' NOT NULL,
181 sorting int(11) unsigned DEFAULT '0' NOT NULL,
182 deleted tinyint(1) unsigned DEFAULT '0' NOT NULL,
183 perms_userid int(11) unsigned DEFAULT '0' NOT NULL,
184 perms_groupid int(11) unsigned DEFAULT '0' NOT NULL,
185 perms_user tinyint(4) unsigned DEFAULT '0' NOT NULL,
186 perms_group tinyint(4) unsigned DEFAULT '0' NOT NULL,
187 perms_everybody tinyint(4) unsigned DEFAULT '0' NOT NULL,
188 editlock tinyint(4) unsigned DEFAULT '0' NOT NULL,
189 crdate int(11) unsigned DEFAULT '0' NOT NULL,
190 cruser_id int(11) unsigned DEFAULT '0' NOT NULL,
191 hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,
192 title varchar(255) DEFAULT '' NOT NULL,
193 doktype tinyint(3) unsigned DEFAULT '0' NOT NULL,
194 TSconfig text,
195 storage_pid int(11) DEFAULT '0' NOT NULL,
196 is_siteroot tinyint(4) DEFAULT '0' NOT NULL,
197 php_tree_stop tinyint(4) DEFAULT '0' NOT NULL,
198 tx_impexp_origuid int(11) DEFAULT '0' NOT NULL,
199 PRIMARY KEY (uid),
200 KEY t3ver_oid (t3ver_oid,t3ver_wsid),
201 KEY parent (pid,sorting,deleted,hidden)
202 );
203
204 #
205 # Table structure for table 'sys_registry'
206 #
207 CREATE TABLE sys_registry (
208 uid int(11) unsigned NOT NULL auto_increment,
209 entry_namespace varchar(128) DEFAULT '' NOT NULL,
210 entry_key varchar(128) DEFAULT '' NOT NULL,
211 entry_value blob,
212 PRIMARY KEY (uid),
213 UNIQUE KEY entry_identifier (entry_namespace,entry_key)
214 );
215
216 #
217 # Table structure for table 'sys_be_shortcuts'
218 #
219 CREATE TABLE sys_be_shortcuts (
220 uid int(11) unsigned NOT NULL auto_increment,
221 userid int(11) unsigned DEFAULT '0' NOT NULL,
222 module_name varchar(255) DEFAULT '' NOT NULL,
223 url text,
224 description varchar(255) DEFAULT '' NOT NULL,
225 sorting int(11) DEFAULT '0' NOT NULL,
226 sc_group tinyint(4) DEFAULT '0' NOT NULL,
227 PRIMARY KEY (uid),
228 KEY event (userid)
229 );
230
231
232 #
233 # Table structure for table 'sys_preview'
234 #
235 CREATE TABLE sys_preview (
236 keyword varchar(32) DEFAULT '' NOT NULL,
237 tstamp int(11) DEFAULT '0' NOT NULL,
238 endtime int(11) DEFAULT '0' NOT NULL,
239 config text,
240 PRIMARY KEY (keyword)
241 );
242
243
244 #
245 # Table structure for table 'sys_filemounts'
246 #
247 CREATE TABLE sys_filemounts (
248 uid int(11) unsigned NOT NULL auto_increment,
249 pid int(11) unsigned DEFAULT '0' NOT NULL,
250 tstamp int(11) unsigned DEFAULT '0' NOT NULL,
251 title varchar(30) DEFAULT '' NOT NULL,
252 path varchar(120) DEFAULT '' NOT NULL,
253 base tinyint(4) unsigned DEFAULT '0' NOT NULL,
254 hidden tinyint(3) unsigned DEFAULT '0' NOT NULL,
255 deleted tinyint(1) unsigned DEFAULT '0' NOT NULL,
256 PRIMARY KEY (uid),
257 KEY parent (pid)
258 );
259
260 #
261 # Table structure for table 'sys_workspace'
262 #
263 CREATE TABLE sys_workspace (
264 uid int(11) NOT NULL auto_increment,
265 pid int(11) DEFAULT '0' NOT NULL,
266 tstamp int(11) DEFAULT '0' NOT NULL,
267 deleted tinyint(1) DEFAULT '0' NOT NULL,
268 title varchar(30) DEFAULT '' NOT NULL,
269 description varchar(255) DEFAULT '' NOT NULL,
270 adminusers varchar(255) DEFAULT '' NOT NULL,
271 members text,
272 reviewers text,
273 db_mountpoints varchar(255) DEFAULT '' NOT NULL,
274 file_mountpoints varchar(255) DEFAULT '' NOT NULL,
275 publish_time int(11) DEFAULT '0' NOT NULL,
276 unpublish_time int(11) DEFAULT '0' NOT NULL,
277 freeze tinyint(3) DEFAULT '0' NOT NULL,
278 live_edit tinyint(3) DEFAULT '0' NOT NULL,
279 review_stage_edit tinyint(3) DEFAULT '0' NOT NULL,
280 vtypes tinyint(3) DEFAULT '0' NOT NULL,
281 disable_autocreate tinyint(1) DEFAULT '0' NOT NULL,
282 swap_modes tinyint(3) DEFAULT '0' NOT NULL,
283 publish_access tinyint(3) DEFAULT '0' NOT NULL,
284 stagechg_notification tinyint(3) DEFAULT '0' NOT NULL,
285
286 PRIMARY KEY (uid),
287 KEY parent (pid)
288 );
289
290 #
291 # Table structure for table 'sys_history'
292 #
293 CREATE TABLE sys_history (
294 uid int(11) unsigned NOT NULL auto_increment,
295 sys_log_uid int(11) DEFAULT '0' NOT NULL,
296 history_data mediumtext,
297 fieldlist text,
298 recuid int(11) DEFAULT '0' NOT NULL,
299 tablename varchar(255) DEFAULT '' NOT NULL,
300 tstamp int(11) DEFAULT '0' NOT NULL,
301 history_files mediumtext,
302 snapshot tinyint(4) DEFAULT '0' NOT NULL,
303 PRIMARY KEY (uid),
304 KEY recordident (tablename,recuid,tstamp),
305 KEY sys_log_uid (sys_log_uid)
306 );
307
308 #
309 # Table structure for table 'sys_lockedrecords'
310 #
311 CREATE TABLE sys_lockedrecords (
312 uid int(11) unsigned NOT NULL auto_increment,
313 userid int(11) unsigned DEFAULT '0' NOT NULL,
314 tstamp int(11) unsigned DEFAULT '0' NOT NULL,
315 record_table varchar(255) DEFAULT '' NOT NULL,
316 record_uid int(11) DEFAULT '0' NOT NULL,
317 record_pid int(11) DEFAULT '0' NOT NULL,
318 username varchar(20) DEFAULT '' NOT NULL,
319 feuserid int(11) unsigned DEFAULT '0' NOT NULL,
320 PRIMARY KEY (uid),
321 KEY event (userid,tstamp)
322 );
323
324 #
325 # Table structure for table 'sys_refindex'
326 #
327 CREATE TABLE sys_refindex (
328 hash varchar(32) DEFAULT '' NOT NULL,
329 tablename varchar(255) DEFAULT '' NOT NULL,
330 recuid int(11) DEFAULT '0' NOT NULL,
331 field varchar(40) DEFAULT '' NOT NULL,
332 flexpointer varchar(255) DEFAULT '' NOT NULL,
333 softref_key varchar(30) DEFAULT '' NOT NULL,
334 softref_id varchar(40) DEFAULT '' NOT NULL,
335 sorting int(11) DEFAULT '0' NOT NULL,
336 deleted tinyint(1) DEFAULT '0' NOT NULL,
337 ref_table varchar(255) DEFAULT '' NOT NULL,
338 ref_uid int(11) DEFAULT '0' NOT NULL,
339 ref_string varchar(200) DEFAULT '' NOT NULL,
340
341 PRIMARY KEY (hash),
342 KEY lookup_rec (tablename,recuid),
343 KEY lookup_uid (ref_table,ref_uid),
344 KEY lookup_string (ref_table,ref_string)
345 );
346
347 #
348 # Table structure for table 'sys_refindex_words'
349 #
350 CREATE TABLE sys_refindex_words (
351 wid int(11) DEFAULT '0' NOT NULL,
352 baseword varchar(60) DEFAULT '' NOT NULL,
353 PRIMARY KEY (wid)
354 );
355
356 #
357 # Table structure for table 'sys_refindex_rel'
358 #
359 CREATE TABLE sys_refindex_rel (
360 rid int(11) DEFAULT '0' NOT NULL,
361 wid int(11) DEFAULT '0' NOT NULL,
362 PRIMARY KEY (rid,wid)
363 );
364
365
366 #
367 # Table structure for table 'sys_refindex_res'
368 #
369 CREATE TABLE sys_refindex_res (
370 rid int(11) DEFAULT '0' NOT NULL,
371 tablename varchar(255) DEFAULT '' NOT NULL,
372 recuid int(11) DEFAULT '0' NOT NULL,
373 PRIMARY KEY (rid)
374 );
375
376 #
377 # Table structure for table 'sys_log'
378 #
379 CREATE TABLE sys_log (
380 uid int(11) unsigned NOT NULL auto_increment,
381 userid int(11) unsigned DEFAULT '0' NOT NULL,
382 action tinyint(4) unsigned DEFAULT '0' NOT NULL,
383 recuid int(11) unsigned DEFAULT '0' NOT NULL,
384 tablename varchar(255) DEFAULT '' NOT NULL,
385 recpid int(11) DEFAULT '0' NOT NULL,
386 error tinyint(4) unsigned DEFAULT '0' NOT NULL,
387 details varchar(255) DEFAULT '' NOT NULL,
388 tstamp int(11) unsigned DEFAULT '0' NOT NULL,
389 type tinyint(3) unsigned DEFAULT '0' NOT NULL,
390 details_nr tinyint(3) unsigned DEFAULT '0' NOT NULL,
391 IP varchar(39) DEFAULT '' NOT NULL,
392 log_data varchar(255) DEFAULT '' NOT NULL,
393 event_pid int(11) DEFAULT '-1' NOT NULL,
394 workspace int(11) DEFAULT '0' NOT NULL,
395 NEWid varchar(20) DEFAULT '' NOT NULL,
396 PRIMARY KEY (uid),
397 KEY event (userid,event_pid),
398 KEY recuidIdx (recuid,uid)
399 ) ENGINE=InnoDB;
400
401 #
402 # Table structure for table 'sys_language'
403 #
404 CREATE TABLE sys_language (
405 uid int(11) unsigned NOT NULL auto_increment,
406 pid int(11) unsigned DEFAULT '0' NOT NULL,
407 tstamp int(11) unsigned DEFAULT '0' NOT NULL,
408 hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,
409 title varchar(80) DEFAULT '' NOT NULL,
410 flag varchar(20) DEFAULT '' NOT NULL,
411 static_lang_isocode int(11) unsigned DEFAULT '0' NOT NULL,
412 PRIMARY KEY (uid),
413 KEY parent (pid)
414 );