Source for file SQLiteAdmin.class.php

<?php
 
/**
 * Implementation file of the SQLiteAdmin class.
 *
 * <pre>
 * PROJECT    : SQLiteAdmin
 *              A Crafty demonstration project.
 * AUTHOR     : Crafty Team <crafty@zulan.net>
 * COPYRIGHT  : (c) Thomas Ryssel, 2004
 *
 * FILE       : [ROOT]\includes\SQLiteAdmin.class.php
 * DESCRIPTION: Implementation file of the SQLiteAdmin class.
 * </pre>
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library 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
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307  USA
 *
 * SQLiteAdmin is only a demonstration project for {@link Crafty}, so please be
 * aware of the fact that this has minor priority compared to the main project
 * itself.
 *
 * @package     SQLiteAdmin
 * @author      Crafty Team <crafty@zulan.net>
 * @copyright   Copyright (c) Thomas Ryssel 2004-2005
 * @version     1.0.4.2005.02.18
 * @link        sqliteadmin.zulan.net
 * @license     http://www.gnu.org/licenses/lgpl.html
 *               GNU Lesser General Public License Version 2.1
 ***/
 
/**
 * SQLiteAdmin - Class implementation
 *
 * This class is a wrapper for the new SQLite extension of PHP5. It's an OOP
 * environment designed for easier Usage of SQLite. This class is designed for
 * and tested with SQLite Version 2.8.14. There is no guarantee that it will run
 * with any other SQLite Version, although it will work most likely with versions
 * prior to 2.8.14. As SQLite itself, this is only a testing stage and there
 * is no guarantee for complete work.
 *
 * The SQLite Project itself could be called a 'front-end' to this class, since
 * it simply passes all tasks to it.
 *
 * You can use SQLiteAdmin by spawning this class:
 *
 * <pre>
 * $sqlite = new SQLiteAdmin('filename.db');
 * </pre>
 *
 * See the descriptions of the class members and methods for further information.
 *
 * @package     SQLiteAdmin
 * @author      Crafty Team <crafty@zulan.net>
 * @copyright   Copyright (c) Thomas Ryssel 2004-2005
 * @version     1.0.4.2005.02.16
 * @link        sqliteadmin.zulan.net
 * @license     http://www.gnu.org/licenses/lgpl.html
 *               GNU Lesser General Public License Version 2.1
 ***/
class SQLiteAdmin
{
    /**
     * Internal database resource
     *
     * The internally stored database handle. It is set by {@link openDB}
     * or {@link createDB} and used by most of the public functions. You do not
     * have to take care of this.
     *
     * @var resource 
     ***/
    protected $_dblink           = NULL;
 
    /**
     * Internal result resource
     *
     * The internally stored result resource. It can be an SQLite query resource
     * or a boolean value depending on the kind of query (whether or not
     * 'SELECT'). It is set whenever the {@link query} public function is called
     * with the parameter '$remember' set to true.
     *
     * @var resource 
     ***/
    protected $_result           = NULL;
 
    /**
     * SQL Datatype array
     *
     * List of accepted data types. Used by {@link getTableInfo}.
     *
     * @var array 
     ***/
    public $datatypeArray    = array();
 
    /**
     * SQL Constraints array
     *
     * List of accepted constraints. Used by {@link getTableInfo}.
     *
     * @var array 
     ***/
    public $constraintsArray = array();
 
    /**#@+
     * @access public
     ***/
    /**
     * Database filename
     *
     * This stores the complete path and file name of the opened database file.
     * If this is empty, no database has (yet) been opened.
     *
     * @var string 
     ***/
    public    $filename          = '';
 
    /**
     * Database short filename
     *
     * This stores the file name without path of the opened database file.
     * If this is empty, no database has (yet) been opened.
     *
     * @var string 
     ***/
    public    $shortname         = '';
 
    /**
     * Database filename
     *
     * This stores the complete path and file name of the opened database file,
     * encoded by PHP's {@link http://php.net/base64_encode base64_encode}
     * public function. That is primarily for correct filename passing via HTTP.
     * If this is empty, no database has (yet) been opened.
     *
     * @var string 
     ***/
    public    $base64name        = '';
 
    /**
     * Message buffer
     *
     * This stores all messages produced during processes done by this class.
     * Mostly in cases of error, this string will contain a more accurate
     * description of what went wrong.
     *
     * @var string 
     ***/
    public    $message    = '';
 
 
    /**
     * Initializes a SQLiteAdmin instance
     *
     * This initializes an SQLiteAdmin instance. If $filename is not empty, the
     * object try to open (see {@link openDB}) the database file and
     * establish a connection to it.<br/>
     * Additionally, it sets up two internal data arrays for keyword lists in the
     * later Table structure parser. These can be given by parameter, too.
     *
     * @param  string  $filename    [optional] Database file, that should be opened.
     * @param  array   $datatypes   [optional] List of accepted SQL data types.
     * @param  array   $constraints [optional] List of accepted SQL constraints.
     ***/
    public function __construct($filename = '', $datatypes = NULL,
                                $constraints = NULL)
    {
        if (!empty($filename)) {
            $this->openDB($filename);
        }
 
        if (!empty($datatypes)) {
            $this->datatypeArray = $datatypes;
        } else {
            $this->datatypeArray = array(
                                        'VARCHAR', 'TINYINT', 'TEXT', 'DATE',
                                        'SMALLINT', 'MEDIUMINT', 'INT', 'INTEGER',
                                        'BIGINT', 'FLOAT', 'DOUBLE', 'DECIMAL',
                                        'DATETIME', 'TIMESTAMP', 'TIME', 'YEAR',
                                        'CHAR', 'TINYBLOB', 'TINYTEXT', 'BLOB',
                                        'MEDIUMBLOB', 'MEDIUMTEXT', 'LONGBLOB',
                                        'LONGTEXT', 'ENUM', 'SET', 'BOOLEAN'
                                    );
        }
 
        if (!empty($constraints)) {
            $this->constraintsArray = $constraints;
        } else {
            $this->constraintsArray = array(
                                          'NOT NULL', 'PRIMARY KEY', 'UNIQUE'
                                      );
        }
    }
 
