Source for file query-defs.php

Documentation is available at query-defs.php

  1. <?php
  2. /* ******************************************************************** */
  3. /* CATALYST PHP Source Code */
  4. /* -------------------------------------------------------------------- */
  5. /* This program is free software; you can redistribute it and/or modify */
  6. /* it under the terms of the GNU General Public License as published by */
  7. /* the Free Software Foundation; either version 2 of the License, or */
  8. /* (at your option) any later version. */
  9. /* */
  10. /* This program is distributed in the hope that it will be useful, */
  11. /* but WITHOUT ANY WARRANTY; without even the implied warranty of */
  12. /* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the */
  13. /* GNU General Public License for more details. */
  14. /* */
  15. /* You should have received a copy of the GNU General Public License */
  16. /* along with this program; if not, write to: */
  17. /* The Free Software Foundation, Inc., 59 Temple Place, Suite 330, */
  18. /* Boston, MA 02111-1307 USA */
  19. /* -------------------------------------------------------------------- */
  20. /* */
  21. /* Filename: query-defs.php */
  22. /* Author: Paul Waite */
  23. /* Description: Definitions for making queries on the database */
  24. /* */
  25. /* ******************************************************************** */
  26. /** @package database */
  27. include_once("datetime-defs.php");
  28.  
  29. // ----------------------------------------------------------------------
  30. // TRANSACTION Class
  31.  
  32. /** Transaction response to failure - rollback */
  33. ("ROLLBACK_ON_FAIL", true);
  34. /** Transaction response to failure - do nothing */
  35. ("NO_ROLLBACK_ON_FAIL", false);
  36.  
  37. // ----------------------------------------------------------------------
  38. // NULL query term
  39.  
  40. /** This value indicates a NULL field value for queries */
  41. ("NULLVALUE", "NULL!FIELD!VALUE");
  42.  
  43. // ----------------------------------------------------------------------
  44. /**
  45. * Transaction class
  46. * Encapsulates the query transaction.
  47. * @package database
  48. * @access private
  49. */
  50. class transaction {
  51. /** Transaction mode */
  52.  
  53. var $mode = ROLLBACK_ON_FAIL;
  54. /** True if there is an open transaction */
  55.  
  56. var $open = false;
  57. /** True if the transaction failed */
  58.  
  59. var $failed = false;
  60. /** If failed, message describing failure */
  61.  
  62. var $failed_msg = "";
  63. // ....................................................................
  64. /**
  65. * Constructor
  66. *
  67. * Create a transaction. Sets basic transaction attributes.
  68. * This transaction object is used, via global functions, for
  69. * every transaction using these query routines.
  70. * @param bool $mode Transaction rollback mode (true = rollback on failure)
  71. */
  72. function transaction($mode=ROLLBACK_ON_FAIL) {
  73. $this->mode = $mode;
  74. }
  75. // ....................................................................
  76. /**
  77. * Begin transaction
  78. * Start a transaction.
  79. * @param bool $mode Transaction rollback mode (true = rollback on failure)
  80. */
  81. function begin($mode=ROLLBACK_ON_FAIL) {
  82. global $RESPONSE;
  83. if ($RESPONSE->db_backed) {
  84. if (!$this->open) {
  85. $RESPONSE->datasource->begin_transaction();
  86. $this->open = true;
  87. $this->mode = $mode;
  88. $this->failed = false;
  89. $this->failed_msg = "";
  90. if (debugging()) {
  91. debugbr("TOK: Start transaction.", DBG_SQL);
  92. }
  93. }
  94. else {
  95. debugbr("TWARN: Nested transactions are not supported", DBG_SQL);
  96. }
  97. }
  98. }
  99. // ....................................................................
  100. /**
  101. * Commit transaction
  102. * Try to commit the open transaction. If an error
  103. * occurs then try to roll back if flagged to do so.
  104. * @return boolean True if the transaction succeeded.
  105. */
  106. function commit() {
  107. global $RESPONSE;
  108. $tok = false;
  109. if ($RESPONSE->db_backed && $this->open) {
  110. // Try to commit the transaction now..
  111. if (!$this->failed) {
  112. if ($RESPONSE->datasource->commit()) {
  113. if (debugging()) {
  114. debugbr("TOK: transaction committed.", DBG_SQL);
  115. }
  116. $tok = true;
  117. }
  118. else {
  119. $this->failed = true;
  120. }
  121. }
  122. // Deal with failed transactions..
  123. if ($this->failed) {
  124. $errmsg = "transaction failed";
  125. error_log("TFAIL: " . APP_NAME . ": $errmsg", 0);
  126. if (debugging()) {
  127. debugbr("TFAIL: $errmsg", DBG_SQL);
  128. }
  129. if ($this->mode == ROLLBACK_ON_FAIL) {
  130. $this->rollback();
  131. }
  132. }
  133. // Always end up with it closed..
  134. $this->open = false;
  135. }
  136. return $tok;
  137. }
  138. // ....................................................................
  139. /**
  140. * Roll back transaction
  141. * Tell the database to roll back the transaction..
  142. */
  143. function rollback() {
  144. global $RESPONSE;
  145. if ($RESPONSE->db_backed && $this->open) {
  146. $RESPONSE->datasource->rollback();
  147. $errmsg = "transaction rolled back";
  148. error_log("TFAIL: " . APP_NAME . " $errmsg", 0);
  149. if (debugging()) {
  150. debugbr("TFAIL: $errmsg", DBG_SQL);
  151. }
  152. $this->open = false;
  153. }
  154. }
  155. } // transaction class
  156. // Instantiate a generic transaction to use..
  157.  
  158. $global_tran = new transaction();
  159.  
  160. // ----------------------------------------------------------------------
  161. /**
  162. * List of things class
  163. * Encapsulates lists of items. A general-purpose class for containing
  164. * lists of things. A utility class to hold lists of things like field
  165. * lists, lists of tablenbames, orderby lists, etc.
  166. * @package database
  167. * @access private
  168. */
  169. class listofthings {
  170. /** The list of things being held */
  171.  
  172. var $things;
  173. /** Total things we have */
  174.  
  175. var $total = 0;
  176. // ....................................................................
  177. /**
  178. * Constructor
  179. * Create a new listofthings object.
  180. */
  181. function listofthings() {
  182. $this->clear();
  183. }
  184. // ....................................................................
  185. /**
  186. * Add thing
  187. * @param string $thing The identifier of the thing we are adding
  188. * @param mixed $val The value of the thing we are adding
  189. */
  190. function add($thing, $val="") {
  191. $this->things[$thing] = $val;
  192. $this->total += 1;
  193. }
  194. // ....................................................................
  195. /**
  196. * Clear things
  197. * Clears all things from the list.
  198. */
  199. function clear() {
  200. if (isset($this->things)) unset($this->things);
  201. $this->total = 0;
  202. }
  203. // ....................................................................
  204. /**
  205. * Return list
  206. * @param string $delim The delimiter for the returned list
  207. * @return string The delimited list of names of things
  208. */
  209. function listed($delim=",") {
  210. $list = "";
  211. if (isset($this->things)) {
  212. reset($this->things);
  213. while (list($thing, $val) = each($this->things)) {
  214. $list .= "$thing~^";
  215. }
  216. $list = str_replace("~^", $delim, trim($list));
  217. if (substr($list, -1) == $delim) {
  218. $list = substr($list, 0, strlen($list) - 1);
  219. }
  220. }
  221. return $list;
  222. }
  223. // ....................................................................
  224. /**
  225. * Return values
  226. * @param string $delim The delimiter for the returned list
  227. * @return string The delimited list of values of things
  228. */
  229. function values($delim=",") {
  230. $list = "";
  231. if (isset($this->things)) {
  232. reset($this->things);
  233. while (list($thing, $value) = each($this->things)) {
  234. if ($value === "") $value = "''";
  235. $list .= "$value~^";
  236. }
  237. $list = str_replace("~^", $delim, trim($list));
  238. if (substr($list, -1) == $delim) {
  239. $list = substr($list, 0, strlen($list) - 1);
  240. }
  241. }
  242. return $list;
  243. }
  244. // ....................................................................
  245. /**
  246. * Return equates
  247. * Returns the things we contain in key=value format, and all
  248. * as a string delimited by the given character.
  249. * @param string $delim The delimiter for the returned list
  250. * @return string The delimited list of equated things
  251. */
  252. function equated($delim=",") {
  253. $list = "";
  254. if (isset($this->things)) {
  255. reset($this->things);
  256. while (list($thing, $value) = each($this->things)) {
  257. if ($value === "") $value = "''";
  258. $list .= "$thing=$value~^";
  259. }
  260. $list = str_replace("~^", $delim, trim($list));
  261. if (substr($list, -1) == $delim) {
  262. $list = substr($list, 0, strlen($list) - 1);
  263. }
  264. }
  265. return $list;
  266. }
  267. } // listofthings class
  268. // ----------------------------------------------------------------------
  269.  
  270. /**
  271. * SQLquery class
  272. * An SQL Statement Text Container.
  273. * This class is the parent of the main dbquery class which directs the
  274. * query to the database. It is mainly a container of SQL query text, in
  275. * the variable 'sql', but also offers a few basic methods for building
  276. * queries. For complex queries however, build your own in a string and
  277. * then just set the 'sql' variable.
  278. * @package database
  279. */
  280. class sqlquery {
  281. /** Type of query 'SELECT', 'DELETE', 'INSERT' or 'UPDATE' */
  282.  
  283. var $type = "";
  284. /** List of fields in the query */
  285.  
  286. var $fields;
  287. /** List of tables in the query */
  288.  
  289. var $tables;
  290. /** The query WHERE clause components */
  291.  
  292. var $where;
  293. /** The GROUP BY clause */
  294.  
  295. var $groupby;
  296. /** The ORDER BY clause */
  297.  
  298. var $orderby;
  299. /** The LIMIT value */
  300.  
  301. var $limit;
  302. /** The OFFSET value */
  303.  
  304. var $offset;
  305. /** The formatted SQL query itself @see build() */
  306.  
  307. var $sql = "";
  308. // ....................................................................
  309. /**
  310. * Constructor
  311. * Create a new SQL Query object.
  312. * @param string $sql The SQL statement in full
  313. */
  314. function sqlquery($sql="") {
  315. $this->clear();
  316. $this->sql = $sql;
  317. } // sqlquery
  318. // ....................................................................
  319. /**
  320. * Clear query - Wipe all of the current query definitions.
  321. */
  322. function clear() {
  323. if (isset($this->fields)) unset($this->fields);
  324. if (isset($this->tables)) unset($this->tables);
  325. if (isset($this->where)) unset($this->where);
  326. if (isset($this->groupby)) unset($this->groupby);
  327. if (isset($this->orderby)) unset($this->orderby);
  328. $this->fields = new listofthings();
  329. $this->tables = new listofthings();
  330. $this->where = new listofthings();
  331. $this->groupby = new listofthings();
  332. $this->orderby = new listofthings();
  333. $this->sql = "";
  334. $this->limit = 0;
  335. $this->offset = 0;
  336. } // clear
  337. // ....................................................................
  338. /**
  339. * Utility function to help building list of things
  340. * @param listofthings $list_of_things listofthings to add to
  341. * @param mixed $list A simple array or a delimited list
  342. * @param string $delim Delimiter, "," default
  343. * @access private
  344. */
  345. function addlist(&$list_of_things, $list, $delim=",") {
  346. if (is_array($list)) {
  347. $items = $list;
  348. }
  349. else {
  350. $items = explode($delim, $list);
  351. }
  352. // Add to our existing list..
  353. foreach ($items as $item) {
  354. if ($item != "") $list_of_things->add($item);
  355. }
  356. } // addlist
  357. // ....................................................................
  358. /**
  359. * Define field list
  360. * Add a list of fields to return in query. This is a cumulative function
  361. * which may be called more than once to add fields. You can specify the
  362. * list of fields either as an array, or as a delimited list. If the latter,
  363. * then default delimiter is a comma, unless you specify your own.
  364. * Applicable to SELECT, DELETE and UPDATE.
  365. * @param string $field_spec The field list to add to the query
  366. * @param string $delim The delimter you want to separate fields with
  367. */
  368. function fieldlist($field_spec="*", $delim=",") {
  369. $this->addlist($this->fields, $field_spec, $delim);
  370. } // fieldlist
  371. // ....................................................................
  372. /**
  373. * Define table list
  374. * Add the table specification to our list. This is a cumulative function
  375. * which may be called more than once to add tables. You can specify the
  376. * list of tables either as an array, or as a delimited list. If the latter,
  377. * then default delimiter is a comma, unless you specify your own.
  378. * @param string $table_spec The table list to add to the query
  379. * @param string $delim The delimiter you want to separate tables with
  380. */
  381. function tables($table_spec, $delim=",") {
  382. $this->addlist($this->tables, $table_spec, $delim);
  383. } // tables
  384. // ....................................................................
  385. /**
  386. * Define table FROM list
  387. * A nicer synonym for "tables()" for SELECT
  388. * @param string $table_spec The table list to add to the query
  389. * @param string $delim The delimiter you want to separate tables with
  390. */
  391. function from($table_spec, $delim=",") {
  392. $this->tables($table_spec, $delim);
  393. } // from
  394. // ....................................................................
  395. /**
  396. * Define table INSERT INTO list
  397. * A nicer synonym for "tables()" for INSERT
  398. * @param string $table_spec The table list to add to the query
  399. * @param string $delim The delimiter you want to separate tables with
  400. */
  401. function into($table_spec, $delim=",") {
  402. $this->tables($table_spec, $delim);
  403. } // into
  404. // ....................................................................
  405. /**
  406. * Define group by field list
  407. * The fields can be an array, or a delimited list. If the latter, then default delimiter is a comma,
  408. * unless you specify your own.
  409. * @param string $field_spec The field list to add to the GROUP BY. Do not include words "GROUP BY".
  410. * @param string $delim The delimiter you want to separate the fields with
  411. */
  412. function groupby($field_spec="", $delim=",") {
  413. $this->addlist($this->groupby, $field_spec, $delim);
  414. } // groupby
  415. // ....................................................................
  416. /**
  417. * Define order field list
  418. * Defines the Sort order field list. The fields can be an array, or a
  419. * delimited list. If the latter, then default delimiter is a comma,
  420. * unless you specify your own.
  421. * @param string $field_spec The field list to add to the ORDER BY. Do not include words "ORDER BY".
  422. * @param string $delim The delimiter you want to separate the fields with
  423. */
  424. function orderby($field_spec="", $delim=",") {
  425. $this->addlist($this->orderby, $field_spec, $delim);
  426. } // orderby
  427. // ....................................................................
  428. /**
  429. * Define query LIMIT
  430. * @param integer $limit Numeric value for limit rows to return. Do not include the word "LIMIT".
  431. * @param integer $offset Numeric value for start row. Do not include the word "OFFSET".
  432. */
  433. function limit($limit) {
  434. $this->limit = $limit;
  435. } // limit
  436. // ....................................................................
  437. /**
  438. * Define query OFFSET
  439. * @param integer $offset Numeric value for start row. Do not include the word "OFFSET".
  440. */
  441. function offset($offset) {
  442. $this->offset = $offset;
  443. } // set
  444. // ....................................................................
  445. /**
  446. * Define field assignments
  447. * Defines the field assignment clauses for UPDATE and INSERT queries.
  448. * @param string $field The name of the field to assign a value to
  449. * @param mixed $val The value to assign to the field. Processed according to type.
  450. */
  451. function set($field, $val) {
  452. global $RESPONSE;
  453. // Numerics are done without quotes
  454. if (is_int($val) || is_float($val)) {
  455. $this->fields->add($field, $val);
  456. }
  457. // Boolean formats dependent on database type..
  458. elseif (is_bool($val)) {
  459. $val = $RESPONSE->datasource->db_value_from_bool($val);
  460. if (!is_int($val)) $val = "'$val'";
  461. $this->fields->add($field, $val);
  462. }
  463. // Everything else is a quoted, escaped string..
  464. else {
  465. $val = trim($val);
  466. $bits = explode("::", $val);
  467. $val = $bits[0];
  468. if ($RESPONSE->multilang && $RESPONSE->mbstring_avail) {
  469. if (mb_substr($val, 0, 1) == "'") $val = mb_substr($val, 1);
  470. if (mb_substr($val, -1) == "'") $val = mb_substr($val, 0, strlen($val) - 1);
  471. }
  472. else {
  473. if (substr($val, 0, 1) == "'") $val = substr($val, 1);
  474. if (substr($val, -1) == "'") $val = substr($val, 0, strlen($val) - 1);
  475. }
  476. $val = $RESPONSE->datasource->escape_string($val);
  477. $this->fields->add($field, "'$val'");
  478. }
  479. } // set
  480. // ....................................................................
  481. /**
  482. * Add WHERE clause component
  483. * This function allows you to add a WHERE clause component. An example might
  484. * be something like: "AND c.foo='myval'". Either call this once with the whole
  485. * WHERE cluase string (minus the word "WHERE"), or multiple times with
  486. * parts of the where clause as in the example above.
  487. * @param string $where_clause A WHERE clause component, without the "WHERE".
  488. */
  489. function where($where_clause) {
  490. if ($where_clause != "") {
  491. $this->where->add($where_clause);
  492. }
  493. } // where
  494. // ....................................................................
  495. /**
  496. * This is useful when you change some part of the query after it has been
  497. * executed once, and want it to rebuild the SQL anew before it gets
  498. * executed again.
  499. */
  500. function rebuild() {
  501. $this->sql = "";
  502. $this->build();
  503. } // rebuild
  504. // ....................................................................
  505. /**
  506. * Build the SQL query
  507. * This takes the various components which have been added to the object
  508. * and parses them to build the full SQL statement which will be sent
  509. * to the server. The result is stored in $this->sql.
  510. * NOTE: this method calls the appropriate database-specific SQL
  511. * builder method.
  512. */
  513. function build() {
  514. global $RESPONSE;
  515. $this->sql = $RESPONSE->datasource->SQL($this);
  516. return $this->sql;
  517. } // build
  518.  
  519. } // sqlquery class
  520. // ----------------------------------------------------------------------
  521.  
  522. /**
  523. * DB Query class
  524. * This class is the one which executes queries against the
  525. * connected database.
  526. * @package database
  527. */
  528. class dbquery extends sqlquery {
  529. /** Number of rows returned after execute */
  530.  
  531. var $rowcount = 0;
  532. /** Number of rows affected by query */
  533.  
  534. var $affectedrowcount = 0;
  535. /** Current row in the query */
  536.  
  537. var $rowno = 0;
  538. /** Current row resource ID */
  539.  
  540. var $rid = false;
  541. /** True if query is valid, post execution */
  542.  
  543. var $valid = false;
  544. /** True if data was returned, after execute */
  545.  
  546. var $hasdata = false;
  547. /** Record last error/info message */
  548.  
  549. var $last_errormsg = "";
  550. // ....................................................................
  551. /**
  552. * Constructor
  553. * Create a new DB Query object.
  554. * @param string $sql An SQL statement in full
  555. */
  556. function dbquery($sql="") {
  557. $this->sqlquery($sql);
  558. return $this;
  559. } // dbquery
  560. // ....................................................................
  561. /**
  562. * Exceute the query
  563. * If we have an SQL phrase, execute it now. We store
  564. * the result in this->valid, and also return it. If
  565. * a transaction is open, update the status.
  566. * @return bool True if query was executed successfully
  567. */
  568. function execute() {
  569. global $RESPONSE;
  570.  
  571. // Head it off at the pass if the system is being run
  572. // standalone, or there is no defined datasource..
  573. if (!isset($RESPONSE->datasource) || !$RESPONSE->db_backed) {
  574. return false;
  575. }
  576.  
  577. global $global_tran;
  578. $this->rid = false;
  579. if ($this->sql == "") {
  580. $this->build();
  581. }
  582. if ($this->sql != "") {
  583. // Execute the query using low-level DB module..
  584. $this->rid = $RESPONSE->datasource->query($this->sql);
  585. // Now examine the result..
  586. if ($this->rid != false) {
  587. if (preg_match("/(^select|^\(select)/i", $this->sql)) {
  588. $this->rowcount = $RESPONSE->datasource->numrows($this->rid);
  589. }
  590. else {
  591. $this->rowcount = $RESPONSE->datasource->affectedrows($this->rid);
  592. }
  593. $this->rowno = 0;
  594. $this->hasdata = ($this->rowcount > 0);
  595. }
  596. else {
  597. // Log the failed query..
  598. $db_err = $RESPONSE->datasource->errormessage();
  599. if ($db_err) $errstr .= " DBSERVER: $db_err";
  600. $this->last_errormsg = $errstr;
  601. if (debugging()) {
  602. debugbr($errstr, DBG_SQL);
  603. }
  604. // Set failed status for any open transaction..
  605. if ($global_tran->open) {
  606. $global_tran->failed = true;
  607. $global_tran->failed_msg = $errstr;
  608. }
  609. }
  610. }
  611. $this->valid = ($this->rid != false);
  612. return $this->valid;
  613. } // execute
  614. // ....................................................................
  615. /**
  616. * Set the SQL statement
  617. * @param string $sql An SQL statement in full
  618. */
  619. function set_sql($sql) {
  620. $this->tidyup();
  621. $this->sql = $sql;
  622. return $this;
  623. } // set_sql
  624. // ....................................................................
  625. /**
  626. * Free resources.
  627. * Not really necessary, but you might be that fastidious kind of person.
  628. */
  629. function tidyup() {
  630. global $RESPONSE;
  631. if ($this->rid) {
  632. $RESPONSE->datasource->freeresult($this->rid);
  633. $this->clear();
  634. $this->rowcount = 0;
  635. $this->affectedrowcount = 0;
  636. $this->rid = false;
  637. $this->valid = false;
  638. $this->hasdata = false;
  639. }
  640. } // tidyup
  641.  
  642. } // dbquery class
  643. // ----------------------------------------------------------------------
  644.  
  645. /**
  646. * DB Rows class
  647. * Renders a query into data and allows access to the data either
  648. * directly or via the usual get first,last,next,previous cursor
  649. * navigation.
  650. * This class returns data as "rows" which is to say a standard
  651. * array of data. For the associative array version then please
  652. * @see dbrecords
  653. * NOTE: On creation, it executes the query and positions to the
  654. * initial record (defaulted to the first).
  655. * @package database
  656. */
  657. class dbrows extends dbquery {
  658. /** An array containing the current DB row */
  659.  
  660. var $current_row;
  661. // ....................................................................
  662. /**
  663. * Constructor
  664. * Create a new DB Rows object.
  665. * @param string $sql An SQL statement in full
  666. */
  667. function dbrows($sql="") {
  668. $this->dbquery($sql);
  669. if ($sql != "") {
  670. $this->execute();
  671. }
  672. } // dbrows
  673. // ....................................................................
  674. /**
  675. * Execute query
  676. * Execute this query. We override the parent method here
  677. * simply to ensure we are positioned at the first row.
  678. * @return bool True if query was executed successfully
  679. */
  680. function execute() {
  681. dbquery::execute();
  682. if ($this->valid) {
  683. $this->get_first();
  684. }
  685. return $this->valid;
  686. } // execute
  687. // ....................................................................
  688. /**
  689. * Set the SQL statement
  690. * In this case we re-execute the SQL automatically.
  691. * @param string $sql An SQL statement in full
  692. * @return bool True if query was executed successfully
  693. */
  694. function set_sql($sql) {
  695. $this->tidyup();
  696. $this->sql = $sql;
  697. return $this->execute();
  698. } // set_sql
  699. // ....................................................................
  700. /**
  701. * Get row raw
  702. * Return the given database row from the resultset. This method may
  703. * be over-ridden in subsequent child classes.
  704. * @param integer $rowno The row number to return
  705. * @return array True if row was available
  706. * @access private
  707. */
  708. function get_row_raw($rowno) {
  709. global $RESPONSE;
  710. if ($this->rid != false) {
  711. return $RESPONSE->datasource->fetch_row($this->rid, $rowno);
  712. }
  713. else return false;
  714. } // get_row_raw
  715. // ....................................................................
  716. /**
  717. * Get row
  718. * Return the given database row from the resultset. Uses the
  719. * get_row_raw() method applicable to this class.
  720. * @see get_row_raw()
  721. * @param integer $rowno The row number to return
  722. * @return mixed The row if it is available, else returns FALSE.
  723. */
  724. function get_row($rowno) {
  725. if ($this->valid && ($this->rowcount > 0)) {
  726. if ($rowno > ($this->rowcount - 1)) $rowno = $this->rowcount - 1;
  727. elseif ($rowno < 0) $rowno = 0;
  728. $this->current_row = $this->get_row_raw($rowno);
  729. if ($this->current_row !== false) {
  730. $this->rowno = $rowno;
  731. if (debugging()) {
  732. $errstr = "";
  733. for($i=0; $i < count($this->current_row); $i++) {
  734. if ($errstr != "") $errstr .= ", ";
  735. $errstr .= $this->current_row[$i];
  736. }
  737. //$errstr = var_dump($this->current_row);
  738. debugbr("QDATA: Row $rowno: $errstr", DBG_SQLDATA);
  739. }
  740. }
  741. else {
  742. debugbr("QDATA: Row $rowno: returned FALSE", DBG_SQLDATA);
  743. }
  744. }
  745. else {
  746. if (isset($this->current_row)) unset($this->current_row);
  747. $this->current_row = false;
  748. }
  749. return $this->current_row;
  750. } // get_row
  751. // ....................................................................
  752. /**
  753. * Returns true if the row number exists in the returned resultset.
  754. * The query has to be valid, and there have to be some rows in it.
  755. * @param integer $rowno Number of the row, zero (0) is first row
  756. * @return bool True if the row is present in the current resultset
  757. */
  758. function rowexists($rowno) {
  759. return (
  760. ($this->valid)
  761. && ($this->rowcount > 0)
  762. && ($rowno >= 0)
  763. && ($rowno <= ($this->rowcount - 1))
  764. );
  765. } // rowexists
  766. // ....................................................................
  767. /**
  768. * Refresh the query
  769. * Re-run the current SQL query. If successful the row will be stored
  770. * in $this->current_row.
  771. */
  772. function refresh() {
  773. $rowno = $this->rowno;
  774. $this->execute();
  775. $this->get_row($rowno);
  776. } // refresh
  777. // ....................................................................
  778. /**
  779. * Get current row
  780. * If current query is invalid, try to execute it first, then do a
  781. * get_first(). If query is then valid, return the current row.
  782. * @see get_first()
  783. * @return mixed The row if it is available, else returns FALSE.
  784. */
  785. function get_current() {
  786. if (!$this->valid) {
  787. $this->execute();
  788. $this->get_first();
  789. }
  790. return $this->current_row;
  791. } // get_current
  792. // ....................................................................
  793. /**
  794. * Get current row
  795. * If current query is invalid, try to execute it first, then do a
  796. * get_first(). If query is then valid, return the current row.
  797. * @see get_first()
  798. * @return mixed The row if it is available, else returns FALSE.
  799. */
  800. function get_first() {
  801. if (!$this->valid) $this->execute();
  802. return $this->get_row(0);
  803. } // get_first
  804. // ....................................................................
  805. /**
  806. * Get last row
  807. * If current query is invalid, try to execute it first, then get
  808. * the last row from the resultset.
  809. * @return mixed The row if it is available, else returns FALSE.
  810. */
  811. function get_last() {
  812. if (!$this->valid) $this->execute();
  813. return $this->get_row($this->rowcount - 1);
  814. } // get_last
  815. // ....................................................................
  816. /**
  817. * Get previous row
  818. * If current query is invalid, try to execute it first, then get
  819. * the previous row from the resultset.
  820. * @return mixed The row if it is available, else returns FALSE.
  821. */
  822. function get_previous() {
  823. if (!$this->valid) $this->execute();
  824. if ($this->rowno > 0) {
  825. return $this->get_row($this->rowno - 1);
  826. }
  827. else return false;
  828. } // get_previous
  829. // ....................................................................
  830. /**
  831. * Get next row
  832. * If current query is invalid, try to execute it first, then get
  833. * the next row from the resultset.
  834. * @return mixed The row if it is available, else returns FALSE.
  835. */
  836. function get_next() {
  837. if (!$this->valid) $this->execute();
  838. if ($this->rowno < ($this->rowcount - 1)) {
  839. return $this->get_row($this->rowno + 1);
  840. }
  841. else return false;
  842. } // get_next
  843. // ....................................................................
  844. /**
  845. * Return the EOF (end-of-file) indicator for this query. Returns
  846. * true if no more results can be returned with get_next(), ie. we
  847. * are at the end of the results set.
  848. * @return boolean True if we are at the end of the results set.
  849. */
  850. function eof() {
  851. if (!$this->valid) return true;
  852. else return ($this->rowno >= ($this->rowcount - 1));
  853. } // eof
  854.  
  855. } // dbrows class
  856. // ----------------------------------------------------------------------
  857.  
  858. /**
  859. * DB Records class
  860. * Renders a query into data and allows access to the data either
  861. * directly or via the usual get first,last,next,previous cursor
  862. * navigation.
  863. * This class returns data as an associative array and is thus
  864. * the most useful of all the data access methods. It extends the
  865. * dbrows class, and over-rides the get_row_raw method to retrieve
  866. * data.
  867. * @see dbrows.
  868. * @package database
  869. */
  870. class dbrecords extends dbrows {
  871. /**
  872. * Constructor
  873. * Create a new DB Records object.
  874. * @param string $sql An SQL statement in full
  875. */
  876. function dbrecords($sql="") {
  877. $this->dbrows($sql);
  878. } // dbrecords
  879. // ....................................................................
  880. /**
  881. * Get row raw
  882. * Return the given database row from the resultset. This over-rides
  883. * the parent method of the same name and returns an array.
  884. * @param integer $rowno The row number to return
  885. * @return array True if row was available
  886. * @access private
  887. */
  888. function get_row_raw($rowno) {
  889. global $RESPONSE;
  890. if ($this->rid) {
  891. return $RESPONSE->datasource->fetch_array($this->rid, $rowno);
  892. }
  893. else return false;
  894. } // get_row_raw
  895. // ....................................................................
  896. /**
  897. * Return whether the name field exists in the resultset, true or false.
  898. * @param string $fieldname The name of the field to check existence of
  899. * @return boolean True if the named field exists in the resultset.
  900. */
  901. function field_exists($fieldname) {
  902. global $RESPONSE;
  903. // Intercept any errant querying in standalone mode..
  904. if (!$RESPONSE->db_backed) return false;
  905. if ($this->rid) {
  906. return isset($this->current_row[$fieldname]);
  907. }
  908. else return false;
  909. } // field_exists
  910. // ....................................................................
  911. /**
  912. * Get field content
  913. * Return the field content from the current database array (row).
  914. * Does not provide ANY pre/post-processing.
  915. * @param string $fieldname The name of the field to return value of
  916. * @return mixed Value of the named field
  917. */
  918. function rawfield($fieldname) {
  919. global $RESPONSE;
  920. // Intercept any errant querying in standalone mode..
  921. if (!$RESPONSE->db_backed) return "";
  922. if ($this->rid) {
  923. $value = $this->current_row[$fieldname];
  924. return $value;
  925. }
  926. else return false;
  927. } // rawfield
  928. // ....................................................................
  929. /**
  930. * Get field content
  931. * Return the field content from the current database array (row).
  932. * If the value is a string, then unescape_string() is done automatically.
  933. * Note that in most databases, string data does not need to be
  934. * unescaped, and so this method will vary depending on DB flavour.
  935. * @param string $fieldname The name of the field to return value of
  936. * @return mixed Value of the named field
  937. */
  938. function field($fieldname) {
  939. global $RESPONSE;
  940. // Intercept any errant querying in standalone mode..
  941. if (!$RESPONSE->db_backed) return "";
  942. if ($this->rid) {
  943. $value = $this->rawfield($fieldname);
  944. if (is_string($value)) {
  945. $value = $RESPONSE->datasource->unescape_string($value);
  946. }
  947. return $value;
  948. }
  949. else return false;
  950. } // field
  951. // ....................................................................
  952. /**
  953. * Database independent boolean handling. Returns TRUE if the named
  954. * field in the current row is boolean true according to the rules of the
  955. * underlying database, else returns FALSE.
  956. * @param string $fieldname The name of the field to return boolean value of
  957. * @return boolean True if field contains database-dependent true value
  958. */
  959. function istrue($fieldname) {
  960. global $RESPONSE;
  961. $value = $this->field($fieldname);
  962. return $RESPONSE->datasource->bool_from_db_value($value);
  963. } // istrue
  964.  
  965. } // dbrecords class
  966. // ----------------------------------------------------------------------
  967. // SPECIFIC DBQUERY TYPES..
  968. // Wrappers which save you specifying some variables, when instantiating
  969. // a new 'dbquery' object that's all..
  970.  
  971. /**
  972. * DB Select class
  973. * A special case of the dbrecords class.
  974. * @package database
  975. */
  976. class dbselect extends dbrecords {
  977. /**
  978. * Constructor
  979. * Create a new DB Select object. This is for selecting rows from
  980. * the database, and returning fields from those rows.
  981. * @param string $table Table(s) to run select on
  982. */
  983. function dbselect($table="") {
  984. $this->dbrecords();
  985. $this->type = "SELECT";
  986. if ($table != "") {
  987. $this->from($table);
  988. }
  989. } // dbselect
  990.  
  991. } // dbselect class
  992. // ----------------------------------------------------------------------
  993.  
  994. /**
  995. * DB Delete class
  996. * A special case of the dbquery class. This is for deleting
  997. * rows from the database.
  998. * @package database
  999. */
  1000. class dbdelete extends dbquery {
  1001. /**
  1002. * Constructor
  1003. * Create a new DB Delete object.
  1004. * @param string $table Table to delete rows from.
  1005. */
  1006. function dbdelete($table="") {
  1007. $this->dbquery();
  1008. $this->type = "DELETE";
  1009. if ($table != "") {
  1010. $this->into($table);
  1011. }
  1012. } // dbdelete
  1013.  
  1014. } // dbdelete class
  1015. // ----------------------------------------------------------------------
  1016.  
  1017. /**
  1018. * DB tablemod class
  1019. * Parent class for classes which only modify a single table. This
  1020. * means either update or inserts. This class is provided so we can
  1021. * define a common method for sequence definition.
  1022. * @package database
  1023. * @abstract
  1024. */
  1025. class dbtablemod extends dbquery {
  1026. /**
  1027. * Constructor
  1028. * Create a new DB Insert object. This is for inserting
  1029. * a record into the database.
  1030. * @param string $table Table to modify, mandatory parameter.
  1031. */
  1032. function dbtablemod($table) {
  1033. $this->dbquery();
  1034. $this->tables($table);
  1035. }
  1036. // ....................................................................
  1037. /**
  1038. * Set the next sequence value for a column, using either a named
  1039. * sequence or, if that is nullstring or the underlying DB does
  1040. * not support sequences, other means. See the next_sequencevalue()
  1041. * method in the underlying DB module db-xxxx.php.
  1042. */
  1043. function next_sequencevalue($sequencename, $column) {
  1044. global $RESPONSE;
  1045. $nextseq = $RESPONSE->datasource->next_sequencevalue(
  1046. $sequencename,
  1047. $this->tables->listed(),
  1048. $column
  1049. );
  1050. $this->set($column, $nextseq);
  1051. }
  1052. }
  1053. // ----------------------------------------------------------------------
  1054. /**
  1055. * DB Insert class
  1056. * A special case of the dbtablemod class.
  1057. * @package database
  1058. */
  1059. class dbinsert extends dbtablemod {
  1060. /**
  1061. * Constructor
  1062. * Create a new DB Insert object. This is for inserting
  1063. * a record into the database.
  1064. * @param string $table Table to insert into
  1065. */
  1066. function dbinsert($table) {
  1067. $this->dbtablemod($table);
  1068. $this->type = "INSERT";
  1069. } // dbinsert
  1070.  
  1071. } // dbinsert class
  1072. // ----------------------------------------------------------------------
  1073.  
  1074. /**
  1075. * DB Update class
  1076. * A special case of the dbquery class. This is for updating data in
  1077. * particular rows in the database.
  1078. * @package database
  1079. */
  1080. class dbupdate extends dbtablemod {
  1081. /**
  1082. * Constructor
  1083. * Create a new DB Select object.
  1084. * @param string $table Table to update
  1085. */
  1086. function dbupdate($table) {
  1087. $this->dbtablemod($table);
  1088. $this->type = "UPDATE";
  1089. } // dbupdate
  1090.  
  1091. } // dbupdate class
  1092. // ----------------------------------------------------------------------
  1093.  
  1094. /**
  1095. * DB seq class
  1096. * A class which allows the management and use of sequences.
  1097. * @package database
  1098. */
  1099. class dbseq extends dbquery {
  1100. // Public
  1101. // Private
  1102. /** The name of the sequence
  1103. @access private */
  1104. var $sequencename = "";
  1105. // ....................................................................
  1106. /**
  1107. * Create a new object to manage a sequence, optionally
  1108. * specifying the sequence name..
  1109. * @param string $sequencename Name of the sequence to manage
  1110. */
  1111. function dbseq($sequencename) {
  1112. $this->sequencename = $sequencename;
  1113. $this->dbquery();
  1114. } // dbseq
  1115. // ....................................................................
  1116. /**
  1117. * Get the next sequence value. We can optionally specify the table and
  1118. * column associated with it. The requirement for these parameters is in fact
  1119. * implementation-specific. If your underlying database does not support
  1120. * named sequences, then you will probably have to nominate the table/column
  1121. * so that the low-level DB access module can do a MAX() to obtain the next
  1122. * value. If it does upport tham then you probably only need the sequence
  1123. * name as specified in the constructor.
  1124. * @param string $table Name of the table associated with this sequence
  1125. * @param string $column Name of the column associated with this sequence
  1126. * @return integer The value of the next integer in this sequence
  1127. */
  1128. function next_sequencevalue($table="", $column="") {
  1129. global $RESPONSE;
  1130. return $RESPONSE->datasource->next_sequencevalue($this->sequencename, $table, $column);
  1131. } // next_sequencevalue
  1132. // ....................................................................
  1133. /**
  1134. * Get the current sequence value.
  1135. * @param string $table Name of the table associated with this sequence
  1136. * @param string $column Name of the column associated with this sequence
  1137. * @return integer The current sequence value
  1138. */
  1139. function current_sequencevalue($table="", $column="") {
  1140. global $RESPONSE;
  1141. return $RESPONSE->datasource->current_sequencevalue($this->sequencename, $table, $column);
  1142. } // current_sequencevalue
  1143. // ....................................................................
  1144. /**
  1145. * Set a sequence value.
  1146. * @param integer $newval New integer value to set sequence to
  1147. * @param string $table Name of the table associated with this sequence
  1148. * @param string $column Name of the column associated with this sequence
  1149. */
  1150. function set_sequencevalue($newval, $table="", $column="") {
  1151. global $RESPONSE;
  1152. return $RESPONSE->datasource->set_sequencevalue($newval, $this->sequencename, $table, $column);
  1153. } // set_sequencevalue
  1154.  
  1155. } // dbseq class
  1156.  
  1157. /** ####################################################################
  1158. * UTILITY FUNCTIONS
  1159. * These are mainly provided to give you a convenient 'short-hand' way
  1160. * of accessing database query methods, without having to specify the
  1161. * full path to the method via the $RESPONSE object etc. It looks nicer.
  1162. */
  1163.  
  1164. /**
  1165. * Execute a DB command
  1166. * A wrapper which caters for the 'command' type of SQL
  1167. * query where no results are reauired, such as for a
  1168. * DELETE or UPDATE, or INSERT etc. Returns true if all
  1169. * ok, otherwise returns false.
  1170. * @param string $sql An SQL statement in full
  1171. * @return bool True if dbcommand succeeded
  1172. */
  1173. function dbcommand($sql) {
  1174. $q = new dbquery($sql);
  1175. $res = $q->execute();
  1176. return $res;
  1177. } // dbcommand
  1178. // ......................................................................
  1179.  
  1180. /**
  1181. * A wrapper which caters for queries which will return
  1182. * a record set identifier for returning data.
  1183. * @param string $sql An SQL statement in full
  1184. * @return resource Returns a resource ID for the recordset
  1185. */
  1186. function dbrecordset($sql) {
  1187. $res = new dbrecords($sql);
  1188. return $res;
  1189. } // dbrecordset
  1190. // ......................................................................
  1191.  
  1192. /**
  1193. * A wrapper to get the next sequence value from a named sequence..
  1194. * @param string $sequencename Name of the sequence
  1195. * @param string $column Name of the column sequence is on
  1196. * @param string $table Name of the table column is on
  1197. * @return integer The value of the next integer in this sequence
  1198. */
  1199. function get_next_sequencevalue($sequencename, $table="", $column="") {
  1200. $seq = new dbseq($sequencename);
  1201. return $seq->next_sequencevalue($table, $column);
  1202. } // get_next_sequencevalue
  1203. // ......................................................................
  1204.  
  1205. /**
  1206. * A wrapper to get the current sequence value from a named sequence..
  1207. * @param string $sequencename Name of the sequence
  1208. * @param string $column Name of the column sequence is on
  1209. * @param string $table Name of the table column is on
  1210. * @return integer The value of the current integer in this sequence
  1211. */
  1212. function get_current_sequencevalue($sequencename, $table="", $column="") {
  1213. $seq = new dbseq($sequencename);
  1214. return $seq->current_sequencevalue($table, $column);
  1215. } // get_current_sequencevalue
  1216. // ......................................................................
  1217. // TRANSACTION Functions
  1218.  
  1219. /**
  1220. * Start a DB transaction. Alias for begin_transaction()
  1221. * @see begin_transaction()
  1222. */
  1223. function start_transaction() {
  1224. return begin_transaction();
  1225. } // start_transaction
  1226. // ......................................................................
  1227.  
  1228. /**
  1229. * Begin a DB transaction
  1230. */
  1231. function begin_transaction() {
  1232. global $global_tran;
  1233. $result = $global_tran->begin();
  1234. return $result;
  1235. } // begin_transaction
  1236. // ......................................................................
  1237.  
  1238. /**
  1239. * Return DB transaction failure status.
  1240. * @return bool True if transaction failed
  1241. */
  1242. function transaction_failed() {
  1243. global $global_tran;
  1244. return $global_tran->failed;
  1245. } // transaction_failed
  1246. // ......................................................................
  1247.  
  1248. /**
  1249. * Return DB transaction success status.
  1250. * @return bool True if transaction succeeded
  1251. */
  1252. function transaction_succeeded() {
  1253. return !transaction_failed();
  1254. } // transaction_succeeded
  1255. // ......................................................................
  1256.  
  1257. /**
  1258. * Return DB transaction open status.
  1259. * @return bool True if transaction already open
  1260. */
  1261. function transaction_open() {
  1262. global $global_tran;
  1263. return $global_tran->open;
  1264. } // transaction_open
  1265. // ......................................................................
  1266.  
  1267. /**
  1268. * Commit a DB transaction
  1269. * @return bool True if transaction committed
  1270. */
  1271. function commit() {
  1272. global $global_tran;
  1273. $result = $global_tran->commit();
  1274. return $result;
  1275. } // commit
  1276. // ......................................................................
  1277.  
  1278. /**
  1279. * Rollback a DB transaction
  1280. * @return bool True if transaction rolled back
  1281. */
  1282. function rollback() {
  1283. global $global_tran;
  1284. $result = $global_tran->rollback();
  1285. return $result;
  1286. } // rollback
  1287. // ......................................................................
  1288.  
  1289. /**
  1290. * Return true or false for boolean data returned by the current DB. This
  1291. * is to provide for boolean compatibility across all DBs.
  1292. * @param mixed $db_value Data to be interpreted as either true or false
  1293. * @return bool true of false
  1294. */
  1295. function bool_from_db_value($db_value) {
  1296. global $RESPONSE;
  1297. return $RESPONSE->datasource->bool_from_db_value($db_value);
  1298. } // bool_from_db_value
  1299. // ......................................................................
  1300.  
  1301. /**
  1302. * Return boolean data compatible with the current DB, from boolean. This
  1303. * is to provide for boolean compatibility across all DBs.
  1304. * @param bool $bool Booean true or false to be returned as DB value
  1305. * @return mixed Db-compatible boolean equivalent value
  1306. */
  1307. function db_value_from_bool($bool) {
  1308. global $RESPONSE;
  1309. return $RESPONSE->datasource->db_value_from_bool($bool);
  1310. } // db_value_from_bool
  1311. // ......................................................................
  1312.  
  1313. /**
  1314. * Escape a string according to the requirements of the current DB.
  1315. * @param string $str String to be escaped.
  1316. * @return string Escaped version of the string
  1317. */
  1318. function escape_string($str) {
  1319. global $RESPONSE;
  1320. return $RESPONSE->datasource->escape_string($str);
  1321. } // escape_string
  1322. // ......................................................................
  1323.  
  1324. /**
  1325. * Unescape a string according to the requirements of the current DB.
  1326. * @param string $str String to be unescaped.
  1327. * @return string Unescaped version of the escaped string
  1328. */
  1329. function unescape_string($str) {
  1330. global $RESPONSE;
  1331. return $RESPONSE->datasource->unescape_string($str);
  1332. } // unescape_string
  1333. // ----------------------------------------------------------------------
  1334. // LOCKING
  1335.  
  1336. /**
  1337. * Take out a lock on a table or tables, in a given mode. The mode string
  1338. * is database-specific and will vary according to the implementation
  1339. * of its locking scheme.
  1340. * @param string $tablelist List of tables to lock, comma-delimited
  1341. * @param string $mode Databes-specific locking-mode or type
  1342. */
  1343. function lock($tablelist, $mode) {
  1344. global $RESPONSE;
  1345. return $RESPONSE->datasource->lock($tablelist, $mode);
  1346. } // lock
  1347. // ----------------------------------------------------------------------
  1348. // DATABASE SELECTION
  1349.  
  1350. /**
  1351. * Global function to set the selected database to a new database. All
  1352. * subsequent queries will then act on that database.
  1353. * @param string $db_name Name of database to select - nullstring for 'default'.
  1354. */
  1355. function select_database($db_name="") {
  1356. global $RESPONSE;
  1357. return $RESPONSE->select_database($db_name);
  1358. }
  1359. ?>

Documentation generated by phpDocumentor 1.3.0RC3