schema.inc 9.1 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
// $Id$

/**
 * @file
 * Database schema code for MySQL database servers.
 */


/**
 * @ingroup schemaapi
 * @{
 */

class DatabaseSchema_mysql extends DatabaseSchema {

  public function tableExists($table) {
    return (bool) $this->connection->query("SHOW TABLES LIKE '{" . $table . "}'", array(), array())->fetchField();
  }
20

21
  public function columnExists($table, $column) {
22
    return (bool) $this->connection->query("SHOW COLUMNS FROM {" . $this->connection->escapeTable($table) . "} LIKE '" . $this->connection->escapeTable($column) . "'", array(), array())->fetchField();
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
  }


  /**
   * Generate SQL to create a new table from a Drupal schema definition.
   *
   * @param $name
   *   The name of the table to create.
   * @param $table
   *   A Schema API table definition array.
   * @return
   *   An array of SQL statements to create the table.
   */
  protected function createTableSql($name, $table) {
    if (empty($table['mysql_suffix'])) {
      $table['mysql_suffix'] = "/*!40100 DEFAULT CHARACTER SET UTF8 */";
    }
40

41
    $sql = "CREATE TABLE {" . $name . "} (\n";
42

43
44
45
46
    // Add the SQL statement for each field.
    foreach ($table['fields'] as $field_name => $field) {
      $sql .= $this->createFieldSql($field_name, $this->processField($field)) . ", \n";
    }
47

48
49
50
51
52
    // Process keys & indexes.
    $keys = $this->createKeysSql($table);
    if (count($keys)) {
      $sql .= implode(", \n", $keys) . ", \n";
    }
53

54
55
    // Remove the last comma and space.
    $sql = substr($sql, 0, -3) . "\n) ";
56

57
    $sql .= $table['mysql_suffix'];
58

59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
    return array($sql);
  }

  /**
   * Create an SQL string for a field to be used in table creation or alteration.
   *
   * Before passing a field out of a schema definition into this function it has
   * to be processed by _db_process_field().
   *
   * @param $name
   *   Name of the field.
   * @param $spec
   *   The field specification, as per the schema data structure format.
   */
  protected function createFieldSql($name, $spec) {
    $sql = "`" . $name . "` " . $spec['mysql_type'];
75

76
77
78
79
80
81
    if (isset($spec['length'])) {
      $sql .= '(' . $spec['length'] . ')';
    }
    elseif (isset($spec['precision']) && isset($spec['scale'])) {
      $sql .= '(' . $spec['precision'] . ', ' . $spec['scale'] . ')';
    }
82

83
84
85
    if (!empty($spec['unsigned'])) {
      $sql .= ' unsigned';
    }
86

87
88
89
    if (!empty($spec['not null'])) {
      $sql .= ' NOT NULL';
    }
90

91
92
93
    if (!empty($spec['auto_increment'])) {
      $sql .= ' auto_increment';
    }
94

95
    // $spec['default'] can be NULL, so we explicitly check for the key here.
96
    if (array_key_exists('default', $spec)) {
97
98
99
      if (is_string($spec['default'])) {
        $spec['default'] = "'" . $spec['default'] . "'";
      }
100
101
102
      elseif (is_null($spec['default'])) {
        $spec['default'] = 'NULL';
      }
103
104
      $sql .= ' DEFAULT ' . $spec['default'];
    }
105

106
107
108
    if (empty($spec['not null']) && !isset($spec['default'])) {
      $sql .= ' DEFAULT NULL';
    }
109

110
111
    return $sql;
  }
112

113
114
115
116
117
118
119
  /**
   * Set database-engine specific properties for a field.
   *
   * @param $field
   *   A field description array, as specified in the schema documentation.
   */
  protected function processField($field) {
120

121
122
123
    if (!isset($field['size'])) {
      $field['size'] = 'normal';
    }
124

125
126
127
128
129
    // Set the correct database-engine specific datatype.
    if (!isset($field['mysql_type'])) {
      $map = db_type_map();
      $field['mysql_type'] = $map[$field['type'] . ':' . $field['size']];
    }
130

131
132
133
    if ($field['type'] == 'serial') {
      $field['auto_increment'] = TRUE;
    }
134

135
136
137
138
    return $field;
  }

  public function getFieldTypeMap() {
139
    // Put :normal last so it gets preserved by array_flip. This makes
140
141
142
143
144
    // it much easier for modules (such as schema.module) to map
    // database types back into schema types.
    static $map = array(
      'varchar:normal'  => 'VARCHAR',
      'char:normal'     => 'CHAR',
145

146
147
148
149
150
      'text:tiny'       => 'TINYTEXT',
      'text:small'      => 'TINYTEXT',
      'text:medium'     => 'MEDIUMTEXT',
      'text:big'        => 'LONGTEXT',
      'text:normal'     => 'TEXT',
151

152
153
154
155
156
      'serial:tiny'     => 'TINYINT',
      'serial:small'    => 'SMALLINT',
      'serial:medium'   => 'MEDIUMINT',
      'serial:big'      => 'BIGINT',
      'serial:normal'   => 'INT',
157

158
159
160
161
162
      'int:tiny'        => 'TINYINT',
      'int:small'       => 'SMALLINT',
      'int:medium'      => 'MEDIUMINT',
      'int:big'         => 'BIGINT',
      'int:normal'      => 'INT',
163

164
165
166
167
168
      'float:tiny'      => 'FLOAT',
      'float:small'     => 'FLOAT',
      'float:medium'    => 'FLOAT',
      'float:big'       => 'DOUBLE',
      'float:normal'    => 'FLOAT',
169

170
      'numeric:normal'  => 'DECIMAL',
171

172
173
      'blob:big'        => 'LONGBLOB',
      'blob:normal'     => 'BLOB',
174

175
176
177
178
      'datetime:normal' => 'DATETIME',
    );
    return $map;
  }
179

180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201