    /**
     * Open an existing database file
     *
     * This will try to open the given database file. In addition, if it succeeds
     * it will setup the internal {@link $_dblink} and create the specific
     * {@link $filename filename} strings.
     *
     * @param   string   $filename  Database file to open
     * @return  boolean  State of success
     ***/
    public function openDB($filename)
    {
        if (!file_exists($filename) || !($db = @sqlite_open($filename))) {
            return false;
        }
        $this->filename   = $filename;
        $this->base64name = base64_encode($filename);
        $this->shortname  = (false !== strrpos($filename, DIRECTORY_SEPARATOR))
                            ? substr($filename, strrpos($filename,
                                                        DIRECTORY_SEPARATOR) + 1)
                            : $filename;
 
        $this->_dblink    = $db;
        return true;
    }
 
    /**
     * Create a new database file
     *
     * This will try to create a new database file. In addition, if it succeeds
     * it will setup the internal {@link $_dblink} and create the specific
     * {@link $filename filename} strings. If if a table name is passed, it will
     * automatically try to create a new table using {@link tableCreate}.
     *
     * @param   string   $filename   Database file to create.
     * @param   string   $tablename  [optional] Table to create in new database.
     * @return  boolean  State of success
     ***/
    public function createDB($filename, $tablename = '')
    {
        if (file_exists($filename) || !($db = @sqlite_open($filename))) {
            return false;
        }
        $this->filename   = $filename;
        $this->base64name = base64_encode($filename);
        $this->shortname  = (false !== strrpos($filename, DIRECTORY_SEPARATOR))
                            ? substr($filename, strrpos($filename,
                                                        DIRECTORY_SEPARATOR) + 1)
                            : $filename;
 
        $this->_dblink    = $db;
        $this->tableCreate($tablename);
        return false;
    }
 
    /**
     * Close currently opened database
     *
     * This will close the currently opened database connection. In any normal
     * case, this will be done at the end of your script automatically. You will
     * need this only if you want to open another database with the same
     * SQLiteAdmin instance.
     *
     * @return  boolean  State of success
     ***/
    public function closeDB()
    {
        if (@sqlite_close($this->_dblink)) {
            $this->filename   = '';
            $this->shortname  = '';
            $this->base64name = '';
            return true;
        }
        return false;
    }
 
    /**
     * Execute an SQL Query
     *
     * This will execute an SQL query using the internal database link. $remember
     * decides whether or not an result resource of a SELECT query shall be stored
     * in the internal {@link $_result} resource. If that's the case, you can call
     * {@link fetchArray} without any other parameters.
     *
     * @param   string            $query     SQL Query to execute
     * @param   boolean           $remember  [optional, default=true] Save in
     *                                        internal result resource?
     * @return  boolean|resource Result resource in case of SELECT queries, state
     *                             of success in any other case.
     ***/
    public function query($query, $remember = true)
    {
        $preg = '~(?>\s*(?>/\*.*?\*/|//[^\r\n]*))*\s*([^\'";]*(?:(["\']?+)'
                . '(?>.*?\2)[^\'";]*)*);~s';
        if (1 < preg_match_all($preg, $query, $matches, PREG_SET_ORDER)) {
            $queries = array();
            foreach ($matches as $m) {
                $queries[] = $m[1];
            }
        } else {
            $queries = array($query);
        }
 
        foreach ($queries as $query_i) {
            if (0 !== strpos(strtoupper($query_i), 'SELECT')
                 && 0 !== strpos(strtoupper($query_i), 'ALTER TABLE')) {
                if (@sqlite_query($this->_dblink, $query_i)) {
                    continue;
                } else {
                    $this->message .= $this->lastError() . '<br />';
                    return FALSE;
                }
            } elseif (0 === strpos(strtoupper($query_i), 'SELECT')) {
                if ($res = @sqlite_query($this->_dblink, $query_i)) {
                    if ($remember) {
                        $this->_result = $res;
                    }
                    return $res;
                } else {
                    $this->message .= $this->lastError() . '<br />';
                    return FALSE;
                }
            } else {
                return $this->queryAlterWrapper($query_i);
            }
        }
        if (count($queries) > 1) {
            $this->message .= 'Executed ' . count($queries) . ' queries.<br />';
        }
        return TRUE;
    }
    
