Location: PHPKode > scripts > SQL Data > class.SQLData.php
<?php

/**
 * @author Dick Munroe <hide@address.com>
 * @copyright copyright (c) Dick Munroe, 2004-2006, All rights reserved.
 * @license http://www.csworks.com/publications/ModifiedNetBSD.html
 */

//
// Base class for any object backed by SQL data.
//
// Edit History:
//
//  Dick Munroe hide@address.com 09-Oct-2004
//      Initial Version Created.
//
//  Dick Munroe hide@address.com 13-Oct-2004
//      Add a function to return all field names for a table.
//
//  Dick Munroe hide@address.com 28-Oct-2004
//      Need to check for identity of null in constructions that
//      build SQL inserts and updates.
//
//  Dick Munroe hide@address.com 06-Nov-2004
//	Normalize naming conventions.
//	Add a next function that allows reloading of the class from the "next"
//	row a a result.
//	Add a function that tells you if the data has been modified.
//
//  Dick Munroe hide@address.com 08-Nov-2004
//	quote field names to avoid clashes with reserved words.
//
//  Dick Munroe hide@address.com 16-Nov-2004
//	Added a way to unset data from the object.
//
//  Dick Munroe hide@address.com 17-Nov-2004
//	Made the code formatting consistent with that used
//	by emacs in php mode with default settings.
//
//  Dick Munroe hide@address.com 05-Dec-2004
//	Add in is_set function to determine if data exists
//	in the fields collection.
//
//  Dick Munroe hide@address.com 06-Dec-2004
//	next would corrupt m_sqlFields if there was no data returned
//	by next
//
//  Dick Munroe hide@address.com 08-Dec-2004
//	The update and insert methods should clear the modified
//	data flags.
//
//  Dick Munroe hide@address.com 22-Dec-2004
//	Select should take a null selector and use needUpdateSelector.
//
//  Dick Munroe hide@address.com 14-Mar-2006
//	Change licensing, reorganize includes.
//
// 	Dick Munroe (hide@address.com) 15-Oct-2006
//		This implementation has been retired and is being kept for
//		purposes of compatibility with existing code.  All new code
//		generated with build class will be based on templates and
//		the new database independent implementation of dm.DB.
//

include_once('DB/class.DB.php') ;
include_once('SDD/class.SDD.php') ;

class SQLData extends DB
{
    var $m_modified = array() ;
    var $m_sqlFields = array() ;
    var $m_tableName = "" ;

    function SQLData($_tableName,
                     $_dataBase,
                     $_host='localhost',
                     $_login="",
                     $_password="")
    {
      $this->m_tableName = $_tableName ;

      //
      // Initialize the database connection for this database.  The connection
      // resource is saved although it generally isn't used.
      //

      $this->DB($_login, $_password, $_dataBase, $_host) ;
      $theConnection = $this->connect() ;

      if (is_string($theConnection))
        {
	  die($theConnection) ;
        }
    }

    function death($theMessage)
    {
      print("<br><br>") ;
      $this->print_rd(debug_backtrace()) ;
      print("<br><br><bold)") ;
      print($theMessage) ;
      print("</bold>") ;
      die() ;
    }

    function print_rd($theVar)
    {
      print("<pre>\n") ;
      print_r($theVar) ;
      print("</pre>") ;
      print("<br>") ;
    }

    function print_r()
    {
      print(SDD::dump($this, !empty($_SERVER['DOCUMENT_ROOT']))) ;
    }

    function getTableFieldNames()
    {
      $this->query("describe {$this->m_tableName}") ;

      if ($this->hasErrors())
        {
	  return false ;
        }

      $theFieldsArray = array() ;

      while ($theResultArray = $this->fetchAssoc())
        {
	  array_push($theFieldsArray, $theResultArray['Field']) ;
        }

      return $theFieldsArray ;
    }

    function getFields()
    {
      return $this->m_sqlFields ;
    }

    function getModified()
    {
      return $this->m_modified ;
    }

    function get($field)
    {
      if (isset($this->m_sqlFields[$field]))
        {
	  return $this->m_sqlFields[$field] ;
        }
      else
        {
	  return null ;
        }
    }

    function initFields($theFields)
    {
      foreach ($theFields as $theField => $theValue)
	{
	  $this->init($theField, $theValue) ;
	}
    }

    function init($field, $value)
    {
      $this->m_sqlFields[$field] = $value ;
      $this->m_modified[$field] = false ;
      if (is_object($value))
	{
	  if (!is_subclass_of($value, 'SQLData'))
	    {
	      $this->death('Attempting to insert an object not a subclass of SQLData (' . get_class($value) . ')') ;
	    }
	}
      else if (is_array($value))
	{
	  foreach ($value as $theIndex => $theElement)
            {
	      if (!is_subclass_of($theElement, 'SQLData'))
                {
		  $this->death('Attempting to insert an object not a subclass of SQLData (' . get_class($theElement) . ')') ;
		}
            }
	}
    }

