Tables.php

Go to the documentation of this file.

<?php
/*
 You may not change or alter any portion of this comment or credits
 of supporting developers from this source code or any supporting source code
 which is considered copyrighted (c) material of the original comment or credit authors.

 This program 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.
 */

namespace Xmf\Database;

use Xmf\Language;

class Tables
{
    const POSITION_FIRST = 1;

    private $db;

    private $tables;

    private $queue;

    protected $lastError;

    protected $lastErrNo;

    public function __construct()
    {
        global $xoopsDB; // lock this to legacy support
        //\Xmf\Debug::dump($xoopsDB,true);
        Language::load('database', 'xmf');

        $this->db =& $xoopsDB;
        $this->queueReset();
    }

    public function name($table)
    {
        return $this->db->prefix($table);
    }

    public function addColumn($table, $column, $attributes, $position = null)
    {
        $columnDef=array(
            'name'=>$column,
            'position'=>$position,
            'attributes'=>$attributes
        );

        // Find table def.
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            // Is this on a table we are adding?
            if (isset($tableDef['create']) && $tableDef['create']) {
                switch ($position) {
                    case Tables::POSITION_FIRST:
                        array_unshift($tableDef['columns'], $columnDef);
                        break;
                    case '':
                    case null:
                    case false:
                        array_push($tableDef['columns'], $columnDef);
                        break;
                    default:
                        // should be a column name to add after
                        // loop thru and find that column
                        $i=0;
                        foreach ($tableDef['columns'] as $col) {
                            ++$i;
                            if (strcasecmp($col['name'], $position)==0) {
                                array_splice($tableDef['columns'], $i, 0, array($columnDef));
                                break;
                            }
                        }
                }

                return true;
            } else {
                foreach ($tableDef['columns'] as $col) {
                    if (strcasecmp($col['name'], $column)==0) {
                        return true;
                    }
                }
                switch ($position) {
                    case Tables::POSITION_FIRST:
                        $pos='FIRST';
                        break;
                    case '':
                    case null:
                    case false:
                        $pos='';
                        break;
                    default:
                        $pos="AFTER `{$position}`";
                }
                $this->queue[]="ALTER TABLE `{$tableDef['name']}`"
                    . " ADD COLUMN {$column} {$columnDef['attributes']} {$pos} ";

            }
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true; // exists or is added to queue
    }

