system/classes/databaseconnection.php

<?php
namespace Habari;

class DatabaseConnection
{
  private $fetch_mode = \PDO::FETCH_CLASS;          // PDO Fetch mode
  private $fetch_class_name = 'QueryRecord';       // The default class name for fetching classes
  private $driver;                                 // PDO driver name
  private $keep_profile = DEBUG;                   // keep profiling and timing information?
  protected $pdo = null;                           // handle to the PDO interface
  private $pdo_statement = null;                   // handle for a PDOStatement
  private $pdo_transaction = false;                // handle for transaction status

  private $tables = array(
    'blocks',
    'blocks_areas',
    'commentinfo',
    'comments',
    'commentstatus',
    'commenttype',
    'crontab',
    'groups',
    'group_token_permissions',
    'log',
    'log_types',
    'object_terms',
    'object_types',
    'options',
    'post_tokens',
    'postinfo',
    'posts',
    'poststatus',
    'posttype',
    'revisions',
    'rewrite_rules',
    'scopes',
    'sessions',
    'terms',
    'terminfo',
    'tokens',
    'userinfo',
    'users',
    'user_token_permissions',
    'users_groups',
    'vocabularies',
  );

  private $sql_tables = array();
  private $sql_tables_repl = array();
  private $errors = array();                       // an array of errors related to queries
  private $profiles = array();                     // an array of query profiles

  protected $prefix = '';                          // class protected storage of the database table prefix, defaults to ''
  private $current_table;

  public static function ConnectionFactory( $connect_string )
  {
    list( $engine ) = explode( ':', $connect_string, 2 );
    $engines = array(
      'sqlite' => '\Habari\SQLiteConnection',
      'mysql' => '\Habari\MySQLConnection',
      'pgsql' => '\Habari\PGSQLConnection',
    );

    require_once( HABARI_PATH . "/system/schema/{$engine}/connection.php" );
    $engine_class = $engines[$engine];
    return new $engine_class();
  }

  protected function load_tables()
  {
    if ( isset( Config::get( 'db_connection' )->prefix ) ) {
      $prefix = Config::get( 'db_connection' )->prefix;
    }
    else {
      $prefix = $this->prefix;
    }
    $this->prefix = $prefix;

    // build the mapping with prefixes
    foreach ( $this->tables as $t ) {
      $this->sql_tables[$t] = $prefix . $t;
      $this->sql_tables_repl[$t] = '{' . $t . '}';
    }
  }

