[Summary view] [Print] [Text view]

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