    //
    // True if any of the fields of the class have been modified.
    // false otherwise.
    //

    function modified()
    {
      foreach ($this->modified as $theIndex => $theFlag)
	{
	  if ($theFlag)
	    {
	      return true ;
	    }
	}

      return false ;
    }

    //
    // Force the state of the modified flags.
    // As per the insert, update, and delete logic, arrays and objects are
    // always forced to false.
    //

    function forceModified($theState = true)
    {
      if ($theState)
	{
	  $theState = 'true' ;
	}
      else
	{
	  $theState = 'false' ;
	}

      $this->m_modified = array_map(create_function('$a', sprintf('
if ((is_array($a)) || (is_object($a)))
    return false ;
else
return %s ;
',
								  $theState)),
						    $this->m_sqlFields) ;
    }

    //
    // populate the class with the next value from the last query.
    // Internal state is reset so that no lingering data is left.
    //

    function next()
    {
      $this->reset() ;

      if (($xxx = $this->fetchAssoc()) !== false)
	{
	  $this->m_sqlFields = $xxx ;
	}

      if ($this->hasErrors())
        {
	  return false ;
        }

      if (!($this->m_sqlFields))
        {
	  return false ;
        }

      foreach($this->m_sqlFields as $theFieldName => $theFieldValue)
        {
	  $this->m_modified[$theFieldName] = false ;
        }

      return true ;
    }


    function reset()
    {
      $this->m_sqlFields = array() ;
      $this->m_modified = array() ;
    }

    function setFields($theFields)
    {
      foreach ($theFields as $theField => $theValue)
	{
	  $this->set($theField, $theValue) ;
	}
    }

    //
    // The set function allows objects and arrays, but forces these items
    // to be set to "unmodified".  They will be processed assuming that
    // they are SQLData objects (which they will be because the set forces
    // them to be that).
    //

    function set($field, $value)
    {
      $this->m_sqlFields[$field] = $value ;
      if (is_object($value))
	{
	  $this->m_modified[$field] = false ;
	  if (!is_subclass_of($value, 'SQLData'))
	    {
	      $this->death('Attempting to insert an object not a subclass of SQLData (' . get_class($value) . ')') ;
	    }
	}
      else if (is_array($value))
	{
	  $this->m_modified[$field] = false ;
	  foreach ($value as $theIndex => $theElement)
	    {
	      if (!is_subclass_of($theElement, 'SQLData'))
		{
		  $this->death('Attempting to insert an object not a subclass of SQLData (' . get_class($theElement) . ')') ;
		}
	    }
	}
      else
	{
	  $this->m_modified[$field] = true ;
	}
    }

    function is_set($field)
    {
      return isset($this->m_sqlFields[$field]) ;
    }

    function un_set($field)
    {
      unset($this->m_sqlFields[$field]) ;
      unset($this->m_modified[$field]) ;
    }

    //
    // The select function will not fill out an object with all it's related
    // tables.  That's a job for the specific application object.
    //

    function select($theSelector=null)
    {
      //
      // Note that this assumes that only one row is returned by the selector.
      //

      if (!$this->rawSelect($theSelector))
        {
	  return false ;
        }

      return $this->next() ;
    }

    function rawSelect($theSelector=null)
    {

      if ($theSelector === null)
	{
	  $theSelector = $this->needUpdateSelector() ;
	}

      $theSQL = "SELECT * FROM " . $this->m_tableName . " " . $theSelector . " ;" ;

      $theResult = $this->query($theSQL) ;

      if ($this->hasErrors())
        {
	  return false ;
        }

      return true ;
    }

    //
    // However, insert and update CAN call the update functions for all
    // sub objects, which they will do.
    //

