########################################################################################################################
#    File: main.py
# Purpose:
#  Author: Dan Huckson, https://github.com/unodan
########################################################################################################################
from dbslave.interface import *
import sqlparse

def main():
    '''
    sql = 'select * from "someschema"."mytable" where id = 1'

    parsed = sqlparse.parse(sql)
    stmt = parsed[0]
    print(sqlparse.format(sql, reindent=True, keyword_case='upper'))
    print("SS", parsed[0], )

    for i in stmt.tokens:
        print(i)

    print(str(stmt))
    '''

    appName = 'TestDBSlave'
    dbName = appName

    table1 = {
        'users1': 'id SERIAL, '
                 'fName VARCHAR(30) NOT NULL, lName VARCHAR(30) NOT NULL',

        'users2': 'id SERIAL, '
                 'fName VARCHAR(30) NOT NULL, lName VARCHAR(30) NOT NULL'
    }

    table2 = {
        'users1': 'id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, '
                 'fName VARCHAR(30) NOT NULL, lName VARCHAR(30) NOT NULL',

        'users2': 'id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, '
                 'fName VARCHAR(30) NOT NULL, lName VARCHAR(30) NOT NULL'
    }

    table3 = {
        'users1': 'id INT IDENTITY(1,1), '
                 'fName NVARCHAR(30), lName NVARCHAR(30)',

        'users2': 'id INT IDENTITY(1,1), '
                 'fName NVARCHAR(30), lName NVARCHAR(30)'
    }

    table4 = {
        'users1': 'id INTEGER PRIMARY KEY, '
                 'fName VARCHAR(30) NOT NULL, lName VARCHAR(30) NOT NULL',

        'users2': 'id INTEGER PRIMARY KEY, '
                 'fName VARCHAR(30) NOT NULL, lName VARCHAR(30) NOT NULL'
    }

    tables = table4

    #db = new_engine('postgres://dan:password@localhost:5432/' + dbName + '/utf8')
    #db = new_engine('mysql://dan:password@localhost:3306/' + dbName + '/utf8')
    #db = new_engine('mssql://sa:password@beta.dyncomp.local:1433/' + dbName + '/utf8')
    db = new_engine('sqlite:///' + dbName + '/utf8')

    host = 'localhost'
    if 'host' in db.credentials:
        host = db.credentials["host"]

    if db.connect():
        #if db.database_exist(dbName):
        #    db.drop_database(dbName)

        if not db.database_exist(dbName):
            db.create_database(dbName)

        if db.use(dbName):
            print('Connected to [{}] using database [{}] via [{}]'.format(host, dbName, db.credentials["dialect"]))
        else:
            print('Could not connected to database {}'.format(dbName))

        for t in tables:
            if not db.table_exist(t):
                db.create_table(t, tables[t])

        if db.index_exist('users1', 'idx_fName'):
            print("Yes it's here dropping it now!")
            db.drop_index('users1', 'idx_fName')
        else:
            print("No it's not here creating it now!")
            db.create_index('users1', 'fName', 'idx_fName')

        if db.index_exist('users1', 'idx_fName'):
            print("Yep and it's still here too!")
        else:
            print("It's not here anymore!")

        fields = {'fName': 'Dan', 'lName': 'Huckson'}
        fields2 = {'fName': 'Daniel', 'lName': 'Huckson'}

        db.update_row('users1', fields2, 1)
        db.insert_row('users1', fields)
        db.delete_row('users1', 10)
        db.drop_table('users2')

        db.execute('UPDATE users1 SET fName=%s,lName=%s WHERE id=%s', ('Danny', 'Huckson', 3))

        if db.execute('SELECT * FROM users1;'):
            for r in db.fetchall():
                print('ROW:', r)

        if db.row_exist('users1', 7):
            print('YES it is there')
        else:
            print('Nope not there')

        #db.dump(dbName)

        db.close()
    else:
        print("Error: Could not connect to (%s)." % dbName)


if __name__ == '__main__':
    main()