PageRenderTime 68ms CodeModel.GetById 16ms RepoModel.GetById 0ms app.codeStats 0ms

/includes/pear/MDB2/Driver/Manager/mysqli.php

https://bitbucket.org/Yason/armory
PHP | 1432 lines | 800 code | 132 blank | 500 comment | 188 complexity | 4a3db0eef4e91422e627db75134af300 MD5 | raw file
Possible License(s): GPL-3.0

Large files files are truncated, but you can click here to view the full file

    

  1. <?php

    2. 

  2. // +----------------------------------------------------------------------+

    3. 

  3. // | PHP versions 4 and 5                                                 |

    4. 

  4. // +----------------------------------------------------------------------+

    5. 

  5. // | Copyright (c) 1998-2008 Manuel Lemos, Tomas V.V.Cox,                 |

    6. 

  6. // | Stig. S. Bakken, Lukas Smith                                         |

    7. 

  7. // | All rights reserved.                                                 |

    8. 

  8. // +----------------------------------------------------------------------+

    9. 

  9. // | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB  |

    10. 

  10. // | API as well as database abstraction for PHP applications.            |

    11. 

  11. // | This LICENSE is in the BSD license style.                            |

    12. 

  12. // |                                                                      |

    13. 

  13. // | Redistribution and use in source and binary forms, with or without   |

    14. 

  14. // | modification, are permitted provided that the following conditions   |

    15. 

  15. // | are met:                                                             |

    16. 

  16. // |                                                                      |

    17. 

  17. // | Redistributions of source code must retain the above copyright       |

    18. 

  18. // | notice, this list of conditions and the following disclaimer.        |

    19. 

  19. // |                                                                      |

    20. 

  20. // | Redistributions in binary form must reproduce the above copyright    |

    21. 

  21. // | notice, this list of conditions and the following disclaimer in the  |

    22. 

  22. // | documentation and/or other materials provided with the distribution. |

    23. 

  23. // |                                                                      |

    24. 

  24. // | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken,    |

    25. 

  25. // | Lukas Smith nor the names of his contributors may be used to endorse |

    26. 

  26. // | or promote products derived from this software without specific prior|

    27. 

  27. // | written permission.                                                  |

    28. 

  28. // |                                                                      |

    29. 

  29. // | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS  |

    30. 

  30. // | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT    |

    31. 

  31. // | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS    |

    32. 

  32. // | FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE      |

    33. 

  33. // | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,          |

    34. 

  34. // | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |

    35. 

  35. // | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|

    36. 

  36. // |  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED  |

    37. 

  37. // | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT          |

    38. 

  38. // | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|

    39. 

  39. // | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE          |

    40. 

  40. // | POSSIBILITY OF SUCH DAMAGE.                                          |

    41. 

  41. // +----------------------------------------------------------------------+

    42. 

  42. // | Author: Lukas Smith <smith@pooteeweet.org>                           |

    43. 

  43. // +----------------------------------------------------------------------+

    44. 

  44. //

    45. 

  45. // $Id: mysqli.php,v 1.100 2008/11/23 20:30:29 quipo Exp $

    46. 

  46. //

    47. 

  47. 

    48. 

  48. require_once 'MDB2/Driver/Manager/Common.php';

    49. 

  49. 

    50. 

  50. /**

    51. 

  51.  * MDB2 MySQLi driver for the management modules

    52. 

  52.  *

    53. 

  53.  * @package MDB2

    54. 

  54.  * @category Database

    55. 

  55.  * @author  Lukas Smith <smith@pooteeweet.org>

    56. 

  56.  */

    57. 

  57. class MDB2_Driver_Manager_mysqli extends MDB2_Driver_Manager_Common

    58. 

  58. {

    59. 

  59. 

    60. 

  60.     // }}}

    61. 

  61.     // {{{ createDatabase()

    62. 

  62. 

    63. 

  63.     /**

    64. 

  64.      * create a new database

    65. 

  65.      *

    66. 

  66.      * @param string $name    name of the database that should be created

    67. 

  67.      * @param array  $options array with charset, collation info

    68. 

  68.      *

    69. 

  69.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    70. 

  70.      * @access public

    71. 

  71.      */

    72. 

  72.     function createDatabase($name, $options = array())

    73. 

  73.     {

    74. 

  74.         $db =& $this->getDBInstance();

    75. 

  75.         if (PEAR::isError($db)) {

    76. 

  76.             return $db;

    77. 

  77.         }

    78. 

  78. 

    79. 

  79.         $name  = $db->quoteIdentifier($name, true);

    80. 

  80.         $query = 'CREATE DATABASE ' . $name;

    81. 

  81.         if (!empty($options['charset'])) {

    82. 

  82.             $query .= ' DEFAULT CHARACTER SET ' . $db->quote($options['charset'], 'text');

    83. 

  83.         }

    84. 

  84.         if (!empty($options['collation'])) {

    85. 

  85.             $query .= ' COLLATE ' . $db->quote($options['collation'], 'text');

    86. 

  86.         }

    87. 

  87.         return $db->standaloneQuery($query, null, true);

    88. 

  88.     }

    89. 

  89. 

    90. 

  90.     // }}}

    91. 

  91.     // {{{ alterDatabase()

    92. 

  92. 

    93. 

  93.     /**

    94. 

  94.      * alter an existing database

    95. 

  95.      *

    96. 

  96.      * @param string $name    name of the database that is intended to be changed

    97. 

  97.      * @param array  $options array with charset, collation info

    98. 

  98.      *

    99. 

  99.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    100. 

  100.      * @access public

    101. 

  101.      */

    102. 

  102.     function alterDatabase($name, $options = array())

    103. 

  103.     {

    104. 

  104.         $db =& $this->getDBInstance();

    105. 

  105.         if (PEAR::isError($db)) {

    106. 

  106.             return $db;

    107. 

  107.         }

    108. 

  108. 

    109. 

  109.         $query = 'ALTER DATABASE '. $db->quoteIdentifier($name, true);

    110. 

  110.         if (!empty($options['charset'])) {

    111. 

  111.             $query .= ' DEFAULT CHARACTER SET ' . $db->quote($options['charset'], 'text');

    112. 

  112.         }

    113. 

  113.         if (!empty($options['collation'])) {

    114. 

  114.             $query .= ' COLLATE ' . $db->quote($options['collation'], 'text');

    115. 

  115.         }

    116. 

  116.         return $db->standaloneQuery($query, null, true);

    117. 

  117.     }

    118. 

  118. 

    119. 

  119.     // }}}

    120. 

  120.     // {{{ dropDatabase()

    121. 

  121. 

    122. 

  122.     /**

    123. 

  123.      * drop an existing database

    124. 

  124.      *

    125. 

  125.      * @param string $name name of the database that should be dropped

    126. 

  126.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    127. 

  127.      * @access public

    128. 

  128.      */

    129. 

  129.     function dropDatabase($name)

    130. 

  130.     {

    131. 

  131.         $db =& $this->getDBInstance();

    132. 

  132.         if (PEAR::isError($db)) {

    133. 

  133.             return $db;

    134. 

  134.         }

    135. 

  135. 

    136. 

  136.         $name = $db->quoteIdentifier($name, true);

    137. 

  137.         $query = "DROP DATABASE $name";

    138. 

  138.         return $db->standaloneQuery($query, null, true);

    139. 

  139.     }

    140. 

  140. 

    141. 

  141.     // }}}

    142. 

  142.     // {{{ _getAdvancedFKOptions()

    143. 

  143. 

    144. 

  144.     /**

    145. 

  145.      * Return the FOREIGN KEY query section dealing with non-standard options

    146. 

  146.      * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...

    147. 

  147.      *

    148. 

  148.      * @param array $definition

    149. 

  149.      * @return string

    150. 

  150.      * @access protected

    151. 

  151.      */

    152. 

  152.     function _getAdvancedFKOptions($definition)

    153. 

  153.     {

    154. 

  154.         $query = '';

    155. 

  155.         if (!empty($definition['match'])) {

    156. 

  156.             $query .= ' MATCH '.$definition['match'];

    157. 

  157.         }

    158. 

  158.         if (!empty($definition['onupdate'])) {

    159. 

  159.             $query .= ' ON UPDATE '.$definition['onupdate'];

    160. 

  160.         }

    161. 

  161.         if (!empty($definition['ondelete'])) {

    162. 

  162.             $query .= ' ON DELETE '.$definition['ondelete'];

    163. 

  163.         }

    164. 

  164.         return $query;

    165. 

  165.     }

    166. 

  166. 

    167. 

  167.     // }}}

    168. 

  168.     // {{{ createTable()

    169. 

  169. 

    170. 

  170.     /**

    171. 

  171.      * create a new table

    172. 

  172.      *

    173. 

  173.      * @param string $name   Name of the database that should be created

    174. 

  174.      * @param array $fields  Associative array that contains the definition of each field of the new table

    175. 

  175.      *                       The indexes of the array entries are the names of the fields of the table an

    176. 

  176.      *                       the array entry values are associative arrays like those that are meant to be

    177. 

  177.      *                       passed with the field definitions to get[Type]Declaration() functions.

    178. 

  178.      *                          array(

    179. 

  179.      *                              'id' => array(

    180. 

  180.      *                                  'type' => 'integer',

    181. 

  181.      *                                  'unsigned' => 1

    182. 

  182.      *                                  'notnull' => 1

    183. 

  183.      *                                  'default' => 0

    184. 

  184.      *                              ),

    185. 

  185.      *                              'name' => array(

    186. 

  186.      *                                  'type' => 'text',

    187. 

  187.      *                                  'length' => 12

    188. 

  188.      *                              ),

    189. 

  189.      *                              'password' => array(

    190. 

  190.      *                                  'type' => 'text',

    191. 

  191.      *                                  'length' => 12

    192. 

  192.      *                              )

    193. 

  193.      *                          );

    194. 

  194.      * @param array $options  An associative array of table options:

    195. 

  195.      *                          array(

    196. 

  196.      *                              'comment' => 'Foo',

    197. 

  197.      *                              'charset' => 'utf8',

    198. 

  198.      *                              'collate' => 'utf8_unicode_ci',

    199. 

  199.      *                              'type'    => 'innodb',

    200. 

  200.      *                          );

    201. 

  201.      *

    202. 

  202.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    203. 

  203.      * @access public

    204. 

  204.      */

    205. 

  205.     function createTable($name, $fields, $options = array())

    206. 

  206.     {

    207. 

  207.         $db =& $this->getDBInstance();

    208. 

  208.         if (PEAR::isError($db)) {

    209. 

  209.             return $db;

    210. 

  210.         }

    211. 

  211. 

    212. 

  212.         // if we have an AUTO_INCREMENT column and a PK on more than one field,

    213. 

  213.         // we have to handle it differently...

    214. 

  214.         $autoincrement = null;

    215. 

  215.         if (empty($options['primary'])) {

    216. 

  216.             $pk_fields = array();

    217. 

  217.             foreach ($fields as $fieldname => $def) {

    218. 

  218.                 if (!empty($def['primary'])) {

    219. 

  219.                     $pk_fields[$fieldname] = true;

    220. 

  220.                 }

    221. 

  221.                 if (!empty($def['autoincrement'])) {

    222. 

  222.                     $autoincrement = $fieldname;

    223. 

  223.                 }

    224. 

  224.             }

    225. 

  225.             if (!is_null($autoincrement) && count($pk_fields) > 1) {

    226. 

  226.                 $options['primary'] = $pk_fields;

    227. 

  227.             } else {

    228. 

  228.                 // the PK constraint is on max one field => OK

    229. 

  229.                 $autoincrement = null;

    230. 

  230.             }

    231. 

  231.         }

    232. 

  232. 

    233. 

  233.         $query = $this->_getCreateTableQuery($name, $fields, $options);

    234. 

  234.         if (PEAR::isError($query)) {

    235. 

  235.             return $query;

    236. 

  236.         }

    237. 

  237. 

    238. 

  238.         if (!is_null($autoincrement)) {

    239. 

  239.             // we have to remove the PK clause added by _getIntegerDeclaration()

    240. 

  240.             $query = str_replace('AUTO_INCREMENT PRIMARY KEY', 'AUTO_INCREMENT', $query);

    241. 

  241.         }

    242. 

  242. 

    243. 

  243.         $options_strings = array();

    244. 

  244. 

    245. 

  245.         if (!empty($options['comment'])) {

    246. 

  246.             $options_strings['comment'] = 'COMMENT = '.$db->quote($options['comment'], 'text');

    247. 

  247.         }

    248. 

  248. 

    249. 

  249.         if (!empty($options['charset'])) {

    250. 

  250.             $options_strings['charset'] = 'DEFAULT CHARACTER SET '.$options['charset'];

    251. 

  251.             if (!empty($options['collate'])) {

    252. 

  252.                 $options_strings['charset'].= ' COLLATE '.$options['collate'];

    253. 

  253.             }

    254. 

  254.         }

    255. 

  255. 

    256. 

  256.         $type = false;

    257. 

  257.         if (!empty($options['type'])) {

    258. 

  258.             $type = $options['type'];

    259. 

  259.         } elseif ($db->options['default_table_type']) {

    260. 

  260.             $type = $db->options['default_table_type'];

    261. 

  261.         }

    262. 

  262.         if ($type) {

    263. 

  263.             $options_strings[] = "ENGINE = $type";

    264. 

  264.         }

    265. 

  265. 

    266. 

  266.         if (!empty($options_strings)) {

    267. 

  267.             $query .= ' '.implode(' ', $options_strings);

    268. 

  268.         }

    269. 

  269.         $result = $db->exec($query);

    270. 

  270.         if (PEAR::isError($result)) {

    271. 

  271.             return $result;

    272. 

  272.         }

    273. 

  273.         return MDB2_OK;

    274. 

  274.     }

    275. 

  275. 

    276. 

  276.     // }}}

    277. 

  277.     // {{{ dropTable()

    278. 

  278. 

    279. 

  279.     /**

    280. 

  280.      * drop an existing table

    281. 

  281.      *

    282. 

  282.      * @param string $name name of the table that should be dropped

    283. 

  283.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    284. 

  284.      * @access public

    285. 

  285.      */

    286. 

  286.     function dropTable($name)

    287. 

  287.     {

    288. 

  288.         $db =& $this->getDBInstance();

    289. 

  289.         if (PEAR::isError($db)) {

    290. 

  290.             return $db;

    291. 

  291.         }

    292. 

  292. 

    293. 

  293.         //delete the triggers associated to existing FK constraints

    294. 

  294.         $constraints = $this->listTableConstraints($name);

    295. 

  295.         if (!PEAR::isError($constraints) && !empty($constraints)) {

    296. 

  296.             $db->loadModule('Reverse', null, true);

    297. 

  297.             foreach ($constraints as $constraint) {

    298. 

  298.                 $definition = $db->reverse->getTableConstraintDefinition($name, $constraint);

    299. 

  299.                 if (!PEAR::isError($definition) && !empty($definition['foreign'])) {

    300. 

  300.                     $result = $this->_dropFKTriggers($name, $constraint, $definition['references']['table']);

    301. 

  301.                     if (PEAR::isError($result)) {

    302. 

  302.                         return $result;

    303. 

  303.                     }

    304. 

  304.                 }

    305. 

  305.             }

    306. 

  306.         }

    307. 

  307. 

    308. 

  308.         return parent::dropTable($name);

    309. 

  309.     }

    310. 

  310. 

    311. 

  311.     // }}}

    312. 

  312.     // {{{ truncateTable()

    313. 

  313. 

    314. 

  314.     /**

    315. 

  315.      * Truncate an existing table (if the TRUNCATE TABLE syntax is not supported,

    316. 

  316.      * it falls back to a DELETE FROM TABLE query)

    317. 

  317.      *

    318. 

  318.      * @param string $name name of the table that should be truncated

    319. 

  319.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    320. 

  320.      * @access public

    321. 

  321.      */

    322. 

  322.     function truncateTable($name)

    323. 

  323.     {

    324. 

  324.         $db =& $this->getDBInstance();

    325. 

  325.         if (PEAR::isError($db)) {

    326. 

  326.             return $db;

    327. 

  327.         }

    328. 

  328. 

    329. 

  329.         $name = $db->quoteIdentifier($name, true);

    330. 

  330.         return $db->exec("TRUNCATE TABLE $name");

    331. 

  331.     }

    332. 

  332. 

    333. 

  333.     // }}}

    334. 

  334.     // {{{ vacuum()

    335. 

  335. 

    336. 

  336.     /**

    337. 

  337.      * Optimize (vacuum) all the tables in the db (or only the specified table)

    338. 

  338.      * and optionally run ANALYZE.

    339. 

  339.      *

    340. 

  340.      * @param string $table table name (all the tables if empty)

    341. 

  341.      * @param array  $options an array with driver-specific options:

    342. 

  342.      *               - timeout [int] (in seconds) [mssql-only]

    343. 

  343.      *               - analyze [boolean] [pgsql and mysql]

    344. 

  344.      *               - full [boolean] [pgsql-only]

    345. 

  345.      *               - freeze [boolean] [pgsql-only]

    346. 

  346.      *

    347. 

  347.      * @return mixed MDB2_OK success, a MDB2 error on failure

    348. 

  348.      * @access public

    349. 

  349.      */

    350. 

  350.     function vacuum($table = null, $options = array())

    351. 

  351.     {

    352. 

  352.         $db =& $this->getDBInstance();

    353. 

  353.         if (PEAR::isError($db)) {

    354. 

  354.             return $db;

    355. 

  355.         }

    356. 

  356. 

    357. 

  357.         if (empty($table)) {

    358. 

  358.             $table = $this->listTables();

    359. 

  359.             if (PEAR::isError($table)) {

    360. 

  360.                 return $table;

    361. 

  361.             }

    362. 

  362.         }

    363. 

  363.         if (is_array($table)) {

    364. 

  364.             foreach (array_keys($table) as $k) {

    365. 

  365.             	$table[$k] = $db->quoteIdentifier($table[$k], true);

    366. 

  366.             }

    367. 

  367.             $table = implode(', ', $table);

    368. 

  368.         } else {

    369. 

  369.             $table = $db->quoteIdentifier($table, true);

    370. 

  370.         }

    371. 

  371. 

    372. 

  372.         $result = $db->exec('OPTIMIZE TABLE '.$table);

    373. 

  373.         if (PEAR::isError($result)) {

    374. 

  374.             return $result;

    375. 

  375.         }

    376. 

  376.         if (!empty($options['analyze'])) {

    377. 

  377.             return $db->exec('ANALYZE TABLE '.$table);

    378. 

  378.         }

    379. 

  379.         return MDB2_OK;

    380. 

  380.     }

    381. 

  381. 

    382. 

  382.     // }}}

    383. 

  383.     // {{{ alterTable()

    384. 

  384. 

    385. 

  385.     /**

    386. 

  386.      * alter an existing table

    387. 

  387.      *

    388. 

  388.      * @param string $name         name of the table that is intended to be changed.

    389. 

  389.      * @param array $changes     associative array that contains the details of each type

    390. 

  390.      *                             of change that is intended to be performed. The types of

    391. 

  391.      *                             changes that are currently supported are defined as follows:

    392. 

  392.      *

    393. 

  393.      *                             name

    394. 

  394.      *

    395. 

  395.      *                                New name for the table.

    396. 

  396.      *

    397. 

  397.      *                            add

    398. 

  398.      *

    399. 

  399.      *                                Associative array with the names of fields to be added as

    400. 

  400.      *                                 indexes of the array. The value of each entry of the array

    401. 

  401.      *                                 should be set to another associative array with the properties

    402. 

  402.      *                                 of the fields to be added. The properties of the fields should

    403. 

  403.      *                                 be the same as defined by the MDB2 parser.

    404. 

  404.      *

    405. 

  405.      *

    406. 

  406.      *                            remove

    407. 

  407.      *

    408. 

  408.      *                                Associative array with the names of fields to be removed as indexes

    409. 

  409.      *                                 of the array. Currently the values assigned to each entry are ignored.

    410. 

  410.      *                                 An empty array should be used for future compatibility.

    411. 

  411.      *

    412. 

  412.      *                            rename

    413. 

  413.      *

    414. 

  414.      *                                Associative array with the names of fields to be renamed as indexes

    415. 

  415.      *                                 of the array. The value of each entry of the array should be set to

    416. 

  416.      *                                 another associative array with the entry named name with the new

    417. 

  417.      *                                 field name and the entry named Declaration that is expected to contain

    418. 

  418.      *                                 the portion of the field declaration already in DBMS specific SQL code

    419. 

  419.      *                                 as it is used in the CREATE TABLE statement.

    420. 

  420.      *

    421. 

  421.      *                            change

    422. 

  422.      *

    423. 

  423.      *                                Associative array with the names of the fields to be changed as indexes

    424. 

  424.      *                                 of the array. Keep in mind that if it is intended to change either the

    425. 

  425.      *                                 name of a field and any other properties, the change array entries

    426. 

  426.      *                                 should have the new names of the fields as array indexes.

    427. 

  427.      *

    428. 

  428.      *                                The value of each entry of the array should be set to another associative

    429. 

  429.      *                                 array with the properties of the fields to that are meant to be changed as

    430. 

  430.      *                                 array entries. These entries should be assigned to the new values of the

    431. 

  431.      *                                 respective properties. The properties of the fields should be the same

    432. 

  432.      *                                 as defined by the MDB2 parser.

    433. 

  433.      *

    434. 

  434.      *                            Example

    435. 

  435.      *                                array(

    436. 

  436.      *                                    'name' => 'userlist',

    437. 

  437.      *                                    'add' => array(

    438. 

  438.      *                                        'quota' => array(

    439. 

  439.      *                                            'type' => 'integer',

    440. 

  440.      *                                            'unsigned' => 1

    441. 

  441.      *                                        )

    442. 

  442.      *                                    ),

    443. 

  443.      *                                    'remove' => array(

    444. 

  444.      *                                        'file_limit' => array(),

    445. 

  445.      *                                        'time_limit' => array()

    446. 

  446.      *                                    ),

    447. 

  447.      *                                    'change' => array(

    448. 

  448.      *                                        'name' => array(

    449. 

  449.      *                                            'length' => '20',

    450. 

  450.      *                                            'definition' => array(

    451. 

  451.      *                                                'type' => 'text',

    452. 

  452.      *                                                'length' => 20,

    453. 

  453.      *                                            ),

    454. 

  454.      *                                        )

    455. 

  455.      *                                    ),

    456. 

  456.      *                                    'rename' => array(

    457. 

  457.      *                                        'sex' => array(

    458. 

  458.      *                                            'name' => 'gender',

    459. 

  459.      *                                            'definition' => array(

    460. 

  460.      *                                                'type' => 'text',

    461. 

  461.      *                                                'length' => 1,

    462. 

  462.      *                                                'default' => 'M',

    463. 

  463.      *                                            ),

    464. 

  464.      *                                        )

    465. 

  465.      *                                    )

    466. 

  466.      *                                )

    467. 

  467.      *

    468. 

  468.      * @param boolean $check     indicates whether the function should just check if the DBMS driver

    469. 

  469.      *                             can perform the requested table alterations if the value is true or

    470. 

  470.      *                             actually perform them otherwise.

    471. 

  471.      * @access public

    472. 

  472.      *

    473. 

  473.       * @return mixed MDB2_OK on success, a MDB2 error on failure

    474. 

  474.      */

    475. 

  475.     function alterTable($name, $changes, $check)

    476. 

  476.     {

    477. 

  477.         $db =& $this->getDBInstance();

    478. 

  478.         if (PEAR::isError($db)) {

    479. 

  479.             return $db;

    480. 

  480.         }

    481. 

  481. 

    482. 

  482.         foreach ($changes as $change_name => $change) {

    483. 

  483.             switch ($change_name) {

    484. 

  484.             case 'add':

    485. 

  485.             case 'remove':

    486. 

  486.             case 'change':

    487. 

  487.             case 'rename':

    488. 

  488.             case 'name':

    489. 

  489.                 break;

    490. 

  490.             default:

    491. 

  491.                 return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,

    492. 

  492.                     'change type "'.$change_name.'" not yet supported', __FUNCTION__);

    493. 

  493.             }

    494. 

  494.         }

    495. 

  495. 

    496. 

  496.         if ($check) {

    497. 

  497.             return MDB2_OK;

    498. 

  498.         }

    499. 

  499. 

    500. 

  500.         $query = '';

    501. 

  501.         if (!empty($changes['name'])) {

    502. 

  502.             $change_name = $db->quoteIdentifier($changes['name'], true);

    503. 

  503.             $query .= 'RENAME TO ' . $change_name;

    504. 

  504.         }

    505. 

  505. 

    506. 

  506.         if (!empty($changes['add']) && is_array($changes['add'])) {

    507. 

  507.             foreach ($changes['add'] as $field_name => $field) {

    508. 

  508.                 if ($query) {

    509. 

  509.                     $query.= ', ';

    510. 

  510.                 }

    511. 

  511.                 $query.= 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field);

    512. 

  512.             }

    513. 

  513.         }

    514. 

  514. 

    515. 

  515.         if (!empty($changes['remove']) && is_array($changes['remove'])) {

    516. 

  516.             foreach ($changes['remove'] as $field_name => $field) {

    517. 

  517.                 if ($query) {

    518. 

  518.                     $query.= ', ';

    519. 

  519.                 }

    520. 

  520.                 $field_name = $db->quoteIdentifier($field_name, true);

    521. 

  521.                 $query.= 'DROP ' . $field_name;

    522. 

  522.             }

    523. 

  523.         }

    524. 

  524. 

    525. 

  525.         $rename = array();

    526. 

  526.         if (!empty($changes['rename']) && is_array($changes['rename'])) {

    527. 

  527.             foreach ($changes['rename'] as $field_name => $field) {

    528. 

  528.                 $rename[$field['name']] = $field_name;

    529. 

  529.             }

    530. 

  530.         }

    531. 

  531. 

    532. 

  532.         if (!empty($changes['change']) && is_array($changes['change'])) {

    533. 

  533.             foreach ($changes['change'] as $field_name => $field) {

    534. 

  534.                 if ($query) {

    535. 

  535.                     $query.= ', ';

    536. 

  536.                 }

    537. 

  537.                 if (isset($rename[$field_name])) {

    538. 

  538.                     $old_field_name = $rename[$field_name];

    539. 

  539.                     unset($rename[$field_name]);

    540. 

  540.                 } else {

    541. 

  541.                     $old_field_name = $field_name;

    542. 

  542.                 }

    543. 

  543.                 $old_field_name = $db->quoteIdentifier($old_field_name, true);

    544. 

  544.                 $query.= "CHANGE $old_field_name " . $db->getDeclaration($field['definition']['type'], $field_name, $field['definition']);

    545. 

  545.             }

    546. 

  546.         }

    547. 

  547. 

    548. 

  548.         if (!empty($rename) && is_array($rename)) {

    549. 

  549.             foreach ($rename as $rename_name => $renamed_field) {

    550. 

  550.                 if ($query) {

    551. 

  551.                     $query.= ', ';

    552. 

  552.                 }

    553. 

  553.                 $field = $changes['rename'][$renamed_field];

    554. 

  554.                 $renamed_field = $db->quoteIdentifier($renamed_field, true);

    555. 

  555.                 $query.= 'CHANGE ' . $renamed_field . ' ' . $db->getDeclaration($field['definition']['type'], $field['name'], $field['definition']);

    556. 

  556.             }

    557. 

  557.         }

    558. 

  558. 

    559. 

  559.         if (!$query) {

    560. 

  560.             return MDB2_OK;

    561. 

  561.         }

    562. 

  562. 

    563. 

  563.         $name = $db->quoteIdentifier($name, true);

    564. 

  564.         return $db->exec("ALTER TABLE $name $query");

    565. 

  565.     }

    566. 

  566. 

    567. 

  567.     // }}}

    568. 

  568.     // {{{ listDatabases()

    569. 

  569. 

    570. 

  570.     /**

    571. 

  571.      * list all databases

    572. 

  572.      *

    573. 

  573.      * @return mixed array of database names on success, a MDB2 error on failure

    574. 

  574.      * @access public

    575. 

  575.      */

    576. 

  576.     function listDatabases()

    577. 

  577.     {

    578. 

  578.         $db =& $this->getDBInstance();

    579. 

  579.         if (PEAR::isError($db)) {

    580. 

  580.             return $db;

    581. 

  581.         }

    582. 

  582. 

    583. 

  583.         $result = $db->queryCol('SHOW DATABASES');

    584. 

  584.         if (PEAR::isError($result)) {

    585. 

  585.             return $result;

    586. 

  586.         }

    587. 

  587.         if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {

    588. 

  588.             $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);

    589. 

  589.         }

    590. 

  590.         return $result;

    591. 

  591.     }

    592. 

  592. 

    593. 

  593.     // }}}

    594. 

  594.     // {{{ listUsers()

    595. 

  595. 

    596. 

  596.     /**

    597. 

  597.      * list all users

    598. 

  598.      *

    599. 

  599.      * @return mixed array of user names on success, a MDB2 error on failure

    600. 

  600.      * @access public

    601. 

  601.      */

    602. 

  602.     function listUsers()

    603. 

  603.     {

    604. 

  604.         $db =& $this->getDBInstance();

    605. 

  605.         if (PEAR::isError($db)) {

    606. 

  606.             return $db;

    607. 

  607.         }

    608. 

  608. 

    609. 

  609.         return $db->queryCol('SELECT DISTINCT USER FROM mysql.USER');

    610. 

  610.     }

    611. 

  611. 

    612. 

  612.     // }}}

    613. 

  613.     // {{{ listFunctions()

    614. 

  614. 

    615. 

  615.     /**

    616. 

  616.      * list all functions in the current database

    617. 

  617.      *

    618. 

  618.      * @return mixed array of function names on success, a MDB2 error on failure

    619. 

  619.      * @access public

    620. 

  620.      */

    621. 

  621.     function listFunctions()

    622. 

  622.     {

    623. 

  623.         $db =& $this->getDBInstance();

    624. 

  624.         if (PEAR::isError($db)) {

    625. 

  625.             return $db;

    626. 

  626.         }

    627. 

  627. 

    628. 

  628.         $query = "SELECT name FROM mysql.proc";

    629. 

  629.         /*

    630. 

  630.         SELECT ROUTINE_NAME

    631. 

  631.           FROM INFORMATION_SCHEMA.ROUTINES

    632. 

  632.          WHERE ROUTINE_TYPE = 'FUNCTION'

    633. 

  633.         */

    634. 

  634.         $result = $db->queryCol($query);

    635. 

  635.         if (PEAR::isError($result)) {

    636. 

  636.             return $result;

    637. 

  637.         }

    638. 

  638.         if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {

    639. 

  639.             $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);

    640. 

  640.         }

    641. 

  641.         return $result;

    642. 

  642.     }

    643. 

  643. 

    644. 

  644.     // }}}

    645. 

  645.     // {{{ listTableTriggers()

    646. 

  646. 

    647. 

  647.     /**

    648. 

  648.      * list all triggers in the database that reference a given table

    649. 

  649.      *

    650. 

  650.      * @param string table for which all referenced triggers should be found

    651. 

  651.      * @return mixed array of trigger names on success, a MDB2 error on failure

    652. 

  652.      * @access public

    653. 

  653.      */

    654. 

  654.     function listTableTriggers($table = null)

    655. 

  655.     {

    656. 

  656.         $db =& $this->getDBInstance();

    657. 

  657.         if (PEAR::isError($db)) {

    658. 

  658.             return $db;

    659. 

  659.         }

    660. 

  660. 

    661. 

  661.         $query = 'SHOW TRIGGERS';

    662. 

  662.         if (!is_null($table)) {

    663. 

  663.             $table = $db->quote($table, 'text');

    664. 

  664.             $query .= " LIKE $table";

    665. 

  665.         }

    666. 

  666.         $result = $db->queryCol($query);

    667. 

  667.         if (PEAR::isError($result)) {

    668. 

  668.             return $result;

    669. 

  669.         }

    670. 

  670.         if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {

    671. 

  671.             $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);

    672. 

  672.         }

    673. 

  673.         return $result;

    674. 

  674.     }

    675. 

  675. 

    676. 

  676.     // }}}

    677. 

  677.     // {{{ listTables()

    678. 

  678. 

    679. 

  679.     /**

    680. 

  680.      * list all tables in the current database

    681. 

  681.      *

    682. 

  682.      * @param string database, the current is default

    683. 

  683.      * @return mixed array of table names on success, a MDB2 error on failure

    684. 

  684.      * @access public

    685. 

  685.      */

    686. 

  686.     function listTables($database = null)

    687. 

  687.     {

    688. 

  688.         $db =& $this->getDBInstance();

    689. 

  689.         if (PEAR::isError($db)) {

    690. 

  690.             return $db;

    691. 

  691.         }

    692. 

  692. 

    693. 

  693.         $query = "SHOW /*!50002 FULL*/ TABLES";

    694. 

  694.         if (!is_null($database)) {

    695. 

  695.             $query .= " FROM $database";

    696. 

  696.         }

    697. 

  697.         $query.= "/*!50002  WHERE Table_type = 'BASE TABLE'*/";

    698. 

  698. 

    699. 

  699.         $table_names = $db->queryAll($query, null, MDB2_FETCHMODE_ORDERED);

    700. 

  700.         if (PEAR::isError($table_names)) {

    701. 

  701.             return $table_names;

    702. 

  702.         }

    703. 

  703. 

    704. 

  704.         $result = array();

    705. 

  705.         foreach ($table_names as $table) {

    706. 

  706.             if (!$this->_fixSequenceName($table[0], true)) {

    707. 

  707.                 $result[] = $table[0];

    708. 

  708.             }

    709. 

  709.         }

    710. 

  710.         if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {

    711. 

  711.             $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);

    712. 

  712.         }

    713. 

  713.         return $result;

    714. 

  714.     }

    715. 

  715. 

    716. 

  716.     // }}}

    717. 

  717.     // {{{ listViews()

    718. 

  718. 

    719. 

  719.     /**

    720. 

  720.      * list all views in the current database

    721. 

  721.      *

    722. 

  722.      * @param string database, the current is default

    723. 

  723.      * @return mixed array of view names on success, a MDB2 error on failure

    724. 

  724.      * @access public

    725. 

  725.      */

    726. 

  726.     function listViews($database = null)

    727. 

  727.     {

    728. 

  728.         $db =& $this->getDBInstance();

    729. 

  729.         if (PEAR::isError($db)) {

    730. 

  730.             return $db;

    731. 

  731.         }

    732. 

  732. 

    733. 

  733.         $query = 'SHOW FULL TABLES';

    734. 

  734.         if (!is_null($database)) {

    735. 

  735.             $query.= " FROM $database";

    736. 

  736.         }

    737. 

  737.         $query.= " WHERE Table_type = 'VIEW'";

    738. 

  738. 

    739. 

  739.         $result = $db->queryCol($query);

    740. 

  740.         if (PEAR::isError($result)) {

    741. 

  741.             return $result;

    742. 

  742.         }

    743. 

  743. 

    744. 

  744.         if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {

    745. 

  745.             $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);

    746. 

  746.         }

    747. 

  747.         return $result;

    748. 

  748.     }

    749. 

  749. 

    750. 

  750.     // }}}

    751. 

  751.     // {{{ listTableFields()

    752. 

  752. 

    753. 

  753.     /**

    754. 

  754.      * list all fields in a table in the current database

    755. 

  755.      *

    756. 

  756.      * @param string $table name of table that should be used in method

    757. 

  757.      * @return mixed array of field names on success, a MDB2 error on failure

    758. 

  758.      * @access public

    759. 

  759.      */

    760. 

  760.     function listTableFields($table)

    761. 

  761.     {

    762. 

  762.         $db =& $this->getDBInstance();

    763. 

  763.         if (PEAR::isError($db)) {

    764. 

  764.             return $db;

    765. 

  765.         }

    766. 

  766. 

    767. 

  767.         $table = $db->quoteIdentifier($table, true);

    768. 

  768.         $result = $db->queryCol("SHOW COLUMNS FROM $table");

    769. 

  769.         if (PEAR::isError($result)) {

    770. 

  770.             return $result;

    771. 

  771.         }

    772. 

  772.         if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {

    773. 

  773.             $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);

    774. 

  774.         }

    775. 

  775.         return $result;

    776. 

  776.     }

    777. 

  777. 

    778. 

  778.     // }}}

    779. 

  779.     // {{{ createIndex()

    780. 

  780. 

    781. 

  781.     /**

    782. 

  782.      * Get the stucture of a field into an array

    783. 

  783.      *

    784. 

  784.      * @author Leoncx

    785. 

  785.      * @param string    $table         name of the table on which the index is to be created

    786. 

  786.      * @param string    $name         name of the index to be created

    787. 

  787.      * @param array     $definition        associative array that defines properties of the index to be created.

    788. 

  788.      *                                 Currently, only one property named FIELDS is supported. This property

    789. 

  789.      *                                 is also an associative with the names of the index fields as array

    790. 

  790.      *                                 indexes. Each entry of this array is set to another type of associative

    791. 

  791.      *                                 array that specifies properties of the index that are specific to

    792. 

  792.      *                                 each field.

    793. 

  793.      *

    794. 

  794.      *                                Currently, only the sorting property is supported. It should be used

    795. 

  795.      *                                 to define the sorting direction of the index. It may be set to either

    796. 

  796.      *                                 ascending or descending.

    797. 

  797.      *

    798. 

  798.      *                                Not all DBMS support index sorting direction configuration. The DBMS

    799. 

  799.      *                                 drivers of those that do not support it ignore this property. Use the

    800. 

  800.      *                                 function supports() to determine whether the DBMS driver can manage indexes.

    801. 

  801.      *

    802. 

  802.      *                                 Example

    803. 

  803.      *                                    array(

    804. 

  804.      *                                        'fields' => array(

    805. 

  805.      *                                            'user_name' => array(

    806. 

  806.      *                                                'sorting' => 'ascending'

    807. 

  807.      *                                                'length' => 10

    808. 

  808.      *                                            ),

    809. 

  809.      *                                            'last_login' => array()

    810. 

  810.      *                                        )

    811. 

  811.      *                                    )

    812. 

  812.      *

    813. 

  813.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    814. 

  814.      * @access public

    815. 

  815.      */

    816. 

  816.     function createIndex($table, $name, $definition)

    817. 

  817.     {

    818. 

  818.         $db =& $this->getDBInstance();

    819. 

  819.         if (PEAR::isError($db)) {

    820. 

  820.             return $db;

    821. 

  821.         }

    822. 

  822. 

    823. 

  823.         $table = $db->quoteIdentifier($table, true);

    824. 

  824.         $name = $db->quoteIdentifier($db->getIndexName($name), true);

    825. 

  825.         $query = "CREATE INDEX $name ON $table";

    826. 

  826.         $fields = array();

    827. 

  827.         foreach ($definition['fields'] as $field => $fieldinfo) {

    828. 

  828.             if (!empty($fieldinfo['length'])) {

    829. 

  829.                 $fields[] = $db->quoteIdentifier($field, true) . '(' . $fieldinfo['length'] . ')';

    830. 

  830.             } else {

    831. 

  831.                 $fields[] = $db->quoteIdentifier($field, true);

    832. 

  832.             }

    833. 

  833.         }

    834. 

  834.         $query .= ' ('. implode(', ', $fields) . ')';

    835. 

  835.         return $db->exec($query);

    836. 

  836.     }

    837. 

  837. 

    838. 

  838.     // }}}

    839. 

  839.     // {{{ dropIndex()

    840. 

  840. 

    841. 

  841.     /**

    842. 

  842.      * drop existing index

    843. 

  843.      *

    844. 

  844.      * @param string    $table         name of table that should be used in method

    845. 

  845.      * @param string    $name         name of the index to be dropped

    846. 

  846.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    847. 

  847.      * @access public

    848. 

  848.      */

    849. 

  849.     function dropIndex($table, $name)

    850. 

  850.     {

    851. 

  851.         $db =& $this->getDBInstance();

    852. 

  852.         if (PEAR::isError($db)) {

    853. 

  853.             return $db;

    854. 

  854.         }

    855. 

  855. 

    856. 

  856.         $table = $db->quoteIdentifier($table, true);

    857. 

  857.         $name = $db->quoteIdentifier($db->getIndexName($name), true);

    858. 

  858.         return $db->exec("DROP INDEX $name ON $table");

    859. 

  859.     }

    860. 

  860. 

    861. 

  861.     // }}}

    862. 

  862.     // {{{ listTableIndexes()

    863. 

  863. 

    864. 

  864.     /**

    865. 

  865.      * list all indexes in a table

    866. 

  866.      *

    867. 

  867.      * @param string $table name of table that should be used in method

    868. 

  868.      * @return mixed array of index names on success, a MDB2 error on failure

    869. 

  869.      * @access public

    870. 

  870.      */

    871. 

  871.     function listTableIndexes($table)

    872. 

  872.     {

    873. 

  873.         $db =& $this->getDBInstance();

    874. 

  874.         if (PEAR::isError($db)) {

    875. 

  875.             return $db;

    876. 

  876.         }

    877. 

  877. 

    878. 

  878.         $key_name = 'Key_name';

    879. 

  879.         $non_unique = 'Non_unique';

    880. 

  880.         if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {

    881. 

  881.             if ($db->options['field_case'] == CASE_LOWER) {

    882. 

  882.                 $key_name = strtolower($key_name);

    883. 

  883.                 $non_unique = strtolower($non_unique);

    884. 

  884.             } else {

    885. 

  885.                 $key_name = strtoupper($key_name);

    886. 

  886.                 $non_unique = strtoupper($non_unique);

    887. 

  887.             }

    888. 

  888.         }

    889. 

  889. 

    890. 

  890.         $table = $db->quoteIdentifier($table, true);

    891. 

  891.         $query = "SHOW INDEX FROM $table";

    892. 

  892.         $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC);

    893. 

  893.         if (PEAR::isError($indexes)) {

    894. 

  894.             return $indexes;

    895. 

  895.         }

    896. 

  896. 

    897. 

  897.         $result = array();

    898. 

  898.         foreach ($indexes as $index_data) {

    899. 

  899.             if ($index_data[$non_unique] && ($index = $this->_fixIndexName($index_data[$key_name]))) {

    900. 

  900.                 $result[$index] = true;

    901. 

  901.             }

    902. 

  902.         }

    903. 

  903. 

    904. 

  904.         if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {

    905. 

  905.             $result = array_change_key_case($result, $db->options['field_case']);

    906. 

  906.         }

    907. 

  907.         return array_keys($result);

    908. 

  908.     }

    909. 

  909. 

    910. 

  910.     // }}}

    911. 

  911.     // {{{ createConstraint()

    912. 

  912. 

    913. 

  913.     /**

    914. 

  914.      * create a constraint on a table

    915. 

  915.      *

    916. 

  916.      * @param string    $table         name of the table on which the constraint is to be created

    917. 

  917.      * @param string    $name         name of the constraint to be created

    918. 

  918.      * @param array     $definition        associative array that defines properties of the constraint to be created.

    919. 

  919.      *                                 Currently, only one property named FIELDS is supported. This property

    920. 

  920.      *                                 is also an associative with the names of the constraint fields as array

    921. 

  921.      *                                 constraints. Each entry of this array is set to another type of associative

    922. 

  922.      *                                 array that specifies properties of the constraint that are specific to

    923. 

  923.      *                                 each field.

    924. 

  924.      *

    925. 

  925.      *                                 Example

    926. 

  926.      *                                    array(

    927. 

  927.      *                                        'fields' => array(

    928. 

  928.      *                                            'user_name' => array(),

    929. 

  929.      *                                            'last_login' => array()

    930. 

  930.      *                                        )

    931. 

  931.      *                                    )

    932. 

  932.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    933. 

  933.      * @access public

    934. 

  934.      */

    935. 

  935.     function createConstraint($table, $name, $definition)

    936. 

  936.     {

    937. 

  937.         $db =& $this->getDBInstance();

    938. 

  938.         if (PEAR::isError($db)) {

    939. 

  939.             return $db;

    940. 

  940.         }

    941. 

  941. 

    942. 

  942.         $type = '';

    943. 

  943.         $idx_name = $db->quoteIdentifier($db->getIndexName($name), true);

    944. 

  944.         if (!empty($definition['primary'])) {

    945. 

  945.             $type = 'PRIMARY';

    946. 

  946.             $idx_name = 'KEY';

    947. 

  947.         } elseif (!empty($definition['unique'])) {

    948. 

  948.             $type = 'UNIQUE';

    949. 

  949.         } elseif (!empty($definition['foreign'])) {

    950. 

  950.             $type = 'CONSTRAINT';

    951. 

  951.         }

    952. 

  952.         if (empty($type)) {

    953. 

  953.             return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,

    954. 

  954.                 'invalid definition, could not create constraint', __FUNCTION__);

    955. 

  955.         }

    956. 

  956. 

    957. 

  957.         $table_quoted = $db->quoteIdentifier($table, true);

    958. 

  958.         $query = "ALTER TABLE $table_quoted ADD $type $idx_name";

    959. 

  959.         if (!empty($definition['foreign'])) {

    960. 

  960.             $query .= ' FOREIGN KEY';

    961. 

  961.         }

    962. 

  962.         $fields = array();

    963. 

  963.         foreach ($definition['fields'] as $field => $fieldinfo) {

    964. 

  964.             $quoted = $db->quoteIdentifier($field, true);

    965. 

  965.             if (!empty($fieldinfo['length'])) {

    966. 

  966.                 $quoted .= '(' . $fieldinfo['length'] . ')';

    967. 

  967.             }

    968. 

  968.             $fields[] = $quoted;

    969. 

  969.         }

    970. 

  970.         $query .= ' ('. implode(', ', $fields) . ')';

    971. 

  971.         if (!empty($definition['foreign'])) {

    972. 

  972.             $query.= ' REFERENCES ' . $db->quoteIdentifier($definition['references']['table'], true);

    973. 

  973.             $referenced_fields = array();

    974. 

  974.             foreach (array_keys($definition['references']['fields']) as $field) {

    975. 

  975.                 $referenced_fields[] = $db->quoteIdentifier($field, true);

    976. 

  976.             }

    977. 

  977.             $query .= ' ('. implode(', ', $referenced_fields) . ')';

    978. 

  978.             $query .= $this->_getAdvancedFKOptions($definition);

    979. 

  979. 

    980. 

  980.             // add index on FK column(s) or we can't add a FK constraint

    981. 

  981.             // @see http://forums.mysql.com/read.php?22,19755,226009

    982. 

  982.             $result = $this->createIndex($table, $name.'_fkidx', $definition);

    983. 

  983.             if (PEAR::isError($result)) {

    984. 

  984.                 return $result;

    985. 

  985.             }

    986. 

  986.         }

    987. 

  987.         $res = $db->exec($query);

    988. 

  988.         if (PEAR::isError($res)) {

    989. 

  989.             return $res;

    990. 

  990.         }

    991. 

  991.         if (!empty($definition['foreign'])) {

    992. 

  992.             return $this->_createFKTriggers($table, array($name => $definition));

    993. 

  993.         }

    994. 

  994.         return MDB2_OK;

    995. 

  995.     }

    996. 

  996. 

    997. 

  997.     // }}}

    998. 

  998.     // {{{ dropConstraint()

    999. 

  999. 

    1000. 

  1000.     /**

    1001. 

  1001.      * drop existing constraint

    1002. 

  1002.      *

    1003. 

  1003.      * @param string    $table        name of table that should be used in method

    1004. 

  1004.      * @param string    $name         name of the constraint to be dropped

    1005. 

  1005.      * @param string    $primary      hint if the constraint is primary

    1006. 

  1006.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    1007. 

  1007.      * @access public

    1008. 

  1008.      */

    1009. 

  1009.     function dropConstraint($table, $name, $primary = false)

    1010. 

  1010.     {

    1011. 

  1011.         $db =& $this->getDBInstance();

    1012. 

  1012.         if (PEAR::isError($db)) {

    1013. 

  1013.             return $db;

    1014. 

  1014.         }

    1015. 

  1015.         

    1016. 

  1016.         if ($primary || strtolower($name) == 'primary') {

    1017. 

  1017.             $query = 'ALTER TABLE '. $db->quoteIdentifier($table, true) .' DROP PRIMARY KEY';

    1018. 

  1018.             return $db->exec($query);

    1019. 

  1019.         }

    1020. 

  1020.         

    1021. 

  1021.         //is it a FK constraint? If so, also delete the associated triggers

    1022. 

  1022.         $db->loadModule('Reverse', null, true);

    1023. 

  1023.         $definition = $db->reverse->getTableConstraintDefinition($table, $name);

    1024. 

  1024.         if (!PEAR::isError($definition) && !empty($definition['foreign'])) {

    1025. 

  1025.             //first drop the FK enforcing triggers

    1026. 

  1026.             $result = $this->_dropFKTriggers($table, $name, $definition['references']['table']);

    1027. 

  1027.             if (PEAR::isError($result)) {

    1028. 

  1028.                 return $result;

    1029. 

  1029.             }

    1030. 

  1030.             //then drop the constraint itself

    1031. 

  1031.             $table = $db->quoteIdentifier($table, true);

    1032. 

  1032.             $name = $db->quoteIdentifier($db->getIndexName($name), true);

    1033. 

  1033.             $query = "ALTER TABLE $table DROP FOREIGN KEY $name";

    1034. 

  1034.             return $db->exec($query);

    1035. 

  1035.         }

    1036. 

  1036. 

    1037. 

  1037.         $table = $db->quoteIdentifier($table, true);

    1038. 

  1038.         $name = $db->quoteIdentifier($db->getIndexName($name), true);

    1039. 

  1039.         $query = "ALTER TABLE $table DROP INDEX $name";

    1040. 

  1040.         return $db->exec($query);

    1041. 

  1041.     }

    1042. 

  1042. 

    1043. 

  1043.     // }}}

    1044. 

  1044.     // {{{ _createFKTriggers()

    1045. 

  1045. 

    1046. 

  1046.     /**

    1047. 

  1047.      * Create triggers to enforce the FOREIGN KEY constraint on the table

    1048. 

  1048.      *

    1049. 

  1049.      * NB: since there's no RAISE_APPLICATION_ERROR facility in mysql,

    1050. 

  1050.      * we call a non-existent procedure to raise the FK violation message.

    1051. 

  1051.      * @see http://forums.mysql.com/read.php?99,55108,71877#msg-71877

    1052. 

  1052.      *

    1053. 

  1053.      * @param string $table        table name

    1054. 

  1054.      * @param array  $foreign_keys FOREIGN KEY definitions

    1055. 

  1055.      *

    1056. 

  1056.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    1057. 

  1057.      * @access private

    1058. 

  1058.      */

    1059. 

  1059.     function _createFKTriggers($table, $foreign_keys)

    1060. 

  1060.     {

    1061. 

  1061.         $db =& $this->getDBInstance();

    1062. 

  1062.         if (PEAR::isError($db)) {

    1063. 

  1063.             return $db;

    1064. 

  1064.         }

    1065. 

  1065.         // create triggers to enforce FOREIGN KEY constraints

    1066. 

  1066.         if ($db->supports('triggers') && !empty($foreign_keys)) {

    1067. 

  1067.             $table_quoted = $db->quoteIdentifier($table, true);

    1068. 

  1068.             foreach ($foreign_keys as $fkname => $fkdef) {

    1069. 

  1069.                 if (empty($fkdef)) {

    1070. 

  1070.                     continue;

    1071. 

  1071.                 }

    1072. 

  1072.                 //set actions to default if not set

    1073. 

  1073.                 $fkdef['onupdate'] = empty($fkdef['onupdate']) ? $db->options['default_fk_action_onupdate'] : strtoupper($fkdef['onupdate']);

    1074. 

  1074.                 $fkdef['ondelete'] = empty($fkdef['ondelete']) ? $db->options['default_fk_action_ondelete'] : strtoupper($fkdef['ondelete']);

    1075. 

  1075. 

    1076. 

  1076.                 $trigger_names = array(

    1077. 

  1077.                     'insert'    => $fkname.'_insert_trg',

    1078. 

  1078.                     'update'    => $fkname.'_update_trg',

    1079. 

  1079.                     'pk_update' => $fkname.'_pk_update_trg',

    1080. 

  1080.                     'pk_delete' => $fkname.'_pk_delete_trg',

    1081. 

  1081.                 );

    1082. 

  1082.                 $table_fields = array_keys($fkdef['fields']);

    1083. 

  1083.                 $referenced_fields = array_keys($fkdef['references']['fields']);

    1084. 

  1084. 

    1085. 

  1085.                 //create the ON [UPDATE|DELETE] triggers on the primary table

    1086. 

  1086.                 $restrict_action = ' IF (SELECT ';

    1087. 

  1087.                 $aliased_fields = array();

    1088. 

  1088.                 foreach ($table_fields as $field) {

    1089. 

  1089.                     $aliased_fields[] = $table_quoted .'.'.$field .' AS '.$field;

    1090. 

  1090.                 }

    1091. 

  1091.                 $restrict_action .= implode(',', $aliased_fields)

    1092. 

  1092.                        .' FROM '.$table_quoted

    1093. 

  1093.                        .' WHERE ';

    1094. 

  1094.                 $conditions  = array();

    1095. 

  1095.                 $new_values  = array();

    1096. 

  1096.                 $null_values = array();

    1097. 

  1097.                 for ($i=0; $i<count($table_fields); $i++) {

    1098. 

  1098.                     $conditions[]  = $table_fields[$i] .' = OLD.'.$referenced_fields[$i];

    1099. 

  1099.                     $new_values[]  = $table_fields[$i] .' = NEW.'.$referenced_fields[$i];

    1100. 

  1100.                     $null_values[] = $table_fields[$i] .' = NULL';

    1101. 

  1101.                 }

    1102. 

  1102.                 $conditions2 = array();

    1103. 

  1103.                 for ($i=0; $i<count($referenced_fields); $i++) {

    1104. 

  1104.                     $conditions2[]  = 'NEW.'.$referenced_fields[$i] .' <> OLD.'.$referenced_fields[$i];

    1105. 

  1105.                 }

    1106. 

  1106.                 $restrict_action .= implode(' AND ', $conditions).') IS NOT NULL'

    1107. 

  1107.                                 .' AND (' .implode(' OR ', $conditions2) .')'

    1108. 

  1108.                                 .' THEN CALL %s_ON_TABLE_'.$table.'_VIOLATES_FOREIGN_KEY_CONSTRAINT();'

    1109. 

  1109.                                 .' END IF;';

    1110. 

  1110. 

    1111. 

  1111.                 $cascade_action_update = 'UPDATE '.$table_quoted.' SET '.implode(', ', $new_values) .' WHERE '.implode(' AND ', $conditions). ';';

    1112. 

  1112.                 $cascade_action_delete = 'DELETE FROM '.$table_quoted.' WHERE '.implode(' AND ', $conditions). ';';

    1113. 

  1113.                 $setnull_action        = 'UPDATE '.$table_quoted.' SET '.implode(', ', $null_values).' WHERE '.implode(' AND ', $conditions). ';';

    1114. 

  1114. 

    1115. 

  1115.                 if ('SET DEFAULT' == $fkdef['onupdate'] || 'SET DEFAULT' == $fkdef['ondelete']) {

    1116. 

  1116.                     $db->loadModule('Reverse', null, true);

    1117. 

  1117.                     $default_values = array();

    1118. 

  1118.                     foreach ($table_fields as $table_field) {

    1119. 

  1119.                         $field_definition = $db->reverse->getTableFieldDefinition($table, $field);

    1120. 

  1120.                         if (PEAR::isError($field_definition)) {

    1121. 

  1121.                             return $field_definition;

    1122. 

  1122.                         }

    1123. 

  1123.                         $default_values[] = $table_field .' = '. $field_definition[0]['default'];

    1124. 

  1124.                     }

    1125. 

  1125.                     $setdefault_action = 'UPDATE '.$table_quoted.' SET '.implode(', ', $default_values).' WHERE '.implode(' AND ', $conditions). ';';

    1126. 

  1126.                 }

    1127. 

  1127. 

    1128. 

  1128.                 $query = 'CREATE TRIGGER %s'

    1129. 

  1129.                         .' %s ON '.$fkdef['references']['table']

    1130. 

  1130.                         .' FOR EACH ROW BEGIN '

    1131. 

  1131.                         .' SET FOREIGN_KEY_CHECKS = 0; ';  //only really needed for ON UPDATE CASCADE

    1132. 

  1132. 

    1133. 

  1133.                 if ('CASCADE' == $fkdef['onupdate']) {

    1134. 

  1134.                     $sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE',  'update') . $cascade_action_update;

    1135. 

  1135.                 } elseif ('SET NULL' == $fkdef['onupdate']) {

    1136. 

  1136.                     $sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . $setnull_action;

    1137. 

  1137.                 } elseif ('SET DEFAULT' == $fkdef['onupdate']) {

    1138. 

  1138.                     $sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . $setdefault_action;

    1139. 

  1139.                 } elseif ('NO ACTION' == $fkdef['onupdate']) {

    1140. 

  1140.                     $sql_update = sprintf($query.$restrict_action, $trigger_names['pk_update'], 'AFTER UPDATE', 'update');

    1141. 

  1141.                 } elseif ('RESTRICT' == $fkdef['onupdate']) {

    1142. 

  1142.                     $sql_update = sprintf($query.$restrict_action, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update');

    1143. 

  1143.                 }

    1144. 

  1144.                 if ('CASCADE' == $fkdef['ondelete']) {

    1145. 

  1145.                     $sql_delete = sprintf($query, $trigger_names['pk_delete'], 'BEFORE DELETE',  'delete') . $cascade_action_delete;

    1146. 

  1146.                 } elseif ('SET NULL' == $fkdef['ondelete']) {

    1147. 

  1147.                     $sql_delete = sprintf($query, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete') . $setnull_action;

    1148. 

  1148.                 } elseif ('SET DEFAULT' == $fkdef['ondelete']) {

    1149. 

  1149.                     $sql_delete = sprintf($query, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete') . $setdefault_action;

    1150. 

  1150.                 } elseif ('NO ACTION' == $fkdef['ondelete']) {

    1151. 

  1151.                     $sql_delete = sprintf($query.$restrict_action, $trigger_names['pk_delete'], 'AFTER DELETE', 'delete');

    1152. 

  1152.                 } elseif ('RESTRICT' == $fkdef['ondelete']) {

    1153. 

  1153.                     $sql_delete = sprintf($query.$restrict_action, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete');

    1154. 

  1154.                 }

    1155. 

  1155.                 $sql_update .= ' SET FOREIGN_KEY_CHECKS = 1; END;';

    1156. 

  1156.                 $sql_delete .= ' SET FOREIGN_KEY_CHECKS = 1; END;';

    1157. 

  1157. 

    1158. 

  1158.                 $db->pushErrorHandling(PEAR_ERROR_RETURN);

    1159. 

  1159.                 $db->expectError(MDB2_ERROR_CANNOT_CREATE); 

    1160. 

  1160.                 $result = $db->exec($sql_delete);

    1161. 

  1161.                 $expected_errmsg = 'This MySQL version doesn\'t support multiple triggers with the same action time and event for one table';

    1162. 

  1162.                 $db->popExpect();

    1163. 

  1163.                 $db->popErrorHandling();

    1164. 

  1164.                 if (PEAR::isError($result)) {

    1165. 

  1165.                     if ($result->getCode() != MDB2_ERROR_CANNOT_CREATE) {

    1166. 

  1166.                         return $result;

    1167. 

  1167.                     }

    1168. 

  1168.                     $db->warnings[] = $expected_errmsg;

    1169. 

  1169.                 }

    1170. 

  1170.                 $db->pushErrorHandling(PEAR_ERROR_RETURN);

    1171. 

  1171.                 $db->expectError(MDB2_ERROR_CANNOT_CREATE);

    1172. 

  1172.                 $result = $db->exec($sql_update);

    1173. 

  1173.                 $db->popExpect();

    1174. 

  1174.                 $db->popErrorHandling();

    1175. 

  1175.                 if (PEAR::isError($result) && $result->getCode() != MDB2_ERROR_CANNOT_CREATE) {

    1176. 

  1176.                     if ($result->getCode() != MDB2_ERROR_CANNOT_CREATE) {

    1177. 

  1177.                         return $result;

    1178. 

  1178.                     }

    1179. 

  1179.                     $db->warnings[] = $expected_errmsg;

    1180. 

  1180.                 }

    1181. 

  1181.             }

    1182. 

  1182.         }

    1183. 

  1183.         return MDB2_OK;

    1184. 

  1184.     }

    1185. 

  1185. 

    1186. 

  1186.     // }}}

    1187. 

  1187.     // {{{ _dropFKTriggers()

    1188. 

  1188. 

    1189. 

  1189.     /**

    1190. 

  1190.      * Drop the triggers created to enforce the FOREIGN KEY constraint on the table

    1191. 

  1191.      *

    1192. 

  1192.      * @param string $table            table name

    1193. 

  1193.      * @param string $fkname           FOREIGN KEY constraint name

    1194. 

  1194.      * @param string $referenced_table referenced table name

    1195. 

  1195.      *

    1196. 

  1196.      * @return mixed MDB2_OK on success, a MDB2 error on failure

    1197. 

  1197.      * @access private

    1198. 

  1198.      */

    1199. 

  1199.     function _dropFKTriggers($table, $fkname, $referenced_table)

    1200. 

  1200.     {

    1201. 

  1201.         $db =& $this->getDBInstance();

    1202. 

  1202.         if (PEAR::isError($db)) {

    1203. 

  1203.             return $db;

    1204. 

  1204.         }

    1205. 

  1205. 

    1206. 

  1206.         $triggers  = $this->listTableTriggers($table);

    1207. 

  1207.         $triggers2 = $this->listTableTriggers($referenced_table);

    1208. 

  1208.         if (!PEAR::isError($triggers2) && !PEAR::isError($triggers)) {

    1209. 

  1209.             $triggers = array_merge($triggers, $triggers2);

    1210. 

  1210.             $pattern = '/^'.$fkname.'(_pk)?_(insert|update|delete)_trg$/i';

    1211. 

  1211.             foreach ($triggers as $tr…
    1212.

Large files files are truncated, but you can click here to view the full file