<?php
// $Id: pnTableDDL.php,v 1.2 2002/10/07 15:13:59 larsneo Exp $
// ----------------------------------------------------------------------
// PostNuke Content Management System
// Copyright (C) 2001 by the PostNuke Development Team.
// http://www.postnuke.com/
// ----------------------------------------------------------------------
// LICENSE
//
// This program is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License (GPL)
// as published by the Free Software Foundation; either version 2
// of the License, or (at your option) any later version.
//
// 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.  See the
// GNU General Public License for more details.
//
// To read the license please visit http://www.gnu.org/copyleft/gpl.html
// ----------------------------------------------------------------------
// Original Author of file: Gary Mitchell
// Purpose of file: Table Maintenance API
// ----------------------------------------------------------------------

/*
 * Public Functions:
 *   
 * pnDBCreateTable ( $table, $fields, <$db_type> )
 * pnDBCreateIndex ( $table, $index, <$db_type> )
 * pnDBDropTable ( $table, <$db_type> )
 * 
 */

/*
$sql[] = pnDBAlterTable(
    array(
        'table'             => $pntable['nascar_tracks'],
        'command'           => 'add',
        'field_name'        => 'pn_track_name',
        'new_field_name'    => 'pn_track_name1'
        'type'              => 'integer',
        'null'              => FALSE,
        'increment'         => TRUE,
        'primary_key'       => TRUE,
    )
);  */

function pnDBAlterTable($args, $dbtype="") {

    // perform validations on input arguments
    if (empty($args)) return FALSE;
    if (!is_array($args)) return FALSE;
    if (empty($args['table']) || (empty($args['command']))) return FALSE;
       
    if (empty($dbtype)) {
        $dbtype = pnConfigGetVar('dbtype');
    }
    // Select the correct database type    
    switch($dbtype) {
        case 'mysql':
            $sql = pnMySQLAlterTable($args);
            break;
        // Other DBs go here
        default:
            $sql = FALSE;
    }
    return $sql;    
}

function pnMySQLAlterTable($args) {

    switch ($args['command']) {
        case 'add':
            if (empty($args['field'])) return FALSE;
            $sql = 'ALTER TABLE '.$args['table'].' ADD ';
            $sql .= join(' ',pnMySQLColumnDefinition ($args['field'],$args));
            if ($args['first'] == TRUE) {
                $sql .= ' FIRST';
            } elseif (!empty($args['after_field'])) {
                $sql .= ' AFTER '.$args['after_field'];
            }
            break;
        case 'modify':
            if (empty($args['field'])) return FALSE;
            $sql = 'ALTER TABLE '.$args['table'].' CHANGE ';
            $sql .= join(' ',pnMySQLColumnDefinition ($args['field'],$args));
            break;
        case 'drop':
            if (empty($args['field'])) return FALSE;
            $sql = 'ALTER TABLE '.$args['table'].' DROP COLUMN '.$args['field'];
            break;
        case 'rename':
            if (empty($args['new_name'])) return FALSE;
            $sql = 'ALTER TABLE '.$args['table'].' RENAME TO '.$args['new_name'];
            break;
        default:
            $sql = FALSE;
    }
    return $sql;
} 

/**
 * generate the SQL to create a table
 * @param table the physical table name 
 * @param fields an array containing the fields to create
 * @returns data|false
 * @return the generated SQL statement, or false on failure
 */
function pnDBCreateTable($table, $fields, $dbtype="") {

    // perform validations on input arguments
    if (empty($table)) return FALSE;
    if (!is_array($fields)) return FALSE;
       
    if (empty($dbtype)) {
        $dbtype = pnConfigGetVar('dbtype');
    }
    // Select the correct database type    
    switch($dbtype) {
        case 'mysql':
            $sql_statement = pnMySQLCreateTable($table,$fields);
            break;
        case 'postgresql':
            $sql_statement = pnPostgreSQLCreateTable($table,$fields);        
            break;       
        // Other DBs go here
        default:
            $sql_statement = FALSE;
    }
    return $sql_statement;    
}



/**
 * generate the SQL to delete a table
 * @param table the physical table name 
 * @param index an array containing the index name, type and fields array
 * @returns data|false
 * @return the generated SQL statement, or false on failure
 */
