Overview

Packages

  • awl
    • AuthPlugin
    • AwlDatabase
    • Browser
    • classEditor
    • DataEntry
    • DataUpdate
    • EMail
    • iCalendar
    • MenuSet
    • PgQuery
    • Session
    • Translation
    • User
    • Utilities
    • Validation
    • vCalendar
    • vComponent
    • XMLDocument
    • XMLElement
  • None
  • PHP

Classes

  • DBRecord

Functions

  • sql_from_object
  • sql_from_post
  • Overview
  • Package
  • Function
  • Tree
  • Deprecated
  • Todo
  1: <?php
  2: /**
  3: * Some functions and a base class to help with updating records.
  4: *
  5: * This subpackage provides some functions that are useful around single
  6: * record database activities such as insert and update.
  7: *
  8: * @package   awl
  9: * @subpackage   DataUpdate
 10: * @author Andrew McMillan <andrew@mcmillan.net.nz>
 11: * @copyright Catalyst IT Ltd, Morphoss Ltd <http://www.morphoss.com/>
 12: * @license   http://gnu.org/copyleft/gpl.html GNU GPL v2 or later
 13: */
 14: 
 15: require_once('AWLUtilities.php');
 16: require_once('AwlQuery.php');
 17: 
 18: 
 19: /**
 20: * Build SQL INSERT/UPDATE statement from an associative array of fieldnames => values.
 21: * @param array $obj The object  of fieldnames => values.
 22: * @param string $type The word "update" or something else (which implies "insert").
 23: * @param string $tablename The name of the table being updated.
 24: * @param string $where What the "WHERE ..." clause needs to be for an UPDATE statement.
 25: * @param string $fprefix An optional string which all fieldnames in $assoc are prefixed with.
 26: * @return string An SQL Update or Insert statement with all fields/values from the array.
 27: */
 28: function sql_from_object( $obj, $type, $tablename, $where, $fprefix = "" ) {
 29:   $fields = awl_get_fields($tablename);
 30:   $update = strtolower($type) == "update";
 31:   if ( $update )
 32:     $sql = "UPDATE $tablename SET ";
 33:   else
 34:     $sql = "INSERT INTO $tablename (";
 35: 
 36:   $flst = "";
 37:   $vlst = "";
 38:   foreach( $fields as $fn => $typ ) {
 39:     // $prefixed_fn = $fprefix . $fn;
 40:     dbg_error_log( "DataUpdate", ":sql_from_object: %s => %s (%s)", $fn, $typ, (isset($obj->{$fn})?$obj->{$fn}:"[undefined value]"));
 41:     if ( !isset($obj->{$fn}) && isset($obj->{"xxxx$fn"}) ) {
 42:       // Sometimes we will have prepended 'xxxx' to the field name so that the field
 43:       // name differs from the column name in the database.
 44:       $obj->{$fn} = $obj->{"xxxx$fn"};
 45:     }
 46:     if ( !isset($obj->{$fn}) ) continue;
 47:     $value = $obj->{$fn};
 48:     if ( $fn == "password" ) {
 49:       if ( $value == "******" || $value == "" ) continue;
 50:       if ( !preg_match('/^\*[0-9a-z+\/=]+\*({SSHA})?[0-9a-z+\/=]+$/i', $value ) ) {
 51:         $value = (function_exists("session_salted_sha1")
 52:                    ? session_salted_sha1($value)
 53:                    : (function_exists('session_salted_md5')
 54:                        ? session_salted_md5($value)
 55:                        : md5($value)
 56:                      )
 57:                  );
 58:       }
 59:     }
 60:     $value = str_replace( "'", "''", str_replace("\\", "\\\\", $value));
 61:     if ( preg_match('{^(time|date|interval)}i', $typ ) && $value == "" ) {
 62:       $value = "NULL";
 63:     }
 64:     else if ( preg_match('{^bool}i', $typ) )  {
 65:       $value = ( $value == false || $value == "f" || $value == "off" || $value == "no" ? "FALSE"
 66:                   : ( $value == true || $value == "t" || $value == "on" || $value == "yes" ? "TRUE"
 67:                       : "NULL" ));
 68:     }
 69:     else if ( preg_match('{^interval}i', $typ) )  {
 70:       $value = "'$value'::$typ";
 71:     }
 72:     else if ( preg_match('{^int}i', $typ) )  {
 73:       $value = ($value == '' || $value === null ? 'NULL' : intval( $value ));
 74:     }
 75:     else if ( preg_match('{^bit}i', $typ) )  {
 76:       $value = ($value == '' || $value === null ? 'NULL' : "'$value'");
 77:     }
 78:     else if ( preg_match('{^(text|varchar)}i', $typ) )  {
 79:       $value = "'$value'";
 80:     }
 81:     else
 82:       $value = "'$value'::$typ";
 83: 
 84:     if ( $update )
 85:       $flst .= ", $fn = $value";
 86:     else {
 87:       $flst .= ", $fn";
 88:       $vlst .= ", $value";
 89:     }
 90:   }
 91:   $flst = substr($flst,2);
 92:   $vlst = substr($vlst,2);
 93:   $sql .= $flst;
 94:   if ( $update ) {
 95:     $sql .= " $where; ";
 96:   }
 97:   else {
 98:     $sql .= ") VALUES( $vlst ); ";
 99:   }
100:  return $sql;
101: }
102: 
103: 
104: /**
105: * Build SQL INSERT/UPDATE statement from the $_POST associative array
106: * @param string $type The word "update" or something else (which implies "insert").
107: * @param string $tablename The name of the table being updated.
108: * @param string $where What the "WHERE ..." clause needs to be for an UPDATE statement.
109: * @param string $fprefix An optional string which all fieldnames in $assoc are prefixed with.
110: * @return string An SQL Update or Insert statement with all fields/values from the array.
111: */
112: function sql_from_post( $type, $tablename, $where, $fprefix = "" ) {
113:   $fakeobject = (object) $_POST;
114:   return sql_from_object( $fakeobject, $type, $tablename, $where, $fprefix );
115: }
116: 
117: 
118: /**
119: * A Base class to use for records which will be read/written from the database.
120: * @package   awl
121: */
122: class DBRecord
123: {
124:   /**#@+
125:   * @access private
126:   */
127:   /**
128:   * The database table that this record goes in
129:   * @var string
130:   */
131:   var $Table;
132: 
133:   /**
134:   * The field names for the record.  The array index is the field name
135:   * and the array value is the field type.
136:   * @var array
137:   */
138:   var $Fields;
139: 
140:   /**
141:   * The keys for the record as an array of key => value pairs
142:   * @var array
143:   */
144:   var $Keys;
145: 
146:   /**
147:   * The field values for the record
148:   * @var object
149:   */
150:   var $Values;
151: 
152:   /**
153:   * The type of database write we will want: either "update" or "insert"
154:   * @var object
155:   */
156:   var $WriteType;
157: 
158:   /**
159:   * A list of associated other tables.
160:   * @var array of string
161:   */
162:   var $OtherTable;
163: 
164:   /**
165:   * The field names for each of the other tables associated.  The array index
166:   * is the table name, the string is a list of field names (and perhaps aliases)
167:   * to stuff into the target list for the SELECT.
168:   * @var array of string
169:   */
170:   var $OtherTargets;
171: 
172:   /**
173:   * An array of JOIN ... clauses.  The first array index is the table name and the array value
174:   * is the JOIN clause like "LEFT JOIN tn t1 USING (myforeignkey)".
175:   * @var array of string
176:   */
177:   var $OtherJoin;
178: 
179:   /**
180:   * An array of partial WHERE clauses.  These will be combined (if present) with the key
181:   * where clause on the main table.
182:   * @var array of string
183:   */
184:   var $OtherWhere;
185: 
186:   /**#@-*/
187: 
188:   /**#@+
189:   * @access public
190:   */
191:   /**
192:   * The mode we are in for any form
193:   * @var object
194:   */
195:   var $EditMode;
196: 
197:   /**#@-*/
198: 
199:   /**
200:   * Really numbingly simple construction.
201:   */
202:   function DBRecord( ) {
203:     dbg_error_log( "DBRecord", ":Constructor: called" );
204:     $this->WriteType = "insert";
205:     $this->EditMode = false;
206:     $this->prefix = "";
207:     $values = (object) array();
208:     $this->Values = &$values;
209:   }
210: 
211:   /**
212:   * This will read the record from the database if it's available, and
213:   * the $keys parameter is a non-empty array.
214:   * @param string $table The name of the database table
215:   * @param array $keys An associative array containing fieldname => value pairs for the record key.
216:   */
217:   function Initialise( $table, $keys = array() ) {
218:     dbg_error_log( "DBRecord", ":Initialise: called" );
219:     $this->Table = $table;
220:     $this->Fields = awl_get_fields($this->Table);
221:     $this->Keys = $keys;
222:     $this->WriteType = "insert";
223:   }
224: 
225:   /**
226:   * This will join an additional table to the maintained set
227:   * @param string $table The name of the database table
228:   * @param array $keys An associative array containing fieldname => value pairs for the record key.
229:   * @param string $join A PostgreSQL join clause.
230:   * @param string $prefix A field prefix to use for these fields to distinguish them from fields
231:   *                       in other joined tables with the same name.
232:   */
233:   function AddTable( $table, $target_list, $join_clause, $and_where ) {
234:     dbg_error_log( "DBRecord", ":AddTable: $table called" );
235:     $this->OtherTable[] = $table;
236:     $this->OtherTargets[$table] = $target_list;
237:     $this->OtherJoin[$table] = $join_clause;
238:     $this->OtherWhere[$table] = $and_where;
239:   }
240: 
241:   /**
242:   * This will assign $_POST values to the internal Values object for each
243:   * field that exists in the Fields array.
244:   */
245:   function PostToValues( $prefix = "" ) {
246:     foreach ( $this->Fields AS $fname => $ftype ) {
247:       @dbg_error_log( "DBRecord", ":PostToValues: %s => %s", $fname, $_POST["$prefix$fname"] );
248:       if ( isset($_POST["$prefix$fname"]) ) {
249:         $this->Set($fname, $_POST["$prefix$fname"]);
250:         @dbg_error_log( "DBRecord", ":PostToValues: %s => %s", $fname, $_POST["$prefix$fname"] );
251:       }
252:     }
253:   }
254: 
255:   /**
256:   * Builds a table join clause
257:   * @return string A simple SQL target join clause excluding the primary table.
258:   */
259:   function _BuildJoinClause() {
260:     $clause = "";
261:     foreach( $this->OtherJoins AS $t => $join ) {
262:       if ( ! preg_match( '/^\s*$/', $join ) ) {
263:         $clause .= ( $clause == "" ? "" : " " )  . $join;
264:       }
265:     }
266: 
267:     return $clause;
268:   }
269: 
270:   /**
271:   * Builds a field target list
272:   * @return string A simple SQL target field list for each field, possibly including prefixes.
273:   */
274:   function _BuildFieldList() {
275:     $list = "";
276:     foreach( $this->Fields AS $fname => $ftype ) {
277:       $list .= ( $list == "" ? "" : ", " );
278:       $list .= "$fname" . ( $this->prefix == "" ? "" : " AS \"$this->prefix$fname\"" );
279:     }
280: 
281:     foreach( $this->OtherTargets AS $t => $targets ) {
282:       if ( ! preg_match( '/^\s*$/', $targets ) ) {
283:         $list .= ( $list == "" ? "" : ", " )  . $targets;
284:       }
285:     }
286: 
287:     return $list;
288:   }
289: 
290:   /**
291:   * Builds a where clause to match the supplied keys
292:   * @param boolean $overwrite_values Controls whether the data values for the key fields will be forced to match the key values
293:   * @return string A simple SQL where clause, including the initial "WHERE", for each key / value.
294:   */
295:   function _BuildWhereClause($overwrite_values=false) {
296:     $where = "";
297:     foreach( $this->Keys AS $k => $v ) {
298:       // At least assign the key fields...
299:       if ( $overwrite_values ) $this->Values->{$k} = $v;
300:       // And build the WHERE clause
301:       $where .= ( $where == '' ? 'WHERE ' : ' AND ' );
302:       $where .= $k . '=' . AwlQuery::quote($v);
303:     }
304: 
305:     if ( isset($this->OtherWhere) && is_array($this->OtherWhere) ) {
306:       foreach( $this->OtherWhere AS $t => $and_where ) {
307:         if ( ! preg_match( '/^\s*$/', $and_where ) ) {
308:           $where .= ($where == '' ? 'WHERE ' : ' AND (' )  . $and_where . ')';
309:         }
310:       }
311:     }
312: 
313:     return $where;
314:   }
315: 
316:   /**
317:   * Sets a single field in the record
318:   * @param string $fname The name of the field to set the value for
319:   * @param string $fval The value to set the field to
320:   * @return mixed The new value of the field (i.e. $fval).
321:   */
322:   function Set($fname, $fval) {
323:     dbg_error_log( "DBRecord", ":Set: %s => %s", $fname, $fval );
324:     $this->Values->{$fname} = $fval;
325:     return $fval;
326:   }
327: 
328:   /**
329:   * Returns a single field from the record
330:   * @param string $fname The name of the field to set the value for
331:   * @return mixed The current value of the field.
332:   */
333:   function Get($fname) {
334:     @dbg_error_log( "DBRecord", ":Get: %s => %s", $fname, $this->Values->{$fname} );
335:     return (isset($this->Values->{$fname}) ? $this->Values->{$fname} : null);
336:   }
337: 
338:   /**
339:   * Unsets a single field from the record
340:   * @param string $fname The name of the field to unset the value for
341:   * @return mixed The current value of the field.
342:   */
343:   function Undefine($fname) {
344:     if ( !isset($this->Values->{$fname}) ) return null;
345:     $current = $this->Values->{$fname};
346:     dbg_error_log( 'DBRecord', ': Unset: %s =was> %s', $fname, $current );
347:     unset($this->Values->{$fname});
348:     return $current;
349:   }
350: 
351:   /**
352:   * To write the record to the database
353:   * @return boolean Success.
354:   */
355:   function Write() {
356:     dbg_error_log( "DBRecord", ":Write: %s record as %s.", $this->Table, $this->WriteType );
357:     $sql = sql_from_object( $this->Values, $this->WriteType, $this->Table, $this->_BuildWhereClause(), $this->prefix );
358:     $qry = new AwlQuery($sql);
359:     return $qry->Exec( "DBRecord", __LINE__, __FILE__ );
360:   }
361: 
362:   /**
363:   * To read the record from the database.
364:   * If we don't have any keys then the record will be blank.
365:   * @return boolean Whether we actually read a record.
366:   */
367:   function Read() {
368:     $i_read_the_record = false;
369:     $values = (object) array();
370:     $this->EditMode = true;
371:     $where = $this->_BuildWhereClause(true);
372:     if ( "" != $where ) {
373:       // $fieldlist = $this->_BuildFieldList();
374:       $fieldlist = "*";
375:   //    $join = $this->_BuildJoinClause(true);
376:       $sql = "SELECT $fieldlist FROM $this->Table $where";
377:       $qry = new AwlQuery($sql);
378:       if ( $qry->Exec( "DBRecord", __LINE__, __FILE__ ) && $qry->rows() > 0 ) {
379:         $i_read_the_record = true;
380:         $values = $qry->Fetch();
381:         $this->EditMode = false;  // Default to not editing if we read the record.
382:         dbg_error_log( "DBRecord", ":Read: Read %s record from table.", $this->Table, $this->WriteType );
383:       }
384:     }
385:     $this->Values = &$values;
386:     $this->WriteType = ( $i_read_the_record ? "update" : "insert" );
387:     dbg_error_log( "DBRecord", ":Read: Record %s write type is %s.", $this->Table, $this->WriteType );
388:     return $i_read_the_record;
389:   }
390: }
391: 
392: 
AWL API documentation generated by ApiGen 2.8.0