    /**
     * Handle ALTER TABLE queries
     *
     * This is a wrapper function for ALTER TABLE queries, since SQLite does
     * not provide that functionality by itself. It merely parses the given
     * query string and provides the collected data to the internal functions
     * {@link tableAddField()}, {@link tableEditField()} and
     * {@link tableDeleteField}.
     *
     * @param   string   $query     ALTER TABLE SQL Query to execute
     * @return  boolean  State of success.
     ***/
    public function queryAlterWrapper($query)
    {
         $table_data = Array();
 
         /* replace arrays to prepare sql table creation string */
         $pats = array('/[\r\n]{1,2}/', '/\s+/', '/\( /', '/ \)/', '/, /');
         $reps = array('',              ' ',     '(',     ')',     ',');
 
         /* prepare pregs for table data extraction */
         $alter_pattern = '/ALTER TABLE (.*?)[ ]*(ADD|MODIFY|DROP|RENAME COLUMN)[ ]*'
               . '(?:\((.*)\)|(.*?$))/i';
         $fname         = '(\S+)';
         $ftype         = '(?<=\W)(' . implode('|', $this->datatypeArray)
                        . ')(?=\W|$)(?: ?\(([^\)]+)\))?';
         $fconst        = ' ?([^,]*)';
         $fld_pattern   = sprintf('/%1$s %2$s(?:%3$s)?(?:,|$)/i', 
                                  $fname, $ftype, $fconst);
         $con_pattern = '/(?<=\W|^)(' . implode('|', $this->constraintsArray)
                      . ')(?=\W|$)/i';
         $flddrop_pat   = '~([^ ,$]+)(?:,[ ]*|$)~';
         $fldren_pat    = '~([^ ,$]+)\s+TO\s+([^ ,$]+)(?:,[ ]*|$)~i';
                        
         $raw  = trim(preg_replace($pats, $reps, $query));
         if (preg_match($alter_pattern, $raw, $matches)) {
             $tbl_name    = $matches[1];
             $alter_type  = strtoupper($matches[2]);
             if ($alter_type == 'ADD' ||$alter_type == 'MODIFY') {
                 preg_match_all($fld_pattern, 
                        (!empty($matches[3]) ? $matches[3] : $matches[4]), 
                        $fld_matches, PREG_SET_ORDER);
                 $tbl_fields = array();
                 foreach ($fld_matches as $id => $field) {
                     $fld = array();
                     $fld['field_name'] = trim($field[1], "'");
                     $fld['field_type'] = strtoupper($field[2]);
                     $fld['field_size'] = $field[3];
                     $fld['field_keys'] = array();
                     if (preg_match_all($con_pattern, $field[4], $con_matches)) {
                         $fld['field_keys'] = $con_matches[1];
                         foreach($fld['field_keys'] as $k => $v) {
                             $fld['field_keys'][$k] = strtoupper($v);
                         }
                     }
                     $tbl_fields[] = $fld;
                 }
             } elseif ($alter_type == 'DROP') {
                 preg_match_all($flddrop_pat, 
                                 (!empty($matches[3]) ? $matches[3] : $matches[4]), 
                                $fld_matches, PREG_SET_ORDER);
                 $tbl_fields = array();
                 foreach ($fld_matches as $id => $field) {
                     $tbl_fields[] = $field[1];
                 }
             } elseif ($alter_type == 'RENAME COLUMN') {
                 preg_match_all($fldren_pat, 
                                 (!empty($matches[3]) ? $matches[3] : $matches[4]), 
                                $fld_matches, PREG_SET_ORDER);
                 $tbl_fields = array();
                 foreach ($fld_matches as $id => $field) {
                     $tbl_fields[] = array('old' => $field[1], 'new' => $field[2]);
                 }
             }
             // used for rename column mode -> don't put it into foreach loop
             $t = $this->getTableInfo($tbl_name);
             foreach ($tbl_fields as $field) {
                 switch ($alter_type){
                     case 'ADD': 
                         if (!$this->tableAddField($tbl_name, 
                                                  $field['field_name'],
                                                  $field['field_type'],
                                                  $field['field_keys'],
                                                  $field['field_size'],
                                                  '0')) {
                             return false;
                         }
                         break;
                     case 'MODIFY': 
                         if (!$this->tableEditField($tbl_name, 
                                                   $field['field_name'],
                                                   $field['field_name'],
                                                     $field['field_type'],
                                                   $field['field_keys'],
                                                   $field['field_size'])) {
                            return false;
                        }
                         break;
                    case 'DROP':
                        if (!$this->tableDeleteField($tbl_name,
                                                    $field)) {
                            return false;
                        }
                        break;
                    case 'RENAME COLUMN':
                        $ff = array();
                        foreach ($t['fields'] as $f) {
                             var_dump($f);
                            var_dump($field);
                            if ($f['field_name'] == $field['old']) {
                                 echo 'BUH!';
                                $ff = $f;
                                break;
                            }
                        }
                        if (empty($ff)) {
                            return false;
                        }
                         if (!$this->tableEditField($tbl_name, 
                                                   $ff['field_name'],
                                                   $field['new'],
                                                   $ff['field_type'],
                                                   $ff['field_keys'],
                                                   $ff['field_size'])) {
                            return false;
                         }
                        break;
                     default:
                         return false;
                 }
            }
        }
        return true;
    }
 
    /**
     * Fetch a result array
     *
     * This will perform a FetchArray operation using the given data. If $res is
     * NULL, the internal {@link $_result} resource will be used. In any other
     * case $res will be treated as result resource. $type is the kind of array
     * that shoud be returned. Will return false if for whatever reason no result
     * row can be fetched.
     *
     * @param   resource|NULL $res   [optional, default=NULL] Result resource
     * @param   integer        $type  [optional, default=SQLITE_ASSOC] The type
     *                                 of the result row that should be delivered.
     *                                 See an SQLite manual for further information.
     * @return  boolean|array An array containing the current query result row,
     *                          false if fails.
     ***/
    public function fetchArray($res = NULL, $type = SQLITE_ASSOC)
    {
        if ($res == NULL) {
            if ($this->_result != NULL) {
                $res = $this->_result;
            } else {
                return false;
            }
        }
        if ($row = @sqlite_fetch_array($res, $type)) {
            return $row;
        }
        return false;
    }
 
