PageRenderTime 74ms CodeModel.GetById 44ms RepoModel.GetById 0ms app.codeStats 0ms

/extlib/DB/Table.php

https://bitbucket.org/stk2k/charcoalphp2.1
PHP | 2649 lines | 1088 code | 350 blank | 1211 comment | 182 complexity | c902540e8ec6c8fb2e6602558a478f96 MD5 | raw file

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

  1. <?php
  2. /**
  3. *
  4. * DB_Table is a database API and data type SQL abstraction class.
  5. *
  6. * DB_Table provides database API abstraction, data type abstraction,
  7. * automated SELECT, INSERT, and UPDATE queries, automated table
  8. * creation, automated validation of inserted/updated column values,
  9. * and automated creation of QuickForm elements based on the column
  10. * definitions.
  11. *
  12. * @category DB
  13. *
  14. * @package DB_Table
  15. *
  16. * @author Paul M. Jones <pmjones@php.net>
  17. * @author Mark Wiesemann <wiesemann@php.net>
  18. *
  19. * @license http://www.gnu.org/copyleft/lesser.html LGPL
  20. *
  21. * @version $Id: Table.php,v 1.69 2006/11/09 20:56:30 wiesemann Exp $
  22. *
  23. */
  24. /**
  25. * Error code at instantiation time when the first parameter to the
  26. * constructor is not a PEAR DB object.
  27. */
  28. define('DB_TABLE_ERR_NOT_DB_OBJECT', -1);
  29. /**
  30. * Error code at instantiation time when the PEAR DB/MDB2 $phptype is not
  31. * supported by DB_Table.
  32. */
  33. define('DB_TABLE_ERR_PHPTYPE', -2);
  34. /**
  35. * Error code when you call select() or selectResult() and the first
  36. * parameter does not match any of the $this->sql keys.
  37. */
  38. define('DB_TABLE_ERR_SQL_UNDEF', -3);
  39. /**
  40. * Error code when you try to insert data to a column that is not in the
  41. * $this->col array.
  42. */
  43. define('DB_TABLE_ERR_INS_COL_NOMAP', -4);
  44. /**
  45. * Error code when you try to insert data, and that data does not have a
  46. * column marked as 'require' in the $this->col array.
  47. */
  48. define('DB_TABLE_ERR_INS_COL_REQUIRED', -5);
  49. /**
  50. * Error code when auto-validation fails on data to be inserted.
  51. */
  52. define('DB_TABLE_ERR_INS_DATA_INVALID', -6);
  53. /**
  54. * Error code when you try to update data to a column that is not in the
  55. * $this->col array.
  56. */
  57. define('DB_TABLE_ERR_UPD_COL_NOMAP', -7);
  58. /**
  59. * Error code when you try to update data, and that data does not have a
  60. * column marked as 'require' in the $this->col array.
  61. */
  62. define('DB_TABLE_ERR_UPD_COL_REQUIRED', -8);
  63. /**
  64. * Error code when auto-validation fails on update data.
  65. */
  66. define('DB_TABLE_ERR_UPD_DATA_INVALID', -9);
  67. /**
  68. * Error code when you use a create() flag that is not recognized (must
  69. * be 'safe', 'drop', 'verify' or boolean false.
  70. */
  71. define('DB_TABLE_ERR_CREATE_FLAG', -10);
  72. /**
  73. * Error code at create() time when you define an index in $this->idx
  74. * that has no columns.
  75. */
  76. define('DB_TABLE_ERR_IDX_NO_COLS', -11);
  77. /**
  78. * Error code at create() time when you define an index in $this->idx
  79. * that refers to a column that does not exist in the $this->col array.
  80. */
  81. define('DB_TABLE_ERR_IDX_COL_UNDEF', -12);
  82. /**
  83. * Error code at create() time when you define a $this->idx index type
  84. * that is not recognized (must be 'normal' or 'unique').
  85. */
  86. define('DB_TABLE_ERR_IDX_TYPE', -13);
  87. /**
  88. * Error code at create() time when you have an error in a 'char' or
  89. * 'varchar' definition in $this->col (usually because 'size' is wrong).
  90. */
  91. define('DB_TABLE_ERR_DECLARE_STRING', -14);
  92. /**
  93. * Error code at create() time when you have an error in a 'decimal'
  94. * definition (usually becuase the 'size' or 'scope' are wrong).
  95. */
  96. define('DB_TABLE_ERR_DECLARE_DECIMAL', -15);
  97. /**
  98. * Error code at create() time when you define a column in $this->col
  99. * with an unrecognized 'type'.
  100. */
  101. define('DB_TABLE_ERR_DECLARE_TYPE', -16);
  102. /**
  103. * Error code at validation time when a column in $this->col has an
  104. * unrecognized 'type'.
  105. */
  106. define('DB_TABLE_ERR_VALIDATE_TYPE', -17);
  107. /**
  108. * Error code at create() time when you define a column in $this->col
  109. * with an invalid column name (usually because it's a reserved keyword).
  110. */
  111. define('DB_TABLE_ERR_DECLARE_COLNAME', -18);
  112. /**
  113. * Error code at create() time when you define an index in $this->idx
  114. * with an invalid index name (usually because it's a reserved keyword).
  115. */
  116. define('DB_TABLE_ERR_DECLARE_IDXNAME', -19);
  117. /**
  118. * Error code at create() time when you define an index in $this->idx
  119. * that refers to a CLOB column.
  120. */
  121. define('DB_TABLE_ERR_IDX_COL_CLOB', -20);
  122. /**
  123. * Error code at create() time when you define a column name that is
  124. * more than 30 chars long (an Oracle restriction).
  125. */
  126. define('DB_TABLE_ERR_DECLARE_STRLEN', -21);
  127. /**
  128. * Error code at create() time when the index name ends up being more
  129. * than 30 chars long (an Oracle restriction).
  130. */
  131. define('DB_TABLE_ERR_IDX_STRLEN', -22);
  132. /**
  133. * Error code at create() time when the table name is more than 30 chars
  134. * long (an Oracle restriction).
  135. */
  136. define('DB_TABLE_ERR_TABLE_STRLEN', -23);
  137. /**
  138. * Error code at nextID() time when the sequence name is more than 30
  139. * chars long (an Oracle restriction).
  140. */
  141. define('DB_TABLE_ERR_SEQ_STRLEN', -24);
  142. /**
  143. * Error code at verify() time when the table does not exist in the
  144. * database.
  145. */
  146. define('DB_TABLE_ERR_VER_TABLE_MISSING', -25);
  147. /**
  148. * Error code at verify() time when the column does not exist in the
  149. * database table.
  150. */
  151. define('DB_TABLE_ERR_VER_COLUMN_MISSING', -26);
  152. /**
  153. * Error code at verify() time when the column type does not match the
  154. * type specified in the column declaration.
  155. */
  156. define('DB_TABLE_ERR_VER_COLUMN_TYPE', -27);
  157. /**
  158. * Error code at instantiation time when the column definition array
  159. * does not contain at least one column.
  160. */
  161. define('DB_TABLE_ERR_NO_COLS', -28);
  162. /**
  163. * Error code at verify() time when an index cannot be found in the
  164. * database table.
  165. */
  166. define('DB_TABLE_ERR_VER_IDX_MISSING', -29);
  167. /**
  168. * Error code at verify() time when an index does not contain all
  169. * columns that it should contain.
  170. */
  171. define('DB_TABLE_ERR_VER_IDX_COL_MISSING', -30);
  172. /**
  173. * Error code at instantiation time when a creation mode
  174. * is not available for a phptype.
  175. */
  176. define('DB_TABLE_ERR_CREATE_PHPTYPE', -31);
  177. /**
  178. * Error code at create() time when you define more than one primary key
  179. * in $this->idx.
  180. */
  181. define('DB_TABLE_ERR_DECLARE_PRIMARY', -32);
  182. /**
  183. * Error code at create() time when a primary key is defined in $this->idx
  184. * and SQLite is used (SQLite does not support primary keys).
  185. */
  186. define('DB_TABLE_ERR_DECLARE_PRIM_SQLITE', -33);
  187. /**
  188. * Error code at alter() time when altering a table field is not possible
  189. * (e.g. because MDB2 has no support for the change or because the DBMS
  190. * does not support the change).
  191. */
  192. define('DB_TABLE_ERR_ALTER_TABLE_IMPOS', -34);
  193. /**
  194. * Error code at alter() time when altering a(n) index/constraint is not possible
  195. * (e.g. because MDB2 has no support for the change or because the DBMS
  196. * does not support the change).
  197. */
  198. define('DB_TABLE_ERR_ALTER_INDEX_IMPOS', -35);
  199. /**
  200. * The PEAR class for errors
  201. */
  202. require_once 'PEAR.php';
  203. /**
  204. * The Date class for recasting date and time values
  205. */
  206. require_once 'DB/Table/Date.php';
  207. /**
  208. * DB_Table supports these RDBMS engines and their various native data
  209. * types; we need these here instead of in Manager.php because the
  210. * initial array key tells us what databases are supported.
  211. */
  212. $GLOBALS['_DB_TABLE']['type'] = array(
  213. 'fbsql' => array(
  214. 'boolean' => 'DECIMAL(1,0)',
  215. 'char' => 'CHAR',
  216. 'varchar' => 'VARCHAR',
  217. 'smallint' => 'SMALLINT',
  218. 'integer' => 'INTEGER',
  219. 'bigint' => 'LONGINT',
  220. 'decimal' => 'DECIMAL',
  221. 'single' => 'REAL',
  222. 'double' => 'DOUBLE PRECISION',
  223. 'clob' => 'CLOB',
  224. 'date' => 'CHAR(10)',
  225. 'time' => 'CHAR(8)',
  226. 'timestamp' => 'CHAR(19)'
  227. ),
  228. 'ibase' => array(
  229. 'boolean' => 'DECIMAL(1,0)',
  230. 'char' => 'CHAR',
  231. 'varchar' => 'VARCHAR',
  232. 'smallint' => 'SMALLINT',
  233. 'integer' => 'INTEGER',
  234. 'bigint' => 'BIGINT',
  235. 'decimal' => 'DECIMAL',
  236. 'single' => 'FLOAT',
  237. 'double' => 'DOUBLE PRECISION',
  238. 'clob' => 'BLOB SUB_TYPE 1',
  239. 'date' => 'DATE',
  240. 'time' => 'TIME',
  241. 'timestamp' => 'TIMESTAMP'
  242. ),
  243. 'mssql' => array(
  244. 'boolean' => 'DECIMAL(1,0)',
  245. 'char' => 'CHAR',
  246. 'varchar' => 'VARCHAR',
  247. 'smallint' => 'SMALLINT',
  248. 'integer' => 'INTEGER',
  249. 'bigint' => 'BIGINT',
  250. 'decimal' => 'DECIMAL',
  251. 'single' => 'REAL',
  252. 'double' => 'FLOAT',
  253. 'clob' => 'TEXT',
  254. 'date' => 'CHAR(10)',
  255. 'time' => 'CHAR(8)',
  256. 'timestamp' => 'CHAR(19)'
  257. ),
  258. 'mysql' => array(
  259. 'boolean' => 'DECIMAL(1,0)',
  260. 'char' => 'CHAR',
  261. 'varchar' => 'VARCHAR',
  262. 'smallint' => 'SMALLINT',
  263. 'integer' => 'INTEGER',
  264. 'bigint' => 'BIGINT',
  265. 'decimal' => 'DECIMAL',
  266. 'single' => 'FLOAT',
  267. 'double' => 'DOUBLE',
  268. 'clob' => 'LONGTEXT',
  269. 'date' => 'CHAR(10)',
  270. 'time' => 'CHAR(8)',
  271. 'timestamp' => 'CHAR(19)'
  272. ),
  273. 'mysqli' => array(
  274. 'boolean' => 'DECIMAL(1,0)',
  275. 'char' => 'CHAR',
  276. 'varchar' => 'VARCHAR',
  277. 'smallint' => 'SMALLINT',
  278. 'integer' => 'INTEGER',
  279. 'bigint' => 'BIGINT',
  280. 'decimal' => 'DECIMAL',
  281. 'single' => 'FLOAT',
  282. 'double' => 'DOUBLE',
  283. 'clob' => 'LONGTEXT',
  284. 'date' => 'CHAR(10)',
  285. 'time' => 'CHAR(8)',
  286. 'timestamp' => 'CHAR(19)'
  287. ),
  288. 'oci8' => array(
  289. 'boolean' => 'NUMBER(1)',
  290. 'char' => 'CHAR',
  291. 'varchar' => 'VARCHAR2',
  292. 'smallint' => 'NUMBER(6)',
  293. 'integer' => 'NUMBER(11)',
  294. 'bigint' => 'NUMBER(19)',
  295. 'decimal' => 'NUMBER',
  296. 'single' => 'REAL',
  297. 'double' => 'DOUBLE PRECISION',
  298. 'clob' => 'CLOB',
  299. 'date' => 'CHAR(10)',
  300. 'time' => 'CHAR(8)',
  301. 'timestamp' => 'CHAR(19)'
  302. ),
  303. 'pgsql' => array(
  304. 'boolean' => 'DECIMAL(1,0)',
  305. 'char' => 'CHAR',
  306. 'varchar' => 'VARCHAR',
  307. 'smallint' => 'SMALLINT',
  308. 'integer' => 'INTEGER',
  309. 'bigint' => 'BIGINT',
  310. 'decimal' => 'DECIMAL',
  311. 'single' => 'REAL',
  312. 'double' => 'DOUBLE PRECISION',
  313. 'clob' => 'TEXT',
  314. 'date' => 'CHAR(10)',
  315. 'time' => 'CHAR(8)',
  316. 'timestamp' => 'CHAR(19)'
  317. ),
  318. 'sqlite' => array(
  319. 'boolean' => 'BOOLEAN',
  320. 'char' => 'CHAR',
  321. 'varchar' => 'VARCHAR',
  322. 'smallint' => 'SMALLINT',
  323. 'integer' => 'INTEGER',
  324. 'bigint' => 'BIGINT',
  325. 'decimal' => 'NUMERIC',
  326. 'single' => 'FLOAT',
  327. 'double' => 'DOUBLE',
  328. 'clob' => 'CLOB',
  329. 'date' => 'DATE',
  330. 'time' => 'TIME',
  331. 'timestamp' => 'TIMESTAMP'
  332. )
  333. );
  334. /**
  335. * US-English default error messages. If you want to internationalize, you can
  336. * set the translated messages via $GLOBALS['_DB_TABLE']['error']. You can also
  337. * use DB_Table::setErrorMessage(). Examples:
  338. *
  339. * <code>
  340. * (1) $GLOBALS['_DB_TABLE]['error'] = array(DB_TABLE_ERR_PHPTYPE => '...',
  341. * DB_TABLE_ERR_SQL_UNDEF => '...');
  342. * (2) DB_Table::setErrorMessage(DB_TABLE_ERR_PHPTYPE, '...');
  343. * DB_Table::setErrorMessage(DB_TABLE_ERR_SQL_UNDEF, '...');
  344. * (3) DB_Table::setErrorMessage(array(DB_TABLE_ERR_PHPTYPE => '...');
  345. * DB_TABLE_ERR_SQL_UNDEF => '...');
  346. * (4) $obj =& new DB_Table();
  347. * $obj->setErrorMessage(DB_TABLE_ERR_PHPTYPE, '...');
  348. * $obj->setErrorMessage(DB_TABLE_ERR_SQL_UNDEF, '...');
  349. * (5) $obj =& new DB_Table();
  350. * $obj->setErrorMessage(array(DB_TABLE_ERR_PHPTYPE => '...');
  351. * DB_TABLE_ERR_SQL_UNDEF => '...');
  352. * </code>
  353. *
  354. * For errors that can occur with-in the constructor call (i.e. e.g. creating
  355. * or altering the database table), only the code from examples (1) to (3)
  356. * will alter the default error messages early enough. For errors that can
  357. * occur later, examples (4) and (5) are also valid.
  358. */
  359. $GLOBALS['_DB_TABLE']['default_error'] = array(
  360. DB_TABLE_ERR_NOT_DB_OBJECT => 'First parameter must be a DB/MDB2 object',
  361. DB_TABLE_ERR_PHPTYPE => 'DB/MDB2 phptype (or dbsyntax) not supported',
  362. DB_TABLE_ERR_SQL_UNDEF => 'Select key not in map',
  363. DB_TABLE_ERR_INS_COL_NOMAP => 'Insert column not in map',
  364. DB_TABLE_ERR_INS_COL_REQUIRED => 'Insert data must be set and non-null for column',
  365. DB_TABLE_ERR_INS_DATA_INVALID => 'Insert data not valid for column',
  366. DB_TABLE_ERR_UPD_COL_NOMAP => 'Update column not in map',
  367. DB_TABLE_ERR_UPD_COL_REQUIRED => 'Update column must be set and non-null',
  368. DB_TABLE_ERR_UPD_DATA_INVALID => 'Update data not valid for column',
  369. DB_TABLE_ERR_CREATE_FLAG => 'Create flag not valid',
  370. DB_TABLE_ERR_IDX_NO_COLS => 'No columns for index',
  371. DB_TABLE_ERR_IDX_COL_UNDEF => 'Column not in map for index',
  372. DB_TABLE_ERR_IDX_TYPE => 'Type not valid for index',
  373. DB_TABLE_ERR_DECLARE_STRING => 'String column declaration not valid',
  374. DB_TABLE_ERR_DECLARE_DECIMAL => 'Decimal column declaration not valid',
  375. DB_TABLE_ERR_DECLARE_TYPE => 'Column type not valid',
  376. DB_TABLE_ERR_VALIDATE_TYPE => 'Cannot validate for unknown type on column',
  377. DB_TABLE_ERR_DECLARE_COLNAME => 'Column name not valid',
  378. DB_TABLE_ERR_DECLARE_IDXNAME => 'Index name not valid',
  379. DB_TABLE_ERR_DECLARE_TYPE => 'Column type not valid',
  380. DB_TABLE_ERR_IDX_COL_CLOB => 'CLOB column not allowed for index',
  381. DB_TABLE_ERR_DECLARE_STRLEN => 'Column name too long, 30 char max',
  382. DB_TABLE_ERR_IDX_STRLEN => 'Index name too long, 30 char max',
  383. DB_TABLE_ERR_TABLE_STRLEN => 'Table name too long, 30 char max',
  384. DB_TABLE_ERR_SEQ_STRLEN => 'Sequence name too long, 30 char max',
  385. DB_TABLE_ERR_VER_TABLE_MISSING => 'Verification failed: table does not exist',
  386. DB_TABLE_ERR_VER_COLUMN_MISSING => 'Verification failed: column does not exist',
  387. DB_TABLE_ERR_VER_COLUMN_TYPE => 'Verification failed: wrong column type',
  388. DB_TABLE_ERR_NO_COLS => 'Column definition array may not be empty',
  389. DB_TABLE_ERR_VER_IDX_MISSING => 'Verification failed: index does not exist',
  390. DB_TABLE_ERR_VER_IDX_COL_MISSING => 'Verification failed: index does not contain all specified cols',
  391. DB_TABLE_ERR_CREATE_PHPTYPE => 'Creation mode is not supported for this phptype',
  392. DB_TABLE_ERR_DECLARE_PRIMARY => 'Only one primary key is allowed',
  393. DB_TABLE_ERR_DECLARE_PRIM_SQLITE => 'SQLite does not support primary keys',
  394. DB_TABLE_ERR_ALTER_TABLE_IMPOS => 'Alter table failed: changing the field type not possible',
  395. DB_TABLE_ERR_ALTER_INDEX_IMPOS => 'Alter table failed: changing the index/constraint not possible'
  396. );
  397. // merge default and user-defined error messages
  398. if (!isset($GLOBALS['_DB_TABLE']['error'])) {
  399. $GLOBALS['_DB_TABLE']['error'] = array();
  400. }
  401. foreach ($GLOBALS['_DB_TABLE']['default_error'] as $code => $message) {
  402. if (!array_key_exists($code, $GLOBALS['_DB_TABLE']['error'])) {
  403. $GLOBALS['_DB_TABLE']['error'][$code] = $message;
  404. }
  405. }
  406. /**
  407. *
  408. * DB_Table is a database API and data type SQL abstraction class.
  409. *
  410. * DB_Table provides database API abstraction, data type abstraction,
  411. * automated SELECT, INSERT, and UPDATE queries, automated table
  412. * creation, automated validation of inserted/updated column values,
  413. * and automated creation of QuickForm elemnts based on the column
  414. * definitions.
  415. *
  416. * @category DB
  417. *
  418. * @package DB_Table
  419. *
  420. * @author Paul M. Jones <pmjones@php.net>
  421. * @author Mark Wiesemann <wiesemann@php.net>
  422. *
  423. * @version 1.4.0
  424. *
  425. */
  426. class DB_Table {
  427. /**
  428. *
  429. * The PEAR DB/MDB2 object that connects to the database.
  430. *
  431. * @access public
  432. *
  433. * @var object
  434. *
  435. */
  436. var $db = null;
  437. /**
  438. *
  439. * The backend type
  440. *
  441. * @access public
  442. *
  443. * @var string
  444. *
  445. */
  446. var $backend = null;
  447. /**
  448. *
  449. * The table or view in the database to which this object binds.
  450. *
  451. * @access public
  452. *
  453. * @var string
  454. *
  455. */
  456. var $table = null;
  457. /**
  458. *
  459. * Associative array of column definitions.
  460. *
  461. * @access public
  462. *
  463. * @var array
  464. *
  465. */
  466. var $col = array();
  467. /**
  468. *
  469. * Associative array of index definitions.
  470. *
  471. * @access public
  472. *
  473. * @var array
  474. *
  475. */
  476. var $idx = array();
  477. /**
  478. *
  479. * Baseline SELECT maps for select(), selectResult(), selectCount().
  480. *
  481. * @access public
  482. *
  483. * @var array
  484. *
  485. */
  486. var $sql = array();
  487. /**
  488. *
  489. * Whether or not to automatically validate data at insert-time.
  490. *
  491. * @access private
  492. *
  493. * @var bool
  494. *
  495. */
  496. var $_valid_insert = true;
  497. /**
  498. *
  499. * Whether or not to automatically validate data at update-time.
  500. *
  501. * @access private
  502. *
  503. * @var bool
  504. *
  505. */
  506. var $_valid_update = true;
  507. /**
  508. *
  509. * When calling select() and selectResult(), use this fetch mode (usually
  510. * a DB_FETCHMODE_* constant). If null, uses whatever is set in the $db
  511. * PEAR DB/MDB2 object.
  512. *
  513. * @access public
  514. *
  515. * @var int
  516. *
  517. */
  518. var $fetchmode = null;
  519. /**
  520. *
  521. * When fetchmode is DB_FETCHMODE_OBJECT, use this class for each
  522. * returned row. If null, uses whatever is set in the $db
  523. * PEAR DB/MDB2 object.
  524. *
  525. * @access public
  526. *
  527. * @var string
  528. *
  529. */
  530. var $fetchmode_object_class = null;
  531. /**
  532. *
  533. * If there is an error on instantiation, this captures that error.
  534. *
  535. * This property is used only for errors encountered in the constructor
  536. * at instantiation time. To check if there was an instantiation error...
  537. *
  538. * <code>
  539. * $obj =& new DB_Table();
  540. * if ($obj->error) {
  541. * // ... error handling code here ...
  542. * }
  543. * </code>
  544. *
  545. * @var object PEAR_Error
  546. *
  547. */
  548. var $error = null;
  549. /**
  550. *
  551. * Whether or not to automatically recast data at insert- and update-time.
  552. *
  553. * @access private
  554. *
  555. * @var bool
  556. *
  557. */
  558. var $_auto_recast = true;
  559. /**
  560. *
  561. * Specialized version of throwError() modeled on PEAR_Error.
  562. *
  563. * Throws a PEAR_Error with a DB_Table error message based on a
  564. * DB_Table constant error code.
  565. *
  566. * @static
  567. *
  568. * @access public
  569. *
  570. * @param Charcoal_String $code A DB_Table error code constant.
  571. *
  572. * @param Charcoal_String $extra Extra text for the error (in addition to the
  573. * regular error message).
  574. *
  575. * @return object PEAR_Error
  576. *
  577. */
  578. function &throwError($code, $extra = null)
  579. {
  580. // get the error message text based on the error code
  581. $text = $GLOBALS['_DB_TABLE']['error'][$code];
  582. // add any additional error text
  583. if ($extra) {
  584. $text .= ' ' . $extra;
  585. }
  586. // done!
  587. $error = PEAR::throwError($text, $code);
  588. return $error;
  589. }
  590. /**
  591. *
  592. * Constructor.
  593. *
  594. * If there is an error on instantiation, $this->error will be
  595. * populated with the PEAR_Error.
  596. *
  597. * @access public
  598. *
  599. * @param object &$db A PEAR DB/MDB2 object.
  600. *
  601. * @param Charcoal_String $table The table name to connect to in the database.
  602. *
  603. * @param mixed $create The automatic table creation mode to pursue:
  604. * - boolean false to not attempt creation
  605. * - 'safe' to create the table only if it does not exist
  606. * - 'drop' to drop any existing table with the same name and re-create it
  607. * - 'verify' to check whether the table exists, whether all the columns
  608. * exist, whether the columns have the right type, and whether the indexes
  609. * exist and have the right type
  610. * - 'alter' does the same as 'safe' if the table does not exist; if it
  611. * exists, a verification for columns existence, the column types, the
  612. * indexes existence, and the indexes types will be performed and the
  613. * table schema will be modified if needed
  614. *
  615. * @return object DB_Table
  616. *
  617. */
  618. function DB_Table(&$db, $table, $create = false)
  619. {
  620. // is the first argument a DB/MDB2 object?
  621. $this->backend = null;
  622. if (is_subclass_of($db, 'db_common')) {
  623. $this->backend = 'db';
  624. } elseif (is_subclass_of($db, 'mdb2_driver_common')) {
  625. $this->backend = 'mdb2';
  626. }
  627. if (is_null($this->backend)) {
  628. $this->error =& DB_Table::throwError(DB_TABLE_ERR_NOT_DB_OBJECT);
  629. return;
  630. }
  631. // array with column definition may not be empty
  632. if (! isset($this->col) || is_null($this->col) ||
  633. (is_array($this->col) && count($this->col) === 0)) {
  634. $this->error =& DB_Table::throwError(DB_TABLE_ERR_NO_COLS);
  635. return;
  636. }
  637. // set the class properties
  638. $this->db =& $db;
  639. $this->table = $table;
  640. // is the RDBMS supported?
  641. list($phptype, $dbsyntax) = DB_Table::getPHPTypeAndDBSyntax($db);
  642. if (! DB_Table::supported($phptype, $dbsyntax)) {
  643. $this->error =& DB_Table::throwError(
  644. DB_TABLE_ERR_PHPTYPE,
  645. "({$db->phptype})"
  646. );
  647. return;
  648. }
  649. // load MDB2_Extended module
  650. if ($this->backend == 'mdb2') {
  651. $this->db->loadModule('Extended', null, false);
  652. }
  653. // should we attempt table creation?
  654. if ($create) {
  655. if ($this->backend == 'mdb2') {
  656. $this->db->loadModule('Manager');
  657. }
  658. // check whether the chosen mode is supported
  659. list($phptype,) = DB_Table::getPHPTypeAndDBSyntax($this->db);
  660. $mode_supported = DB_Table::modeSupported($create, $phptype);
  661. if (PEAR::isError($mode_supported)) {
  662. $this->error =& $mode_supported;
  663. return;
  664. }
  665. if (!$mode_supported) {
  666. $this->error =& $this->throwError(
  667. DB_TABLE_ERR_CREATE_PHPTYPE,
  668. "('$create', '$phptype')"
  669. );
  670. return;
  671. }
  672. include_once 'DB/Table/Manager.php';
  673. switch ($create) {
  674. case 'alter':
  675. $result = $this->alter();
  676. break;
  677. case 'drop':
  678. case 'safe':
  679. $result = $this->create($create);
  680. break;
  681. case 'verify':
  682. $result = $this->verify();
  683. break;
  684. }
  685. if (PEAR::isError($result)) {
  686. // problem creating/altering/verifing the table
  687. $this->error =& $result;
  688. return;
  689. }
  690. }
  691. }
  692. /**
  693. *
  694. * Is a particular RDBMS supported by DB_Table?
  695. *
  696. * @static
  697. *
  698. * @access public
  699. *
  700. * @param Charcoal_String $phptype The RDBMS type for PHP.
  701. *
  702. * @param Charcoal_String $dbsyntax The chosen database syntax.
  703. *
  704. * @return bool True if supported, false if not.
  705. *
  706. */
  707. function supported($phptype, $dbsyntax = '')
  708. {
  709. // only Firebird is supported, not its ancestor Interbase
  710. if ($phptype == 'ibase' && $dbsyntax != 'firebird') {
  711. return false;
  712. }
  713. $supported = array_keys($GLOBALS['_DB_TABLE']['type']);
  714. return in_array(strtolower($phptype), $supported);
  715. }
  716. /**
  717. *
  718. * Is a creation mode supported for a RDBMS by DB_Table?
  719. *
  720. * @access public
  721. *
  722. * @param Charcoal_String $mode The chosen creation mode.
  723. *
  724. * @param Charcoal_String $phptype The RDBMS type for PHP.
  725. *
  726. * @return bool|object True if supported, false if not, or a PEAR_Error
  727. * if an unknown mode is specified.
  728. *
  729. */
  730. function modeSupported($mode, $phptype)
  731. {
  732. // check phptype for validity
  733. $supported = array_keys($GLOBALS['_DB_TABLE']['type']);
  734. if (!in_array(strtolower($phptype), $supported)) {
  735. return false;
  736. }
  737. switch ($mode) {
  738. case 'drop':
  739. case 'safe':
  740. // supported for all RDBMS
  741. return true;
  742. case 'alter':
  743. case 'verify':
  744. // not supported for fbsql and mssql (yet)
  745. switch ($phptype) {
  746. case 'fbsql':
  747. case 'mssql':
  748. return false;
  749. default:
  750. return true;
  751. }
  752. default:
  753. // unknown creation mode
  754. return $this->throwError(
  755. DB_TABLE_ERR_CREATE_FLAG,
  756. "('$mode')"
  757. );
  758. }
  759. }
  760. /**
  761. *
  762. * Detect values of 'phptype' and 'dbsyntax' keys of DSN.
  763. *
  764. * @static
  765. *
  766. * @access public
  767. *
  768. * @param object &$db A PEAR DB/MDB2 object.
  769. *
  770. * @return array Values of 'phptype' and 'dbsyntax' keys of DSN.
  771. *
  772. */
  773. function getPHPTypeAndDBSyntax(&$db) {
  774. $phptype = '';
  775. $dbsyntax = '';
  776. if (is_subclass_of($db, 'db_common')) {
  777. $phptype = $db->phptype;
  778. $dbsyntax = $db->dbsyntax;
  779. } elseif (is_subclass_of($db, 'mdb2_driver_common')) {
  780. $dsn = MDB2::parseDSN($db->getDSN());
  781. $phptype = $dsn['phptype'];
  782. $dbsyntax = $dsn['dbsyntax'];
  783. }
  784. return array($phptype, $dbsyntax);
  785. }
  786. /**
  787. *
  788. * Overwrite one or more error messages, e.g. to internationalize them.
  789. *
  790. * @access public
  791. *
  792. * @param mixed $code If string, the error message with code $code will
  793. * be overwritten by $message. If array, the error messages with code
  794. * of each array key will be overwritten by the key's value.
  795. *
  796. * @param Charcoal_String $message Only used if $key is not an array.
  797. *
  798. * @return void
  799. *
  800. */
  801. function setErrorMessage($code, $message = null) {
  802. if (is_array($code)) {
  803. foreach ($code as $single_code => $single_message) {
  804. $GLOBALS['_DB_TABLE']['error'][$single_code] = $single_message;
  805. }
  806. } else {
  807. $GLOBALS['_DB_TABLE']['error'][$code] = $message;
  808. }
  809. }
  810. /**
  811. *
  812. * Returns all or part of the $this->col property array.
  813. *
  814. * @access public
  815. *
  816. * @param mixed $col If null, returns the $this->col property array
  817. * as it is. If string, returns that column name from the $this->col
  818. * array. If an array, returns those columns named as the array
  819. * values from the $this->col array as an array.
  820. *
  821. * @return mixed All or part of the $this->col property array, or
  822. * boolean false if no matching column names are found.
  823. *
  824. */
  825. function getColumns($col = null)
  826. {
  827. // by default, return all column definitions
  828. if (is_null($col)) {
  829. return $this->col;
  830. }
  831. // if the param is a string, only return the column definition
  832. // named by the that string
  833. if (is_string($col)) {
  834. if (isset($this->col[$col])) {
  835. return $this->col[$col];
  836. } else {
  837. return false;
  838. }
  839. }
  840. // if the param is a sequential array of column names,
  841. // return only those columns named in that array
  842. if (is_array($col)) {
  843. $set = array();
  844. foreach ($col as $name) {
  845. $set[$name] = $this->getColumns($name);
  846. }
  847. if (count($set) == 0) {
  848. return false;
  849. } else {
  850. return $set;
  851. }
  852. }
  853. // param was not null, string, or array
  854. return false;
  855. }
  856. /**
  857. *
  858. * Returns all or part of the $this->idx property array.
  859. *
  860. * @access public
  861. *
  862. * @param Charcoal_String $col If specified, returns only this index key
  863. * from the $this->col property array.
  864. *
  865. * @return array All or part of the $this->idx property array.
  866. *
  867. */
  868. function getIndexes($idx = null)
  869. {
  870. // by default, return all index definitions
  871. if (is_null($idx)) {
  872. return $this->idx;
  873. }
  874. // if the param is a string, only return the index definition
  875. // named by the that string
  876. if (is_string($idx)) {
  877. if (isset($this->idx[$idx])) {
  878. return $this->idx[$idx];
  879. } else {
  880. return false;
  881. }
  882. }
  883. // if the param is a sequential array of index names,
  884. // return only those indexes named in that array
  885. if (is_array($idx)) {
  886. $set = array();
  887. foreach ($idx as $name) {
  888. $set[$name] = $this->getIndexes($name);
  889. }
  890. if (count($set) == 0) {
  891. return false;
  892. } else {
  893. return $set;
  894. }
  895. }
  896. // param was not null, string, or array
  897. return false;
  898. }
  899. /**
  900. *
  901. * Selects rows from the table using one of the DB/MDB2 get*() methods.
  902. *
  903. * @access public
  904. *
  905. * @param Charcoal_String $sqlkey The name of the SQL SELECT to use from the
  906. * $this->sql property array.
  907. *
  908. * @param Charcoal_String $filter Ad-hoc SQL snippet to AND with the default
  909. * SELECT WHERE clause.
  910. *
  911. * @param Charcoal_String $order Ad-hoc SQL snippet to override the default
  912. * SELECT ORDER BY clause.
  913. *
  914. * @param int $start The row number to start listing from in the
  915. * result set.
  916. *
  917. * @param int $count The number of rows to list in the result set.
  918. *
  919. * @param array $params Parameters to use in placeholder substitutions (if
  920. * any).
  921. *
  922. * @return mixed An array of records from the table (if anything but
  923. * 'getOne'), a single value (if 'getOne'), or a PEAR_Error object.
  924. *
  925. * @see DB::getAll()
  926. *
  927. * @see MDB2::getAll()
  928. *
  929. * @see DB::getAssoc()
  930. *
  931. * @see MDB2::getAssoc()
  932. *
  933. * @see DB::getCol()
  934. *
  935. * @see MDB2::getCol()
  936. *
  937. * @see DB::getOne()
  938. *
  939. * @see MDB2::getOne()
  940. *
  941. * @see DB::getRow()
  942. *
  943. * @see MDB2::getRow()
  944. *
  945. * @see DB_Table::_swapModes()
  946. *
  947. */
  948. function select($sqlkey, $filter = null, $order = null,
  949. $start = null, $count = null, $params = array())
  950. {
  951. // build the base command
  952. $sql = $this->buildSQL($sqlkey, $filter, $order, $start, $count);
  953. if (PEAR::isError($sql)) {
  954. return $sql;
  955. }
  956. // set the get*() method name
  957. if (isset($this->sql[$sqlkey]['get'])) {
  958. $method = ucwords(strtolower(trim($this->sql[$sqlkey]['get'])));
  959. $method = "get$method";
  960. } else {
  961. $method = 'getAll';
  962. }
  963. // DB_Table assumes you are using a shared PEAR DB/MDB2 object. Other
  964. // scripts using the same object probably expect its fetchmode
  965. // not to change, unless they change it themselves. Thus, to
  966. // provide friendly mode-swapping, we will restore these modes
  967. // afterwards.
  968. $restore_mode = $this->db->fetchmode;
  969. if ($this->backend == 'mdb2') {
  970. $restore_class = $this->db->getOption('fetch_class');
  971. } else {
  972. $restore_class = $this->db->fetchmode_object_class;
  973. }
  974. // swap modes
  975. $fetchmode = $this->fetchmode;
  976. $fetchmode_object_class = $this->fetchmode_object_class;
  977. if (isset($this->sql[$sqlkey]['fetchmode'])) {
  978. $fetchmode = $this->sql[$sqlkey]['fetchmode'];
  979. }
  980. if (isset($this->sql[$sqlkey]['fetchmode_object_class'])) {
  981. $fetchmode_object_class = $this->sql[$sqlkey]['fetchmode_object_class'];
  982. }
  983. $this->_swapModes($fetchmode, $fetchmode_object_class);
  984. // make sure params is an array
  985. if (! is_null($params)) {
  986. $params = (array) $params;
  987. }
  988. // get the result
  989. if ($this->backend == 'mdb2') {
  990. $result = $this->db->extended->$method($sql, null, $params);
  991. } else {
  992. switch ($method) {
  993. case 'getCol':
  994. $result = $this->db->$method($sql, 0, $params);
  995. break;
  996. case 'getAssoc':
  997. $result = $this->db->$method($sql, false, $params);
  998. break;
  999. default:
  1000. $result = $this->db->$method($sql, $params);
  1001. break;
  1002. }
  1003. }
  1004. // swap modes back
  1005. $this->_swapModes($restore_mode, $restore_class);
  1006. // return the result
  1007. return $result;
  1008. }
  1009. /**
  1010. *
  1011. * Selects rows from the table as a DB_Result/MDB2_Result_* object.
  1012. *
  1013. * @access public
  1014. *
  1015. * @param Charcoal_String $sqlkey The name of the SQL SELECT to use from the
  1016. * $this->sql property array.
  1017. *
  1018. * @param Charcoal_String $filter Ad-hoc SQL snippet to add to the default
  1019. * SELECT WHERE clause.
  1020. *
  1021. * @param Charcoal_String $order Ad-hoc SQL snippet to override the default
  1022. * SELECT ORDER BY clause.
  1023. *
  1024. * @param int $start The record number to start listing from in the
  1025. * result set.
  1026. *
  1027. * @param int $count The number of records to list in the result set.
  1028. *
  1029. * @param array $params Parameters to use in placeholder substitutions (if
  1030. * any).
  1031. *
  1032. * @return mixed A PEAR_Error on failure, or a DB_Result/MDB2_Result_*
  1033. * object on success.
  1034. *
  1035. * @see DB_Table::_swapModes()
  1036. *
  1037. */
  1038. function selectResult($sqlkey, $filter = null, $order = null,
  1039. $start = null, $count = null, $params = array())
  1040. {
  1041. // build the base command
  1042. $sql = $this->buildSQL($sqlkey, $filter, $order, $start, $count);
  1043. if (PEAR::isError($sql)) {
  1044. return $sql;
  1045. }
  1046. // DB_Table assumes you are using a shared PEAR DB/MDB2 object. Other
  1047. // scripts using the same object probably expect its fetchmode
  1048. // not to change, unless they change it themselves. Thus, to
  1049. // provide friendly mode-swapping, we will restore these modes
  1050. // afterwards.
  1051. $restore_mode = $this->db->fetchmode;
  1052. if ($this->backend == 'mdb2') {
  1053. $restore_class = $this->db->getOption('fetch_class');
  1054. } else {
  1055. $restore_class = $this->db->fetchmode_object_class;
  1056. }
  1057. // swap modes
  1058. $fetchmode = $this->fetchmode;
  1059. $fetchmode_object_class = $this->fetchmode_object_class;
  1060. if (isset($this->sql[$sqlkey]['fetchmode'])) {
  1061. $fetchmode = $this->sql[$sqlkey]['fetchmode'];
  1062. }
  1063. if (isset($this->sql[$sqlkey]['fetchmode_object_class'])) {
  1064. $fetchmode_object_class = $this->sql[$sqlkey]['fetchmode_object_class'];
  1065. }
  1066. $this->_swapModes($fetchmode, $fetchmode_object_class);
  1067. // make sure params is an array
  1068. if (! is_null($params)) {
  1069. $params = (array) $params;
  1070. }
  1071. // get the result
  1072. if ($this->backend == 'mdb2') {
  1073. $stmt =& $this->db->prepare($sql);
  1074. $result =& $stmt->execute($params);
  1075. } else {
  1076. $result =& $this->db->query($sql, $params);
  1077. }
  1078. // swap modes back
  1079. $this->_swapModes($restore_mode, $restore_class);
  1080. // return the result
  1081. return $result;
  1082. }
  1083. /**
  1084. *
  1085. * Counts the number of rows which will be returned by a query.
  1086. *
  1087. * This function works identically to {@link select()}, but it
  1088. * returns the number of rows returned by a query instead of the
  1089. * query results themselves.
  1090. *
  1091. * This makes using DB_Table with Pager easier, since you can pass the
  1092. * return value of this to Pager as totalItems, then select only the
  1093. * rows you need to display on a page.
  1094. *
  1095. * @author Ian Eure <ian@php.net>
  1096. *
  1097. * @access public
  1098. *
  1099. * @param Charcoal_String $sqlkey The name of the SQL SELECT to use from the
  1100. * $this->sql property array.
  1101. *
  1102. * @param Charcoal_String $filter Ad-hoc SQL snippet to AND with the default
  1103. * SELECT WHERE clause.
  1104. *
  1105. * @param Charcoal_String $order Ad-hoc SQL snippet to override the default
  1106. * SELECT ORDER BY clause.
  1107. *
  1108. * @param int $start The row number to start listing from in the
  1109. * result set.
  1110. *
  1111. * @param int $count The number of rows to list in the result set.
  1112. *
  1113. * @param array $params Parameters to use in placeholder substitutions (if
  1114. * any).
  1115. *
  1116. * @return mixed An integer number of records from the table, or a
  1117. * PEAR_Error object.
  1118. *
  1119. * @see DB_Table::select()
  1120. *
  1121. */
  1122. function selectCount($sqlkey, $filter = null, $order = null,
  1123. $start = null, $count = null, $params = array())
  1124. {
  1125. // does the SQL SELECT key exist?
  1126. $tmp = array_keys($this->sql);
  1127. if (! in_array($sqlkey, $tmp)) {
  1128. return $this->throwError(
  1129. DB_TABLE_ERR_SQL_UNDEF,
  1130. "('$sqlkey')"
  1131. );
  1132. }
  1133. // create a SQL key name for this count-query
  1134. $count_key = '__count_' . $sqlkey;
  1135. // has a count-query for the SQL key already been created?
  1136. if (! isset($this->sql[$count_key])) {
  1137. // we've not asked for a count on this query yet.
  1138. // get the elements of the query ...
  1139. $count_sql = $this->sql[$sqlkey];
  1140. // is a count-field set for the query?
  1141. if (! isset($count_sql['count']) ||
  1142. trim($count_sql['count']) == '') {
  1143. $count_sql['count'] = '*';
  1144. }
  1145. // replace the SELECT fields with a COUNT() command
  1146. $count_sql['select'] = "COUNT({$count_sql['count']})";
  1147. // replace the 'get' key so we only get the one result item
  1148. $count_sql['get'] = 'one';
  1149. // create the new count-query in the $sql array
  1150. $this->sql[$count_key] = $count_sql;
  1151. }
  1152. // retrieve the count results
  1153. return $this->select($count_key, $filter, $order, $start, $count,
  1154. $params);
  1155. }
  1156. /**
  1157. *
  1158. * Changes the $this->db PEAR DB/MDB2 object fetchmode and
  1159. * fetchmode_object_class.
  1160. *
  1161. * Because DB_Table objects tend to use the same PEAR DB/MDB2 object, it
  1162. * may sometimes be useful to have one object return results in one
  1163. * mode, and have another object return results in a different mode.
  1164. * This method allows us to switch DB/MDB2 fetch modes on the fly.
  1165. *
  1166. * @access private
  1167. *
  1168. * @param Charcoal_String $new_mode A DB_FETCHMODE_* constant. If null,
  1169. * defaults to whatever the DB/MDB2 object is currently using.
  1170. *
  1171. * @param Charcoal_String $new_class The object class to use for results when
  1172. * the $db object is in DB_FETCHMODE_OBJECT fetch mode. If null,
  1173. * defaults to whatever the the DB/MDB2 object is currently using.
  1174. *
  1175. * @return void
  1176. *
  1177. */
  1178. function _swapModes($new_mode, $new_class)
  1179. {
  1180. // get the old (current) mode and class
  1181. $old_mode = $this->db->fetchmode;
  1182. if ($this->backend == 'mdb2') {
  1183. $old_class = $this->db->getOption('fetch_class');
  1184. } else {
  1185. $old_class = $this->db->fetchmode_object_class;
  1186. }
  1187. // don't need to swap anything if the new modes are both
  1188. // null or if the old and new modes already match.
  1189. if ((is_null($new_mode) && is_null($new_class)) ||
  1190. ($old_mode == $new_mode && $old_class == $new_class)) {
  1191. return;
  1192. }
  1193. // set the default new mode
  1194. if (is_null($new_mode)) {
  1195. $new_mode = $old_mode;
  1196. }
  1197. // set the default new class
  1198. if (is_null($new_class)) {
  1199. $new_class = $old_class;
  1200. }
  1201. // swap modes
  1202. $this->db->setFetchMode($new_mode, $new_class);
  1203. }
  1204. /**
  1205. *
  1206. * Builds the SQL command from a specified $this->sql element.
  1207. *
  1208. * @access public
  1209. *
  1210. * @param Charcoal_String $sqlkey The $this->sql key to use as the basis for the
  1211. * SQL query string.
  1212. *
  1213. * @param Charcoal_String $filter A filter to add to the WHERE clause of the
  1214. * defined SELECT in $this->sql.
  1215. *
  1216. * @param Charcoal_String $order An ORDER clause to override the defined order
  1217. * in $this->sql.
  1218. *
  1219. * @param int $start The row number to start listing from in the
  1220. * result set.
  1221. *
  1222. * @param int $count The number of rows to list in the result set.
  1223. *
  1224. * @return mixed A PEAR_Error on failure, or an SQL command string on
  1225. * success.
  1226. *
  1227. */
  1228. function buildSQL($sqlkey, $filter = null, $order = null,
  1229. $start = null, $count = null)
  1230. {
  1231. // does the SQL SELECT key exist?
  1232. if (is_null($this->sql)) {
  1233. $this->sql = array();
  1234. }
  1235. $tmp = array_keys($this->sql);
  1236. if (! in_array($sqlkey, $tmp)) {
  1237. return $this->throwError(
  1238. DB_TABLE_ERR_SQL_UNDEF,
  1239. "('$sqlkey')"
  1240. );
  1241. }
  1242. // the SQL clause parts and their default values
  1243. $part = array(
  1244. 'select' => '*',
  1245. 'from' => $this->table,
  1246. 'join' => null,
  1247. 'where' => null,
  1248. 'group' => null,
  1249. 'having' => null,
  1250. 'order' => null
  1251. );
  1252. // loop through each possible clause
  1253. foreach ($part as $key => $val) {
  1254. if (! isset($this->sql[$sqlkey][$key])) {
  1255. continue;
  1256. } else {
  1257. $part[$key] = $this->sql[$sqlkey][$key];
  1258. }
  1259. }
  1260. // add the filter to the WHERE part
  1261. if ($filter) {
  1262. if (! $part['where']) {
  1263. $part['where'] .= $filter;
  1264. } else {
  1265. $part['where'] .= " AND ($filter)";
  1266. }
  1267. }
  1268. // override the ORDER part
  1269. if ($order) {
  1270. $part['order'] = $order;
  1271. }
  1272. // build up the command string form the parts
  1273. $cmd = '';
  1274. foreach ($part as $key => $val) {
  1275. // if the part value has not been set, skip it
  1276. if (! $val) {
  1277. continue;
  1278. }
  1279. switch ($key) {
  1280. case 'join':
  1281. $cmd .= " $val\n";
  1282. break;
  1283. case 'group':
  1284. case 'order':
  1285. $cmd .= strtoupper($key) . " BY $val\n";
  1286. break;
  1287. default:
  1288. $cmd .= strtoupper($key) . " $val\n";
  1289. break;
  1290. }
  1291. }
  1292. // add LIMIT if requested
  1293. if (! is_null($start) && ! is_null($count)) {
  1294. if ($this->backend == 'mdb2') {
  1295. $this->db->setLimit($count, $start);
  1296. } else {
  1297. $cmd = $this->db->modifyLimitQuery(
  1298. $cmd, $start, $count);
  1299. }
  1300. }
  1301. return $cmd;
  1302. }
  1303. /**
  1304. *
  1305. * Inserts a single table row after validating through validInsert().
  1306. *
  1307. * @access public
  1308. *
  1309. * @param array $data An associative array of key-value pairs where
  1310. * the key is the column name and the value is the column value. This
  1311. * is the data that will be inserted into the table. Data is checked
  1312. * against the column data type for validity.
  1313. *
  1314. * @return mixed Void on success, a PEAR_Error object on failure.
  1315. *
  1316. * @see validInsert()
  1317. *
  1318. * @see DB::autoExecute()
  1319. *
  1320. * @see MDB2::autoExecute()
  1321. *
  1322. */
  1323. function insert($data)
  1324. {
  1325. // forcibly recast the data elements to their proper types?
  1326. if ($this->_auto_recast) {
  1327. $this->recast($data);
  1328. }
  1329. // validate the data if auto-validation is turned on
  1330. if ($this->_valid_insert) {
  1331. $result = $this->validInsert($data);
  1332. if (PEAR::isError($result)) {
  1333. return $result;
  1334. }
  1335. }
  1336. if ($this->backend == 'mdb2') {
  1337. $result = $this->db->extended->autoExecute($this->table, $data,
  1338. MDB2_AUTOQUERY_INSERT);
  1339. } else {
  1340. $result = $this->db->autoExecute($this->table, $data,
  1341. DB_AUTOQUERY_INSERT);
  1342. }
  1343. return $result;
  1344. }
  1345. /**
  1346. *
  1347. * Turns on (or off) automatic validation of inserted data.
  1348. *
  1349. * @access public
  1350. *
  1351. * @param bool $flag True to turn on auto-validation, false to turn it off.
  1352. *
  1353. * @return void
  1354. *
  1355. */
  1356. function autoValidInsert($flag = true)
  1357. {
  1358. if ($flag) {
  1359. $this->_valid_insert = true;
  1360. } else {
  1361. $this->_valid_insert = false;
  1362. }
  1363. }
  1364. /**
  1365. *
  1366. * Validates an array for insertion into the table.
  1367. *
  1368. * @access public
  1369. *
  1370. * @param array $data An associative array of key-value pairs where
  1371. * the key is the column name and the value is the column value. This
  1372. * is the data that will be inserted into the table. Data is checked
  1373. * against the column data type for validity.
  1374. *
  1375. * @return mixed Boolean true on success, a PEAR_Error object on
  1376. * failure.
  1377. *
  1378. * @see insert()
  1379. *
  1380. */
  1381. function validInsert(&$data)
  1382. {
  1383. // loop through the data, and disallow insertion of unmapped
  1384. // columns
  1385. foreach ($data as $col => $val) {
  1386. if (! isset($this->col[$col])) {
  1387. return $this->throwError(
  1388. DB_TABLE_ERR_INS_COL_NOMAP,
  1389. "('$col')"
  1390. );
  1391. }
  1392. }
  1393. // loop through each column mapping, and check the data to be
  1394. // inserted into it against the column data type. we loop through
  1395. // column mappings instead of the insert data to make sure that
  1396. // all necessary columns are being inserted.
  1397. foreach ($this->col as $col => $val) {
  1398. // is the value allowed to be null?
  1399. if (isset($val['require']) &&
  1400. $val['require'] == true &&
  1401. (! isset($data[$col]) || is_null($data[$col]))) {
  1402. return $this->throwError(
  1403. DB_TABLE_ERR_INS_COL_REQUIRED,
  1404. "'$col'"
  1405. );
  1406. }
  1407. // does the value to be inserted match the column data type?
  1408. if (isset($data[$col]) &&
  1409. ! $this->isValid($data[$col], $col)) {
  1410. return $this->throwError(
  1411. DB_TABLE_ERR_INS_DATA_INVALID,
  1412. "'$col' ('$data[$col]')"
  1413. );
  1414. }
  1415. }
  1416. return true;
  1417. }
  1418. /**
  1419. *
  1420. * Updates table row(s) matching a custom WHERE clause, after checking
  1421. * against validUpdate().
  1422. *
  1423. * @access public
  1424. *
  1425. * @param array $data An associative array of key-value pairs where
  1426. * the key is the column name and the value is the column value. These
  1427. * are the columns that will be updated with new values.
  1428. *
  1429. * @param Charcoal_String $where An SQL WHERE clause limiting which records
  1430. * are to be updated.
  1431. *
  1432. * @return mixed Void on success, a PEAR_Error object on failure.
  1433. *
  1434. * @see validUpdate()
  1435. *
  1436. * @see DB::autoExecute()
  1437. *
  1438. * @see MDB2::autoExecute()
  1439. *
  1440. */
  1441. function update($data, $where)
  1442. {
  1443. // forcibly recast the data elements to their proper types?
  1444. if ($this->_auto_recast) {
  1445. $this->recast($data);
  1446. }
  1447. // validate the data if auto-validation is turned on
  1448. if ($this->_valid_update) {
  1449. $result = $this->validUpdate($data);
  1450. if (PEAR::isError($result)) {
  1451. return $result;
  1452. }
  1453. }
  1454. if ($this->backend == 'mdb2') {
  1455. $result = $this->db->extended->autoExecute($this->table, $data,
  1456. MDB2_AUTOQUERY_UPDATE, $where);
  1457. } else {
  1458. $result = $this->db->autoExecute($this->table, $data,
  1459. DB_AUTOQUERY_UPDATE, $where);
  1460. }
  1461. return $result;
  1462. }
  1463. /**
  1464. *
  1465. * Turns on (or off) automatic validation of updated data.
  1466. *
  1467. * @access public
  1468. *
  1469. * @param bool $flag True to turn on auto-validation, false to turn it off.
  1470. *
  1471. * @return void
  1472. *
  1473. */
  1474. function autoValidUpdate($flag = true)
  1475. {
  1476. if ($flag) {
  1477. $this->_valid_update = true;
  1478. } else {
  1479. $this->_valid_update = false;
  1480. }
  1481. }
  1482. /**
  1483. *
  1484. * Validates an array for updating the table.
  1485. *
  1486. * @access public
  1487. *
  1488. * @param array $data An associative array of key-value pairs where
  1489. * the key is the column name and the value is the column value. …

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