[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 $gpdb;
   8      $gp_schema = array();
   9  
  10      /*
  11      Translations
  12       - There are fields to take all the plural forms (no known language has more than 4 plural forms)
  13       - Belongs to an original string
  14       - Belongs to a user
  15       - Status can be: new, approved, unaproved, current, spam or whatever you'd like
  16      */
  17      $gp_schema['translations'] = "CREATE TABLE IF NOT EXISTS `$gpdb->translations` (
  18          `id` INT(10) NOT NULL auto_increment,
  19          `original_id` INT(10) DEFAULT NULL,
  20          `translation_set_id` INT(10) DEFAULT NULL,
  21          `translation_0` TEXT NOT NULL,
  22          `translation_1` TEXT DEFAULT NULL,
  23          `translation_2` TEXT DEFAULT NULL,
  24          `translation_3` TEXT DEFAULT NULL,
  25          `translation_4` TEXT DEFAULT NULL,
  26          `translation_5` TEXT DEFAULT NULL,
  27          `user_id` INT(10) DEFAULT NULL,
  28          `status` VARCHAR(20) NOT NULL default 'waiting',
  29          `date_added` DATETIME DEFAULT NULL,
  30          `date_modified` DATETIME DEFAULT NULL,
  31          `warnings` TEXT DEFAULT NULL,
  32          PRIMARY KEY (`id`),
  33          KEY `original_id` (`original_id`),
  34          KEY `user_id` (`user_id`),
  35          KEY `translation_set_id` (`translation_set_id`),
  36          KEY `translation_set_id_status` (`translation_set_id`,`status`),
  37          KEY `date_added` (`date_added`),
  38          KEY `warnings` (`warnings` (1))
  39      );";
  40  
  41      /*
  42      Translations sets: a translation set holds specific translation of a project in a specific language
  43      For example each WordPress Spanish translation (formal, informal and that of Diego) will be different sets.
  44      Most projects will have only one translation set per language.
  45      */
  46      $gp_schema['translation_sets'] = "CREATE TABLE IF NOT EXISTS `$gpdb->translation_sets` (
  47          `id` INT(10) NOT NULL auto_increment,
  48          `name` VARCHAR(255) NOT NULL,
  49          `slug` VARCHAR(255) NOT NULL,
  50          `project_id` INT(10) DEFAULT NULL,
  51          `locale` VARCHAR(10) DEFAULT NULL,
  52          PRIMARY KEY (`id`),
  53          UNIQUE KEY `project_id_slug_locale` (`project_id`, `slug`, `locale`)
  54      );";
  55  
  56      /*
  57      Original strings
  58       - Has many translations
  59       - Belongs to a project
  60       */
  61      $gp_schema['originals'] = "CREATE TABLE IF NOT EXISTS `$gpdb->originals` (
  62          `id` INT(10) NOT NULL auto_increment,
  63          `project_id` INT(10) DEFAULT NULL,
  64          `context` VARCHAR(255) DEFAULT NULL,
  65          `singular` TEXT NOT NULL,
  66          `plural` TEXT DEFAULT NULL,
  67          `references` TEXT DEFAULT NULL,
  68          `comment` TEXT DEFAULT NULL,
  69          `status` VARCHAR(255) NOT NULL DEFAULT '+active',
  70          `priority` TINYINT NOT NULL DEFAULT 0,
  71          `date_added` DATETIME DEFAULT NULL,
  72          PRIMARY KEY (`id`),
  73          KEY `project_id` (`project_id`)
  74      );";
  75  
  76      /*
  77      Projects
  78      - Has a project -- its parent
  79      - The path is the combination of the slugs of all its parents, separated by /
  80      */
  81      $gp_schema['projects'] = "CREATE TABLE IF NOT EXISTS `$gpdb->projects` (
  82          `id` INT(10) NOT NULL auto_increment,
  83          `name` VARCHAR(255) NOT NULL,
  84          `slug` VARCHAR(255) NOT NULL,
  85          `path` VARCHAR(255) NOT NULL,
  86          `description` TEXT NOT NULL,
  87          `parent_project_id` INT(10) DEFAULT NULL,
  88          `source_url_template` VARCHAR(255) DEFAULT '',
  89          `active` TINYINT DEFAULT 0,
  90          PRIMARY KEY (`id`),
  91          KEY `path` (`path`),
  92          KEY `parent_project_id` (`parent_project_id`)
  93      );";
  94  
  95      /*
  96      Users
  97       - Has many translations
  98       - 'user_login', 'user_nicename' and 'user_registered' indices are inconsistent with WordPress
  99      */
 100      $gp_schema['users'] = "CREATE TABLE IF NOT EXISTS `$gpdb->users` (
 101          `ID` bigINT(20) unsigned NOT NULL auto_increment,
 102          `user_login` varchar(60) NOT NULL default '',
 103          `user_pass` varchar(64) NOT NULL default '',
 104          `user_nicename` varchar(50) NOT NULL default '',
 105          `user_email` varchar(100) NOT NULL default '',
 106          `user_url` varchar(100) NOT NULL default '',
 107          `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',
 108          `user_status` INT(11) NOT NULL default 0,
 109          `display_name` varchar(250) NOT NULL default '',
 110          PRIMARY KEY (`ID`),
 111          UNIQUE KEY `user_login` (`user_login`),
 112          UNIQUE KEY `user_nicename` (`user_nicename`),
 113          KEY `user_registered` (`user_registered`)
 114      );";
 115      if ( defined('CUSTOM_USER_TABLE') && $gpdb->users == CUSTOM_USER_TABLE ) unset( $gp_schema['users'] );
 116  
 117      // usermeta
 118      $gp_schema['usermeta'] = "CREATE TABLE IF NOT EXISTS `$gpdb->usermeta` (
 119          `umeta_id` bigINT(20) NOT NULL auto_increment,
 120          `user_id` bigINT(20) NOT NULL default 0,
 121          `meta_key` varchar(255) NOT NULL,
 122          `meta_value` longTEXT NOT NULL,
 123          PRIMARY KEY (`umeta_id`),
 124          KEY `user_id` (`user_id`),
 125          KEY `meta_key` (`meta_key`)
 126      );";
 127      
 128      // meta
 129      $gp_schema['meta'] = "CREATE TABLE IF NOT EXISTS `$gpdb->meta` (
 130          `meta_id` bigint(20) NOT NULL auto_increment,
 131          `object_type` varchar(16) NOT NULL default 'gp_option',
 132          `object_id` bigint(20) NOT NULL default 0,
 133          `meta_key` varchar(255) DEFAULT NULL,
 134          `meta_value` longtext DEFAULT NULL,
 135          PRIMARY KEY (`meta_id`),
 136          KEY `object_type__meta_key` (`object_type`, `meta_key`),
 137          KEY `object_type__object_id__meta_key` (`object_type`, `object_id`, `meta_key`)
 138      );";
 139      
 140      // permissions
 141      $gp_schema['permissions'] = "CREATE TABLE IF NOT EXISTS `$gpdb->permissions` (
 142          `id` INT(10) NOT NULL AUTO_INCREMENT,
 143          `user_id` INT(10) DEFAULT NULL,
 144          `action` VARCHAR(255) DEFAULT NULL,
 145          `object_type` VARCHAR(255) DEFAULT NULL,
 146          `object_id` VARCHAR(255) DEFAULT NULL,
 147          PRIMARY KEY (`id`),
 148          KEY `user_id_action` (`user_id`,`action`)
 149      );";
 150  
 151      // API keys
 152      $gp_schema['api_keys'] = "CREATE TABLE IF NOT EXISTS `$gpdb->api_keys` (
 153          `id` INT(10) NOT NULL AUTO_INCREMENT,
 154          `user_id` INT(10) NOT NULL,
 155          `api_key` VARCHAR(16) NOT NULL,
 156          PRIMARY KEY (`id`),
 157          UNIQUE KEY `user_id` (`user_id`),
 158          UNIQUE KEY `api_key` (`api_key`)
 159      );";
 160  
 161      $gp_schema = apply_filters( 'gp_schema_pre_charset', $gp_schema );
 162  
 163      // Set the charset and collation on each table
 164      foreach ($gp_schema as $_table_name => $_sql) {
 165          // Skip SQL that isn't creating a table
 166          if (!preg_match('@^\s*CREATE\s+TABLE\s+@im', $_sql)) {
 167              continue;
 168          }
 169      
 170          // Skip if the table's database doesn't support collation
 171          if (!$gpdb->has_cap('collation', $gpdb->$_table_name)) {
 172              continue;
 173          }
 174      
 175          // Find out if the table has a custom database set
 176          if (
 177              isset($gpdb->db_tables) &&
 178              is_array($gpdb->db_tables) &&
 179              isset($gpdb->db_tables[$gpdb->$_table_name])
 180          ) {
 181              // Set the database for this table
 182              $_database = $gpdb->db_tables[$gpdb->$_table_name];
 183          } else {
 184              // Set the default global database
 185              $_database = 'dbh_global';
 186          }
 187      
 188          // Make sure the database exists
 189          if (
 190              isset($gpdb->db_servers) &&
 191              is_array($gpdb->db_servers) &&
 192              isset($gpdb->db_servers[$_database]) &&
 193              is_array($gpdb->db_servers[$_database])
 194          ) {
 195              $_charset_collate = '';
 196              if (isset($gpdb->db_servers[$_database]['charset']) && !empty($gpdb->db_servers[$_database]['charset'])) {
 197                  // Add a charset if set
 198                  $_charset_collate .= ' DEFAULT CHARACTER SET \'' . $gpdb->db_servers[$_database]['charset'] . '\'';
 199              }
 200              if (isset($gpdb->db_servers[$_database]['collate']) && !empty($gpdb->db_servers[$_database]['collate'])) {
 201                  // Add a collation if set
 202                  $_charset_collate .= ' COLLATE \'' . $gpdb->db_servers[$_database]['collate'] . '\'';
 203              }
 204              if ($_charset_collate) {
 205                  // Modify the SQL
 206                  $gp_schema[$_table_name] = str_replace(';', $_charset_collate . ';', $_sql);
 207              }            
 208          }
 209          unset($_database, $_charset_collate);
 210      }
 211      unset($_table_name, $_sql);
 212  
 213      $gp_schema = apply_filters( 'gp_schema', $gp_schema );
 214  
 215      return $gp_schema;
 216  }