    /**
     * Reset internal result resource
     *
     * This will reset the internal {@link $_result} resource, so that it can be
     * freely used again for further operations.
     **/
 
    public function resetResult()
    {
        $this->_result = NULL;
    }
 
    /**
     * Check if result exists
     *
     * This returns true if there is currently a valid {@link $_result} resource
     * that can be used for {@link fetchArray()} operations.
     *
     * @return   boolean  Whether or not there is a result.
     ***/
    public function hasResult()
    {
        return (bool) $this->_result;
    }
 
    /**
     * Get number of rows in current result set
     *
     * Returns the number of rows in the given result set. If $result is NULL,
     * the internal result resource link will be used.
     * 
     * @param   resoure|NULL $result  Result resource link
     * @return  integer       Number of rows in result set
     ***/
    public function numRows($result = NULL)
    {
        if ($result == NULL) {
            $result = $this->_result;
        }
        
        return @sqlite_num_rows($result);
    }
    
    /**
     * Last inserted row id
     *
     * Returns the row id created by the last insert instruction.
     *
     * @return  integer  Last inserted row id
     ***/
    public function lastInsertRowID()
    {
        return @sqlite_last_insert_rowid($this->_dblink);
    }
    
    /**
     * Performs a query using a format string and a list of arguments.
     * 
     * This will perform a query using the given format string and the list of
     * arguments passed. It works similar to the php {@link http://php.net/printf}
     * printf} function. If the first parameter after the format string is boolean
     * it will be taken as the $remember parameter (see {@link query()}) and the
     * other parameters as list. In any other case, all parameters after the format
     * string will be taken for the parameter list.
     * 
     * Example:
     * <pre>
     * // without 'remember':
     * $res = $cfg_sql->queryFormatted('SELECT * FROM %s WHERE 1', false, 'table');
     * $row = $cfg_sql->fetchArray($res);
     * 
     * // with 'remember':
     * $cfg_sql->queryFormatted('SELECT * FROM %s WHERE 1', 'table');
     * $row = $cfg_sql->fetchArray();
     * </pre>
     * 
     * @param   string   $format    Format string for query
     * @param   boolean  $remember  [optional, default=true] Whether or not store
     *                               result in internal result link
     * @param   mixed    $args      Argument list for format string
     * @return  boolean|resource   Result resource in case of SELECT queries, state
     *                               of success in any other case.
     ***/
    public function queryFormatted($format)
    {
        $args = func_get_args();
 
        array_shift($args); // 'kill' format string
        if (is_bool($args[0])) {
            $remember = $args[0];
            array_shift($args); // 'kill' $remember
        } else {
              $remember = true;
        }
        
        if (count($args) > 0) {
            $format = vsprintf($format, $args);
        }
        
        return $this->query($format, $remember);
    }
    
    /**
     * Get last error
     *
     * This will return an error message string of the last error that occured
     * during an SQLite operation. It will return an {@link http://php.net/sprintf}
     * sprintf}ed string (error number is first and message is second parameter).
     *
     * @param   string  $format  Message format string
     * @return  string  Error message
     ***/
    public function lastError($format = 'Error #%02d: "%s"')
    {
        return htmlspecialchars(sprintf($format, @sqlite_last_error($this->_dblink),
                            sqlite_error_string(@sqlite_last_error($this->_dblink))));
    }
 
    /**
     * Create new table
     *
     * This will try to create a new table with the name $tablename in the
     * currently opened database. It will return true if it succeeds, false if
     * fails. It will automatically create an 'id INTEGER PRIMARY KEY' field.
     *
     * @param   string  $tablename  Name of the table to create
     * @return  boolean State of success
     ***/
    public function tableCreate($tablename)
    {
        if (empty($tablename)) {
            return false;
        }
        if ($this->query("CREATE TABLE $tablename (id INTEGER PRIMARY KEY)",
                               false)) {
            return true;
        }
        return false;
    }
 
    /**
     * Delete table
     *
     * This will try to delete the table with the name $tablename in the
     * currently opened database. It will return true if it succeeds, false if
     * fails.
     *
     * @param   string  $tablename  Name of the table to delete
     * @return  boolean State of success
     ***/
    public function tableDelete($tablename)
    {
        if (empty($tablename)) {
            return false;
        }
        if ($this->query("DROP TABLE $tablename", false)) {
            return true;
        }
        return false;
    }
    
     /**
      * Check if table exists
      * 
      * Returns true, if the given table exists in currently opened database,
      * or false in any other case.
      * 
      * @param   string   $tablename  Name of table
      * @return  boolean  Whether (true) or not (false) $tablename exists.
      ***/
    public function hasTable($tablename)
    {
        $tables = $this->getTableInfo();
        foreach ($tables as $t) {
            if ($t['name'] == $tablename) {
                return true;
            }
        }
        return false;
    }
 