function pnDBDropTable( $table, $dbtype="" ) {

    // perform validations on input arguments
    if (empty($table)) return FALSE;
    
    if (empty($dbtype)) {
        $dbtype = pnConfigGetVar('dbtype');
    }

    switch($dbtype) {
        case 'mysql':
            $sql = 'DROP TABLE '.$table;
            break;
        // Other DBs go here
        default:
            $sql = FALSE;
    }
    return $sql;    

}
 
/**
 * generate the SQL to create a table index
 * @param table the physical table name 
 * @param index an array containing the index name, type and fields array
 * @param dbtype is an optional parameter to specify the database type
 * @returns data|false
 * @return the generated SQL statement, or false on failure
 */
function pnDBCreateIndex( $table, $index, $dbtype="") {

    // perform validations on input arguments
    if (empty($table)) return FALSE;
    if (!is_array($index)) return FALSE;
    if (!is_array($index['fields'])) return FALSE;
    if (empty($index['name']) || empty($index['fields'])) return FALSE;
    
    if (empty($dbtype)) {
        $dbtype = pnConfigGetVar('dbtype');
    }
    // Select the correct database type    
    switch($dbtype) {

        case 'mysql':

            if ($index['unique'] == TRUE) {
                $sql .= 'CREATE UNIQUE INDEX '.$index['name'].' ON '.$table;
            } else {
                $sql .= 'CREATE INDEX '.$index['name'].' ON '.$table;
            }
            $sql .= ' ('.join(',',$index['fields']).')';
            break;

        // Other DBs go here
        default:
            $sql = FALSE;
    }
    return $sql;    
}
 
function pnDBDropIndex($table,$fields) {
}

// PRIVATE FUNCTIONS BELOW - do not call directly

/**
 * Private Function to generate the MySQL to create a table
 * @param table the physical table name 
 * @param fields an array containing the fields to create
 * @returns data|false
 * @return the generated SQL statement, or false on failure
 */
function pnMySQLCreateTable($table, $fields) {
    $sql_fields = array();

    while (list($field_name,$parameters)=each($fields)) {
        $parameters['command'] = 'create';
        $this_field = pnMySQLColumnDefinition( $field_name, $parameters );
        $sql_fields[] = implode(' ',$this_field);
    }
    $sql_statement = 'CREATE TABLE '.$table.' ('.implode(',',$sql_fields).')';
    return $sql_statement;
}

