/.config-rdbms
[return to app]1
<?php
2 /**
3 * Change mysqlabstract in BOTH TWO places below to extend whichever PHP database extension that you will use
4 *
5 * For PostgreSQL/PGSQL use: postgresql
6 * For Oracle OCI8 use: oracle
7 * For MS SQL use: mssql
8 * For IBM DB2 use: db2
9 * For Sybase use: sybase
10 * For SQLite use: sqlite3abstract
11 * For MySQL (the default) or Amazon RDS use: mysqlabstract
12 * To use the older [deprecated] "mysql" PHP extension (instead of newer "mysqli") adjust the /sql/mysqlabstract
file
13 */
14 require 'sql/mysqlabstract';
15
16 class db extends mysqlabstract {
17 /**
18 * Date format used by your database
19 * This is set here for convenience and not required if you will not use it within your models
20 */
21 const DATE_FORMAT = '%b %e, %Y';
22
23 /**
24 * Date/time format used by your database
25 * This is set here for convenience and not required if you will not use it within your models
26 */
27 const DATETIME_FORMAT = '%b %e, %Y %l:%i %p';
28
29 /**
30 * Public interface to DATE_FORMAT constant
31 * @toDo Remove this once PHP 5.3 becomes more mainstream
32 * @deprecated If you are on PHP 5.3+ you can access the db::DATE_FORMAT constant directly & remove this
method
33 * @return string
34 */
35 public function getDateFormat() {
36 return self::DATE_FORMAT;
37 }
38
39 /**
40 * Public interface to DATETIME_FORMAT constant
41 * @toDo Remove this once PHP 5.3 becomes more mainstream
42 * @deprecated If you are on PHP 5.3+ you can access the db::DATETIME_FORMAT constant directly & remove this
method
43 * @return string
44 */
45 public function getDateTimeFormat() {
46 return self::DATETIME_FORMAT;
47 }
48
49 /**
50 * This is technically a constant but it is set as a public variable to give the option of turning off
51 * surrounding quotes by simply setting to an empty string from within the code.
52 *
53 * @var string
54 */
55 public $surroundingQuote = "'";
56
57 /**
58 * Prepares a string or an array of strings for use within a SQL query
59 * $this->db->cleanString("Invalid chars ain't gonna break your SQL!")
60 *
61 * @param mixed $var Can be a string or an array of strings (key-associations will be maintained)
62 * @return mixed Returns the same format sent to it in the arg (string or array of strings)
63 */
64 public function cleanString($var) {
65 if (!is_array($var)) {
66 $var = $this->surroundingQuote . $this->{self::ESCAPE_STRING}($var) . $this->surroundingQuote;
67 } else {
68 foreach ($var as $key => $val) {
69 $var[$key] = $this->cleanString($val);
70 }
71 }
72 return $var;
73 }
74
75 /**
76 * Direct replacement for query() that adds automatic value cleansing and substitution
77 *
78 * Uses the prepared-statement standard question-mark as the placeholder, eg.:
79 * $quantity = 7; $name = $_POST['name']; $id = 613; $color = 'blue';
80 * $sql = 'update widgets set quantity=?, name=? where id=? and color=?';
81 * $this->db->pquery($sql, $quantity, $name, $id, $color);
82 *
83 * Alternatively you can send the parameters in an array:
84 * $params = array($quantity, $name, $id, $color);
85 * $this->db->pquery($sql, $params);
86 *
87 * Or even mix the two formats:
88 * $params = array($name, $id);
89 * $this->db->pquery($sql, $quantity, $params, $color);
90 *
91 * @param string $sql
92 * @param mixed Optional one or more arguments for SQL substitution, can be scalar or an array of scalar
values
93 * @return object
94 */
95 public function pquery($sql /* polymorphic */) {
96 $args = func_get_args();
97 array_shift($args); //remove SQL string
98 $replacementCount = substr_count($sql, '?');
99 if ($replacementCount && $args) {
100 $params = array();
101 foreach ($args as $arg) {
102 if (!is_array($arg)) {
103 $params[] = $arg;
104 } else {
105 $params = array_merge($params, $arg);
106 }
107 }
108 if (count($params) < $replacementCount) {
109 $params = array_pad($params, $replacementCount, null);
110 }
111 if ($params) {
112 $params = $this->cleanString($params);
113 $x = -1;
114 $sql = preg_replace('/\?/e', '$params[++$x]', $sql);
115 }
116 }
117 return $this->query($sql);
118 }
119
120 /**
121 * Buffer to store the cols after extracting in the insertSql() method
122 * @var mixed
123 */
124 protected $_cols;
125
126 /**
127 * Builds the insert SQL string based on an array of values.
128 *
129 * ANSI-standard insert syntax should work with all databases.
130 *
131 * Argument array keys are:
132 * table - required
133 * vals - required, can be a string (for inserting into one column only), an array of column values
134 * or an array containing multiple subarrays of column values to insert multiple rows with one query.
135 * The array keys in the vals argument can be set to correspond to the column names as an alternative
136 * to setting the cols argument (below.) If both are set, cols takes precedence.
137 * cols - optional, it is highly recommended to always include this to ensure that nothing breaks if
138 * you modify the table structure
139 *
140 * @param array $args
141 * @return string
142 */
143 public function insertSql(array $args) {
144 $sql = 'insert into ' . $args['table'];
145 if (!isset($args['cols']) && is_array($args['vals'])) {
146 if (key($args['vals']) !== 0) {
147 $args['cols'] = array_keys($args['vals']);
148 } else if (is_array($args['vals'][0]) && key($args['vals'][0]) !== 0) {
149 $args['cols'] = array_keys($args['vals'][0]);
150 }
151 }
152 if (isset($args['cols'])) {
153 $this->_cols = $args['cols'];
154 $sql .= ' (' . (is_array($args['cols']) ? implode(', ', $args['cols']) : $args['cols']) . ')';
155 }
156 $sql .= ' values (';
157 if (!is_array($args['vals'])) {
158 $sql .= $args['vals'];
159 } else if (!is_array(current($args['vals']))) {
160 $sql .= implode(', ', $args['vals']);
161 } else {
162 foreach ($args['vals'] as $key => $valueArray) {
163 $args['vals'][$key] = implode(', ', $valueArray);
164 }
165 $sql .= implode('), (', $args['vals']);
166 }
167 $sql .= ')';
168 return $sql;
169 }
170
171 /**
172 * Shortcut method for retrieving data from queries returning exactly two columns of data
173 * SQL that returs just one column will trigger PHP warnings. Columns in position 3 or greater will be
ignored.
174 *
175 * @param string $sql
176 * @return array Keys are the first column of data, values are the second column
177 */
178 public function keyval($sql) {
179 $fetch = (get_parent_class() != 'sqlite3abstract' ? 'fetch_row' : 'fetchArray');
180 $res = $this->query($sql);
181 while ($row = $res->$fetch()) {
182 $keyvals[$row[0]] = $row[1];
183 }
184 return (isset($keyvals) ? $keyvals : array());
185 }
186 }
187
188 /**
189 * Configuration of the debug functionality. When debug mode is not enabled this does not get loaded and has no
effect
190 */
191 class dbDebug extends db {
192 /**
193 * If your database uses the method "query" with the same function signature (the same arguments) as
194 * this method then nothing needs to change here, otherwise in order to see your SQL queries when in
195 * debug mode you will need to change the method name and signature to the equivelent method that is
196 * utilized by your DB extension.
197 */
198 public function query($query, $arg2 = null) {
199 $timeStart = debug::microtime();
200 $return = (!$arg2 ? parent::query($query) : parent::query($query, $arg2));
201 debug::logQuery($query, (number_format(debug::microtime() - $timeStart, 5)), $this->error);
202 return $return;
203 }
204 }