    /**
     * Get table information
     *
     * This will return an array of information of all tables in the currently
     * opened database. If $tablename is given the information will be limited
     * to this specific table.
     *
     * The returned array contains the following information. (Excerpt of one
     * element):
     *
     * <pre>
     * array {
     *     ["name"]    => Table name
     *     ["entries"] => Number of entries in that table
     *     ["fields"]  => Array containing field data
     *         array {
     *             [linear_index]=>
     *                 array {
     *                     ["field_name"] => Field name
     *                     ["field_type"] => Field type
     *                     ["field_keys"] => Field constraints
     *                     ["field_size"] => Field size
     *               }
     *         }
     * }
     * </pre>
     *
     * @param   string         $tablename [optional] Table name
     * @return  boolean|array Table information or false if fails
     ***/
    public function getTableInfo($tablename = '')
    {
        if (empty($tablename)) {
            $query_str = "SELECT * FROM sqlite_master WHERE type='table'";
        } else {
            $query_str = "SELECT * FROM sqlite_master WHERE
                          type='table' AND name='$tablename'";
        }
        $dbfile = $this->base64name;
        if ($query_res = $this->query($query_str, false)) {
            $table_data = Array();
 
            /* replace arrays to prepare sql table creation string */
            $pats = array('/[\r\n]{1,2}/', '/\s+/', '/\( /', '/ \)/', '/, /');
            $reps = array('',              ' ',     '(',     ')',     ',');
 
            /* prepare pregs for table data extraction */
            $tbl_pattern = '/CREATE TABLE (.*?)[ ]*\((.*)\)/i';
            $fname = '(\S+)';
            $ftype = '(?<=\W)(' . implode('|', $this->datatypeArray)
                   . ')(?=\W|$)(?: ?\(([^\)]+)\))?';
            $fconst = ' ?([^,]*)';
            $fld_pattern = sprintf('/%1$s %2$s(?:%3$s)?(?:,|$)/i', 
                                   $fname, $ftype, $fconst);
            $con_pattern = '/(?<=\W|^)(' . implode('|', $this->constraintsArray)
                         . ')(?=\W|$)/i';
                           
            while ($row = $this->fetchArray($query_res)) {
                $raw  = trim(preg_replace($pats, $reps, $row['sql']));
                if (preg_match_all($tbl_pattern, $raw, $matches, PREG_SET_ORDER)) {
                    $tbl_name    = $matches[0][1];
                    preg_match_all($fld_pattern, $matches[0][2], $fld_matches, 
                                   PREG_SET_ORDER);
                    $tbl_ofields = array();
                    foreach ($fld_matches as $id => $field) {
                        $fld = array();
                        $fld['field_name'] = trim($field[1], "'");
                        $fld['field_type'] = strtoupper($field[2]);
                        $fld['field_size'] = $field[3];
                        $fld['field_keys'] = array();
                        if (preg_match_all($con_pattern, $field[4], $con_matches)) {
                            $fld['field_keys'] = $con_matches[1];
                            foreach($fld['field_keys'] as $k => $v) {
                                $fld['field_keys'][$k] = strtoupper($v);
                            }
                        }
                        $tbl_ofields[] = $fld;
                    }
                    $cnt_res = $this->query('SELECT COUNT() FROM '
                                                  . $row['tbl_name'], false);
                    $cnt_row = $this->fetchArray($cnt_res);
 
                    $cur_table = array('name' => $row['tbl_name'],
                                       'fields' => $tbl_ofields);
 
                    $cur_table['entries']      = $cnt_row['COUNT()'];
                }
 
                if (!empty($tablename)) {
                    return $cur_table;
                }
                $table_data[] = $cur_table;
            }
        } else {
            return false;
        }
        return $table_data;
    }
 
    /**
     * Rename a table.
     *
     * This will rename $tblname to $tblnewname.
     *
     * <b>WARNING:</b> This public function should be considered instable. Since
     * there are no builtin public functions for table altering in SQLite, we have
     * to do a quite bad workaround by backing all data up, deleting the table and
     * recreating it, then putting all the data back. There has not yet been
     * very intensive testing as this is only a side project of {@link Crafty}.
     *
     * @param   string   $tblname  Name of target table
     * @param   string   $fldname  New name of target table
     * @return  boolean  State of success
     ***/
    public function tableRename($tblname, $newtblname)
    {
        $table_info = $this->getTableInfo($tblname);
        $this->message = '';
 
        $create_str = 'CREATE TABLE ' . $newtblname . "(";
        $comma = '';
        foreach($table_info['fields'] as $f) {
            $s = (!empty($f['field_size']))
                 ? '(' . $f['field_size'] . ')' : '';
            $create_str .= $comma . $f['field_name'] . ' '
                           . $f['field_type'] . $s . ' '
                           . implode(' ',$f['field_keys']) . "\n";
            $comma = ', ';
        }
        $create_str .= ')';
 
        $backup_queries = array();
        $query_str = 'SELECT * FROM ' . $table_info['name'];
        if ($query_res = $this->query($query_str, false)) {
            while ($row = $this->fetchArray($query_res)) {
                $comma  = '';
                $fields = '';
                $values = '';
                foreach ($row as $k => $v) {
                    $fields .= $comma . $k;
                    $values .= $comma . $this->getValidContent($v);
                    $comma   = ', ';
                }
                $backup_queries[] = 'INSERT INTO ' . $newtblname
                                    . " ($fields) VALUES ($values)";
            }
        }
 
        $query_del = 'DROP TABLE ' . $tblname;
        if ($this->query($query_del, false)) {
            if ($this->query($create_str, false)) {
                $this->message .= 'Recreated table.';
                foreach ($backup_queries as $bq) {
                    $bqh = htmlentities($bq);
                    if (!$this->query($bq, false)) {
                        $this->message .= "QUERY \"$bqh\" failed.<br />";
                    } else {
                        $this->message .= "QUERY \"$bqh\" succeeded.<br />";
                    }
                }
            } else {
                $this->message .= 'Could not recreate table.';
                return false;
            }
        } else {
            $this->message = 'ERROR: Could not delete table.';
            return false;
        }
        return true;
    }
 
