Location: PHPKode > projects > Sierra-php PHP Application Framework > sierra/lib/sql/SRA_DatabaseSqLite.php
<?php
// {{{ Header
/*
 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
 | SIERRA : PHP Application Framework  http://code.google.com/p/sierra-php |
 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
 | Copyright 2005 Jason Read                                               |
 |                                                                         |
 | Licensed under the Apache License, Version 2.0 (the "License");         |
 | you may not use this file except in compliance with the License.        |
 | You may obtain a copy of the License at                                 |
 |                                                                         |
 |     http://www.apache.org/licenses/LICENSE-2.0                          |
 |                                                                         |
 | Unless required by applicable law or agreed to in writing, software     |
 | distributed under the License is distributed on an "AS IS" BASIS,       |
 | WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.|
 | See the License for the specific language governing permissions and     |
 | limitations under the License.                                          |
 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
 */
// }}}

// {{{ SRA_DatabaseSqLite
/**
 * database class for interracting with an sqlite database. NOTE: this database 
 * class works ONLY with PHP PDO currently
 * @author Jason Read <hide@address.com>
 * @package sierra.sql
 */
class SRA_DatabaseSqLite extends SRA_Database {
  
  // {{{ SRA_DatabaseSqLite
  /**
   * empty constructor to keep PHP from calling parent constructor
   * @access  public
   */
  function SRA_DatabaseSqLite() { }
  // }}}

  // {{{ _closeConn()
  /**
   * closes a sqlite database connection
   * @param PDO $conn the connection to close
   * @access private
   * @return void
   */
  function _closeConn($conn) {
    $conn = NULL;
  }
  // }}}

  // {{{ commit
  /**
   * commits a database transaction. returns TRUE on success, FALSE otherwise
   * @param PDO $conn the database instance to commit
   * @access public
   * @return boolean
   */
  function commit($conn=null) {
    if ($conn === NULL) {
      $result = $this->processCommit();
      if (SRA_Error::isError($result)) {
        $msg = 'SRA_DatabaseSqLite::commit - Failed due to error from SRA_Database::processCommit';
        return SRA_Error::logError($msg, __FILE__, __LINE__);
      }
    }
    else {
      if ($conn->_sraInTransaction && $conn->commit()) {
        $conn->_sraInTransaction = FALSE;
        return TRUE;
      }
      else {
        return FALSE;
      }
    }
  }
  // }}}

  // {{{ convertBlob
  /**
   * this method implements the parent convertBlob method. see SRA_Database 
   * class api for info
   * @param mixed $blob the value to convert
   * @access public
   * @return string
   */
  function &convertBlob(&$blob) {
    if (!isset($blob)) { return 'NULL'; }
    
    $conn = $this->_getAppDbConnection($query);
    
    if (!$conn || SRA_Error::isError($conn)) {
      $msg = "SRA_DatabaseSqLite::convertText - failed to _getAppDbConnection";
      return SRA_Error::logError($msg, __FILE__, __LINE__);
    }
    
    return $conn->quote($blob);
  }
  // }}}
    
  // {{{ convertDate
  /**
   * Convert a dateOnly SRA_GregorianDate object into a database readable 
   * string. This format is required for insertion into the database. this is an 
   * abstract method that must be implemented by the child SRA_Database* 
   * classes. This method returns an SRA_Error object if the $dateTime parameter 
   * is not a valid SRA_GregorianDate object
   * @param SRA_GregorianDate $dateTime the date to convert
   * @access public
   * @return string
   */
  function convertDate($dateTime) {
    if (!isset($dateTime)) { return 'NULL'; }
    if (!SRA_GregorianDate::isValid($dateTime)) {
      $msg = 'SRA_DatabaseSqLite::convertDate: Passed parameter is not a valid SRA_GregorianDate object';
      return(SRA_Error::logError($msg, __FILE__, __LINE__));
    }
    return "'" . $dateTime->format($dateTime->isDateOnly() ? 'Ymd' : 'YmdHis') . "'";
  }
  // }}}
  
  // {{{ convertTime
  /**
   * Convert a date/time SRA_GregorianDate object into a database readable 
   * string using the db timezone. This format is required for insertion into 
   * the database. this is an abstract method that must be implemented by the 
   * child SRA_Database* classes. This method returns an SRA_Error object if the
   * $dateTime parameter is not a valid SRA_GregorianDate object
   * @param SRA_GregorianDate $dateTime the time to convert
   * @access public
   * @return string
   */
  function convertTime($dateTime) {
    if (SRA_GregorianDate::isValid($dateTime)) {
      $tz =& $dateTime->getTimeZone();
      $dateTime->setTimeZone($this->_timeZone);
    }
    $time = $this->convertDate($dateTime);
    if (SRA_GregorianDate::isValid($dateTime)) {
      $dateTime->setTimeZone($tz);
    }
    return $time;
  }
  // }}}
  