    public function addPrimaryKey($table, $column)
    {
        if (isset($this->tables[$table])) {
            $this->queue[]
                = "ALTER TABLE `{$table}` ADD PRIMARY KEY({$column})";
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }

    public function addTable($table)
    {
        if (isset($this->tables[$table])) {
            return true;
        }
        $tableDef=$this->getTable($table);
        if (is_array($tableDef)) {
            $this->tables[$table] = $tableDef;

            return true;
        } else {
            if ($tableDef===true) {
                $tableDef=array();
                $tableDef = array(
                      'name' => $this->db->prefix($table)
                    , 'options' => 'ENGINE=MyISAM');
                $tableDef['create'] = true;
                $this->tables[$table] = $tableDef;

                $this->queue[]=array('createtable'=>$table);

                return true;
            } else {
                return false;
            }
        }
    }

    public function useTable($table)
    {
        if (isset($this->tables[$table])) {
            return true;
        }
        $tableDef=$this->getTable($table);
        if (is_array($tableDef)) {
            $this->tables[$table] = $tableDef;
            return true;
        }
        return false;
    }


    public function alterColumn($table, $column, $attributes, $newName = '', $position = null)
    {
        if (empty($newName)) {
            $newName=$column;
        }
        // Find table def.
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            // Is this on a table we are adding?
            if (isset($tableDef['create']) && $tableDef['create']
                && empty($position)
            ) {
                // loop thru and find the column
                foreach ($tableDef['columns'] as &$col) {
                    if (strcasecmp($col['name'], $column)==0) {
                        $col['name']=$newName;
                        $col['attributes']=$attributes;
                        break;
                    }
                }

                return true;
            } else {
                switch ($position) {
                    case Tables::POSITION_FIRST:
                        $pos='FIRST';
                        break;
                    case '':
                    case null:
                    case false:
                        $pos='';
                        break;
                    default:
                        $pos="AFTER `{$position}`";
                }
                $this->queue[]="ALTER TABLE `{$tableDef['name']}` " .
                    "CHANGE COLUMN `{$column}` `{$newName}` {$attributes} {$pos} ";
            }
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }

    public function copyTable($table, $newTable, $withData = false)
    {
        if (isset($this->tables[$newTable])) {
            return true;
        }
        $tableDef=$this->getTable($table);
        $copy=$this->name($newTable);
        $original=$this->name($table);

        if (is_array($tableDef)) {
            $tableDef['name']=$copy;
            if ($withData) {
                $this->queue[] = "CREATE TABLE `{$copy}` LIKE `{$original}` ;";
                $this->queue[]
                    = "INSERT INTO `{$copy}` SELECT * FROM `{$original}` ;";
            } else {
                $tableDef['create'] = true;
                $this->queue[]=array('createtable'=>$newTable);
            }
            $this->tables[$newTable]=$tableDef;

            return true;
        } else {
            return false;
        }

    }

    public function createIndex($name, $table, $column, $unique = false)
    {
        if (isset($this->tables[$table])) {
            //ALTER TABLE `table` ADD INDEX `product_id` (`product_id`)
            $add = ($unique?'ADD UNIQUE INDEX':'ADD INDEX');
            $this->queue[]
                = "ALTER TABLE `{$table}` {$add} {$name} ({$column})";
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }

    public function dropColumn($table, $column)
    {
        // Find table def.
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $this->queue[]
                = "ALTER TABLE `{$tableDef['name']}` DROP COLUMN `{$column}`";
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }

    public function dropIndex($name, $table)
    {
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $this->queue[]="ALTER TABLE `{$tableDef['name']}` DROP INDEX `{$name}`";
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }

    public function dropIndexes($table)
    {
        // Find table def.
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            // Is this on a table we are adding?
            if (isset($tableDef['create']) && $tableDef['create']) {
                // strip everything but the PRIMARY from definition
                foreach ($tableDef['keys'] as $keyname => $key) {
                    if ($keyname!='PRIMARY') {
                        unset($tableDef['keys'][$keyname]);
                    }
                }
            } else {
                // build drops to strip everything but the PRIMARY
                foreach ($tableDef['keys'] as $keyname => $key) {
                    if ($keyname!='PRIMARY') {
                        $this->queue[] = "ALTER TABLE `{$tableDef['name']}`"
                            . " DROP INDEX {$keyname}";
                    }
                }
            }
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }

    public function dropPrimaryKey($table)
    {
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $this->queue[]="ALTER TABLE `{$tableDef['name']}` DROP PRIMARY KEY ";
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }

    public function dropTable($table)
    {
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $this->queue[]="DROP TABLE `{$tableDef['name']}` ";
            unset($this->tables[$table]);
        }
        // no table is not an error since we are dropping it anyway
        return true;
    }


    public function renameTable($table, $newName)
    {
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $newTable = $this->name($newName);
            $this->queue[]
                = "ALTER TABLE `{$tableDef['name']}` RENAME TO `{$newTable}`";
            $tableDef['name'] = $newTable;
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }

    public function setTableOptions($table, $options)
    {
        // ENGINE=MEMORY DEFAULT CHARSET=utf8;
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $this->queue[]="ALTER TABLE `{$tableDef['name']}` {$options} ";
            $tableDef['options'] = $options;
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }


    public function queueReset()
    {
        $this->tables = array();
        $this->queue  = array();
    }

    public function queueExecute($force = false)
    {
        $this->expandQueue();
        foreach ($this->queue as &$ddl) {
            if (is_array($ddl)) {
                if (isset($ddl['createtable'])) {
                    $ddl=$this->renderTableCreate($ddl['createtable']);
                }
            }
            $result = $this->execSql($ddl, $force);
            if (!$result) {
                $this->lastError = $this->db->error();
                $this->lastErrNo = $this->db->errno();

                return false;
            }
        }

        return true;
    }


    public function delete($table, $criteria)
    {
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $where = '';
            if (is_scalar($criteria)) {
                $where = 'WHERE '.$criteria;
            } elseif (is_object($criteria)) {
                $where = $criteria->renderWhere();
            }
            $this->queue[]="DELETE FROM `{$tableDef['name']}` {$where}";
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }

    public function insert($table, $columns)
    {
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $colsql = '';
            $valsql = '';
            foreach ($tableDef['columns'] as $col) {
                $comma=empty($colsql)?'':', ';
                if (isset($columns[$col['name']])) {
                    $colsql .= $comma.$col['name'];
                    $valsql .= $comma.$this->db->quote($columns[$col['name']]);
                }
            }
            $sql = "INSERT INTO `{$tableDef['name']}` ({$colsql}) VALUES({$valsql})";
            $this->queue[]=$sql;

            return true;
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return null;
        }
    }

