[ Index ]

PHP Cross Reference of GlotPress

title

Body

[close]

/gp-includes/ -> schema.php (source)

   1  <?php
   2  /**
   3   * Includes the database schema definitions and comments
   4   */
   5  
   6  function gp_schema_get() {
   7      global $wpdb;
   8  
   9      $gp_schema = array();
  10  
  11      $charset_collate = '';
  12      if ( ! empty($wpdb->charset) )
  13          $charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
  14      if ( ! empty($wpdb->collate) )
  15          $charset_collate .= " COLLATE $wpdb->collate";
  16  
  17      /*
  18       * Indexes have a maximum size of 767 bytes in the MyISAM database engine. Historically, we haven't needed to be overtly
  19       * concerned about that.
  20       *
  21       * As of WordPress 4.2, however, utf8mb4 is now the default collation which uses 4 bytes per character. This means that
  22       * an index which used to have room for floor(767/3) = 255 characters, now only has room for floor(767/4) = 191 characters.
  23       *
  24       * See https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html for details on the storage requirements
  25       * for each data type.
  26       */
  27      $max_index_characters = 767;
  28      $bytes_per_character = 4;
  29      $max_index_length = floor( $max_index_characters / $bytes_per_character );
  30  
  31      /*
  32       * Translations
  33       *  - There are fields to take all the plural forms (no known locale has more than 4 plural forms)
  34       *  - Belongs to an original string
  35       *  - Belongs to a user
  36       *  - Status can be: new, approved, unapproved, current, spam or whatever you'd like
  37       */
  38      $gp_schema['translations'] = "CREATE TABLE $wpdb->gp_translations (
  39          id int(10) NOT NULL auto_increment,
  40          original_id int(10) DEFAULT NULL,
  41          translation_set_id int(10) DEFAULT NULL,
  42          translation_0 text NOT NULL,
  43          translation_1 text DEFAULT NULL,
  44          translation_2 text DEFAULT NULL,
  45          translation_3 text DEFAULT NULL,
  46          translation_4 text DEFAULT NULL,
  47          translation_5 text DEFAULT NULL,
  48          user_id bigint(20) DEFAULT NULL,
  49          user_id_last_modified bigint(20) DEFAULT NULL,
  50          status varchar(20) NOT NULL default 'waiting',
  51          date_added datetime DEFAULT NULL,
  52          date_modified datetime DEFAULT NULL,
  53          warnings text DEFAULT NULL,
  54          PRIMARY KEY  (id),
  55          KEY original_id (original_id),
  56          KEY user_id (user_id),
  57          KEY translation_set_id (translation_set_id),
  58          KEY translation_set_id_status (translation_set_id,status),
  59          KEY original_id_translation_set_id_status (original_id,translation_set_id,status),
  60          KEY date_added (date_added),
  61          KEY warnings (warnings(1))
  62      ) $charset_collate;";
  63  
  64      /*
  65       * Translations sets: A "translation set" holds all translated strings within a project for a specific locale.
  66       * For example each WordPress Spanish translation (formal, informal and that of Diego) will be different sets.
  67       * Most projects will have only one translation set per locale.
  68       */
  69  
  70      /*
  71        * The maximum length for the slug component of the project_id_slug_locale key is limited by the index size limit
  72       * minus the size of the project_id (4 bytes = 1 character) and locale (10 characters).
  73       *
  74       * Also make sure to never go over the length of the column.
  75       */
  76      $max_pid_slug_locale_key_length = min( $max_index_length - 1 - 10, 255 );
  77  
  78      /*
  79        * The maximum length for the slug component of the locale_slug key is limited by the index size limit
  80       * minus the size of the locale (10 characters).
  81       *
  82       * Also make sure to never go over the length of the column.
  83       */
  84      $max_locale_slug_key_length = min( $max_index_length - 10, 255 );
  85  
  86      $gp_schema['translation_sets'] = "CREATE TABLE $wpdb->gp_translation_sets (
  87          id int(10) NOT NULL auto_increment,
  88          name varchar(255) NOT NULL,
  89          slug varchar(255) NOT NULL,
  90          project_id int(10) DEFAULT NULL,
  91          locale varchar(10) DEFAULT NULL,
  92          PRIMARY KEY  (id),
  93          UNIQUE KEY project_id_slug_locale (project_id,slug({$max_pid_slug_locale_key_length}),locale),
  94          KEY locale_slug (locale,slug({$max_locale_slug_key_length}))
  95      ) $charset_collate;";
  96  
  97      /*
  98       * Notes
  99       */
 100      $gp_schema['notes'] = "CREATE TABLE $wpdb->gp_notes (
 101          id int(10) NOT NULL auto_increment,
 102          original_id int(10) DEFAULT NULL,
 103          translation_id int(10) DEFAULT NULL,
 104          note text NOT NULL,
 105          user_id bigint(20) DEFAULT NULL,
 106          date_added datetime DEFAULT NULL,
 107          date_modified datetime DEFAULT NULL,
 108          PRIMARY KEY  (id),
 109          KEY original_id (original_id),
 110          KEY user_id (user_id),
 111          KEY date_added (date_added)
 112      ) $charset_collate;";
 113  
 114      /*
 115       * Original strings
 116       *  - Has many translations
 117       *  - Belongs to a project
 118       *
 119       * Note that 'references' is a reserved keyword in MySQL it *MUST* be surrounded in
 120       * backticks during the creation of the table.  However during upgrades this will
 121       * cause a warning to be created in the PHP error logs about incorrect SQL syntax.
 122       *
 123       * See https://core.trac.wordpress.org/ticket/20263 for more information.
 124       *
 125       */
 126  
 127      /*
 128        * The maximum length for the components of the singular_plural_context key is limited by the index size limit
 129       * divided by three.
 130       *
 131       * Also make sure to never go over the length of the column (or in the case of a text type, a max of 255).
 132       */
 133      $max_singular_plural_context_key_length = min( floor( $max_index_length / 3 ), 255 );
 134  
 135      $gp_schema['originals'] = "CREATE TABLE $wpdb->gp_originals (
 136          id int(10) NOT NULL auto_increment,
 137          project_id int(10) DEFAULT NULL,
 138          context varchar(255) DEFAULT NULL,
 139          singular text NOT NULL,
 140          plural text DEFAULT NULL,
 141          `references` text DEFAULT NULL,
 142          comment text DEFAULT NULL,
 143          status varchar(20) NOT NULL DEFAULT '+active',
 144          priority tinyint(4) NOT NULL DEFAULT 0,
 145          date_added datetime DEFAULT NULL,
 146          PRIMARY KEY  (id),
 147          KEY project_id_status (project_id,status),
 148          KEY singular_plural_context (singular({$max_singular_plural_context_key_length}),plural({$max_singular_plural_context_key_length}),context({$max_singular_plural_context_key_length})),
 149          KEY project_id_status_priority_date_added (project_id,status,priority,date_added)
 150      ) $charset_collate;";
 151  
 152      /*
 153       * Glossary Entries
 154       */
 155      $gp_schema['glossary_entries'] = "CREATE TABLE $wpdb->gp_glossary_entries (
 156          id int(10) unsigned NOT NULL auto_increment,
 157          glossary_id int(10) unsigned NOT NULL,
 158          term varchar(255) NOT NULL,
 159          part_of_speech varchar(255) DEFAULT NULL,
 160          comment text DEFAULT NULL,
 161          translation varchar(255) DEFAULT NULL,
 162          date_modified datetime NOT NULL,
 163          last_edited_by bigint(20) NOT NULL,
 164          PRIMARY KEY  (id)
 165      ) $charset_collate;";
 166  
 167      /*
 168       * Glossaries
 169       */
 170      $gp_schema['glossaries'] = "CREATE TABLE $wpdb->gp_glossaries (
 171          id int(10) unsigned NOT NULL auto_increment,
 172          translation_set_id int(10)  NOT NULL,
 173          description text DEFAULT NULL,
 174          PRIMARY KEY  (id)
 175      ) $charset_collate;";
 176  
 177      /*
 178       * Projects
 179       * - Has a project -- its parent
 180       * - The path is the combination of the slugs of all its parents, separated by /
 181       */
 182      $gp_schema['projects'] = "CREATE TABLE $wpdb->gp_projects (
 183          id int(10) NOT NULL auto_increment,
 184          name varchar(255) NOT NULL,
 185          slug varchar(255) NOT NULL,
 186          path varchar(255) NOT NULL,
 187          description text NOT NULL,
 188          parent_project_id int(10) DEFAULT NULL,
 189          source_url_template varchar(255) DEFAULT '',
 190          active tinyint(4) DEFAULT 0,
 191          PRIMARY KEY  (id),
 192          KEY path (path),
 193          KEY parent_project_id (parent_project_id)
 194      ) $charset_collate;";
 195  
 196      /*
 197       * Meta
 198       */
 199  
 200      /*
 201        * The maximum length for the meta_key component of the object_type__meta_key key is limited by the index size limit
 202       * minus the size of the object_type (32 characters).
 203       *
 204       * Also make sure to never go over the length of the column.
 205       */
 206      $max_objtype_metakey_key_length = min( $max_index_length - 32, 255 );
 207  
 208      /*
 209        * The maximum length for the meta_key component of the object_type__object_id__meta_key key is limited by the index size limit
 210       * minus the size of the object_type (32 characters) and the object_id (8 bytes = 2 characters).
 211       *
 212       * Also make sure to never go over the length of the column.
 213       */
 214      $max_objtype_objid_metakey_key_length = min( $max_index_length - 32 - 2, 255 );
 215  
 216      $gp_schema['meta'] = "CREATE TABLE $wpdb->gp_meta (
 217          meta_id bigint(20) NOT NULL auto_increment,
 218          object_type varchar(32) NOT NULL default 'gp_option',
 219          object_id bigint(20) NOT NULL default 0,
 220          meta_key varchar(255) DEFAULT NULL,
 221          meta_value longtext DEFAULT NULL,
 222          PRIMARY KEY  (meta_id),
 223          KEY object_type__meta_key (object_type,meta_key({$max_objtype_metakey_key_length})),
 224          KEY object_type__object_id__meta_key (object_type,object_id,meta_key({$max_objtype_objid_metakey_key_length}))
 225      ) $charset_collate;";
 226  
 227      /*
 228       * Permissions
 229       */
 230      $gp_schema['permissions'] = "CREATE TABLE $wpdb->gp_permissions (
 231          id int(10) NOT NULL AUTO_INCREMENT,
 232          user_id bigint(20) DEFAULT NULL,
 233          action varchar(60) DEFAULT NULL,
 234          object_type varchar(255) DEFAULT NULL,
 235          object_id varchar(255) DEFAULT NULL,
 236          PRIMARY KEY  (id),
 237          KEY user_id_action (user_id,action)
 238      ) $charset_collate;";
 239  
 240      /**
 241       * Filter the GlotPress database schema.
 242       *
 243       * @since 1.0.0
 244       *
 245       * @param array $gp_schema Schema definitions in SQL, table names without prefixes as keys.
 246       */
 247      $gp_schema = apply_filters( 'gp_schema', $gp_schema );
 248  
 249      return $gp_schema;
 250  }


Generated: Wed Jun 26 01:02:08 2019 Cross-referenced by PHPXref 0.7.1