    /**
     * Delete a field from a table.
     *
     * This will delete the field $fldname from the table $tblname.
     *
     * <b>WARNING:</b> This public function should be considered instable. Since
     * there are no builtin public functions for table altering in SQLite, we have
     * to do a quite bad workaround by backing all data up, deleting the table and
     * recreating it, then putting all the data back. There has not yet been
     * very intensive testing as this is only a side project of {@link Crafty}.
     *
     * @param   string   $tblname  Name of target table
     * @param   string   $fldname  Name of target field
     * @return  boolean  State of success
     ***/
    public function tableDeleteField($tblname, $fldname)
    {
        $table_info = $this->getTableInfo($tblname);
        $this->message = '';
 
        if (count($table_info['fields']) > 1) {
            $create_str = 'CREATE TABLE ' . $table_info['name'] . "(";
            $comma = '';
            foreach($table_info['fields'] as $f) {
                if ($f['field_name'] != $fldname) {
                    $s = (!empty($f['field_size']))
                         ? '(' . $f['field_size'] . ')' : '';
                    $create_str .= $comma . $f['field_name'] . ' '
                                   . $f['field_type'] . $s . ' '
                                   . implode(' ',$f['field_keys']) . "\n";
                    $comma = ', ';
                }
            }
            $create_str .= ')';
 
            $backup_queries = array();
            $query_str = 'SELECT * FROM ' . $table_info['name'];
            if ($query_res = $this->query($query_str, false)) {
                while ($row = $this->fetchArray($query_res)) {
                    $comma  = '';
                    $fields = '';
                    $values = '';
                    foreach ($row as $k => $v) {
                        if ($k != $fldname) {
                            $fields .= $comma . $k;
                            $values .= $comma . $this->getValidContent($v);
                            $comma   = ', ';
                        }
                    }
                    $backup_queries[] = 'INSERT INTO ' . $table_info['name']
                                        . " ($fields) VALUES ($values)";
                }
            }
 
            $query_del = 'DROP TABLE ' . $table_info['name'];
            if ($this->query($query_del, false)) {
                if ($this->query($create_str, false)) {
                    $this->message .= 'Recreated table.';
                    foreach ($backup_queries as $bq) {
                        $bqh = htmlentities($bq);
                        if (!$this->query($bq, false)) {
                            $this->message .= "QUERY \"$bqh\" failed.<br />";
                        } else {
                            $this->message .= "QUERY \"$bqh\" succeeded.<br />";
                        }
                    }
                } else {
                    $this->message .= 'Could not recreate table.';
                    return false;
                }
            } else {
                $this->message = 'ERROR: Could not delete table.';
                return false;
            }
        } else {
            $this->message = 'ERROR: Cannot delete last field. This is equal to
                              deleting the whole table.';
            return false;
        }
        return true;
    }
    
    /**
     * Add a field to a table.
     *
     * This will add the field $fldname to the table $tblname. Specifications of
     * the new field are passed. (See parameter list)
     *
     * <b>WARNING:</b> This public function should be considered instable. Since
     * there are no builtin public functions for table altering in SQLite, we have
     * to do a quite bad workaround by backing all data up, deleting the table and
     * recreating it, then putting all the data back. There has not yet been
     * very intensive testing as this is only a side project of {@link Crafty}.
     *
     * @param   string   $tblname     Name of target table
     * @param   string   $fldname     Name of new field
     * @param   string   $fldtype     Type of new field
     * @param   string   $fldconst    Constraints of new field
     * @param   string   $fldsize     Size of new field
     * @param   string   $flddefault  Default value of new field (for already
     *                                 existing database entries)
     * @return  boolean  State of success
     ***/
    public function tableAddField($tblname, $fldname, $fldtype, $fldconst,
                                 $fldsize, $flddefault)
    {
        if (empty($fldname)) {
            $this->message = 'Field name has to be set.';
            return false;
        }
        $table_info = $this->getTableInfo($tblname);
        $this->message = '';
        if (!empty($fldconst)) {
            $fc = (is_array($fldconst)) ? $fldconst : array($fldconst);
            $fc2 = array();
            foreach ($fc as $_fc) {
                if ($_fc[0] != '*') {
                    $fc2[] = $_fc;
                }
            }
            $fc = $fc2;
        } else {
            $fc = array();
        }
        $create_str = 'CREATE TABLE ' . $table_info['name'] . "(";
        $comma = '';
        $target_cnt = 0;
        foreach($table_info['fields'] as $f) {
            if ($f['field_name'] == $fldname) {
                $target_cnt++;
            }
            $s = (!empty($f['field_size'])) ? '(' . $f['field_size'] . ')' : '';
            $create_str .= $comma . $f['field_name'] . ' ' . $f['field_type']
                           . $s . ' ' . implode(' ',$f['field_keys']);
            $comma = ', ';
        }
        $s = (!empty($fldsize)) ? '(' . $fldsize . ')' : '';
        $create_str .= $comma . $fldname . ' ' . $fldtype . $s . ' '
                       . implode(' ',$fc);
        $create_str .= ')';
 
        if ($target_cnt > 0) {
            $this->message = 'ERROR: Field adding would create duplicate fields
                             in db.';
            return false;
        } else {
            $backup_queries = array();
            $query_str = 'SELECT * FROM ' . $table_info['name'];
            if ($query_res = $this->query($query_str, false)) {
                $fill = $this->getValidContent($flddefault);
                while ($row = $this->fetchArray($query_res)) {
                    $comma = '';
                    $fields = '';
                    $values = '';
                    foreach ($row as $k => $v) {
                        $fields .= $comma . $k;
                        $values .= $comma . $this->getValidContent($v);
                        $comma = ', ';
                    }
                    $fields .= $comma . $fldname;
                    $values .= $comma . $fill;
                    $backup_queries[] = 'INSERT INTO ' . $table_info['name']
                                        . " ($fields) VALUES ($values)";
                }
            }
 
            $query_del = 'DROP TABLE ' . $table_info['name'];
            if ($this->query($query_del, false)) {
                if ($this->query($create_str, false)) {
                    $this->message .= 'Recreated table.';
                    foreach ($backup_queries as $bq) {
                        $bqh = htmlentities($bq);
                        if (!$this->query($bq, false)) {
                            $this->message .= "QUERY \"$bqh\" failed.<br />";
                        } else {
                            $this->message .= "QUERY \"$bqh\" succeeded.<br />";
                        }
                    }
                } else {
                    $this->message .= 'Could not recreate table.';
                    return false;
                }
                return true;
            } else {
                $this->message = 'ERROR: Could not delete table.';
                return false;
            }
        }
    }
 