function pnMySQLColumnDefinition ($field_name,$parameters) {
    $this_field = array($field_name);

    if ($parameters['command']=='modify') {
        if (empty($parameters['new_field_name'])) {
            $this_field[] = $field_name;                    //use same name
        } else {
            $this_field[] = $parameters['new_field_name'];  // use new name
        }
    }
    switch($parameters['type']) {
        case 'integer':
            switch ($parameters['size']) {
                case 'tiny':
                    $this_field[] = 'TINYINT';
                    break;
                case 'small':
                    $this_field[] = 'SMALLINT';
                    break;
                case 'medium':
                    $this_field[] = 'MEDIUMINT';
                    break;
                case 'big':
                    $this_field[] = 'BIGINT';
                    break;
                default:
                    $this_field[] = 'INTEGER';
            } // switch ($parameters['size']) 
            break;
        case 'char':
            if (empty($parameters['size'])) {
                return FALSE;
            } else {
                $this_field[] = 'CHAR('.$parameters['size'].')';
            }
            break;   
        case 'varchar':
            if (empty($parameters['size'])) {
                return FALSE;
            } else {
                $this_field[] = 'VARCHAR('.$parameters['size'].')';
            }
            break;   
        case 'text':
                switch ($parameters['size']) {
                    case 'tiny':
                        $this_field[] = 'TINYTEXT';
                        break;
                    case 'medium':
                        $this_field[] = 'MEDIUMTEXT';
                        break;
                    case 'long':
                        $this_field[] = 'LONGTEXT';
                        break;
                    default:
                        $this_field[] = 'TEXT';
                }
                break;                                    
            case 'blob':
                switch ($parameters['size']) {
                    case 'tiny':
                        $this_field[] = 'TINYBLOB';
                        break;
                    case 'medium':
                        $this_field[] = 'MEDIUMBLOB';
                        break;
                    case 'long':
                        $this_field[] = 'LONGBLOB';
                        break;
                    default:
                        $this_field[] = 'BLOB';
                }
                break;
            case 'boolean':
                $this_field[] = "BOOL";            
                break;   
            case 'datetime':
                $this_field[] = "DATETIME";
                // convert parameter array back to string for datetime
                // array('year'=>2002,'month'=>04,'day'=>17,'hour'=>'12','minute'=>59,'second'=>0)
                if (isset($parameters['default'])) {
                    $datetime_defaults = $parameters['default'];
                    $parameters['default'] = $datetime_defaults['year']
                    .'-'.$datetime_defaults['month']
                    .'-'.$datetime_defaults['day']
                    .' '.$datetime_defaults['hour']
                    .':'.$datetime_defaults['minute']
                    .':'.$datetime_defaults['second'];                                                                                
                }
                break;                                 
            case 'date':
                $this_field[] = "DATE";
                // convert parameter array back to string for datetime
                // array('year'=>2002,'month'=>04,'day'=>17)
                if (isset($parameters['default'])) {
                    $datetime_defaults = $parameters['default'];
                    $parameters['default'] = $datetime_defaults['year']
                    .'-'.$datetime_defaults['month']
                    .'-'.$datetime_defaults['day'];
                }
                break;                                 
//        'pn_f001'=>array('type'=>'float', 'width'=>6,'decimals'=>2),
//        'pn_f002'=>array('type'=>'float', 'size'=>'double','width'=>12, 'decimals'=>2),
//        'pn_f003'=>array('type'=>'float', 'size'=>'decimal','width'=>12, 'decimals'=>2)  
            case 'float':
                switch ($parameters['size']) {
                    case 'double':
                        $data_type = 'DOUBLE';
                        break;
                    case 'decimal':
                        $data_type = 'DECIMAL';
                        break;
                    default:
                        $data_type = 'FLOAT';
                }
                if (isset($parameters['width']) && isset($parameters['decimals'])) {
                    $data_type .= '('.$parameters['width'].','.$parameters['width'].')';
                }
                $this_field[] = $data_type;                
                break;
            case 'timestamp':
                switch ($parameters['size']) {
                    case 'YY':
                        $this_field[] = 'TIMESTAMP(2)';
                        break;
                    case 'YYYY':
                        $this_field[] = 'TIMESTAMP(4)';
                        break;
                    case 'YYYYMM':                                    
                        $this_field[] = 'TIMESTAMP(6)';
                        break;
                    case 'YYYYMMDD':                                    
                        $this_field[] = 'TIMESTAMP(8)';
                        break;
                    case 'YYYYMMDDHH':                                    
                        $this_field[] = 'TIMESTAMP(10)';
                        break;
                    case 'YYYYMMDDHHMM':                                    
                        $this_field[] = 'TIMESTAMP(12)';
                        break;
                    case 'YYYYMMDDHHMMSS':
                        $this_field[] = 'TIMESTAMP(14)';
                        break;
                    default:                                    
                        $this_field[] = 'TIMESTAMP';
                }
                break;
            // undefined type
            default:
                return FALSE;
        }

        // Test for UNSIGNED
        if (isset($parameters['unsigned']) && $parameters['unsigned']==TRUE) {
            $this_field[] = "UNSIGNED";
        } 

        // Test for NO NULLS
        if (isset($parameters['null']) && $parameters['null']==FALSE) {
            $this_field[] = "NOT NULL";
        } 

        // Test for defaults
        if (isset($parameters['default'])) {
            if ($parameters['default']=='NULL') {
                $this_field[] = "DEFAULT NULL";
            } else {
                $this_field[] = "DEFAULT '".$parameters['default']."'";
            }
        } 
                
        // Test for AUTO_INCREMENT
        if (isset($parameters['increment']) && $parameters['increment']==TRUE) {
            $this_field[] = "AUTO_INCREMENT";
        } 
                
        // Test for PRIMARY KEY
        if (isset($parameters['primary_key']) && $parameters['primary_key']==TRUE) {
            $this_field[] = "PRIMARY KEY";
        } 
    return $this_field;
}

/**
 * Private Function to generate the PostgreSQL to create a table
 * @param table the physical table name 
 * @param fields an array containing the fields to create
 * @returns data|false
 * @return the generated SQL statement, or false on failure
 */
