# ==================================================================
# DBMan SQL - enhanced database management system
#
#   Website  : http://gossamer-threads.com/
#   Support  : http://gossamer-threads.com/scripts/support/
#   CVS Info :                     
#   Revision : $Id: SQL.pm,v 1.32 2001/10/24 16:31:24 bao Exp $
# 
# Copyright (c) 2001 Gossamer Threads Inc.  All Rights Reserved.
# Redistribution in part or in whole strictly prohibited. Please
# see LICENSE file for full details.
# ==================================================================

package Dbsql::SQL;

use strict;
use vars  qw/@TABLES/;
use Dbsql qw/$DB $CFG/;

@TABLES = qw/Dbsql DbsqlList Sample Sample_Users Sample_Users_Sessions Sample_Log Dbsql_EmailTemplates Dbsql_EmailMailings Dbsql_MailingList Dbsql_MailingIndex Dbsql_MailingListIndex image_gallery homes/;

sub tables {
# ------------------------------------------------------------------
# Defines the SQL tables.
#
    my $action = shift || 'warn';
    my $output = '';
# --------- Dbsql Table ----------------
    $output .= "Creating Dbsql table .. ";
    my $c = $DB->creator('Dbsql');
    $c->cols (
            Tablename               => { pos => 1, type => 'CHAR', size => 70, not_null => 1 },
            auth_no_authentication  => { pos => 2, type => 'ENUM', values => ['0', '1'],default => '0'},
            auth_allow_default      => { pos => 3, type => 'CHAR', size => 9},
            auth_signup             => { pos => 4, type => 'ENUM', values => ['0', '1'],default => '0'},
            auth_signup_permissions => { pos => 5, type => 'CHAR', size => 9},
            auth_view_own           => { pos => 6, type => 'ENUM', values => ['0', '1'],default => '0'},
            auth_modify_own         => { pos => 7, type => 'ENUM', values => ['0', '1'],default => '0'},
            auth_user_field         => { pos => 8, type => 'CHAR', size => 50},
            auth_logging            => { pos => 9, type => 'CHAR', size => 255},
            log_file                => { pos => 10,type => 'CHAR', size => 70},
            template                => { pos => 11,type => 'CHAR', size => 50},
            user_table_use          => { pos => 12,type => 'CHAR', size => 70},
            Type                    => { pos => 2, type => 'ENUM', values => ['0', '1','2'],default => '2'},
        );
    $c->pk('Tablename');

    if ($c->create($action)) { 
        $output .= "ok\n"; 
    }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n"); 
        $c->set_defaults();
        $c->save_schema();
    }

# --------- Sample Table ----------------
    $output .= "Creating Sample table .. ";
    $c = $DB->creator('Sample');
    $c->cols (
        item        => { pos => 1, type => 'CHAR', size => 75, not_null => 1},
        price       => { pos => 2, type => 'CHAR', binary => 1, size => 25, not_null => 1  },
        quantity    => { pos => 3, type => 'CHAR', binary => 1, size => 25  },
        category    => { pos => 4, type => 'ENUM', values => ['---', 'Automobiles', 'Building Supplies','Clothing','Other'], not_null => 1, default => '---' },
        description => { pos => 5, type => 'TEXT', form_type => 'TEXTAREA'},
        color       => { pos => 6, type => 'CHAR', size => 100, form_values => ['Red','White','Black'], form_type => 'CHECKBOX',form_names => ['1','2','3']},
        picture     => { pos => 7, type => 'CHAR', size => 255, form_type => 'FILE', file_save_in => $CFG->{admin_root_path}.'/tmp'}
        );
    
    $c->pk('item');
    if   ($c->create($action)) { $output .= "ok\n"; }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n"); 
        $c->set_defaults();
        $c->save_schema();
    }
# --------- Sample_Files Table ----------------
    $output .= "Creating Sample_Files table .. ";
    $c = $DB->creator('Sample_Files');
    $c->cols(ID             => {pos => 1, type => 'INT' , not_null => 1, unsigned => 1,regex => '^\d+$'},
             ForeignColName => {pos => 2, type => 'CHAR', size     => 50},
             ForeignColKey  => {pos => 3, type => 'CHAR', size     => 50},
             File_Name      => {pos => 4, type => 'CHAR', size     => 255},
             File_Directory => {pos => 5, type => 'CHAR', size     => 255},
             File_MimeType  => {pos => 6, type => 'CHAR', size     => 50},
             File_Size      => {pos => 7, type => 'INT' , not_null => 1}); 
    $c->pk('ID');
    $c->ai('ID');
    if   ($c->create($action)) { $output .= "ok\n"; }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n"); 
        $c->set_defaults();
        $c->save_schema();
    }

