#!/usr/bin/env python

import sys; sys.path.append('../../../')

import web, web.database, datetime
import web.error, web.error.handler;  web.error.handle(web.error.handler.htmlfile,  file='error.html')
import copy
useage = "Useage: \npython db.py <database type>\n\neg:\npython db.py gadfly"

def generateValues():
    return (
        "'",
        "STRINi'G",
        """BIN678ARY0BIN678ARY0BIN678ARY0BIN678ARY0B
        IN678ARY0BIN678ARY0BIN678ARY0BIN678ARY0BIN678
        ARY0BIN678ARY0BIN678ARY0BIN678ARY0BIN678ARY0B
        IN678ARY0BIN678ARY0BIN678ARY0BIN678ARY0BIN678ARY
        0BIN678ARY0BIN678ARY0BIN678ARY0BIN678ARY0BIN678
        ARY0BIN678ARY0BIN678ARY0BIN678ARY0BIN678ARY0
        BIN678ARY0BIN678ARY0BIN678ARY0""",
        230,
        0.000768,
        datetime.date(2003,6,1),#2,2,2
        datetime.time(12,22,3),
        datetime.datetime(2003,6,1,12,22,3),
    )
                    
def same(tuple1, tuple2):
    if len(tuple1) <> len(tuple2):
        print "FAILED: tuples different lengths"
        return False
    else:
        errors = []
        for i in range(len(tuple1)):
            if type(tuple1[i]) <> type(tuple2[i]):
                errors.append('%s different type to %s'%(tuple1[i],tuple2[i]))
            if tuple1[i] <> tuple2[i]:
                errors.append('%s <> to %s'%(tuple1[i],tuple2[i]))
        if errors:
            print '\n'.join(errors)
            return False
        return True
        
if __name__ == '__main__':

    if len(sys.argv) <> 2:
        db = raw_input('Database type [mysql, gadfly, odbc, sqlite]: ')
    else:
        db = sys.argv[1]
    if db == 'gadfly':
        conn = web.database.connect(type="gadfly", database="test", dir=".", startup=True)
        cursor = conn.cursor()
    elif db == 'sqlite':
        conn = web.database.connect(type='sqlite', database='test.db')
        cursor = conn.cursor()
    elif db == 'mysql':
        conn = web.database.connect(type='mysql', database='test')
        cursor = conn.cursor()
    elif db == 'odbc':
        conn = web.database.connect(type='odbc', database='Test')
        cursor = conn.cursor()
    else:
        raise Exception("Database type '%s' not recognised."%db)
        
    # Delete the old table ready to run the test again.
    if cursor.tableExists('test'):
        cursor.drop('test')
        conn.commit()
        print "Table test dropped."
    else:
        print "Couldn't delete the test table. Perhaps this is the first time the script has been run."

#
# Test 1: Check Column Types
#

    print "\n----------------------------------------------------"
    print "Test 1: Making sure each column type can be created."
    print "----------------------------------------------------\n"
    
    cursor.create('test',(('TestCHAR', "Char"),))
    cursor.drop('test')
    print "CHAR     Passed"
    
    cursor.create('test',(('TestSTRING', "String"),) )
    cursor.drop('test')
    print "STRING   Passed"
    
    cursor.create('test',(('TestTEXT', "Text"),) )
    cursor.drop('test')
    print "TEXT     Passed"
    
    cursor.create('test',(('TestINTEGER', "Integer"),) )
    cursor.drop('test')
    print "INTEGER  Passed"
    
    cursor.create('test',(('TestFLOAT', "Float"),))
    cursor.drop('test')
    print "FLOAT    Passed"
    
    cursor.create('test',(('TestDATE', "Date"),))
    cursor.drop('test')
    print "DATE     Passed"
    
    cursor.create('test',(('TestTIME', "Time"),) )
    cursor.drop('test')
    print "TIME     Passed"
    
    cursor.create('test',(('TestDATETIME', "DateTime"),))
    cursor.drop('test')
    print "DATETIME Passed"

#
# Test 2: Check Encoding/Decoding
#

    print "\n----------------------------------------------------"
    print "Test 2: Check Encoding/Decoding."
    print "----------------------------------------------------\n"
    
    cursor.create(
        'test',
        (  
            ('TestCHAR', "Char"),
            ('TestSTRING', "String"),
            ('TestTEXT', "Text"),
            ('TestINTEGER', "Integer"),
            ('TestFLOAT', "Float"),
            ('TestDATE', "Date"),
            ('TestTIME', "Time"),
            ('TestDATETIME', "DateTime"),
        ) 
    )
    conn.commit()    
    
    values = generateValues()
    
    cursor.insert(
        'test', 
        ('TestCHAR','TestSTRING','TestTEXT','TestINTEGER','TestFLOAT','TestDATE','TestTIME','TestDATETIME'),
        copy.copy(values),
    )

    newValues = cursor.select(
        ('TestCHAR','TestSTRING','TestTEXT','TestINTEGER','TestFLOAT','TestDATE','TestTIME','TestDATETIME'),
        'test', 
    )[0]
    if not same(values,newValues):
        print "FAILED INSERT  Values do not match"
    else:
        print "PASSED INSERT"
        
    cursor.update(
        'test', 
        ('TestCHAR','TestSTRING','TestTEXT','TestINTEGER','TestFLOAT','TestDATE','TestTIME','TestDATETIME'),
        copy.copy(values),
    )
    newValues = cursor.select(
        ('TestCHAR','TestSTRING','TestTEXT','TestINTEGER','TestFLOAT','TestDATE','TestTIME','TestDATETIME'),
        'test', 
    )[0]
    
    if not same(values,newValues):
        print "FAILED UPDATE  Values do not match"
    else:
        print "PASSED UPDATE"