  // {{{ convertFloat
  /**
   * this method implements the parent convertFloat method. see SRA_Database
   * class api for info
   * @param float $value the value to convert
   * @access public
   * @return float
   */
  function convertFloat($value) {
    if (!isset($value)) { return 'NULL'; }
    return $value;
  }
  // }}}
  
  // {{{ convertText
  /**
   * this method implements the parent convertText method. see SRA_Database
   * class api for info
   * @param string $text the text to convert
   * @access public
   * @return string
   */
  function convertText($text) {
    return $this->convertBlob($text);
  }
  // }}}
  
  // {{{ convertString
  /**
   * alias for convertText
   * @param string $str the string to convert
   * @access public
   * @return string
   */
  function convertString($str) {
    return $this->convertText($str);
  }
  // }}}
  
  // {{{ execute
  /**
   * this method implements the parent execute method. see SRA_Database class
   * api for info
   * @param string $query the query to execute. see SRA_Database::execute
   * method api for more info
   * @param boolean $incCol the name of the db managed incremental column to 
   * retrieve and store in the SRA_ExecuteSet return object the incremental 
   * value that was used (applied only to INSERT queries performed on tables 
   * that contain db managed incremental columns). see SRA_Database::execute 
   * method api for more info.
   * @param int $errorLevel the query error logging level. see
   * SRA_Database::execute method api for more info
   * @param PDO $conn the database connection object to perform the query on. if 
   * null (default) this method will simply return SRA_Database::processUpdate. 
   * otherwise it will attempt to perform the query using the specified 
   * connection
   * @access public
   * @return SRA_ExecuteSet
   */
  function &execute($query, $incCol=FALSE, $errorLevel=SRA_ERROR_PROBLEM, $conn=NULL) {
    if ($conn === NULL) {
      $xs =& $this->processUpdate($query, $incCol, $errorLevel);
      if (SRA_Error::isError($xs)) {
        $msg = 'SRA_DatabaseSqLite::execute - Failed because error returned from SRA_Database::processUpdate';
        return(SRA_Error::logError($msg, __FILE__, __LINE__, $errorLevel));
      }
      return $xs;
    }
    else {
      $count = $conn->exec($query);

      $xs = new SRA_ExecuteSet($count);

      if ($incCol) { $xs->setSequenceValue($conn->lastInsertId()); }
      
      return($xs);
    }
  }
  // }}}

  // {{{ fetch
  /**
   * this method implements the parent fetch method. See SRA_Database class api 
   * for info
   * @param string $query the query to fetch (see SRA_Database::fetch method api 
   * for more info)
   * @param array types the query return types. (see SRA_Database::fetch method 
   * api for more info)
   * @param int $limit (see SRA_Database::fetch method api for more info)
   * @param int $offset (see SRA_Database::fetch method api for more info)
   * @param int $errorLevel the error level for the query. (See 
   * SRA_Database::fetch method api for more info)
   * @access public
   * @return SRA_ResultSet
   */
  function &fetch($query, $types=NULL, $limit=NULL, $offset=NULL, $errorLevel=SRA_ERROR_PROBLEM) {
    // Check for cache
    if ($results =& $this->_fetchInCache($query)) { return $results; }
    
    $conn = $this->_getAppDbConnection($query);
    
    if (!$conn || SRA_Error::isError($conn)) {
      $msg = "SRA_DatabaseSqLite::fetch - failed to _getAppDbConnection";
      return SRA_Error::logError($msg, __FILE__, __LINE__);
    }
    
    // add limit and offset
    $baseQuery = $query;
    if ($limit && $offset) {
      $query .= " LIMIT $offset, $limit";
    }
    else if ($limit) {
      $query .= " LIMIT $limit";
    }
    else if ($offset) {
      $query .= " LIMIT $offset, 1000000000";
    }
    $query = SRA_Database::applyLimitAndOffset($query, $limit, $offset);
    
    $result = $conn->query($query);
    
    if (!$result) {
      $msg = 'SRA_DatabaseSqLite::fetch: Query "' . $query . '" failed with errors: ' . implode(', ', $conn->errorInfo());
      return(SRA_Error::logError($msg, __FILE__, __LINE__, $errorLevel));
    }
    
    if ($types) {
      foreach(array_keys($types) as $key) { $types[$key] = strtolower($types[$key]); }
    }
    
    $rs = new SRA_ResultSet($this, $query, $baseQuery, $limit, $offset);
    
    while ($row =& $result->fetch(PDO::FETCH_NUM)) {
      $cols = $cols ? $cols : count($row);
      for ($j = 0; $j < $cols; ++$j) {
        $type = $types && isset($types[$j]) ? $types[$j] : SRA_DATA_TYPE_STRING;
        switch ($type) {
          case SRA_DATA_TYPE_BOOLEAN :
            $row[$j] = $this->unconvertBoolean($row[$j]);
            break;
          
          case SRA_DATA_TYPE_TIME :
          case SRA_DATA_TYPE_DATE :
            if ($row[$j]) { $row[$j] = new SRA_GregorianDate($row[$j], $this->_timeZone); }
            if ($type == SRA_DATA_TYPE_DATE && SRA_GregorianDate::isValid($row[$j])) { $row[$j]->setDateOnly(TRUE); }
            if ($type == SRA_DATA_TYPE_TIME && SRA_GregorianDate::isValid($row[$j])) $row[$j]->setTimeZone(SRA_Controller::getAppTimeZone());
            break;
          case SRA_DATA_TYPE_FLOAT :
          case SRA_DATA_TYPE_INT :
            $row[$j] = $row[$j]*1;
            break;
          case SRA_DATA_TYPE_STRING :
            $row[$j] = $row[$j] . '';
            break;
        }
      }
      $rs->add($row);
    }

    // add to cache
    $this->_addFetchToCache($query, $rs);
    
    // Return.
    return $rs;
  }
  // }}}

