1:   2:   3:   4:   5:   6:   7:   8:   9:  10:  11:  12:  13:  14:  15:  16:  17:  18:  19:  20:  21:  22:  23:  24:  25:  26:  27:  28:  29:  30:  31:  32:  33:  34:  35:  36:  37:  38:  39:  40:  41:  42:  43:  44:  45:  46:  47:  48:  49:  50:  51:  52:  53:  54:  55:  56:  57:  58:  59:  60:  61:  62:  63:  64:  65:  66:  67:  68:  69:  70:  71:  72:  73:  74:  75:  76:  77:  78:  79:  80:  81:  82:  83:  84:  85:  86:  87:  88:  89:  90:  91:  92:  93:  94:  95:  96:  97:  98:  99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256: 257: 258: 259: 260: 261: 262: 263: 264: 265: 266: 267: 268: 269: 270: 271: 272: 273: 274: 275: 276: 277: 278: 279: 280: 281: 282: 283: 284: 285: 286: 287: 288: 289: 290: 291: 292: 293: 294: 295: 296: 297: 298: 299: 300: 301: 302: 303: 304: 305: 306: 307: 308: 309: 310: 311: 312: 313: 314: 315: 316: 317: 318: 319: 320: 321: 322: 323: 324: 325: 326: 327: 328: 329: 330: 331: 332: 333: 334: 335: 336: 337: 338: 339: 340: 341: 342: 343: 344: 345: 346: 347: 348: 349: 350: 351: 352: 353: 354: 355: 356: 357: 358: 359: 360: 361: 362: 363: 364: 365: 366: 367: 368: 369: 370: 371: 372: 373: 374: 375: 376: 377: 378: 379: 380: 381: 382: 383: 384: 385: 386: 387: 388: 389: 390: 391: 392: 

<?php
/**
* Some functions and a base class to help with updating records.
*
* This subpackage provides some functions that are useful around single
* record database activities such as insert and update.
*
* @package   awl
* @subpackage   DataUpdate
* @author Andrew McMillan <andrew@mcmillan.net.nz>
* @copyright Catalyst IT Ltd, Morphoss Ltd <http://www.morphoss.com/>
* @license   http://gnu.org/copyleft/gpl.html GNU GPL v2 or later
*/

require_once('AWLUtilities.php');
require_once('AwlQuery.php');


/**
* Build SQL INSERT/UPDATE statement from an associative array of fieldnames => values.
* @param array $obj The object  of fieldnames => values.
* @param string $type The word "update" or something else (which implies "insert").
* @param string $tablename The name of the table being updated.
* @param string $where What the "WHERE ..." clause needs to be for an UPDATE statement.
* @param string $fprefix An optional string which all fieldnames in $assoc are prefixed with.
* @return string An SQL Update or Insert statement with all fields/values from the array.
*/
function sql_from_object( $obj, $type, $tablename, $where, $fprefix = "" ) {
  $fields = get_fields($tablename);
  $update = strtolower($type) == "update";
  if ( $update )
    $sql = "UPDATE $tablename SET ";
  else
    $sql = "INSERT INTO $tablename (";

  $flst = "";
  $vlst = "";
  foreach( $fields as $fn => $typ ) {
    // $prefixed_fn = $fprefix . $fn;
    dbg_error_log( "DataUpdate", ":sql_from_object: %s => %s (%s)", $fn, $typ, (isset($obj->{$fn})?$obj->{$fn}:"[undefined value]"));
    if ( !isset($obj->{$fn}) && isset($obj->{"xxxx$fn"}) ) {
      // Sometimes we will have prepended 'xxxx' to the field name so that the field
      // name differs from the column name in the database.
      $obj->{$fn} = $obj->{"xxxx$fn"};
    }
    if ( !isset($obj->{$fn}) ) continue;
    $value = $obj->{$fn};
    if ( $fn == "password" ) {
      if ( $value == "******" || $value == "" ) continue;
      if ( !preg_match('/^\*[0-9a-z+\/=]+\*({SSHA})?[0-9a-z+\/=]+$/i', $value ) ) {
        $value = (function_exists("session_salted_sha1")
                   ? session_salted_sha1($value)
                   : (function_exists('session_salted_md5')
                       ? session_salted_md5($value)
                       : md5($value)
                     )
                 );
      }
    }
    $value = str_replace( "'", "''", str_replace("\\", "\\\\", $value));
    if ( preg_match('{^(time|date|interval)}i', $typ ) && $value == "" ) {
      $value = "NULL";
    }
    else if ( preg_match('{^bool}i', $typ) )  {
      $value = ( $value == false || $value == "f" || $value == "off" || $value == "no" ? "FALSE"
                  : ( $value == true || $value == "t" || $value == "on" || $value == "yes" ? "TRUE"
                      : "NULL" ));
    }
    else if ( preg_match('{^interval}i', $typ) )  {
      $value = "'$value'::$typ";
    }
    else if ( preg_match('{^int}i', $typ) )  {
      $value = ($value == '' || $value === null ? 'NULL' : intval( $value ));
    }
    else if ( preg_match('{^bit}i', $typ) )  {
      $value = ($value == '' || $value === null ? 'NULL' : "'$value'");
    }
    else if ( preg_match('{^(text|varchar)}i', $typ) )  {
      $value = "'$value'";
    }
    else
      $value = "'$value'::$typ";

    if ( $update )
      $flst .= ", $fn = $value";
    else {
      $flst .= ", $fn";
      $vlst .= ", $value";
    }
  }
  $flst = substr($flst,2);
  $vlst = substr($vlst,2);
  $sql .= $flst;
  if ( $update ) {
    $sql .= " $where; ";
  }
  else {
    $sql .= ") VALUES( $vlst ); ";
  }
 return $sql;
}


