class.pdoDB.php

Go to the documentation of this file.

<?php

class pdoDB {

        protected $pdo;
        protected static $staticPbo;
        protected $type_to_mysql_type = array(
                'text' => 'VARCHAR',
                'integer' => 'INT',
                'float' => 'DOUBLE',
                'date' => 'DATE',
                'time' => 'TIME',
                'datetime' => 'TIMESTAMP',
                'clob' => 'LONGTEXT',

        );


        public function __construct() {
                $this->pdo = new PDO('mysql:host=localhost;dbname=test_db;charset=utf8', 'travis', '');
                $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                $attr = PDO::MYSQL_ATTR_USE_BUFFERED_QUERY;
        }


        public function nextId($table_name) {
                if ($this->tableExists($table_name . '_seq')) {
                        $table_seq = $table_name . '_seq';
                        $stmt = $this->pdo->prepare("SELECT * FROM $table_seq");
                        $stmt->execute();
                        $rows = $stmt->fetch(PDO::FETCH_ASSOC);
                        $stmt->closeCursor();

                        return count($rows) ? 0 : $rows['seq'];
                } else {
                        //            return $this->pdo->lastInsertId($table_name) + 1;
                        return 0;
                }
        }


        public function createTable($table_name, $fields) {
                $fields_query = $this->createTableFields($fields);
                $query = "CREATE TABLE $table_name ($fields_query);";
                $this->pdo->exec($query);
        }


        protected function createTableFields($fields) {
                $query = "";
                foreach ($fields as $name => $field) {
                        $type = $this->type_to_mysql_type[$field['type']];
                        $length = $field['length'];
                        $primary = isset($field['is_primary']) && $field['is_primary'] ? "PRIMARY KEY" : "";
                        $notnull = isset($field['is_notnull']) && $field['is_notnull'] ? "NOT NULL" : "";
                        $sequence = isset($field['sequence']) && $field['sequence'] ? "AUTO_INCREMENT" : "";
                        $query .= "$name $type ($length) $sequence $primary $notnull,";
                }

                return substr($query, 0, - 1);
        }


        public function addPrimaryKey($table_name, $primary_keys) {
                $keys = implode($primary_keys);
                $this->pdo->exec("ALTER TABLE $table_name ADD PRIMARY KEY ($keys)");
        }


        public function createSequence($table_name) {
                //TODO
        }


        public function tableExists($table_name) {
                $result = $this->pdo->prepare("SHOW TABLES LIKE :table_name");
                $result->execute(array( ':table_name' => $table_name ));
                $return = $result->rowCount();
                $result->closeCursor();

                return $return > 0;
        }


        public function tableColumnExists($table_name, $column_name) {
                $statement = $this->pdo->query("SHOW COLUMNS FROM $table_name WHERE Field = '$column_name'");
                $statement != NULL ? $statement->closeCursor() : "";

                return $statement != NULL && $statement->rowCount() != 0;
        }


        public function addTableColumn($table_name, $column_name, $attributes) {
                $col = array( $column_name => $attributes );
                $col_str = $this->createTableFields($col);
                $this->pdo->exec("ALTER TABLE $$table_name ADD $$col_str");
        }


        public function dropTable($table_name) {
                $this->pdo->exec("DROP TABLE $table_name");
        }


        public function query($query) {
                $res = $this->pdo->query($query);
                $err = $this->pdo->errorInfo();

                return $res;
        }


        public function fetchAll($query_result) {
                return $query_result->fetchAll($query_result);
        }


        public function dropSequence($table_name) {
                $table_seq = $table_name . "_seq";
                if ($this->tableExists($table_seq)) {
                        $this->pdo->exec("DROP TABLE $table_seq");
                }
        }


        public function dropTableColumn($table_name, $column_name) {
                $this->pdo->exec("ALTER TABLE $$table_name DROP COLUMN $column_name");
        }


        public function renameTableColumn($table_name, $column_old_name, $column_new_name) {
                $this->pdo->exec("alter table $table_name change $column_old_name $column_new_name");
        }


        public function insert($table_name, $values) {
                $real = array();
                foreach ($values as $val) {
                        $real[] = $this->quote($val[1], $val[0]);
                }
                $values = implode(",", $real);
                $this->pdo->exec("INSERT INTO $table_name VALUES ($values)");
        }


        public function fetchObject($query_result) {
                $res = $query_result->fetchObject();
                if ($res == NULL) {
                        $query_result->closeCursor();

                        return NULL;
                }

                return $res;
        }


        public function update($table_name, $values, $where) {
                $query = "UPDATE $table_name SET ";
                foreach ($values as $key => $val) {
                        $qval = $this->quote($val[1], $val[0]);
                        $query .= "$key=$qval,";
                }
                $query = substr($query, 0, - 1) . " WHERE ";
                foreach ($where as $key => $val) {
                        $qval = $this->quote($val[1], $val[0]);
                        $query .= "$key=$qval,";
                }
                $query = substr($query, 0, - 1);
                $this->pdo->exec($query);
        }


        public function manipulate($query) {
                $this->pdo->exec($query);
        }


        public function fetchAssoc($query_result) {
                $res = $query_result->fetch(PDO::FETCH_ASSOC);
                if ($res == NULL) {
                        $query_result->closeCursor();

                        return NULL;
                }

                return $res;
        }


        public function numRows($query_result) {
                return $query_result->rowCount();
        }


        public function quote($value, $type) {
                //TODO TYPE SENSITIVE.
                return $this->pdo->quote($value);
        }


        public function addIndex($table_name, $index_name) {
                return NULL;
        }
}

?>