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