    /**
     * Alter a field of a table.
     *
     * This will alter the field $fldname in the table $tblname. Specifications
     * of the field are passed. (See parameter list)
     *
     * <b>WARNING:</b> This public function should be considered instable. Since
     * there are no builtin public functions for table altering in SQLite, we have
     * to do a quite bad workaround by backing all data up, deleting the table and
     * recreating it, then putting all the data back. There has not yet been
     * very intensive testing as this is only a side project of {@link Crafty}.
     *
     * @param   string   $tblname     Name of target table
     * @param   string   $fldoldname  Name of target field
     * @param   string   $fldname     New name of target field
     * @param   string   $fldtype     New type of target field
     * @param   string   $fldconst    New constraints of target field
     * @param   string   $fldsize     New size of target field
     * @return  boolean  State of success
     ***/
    public function tableEditField($tblname, $fldoldname, $fldname, $fldtype,
                                  $fldconst, $fldsize)
    {
        if (empty($fldname)) {
            $this->message = 'Field name has to be set.';
            return false;
        }
        $table_info = $this->getTableInfo($tblname);
        $this->message = '';
 
        if (!empty($fldconst)) {
            $fc = (is_array($fldconst)) ? $fldconst : array($fldconst);
            $fc2 = array();
            foreach ($fc as $_fc) {
                if ($_fc[0] != '*') {
                    $fc2[] = $_fc;
                }
            }
            $fc = $fc2;
        } else {
            $fc = array();
        }
        for($x = 0; $x < count($table_info['fields']); $x++) {
            if ($table_info['fields'][$x]['field_name'] == $fldoldname) {
                $new = array();
                $new['field_name'] = $fldname;
                $new['field_size'] = $fldsize;
                $new['field_keys'] = $fc;
                $new['field_type'] = $fldtype;
                $table_info['fields'][$x] = $new;
                break;
            }
        }
        $create_str = 'CREATE TABLE ' . $table_info['name'] . "(";
        $comma = '';
        $target_cnt = 0;
        foreach($table_info['fields'] as $f) {
            if ($f['field_name'] == $fldname) {
                $target_cnt++;
            }
            $s = (!empty($f['field_size'])) ? '(' . $f['field_size'] . ')' : '';
            $create_str .= $comma . $f['field_name'] . ' ' . $f['field_type']
                           . $s . ' ' . implode(' ',$f['field_keys']) . "\n";
            $comma = ', ';
        }
        $create_str .= ')';
 
        if ($target_cnt > 1) {
            $this->message = 'ERROR: Field editing would create duplicate fields
                             in db.';
            return false;
        } else {
            $backup_queries = array();
            $query_str = 'SELECT * FROM ' . $table_info['name'];
            if ($query_res = $this->query($query_str, false)) {
                while ($row = $this->fetchArray($query_res)) {
                    $comma = '';
                    $fields = '';
                    $values = '';
                    foreach ($row as $k => $v) {
                        if ($k == $fldoldname && $fldoldname != $fldname) {
                            $row[$fldname] = $v;
                            unset($row[$k]);
                            $k = $fldname;
                        }
                        $fields .= $comma . $k;
                        $values .= $comma . $this->getValidContent($v);
                        $comma = ', ';
                    }
                    $backup_queries[] = 'INSERT INTO ' . $table_info['name']
                                        . " ($fields) VALUES ($values)";
                }
            }
 
            $query_del = 'DROP TABLE ' . $table_info['name'];
            if ($this->query($query_del, false)) {
                if ($this->query($create_str, false)) {
                    $this->message .= 'Recreated table.';
                    foreach ($backup_queries as $bq) {
                        $bqh = htmlentities($bq);
                        if (!$this->query($bq, false)) {
                            $this->message .= "QUERY \"$bqh\" failed.<br />";
                            return false;
                        } else {
                            $this->message .= "QUERY \"$bqh\" succeeded.<br />";
                        }
                    }
                } else {
                    $this->message .= 'Could not recreate table.';
                    return false;
                }
                return true;
            } else {
                $this->message = 'ERROR: Could not delete table.';
                return false;
            }
        }
    }
 