# --------- Demo Table ----------------
    $output .= "Creating image_gallery table .. ";
    $c  = $DB->creator('image_gallery');
    $c->clear_schema() if ($action eq 'force');
    $c->cols ( 
            ID              => { pos => 1, type => 'INT', not_null => 1},
            Title           => { pos => 2, type => 'CHAR', size => 100, not_null => 0},
            URL             => { pos => 3, type => 'CHAR', size => 255, not_null => 0, default => 'http://'},
            Description     => { pos => 4, type => 'TEXT'},
            Hits            => { pos => 5, type => 'INT', not_null => 0, default => 0},
            isNew           => { pos => 6, type =>  'ENUM', values => ['No', 'Yes'], not_null => 0, default => 'No' },
            isChanged       => { pos => 7, type => 'ENUM', values => ['No', 'Yes'], not_null => 0, default => 'No' },
            isPopular       => { pos => 8, type => 'ENUM', values => ['No', 'Yes'], not_null => 0, default => 'No' },
            isValidated     => { pos => 9, type => 'ENUM', values => ['No', 'Yes'], not_null => 0, default => 'Yes' },
            Status          => { pos => 10, type => 'SMALLINT', not_null => 0, default => 0},
            Date_Checked    => { pos => 11, type => 'DATETIME', default => '' },
            Timestmp        => { pos => 12, type => 'TIMESTAMP', time_check => 0 },
            TCodeIn         => { pos => 13, type => 'CHAR', size => 255 },
            TCodeOut        => { pos => 14, type => 'CHAR', size => 255 },
            Length          => { pos => 15, type => 'CHAR', size => 255 },
            Keywords        => { pos => 16, type => 'CHAR', size => 255 },
            Format          => { pos => 17, type => 'CHAR', size => 255 },
            Speed           => { pos => 18, type => 'CHAR', size => 255 },
            FPS             => { pos => 19, type => 'CHAR', size => 255 },
            Notes           => { pos => 20, type => 'CHAR', size => 255 },
            Photo           => { pos => 21, type =>  'ENUM', values => ['No', 'Yes'], not_null => 0, default => 'No' },
        );
    $c->pk('ID');
    $c->ai('ID');
    if   ($c->create($action)) { $output .= "ok\n"; }
    else { 
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n"); 
        $c->set_defaults();
        $c->save_schema();
    }


# --------- Demo2 Table ----------------
    $output .= "Creating homes table .. ";
    $c = $DB->creator('homes');
    $c->cols (

        ListID		=> { pos => 0, type => 'INT',   not_null => 1, regex => '^\d+$' },
    	ListType	=> { pos => 1, type => 'INT'   },
    	Acres		=> { pos => 2, type => 'CHAR', size => 15},
    	SqFtApprox	=> { pos => 3, type => 'INT',  not_null => 1, regex => '^\d+$' },
    	Bldr		=> { pos => 4, type => 'CHAR', size => 15},
    	BdrmQT		=> { pos => 5, type => 'INT',  not_null => 1, regex => '^\d+$' },
    	BthQT		=> { pos => 6, type => 'CHAR', size => 15},
    	CitySV		=> { pos => 7, type => 'CHAR', },
    	CountySV	=> { pos => 8, type => 'CHAR', },
    	District	=> { pos => 9, type => 'CHAR', size => 30},
    	SchElem		=> { pos => 10, type => 'CHAR', },
    	AddrHseNo	=> { pos => 11, type => 'CHAR', },
    	SchJrHi		=> { pos => 12, type => 'CHAR', },
    	ListPrice	=> { pos => 13, type => 'INT',},
    	SchDistSV	=> { pos => 14, type => 'CHAR', },
    	SchSrHi		=> { pos => 15, type => 'CHAR', },
    	SalePrice	=> { pos => 16, type => 'CHAR',},
    	HStatus		=> { pos => 17, type => 'CHAR', },
    	AddrSt		=> { pos => 18, type => 'CHAR', },
    	UnitNo		=> { pos => 19, type => 'CHAR', },
    	YrBlt		=> { pos => 20, type => 'INT',  },
    	ZipCode		=> { pos => 21, type => 'CHAR', },
    	PubRmk		=> { pos => 22, type => 'TEXT', },
    	SDate		=> { pos => 23, type => 'CHAR', },
        UserID		=> { pos => 24, type => 'CHAR', size => 15},
        );
    
    $c->pk('ListID');
    $c->ai('ListID');
    if   ($c->create($action)) { $output .= "ok\n"; }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n"); 
        $c->set_defaults();
        $c->save_schema();
    }

