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