Include File for Building Schema

<?php

/**
 * Functions for creating and destroying a database schema's contents
 *
 * @package    Portability
 * @author     Daniel Convissor <danielc@analysisandsolutions.com>
 * @copyright  2002-2005 The Analysis and Solutions Company 
 * @license    http://www.analysisandsolutions.com/software/license.txt  Simple Public License
 * @link       http://www.analysisandsolutions.com/presentations/portability/
 * @see        buildSchema.php, viewSchema.php
 */

/**
 * Creates the tables and inserts the initial data
 *
 * Goes through each file in the <kbd>./schema</kbd> directory,
 * extracting the <kbd>CREATE TABLE</kbd> query therein, perform the
 * column type searches/replaces necessary for the current DBMS type
 * and then execute the query.
 *
 * Then pull the queries from each file in the <kbd>./data</kbd>
 * directory and execute those.
 *
 * @param object $p   the portability object
 * @param object $db  the PEAR::DB object
 *
 * @return void
 */
function buildSchema($p, $db) {
    set_time_limit(0);
    $dir  = dirname(__FILE__);
    $warn = 0;  // Take note of attempts to create something that exists.

    if (!is_dir("$dir/schema")) {
        echo "Could not read the $dir/schema directory.\n"
             . "Creation process terminated.\n";
        exit;
    }

    if (!is_dir("$dir/data")) {
        echo "Could not read the $dir/data directory.\n"
             . "Creation process terminated.\n";
        exit;
    }

    /*
     * preg search and replace pairs for create table statements
     */
    $search_create = array(
        '/REPLSQL-boolean/',
        '/REPLSQL-clob/',
        '/REPLSQL-dateliteral/',
        '/REPLSQL-date/',
        '/REPLSQL-decimal(\(\d+, *\d+\))/',
        '/REPLSQL-null/',
        '/REPLSQL-timestampliteral/',
        '/REPLSQL-timestamp/',
    );

    $replace_create = array(
        $p->getBooleanType(),
        $p->getClobType(),
        $p->getDateLiteralType(),
        $p->getDateType(),
        $p->getDecimalType(),
        $p->getNullKeyword(),
        $p->getTimestampLiteralType(),
        $p->getTimestampType(),
    );

    /*
     * preg search and replace pairs for data files
     */
    $search_data = array(
        '/REPLSQL-dateliteral/',
        '/REPLSQL-false/',
        '/REPLSQL-true/',
        '/REPLSQL-timestampliteral/',
    );

    $replace_data = array(
        $p->getDateLiteralType(),
        $db->quoteSmart(false),
        $db->quoteSmart(true),
        $p->getTimestampLiteralType(),
    );

    /*
     * Process each schema file
     */
    $dh = opendir("$dir/schema");
    while ($file = readdir($dh)) {
        if (substr($file, -3) == 'sql') {
            $new_table   = true;
            $schema_file = "$dir/schema/$file";
            $fh          = fopen($schema_file, 'r');
            $contents    = '';
            while (!feof($fh)) {
                $line = fgets($fh, 5000);
                if (substr($line, 0, 2) != '--') {
                    $contents .= $line;
                }
            }
            $contents    = preg_replace($search_create, $replace_create,
                                        $contents);
            $queries     = preg_split('/;\s*$/m', $contents);

            foreach ($queries as $query) {
                if (trim($query) == '') {
                    continue;
                }
                $res =& $db->query($query);
                if (DB::isError($res)) {
                    switch ($res->getCode()) {
                        case DB_ERROR_ALREADY_EXISTS:
                            echo "TABLE OR INDEX ALREADY EXISTS: $file\n";
                            $warn++;
                            $new_table = false;
                            break;
                        default:
                            echo "---------\nCREATION ERROR in $file\n\n"
                                 . $res->getDebugInfo()
                                 . "\n---------\nCreation process has been"
                                 . " terminated before completion!\n";
                            exit;
                    }
                }
            }
            if ($new_table) {
                echo "Executed create: $file\n";
            }

            /*
             * Process the data file
             */
            $data_file = "$dir/data/$file";
            if (file_exists($data_file) && $new_table) {
                $fh = fopen($data_file, 'r');

                while ($line = fgets($fh, 50000)) {
                    if (!preg_match('/^(\s*--|[\r\n]+)/', $line)) {
                        $line = preg_replace('/; *[\r\n]*$/', '', $line);
                        $line = preg_replace($search_data, $replace_data, $line);

                        switch ($db->phptype) {
                            case 'mysql':
                            case 'mysqli':
                            case 'pgsql':
                                $line = str_replace('\\', '\\\\', $line);
                                break;
                        }

                        $res =& $db->query($line);
                        if (DB::isError($res)) {
                            echo "---------\nDATA ERROR in $file\n\n"
                                 . $res->getDebugInfo()
                                 . "\n---------\nCreation process has been"
                                 . " terminated before completion!\n";
                            exit;
                        }
                    }
                }
                echo "Executed data:   $file\n";
            }
        }
    }

    if ($warn) {
        echo "\nCompleted table creation.\n\n";
        echo "$warn tables or indexes already existed.\n";
        echo "They were NOT overwritten.\n\n";
    } else {
        echo "\nSucessfully completed table creation.\n\n";
    }
}

/**
 * Drops the tables
 *
 * Goes through the list of files in the <kbd>./schema</kbd> directory
 * and drops each table listed there.  This works by using the file
 * names, so don't put multiple create table statements in a schema file.
 *
 * This is a simplistic routine, so it doesn't directly drop indexes.
 * So, if the DBMS doesn't drop indexes automatically when the table they
 * relate to are dropped, you'll have to do that manually.
 *
 * @param object $p   the portability object
 * @param object $db  the PEAR::DB object
 *
 * @return void
 */
function dropSchema($p, $db) {
    set_time_limit(0);
    $dir  = dirname(__FILE__);
    $warn = 0;  // Take note of attempts to drop things that don't exist.

    if (!is_dir("$dir/schema")) {
        echo "Could not read the $dir/schema directory.\n"
             . "Drop process terminated.\n";
        exit;
    }

    $dh = opendir("$dir/schema");
    while ($file = readdir($dh)) {
        if (substr($file, -3) == 'sql') {
            $table = basename($file, '.sql');
            $res =& $db->query("DROP TABLE $table " . $p->getDropRestrict());
            if (DB::isError($res)) {
                switch ($res->getCode()) {
                    case DB_ERROR_NOSUCHTABLE:
                    case DB_ERROR_NOT_FOUND:
                        echo "TABLE DIDN'T EXIST: $table\n";
                        break;
                    default:
                        echo "-----\nCOULDN'T DELETE: $table\n";
                        echo $res->getDebugInfo();
                        echo "-----\n\n";
                }
                $warn++;
                continue;
            }
            echo 'Dropped: ' . $table . "\n";
        }
    }

    if ($warn) {
        echo "\n$warn tables or indexes could not be deleted.\n\n";
    } else {
        echo "\nSucessfully completed table drops.\n\n";
    }
}