Andrew's Web Libraries (AWL)
 All Classes Namespaces Functions Variables Pages
DataUpdate.php
1 <?php
15 require_once('AWLUtilities.php');
16 require_once('AwlQuery.php');
17 
18 
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 
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 
122 class DBRecord
123 {
131  var $Table;
132 
138  var $Fields;
139 
144  var $Keys;
145 
150  var $Values;
151 
156  var $WriteType;
157 
162  var $OtherTable;
163 
170  var $OtherTargets;
171 
177  var $OtherJoin;
178 
184  var $OtherWhere;
185 
195  var $EditMode;
196 
202  function __construct() {
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 
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 
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 
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 
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 
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 
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 
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 
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 
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 
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 
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 
PostToValues($prefix="")
Definition: DataUpdate.php:245
AddTable($table, $target_list, $join_clause, $and_where)
Definition: DataUpdate.php:233
static quote($str=null)
Definition: AwlQuery.php:316
Set($fname, $fval)
Definition: DataUpdate.php:322
_BuildJoinClause()
Definition: DataUpdate.php:259
Undefine($fname)
Definition: DataUpdate.php:343
Get($fname)
Definition: DataUpdate.php:333
_BuildWhereClause($overwrite_values=false)
Definition: DataUpdate.php:295
Initialise($table, $keys=array())
Definition: DataUpdate.php:217
_BuildFieldList()
Definition: DataUpdate.php:274