# --------- Sample_Users Table ----------------
    $output .= "Creating Sample_Users table .. ";
    $c = $DB->creator('Sample_Users');
    $c->cols (
            Username    => { pos => 1, type => 'CHAR', size => 50, not_null => 1, protect => 1 },
            Password    => { pos => 2, type => 'CHAR', binary => 1, size => 25, not_null => 1, protect => 1 },
            Email       => { pos => 3, type => 'CHAR', size => 75, not_null => 1, regex => '^(?:(?:.+\@.+\..+)|\s*)$', protect => 1 },
            Name        => { pos => 4, type => 'CHAR', size => 75, protect => 1 },
            Status      => { pos => 6, type => 'ENUM', values => ['Not Validated', 'Registered', 'Administrator'], not_null => 1, default => 'Not Validated', protect => 1 },
            ReceiveMail => { pos => 7, type => 'ENUM', values => ['No', 'Yes'], not_null => 1, default => 'Yes', protect => 1 },
            Newsletter  => { pos => 8, type => 'ENUM', values => ['No', 'Yes'], not_null => 1, default => 'Yes', protect => 1 },
            view_p      => { pos => 9, type => 'ENUM', values => ['0', '1'],default => '0', protect => 1},
            add_p       => { pos => 10, type => 'ENUM',values => ['0', '1'],default => '0', protect => 1},
            delete_p    => { pos => 11, type => 'ENUM',values => ['0', '1'],default => '0', protect => 1},
            modify_p    => { pos => 12, type => 'ENUM',values => ['0', '1'],default => '0', protect => 1},
            admin_p     => { pos => 13, type => 'ENUM',values => ['0', '1'],default => '0', protect => 1},
        );
    $c->unique ({ emailndx => ['Email'] } );
    $c->pk('Username');
    if   ($c->create($action)) { $output .= "ok\n"; }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n"); 
        $c->set_defaults();
        $c->save_schema();
    }

# --------- Sessions table ----------------
    $output .= "Creating Sample_Users_Sessions table .. ";
    $c  = $DB->creator('Sample_Users_Sessions');
    $c->cols(
            session_id       => { pos => 1, type => 'CHAR', size =>32,not_null => 1},
            session_user_id  => { pos => 2, type => 'CHAR', sise => 20},
            session_date     => { pos => 3, type => 'int'},
            session_data     => { pos => 4, type => 'text'},
        );
    $c->pk('session_id');
    if ($c->create($action)) { 
        $output .= "ok\n"; 
    }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n");
        $c->set_defaults();
        $c->save_schema();
    }

# --------- SampleAuto_Log table ----------------
    $output .= "Creating Sample_Log table .. ";
    $c  = $DB->creator('Sample_Log');
    $c->cols(
            log_time    => { pos => 1, type => 'datetime'},
            action      => { pos => 2, type => 'CHAR', sise => 255},
            user_id     => { pos => 3, type => 'CHAR', size => 16},
            ip          => { pos => 4, type => 'CHAR', size => 20},
            tablename   => { pos => 4, type => 'CHAR', size => 70},
        );
    if ($c->create($action)) { 
        $output .= "ok\n"; 
    }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n");
        $c->set_defaults();
        $c->save_schema();
    }
    
# --------- Email Template Table ----------------
    $output .= "Creating Dbsql_EmailTemplates table .. ";
    $c  = $DB->creator('Dbsql_EmailTemplates');
    $c->cols(
            Name            => { pos => 1, type => 'CHAR', size => 50, not_null => 1, regex => '\S' },
            MsgFrom         => { pos => 2, type => 'TEXT', not_null => 1, regex => '\A(?:\S+\@[a-zA-Z0-9][a-zA-Z0-9-]*(?:\.[a-zA-Z0-9][a-zA-Z0-9-]*)+)\Z' },
            MsgFromName     => { pos => 3, type => 'TEXT', not_null => 1 },
            Subject         => { pos => 4, type => 'TEXT', not_null => 1 },
            Message         => { pos => 5, type => 'MEDIUMTEXT', not_null => 1 },
            MessageFormat   => { pos => 6, type => 'ENUM', values => [qw[text html]], not_null => 1, default => 'text' },
        );
    $c->pk('Name');
    if ($c->create($action)) { $output .= "ok\n"; }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n");
        $c->set_defaults();
        $c->save_schema();
    }