/**
* Build SQL INSERT/UPDATE statement from the $_POST associative array
* @param string $type The word "update" or something else (which implies "insert").
* @param string $tablename The name of the table being updated.
* @param string $where What the "WHERE ..." clause needs to be for an UPDATE statement.
* @param string $fprefix An optional string which all fieldnames in $assoc are prefixed with.
* @return string An SQL Update or Insert statement with all fields/values from the array.
*/
function sql_from_post( $type, $tablename, $where, $fprefix = "" ) {
  $fakeobject = (object) $_POST;
  return sql_from_object( $fakeobject, $type, $tablename, $where, $fprefix );
}


/**
* A Base class to use for records which will be read/written from the database.
* @package   awl
*/
class DBRecord
{
  /**#@+
  * @access private
  */
  /**
  * The database table that this record goes in
  * @var string
  */
  var $Table;

  /**
  * The field names for the record.  The array index is the field name
  * and the array value is the field type.
  * @var array
  */
  var $Fields;

  /**
  * The keys for the record as an array of key => value pairs
  * @var array
  */
  var $Keys;

  /**
  * The field values for the record
  * @var object
  */
  var $Values;

  /**
  * The type of database write we will want: either "update" or "insert"
  * @var object
  */
  var $WriteType;

  /**
  * A list of associated other tables.
  * @var array of string
  */
  var $OtherTable;

  /**
  * The field names for each of the other tables associated.  The array index
  * is the table name, the string is a list of field names (and perhaps aliases)
  * to stuff into the target list for the SELECT.
  * @var array of string
  */
  var $OtherTargets;

  /**
  * An array of JOIN ... clauses.  The first array index is the table name and the array value
  * is the JOIN clause like "LEFT JOIN tn t1 USING (myforeignkey)".
  * @var array of string
  */
  var $OtherJoin;

  /**
  * An array of partial WHERE clauses.  These will be combined (if present) with the key
  * where clause on the main table.
  * @var array of string
  */
  var $OtherWhere;

  /**#@-*/

  /**#@+
  * @access public
  */
  /**
  * The mode we are in for any form
  * @var object
  */
  var $EditMode;

  /**#@-*/

  /**
  * Really numbingly simple construction.
  */
  function DBRecord( ) {
    dbg_error_log( "DBRecord", ":Constructor: called" );
    $this->WriteType = "insert";
    $this->EditMode = false;
    $this->prefix = "";
    $values = (object) array();
    $this->Values = &$values;
  }

  /**
  * This will read the record from the database if it's available, and
  * the $keys parameter is a non-empty array.
  * @param string $table The name of the database table
  * @param array $keys An associative array containing fieldname => value pairs for the record key.
  */
  function Initialise( $table, $keys = array() ) {
    dbg_error_log( "DBRecord", ":Initialise: called" );
    $this->Table = $table;
    $this->Fields = get_fields($this->Table);
    $this->Keys = $keys;
    $this->WriteType = "insert";
  }

  /**
  * This will join an additional table to the maintained set
  * @param string $table The name of the database table
  * @param array $keys An associative array containing fieldname => value pairs for the record key.
  * @param string $join A PostgreSQL join clause.
  * @param string $prefix A field prefix to use for these fields to distinguish them from fields
  *                       in other joined tables with the same name.
  */
  function AddTable( $table, $target_list, $join_clause, $and_where ) {
    dbg_error_log( "DBRecord", ":AddTable: $table called" );
    $this->OtherTable[] = $table;
    $this->OtherTargets[$table] = $target_list;
    $this->OtherJoin[$table] = $join_clause;
    $this->OtherWhere[$table] = $and_where;
  }

  /**
  * This will assign $_POST values to the internal Values object for each
  * field that exists in the Fields array.
  */
  function PostToValues( $prefix = "" ) {
    foreach ( $this->Fields AS $fname => $ftype ) {
      @dbg_error_log( "DBRecord", ":PostToValues: %s => %s", $fname, $_POST["$prefix$fname"] );
      if ( isset($_POST["$prefix$fname"]) ) {
        $this->Set($fname, $_POST["$prefix$fname"]);
        @dbg_error_log( "DBRecord", ":PostToValues: %s => %s", $fname, $_POST["$prefix$fname"] );
      }
    }
  }