    function insert()
    {
      //
      // The first thing to do is to walk the object's data and for
      // all enclosed objects, call their insert functions.  This will
      // work because there isn't any selector the call.  If I want
      // update to have this capability as well, I'll need to figure out
      // a way to get at a selector.
      //

      foreach ($this->m_sqlFields as $theField => $theValue)
	{
	  if (is_object($theValue))
	    {
	      if (!$theValue->insert())
		{
		  return false ;
		}
	    }
	  else if (is_array($theValue))
	    {
	      foreach ($theValue as $theArrayIndex => $theArrayValue)
                {
		  if (!$theArrayValue->insert())
                    {
		      return false ;
                    }
		}
	    }
        }

      //
      // Now update the contents of all the modified fields.
      //

      $theSQL = "insert into `" . $this->m_tableName . "` (" ;

      $insertRequired = false ;

      foreach ($this->m_modified as $field => $modified)
        {
	  if ($modified)
            {
	      $insertRequired = true ;

	      $theSQL .= "`" . $field . "`, " ;
            }
	}

      if ($insertRequired)
        {
	  $theSQL = substr($theSQL, 0, strlen($theSQL) - 2) . ") VALUES (" ;
        }
      else
        {
	  $theSQL .= ") VALUES (" ;
        }

      foreach ($this->m_modified as $field => $modified)
        {
	  if ($modified)
            {
	      if (isset($this->m_sqlFields[$field]))
                {
		  if ($this->m_sqlFields[$field] === null)
                    {
		      $theSQL .= "NULL, " ;
                    }
		  else
                    {
		      $theSQL .= "'" . $this->escape_string($this-> m_sqlFields[$field]) . "', " ;
                    }
                }
	      else
                {
		  $theSQL .= "NULL, " ;
                }
            }
        }

      if ($insertRequired)
        {
	  $theSQL = substr($theSQL, 0, strlen($theSQL) - 2) . ")" ;
        }
      else
        {
	  $theSQL .= ")" ;
        }

      $this->query($theSQL) ;

      if ($this->hasErrors())
	{
	  return false ;
	}
      else
	{
	  $this->forceModified(false) ;
	  return true ;
	}
    }

    function update($theSelector = null)
    {
      //
      // The first thing to do is to walk the object's data and for
      // all enclosed objects, call their update functions.
      // Each object is required to define a needUpdateSelector or
      // provide an explicit selector for each call to update and
      // define needUpdateSelector functions for any SQLData objects
      // contained in the one being written.
      //

      foreach ($this->m_sqlFields as $theField => $theValue)
	{
	  if (is_object($theValue))
	    {
	      if (!$theValue->update())
		{
		  return false ;
		}
	    }
	  else if (is_array($theValue))
	    {
	      foreach ($theValue as $theArrayIndex => $theArrayValue)
                {
		  if (!$theArrayValue->update())
                    {
		      return false ;
                    }
		}
	    }
        }

      if ($theSelector == null)
	{
	  $theSelector = $this->needUpdateSelector() ;
	}

      $theSQL = "update " . $this->m_tableName . " set " ;

      $updateRequired = false ;

      foreach ($this->m_modified as $field => $modified)
        {
	  if ($modified)
            {
	      $updateRequired = true ;

	      if (isset($this->m_sqlFields[$field]))
                {
		  if ($this->m_sqlFields[$field] === null)
                    {
		      $theSQL .= " `" . $field . "`=NULL, " ;
                    }
		  else
                    {
		      $theSQL .= " `" . $field . "`='" . $this->escape_string($this->m_sqlFields[$field]) . "', " ;
                    }
                }
	      else
                {
		  $theSQL .= " `" . $field . "`=NULL, " ;
                }
            }
        }

      if ($updateRequired)
        {
	  $theSQL = substr($theSQL, 0, strlen($theSQL) - 2) . " " . $theSelector . ";" ;

	  $this->query($theSQL) ;

	  if ($this->hasErrors())
	    {
	      return false ;
	    }
	  else
	    {
	      $this->forceModified(false) ;
	      return true ;
	    }
        }
      else
        {
	  return true ;
        }
    }


    //
    // However, insert, update and delete CAN call the update functions for all
    // sub objects, which they will do.
    //

    function delete($theSelector=null)
    {
      //
      // The first thing to do is to walk the object's data and for
      // all enclosed objects, call their delete functions.  This will
      // work because there isn't any selector the call.  If I want
      // update to have this capability as well, I'll need to figure out
      // a way to get at a selector.
      //

      foreach ($this->m_sqlFields as $theField => $theValue)
	{
	  if (is_object($theValue))
	    {
	      if (!$theValue->delete())
		{
		  return false ;
		}
	    }
	  else if (is_array($theValue))
	    {
	      foreach ($theValue as $theArrayIndex => $theArrayValue)
                {
		  if (!$theArrayValue->delete())
                    {
		      return false ;
                    }
		}
	    }
        }

      if ($theSelector == null)
	{
	  $theSelector = $this->needUpdateSelector() ;
	}

      $theSQL = "delete from `" . $this->m_tableName . "` " . $theSelector ;

      $this->query($theSQL) ;

      return !$this->hasErrors() ;
    }

    function needUpdateSelector()
    {
      $this->death("The subclass must provide selectors for all classes on request.") ;
    }
}
?>
Return current item: SQL Data