  // {{{ getNextSequence
  /**
   * this method implements the parent getNextSequence method. see 
   * SRA_Database class api for info
   * @param string $sequence the name of the sequence to return the next value 
   * for
   * @param	int $errorLevel the query error logging level. see 
   * SRA_Database::getNextSequence method api for more info.
   * @param	PDO $conn the database connection object to perform the sequence 
   * query on. if null (default) this method will simply return 
   * SRA_Database::processNextSequence. otherwise it will attempt to perform 
   * the query using the specified connection. see 
   * SRA_Database::getNextSequence api for more info.
   * @access public
   * @return int
   */
  function getNextSequence($sequence, $errorLevel = SRA_ERROR_PROBLEM, $conn = NULL) {
    if ($conn === NULL) {
      return $this->processNextSequence($sequence, $errorLevel);
    }
    else if ($result = $conn->query("INSERT INTO $sequence VALUES (0)")) {
      return $conn->lastInsertId();
    }
    else {
      $msg = "SRA_DatabaseSqLite::getNextSequence: Failed for sequence '$sequency' due to the following errors: " . implode(', ', $conn->errorInfo());
      return SRA_Error::logError($msg, __FILE__, __LINE__, $errorLevel);
    }
  }
  // }}}

  // {{{ _openConn
  /**
   * this method is used to open one connection to a database server. it is 
   * called by the SRA_Database parent class in order to open needed database 
   * connections. It returns either a connection object (if successful) or an 
   * SRA_Error object if not
   * @param array $config the setup to use for the connection (see 
   * SRA_Database::_openConn api for more info). for sqlite databases, only the 
   * database name is used which should be the path to the database file
   * @return PDO
   */
  function _openConn($config) {
    if (isset($config['name']) && is_dir(dirname($config['name'])) && is_writable(dirname($config['name'])) && (!file_exists($config['name']) || is_writable($config['name']))) {
      return new PDO('sqlite:' . $config['name']);
    }
    else {
      $msg = 'SRA_DatabaseSqLite::_openConn: Failed - invalid database: ' . $config['name'];
      return SRA_Error::logError($msg, __FILE__, __LINE__, $errorLevel);
    }
  }
  // }}}

  // {{{ rollback
  /**
   * this method implements the parent rollback method. see SRA_Database class 
   * api for info
   * @param PDO $conn the database connection object to rollback a transaction 
   * for. if null (default) this method will simply return 
   * SRA_Database::processRollback. otherwise it will attempt to rollback the
   * transaction using the specified connection
   * @access public
   * @return boolean
   */
  function rollback($conn=null) {
    if ($conn === NULL) {
      $result = $this->processRollback();
      if (SRA_Error::isError($result)) {
        $msg = 'SRA_DatabaseSqLite::rollback - Failed due to error from SRA_Database::processRollback';
        return SRA_Error::logError($msg, __FILE__, __LINE__);
      }
    }
    else {
      if ($conn->_sraInTransaction && $conn->rollback()) {
        $conn->_sraInTransaction = FALSE;
        return TRUE;
      }
      else {
        return FALSE;
      }
    }
  }
  // }}}