  /**
  * Builds a table join clause
  * @return string A simple SQL target join clause excluding the primary table.
  */
  function _BuildJoinClause() {
    $clause = "";
    foreach( $this->OtherJoins AS $t => $join ) {
      if ( ! preg_match( '/^\s*$/', $join ) ) {
        $clause .= ( $clause == "" ? "" : " " )  . $join;
      }
    }

    return $clause;
  }

  /**
  * Builds a field target list
  * @return string A simple SQL target field list for each field, possibly including prefixes.
  */
  function _BuildFieldList() {
    $list = "";
    foreach( $this->Fields AS $fname => $ftype ) {
      $list .= ( $list == "" ? "" : ", " );
      $list .= "$fname" . ( $this->prefix == "" ? "" : " AS \"$this->prefix$fname\"" );
    }

    foreach( $this->OtherTargets AS $t => $targets ) {
      if ( ! preg_match( '/^\s*$/', $targets ) ) {
        $list .= ( $list == "" ? "" : ", " )  . $targets;
      }
    }

    return $list;
  }

  /**
  * Builds a where clause to match the supplied keys
  * @param boolean $overwrite_values Controls whether the data values for the key fields will be forced to match the key values
  * @return string A simple SQL where clause, including the initial "WHERE", for each key / value.
  */
  function _BuildWhereClause($overwrite_values=false) {
    $where = "";
    foreach( $this->Keys AS $k => $v ) {
      // At least assign the key fields...
      if ( $overwrite_values ) $this->Values->{$k} = $v;
      // And build the WHERE clause
      $where .= ( $where == '' ? 'WHERE ' : ' AND ' );
      $where .= $k . '=' . AwlQuery::quote($v);
    }

    if ( isset($this->OtherWhere) && is_array($this->OtherWhere) ) {
      foreach( $this->OtherWhere AS $t => $and_where ) {
        if ( ! preg_match( '/^\s*$/', $and_where ) ) {
          $where .= ($where == '' ? 'WHERE ' : ' AND (' )  . $and_where . ')';
        }
      }
    }

    return $where;
  }

  /**
  * Sets a single field in the record
  * @param string $fname The name of the field to set the value for
  * @param string $fval The value to set the field to
  * @return mixed The new value of the field (i.e. $fval).
  */
  function Set($fname, $fval) {
    dbg_error_log( "DBRecord", ":Set: %s => %s", $fname, $fval );
    $this->Values->{$fname} = $fval;
    return $fval;
  }

  /**
  * Returns a single field from the record
  * @param string $fname The name of the field to set the value for
  * @return mixed The current value of the field.
  */
  function Get($fname) {
    @dbg_error_log( "DBRecord", ":Get: %s => %s", $fname, $this->Values->{$fname} );
    return (isset($this->Values->{$fname}) ? $this->Values->{$fname} : null);
  }

  /**
  * Unsets a single field from the record
  * @param string $fname The name of the field to unset the value for
  * @return mixed The current value of the field.
  */
  function Undefine($fname) {
    if ( !isset($this->Values->{$fname}) ) return null;
    $current = $this->Values->{$fname};
    dbg_error_log( 'DBRecord', ': Unset: %s =was> %s', $fname, $current );
    unset($this->Values->{$fname});
    return $current;
  }

  /**
  * To write the record to the database
  * @return boolean Success.
  */
  function Write() {
    dbg_error_log( "DBRecord", ":Write: %s record as %s.", $this->Table, $this->WriteType );
    $sql = sql_from_object( $this->Values, $this->WriteType, $this->Table, $this->_BuildWhereClause(), $this->prefix );
    $qry = new AwlQuery($sql);
    return $qry->Exec( "DBRecord", __LINE__, __FILE__ );
  }

  /**
  * To read the record from the database.
  * If we don't have any keys then the record will be blank.
  * @return boolean Whether we actually read a record.
  */
  function Read() {
    $i_read_the_record = false;
    $values = (object) array();
    $this->EditMode = true;
    $where = $this->_BuildWhereClause(true);
    if ( "" != $where ) {
      // $fieldlist = $this->_BuildFieldList();
      $fieldlist = "*";
  //    $join = $this->_BuildJoinClause(true);
      $sql = "SELECT $fieldlist FROM $this->Table $where";
      $qry = new AwlQuery($sql);
      if ( $qry->Exec( "DBRecord", __LINE__, __FILE__ ) && $qry->rows() > 0 ) {
        $i_read_the_record = true;
        $values = $qry->Fetch();
        $this->EditMode = false;  // Default to not editing if we read the record.
        dbg_error_log( "DBRecord", ":Read: Read %s record from table.", $this->Table, $this->WriteType );
      }
    }
    $this->Values = &$values;
    $this->WriteType = ( $i_read_the_record ? "update" : "insert" );
    dbg_error_log( "DBRecord", ":Read: Record %s write type is %s.", $this->Table, $this->WriteType );
    return $i_read_the_record;
  }
}