    /**
     * Get VIEW information
     *
     * This will return an array of information of all VIEWs in the currently
     * opened database.
     * 
     * The returned array contains the following information. (Excerpt of one
     * element):
     *
     * <pre>
     * array {
     *     ["name"]      => VIEW name
     *     ["rootpage"]  => VIEW rootpage
     *     ["sql"]       => Complete SQL string that has been used for creating
     *                      the VIEW
     *     ["sql_short"] => The SELECT statement the VIEW represents
     *     ["table"]     => Name of the table the VIEW uses
     * }
     * </pre>
     *
     * @return  boolean|array VIEW information or false if fails
     ***/
    public function getViewInfo()
    {
        $query_str = "SELECT name, rootpage, sql FROM sqlite_master WHERE "
                   . "type='view'";
 
        if ($query_res = $this->query($query_str, false)) {
            $view_data = Array();
            while ($data = $this->fetchArray($query_res)) {
                if (preg_match('~from\s+(\'?)(.*?)(\\1)?\s+where~i', 
                               $data['sql'], $matches)) {
                    $data['table'] = $matches[2];
                } else {
                    $data['table'] = $data['name'];
                }
                if (preg_match('~(select.*?)$~i', $data['sql'], $matches)) {
                    $data['sql_short'] = $matches[1];
                } else {
                    $data['sql_short'] = $data['sql'];
                }
                $view_data[] = $data;
            }
            return $view_data;
        }
        return false;
    }
    
    /**
     * Create a new VIEW
     *
     * This will create a new VIEW named $name with the SELECT statement $sql
     * in the currently opened database.
     * 
     * @param   string   $name  VIEW name
     * @param   string   $sql   SELECT statement
     * @return  boolean  State of success
     ***/
    public function createView($name, $sql)
    {
       if ($this->query("CREATE VIEW $name AS $sql", false)) {
           return true;
       } else {
           return false;
       }
    }
 
    /**
     * Delete a VIEW
     *
     * This will delete the VIEW named $name in the currently opened database.
     *
     * @param   string   $name  VIEW name
     * @return  boolean  State of success
     ***/
    public function dropView($name)
    {
       if ($this->query("DROP VIEW $name", false)) {
           return true;
       } else {
           return false;
       }
    }
 
 
    /**
     * Get Index information
     *
     * This will return an array of information of all indices on the table
     * $tablename in the currently opened database.
     *
     * The returned array contains the following information. (Excerpt of one
     * element):
     *
     * <pre>
     * array {
     *     ["name"]           => Index name
     *     ["rootpage"]       => Index rootpage
     *     ["sql"]            => Complete SQL string that has been used for creating
     *                           the Index
     *     ["type"]           => Index type ('STANDARD' or 'UNIQUE')
     *     ["columns"]        => Linear array of names of affected columns
     *     ["columns_string"] => Comma-seperated list of names of affected columns
     * }
     * </pre>
     *
     * @param   string         $tablename  Table name
     * @return  boolean|array VIEW information or false if fails
     ***/
    public function getIndexInfo($tablename)
    {
        $query_str = "SELECT name, rootpage, sql FROM sqlite_master WHERE "
                   . "type='index' AND tbl_name='$tablename'";
 
        if ($query_res = $this->query($query_str, false)) {
            $index_data = Array();
            while ($data = $this->fetchArray($query_res)) {
                if (strpos(strtoupper($data['sql']), 'CREATE UNIQUE') === 0) {
                    $data['type'] = 'UNIQUE';
                } else {
                    $data['type'] = 'STANDARD';
                }
                if (preg_match('~\\(((?:(\'?).*?(\\2),?)+\\))~i',
                               $data['sql'], $matches)) {
                    if (preg_match_all('~(\'?)(.*?)(\\1)[,\\)]\s*~', $matches[1],
                                       $matches2)) {
                        $data['columns'] = $matches2[2];
                        $data['columns_string'] = implode(', ', $matches2[2]);
                    } else {
                        $data['columns'] = Array();
                        $data['columns_string'] = '';
                    }
                } else {
                    $data['columns'] = Array();
                    $data['columns_string'] = '';
                }
                $index_data[] = $data;
            }
            return $index_data;
        }
        return false;
    }
 
    /**
     * Create an Index
     *
     * This will create the Index named $name on the table $table in the currently
     * opened database. It will be of the type $type and affect the fields $fields.
     *
     * @param   string   $name   Index name
     * @param   string   $table  Table on which the index shall be created
     * @param   string   $type   Index type (STANDARD or UNIQUE)
     * @param   array    $fields Affected fields
     * @return  boolean  State of success
     ***/
    public function createIndex($name, $type, $table, $fields)
    {
       $fields = implode(', ', $fields);
       $type   = ($type == 'UNIQUE') ? ' UNIQUE' : '';
       if ($this->query("CREATE$type INDEX $name ON $table ($fields)", false)) {
           return true;
       } else {
           return false;
       }
    }
 
    /**
     * Delete an Index
     *
     * This will delete the Index named $name in the currently opened database.
     *
     * @param   string   $name  Index name
     * @return  boolean  State of success
     ***/
    public function dropIndex($name)
    {
       if ($this->query("DROP INDEX $name", false)) {
           return true;
       } else {
           return false;
       }
    }
 
    /**
     * Get a valid content string
     *
     * Returns a valid content string created from given content string. Some
     * strings are key contents (like NULL) in SQLite. They must not be set in
     * single quotes. All others have to. That is what this public function takes
     * care of. Mostly for internal use.
     *
     * @param   string  $cont  Given content string
     * @return  string  Valid content string
     ***/
    public function getValidContent($cont)
    {
        if (empty($cont) && $cont !== "0") {
            return 'NULL';
        }
        if (preg_match('~^(NULL)$~i', $cont)) {
            return $cont;
        } else {
            return "'$cont'";
        }
    }
    /**#@-*/
}
 
?>