  // {{{ startTransaction
  /**
   * this method implements the SRA_Database::startTransaction method. see this 
   * method's api for more info
   * @param PDO $conn the database connection to start the transaction for. if 
   * null (default) this method will simply return 
   * SRA_Database::startTransactions.
   * @access public
   * @return boolean
   */
  function startTransaction($conn=null) {
    if ($conn === NULL) {
      $result = $this->startTransactions();
      if (SRA_Error::isError($result)) {
        $msg = 'SRA_DatabaseSqLite::startTransaction - Failed due to error from SRA_Database::startTransactions';
        return SRA_Error::logError($msg, __FILE__, __LINE__);
      }
    }
    else {
      if ($conn->beginTransaction()) {
        $conn->_sraInTransaction = TRUE;
        return TRUE;
      }
      else {
        $conn->_sraInTransaction = FALSE;
        return FALSE;
      }
    }
  }
  // }}}
		
		
	// {{{ getColumnDefinition()
	/**
	 * provides an sql statement defining the data type, constraints, and 
	 * referential integrity (optional) for a given SRA_SchemaColumn. this method 
	 * may be implemented by each of the underlying database types if the 
	 * default MYSQL implementation does not suffice
	 * @param SRA_SchemaTable $table the SRA_SchemaTable that the column belongs 
   * to
	 * @param SRA_SchemaColumn $column the SRA_SchemaColumn to create the 
   * definition for
	 * @param boolean $dbRefIntegrity whether or not referential integrity should 
	 * be enforced at the database layer (not supported in SQLite)
	 * @param boolean $addCheckConstraint whether or not to add a check constraint 
	 * to the column definition (not supported in SQLite)
	 * @access public
	 * @return string
	 */
	function getColumnDefinition(& $table, & $column, $dbRefIntegrity = TRUE, $addCheckConstraint = FALSE) {
		$definition = parent::getColumnDefinition($table, $column, $dbRefIntegrity, $addCheckConstraint);
		
		// sequence types
		if ($column->isSequence()) {
      $definition = str_replace('PRIMARY KEY', '', $definition);
			$definition = str_replace('AUTO_INCREMENT', 'PRIMARY KEY AUTOINCREMENT', $definition);
		}
    // boolean
    if ($column->getType() == SRA_DATA_TYPE_BOOLEAN) {
      $definition = str_replace('ENUM(' . $this->convertBoolean(TRUE) . ',' . $this->convertBoolean(FALSE) . ')', 'INTEGER', $definition);
    }
    // date/time
    if ($column->getType() == SRA_DATA_TYPE_DATE || $column->getType() == SRA_DATA_TYPE_TIME) {
      $definition = str_replace('DEFAULT 0', 'DEFAULT NULL', $definition);
      $definition = str_replace('TIMESTAMP', 'INTEGER', $definition);
      $definition = str_replace('DATE', 'INTEGER', $definition);
    }
    // float
    if ($column->getType() == SRA_DATA_TYPE_FLOAT) {
      $definition = str_replace('DOUBLE', 'REAL', $definition);
    }
    // integer
    if ($column->getType() == SRA_DATA_TYPE_INT) {
      $definition = str_replace('INT', 'INTEGER', $definition);
    }
    // string
    if ($column->getType() == SRA_DATA_TYPE_STRING) {
      $definition = str_replace('VARCHAR(' . $column->getVars('maxLength') . ')', 'TEXT', $definition);
    }
		return $definition;
	}
	// }}}
  
  
  // {{{ getTableDefinition
  /**
   * provides an sql statement defining the constraints for a given 
   * SRA_SchemaTable. this method may be implemented by each of the underlying 
   * database types if the default implementation does not suffice
   *
   * @param SRA_SchemaTable $table the SRA_SchemaTable to create the definition for
   * @param boolean $dbRefIntegrity whether or not referential integrity should 
   * be enforced at the database layer
   * @access  public
   * @return	string
   */
  function getTableDefinition(& $table, $dbRefIntegrity = TRUE) {
    $pkname = $table->getPrimaryKey();
    if ($pkname) $pk =& $table->getColumns(is_array($pkname) ? $pkname[0] : $pkname);
    return $pk && $pk->isSequence() ? '' : parent::getTableDefinition($table, $dbRefIntegrity);
  }
  // }}}
  
  
  // {{{ isValid
  /**
   * Static method that returns TRUE if the object parameter references a
   * valid SRA_DatabaseSqLite object
   * @param  object $object the object to validate.
   * @access public
   * @return boolean
   */
  function isValid($object) {
    return is_object($object) && (!isset($object->err) || !SRA_Error::isError($object->err)) && 
           (strtolower(get_class($object)) == 'sra_databasesqlite' || is_subclass_of($object, 'SRA_DatabaseSqLite'));
  }
  // }}}

}
// }}}

?>
Return current item: Sierra-php PHP Application Framework