schema.php 30 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069
  1. <?php
  2. /**
  3. * StatusNet, the distributed open-source microblogging tool
  4. *
  5. * Database schema utilities
  6. *
  7. * PHP version 5
  8. *
  9. * LICENCE: This program is free software: you can redistribute it and/or modify
  10. * it under the terms of the GNU Affero General Public License as published by
  11. * the Free Software Foundation, either version 3 of the License, or
  12. * (at your option) any later version.
  13. *
  14. * This program is distributed in the hope that it will be useful,
  15. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  16. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  17. * GNU Affero General Public License for more details.
  18. *
  19. * You should have received a copy of the GNU Affero General Public License
  20. * along with this program. If not, see <http://www.gnu.org/licenses/>.
  21. *
  22. * @category Database
  23. * @package StatusNet
  24. * @author Evan Prodromou <evan@status.net>
  25. * @copyright 2009 StatusNet, Inc.
  26. * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0
  27. * @link http://status.net/
  28. */
  29. if (!defined('STATUSNET')) {
  30. exit(1);
  31. }
  32. /**
  33. * Class representing the database schema
  34. *
  35. * A class representing the database schema. Can be used to
  36. * manipulate the schema -- especially for plugins and upgrade
  37. * utilities.
  38. *
  39. * @category Database
  40. * @package StatusNet
  41. * @author Evan Prodromou <evan@status.net>
  42. * @author Brion Vibber <brion@status.net>
  43. * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0
  44. * @link http://status.net/
  45. */
  46. class Schema
  47. {
  48. static $_static = null;
  49. protected $conn = null;
  50. /**
  51. * Constructor. Only run once for singleton object.
  52. */
  53. protected function __construct($conn = null)
  54. {
  55. if (is_null($conn)) {
  56. // XXX: there should be an easier way to do this.
  57. $user = new User();
  58. $conn = $user->getDatabaseConnection();
  59. $user->free();
  60. unset($user);
  61. }
  62. $this->conn = $conn;
  63. }
  64. /**
  65. * Main public entry point. Use this to get
  66. * the schema object.
  67. *
  68. * @return Schema the Schema object for the connection
  69. */
  70. static function get($conn = null)
  71. {
  72. if (is_null($conn)) {
  73. $key = 'default';
  74. } else {
  75. $key = md5(serialize($conn->dsn));
  76. }
  77. $type = common_config('db', 'type');
  78. if (empty(self::$_static[$key])) {
  79. $schemaClass = ucfirst($type).'Schema';
  80. self::$_static[$key] = new $schemaClass($conn);
  81. }
  82. return self::$_static[$key];
  83. }
  84. /**
  85. * Gets a ColumnDef object for a single column.
  86. *
  87. * Throws an exception if the table is not found.
  88. *
  89. * @param string $table name of the table
  90. * @param string $column name of the column
  91. *
  92. * @return ColumnDef definition of the column or null
  93. * if not found.
  94. */
  95. public function getColumnDef($table, $column)
  96. {
  97. $td = $this->getTableDef($table);
  98. if (!empty($td) && !empty($td->columns)) {
  99. foreach ($td->columns as $cd) {
  100. if ($cd->name == $column) {
  101. return $cd;
  102. }
  103. }
  104. }
  105. return null;
  106. }
  107. /**
  108. * Creates a table with the given names and columns.
  109. *
  110. * @param string $tableName Name of the table
  111. * @param array $def Table definition array listing fields and indexes.
  112. *
  113. * @return boolean success flag
  114. */
  115. public function createTable($tableName, $def)
  116. {
  117. $statements = $this->buildCreateTable($tableName, $def);
  118. return $this->runSqlSet($statements);
  119. }
  120. /**
  121. * Build a set of SQL statements to create a table with the given
  122. * name and columns.
  123. *
  124. * @param string $name Name of the table
  125. * @param array $def Table definition array
  126. *
  127. * @return boolean success flag
  128. */
  129. public function buildCreateTable($name, $def)
  130. {
  131. $def = $this->validateDef($name, $def);
  132. $def = $this->filterDef($def);
  133. $sql = array();
  134. foreach ($def['fields'] as $col => $colDef) {
  135. $this->appendColumnDef($sql, $col, $colDef);
  136. }
  137. // Primary, unique, and foreign keys are constraints, so go within
  138. // the CREATE TABLE statement normally.
  139. if (!empty($def['primary key'])) {
  140. $this->appendPrimaryKeyDef($sql, $def['primary key']);
  141. }
  142. if (!empty($def['unique keys'])) {
  143. foreach ($def['unique keys'] as $col => $colDef) {
  144. $this->appendUniqueKeyDef($sql, $col, $colDef);
  145. }
  146. }
  147. if (!empty($def['foreign keys'])) {
  148. foreach ($def['foreign keys'] as $keyName => $keyDef) {
  149. $this->appendForeignKeyDef($sql, $keyName, $keyDef);
  150. }
  151. }
  152. // Wrap the CREATE TABLE around the main body chunks...
  153. $statements = array();
  154. $statements[] = $this->startCreateTable($name, $def) . "\n" .
  155. implode($sql, ",\n") . "\n" .
  156. $this->endCreateTable($name, $def);
  157. // Multi-value indexes are advisory and for best portability
  158. // should be created as separate statements.
  159. if (!empty($def['indexes'])) {
  160. foreach ($def['indexes'] as $col => $colDef) {
  161. $this->appendCreateIndex($statements, $name, $col, $colDef);
  162. }
  163. }
  164. if (!empty($def['fulltext indexes'])) {
  165. foreach ($def['fulltext indexes'] as $col => $colDef) {
  166. $this->appendCreateFulltextIndex($statements, $name, $col, $colDef);
  167. }
  168. }
  169. return $statements;
  170. }
  171. /**
  172. * Set up a 'create table' SQL statement.
  173. *
  174. * @param string $name table name
  175. * @param array $def table definition
  176. * @param $string
  177. */
  178. function startCreateTable($name, array $def)
  179. {
  180. return 'CREATE TABLE ' . $this->quoteIdentifier($name) . ' (';
  181. }
  182. /**
  183. * Close out a 'create table' SQL statement.
  184. *
  185. * @param string $name table name
  186. * @param array $def table definition
  187. * @return string
  188. */
  189. function endCreateTable($name, array $def)
  190. {
  191. return ')';
  192. }
  193. /**
  194. * Append an SQL fragment with a column definition in a CREATE TABLE statement.
  195. *
  196. * @param array $sql
  197. * @param string $name
  198. * @param array $def
  199. */
  200. function appendColumnDef(array &$sql, $name, array $def)
  201. {
  202. $sql[] = "$name " . $this->columnSql($def);
  203. }
  204. /**
  205. * Append an SQL fragment with a constraint definition for a primary
  206. * key in a CREATE TABLE statement.
  207. *
  208. * @param array $sql
  209. * @param array $def
  210. */
  211. function appendPrimaryKeyDef(array &$sql, array $def)
  212. {
  213. $sql[] = "PRIMARY KEY " . $this->buildIndexList($def);
  214. }
  215. /**
  216. * Append an SQL fragment with a constraint definition for a unique
  217. * key in a CREATE TABLE statement.
  218. *
  219. * @param array $sql
  220. * @param string $name
  221. * @param array $def
  222. */
  223. function appendUniqueKeyDef(array &$sql, $name, array $def)
  224. {
  225. $sql[] = "CONSTRAINT $name UNIQUE " . $this->buildIndexList($def);
  226. }
  227. /**
  228. * Append an SQL fragment with a constraint definition for a foreign
  229. * key in a CREATE TABLE statement.
  230. *
  231. * @param array $sql
  232. * @param string $name
  233. * @param array $def
  234. */
  235. function appendForeignKeyDef(array &$sql, $name, array $def)
  236. {
  237. if (count($def) != 2) {
  238. throw new Exception("Invalid foreign key def for $name: " . var_export($def, true));
  239. }
  240. list($refTable, $map) = $def;
  241. $srcCols = array_keys($map);
  242. $refCols = array_values($map);
  243. $sql[] = "CONSTRAINT $name FOREIGN KEY " .
  244. $this->buildIndexList($srcCols) .
  245. " REFERENCES " .
  246. $this->quoteIdentifier($refTable) .
  247. " " .
  248. $this->buildIndexList($refCols);
  249. }
  250. /**
  251. * Append an SQL statement with an index definition for an advisory
  252. * index over one or more columns on a table.
  253. *
  254. * @param array $statements
  255. * @param string $table
  256. * @param string $name
  257. * @param array $def
  258. */
  259. function appendCreateIndex(array &$statements, $table, $name, array $def)
  260. {
  261. $statements[] = "CREATE INDEX $name ON $table " . $this->buildIndexList($def);
  262. }
  263. /**
  264. * Append an SQL statement with an index definition for a full-text search
  265. * index over one or more columns on a table.
  266. *
  267. * @param array $statements
  268. * @param string $table
  269. * @param string $name
  270. * @param array $def
  271. */
  272. function appendCreateFulltextIndex(array &$statements, $table, $name, array $def)
  273. {
  274. throw new Exception("Fulltext index not supported in this database");
  275. }
  276. /**
  277. * Append an SQL statement to drop an index from a table.
  278. *
  279. * @param array $statements
  280. * @param string $table
  281. * @param string $name
  282. * @param array $def
  283. */
  284. function appendDropIndex(array &$statements, $table, $name)
  285. {
  286. $statements[] = "DROP INDEX $name ON " . $this->quoteIdentifier($table);
  287. }
  288. function buildIndexList(array $def)
  289. {
  290. // @fixme
  291. return '(' . implode(',', array_map(array($this, 'buildIndexItem'), $def)) . ')';
  292. }
  293. function buildIndexItem($def)
  294. {
  295. if (is_array($def)) {
  296. list($name, $size) = $def;
  297. return $this->quoteIdentifier($name) . '(' . intval($size) . ')';
  298. }
  299. return $this->quoteIdentifier($def);
  300. }
  301. /**
  302. * Drops a table from the schema
  303. *
  304. * Throws an exception if the table is not found.
  305. *
  306. * @param string $name Name of the table to drop
  307. *
  308. * @return boolean success flag
  309. */
  310. public function dropTable($name)
  311. {
  312. global $_PEAR;
  313. $res = $this->conn->query("DROP TABLE $name");
  314. if ($_PEAR->isError($res)) {
  315. PEAR_ErrorToPEAR_Exception($res);
  316. }
  317. return true;
  318. }
  319. /**
  320. * Adds an index to a table.
  321. *
  322. * If no name is provided, a name will be made up based
  323. * on the table name and column names.
  324. *
  325. * Throws an exception on database error, esp. if the table
  326. * does not exist.
  327. *
  328. * @param string $table Name of the table
  329. * @param array $columnNames Name of columns to index
  330. * @param string $name (Optional) name of the index
  331. *
  332. * @return boolean success flag
  333. */
  334. public function createIndex($table, $columnNames, $name=null)
  335. {
  336. global $_PEAR;
  337. if (!is_array($columnNames)) {
  338. $columnNames = array($columnNames);
  339. }
  340. if (empty($name)) {
  341. $name = "{$table}_".implode("_", $columnNames)."_idx";
  342. }
  343. $res = $this->conn->query("ALTER TABLE $table ".
  344. "ADD INDEX $name (".
  345. implode(",", $columnNames).")");
  346. if ($_PEAR->isError($res)) {
  347. PEAR_ErrorToPEAR_Exception($res);
  348. }
  349. return true;
  350. }
  351. /**
  352. * Drops a named index from a table.
  353. *
  354. * @param string $table name of the table the index is on.
  355. * @param string $name name of the index
  356. *
  357. * @return boolean success flag
  358. */
  359. public function dropIndex($table, $name)
  360. {
  361. global $_PEAR;
  362. $res = $this->conn->query("ALTER TABLE $table DROP INDEX $name");
  363. if ($_PEAR->isError($res)) {
  364. PEAR_ErrorToPEAR_Exception($res);
  365. }
  366. return true;
  367. }
  368. /**
  369. * Adds a column to a table
  370. *
  371. * @param string $table name of the table
  372. * @param ColumnDef $columndef Definition of the new
  373. * column.
  374. *
  375. * @return boolean success flag
  376. */
  377. public function addColumn($table, $columndef)
  378. {
  379. global $_PEAR;
  380. $sql = "ALTER TABLE $table ADD COLUMN " . $this->_columnSql($columndef);
  381. $res = $this->conn->query($sql);
  382. if ($_PEAR->isError($res)) {
  383. PEAR_ErrorToPEAR_Exception($res);
  384. }
  385. return true;
  386. }
  387. /**
  388. * Modifies a column in the schema.
  389. *
  390. * The name must match an existing column and table.
  391. *
  392. * @param string $table name of the table
  393. * @param ColumnDef $columndef new definition of the column.
  394. *
  395. * @return boolean success flag
  396. */
  397. public function modifyColumn($table, $columndef)
  398. {
  399. global $_PEAR;
  400. $sql = "ALTER TABLE $table MODIFY COLUMN " .
  401. $this->_columnSql($columndef);
  402. $res = $this->conn->query($sql);
  403. if ($_PEAR->isError($res)) {
  404. PEAR_ErrorToPEAR_Exception($res);
  405. }
  406. return true;
  407. }
  408. /**
  409. * Drops a column from a table
  410. *
  411. * The name must match an existing column.
  412. *
  413. * @param string $table name of the table
  414. * @param string $columnName name of the column to drop
  415. *
  416. * @return boolean success flag
  417. */
  418. public function dropColumn($table, $columnName)
  419. {
  420. global $_PEAR;
  421. $sql = "ALTER TABLE $table DROP COLUMN $columnName";
  422. $res = $this->conn->query($sql);
  423. if ($_PEAR->isError($res)) {
  424. PEAR_ErrorToPEAR_Exception($res);
  425. }
  426. return true;
  427. }
  428. /**
  429. * Ensures that a table exists with the given
  430. * name and the given column definitions.
  431. *
  432. * If the table does not yet exist, it will
  433. * create the table. If it does exist, it will
  434. * alter the table to match the column definitions.
  435. *
  436. * @param string $tableName name of the table
  437. * @param array $def Table definition array
  438. *
  439. * @return boolean success flag
  440. */
  441. public function ensureTable($tableName, $def)
  442. {
  443. $statements = $this->buildEnsureTable($tableName, $def);
  444. return $this->runSqlSet($statements);
  445. }
  446. /**
  447. * Run a given set of SQL commands on the connection in sequence.
  448. * Empty input is ok.
  449. *
  450. * @fixme if multiple statements, wrap in a transaction?
  451. * @param array $statements
  452. * @return boolean success flag
  453. */
  454. function runSqlSet(array $statements)
  455. {
  456. global $_PEAR;
  457. $ok = true;
  458. foreach ($statements as $sql) {
  459. if (defined('DEBUG_INSTALLER')) {
  460. echo "<tt>" . htmlspecialchars($sql) . "</tt><br/>\n";
  461. }
  462. $res = $this->conn->query($sql);
  463. if ($_PEAR->isError($res)) {
  464. common_debug('PEAR exception on query: '.$sql);
  465. PEAR_ErrorToPEAR_Exception($res);
  466. }
  467. }
  468. return $ok;
  469. }
  470. /**
  471. * Check a table's status, and if needed build a set
  472. * of SQL statements which change it to be consistent
  473. * with the given table definition.
  474. *
  475. * If the table does not yet exist, statements will
  476. * be returned to create the table. If it does exist,
  477. * statements will be returned to alter the table to
  478. * match the column definitions.
  479. *
  480. * @param string $tableName name of the table
  481. * @param array $columns array of ColumnDef
  482. * objects for the table
  483. *
  484. * @return array of SQL statements
  485. */
  486. function buildEnsureTable($tableName, array $def)
  487. {
  488. try {
  489. $old = $this->getTableDef($tableName);
  490. } catch (SchemaTableMissingException $e) {
  491. return $this->buildCreateTable($tableName, $def);
  492. }
  493. // Filter the DB-independent table definition to match the current
  494. // database engine's features and limitations.
  495. $def = $this->validateDef($tableName, $def);
  496. $def = $this->filterDef($def);
  497. $statements = array();
  498. $fields = $this->diffArrays($old, $def, 'fields', array($this, 'columnsEqual'));
  499. $uniques = $this->diffArrays($old, $def, 'unique keys');
  500. $indexes = $this->diffArrays($old, $def, 'indexes');
  501. $foreign = $this->diffArrays($old, $def, 'foreign keys');
  502. $fulltext = $this->diffArrays($old, $def, 'fulltext indexes');
  503. // Drop any obsolete or modified indexes ahead...
  504. foreach ($indexes['del'] + $indexes['mod'] as $indexName) {
  505. $this->appendDropIndex($statements, $tableName, $indexName);
  506. }
  507. // Drop any obsolete or modified fulltext indexes ahead...
  508. foreach ($fulltext['del'] + $fulltext['mod'] as $indexName) {
  509. $this->appendDropIndex($statements, $tableName, $indexName);
  510. }
  511. // For efficiency, we want this all in one
  512. // query, instead of using our methods.
  513. $phrase = array();
  514. foreach ($foreign['del'] + $foreign['mod'] as $keyName) {
  515. $this->appendAlterDropForeign($phrase, $keyName);
  516. }
  517. foreach ($uniques['del'] + $uniques['mod'] as $keyName) {
  518. $this->appendAlterDropUnique($phrase, $keyName);
  519. }
  520. if (isset($old['primary key']) && (!isset($def['primary key']) || $def['primary key'] != $old['primary key'])) {
  521. $this->appendAlterDropPrimary($phrase);
  522. }
  523. foreach ($fields['add'] as $columnName) {
  524. $this->appendAlterAddColumn($phrase, $columnName,
  525. $def['fields'][$columnName]);
  526. }
  527. foreach ($fields['mod'] as $columnName) {
  528. $this->appendAlterModifyColumn($phrase, $columnName,
  529. $old['fields'][$columnName],
  530. $def['fields'][$columnName]);
  531. }
  532. foreach ($fields['del'] as $columnName) {
  533. $this->appendAlterDropColumn($phrase, $columnName);
  534. }
  535. if (isset($def['primary key']) && (!isset($old['primary key']) || $old['primary key'] != $def['primary key'])) {
  536. $this->appendAlterAddPrimary($phrase, $def['primary key']);
  537. }
  538. foreach ($uniques['mod'] + $uniques['add'] as $keyName) {
  539. $this->appendAlterAddUnique($phrase, $keyName, $def['unique keys'][$keyName]);
  540. }
  541. foreach ($foreign['mod'] + $foreign['add'] as $keyName) {
  542. $this->appendAlterAddForeign($phrase, $keyName, $def['foreign keys'][$keyName]);
  543. }
  544. $this->appendAlterExtras($phrase, $tableName, $def);
  545. if (count($phrase) > 0) {
  546. $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(",\n", $phrase);
  547. $statements[] = $sql;
  548. }
  549. // Now create any indexes...
  550. foreach ($indexes['mod'] + $indexes['add'] as $indexName) {
  551. $this->appendCreateIndex($statements, $tableName, $indexName, $def['indexes'][$indexName]);
  552. }
  553. foreach ($fulltext['mod'] + $fulltext['add'] as $indexName) {
  554. $colDef = $def['fulltext indexes'][$indexName];
  555. $this->appendCreateFulltextIndex($statements, $tableName, $indexName, $colDef);
  556. }
  557. return $statements;
  558. }
  559. function diffArrays($oldDef, $newDef, $section, $compareCallback=null)
  560. {
  561. $old = isset($oldDef[$section]) ? $oldDef[$section] : array();
  562. $new = isset($newDef[$section]) ? $newDef[$section] : array();
  563. $oldKeys = array_keys($old);
  564. $newKeys = array_keys($new);
  565. $toadd = array_diff($newKeys, $oldKeys);
  566. $todrop = array_diff($oldKeys, $newKeys);
  567. $same = array_intersect($newKeys, $oldKeys);
  568. $tomod = array();
  569. $tokeep = array();
  570. // Find which fields have actually changed definition
  571. // in a way that we need to tweak them for this DB type.
  572. foreach ($same as $name) {
  573. if ($compareCallback) {
  574. $same = call_user_func($compareCallback, $old[$name], $new[$name]);
  575. } else {
  576. $same = ($old[$name] == $new[$name]);
  577. }
  578. if ($same) {
  579. $tokeep[] = $name;
  580. continue;
  581. }
  582. $tomod[] = $name;
  583. }
  584. return array('add' => $toadd,
  585. 'del' => $todrop,
  586. 'mod' => $tomod,
  587. 'keep' => $tokeep,
  588. 'count' => count($toadd) + count($todrop) + count($tomod));
  589. }
  590. /**
  591. * Append phrase(s) to an array of partial ALTER TABLE chunks in order
  592. * to add the given column definition to the table.
  593. *
  594. * @param array $phrase
  595. * @param string $columnName
  596. * @param array $cd
  597. */
  598. function appendAlterAddColumn(array &$phrase, $columnName, array $cd)
  599. {
  600. $phrase[] = 'ADD COLUMN ' .
  601. $this->quoteIdentifier($columnName) .
  602. ' ' .
  603. $this->columnSql($cd);
  604. }
  605. /**
  606. * Append phrase(s) to an array of partial ALTER TABLE chunks in order
  607. * to alter the given column from its old state to a new one.
  608. *
  609. * @param array $phrase
  610. * @param string $columnName
  611. * @param array $old previous column definition as found in DB
  612. * @param array $cd current column definition
  613. */
  614. function appendAlterModifyColumn(array &$phrase, $columnName, array $old, array $cd)
  615. {
  616. $phrase[] = 'MODIFY COLUMN ' .
  617. $this->quoteIdentifier($columnName) .
  618. ' ' .
  619. $this->columnSql($cd);
  620. }
  621. /**
  622. * Append phrase(s) to an array of partial ALTER TABLE chunks in order
  623. * to drop the given column definition from the table.
  624. *
  625. * @param array $phrase
  626. * @param string $columnName
  627. */
  628. function appendAlterDropColumn(array &$phrase, $columnName)
  629. {
  630. $phrase[] = 'DROP COLUMN ' . $this->quoteIdentifier($columnName);
  631. }
  632. function appendAlterAddUnique(array &$phrase, $keyName, array $def)
  633. {
  634. $sql = array();
  635. $sql[] = 'ADD';
  636. $this->appendUniqueKeyDef($sql, $keyName, $def);
  637. $phrase[] = implode(' ', $sql);
  638. }
  639. function appendAlterAddForeign(array &$phrase, $keyName, array $def)
  640. {
  641. $sql = array();
  642. $sql[] = 'ADD';
  643. $this->appendForeignKeyDef($sql, $keyName, $def);
  644. $phrase[] = implode(' ', $sql);
  645. }
  646. function appendAlterAddPrimary(array &$phrase, array $def)
  647. {
  648. $sql = array();
  649. $sql[] = 'ADD';
  650. $this->appendPrimaryKeyDef($sql, $def);
  651. $phrase[] = implode(' ', $sql);
  652. }
  653. function appendAlterDropPrimary(array &$phrase)
  654. {
  655. $phrase[] = 'DROP CONSTRAINT PRIMARY KEY';
  656. }
  657. function appendAlterDropUnique(array &$phrase, $keyName)
  658. {
  659. $phrase[] = 'DROP CONSTRAINT ' . $keyName;
  660. }
  661. function appendAlterDropForeign(array &$phrase, $keyName)
  662. {
  663. $phrase[] = 'DROP FOREIGN KEY ' . $keyName;
  664. }
  665. function appendAlterExtras(array &$phrase, $tableName, array $def)
  666. {
  667. // no-op
  668. }
  669. /**
  670. * Quote a db/table/column identifier if necessary.
  671. *
  672. * @param string $name
  673. * @return string
  674. */
  675. function quoteIdentifier($name)
  676. {
  677. return $name;
  678. }
  679. function quoteDefaultValue($cd)
  680. {
  681. if ($cd['type'] == 'datetime' && $cd['default'] == 'CURRENT_TIMESTAMP') {
  682. return $cd['default'];
  683. } else {
  684. return $this->quoteValue($cd['default']);
  685. }
  686. }
  687. function quoteValue($val)
  688. {
  689. return $this->conn->quoteSmart($val);
  690. }
  691. /**
  692. * Check if two column definitions are equivalent.
  693. * The default implementation checks _everything_ but in many cases
  694. * you may be able to discard a bunch of equivalencies.
  695. *
  696. * @param array $a
  697. * @param array $b
  698. * @return boolean
  699. */
  700. function columnsEqual(array $a, array $b)
  701. {
  702. return !array_diff_assoc($a, $b) && !array_diff_assoc($b, $a);
  703. }
  704. /**
  705. * Returns the array of names from an array of
  706. * ColumnDef objects.
  707. *
  708. * @param array $cds array of ColumnDef objects
  709. *
  710. * @return array strings for name values
  711. */
  712. protected function _names($cds)
  713. {
  714. $names = array();
  715. foreach ($cds as $cd) {
  716. $names[] = $cd->name;
  717. }
  718. return $names;
  719. }
  720. /**
  721. * Get a ColumnDef from an array matching
  722. * name.
  723. *
  724. * @param array $cds Array of ColumnDef objects
  725. * @param string $name Name of the column
  726. *
  727. * @return ColumnDef matching item or null if no match.
  728. */
  729. protected function _byName($cds, $name)
  730. {
  731. foreach ($cds as $cd) {
  732. if ($cd->name == $name) {
  733. return $cd;
  734. }
  735. }
  736. return null;
  737. }
  738. /**
  739. * Return the proper SQL for creating or
  740. * altering a column.
  741. *
  742. * Appropriate for use in CREATE TABLE or
  743. * ALTER TABLE statements.
  744. *
  745. * @param ColumnDef $cd column to create
  746. *
  747. * @return string correct SQL for that column
  748. */
  749. function columnSql(array $cd)
  750. {
  751. $line = array();
  752. $line[] = $this->typeAndSize($cd);
  753. if (isset($cd['default'])) {
  754. $line[] = 'default';
  755. $line[] = $this->quoteDefaultValue($cd);
  756. } else if (!empty($cd['not null'])) {
  757. // Can't have both not null AND default!
  758. $line[] = 'not null';
  759. }
  760. return implode(' ', $line);
  761. }
  762. /**
  763. *
  764. * @param string $column canonical type name in defs
  765. * @return string native DB type name
  766. */
  767. function mapType($column)
  768. {
  769. return $column;
  770. }
  771. function typeAndSize($column)
  772. {
  773. //$type = $this->mapType($column);
  774. $type = $column['type'];
  775. if (isset($column['size'])) {
  776. $type = $column['size'] . $type;
  777. }
  778. $lengths = array();
  779. if (isset($column['precision'])) {
  780. $lengths[] = $column['precision'];
  781. if (isset($column['scale'])) {
  782. $lengths[] = $column['scale'];
  783. }
  784. } else if (isset($column['length'])) {
  785. $lengths[] = $column['length'];
  786. }
  787. if ($lengths) {
  788. return $type . '(' . implode(',', $lengths) . ')';
  789. } else {
  790. return $type;
  791. }
  792. }
  793. /**
  794. * Convert an old-style set of ColumnDef objects into the current
  795. * Drupal-style schema definition array, for backwards compatibility
  796. * with plugins written for 0.9.x.
  797. *
  798. * @param string $tableName
  799. * @param array $defs: array of ColumnDef objects
  800. * @return array
  801. */
  802. protected function oldToNew($tableName, array $defs)
  803. {
  804. $table = array();
  805. $prefixes = array(
  806. 'tiny',
  807. 'small',
  808. 'medium',
  809. 'big',
  810. );
  811. foreach ($defs as $cd) {
  812. $column = array();
  813. $column['type'] = $cd->type;
  814. foreach ($prefixes as $prefix) {
  815. if (substr($cd->type, 0, strlen($prefix)) == $prefix) {
  816. $column['type'] = substr($cd->type, strlen($prefix));
  817. $column['size'] = $prefix;
  818. break;
  819. }
  820. }
  821. if ($cd->size) {
  822. if ($cd->type == 'varchar' || $cd->type == 'char') {
  823. $column['length'] = $cd->size;
  824. }
  825. }
  826. if (!$cd->nullable) {
  827. $column['not null'] = true;
  828. }
  829. if ($cd->auto_increment) {
  830. $column['type'] = 'serial';
  831. }
  832. if ($cd->default) {
  833. $column['default'] = $cd->default;
  834. }
  835. $table['fields'][$cd->name] = $column;
  836. if ($cd->key == 'PRI') {
  837. // If multiple columns are defined as primary key,
  838. // we'll pile them on in sequence.
  839. if (!isset($table['primary key'])) {
  840. $table['primary key'] = array();
  841. }
  842. $table['primary key'][] = $cd->name;
  843. } else if ($cd->key == 'MUL') {
  844. // Individual multiple-value indexes are only per-column
  845. // using the old ColumnDef syntax.
  846. $idx = "{$tableName}_{$cd->name}_idx";
  847. $table['indexes'][$idx] = array($cd->name);
  848. } else if ($cd->key == 'UNI') {
  849. // Individual unique-value indexes are only per-column
  850. // using the old ColumnDef syntax.
  851. $idx = "{$tableName}_{$cd->name}_idx";
  852. $table['unique keys'][$idx] = array($cd->name);
  853. }
  854. }
  855. return $table;
  856. }
  857. /**
  858. * Filter the given table definition array to match features available
  859. * in this database.
  860. *
  861. * This lets us strip out unsupported things like comments, foreign keys,
  862. * or type variants that we wouldn't get back from getTableDef().
  863. *
  864. * @param array $tableDef
  865. */
  866. function filterDef(array $tableDef)
  867. {
  868. return $tableDef;
  869. }
  870. /**
  871. * Validate a table definition array, checking for basic structure.
  872. *
  873. * If necessary, converts from an old-style array of ColumnDef objects.
  874. *
  875. * @param string $tableName
  876. * @param array $def: table definition array
  877. * @return array validated table definition array
  878. *
  879. * @throws Exception on wildly invalid input
  880. */
  881. function validateDef($tableName, array $def)
  882. {
  883. if (isset($def[0]) && $def[0] instanceof ColumnDef) {
  884. $def = $this->oldToNew($tableName, $def);
  885. }
  886. // A few quick checks :D
  887. if (!isset($def['fields'])) {
  888. throw new Exception("Invalid table definition for $tableName: no fields.");
  889. }
  890. return $def;
  891. }
  892. function isNumericType($type)
  893. {
  894. $type = strtolower($type);
  895. $known = array('int', 'serial', 'numeric');
  896. return in_array($type, $known);
  897. }
  898. /**
  899. * Pull info from the query into a fun-fun array of dooooom
  900. *
  901. * @param string $sql
  902. * @return array of arrays
  903. */
  904. protected function fetchQueryData($sql)
  905. {
  906. global $_PEAR;
  907. $res = $this->conn->query($sql);
  908. if ($_PEAR->isError($res)) {
  909. PEAR_ErrorToPEAR_Exception($res);
  910. }
  911. $out = array();
  912. $row = array();
  913. while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) {
  914. $out[] = $row;
  915. }
  916. $res->free();
  917. return $out;
  918. }
  919. }
  920. class SchemaTableMissingException extends Exception
  921. {
  922. // no-op
  923. }