#
# Test 3: FetchModes
#

    print "\n----------------------------------------------------"
    print "Test 3: FetchModes."
    print "----------------------------------------------------\n"
    

    result = cursor.select('*',"test", fetchMode = 'dtuple')
    if result[0][3] == result[0]['TestINTEGER'] == 230:
        print 'dtuple Passed'

    result = cursor.select('*',"test",fetchMode = 'tuple')
    if result[0][3] == 230:
        print 'tuple  Passed'
        
    result = cursor.select('*',"test", fetchMode = 'dict')
    if result[0]['TestINTEGER'] == 230:
        print 'dict   Passed'

#
# Test 4: Alter/Drop Code
#

    print "\n----------------------------------------------------"
    print "Test 4: Alter/Drop Code."
    print "----------------------------------------------------\n"
    
    conn.commit()
    
    if cursor.type in ['mysql']:
        cursor.alter('test', 'add', 'TestColumn','String')
        print "Added Column"
    
        cursor.alter('test', 'drop', 'TestColumn')
        print "Dropped Column"

    else:
        print "No ALTER support.. skipping"

#
# Test 5: NULL Handling
#

    print "\n----------------------------------------------------"
    print "Test 5: NULL Handling."
    print "----------------------------------------------------\n"
    
    if cursor.type in ['mysql']:
        "Gadfly doesn't support NULLs.. skipping."
    else:
        cursor.delete('test')
        cursor.insert (
            'test', 
            ('TestCHAR','TestSTRING','TestTEXT','TestINTEGER','TestFLOAT','TestDATE','TestTIME','TestDATETIME'),
            (None,None,None,None,None,None,None,None),
        )
        newValues = cursor.select(
            ('TestCHAR','TestSTRING','TestTEXT','TestINTEGER','TestFLOAT','TestDATE','TestTIME','TestDATETIME'),
            'test'
        )[0]
        if not same((None,None,None,None,None,None,None,None,),newValues):
            print "FAILED NULLS  Values do not match"
        else:
            print "PASSED NULLS"
        

#
# Test 6: Min and Max Test
#

    print "\n----------------------------------------------------"
    print "Test 6: Min and Max Test."
    print "----------------------------------------------------\n"
    
    cursor.delete('test')
    v = list(generateValues())
    v[3] = 10
    v[4] = 10.06
    v[5] = datetime.date(2004,07,11)
    v[6] = datetime.time(23,25,38)
    v[7] = datetime.datetime(2004,07,11,23,25,38)
    cursor.insert (
        'test', 
        ('TestCHAR','TestSTRING','TestTEXT','TestINTEGER','TestFLOAT','TestDATE','TestTIME','TestDATETIME'),
        v,
    )
    v[3] = 11
    v[4] = 11.06
    v[5] = datetime.date(2004,07,12)
    v[6] = datetime.time(23,25,39)
    v[7] = datetime.datetime(2004,07,11,23,25,39)
    cursor.insert (
        'test', 
        ('TestCHAR','TestSTRING','TestTEXT','TestINTEGER','TestFLOAT','TestDATE','TestTIME','TestDATETIME'),
        v,
    )

    if 11 == cursor.max('TestINTEGER','test'):
        print "PASSED Integer MAX"
    else:
        print "FAILED Integer MAX"
    if 10 == cursor.min('TestINTEGER','test'):
        print "PASSED Integer MIN"
    else:
        print "FAILED Integer MIN"
    if 11.06 == cursor.max('TestFLOAT','test'):
        print "PASSED Float MAX"
    else:
        print "FAILED Float MAX"
    if 10.06 == cursor.min('TestFLOAT','test'):
        print "PASSED Float MIN"
    else:
        print "FAILED Float MIN"
    if datetime.date(2004,07,12) == cursor.max('TestDATE','test'):
        print "PASSED Date MAX"
    else:
        print "FAILED Date MAX"
    if datetime.date(2004,07,11) == cursor.min('TestDATE','test'):
        print "PASSED Date MIN"
    else:
        print "FAILED Date MIN"
    if datetime.time(23,25,39) == cursor.max('TestDATE','test'):
        print "PASSED Time MAX"
    else:
        print "FAILED Time MAX"
    if datetime.time(23,25,38) == cursor.min('TestDATE','test'):
        print "PASSED Time MIN"
    else:
        print "FAILED Time MIN"
    if datetime.datetime(2004,07,11,23,25,39) == cursor.max('TestDATE','test'):
        print "PASSED DateTime MAX"
    else:
        print "FAILED DateTime MAX"
    if datetime.datetime(2004,07,11,23,25,38) == cursor.min('TestDATE','test'):
        print "PASSED DateTime MIN"
    else:
        print "FAILED DateTime MIN"

    # XXX WARNING, Do database have different interpretaions for string fields?
    
    raw_input("\nAll done! [Press ENTER]")
else:
    print useage