Storage.php

<?php

namespace Khansia\Db;

use Khansia\Db\Storage\Helper;

class Storage {

    const DRIVER_MYSQL = 0;
    const DRIVER_OCI8 = 1;

    protected $_db;
    protected $_sql;
    protected $_driver;
    protected $_helper;
    protected $_tables = array();

    protected $_now;

    protected function _($string, $prefix = '') {

        if ($this->_driver == self::DRIVER_OCI8) {
            return $prefix . strtoupper($string);
        }
        return $prefix . $string;
    }

    protected function __($list) {

        if ($this->_driver == self::DRIVER_OCI8) {
            return array_change_key_case($list, CASE_UPPER);
        }
        return $list;
    }

    protected function ___($list) {

        if ($this->_driver == self::DRIVER_OCI8) {
            foreach ($list as $key => $item) {
                $list[$key] = strtoupper($item);
            }
        }
        return $list;
    }

    public function __construct(\Zend\Db\Adapter\Adapter $adapter, $config = array()) {

        $this->_db = $adapter;
        $this->_sql = new \Zend\Db\Sql\Sql($this->_db);

        /* set default global table name */
        $this->_tables = array_merge($this->_tables, array(
          'config' => 'config', /* global lib config */
        ));

        /* get tables from config */
        if (isset($config['tables']) && is_array($config['tables'])) {
            $tables = $config['tables'];
            foreach ($tables as $key => $value) {
                if ($value) {
                    $this->_tables[$key] = $this->_($value);
                }
            }
        }

        if ($this->_db->getDriver() instanceof \Zend\Db\Adapter\Driver\Oci8\Oci8) {
            $this->_driver = self::DRIVER_OCI8;
            $this->_now = new \Zend\Db\Sql\Expression("SYSDATE");
        } else {
            $this->_driver = self::DRIVER_MYSQL;
            $this->_now = new \Zend\Db\Sql\Expression("now()");
        }

        $this->_helper = new Helper($this);
    }

    public function getAdapter() {

        return $this->_db;
    }

    public function getDriver() {

        return $this->_driver;
    }

    public function getHelper() {

        return $this->_helper;
    }

    public function getTables() {

        return $this->_tables;
    }

    public function select() {

        return $this->_sql->select();
    }

    public function insert() {

        return new Sql\Insert();
        return $this->_sql->insert();
    }

    public function update() {

        return $this->_sql->update();
    }

    public function delete() {

        return $this->_sql->delete();
    }

    public function store() {

        return new Sql\Store($this->_db);
    }

    public function recycle() {

        return new Sql\Recycle($this->_db);
    }

    public function fetchAll(\Zend\Db\Sql\Select $select, $raw = true) {

        $statement = $this->_sql->prepareStatementForSqlObject($select);
        //var_dump($statement); echo '<hr>';

        if ($result = $statement->execute()) {

            if (($this->_driver != self::DRIVER_OCI8) && (count($result) == 0)) { return false; }

            if ((!$raw) && ($result instanceof ResultInterface)) {
                $resultset = new \Zend\Db\ResultSet\ResultSet();
                $resultset->initialize($result);
                return $resultset;
            }

            return $result;
        }

        return false;
    }

    public function fetchRow(\Zend\Db\Sql\Select $select) {

        if ($this->_driver != self::DRIVER_OCI8) {
            $select->limit(1);
        }

        if ($result = $this->fetchAll($select)) {
            foreach ($result as $row) {
                return $row;
            }
        }
        return null;
    }

    public function fetchValue(\Zend\Db\Sql\Select $select, $field = null) {

        if ($row = $this->fetchRow($select)) {
            if ($field) {
                if (array_key_exists($field, $row)) {
                    return $row[$field];
                }
            } else {
                foreach ($row as $data) {
                    return $data;
                }
            }
        }
        return null;
    }

    public function fetchConfig($code) {

        $select = $this->select()
                  ->from($this->_tables['config'])
                  ->where($this->__(array('config_code' => $code)));
        if ($row = $this->fetchRow($select)) {
            if ($data = @json_decode(stripslashes($row[$this->_('config_data')]), true)) {
                return new \Zend\Config\Config($data);
            }
        }
        return  new \Zend\Config\Config(array());
    }