  protected function createKeysSql($spec) {
    $keys = array();

    if (!empty($spec['primary key'])) {
      $keys[] = 'PRIMARY KEY (' . $this->createKeysSqlHelper($spec['primary key']) . ')';
    }
    if (!empty($spec['unique keys'])) {
      foreach ($spec['unique keys'] as $key => $fields) {
        $keys[] = 'UNIQUE KEY ' . $key .' ('. $this->createKeysSqlHelper($fields) . ')';
      }
    }
    if (!empty($spec['indexes'])) {
      foreach ($spec['indexes'] as $index => $fields) {
        $keys[] = 'INDEX ' . $index . ' (' . $this->createKeysSqlHelper($fields) . ')';
      }
    }

    return $keys;
  }
202

203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
  protected function createKeySql($fields) {
    $ret = array();
    foreach ($fields as $field) {
      if (is_array($field)) {
        $ret[] = $field[0] . '(' . $field[1] . ')';
      }
      else {
        $ret[] = $field;
      }
    }
    return implode(', ', $ret);
  }

  protected function createKeysSqlHelper($fields) {
    $ret = array();
    foreach ($fields as $field) {
      if (is_array($field)) {
        $ret[] = $field[0] . '(' . $field[1] . ')';
      }
      else {
        $ret[] = $field;
      }
    }
    return implode(', ', $ret);
  }

  public function renameTable(&$ret, $table, $new_name) {
    $ret[] = update_sql('ALTER TABLE {' . $table . '} RENAME TO {' . $new_name . '}');
  }
232

233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
  public function dropTable(&$ret, $table) {
    $ret[] = update_sql('DROP TABLE {' . $table . '}');
  }

  public function addField(&$ret, $table, $field, $spec, $keys_new = array()) {
    $fixnull = FALSE;
    if (!empty($spec['not null']) && !isset($spec['default'])) {
      $fixnull = TRUE;
      $spec['not null'] = FALSE;
    }
    $query = 'ALTER TABLE {' . $table . '} ADD ';
    $query .= $this->createFieldSql($field, $this->processField($spec));
    if (count($keys_new)) {
      $query .= ', ADD ' . implode(', ADD ', $this->createKeysSql($keys_new));
    }
    $ret[] = update_sql($query);
    if (isset($spec['initial'])) {
      // All this because update_sql does not support %-placeholders.
      $sql = 'UPDATE {' . $table . '} SET ' . $field . ' = ' . db_type_placeholder($spec['type']);
      $result = db_query($sql, $spec['initial']);
      $ret[] = array('success' => $result !== FALSE, 'query' => check_plain($sql . ' (' . $spec['initial'] . ')'));
    }
    if ($fixnull) {
      $spec['not null'] = TRUE;
      $this->changeField($ret, $table, $field, $field, $spec);
    }
  }

  public function dropField(&$ret, $table, $field) {
    $ret[] = update_sql('ALTER TABLE {' . $table . '} DROP ' . $field);
  }

  public function fieldSetDefault(&$ret, $table, $field, $default) {
266
    if (is_null($default)) {
267
268
269
270
271
      $default = 'NULL';
    }
    else {
      $default = is_string($default) ? "'$default'" : $default;
    }
272

273
274
275
276
277
278
279
280
281
282
    $ret[] = update_sql('ALTER TABLE {' . $table . '} ALTER COLUMN ' . $field . ' SET DEFAULT ' . $default);
  }

  public function fieldSetNoDefault(&$ret, $table, $field) {
    $ret[] = update_sql('ALTER TABLE {' . $table . '} ALTER COLUMN ' . $field . ' DROP DEFAULT');
  }

  public function addPrimaryKey(&$ret, $table, $fields) {
    $ret[] = update_sql('ALTER TABLE {' . $table . '} ADD PRIMARY KEY (' . $this->createKeySql($fields) . ')');
  }
283

284
285
286
287
288
289
290
291
292
293
294
  public function dropPrimaryKey(&$ret, $table) {
    $ret[] = update_sql('ALTER TABLE {' . $table . '} DROP PRIMARY KEY');
  }

  public function addUniqueKey(&$ret, $table, $name, $fields) {
    $ret[] = update_sql('ALTER TABLE {' . $table . '} ADD UNIQUE KEY ' . $name . ' (' . $this->createKeySql($fields) . ')');
  }

  public function dropUniqueKey(&$ret, $table, $name) {
    $ret[] = update_sql('ALTER TABLE {' . $table . '} DROP KEY ' . $name);
  }
295

296
297
298
299
300
301
302
303
  public function addIndex(&$ret, $table, $name, $fields) {
    $query = 'ALTER TABLE {' . $table . '} ADD INDEX ' . $name . ' (' . $this->createKeySql($fields) . ')';
    $ret[] = update_sql($query);
  }

  public function dropIndex(&$ret, $table, $name) {
    $ret[] = update_sql('ALTER TABLE {' . $table . '} DROP INDEX ' . $name);
  }
304

305
  public function changeField(&$ret, $table, $field, $field_new, $spec, $keys_new = array()) {
306
    $sql = 'ALTER TABLE {' . $table . '} CHANGE `' . $field . '` ' . $this->createFieldSql($field_new, $this->processField($spec));
307
308
309
310
311
312
313
314
315
316
317
    if (count($keys_new)) {
      $sql .= ', ADD ' . implode(', ADD ', $this->createKeysSql($keys_new));
    }
    $ret[] = update_sql($sql);
  }

}

/**
 * @} End of "ingroup schemaapi".
 */