  public function connect ( $connect_string, $db_user, $db_pass )
  {
    // Do not display errors so that they can be handled by the error method
    $this->pdo = @new \PDO( $connect_string, $db_user, $db_pass );
    $this->pdo->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_WARNING );
    $this->load_tables();
    return true;
  }

  public function disconnect()
  {
    $this->pdo = null; // this is the canonical way :o

    return true;
  }

  public function is_connected()
  {
    return ( null != $this->pdo );
  }

  public function in_transaction()
  {
    return $this->pdo_transaction;
  }

  public function table( $name )
  {
    if ( isset( $this->sql_tables[$name] ) ) {
      return $this->sql_tables[$name];
    }
    else {
      return $name;
    }
  }

  public function register_table( $name )
  {
    $this->tables[] = $name;
    $this->load_tables();
  }

  public function set_fetch_mode( $mode )
  {
    $this->fetch_mode = $mode;
  }

  public function set_fetch_class( $class_name )
  {
    $this->fetch_class_name = $class_name;
  }

  public function exec( $query )
  {
    // Allow plugins to modify the query
    $query = Plugins::filter( 'db_exec', $query, array() );
    // Translate the query for the database engine
    $query = $this->sql_t( $query, array() );
    // Replace braced table names in the query with their prefixed counterparts
    $query = self::filter_tables( $query );
    // Allow plugins to modify the query after it has been processed
    $query = Plugins::filter( 'db_exec_postprocess', $query, array() );

    return ( $this->pdo->exec( $query ) !== false );
  }

  public function query( $query, $args = array() )
  {
    if ( $this->pdo_statement != null ) {
      $this->pdo_statement->closeCursor();
    }

    // Allow plugins to modify the query
    $query = Plugins::filter( 'query', $query, $args );
    // Translate the query for the database engine
    $query = $this->sql_t( $query, $args );
    // Replace braced table names in the query with their prefixed counterparts
    $query = self::filter_tables( $query );
    // Allow plugins to modify the query after it has been processed
    $query = Plugins::filter( 'query_postprocess', $query, $args );

    if ( $this->pdo_statement = $this->pdo->prepare( $query ) ) {
      if ( $this->fetch_mode == \PDO::FETCH_CLASS ) {
        /* Try to get the result class autoloaded. */
        if ( ! class_exists( $this->fetch_class_name, true ) ) {
          $class_name = $this->fetch_class_name;
          // @todo This is a GIANT namespace kludge, replacing Model class names with no namespace that don't exist with a default prefixed class
          if(strpos($class_name, '\\') == false) {
            $class_name = '\\Habari\\' . $class_name;
            $this->fetch_class_name = $class_name;
            if ( ! class_exists( $this->fetch_class_name, true ) ) {
              throw new \Exception('The model class that was specified for data retreival could not be loaded.');
            }
          }
        }
        /* Ensure that the class is actually available now, otherwise segfault happens (if we haven't died earlier anyway). */
        if ( class_exists( $this->fetch_class_name ) ) {
          $this->pdo_statement->setFetchMode( \PDO::FETCH_CLASS, $this->fetch_class_name, array() );
        }
        else {
          /* Die gracefully before the segfault occurs */
          echo '<br><br>' . _t( 'Attempt to fetch in class mode with a non-included class' ) . '<br><br>';
          return false;
        }
      }
      else {
        $this->pdo_statement->setFetchMode( $this->fetch_mode );
      }

      /* If we are profiling, then time the query */
      if ( $this->keep_profile ) {
        $profile = new QueryProfile( $query );
        $profile->params = $args;
        $profile->start();
      }
      if ( ! $this->pdo_statement->execute( $args ) ) {
        $this->add_error( array( 'query'=>$query,'error'=>$this->pdo_statement->errorInfo() ) );
        return false;
      }
      if ( $this->keep_profile ) {
        $profile->stop();
        $this->profiles[] = $profile;
      }
      return true;
    }
    else {
      $this->add_error( array(
        'query' => $query,
        'error' => $this->pdo->errorInfo(),
      ) );
      return false;
    }
  }

  public function execute_procedure( $procedure, $args = array() )
  {
    /* Local scope caching */
    $pdo = $this->pdo;
    $pdo_statement = $this->pdo_statement;

    if ( $pdo_statement != null ) {
      $pdo_statement->closeCursor();
    }

    $query = 'CALL ' . $procedure . '( ';
    if ( count( $args ) > 0 ) {
      $query .= str_repeat( '?,', count( $args ) ); // Add the placeholders
      $query = substr( $query, 0, strlen( $query ) - 1 ); // Strip the last comma
    }
    $query .= ' )';
    $query = $this->sql_t( $query, $args );

    if ( $pdo_statement = $pdo->prepare( $query ) ) {
      /* If we are profiling, then time the query */
      if ( $this->keep_profile ) {
        $profile = new QueryProfile( $query );
        $profile->start();
      }
      if ( ! $pdo_statement->execute( $args ) ) {
        $this->add_error( array( 'query'=>$query,'error'=>$pdo_statement->errorInfo() ) );
        return false;
      }
      if ( $this->keep_profile ) {
        $profile->stop();
        $this->profiles[] = $profile;
      }
      return true;
    }
    else {
      $this->add_error( array( 'query'=>$query,'error'=>$pdo_statement->errorInfo() ) );
      return false;
    }
  }

  public function begin_transaction()
  {
    if ( ! $this->pdo_transaction ) {
      if( $this->pdo->beginTransaction() ) {
        $this->pdo_transaction = true;
      }
    }
  }

  public function rollback()
  {
    if ( $this->pdo_transaction ) {
      if( $this->pdo->rollBack() ) {
        $this->pdo_transaction = false;
      }
    }
  }

  public function commit()
  {
    if ( $this->pdo_transaction ) {
      if( $this->pdo->commit() ) {
        $this->pdo_transaction = false;
      }
    }
  }

  public function get_profiles()
  {
    return $this->profiles;
  }

  public function add_error( $error )
  {
    $backtrace1 = debug_backtrace();
    $backtrace = array();
    foreach ( $backtrace1 as $trace ) {
      $backtrace[] = array_intersect_key( $trace, array('file'=>1, 'line'=>1, 'function'=>1, 'class'=>1) );
    }
    $this->errors[] = array_merge( $error, array( 'backtrace'=> $backtrace ) );
  }

  public function get_errors()
  {
    return $this->errors;
  }

  public function has_errors()
  {
    return ( count( $this->errors ) > 0 );
  }

  public function clear_errors()
  {
    $this->errors = array();
  }

  public function get_last_error()
  {
    $error = end( $this->errors );
    return ( array( 'query'=>$error['query'], 'message'=>$error['error'][2] ) );
  }

  public function get_results( $query, $args = array(), $class_name = '\Habari\QueryRecord' )
  {
    $this->set_fetch_mode( \PDO::FETCH_CLASS );
    $this->set_fetch_class( $class_name );
    if ( $this->query( $query, $args ) ) {
      return $this->pdo_statement->fetchAll();
    }
    else {
      return false;
    }
  }

  public function get_row( $query, $args = array(), $class_name = '\Habari\QueryRecord' )
  {
    $this->set_fetch_mode( \PDO::FETCH_CLASS );
    $this->set_fetch_class( $class_name );

    if ( $this->query( $query, $args ) ) {
      return $this->pdo_statement->fetch();
    }
    else {
      return false;
    }
  }

  public function get_column( $query, $args = array() )
  {
    if ( $this->query( $query, $args ) ) {
      return $this->pdo_statement->fetchAll( \PDO::FETCH_COLUMN );
    }
    else {
      return false;
    }
  }

  public function get_value( $query, $args = array() )
  {
    if ( $this->query( $query, $args ) ) {
      $result = $this->pdo_statement->fetch( \PDO::FETCH_NUM );
      return $result[0];
    }
    else {
      return false;
    }
  }

  public function get_keyvalue( $query, $args = array() )
  {
    if ( $this->query( $query, $args ) ) {
      $output = $this->pdo_statement->fetchAll( \PDO::FETCH_KEY_PAIR );
      return $output;
    }
    else {
      return false;
    }
  }

  public function insert( $table, $fieldvalues )
  {
    ksort( $fieldvalues );

    $fields = array_keys( $fieldvalues );
    $values = array_values( $fieldvalues );
    
    $query = "INSERT INTO {$table} ( " . implode( ', ', $fields ) . ' ) VALUES ( ' . implode( ', ', array_fill( 0, count( $values ), '?' ) ) . ' )';

    // need to pass $table on to the $o singleton object;
    $this->current_table = $table;

    return $this->query( $query, $values );
  }

  public function exists( $table, $keyfieldvalues )
  {
    $qry = "SELECT 1 as c FROM {$table} WHERE 1=1 ";

    $values = array();
    foreach ( $keyfieldvalues as $keyfield => $keyvalue ) {
      $qry .= " AND {$keyfield} = ? ";
      $values[] = $keyvalue;
    }
    $result = $this->get_row( $qry, $values );
    return ( $result !== false );
  }

  public function update( $table, $fieldvalues, $keyfields )
  {
    ksort( $fieldvalues );
    ksort( $keyfields );

    $keyfieldvalues = array();
    foreach ( $keyfields as $keyfield => $keyvalue ) {
      if ( is_numeric( $keyfield ) ) {
        // if the key is numeric, assume we were handed a simple list of fields that are keys and fetch its value from $fieldvalues
        $keyfieldvalues[$keyvalue] = $fieldvalues[$keyvalue];
      }
      else {
        // otherwise we were handed a key => value pair, use it as-is
        $keyfieldvalues[$keyfield] = $keyvalue;
      }
    }
    if ( $this->exists( $table, $keyfieldvalues ) ) {
      $qry = "UPDATE {$table} SET";
      $values = array();
      $comma = '';
      foreach ( $fieldvalues as $fieldname => $fieldvalue ) {
        $qry .= $comma . " {$fieldname} = ?";
        $values[] = $fieldvalue;
        $comma = ' ,';
      }
      $qry .= ' WHERE 1=1 ';

      foreach ( $keyfieldvalues as $keyfield => $keyvalue ) {
        $qry .= "AND {$keyfield} = ? ";
        $values[] = $keyvalue;
      }
      return $this->query( $qry, $values );
    }
    else {
      // We want the keyfieldvalues to be included in
      // the insert, with fieldvalues taking precedence.
      $fieldvalues = $fieldvalues + $keyfieldvalues;
      return $this->insert( $table, $fieldvalues );
    }
  }

  public function delete( $table, $keyfields )
  {
    $qry = "DELETE FROM {$table} WHERE 1=1 ";
    foreach ( $keyfields as $keyfield => $keyvalue ) {
      $qry .= "AND {$keyfield} = ? ";
      $values[] = $keyvalue;
    }

    return $this->query( $qry, $values );
  }

  public function last_insert_id()
  {
    if ( $this->pdo->getAttribute( \PDO::ATTR_DRIVER_NAME ) == 'pgsql' ) {
      return $this->pdo->lastInsertId( $this->current_table. '_pkey_seq' );
    }
    else {
      return $this->pdo->lastInsertId( func_num_args() == 1 ? func_get_arg( 0 ) : '' );
    }
  }

  public function dbdelta( $queries, $execute = true, $silent = true ){}

  public function upgrade_pre( $old_version ){}

  public function upgrade_post( $old_version ){}


  public function upgrade( $old_version, $upgrade_path = '' )
  {
    // Get all the upgrade files
    $upgrade_files = Utils::glob( "{$upgrade_path}/*.sql" );

    // Put the upgrade files into an array using the 0-padded revision + '_0' as the key
    $upgrades = array();
    foreach ( $upgrade_files as $file ) {
      if ( intval( basename( $file, '.sql' ) ) > $old_version ) {
        $upgrades[ sprintf( '%010s_0', basename( $file, '.sql' ) )] = $file;
      }
    }
    // Put the upgrade functions into an array using the 0-padded revision + '_1' as the key
    $upgrade_functions = get_class_methods( $this );
    foreach ( $upgrade_functions as $fn ) {
      if ( preg_match( '%^upgrade_([0-9]+)$%i', $fn, $matches ) ) {
        if ( intval( $matches[1] ) > $old_version ) {
          $upgrades[ sprintf( '%010s_1', $matches[1] )] = array( $this, $fn );
        }
      }
    }

    // Sort the upgrades by revision, ascending
    ksort( $upgrades );

    // Execute all of the upgrade functions
    $result = true;
    foreach ( $upgrades as $upgrade ) {
      if ( is_array( $upgrade ) ) {
        $result &= $upgrade();
      }
      else {
        $result &= $this->query_file( $upgrade );
      }
      if ( !$result ) {
        break;
      }
    }

    return $result;
  }

  public function query_file( $file )
  {
    $upgrade_sql = trim( file_get_contents( $file ) );
    $upgrade_sql = str_replace( '{$prefix}', $this->prefix, $upgrade_sql );

    // Split up the queries
    $queries = explode( ';', $upgrade_sql );

    foreach ( $queries as $query ) {
      if ( trim( $query ) != '' ) {
        if ( !$this->query( $query ) ) {
          return false;
        }
      }
    }

    return true;
  }


  public function sql_t( $query )
  {
    return $query;
  }

  public function filter_tables( $query )
  {
    return str_replace( $this->sql_tables_repl, $this->sql_tables, $query );
  }

  public function get_driver_name()
  {
    if ( ! $this->driver ) {
      $this->driver = $this->pdo->getAttribute( \PDO::ATTR_DRIVER_NAME );
    }
    return $this->driver;
  }

  public function get_driver_version()
  {
    return $this->pdo->getAttribute( \PDO::ATTR_SERVER_VERSION );
  }

  public function row_count()
  {
    return $this->pdo_statement->rowCount();
  }

  public function list_tables()
  {
    return $this->sql_tables;
  }

  public function quote ( $string )
  {
    return $this->pdo->quote( $string );
  }

}

?>