# --------- Email Mailings Table ----------------
    $output .= "Creating Dbsql_EmailMailings table .. ";
    $c  = $DB->creator('Dbsql_EmailMailings');
    $c->cols(
            ID      => { pos => 1, type => 'INT',not_null => 1},
            Mailing => { pos => 2, type => 'INT'},
            Email   => { pos => 3, type => 'CHAR', size => 100},
            Sent    => { pos => 4, type => 'TINYINT'},
        );
    $c->pk('ID');
    $c->ai('ID');
    if ($c->create($action)) { $output .= "ok\n"; }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n");
        $c->set_defaults();
        $c->save_schema();
    }
    
# --------- Email Mailing List table ----------------
    $output .= "Creating Dbsql_Mailing List table .. ";
    $c  = $DB->creator('Dbsql_MailingList');
    $c->cols(
            ID      => { pos => 1, type => 'INT'},
            Email   => { pos => 2, type => 'CHAR', size => 100},
        );
    if ($c->create($action)) { $output .= "ok\n"; }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n");
        $c->set_defaults();
        $c->save_schema();
    }

# --------- Email Mailing Index Table ----------------
    $output .= "Creating Dbsql_Mailing Index table .. ";
    $c  = $DB->creator('Dbsql_MailingIndex');
    $c->cols(
            Mailing         => { pos => 1, type => 'INT',not_null => 1},
            done            => { pos => 2, type => 'INT'},
            mailfrom        => { pos => 3, type => 'CHAR', size => 100},
            name            => { pos => 4, type => 'CHAR', size => 100},
            subject         => { pos => 5, type => 'CHAR', size => 100},
            message         => { pos => 6, type => 'TEXT'},
            messageformat   => { pos => 7, type => 'CHAR', size => 4},
        );
    $c->pk('Mailing');
    $c->ai('Mailing');
    if ($c->create($action)) { $output .= "ok\n"; }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n");
        $c->set_defaults();
        $c->save_schema();
    }
# --------- Email Mailing List Index Table ----------------
    $output .= "Creating Dbsql_Mailing List Index table .. ";
    $c  = $DB->creator('Dbsql_MailingListIndex');
    $c->cols(
            ID              => { pos => 1, type => 'INT',not_null => 1},
            Name            => { pos => 2, type => 'CHAR', size => 100},
            DateModified    => { pos => 3, type => 'INT'},
            DateCreated     => { pos => 4, type => 'INT'},
        );
    $c->pk('ID');
    $c->ai('ID');
    if ($c->create($action)) { $output .= "ok\n"; }
    else {
        $GT::SQL::errcode eq 'TBLEXISTS' ? ($output .= "failed (table already exists)\n") : ($output .= "failed ($GT::SQL::error)\n");
        $c->set_defaults();
        $c->save_schema();
    }
    return $output;
}


sub load_from_sql {
# ---------------------------------------------------------------
# Creates def files based on existing tables.
#
    my ($output, $return);
    foreach my $table (@TABLES) {
        $output .= "$table .. ";
        my $c = $DB->creator($table);
        $return = $c->load_table($table);
        if ($return) {
            $output .= "ok\n";
            $c->save_schema();
        }
        else {
            $output .= "failed: $GT::SQL::error\n";
        }
    }
    return $output;
}

sub load {
# ---------------------------------------------------------------
# Return a hash of current connection settings.
#
    my %h = ();
    $h{prefix}   = $DB->prefix();
    $h{database} = $DB->{connect}->{database};
    $h{login}    = $DB->{connect}->{login};
    $h{password} = $DB->{connect}->{password};
    $h{host}     = $DB->{connect}->{host};
    $h{host}    .= ":" . $DB->{connect}->{port} if ($DB->{connect}->{port});
    $h{driver}   = $DB->{connect}->{driver};
    return \%h;
}

1;