    public function update($table, $columns, $criteria)
    {
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $where = '';
            if (is_scalar($criteria)) {
                $where = 'WHERE '.$criteria;
            } elseif (is_object($criteria)) {
                $where = $criteria->renderWhere();
            }
            $colsql = '';
            foreach ($tableDef['columns'] as $col) {
                $comma=empty($colsql)?'':', ';
                if (isset($columns[$col['name']])) {
                    $colsql .= $comma . $col['name'] . ' = '
                        . $this->db->quote($columns[$col['name']]);
                }
            }
            $sql = "UPDATE `{$tableDef['name']}` SET {$colsql} {$where}";
            $this->queue[]=$sql;

            return true;
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return null;
        }
    }

    public function truncate($table)
    {
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $this->queue[]="TRUNCATE TABLE `{$tableDef['name']}`";
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return false;
        }

        return true;
    }



    public function renderTableCreate($table, $prefixed = false)
    {
        if (isset($this->tables[$table])) {
            $tableDef = &$this->tables[$table];
            $tablename=($prefixed?$tableDef['name']:$table);
            $sql = "CREATE TABLE `{$tablename}` (\n";
            foreach ($tableDef['columns'] as $col) {
                $sql .= "    {$col['name']}  {$col['attributes']},\n";
            }
            $keysql='';
            foreach ($tableDef['keys'] as $keyname => $key) {
                $comma = empty($keysql)?'  ':', ';
                if ($keyname=='PRIMARY') {
                    $keysql .= "  {$comma}PRIMARY KEY ({$key['columns']})\n";
                } else {
                    $unique=$key['unique']?'UNIQUE ':'';
                    $keysql .= "  {$comma}{$unique}KEY {$keyname} "
                        . " ({$key['columns']})\n";
                }
            }
            $sql .= $keysql;
            $sql .= ") {$tableDef['options']};\n";

            return $sql;
        } else { // no table established
            $this->lastError = _DB_XMF_TABLE_IS_NOT_DEFINED;
            $this->lastErrNo = -1;

            return null;
        }

    }

    private function & execSql($sql, $force = false)
    {
        if ($force) {
            $result = $this->db->queryF($sql);
        } else {
            $result = $this->db->query($sql);
        }

        if (!$result) {
            $this->lastError = $this->db->error();
            $this->lastErrNo = $this->db->errno();
        }

        return $result;

    }

    private function fetch(&$result)
    {
        return $this->db->fetchArray($result);
    }

    private function getTable($table)
    {

        $tableDef = array();

        $sql  = 'SELECT TABLE_NAME, ENGINE, CHARACTER_SET_NAME ';
        $sql .= ' FROM `INFORMATION_SCHEMA`.`TABLES` t, ';
        $sql .= ' `INFORMATION_SCHEMA`.`COLLATIONS` c ';
        $sql .= ' WHERE t.TABLE_SCHEMA = \'' . \XoopsBaseConfig::get('db-name') . '\' ';
        $sql .= ' AND t.TABLE_NAME = \'' . $this->name($table) . '\' ';
        $sql .= ' AND t.TABLE_COLLATION  = c.COLLATION_NAME ';

        $result = $this->execSql($sql);
        if (!$result) {
            return false;
        }
        $tableSchema = $this->fetch($result);
        if (empty($tableSchema)) {
            return true;
        }
        $tableDef['name'] =  $tableSchema['TABLE_NAME'];
        $tableDef['options'] = 'ENGINE=' . $tableSchema['ENGINE'] . ' '
            . 'DEFAULT CHARSET=' . $tableSchema['CHARACTER_SET_NAME'];

        $sql  = 'SELECT * ';
        $sql .= ' FROM `INFORMATION_SCHEMA`.`COLUMNS` ';
        $sql .= ' WHERE TABLE_SCHEMA = \'' . \XoopsBaseConfig::get('db-name') . '\' ';
        $sql .= ' AND TABLE_NAME = \'' . $this->name($table) . '\' ';
        $sql .= ' ORDER BY `ORDINAL_POSITION` ';

        $result = $this->execSql($sql);

        while ($column=$this->fetch($result)) {
            $attributes = ' ' . $column['COLUMN_TYPE'] . ' '
                . (($column['IS_NULLABLE'] == 'NO') ? ' NOT NULL ' : '' )
                . (($column['COLUMN_DEFAULT'] === null) ? '' :
                        " DEFAULT '". $column['COLUMN_DEFAULT'] . "' ")
                . $column['EXTRA'];

            $columnDef=array(
                'name'=>$column['COLUMN_NAME'],
                'position'=>$column['ORDINAL_POSITION'],
                'attributes'=>$attributes
            );

            $tableDef['columns'][] = $columnDef;
        };

        $sql  = 'SELECT `INDEX_NAME`, `SEQ_IN_INDEX`, `NON_UNIQUE`, ';
        $sql .= ' `COLUMN_NAME`, `SUB_PART` ';
        $sql .= ' FROM `INFORMATION_SCHEMA`.`STATISTICS` ';
        $sql .= ' WHERE TABLE_SCHEMA = \'' . \XoopsBaseConfig::get('db-name') . '\' ';
        $sql .= ' AND TABLE_NAME = \'' . $this->name($table) . '\' ';
        $sql .= ' ORDER BY `INDEX_NAME`, `SEQ_IN_INDEX` ';

        $result = $this->execSql($sql);

        $lastkey = '';
        $keycols='';
        $keyunique = false;
        while ($key=$this->fetch($result)) {
            if ($lastkey != $key['INDEX_NAME']) {
                if (!empty($lastkey)) {
                    $tableDef['keys'][$lastkey]['columns'] = $keycols;
                    $tableDef['keys'][$lastkey]['unique'] = $keyunique;
                }
                $lastkey = $key['INDEX_NAME'];
                $keycols = $key['COLUMN_NAME'];
                if (!empty($key['SUB_PART'])) {
                    $keycols .= ' (' . $key['SUB_PART'] . ')';
                }
                $keyunique = !$key['NON_UNIQUE'];
            } else {
                $keycols .= ', ' . $key['COLUMN_NAME'];
                if (!empty($key['SUB_PART'])) {
                    $keycols .= ' ('.$key['SUB_PART'].')';
                }
            }
            //$tableDef['keys'][$key['INDEX_NAME']][$key['SEQ_IN_INDEX']] = $key;
        };
        if (!empty($lastkey)) {
            $tableDef['keys'][$lastkey]['columns'] = $keycols;
            $tableDef['keys'][$lastkey]['unique'] = $keyunique;
        }

        return $tableDef;

    }

    private function expandQueue()
    {
        foreach ($this->queue as &$ddl) {
            if (is_array($ddl)) {
                if (isset($ddl['createtable'])) {
                    $ddl=$this->renderTableCreate($ddl['createtable'], true);
                }
            }
        }
    }

    public function getLastError()
    {
        return $this->lastError;
    }

    public function getLastErrNo()
    {
        return $this->lastErrNo;
    }

    public function dumpTables()
    {
        return $this->tables;
    }

    public function dumpQueue()
    {
        $this->expandQueue();

        return $this->queue;
    }
}