typo3/sysext/core/ext_tables.sql

   1 #
   2 # Table structure for table 'be_groups'
   3 #
   4 CREATE TABLE be_groups (
   5         title varchar(50) DEFAULT '' NOT NULL,
   6         non_exclude_fields text,
   7         explicit_allowdeny text,
   8         allowed_languages varchar(255) DEFAULT '' NOT NULL,
   9         custom_options text,
  10         db_mountpoints text,
  11         pagetypes_select varchar(255) DEFAULT '' NOT NULL,
  12         tables_select text,
  13         tables_modify text,
  14         groupMods text,
  15         file_mountpoints text,
  16         file_permissions text,
  17         lockToDomain varchar(50) DEFAULT '' NOT NULL,
  18         TSconfig text,
  19         subgroup text,
  20         workspace_perms tinyint(3) DEFAULT '1' NOT NULL,
  21         category_perms text
  22 );
  23
  24 #
  25 # Table structure for table 'be_sessions'
  26 #
  27 CREATE TABLE be_sessions (
  28         ses_id varchar(32) DEFAULT '' NOT NULL,
  29         ses_iplock varchar(39) DEFAULT '' NOT NULL,
  30         ses_userid int(11) unsigned DEFAULT '0' NOT NULL,
  31         ses_tstamp int(11) unsigned DEFAULT '0' NOT NULL,
  32         ses_data longblob,
  33         ses_backuserid int(11) NOT NULL default '0',
  34         PRIMARY KEY (ses_id),
  35         KEY ses_tstamp (ses_tstamp)
  36 );
  37
  38 #
  39 # Table structure for table 'be_users'
  40 #
  41 CREATE TABLE be_users (
  42         username varchar(50) DEFAULT '' NOT NULL,
  43         avatar int(11) unsigned NOT NULL default '0',
  44         password varchar(100) DEFAULT '' NOT NULL,
  45         admin tinyint(4) unsigned DEFAULT '0' NOT NULL,
  46         usergroup varchar(255) DEFAULT '' NOT NULL,
  47         lang varchar(6) DEFAULT '' NOT NULL,
  48         email varchar(255) DEFAULT '' NOT NULL,
  49         db_mountpoints text,
  50         options tinyint(4) unsigned DEFAULT '0' NOT NULL,
  51         realName varchar(80) DEFAULT '' NOT NULL,
  52         userMods text,
  53         allowed_languages varchar(255) DEFAULT '' NOT NULL,
  54         uc mediumblob,
  55         file_mountpoints text,
  56         file_permissions text,
  57         workspace_perms tinyint(3) DEFAULT '1' NOT NULL,
  58         lockToDomain varchar(50) DEFAULT '' NOT NULL,
  59         disableIPlock tinyint(1) unsigned DEFAULT '0' NOT NULL,
  60         TSconfig text,
  61         lastlogin int(10) unsigned DEFAULT '0' NOT NULL,
  62         createdByAction int(11) DEFAULT '0' NOT NULL,
  63         usergroup_cached_list text,
  64         workspace_id int(11) DEFAULT '0' NOT NULL,
  65         category_perms text,
  66         KEY username (username)
  67 );
  68
  69 #
  70 # Table structure for table 'pages'
  71 #
  72 CREATE TABLE pages (
  73         perms_userid int(11) unsigned DEFAULT '0' NOT NULL,
  74         perms_groupid int(11) unsigned DEFAULT '0' NOT NULL,
  75         perms_user tinyint(4) unsigned DEFAULT '0' NOT NULL,
  76         perms_group tinyint(4) unsigned DEFAULT '0' NOT NULL,
  77         perms_everybody tinyint(4) unsigned DEFAULT '0' NOT NULL,
  78         title varchar(255) DEFAULT '' NOT NULL,
  79         doktype int(11) unsigned DEFAULT '0' NOT NULL,
  80         TSconfig text,
  81         is_siteroot tinyint(4) DEFAULT '0' NOT NULL,
  82         php_tree_stop tinyint(4) DEFAULT '0' NOT NULL,
  83         url varchar(255) DEFAULT '' NOT NULL,
  84         shortcut int(10) unsigned DEFAULT '0' NOT NULL,
  85         shortcut_mode int(10) unsigned DEFAULT '0' NOT NULL,
  86         subtitle varchar(255) DEFAULT '' NOT NULL,
  87         layout int(11) unsigned DEFAULT '0' NOT NULL,
  88         target varchar(80) DEFAULT '' NOT NULL,
  89         media int(11) unsigned DEFAULT '0' NOT NULL,
  90         lastUpdated int(10) unsigned DEFAULT '0' NOT NULL,
  91         keywords text,
  92         cache_timeout int(10) unsigned DEFAULT '0' NOT NULL,
  93         cache_tags varchar(255) DEFAULT '' NOT NULL,
  94         newUntil int(10) unsigned DEFAULT '0' NOT NULL,
  95         description text,
  96         no_search tinyint(3) unsigned DEFAULT '0' NOT NULL,
  97         SYS_LASTCHANGED int(10) unsigned DEFAULT '0' NOT NULL,
  98         abstract text,
  99         module varchar(255) DEFAULT '' NOT NULL,
 100         extendToSubpages tinyint(3) unsigned DEFAULT '0' NOT NULL,
 101         author varchar(255) DEFAULT '' NOT NULL,
 102         author_email varchar(255) DEFAULT '' NOT NULL,
 103         nav_title varchar(255) DEFAULT '' NOT NULL,
 104         nav_hide tinyint(4) DEFAULT '0' NOT NULL,
 105         content_from_pid int(10) unsigned DEFAULT '0' NOT NULL,
 106         mount_pid int(10) unsigned DEFAULT '0' NOT NULL,
 107         mount_pid_ol tinyint(4) DEFAULT '0' NOT NULL,
 108         alias varchar(32) DEFAULT '' NOT NULL,
 109         l18n_cfg tinyint(4) DEFAULT '0' NOT NULL,
 110         fe_login_mode tinyint(4) DEFAULT '0' NOT NULL,
 111         backend_layout varchar(64) DEFAULT '' NOT NULL,
 112         backend_layout_next_level varchar(64) DEFAULT '' NOT NULL,
 113         tsconfig_includes text,
 114         legacy_overlay_uid int(11) unsigned DEFAULT '0' NOT NULL,
 115
 116         KEY alias (alias),
 117         KEY determineSiteRoot (is_siteroot),
 118         KEY language_identifier (l10n_parent,sys_language_uid)
 119 );
 120
 121 #
 122 # Table structure for table 'sys_registry'
 123 #
 124 CREATE TABLE sys_registry (
 125         uid int(11) unsigned NOT NULL auto_increment,
 126         entry_namespace varchar(128) DEFAULT '' NOT NULL,
 127         entry_key varchar(128) DEFAULT '' NOT NULL,
 128         entry_value mediumblob,
 129         PRIMARY KEY (uid),
 130         UNIQUE KEY entry_identifier (entry_namespace,entry_key)
 131 );
 132
 133 #
 134 # Table structure for table 'sys_be_shortcuts'
 135 #
 136 CREATE TABLE sys_be_shortcuts (
 137         uid int(11) unsigned NOT NULL auto_increment,
 138         userid int(11) unsigned DEFAULT '0' NOT NULL,
 139         module_name varchar(255) DEFAULT '' NOT NULL,
 140         url text,
 141         description varchar(255) DEFAULT '' NOT NULL,
 142         sorting int(11) DEFAULT '0' NOT NULL,
 143         sc_group tinyint(4) DEFAULT '0' NOT NULL,
 144         PRIMARY KEY (uid),
 145         KEY event (userid)
 146 );
 147
 148
 149 #
 150 # Table structure for table 'sys_news'
 151 #
 152 CREATE TABLE sys_news (
 153         title varchar(255) DEFAULT '' NOT NULL,
 154         content mediumtext
 155 );
 156
 157
 158 #
 159 # Table structure for table 'sys_filemounts'
 160 #
 161 CREATE TABLE sys_filemounts (
 162         title varchar(255) DEFAULT '' NOT NULL,
 163         path varchar(255) DEFAULT '' NOT NULL,
 164         base int(11) unsigned DEFAULT '0' NOT NULL,
 165         read_only tinyint(1) unsigned DEFAULT '0' NOT NULL
 166 );
 167
 168
 169 #
 170 # Table structure for table 'sys_file_storage'
 171 #
 172 CREATE TABLE sys_file_storage (
 173         name varchar(30) DEFAULT '' NOT NULL,
 174         driver tinytext,
 175         configuration text,
 176         is_default tinyint(4) DEFAULT '0' NOT NULL,
 177         is_browsable tinyint(4) DEFAULT '0' NOT NULL,
 178         is_public tinyint(4) DEFAULT '0' NOT NULL,
 179         is_writable tinyint(4) DEFAULT '0' NOT NULL,
 180         is_online tinyint(4) DEFAULT '1' NOT NULL,
 181         auto_extract_metadata tinyint(4) DEFAULT '1' NOT NULL,
 182         processingfolder tinytext
 183 );
 184
 185 #
 186 # Table structure for table 'sys_file'
 187 #
 188 CREATE TABLE sys_file (
 189         last_indexed int(11) DEFAULT '0' NOT NULL,
 190
 191         # management information
 192         missing tinyint(4) DEFAULT '0' NOT NULL,
 193         storage int(11) DEFAULT '0' NOT NULL,
 194         type varchar(10) DEFAULT '' NOT NULL,
 195         metadata int(11) DEFAULT '0' NOT NULL,
 196
 197         # file info data
 198         identifier text,
 199         identifier_hash char(40) DEFAULT '' NOT NULL,
 200         folder_hash char(40) DEFAULT '' NOT NULL,
 201         extension varchar(255) DEFAULT '' NOT NULL,
 202         mime_type varchar(255) DEFAULT '' NOT NULL,
 203         name tinytext,
 204         sha1 char(40) DEFAULT '' NOT NULL,
 205         size bigint(20) unsigned DEFAULT '0' NOT NULL,
 206         creation_date int(11) DEFAULT '0' NOT NULL,
 207         modification_date int(11) DEFAULT '0' NOT NULL,
 208
 209         KEY sel01 (storage,identifier_hash),
 210         KEY folder (storage,folder_hash),
 211         KEY tstamp (tstamp),
 212         KEY lastindex (last_indexed),
 213         KEY sha1 (sha1)
 214 );
 215
 216 #
 217 # Table structure for table 'sys_file_metadata'
 218 #
 219 CREATE TABLE sys_file_metadata (
 220         file int(11) DEFAULT '0' NOT NULL,
 221         title tinytext,
 222         width int(11) DEFAULT '0' NOT NULL,
 223         height int(11) DEFAULT '0' NOT NULL,
 224         description text,
 225         alternative text,
 226
 227         KEY file (file),
 228         KEY fal_filelist (l10n_parent,sys_language_uid)
 229 );
 230
 231
 232 #
 233 # Table structure for table 'sys_file_processedfile'.
 234 # which is a "temporary" file, like an image preview
 235 # This table does not have a TCA representation, as it is only written
 236 # to using direct SQL queries in the code
 237 #
 238 CREATE TABLE sys_file_processedfile (
 239         uid int(11) NOT NULL auto_increment,
 240         tstamp int(11) DEFAULT '0' NOT NULL,
 241         crdate int(11) DEFAULT '0' NOT NULL,
 242
 243         storage int(11) DEFAULT '0' NOT NULL,
 244         original int(11) DEFAULT '0' NOT NULL,
 245         identifier varchar(512) DEFAULT '' NOT NULL,
 246         name tinytext,
 247         configuration text,
 248         configurationsha1 char(40) DEFAULT '' NOT NULL,
 249         originalfilesha1 char(40) DEFAULT '' NOT NULL,
 250         task_type varchar(200) DEFAULT '' NOT NULL,
 251         checksum char(10) DEFAULT '' NOT NULL,
 252         width int(11) DEFAULT '0',
 253         height int(11) DEFAULT '0',
 254
 255         PRIMARY KEY (uid),
 256         KEY combined_1 (original,task_type,configurationsha1),
 257         KEY identifier (storage,identifier(199))
 258 );
 259
 260 #
 261 # Table structure for table 'sys_file_reference'
 262 # which is one usage of a file with overloaded metadata
 263 #
 264 CREATE TABLE sys_file_reference (
 265         # Reference fields (basically same as MM table)
 266         uid_local int(11) DEFAULT '0' NOT NULL,
 267         uid_foreign int(11) DEFAULT '0' NOT NULL,
 268         tablenames varchar(64) DEFAULT '' NOT NULL,
 269         fieldname varchar(64) DEFAULT '' NOT NULL,
 270         sorting_foreign int(11) DEFAULT '0' NOT NULL,
 271         table_local varchar(64) DEFAULT '' NOT NULL,
 272
 273         # Local usage overlay fields
 274         title tinytext,
 275         description text,
 276         alternative text,
 277         link varchar(1024) DEFAULT '' NOT NULL,
 278         crop varchar(4000) DEFAULT '' NOT NULL,
 279         autoplay tinyint(4) DEFAULT '0' NOT NULL,
 280
 281         KEY tablenames_fieldname (tablenames(32),fieldname(12)),
 282         KEY deleted (deleted),
 283         KEY uid_local (uid_local),
 284         KEY uid_foreign (uid_foreign),
 285         KEY combined_1 (l10n_parent, t3ver_oid, t3ver_wsid, t3ver_state, deleted)
 286 );
 287
 288
 289 #
 290 # Table structure for table 'sys_file_collection'
 291 #
 292 CREATE TABLE sys_file_collection (
 293         title tinytext,
 294         type varchar(30) DEFAULT 'static' NOT NULL,
 295
 296         # for type=static
 297         files int(11) DEFAULT '0' NOT NULL,
 298
 299         # for type=folder:
 300         storage int(11) DEFAULT '0' NOT NULL,
 301         folder text,
 302         recursive tinyint(4) DEFAULT '0' NOT NULL,
 303
 304         # for type=category:
 305         category int(11) DEFAULT '0' NOT NULL
 306 );
 307
 308 #
 309 # Table structure for table 'sys_collection'
 310 #
 311 CREATE TABLE sys_collection (
 312         title tinytext,
 313         type varchar(32) DEFAULT 'static' NOT NULL,
 314         table_name tinytext,
 315         items int(11) DEFAULT '0' NOT NULL
 316 );
 317
 318 #
 319 # Table structure for table 'sys_collection_entries'
 320 #
 321 CREATE TABLE sys_collection_entries (
 322         uid int(11) NOT NULL auto_increment,
 323         uid_local int(11) DEFAULT '0' NOT NULL,
 324         uid_foreign int(11) DEFAULT '0' NOT NULL,
 325         tablenames varchar(64) DEFAULT '' NOT NULL,
 326         sorting int(11) DEFAULT '0' NOT NULL,
 327
 328         KEY uid_local (uid_local),
 329         KEY uid_foreign (uid_foreign),
 330         PRIMARY KEY (uid)
 331 );
 332
 333 #
 334 # Table structure for table 'sys_history'
 335 #
 336 CREATE TABLE sys_history (
 337         actiontype tinyint(3) DEFAULT '0' NOT NULL,
 338         usertype varchar(2) DEFAULT 'BE' NOT NULL,
 339         userid int(11) unsigned,
 340         originaluserid int(11) unsigned,
 341         recuid int(11) DEFAULT '0' NOT NULL,
 342         tablename varchar(255) DEFAULT '' NOT NULL,
 343         history_data mediumtext,
 344         workspace int(11) DEFAULT '0',
 345
 346         KEY recordident_1 (tablename,recuid),
 347         KEY recordident_2 (tablename,tstamp)
 348 ) ENGINE=InnoDB;
 349
 350 #
 351 # Table structure for table 'sys_lockedrecords'
 352 #
 353 CREATE TABLE sys_lockedrecords (
 354         uid int(11) unsigned NOT NULL auto_increment,
 355         userid int(11) unsigned DEFAULT '0' NOT NULL,
 356         tstamp int(11) unsigned DEFAULT '0' NOT NULL,
 357         record_table varchar(255) DEFAULT '' NOT NULL,
 358         record_uid int(11) DEFAULT '0' NOT NULL,
 359         record_pid int(11) DEFAULT '0' NOT NULL,
 360         username varchar(50) DEFAULT '' NOT NULL,
 361         feuserid int(11) unsigned DEFAULT '0' NOT NULL,
 362         PRIMARY KEY (uid),
 363         KEY event (userid,tstamp)
 364 );
 365
 366 #
 367 # Table structure for table 'sys_refindex'
 368 #
 369 CREATE TABLE sys_refindex (
 370         hash varchar(32) DEFAULT '' NOT NULL,
 371         tablename varchar(255) DEFAULT '' NOT NULL,
 372         recuid int(11) DEFAULT '0' NOT NULL,
 373         field varchar(64) DEFAULT '' NOT NULL,
 374         flexpointer varchar(255) DEFAULT '' NOT NULL,
 375         softref_key varchar(30) DEFAULT '' NOT NULL,
 376         softref_id varchar(40) DEFAULT '' NOT NULL,
 377         sorting int(11) DEFAULT '0' NOT NULL,
 378         deleted tinyint(1) DEFAULT '0' NOT NULL,
 379         workspace int(11) DEFAULT '0' NOT NULL,
 380         ref_table varchar(255) DEFAULT '' NOT NULL,
 381         ref_uid int(11) DEFAULT '0' NOT NULL,
 382         ref_string varchar(1024) DEFAULT '' NOT NULL,
 383
 384         PRIMARY KEY (hash),
 385         KEY lookup_rec (tablename(240),recuid),
 386         KEY lookup_uid (ref_table(240),ref_uid),
 387         KEY lookup_string (ref_string(255))
 388 );
 389
 390 #
 391 # Table structure for table 'sys_log'
 392 #
 393 CREATE TABLE sys_log (
 394         userid int(11) unsigned DEFAULT '0' NOT NULL,
 395         action tinyint(4) unsigned DEFAULT '0' NOT NULL,
 396         recuid int(11) unsigned DEFAULT '0' NOT NULL,
 397         tablename varchar(255) DEFAULT '' NOT NULL,
 398         recpid int(11) DEFAULT '0' NOT NULL,
 399         error tinyint(4) unsigned DEFAULT '0' NOT NULL,
 400         details text,
 401         type tinyint(3) unsigned DEFAULT '0' NOT NULL,
 402         details_nr tinyint(3) DEFAULT '0' NOT NULL,
 403         IP varchar(39) DEFAULT '' NOT NULL,
 404         log_data text,
 405         event_pid int(11) DEFAULT '-1' NOT NULL,
 406         workspace int(11) DEFAULT '0' NOT NULL,
 407         NEWid varchar(30) DEFAULT '' NOT NULL,
 408         request_id varchar(13) DEFAULT '' NOT NULL,
 409         time_micro float DEFAULT '0' NOT NULL,
 410         component varchar(255) DEFAULT '' NOT NULL,
 411         level tinyint(1) unsigned DEFAULT '0' NOT NULL,
 412         message text,
 413         data text,
 414         KEY event (userid,event_pid),
 415         KEY recuidIdx (recuid),
 416         KEY user_auth (type,action,tstamp),
 417         KEY request (request_id),
 418         KEY combined_1 (tstamp, type, userid)
 419 ) ENGINE=InnoDB;
 420
 421 #
 422 # Table structure for table 'sys_language'
 423 #
 424 CREATE TABLE sys_language (
 425         title varchar(80) DEFAULT '' NOT NULL,
 426         flag varchar(20) DEFAULT '' NOT NULL,
 427         language_isocode varchar(2) DEFAULT '' NOT NULL,
 428         static_lang_isocode int(11) unsigned DEFAULT '0' NOT NULL
 429 );
 430
 431 #
 432 # Table structure for table 'sys_category'
 433 #
 434 CREATE TABLE sys_category (
 435         title tinytext NOT NULL,
 436         parent int(11) DEFAULT '0' NOT NULL,
 437         items int(11) DEFAULT '0' NOT NULL,
 438
 439         KEY category_parent (parent),
 440         KEY category_list (pid,deleted,sys_language_uid)
 441 );
 442
 443 #
 444 # Table structure for table 'sys_category_record_mm'
 445 #
 446 CREATE TABLE sys_category_record_mm (
 447         uid_local int(11) DEFAULT '0' NOT NULL,
 448         uid_foreign int(11) DEFAULT '0' NOT NULL,
 449         tablenames varchar(255) DEFAULT '' NOT NULL,
 450         fieldname varchar(255) DEFAULT '' NOT NULL,
 451         sorting int(11) DEFAULT '0' NOT NULL,
 452         sorting_foreign int(11) DEFAULT '0' NOT NULL,
 453
 454         KEY uid_local_foreign (uid_local,uid_foreign),
 455         KEY uid_foreign_tablefield (uid_foreign,tablenames(40),fieldname(3),sorting_foreign)
 456 );