Nephthys

 <?php

 /***************************************************************************

  *

  * Nephthys - file sharing management

  * Copyright (c) by Andreas Unterkircher, unki@netshadow.at

  *

  *  This file is part of Nephthys.

  *

  *  Nephthys is free software: you can redistribute it and/or modify

  *  it under the terms of the GNU General Public License as published by

  *  the Free Software Foundation, either version 3 of the License, or

  *  (at your option) any later version.

  *

  *  Nephthys is distributed in the hope that it will be useful,

  *  but WITHOUT ANY WARRANTY; without even the implied warranty of

  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

  *  GNU General Public License for more details.

  *

  *  You should have received a copy of the GNU General Public License

  *  along with Nephthys. If not, see <http://www.gnu.org/licenses/>.

  *

  ***************************************************************************/

 class NEPHTHYS_DB {

    private $db;

    private $cfg;

    private $is_connected;

    private $last_error;

    private $parent;

    /**

     * NEPHTHYS_DB class constructor

     *

     * This constructor initially connect to the database.

     */

    public function __construct()

    {

       global $nephthys;

       $this->parent =& $nephthys;

       $this->cfg = $this->parent->cfg;

       /* We are starting disconnected */

       $this->setConnStatus(false);

       /* Connect to MySQL Database */

       $this->db_connect();

    } // __construct()

    /**

     * NEPHTHYS_DB class deconstructor

     *

     * This destructor will close the current database connection.

     */

    public function __destruct()

    {

       $this->db_disconnect();

    } // _destruct()

    /**

     * NEPHTHYS_DB database connect

     *

     * This function will connect to the database via MDB2

     */

    private function db_connect()

    {

       $options = array(

          'debug' => 2,

          'portability' => 'DB_PORTABILITY_ALL'

       );

       switch($this->cfg->db_type) {

          default:

          case 'mysql':

             $dsn = "mysqli://"

                . $this->cfg->mysql_user .":"

                . $this->cfg->mysql_pass ."@"

                . $this->cfg->mysql_host ."/"

                . $this->cfg->mysql_db;

             break;

          case 'sqlite':

             $dsn = "sqlite:///"

                . $this->cfg->sqlite_path;

             break;

       }

       $this->db = MDB2::connect($dsn, $options);

       if(PEAR::isError($this->db)) {

          $this->throwError("Unable to connect to database: ". $this->db->getMessage() .' - '. $this->db->getUserInfo());

          $this->setConnStatus(false);

       }

       $this->setConnStatus(true);

    } // db_connect()

    /**

     * NEPHTHYS_DB database disconnect

     *

     * This function will disconnected an established database connection.

     */

    private function db_disconnect()

    {

       $this->db->disconnect();

    } // db_disconnect()

    /**

     * NEPHTHYS_DB database query

     *

     * This function will execute a SQL query and return the result as

     * object.

     */

    public function db_query($query = "", $mode = MDB2_FETCHMODE_OBJECT)

    {

       if($this->getConnStatus()) {

          $this->db->setFetchMode($mode);

          /* for manipulating queries use exec instead of query. can save

           * some resource because nothing has to be allocated for results.

           */

          if(preg_match('/^(update|insert)i/', $query)) {

             $result = $this->db->exec($query);

          }

          else {

             $result = $this->db->query($query);

          }

          if(PEAR::isError($result))

             $this->throwError($result->getMessage() .' - '. $result->getUserInfo());

          return $result;

       }

       else

          $this->throwError("Can't execute query - we are not connected!");

    } // db_query()

    /**

     * NEPHTHYS_DB database prepare query

     *

     * This function will prepare a SQL query to be executed

     * @param string $query

     * @param int $mode

     * @return mixed

     */

    public function db_prepare($query = "")

    {

       if($this->getConnStatus()) {

          $this->db->prepare($query);

          /* for manipulating queries use exec instead of query. can save

           * some resource because nothing has to be allocated for results.

           */

          if(preg_match('/^(update|insert|delete)i/', $query)) {

             $sth = $this->db->prepare($query, MDB2_PREPARE_MANIP);

          }

          else {

             $sth = $this->db->prepare($query, MDB2_PREPARE_RESULT);

          }

          if(PEAR::isError($sth))

             $this->throwError($sth->getMessage() .' - '. $sth->getUserInfo());

          return $sth;

       }

       else

          $this->throwError("Can't prepare query - we are not connected!");

    } // db_prepare()

    /**

     * NEPHTHYS_DB database execute a prepared query

     *

     * This function will execute a previously prepared SQL query

     * @param mixed $sth

     * @param mixed $data

     */

    public function db_execute($sth, $data)

    {

       if($this->getConnStatus()) {

          $result = $sth->execute($data);

          if(PEAR::isError($result))

             $this->throwError($result->getMessage() .' - '. $result->getUserInfo());

       }

       else

          $this->throwError("Can't prepare query - we are not connected!");

    } // db_execute()

    /**

     * NEPHTHYS_DB database query & execute

     *

     * This function will execute a SQL query and return nothing.

     */

    public function db_exec($query = "")

    {

       if(!$this->getConnStatus())

          return false;

       $affected =& $this->db->exec($query);

       if(PEAR::isError($affected)) {

          $this->throwError($affected->getMessage());

          return false;

       }

       return true;

    } // db_exec()

    /**

     * NEPHTHYS_DB fetch ONE row

     *

     * This function will execute the given but only return the

     * first result.

     */

    public function db_fetchSingleRow($query = "", $mode = MDB2_FETCHMODE_OBJECT)

    {

       if($this->getConnStatus()) {

          $row = $this->db->queryRow($query, array(), $mode);

          if(PEAR::isError($row))

             $this->throwError($row->getMessage() .' - '. $row->getUserInfo());

          return $row;

       }

       else {

          $this->throwError("Can't fetch row - we are not connected!");

       }

    } // db_fetchSingleRow()

    /**

     * NEPHTHYS_DB number of affected rows

     *

     * This functions returns the number of affected rows but the

     * given SQL query.

     */

    public function db_getNumRows($query = "")

    {

       /* Execute query */

       $result = $this->db_query($query);

       /* Errors? */

       if(PEAR::isError($result))

          $this->throwError($result->getMessage() .' - '. $result->getUserInfo());

       return $result->numRows();

    } // db_getNumRows()

    /**

     * NEPHTHYS_DB get primary key

     *

     * This function returns the primary key of the last

     * operated insert SQL query.

     */

    public function db_getid($table_name = null)

    {

       /* Get the last primary key ID from execute query */

       $id = $this->db->lastInsertID($table_name);

       if (PEAR::isError($id)) {

           $this->throwError($id->getMessage());

       }

       return $id;

    } // db_getid()

    /**

     * NEPHTHYS_DB check table exists

     *

     * This function checks if the given table exists in the

     * database

     * @param string, table name

     * @return true if table found otherwise false

     */

    public function db_check_table_exists($table_name = "")

    {

       if(!$this->getConnStatus())

          return false;

       switch($this->cfg->db_type) {

          default:

          case 'mysql':

             $result = $this->db_query("SHOW TABLES");

             $tables_in = "Tables_in_". $this->cfg->mysql_db;

             while($row = $result->fetchRow()) {

                if($row->$tables_in == $table_name)

                   return true;

             }

             break;

          case 'sqlite':

             $result = $this->db_query("SELECT name FROM sqlite_master WHERE type='table'");

             while($row = $result->fetchRow()) {

                if($row->name == $table_name)

                   return true;

             }

             break;

       }

       return false;

    } // db_check_table_exists()

    /**

     * NEPHTHYS_DB rename table

     *

     * This function will rename an database table

     * @param old_name, new_name

     */

    public function db_rename_table($old, $new)

    {

       if($this->db_check_table_exists($old)) {

          if(!$this->db_check_table_exists($new))

             $this->db_query("RENAME TABLE ". $old ." TO ". $new);

          else

             $this->throwError("Can't rename table ". $old ." - ". $new ." already exists!");

       }

    } // db_rename_table()

    /**

     * NEPHTHYS_DB drop table

     *

     * This function will delete the given table from database

     */

    public function db_drop_table($table_name)

    {

       if($this->db_check_table_exists($table_name))

          $this->db_query("DROP TABLE ". $table_name);

    } // db_drop_table()

    /**

     * NEPHTHYS_DB truncate table

     *

     * This function will truncate (reset) the given table

     */

    public function db_truncate_table($table_name)

    {

       if($this->db_check_table_exists($table_name))

          $this->db_query("TRUNCATE TABLE ". $table_name);

    } // db_truncate_table()

    /**

     * NEPHTHYS_DB check column exist

     *

     * This function checks if the given column exists within

     * the specified table.

     */

    public function db_check_column_exists($table_name, $column)

    {

       if(!$this->getConnStatus())

          return false;

       switch($this->cfg->db_type) {

          default:

          case 'mysql':

             $result = $this->db_query("DESC ". $table_name, MDB2_FETCHMODE_ORDERED);

             while($row = $result->fetchRow()) {

             if(in_array($column, $row))

                return true;

             }

             break;

          case 'sqlite':

             $result = $this->db_query("

                SELECT sql

                FROM

                   (SELECT * FROM sqlite_master UNION ALL

                    SELECT * FROM sqlite_temp_master)

                WHERE

                   tbl_name LIKE '". $table_name ."'

                AND type!='meta'

                AND sql NOT NULL

                AND name NOT LIKE 'sqlite_%'

                ORDER BY substr(type,2,1), name

             ");

             while($row = $result->fetchRow()) {

                /* CREATE TABLE xx ( col1 int, col2 bool, col3 ...) */

                if(strstr($row->sql, " ". $column ." ") !== false)

                   return true;

             }

             break;

       }

       return false;

    } // db_check_column_exists()

    /**

     * NEPHTHYS_DB check index exists

     *

     * This function checks if the given index can be found

     * within the specified table.

     */

    public function db_check_index_exists($table_name, $index_name)

    {

       $result = $this->db_query("DESC ". $table_name, MDB2_FETCHMODE_ORDERED);

       while($row = $result->fetchRow()) {

          if(in_array("KEY `". $index_name ."`", $row))

             return 1;

       }

       return 0;

    } // db_check_index_exists()

    /**

     * NEPHTHYS_DB alter table

     *

     * This function offers multiple methods to alter a table.

     * * add/modify/delete columns

     * * drop index

     */

    public function db_alter_table($table_name, $option, $column, $param1 = "", $param2 = "")

    {

       if(!$this->db_check_table_exists($table_name)) {

          $this->throwError("Table ". $table_name ." does not exist!");

          return false;

       }

       switch($this->cfg->db_type) {

          default:

          case 'mysql':

             switch(strtolower($option)) {

                case 'add':

                   if(!$this->db_check_column_exists($table_name, $column))

                      $this->db_query("ALTER TABLE ". $table_name ." ADD ". $column ." ". $param1);

                   break;

                case 'change':

                   if($this->db_check_column_exists($table_name, $column))

                      $this->db_query("ALTER TABLE ". $table_name ." CHANGE ". $column ." ". $param1);

                   break;

                case 'drop':

                   if($this->db_check_column_exists($table_name, $column))

                      $this->db_query("ALTER TABLE ". $table_name ." DROP ". $column);

                   break;

                case 'dropidx':

                   if($this->db_check_index_exists($table_name, $column))

                      $this->db_query("ALTER TABLE ". $table_name ." DROP INDEX ". $column);

                   break;

             }

             break;

          case 'sqlite':

             $this->throwError("SQLite only support ALTER TABLE rudimentary with version 3, so no support here right now.");

             break;

       }

    } // db_alter_table()

    /**

     * NEPHTHYS_DB get database version

     *

     * This functions returns the current database version

     */

    public function getVersion()

    {

       if($this->db_check_table_exists(MYSQL_PREFIX ."settings")) {

          $result = $this->db_fetchSingleRow("

             SELECT setting_value

             FROM ". MYSQL_PREFIX ."settings

             WHERE setting_key LIKE 'version'

          ");

          return $result->setting_value;

       }

       else

          return 0;

    } // getVersion()

    /**

     * NEPHTHYS_DB set version

     *

     * This function sets the version of database

     */

    public function setVersion($version)

    {

       $this->db_query("

          REPLACE INTO ". MYSQL_PREFIX ."settings

             (setting_key, setting_value)

          VALUES ('version', '". $version ."')

       ");

    } // setVersion()

    /**

     * NEPHTHYS_DB get connection status

     *

     * This function checks the internal state variable if already

     * connected to database.

     */

    private function setConnStatus($status)

    {

       $this->is_connected = $status;

    } // setConnStatus()

    /**

     * NEPHTHYS_DB set connection status

     * This function sets the internal state variable to indicate

     * current database connection status.

     */

    private function getConnStatus()

    {

       return $this->is_connected;

    } // getConnStatus()

    /**

     * NEPHTHYS_DB throw error

     *

     * This function shows up error messages and afterwards through exceptions.

     */

    private function throwError($string)

    {

       if(!defined('DB_NOERROR'))  {

          $this->parent->_error($string);

          try {

             throw new NEPHTHYS_EXCEPTION;

          }

          catch(NEPHTHYS_EXCEPTION $e) {

             print "<br /><br />\n";

             $this->parent->_error($e);

             die;

          }

       }

       $this->last_error = $string;

    } // throwError()

    /**

     * quote string

     *

     * this function handsover the provided string to the MDB2

     * quote() function which will return the, for the selected

     * database system, correctly quoted string.

     *

     * @param string $query

     * @return string

     */

    public function db_quote($obj)

    {

       return $this->db->quote($obj);

    } // db_quote()

    /**

     * start transaction

     *

     * this will start a transaction on ACID-supporting database

     * systems.

     *

     * @return bool

     */

    public function db_start_transaction()

    {

       if(!$this->getConnStatus())

          return false;

       if(!$this->db->supports('transactions'))

          return false;

       $result = $this->db->beginTransaction();

       /* Errors? */

       if(PEAR::isError($result))

          $this->throwError($result->getMessage() .' - '. $result->getUserInfo());

       return true;

    } // db_start_transaction()

    /**

     * commit transaction

     *

     * this will commit an ongoing transaction on ACID-supporting

     * database systems

     *

     * @return bool

     */

    public function db_commit_transaction()

    {

       if(!$this->getConnStatus())

          return false;

       if(!$this->db->inTransaction())

          return false;

       $result = $this->db->commit();

       /* Errors? */

       if(PEAR::isError($result))

          $this->throwError($result->getMessage() .' - '. $result->getUserInfo());

       return true;

    } // db_commit_transaction()

    /**

     * rollback transaction()

     *

     * this function aborts a on going transaction

     *

     * @return bool

     */

    public function db_rollback_transaction()

    {

       if(!$this->getConnStatus())

          return false;

       if(!$this->db->inTransaction())

          return false;

       $result = $this->db->rollback();

       /* Errors? */

       if(PEAR::isError($result))

          $this->throwError($result->getMessage() .' - '. $result->getUserInfo());

       return true;

    } // db_rollback_transaction()

 } // NEPHTHYS_DB()

 // vim: set filetype=php expandtab softtabstop=3 tabstop=3 shiftwidth=3 autoindent smartindent:

 ?>