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

Documentation generated by phpDocumentor 1.3.0RC3