    public function execute(\Zend\Db\Sql\SqlInterface $query) {

        /* recycle? */
        if ($query instanceof Sql\Recycle) {

          /* get recycle target data */
          if ($target = $query->getSqlString()) {

            /* init */
            $count = $target->set->count();
            $saved = 0;

            /* table, primary, flag field exists? */
            if (($target->table) && ($target->primary) && ($target->flag)) {

              /* any data to save? */
              if ($count > 0) {

                /* select existing */
                $select = $this->select()
                          ->from($target->table, array($target->primary, $target->flag))
                          ->where($target->where);
                if ($existing = $this->fetchAll($select)) {

                  /* loop existing and update */
                  foreach ($existing as $row) {

                    /* all new record saved? */
                    if ($saved >= $count) {

                      /* flag remaining records to unused */
                      $update = $this->update()
                                ->table($target->table)
                                ->set(array($target->flag => $query::FLAG_UNUSED))
                                ->where(array($target->primary => $row[$target->primary]));
                      $this->execute($update);

                    } else {

                      /* overwrite existing record with new data */
                      $update = $this->update()
                                ->table($target->table)
                                ->set(array_merge(
                                  $target->set->item($saved)->pull(array_merge(
                                    array(
                                      $target->primary,
                                      $target->flag,
                                    ),
                                    $target->exclude
                                  )),
                                  array(
                                    $target->flag => $query::FLAG_USED,
                                  )
                                ))
                                ->where(array($target->primary => $row[$target->primary]));
                      if ($this->execute($update)) {

                        /* assign new id to updated item */
                        $target->set->item($saved)->push(array(
                          $target->primary => $row[$target->primary],
                        ));

                      }

                      /* increment saved */
                      $saved++;

                    } //all saved

                  } //each existing

                } //fetch

                /* any remaining unsaved records? */
                if ($saved < $count) {
                  for ($index = $saved; $index < $count; $index++) {

                    /* insert remaining record */
                    $insert = $this->insert()
                              ->into($target->table)
                              ->values(array_merge(
                                $target->set->item($index)->pull(array_merge(
                                  array(
                                    $target->primary,
                                    $target->flag,
                                  ),
                                  $target->exclude
                                )),
                                array(
                                  $target->flag => $query::FLAG_USED,
                                )
                              ));
                    if ($id = $this->execute($insert)) {

                        /* assign new id to set item */
                        $target->set->item($index)->push(array(
                          $target->primary => $id,
                        ));

                    }

                  } //each remaining record
                } //remaining?

                /* return! */
                return ($saved > 0);

              } else {

                /* flag related existing record to unused */
                $update = $this->update()
                          ->table($target->table)
                          ->set(array($target->flag => $query::FLAG_UNUSED))
                          ->where($target->where);
                return $this->execute($update);

              } //any data to save

            } //target primary check

          } //get data

          /* default return false */
          return false;

        /* store statement */
        } elseif ($query instanceof Sql\Store) {

          /* get store data */
          $target = $query->getSqlString();
          $values = $target->values;

          /* get id if any */
          $id = null;
          if (isset($values[$target->primary])) {
            $id = $values[$target->primary];
          }

          /* have id? update */
          if ($id) {

            /* unset id */
            unset($values[$target->primary]);

            /* generate update statement */
            $update = $this->update()
                      ->table($target->into)
                      ->set($values)
                      ->where(array($target->primary => $id));

            /* execute update! */
            if ($this->execute($update)) {
              return $id;
            }

          /* no id, insert */
          } else {

            /* unset id if its an autoincrement */
            if ($target->auto == true) { unset($values[$target->primary]); }

            /* generate insert */
            $insert = $this->insert()
                      ->into($target->into)
                      ->values($values);

            /* execute insert */
            return $this->execute($insert);
          }

          /* return false as default */
          return false;

        /* other normal statement */
        } else {

          /* prepare & execute */
          $statement = $this->_sql->prepareStatementForSqlObject($query);
          $result = $statement->execute();

          /* insert, return last generated value (auto increment) */
          if ($result && ($query instanceof Sql\Insert)) {

            /* oci8? */
            if ($this->_driver == self::DRIVER_OCI8) {
              /* has ai field? */
              if (($field = $query->hasAutoincrement()) !== false) {
                if ($id = $query->{$field}) {
                  return $id;
                }
              }
            } else {
              /* others - mysql */
              if ($id = $this->_db->getDriver()->getLastGeneratedValue()) {
                return $id;
              }
            }
          }

          /* return result */
          return $result;

        } //recycle or not
    }

    public function now() {

        return $this->_now;
    }

    public function expression($expression) {

        return new \Zend\Db\Sql\Expression($expression);
    }
}