function pnPostgreSQLCreateTable($table, $fields) {
    $sql_fields = array();

    while (list($field_name,$parameters)=each($fields)) {

        $this_field = array($field_name);

        switch($parameters['type']) {
            case 'integer':
                if (isset($parameters['increment']) && $parameters['increment']==TRUE) {
                    switch ($parameters['size']) {
                        case 'big':
                            $this_field[] = 'BIGSERIAL';
                            break;
                        default:
                            $this_field[] = 'SERIAL';
                    }
                } else {
                    switch ($parameters['size']) {
                        case 'tiny':
                            $this_field[] = 'SMALLINT';
                            break;
                        case 'small':
                            $this_field[] = 'SMALLINT';
                            break;
                        case 'big':
                            $this_field[] = 'BIGINT';
                            break;
                        default:
                            $this_field[] = 'INTEGER';
                    }
                } // switch ($parameters['size']) 
                break;
            case 'char':
                if (empty($parameters['size'])) {
                    return FALSE;
                } else {
                    $this_field[] = 'CHAR('.$parameters['size'].')';
                }
                break;   
            case 'varchar':
                if (empty($parameters['size'])) {
                    return FALSE;
                } else {
                    $this_field[] = 'VARCHAR('.$parameters['size'].')';
                }
                break;   
            case 'text':
                $this_field[] = 'TEXT';
                break;                                    
            case 'blob':
                $this_field[] = 'BYTEA';
                break;
            case 'boolean':
                $this_field[] = "BOOLEAN";            
                break;   
            case 'timestamp':
            case 'datetime':
                $this_field[] = "TIMESTAMP";
                // convert parameter array back to string for datetime
                // array('year'=>2002,'month'=>04,'day'=>17,'hour'=>'12','minute'=>59,'second'=>0)
                if (isset($parameters['default'])) {
                    $datetime_defaults = $parameters['default'];
                    $parameters['default'] = $datetime_defaults['year']
                    .'-'.$datetime_defaults['month']
                    .'-'.$datetime_defaults['day']
                    .' '.$datetime_defaults['hour']
                    .':'.$datetime_defaults['minute']
                    .':'.$datetime_defaults['second'];
                    if (isset($datetime_defaults['timezone'])) {  // optional parm
                        $parameters['default'] = $datetime_defaults['timezone'];
                    }
                }
                break;                                 
            case 'date':
                $this_field[] = "DATE";
                // convert parameter array back to string for datetime
                // array('year'=>2002,'month'=>04,'day'=>17)
                if (isset($parameters['default'])) {
                    $datetime_defaults = $parameters['default'];
                    $parameters['default'] = $datetime_defaults['year']
                    .'-'.$datetime_defaults['month']
                    .'-'.$datetime_defaults['day'];
                }
                break;                                 
//        'pn_f001'=>array('type'=>'float', 'width'=>6,'decimals'=>2),
//        'pn_f002'=>array('type'=>'float', 'size'=>'double','width'=>12, 'decimals'=>2),
//        'pn_f003'=>array('type'=>'float', 'size'=>'decimal','width'=>12, 'decimals'=>2)  
            case 'float':
                switch ($parameters['size']) {
                    case 'double':
                        $data_type = 'DOUBLE PRECISION';
                        break;
                    case 'decimal':
                        $data_type = 'DECIMAL';
                        break;
                    default:
                        $data_type = 'REAL';
                }
                if (isset($parameters['width']) && isset($parameters['decimals'])) {
                    $data_type .= '('.$parameters['width'].','.$parameters['width'].')';
                }
                $this_field[] = $data_type;                
                break;

            // undefined type
            default:
                return FALSE;
        }

        // Test for defaults - must come after datatype for PostgreSQL
        if (isset($parameters['default'])) {
            if ($parameters['default']=='NULL') {
                $this_field[] = "DEFAULT NULL";
            } else {
                $this_field[] = "DEFAULT '".$parameters['default']."'";
            }
        } 
        
        // Test for UNSIGNED
        if (isset($parameters['unsigned']) && $parameters['unsigned']==TRUE) {
            $this_field[] = "UNSIGNED";
        } 

        // Test for NO NULLS
        if (isset($parameters['null']) && $parameters['null']==FALSE) {
            $this_field[] = "NOT NULL";
        } 

        // Test for PRIMARY KEY
        if (isset($parameters['primary_key']) && $parameters['primary_key']==TRUE) {
            $this_field[] = "PRIMARY KEY";
        } 

        $sql_fields[] = implode(' ',$this_field);
    }
    $sql_statement = 'CREATE TABLE '.$table.' ('.implode(',',$sql_fields).')';
    return $sql_statement;
}

?>