import pyodbc
import sys
import unittest
from urllib import quote

from sqlalchemy import create_engine

from reporting.vertica.base import VerticaDialect


def odbc_connstring(pairtuple):
    return ';'.join(['='.join(pair) for pair in pairtuple])


class TestVertica(unittest.TestCase):
    connsettings = (
        ('DSN', 'Vertica'),
        ('SERVERNAME', 'vprod03'),
        ('DATABASE', 'vmc_prod'),
    )

    tmp_create = 'CREATE TABLE my_sample_table (firstcol INT, secondcol INT)'
    tmp_drop = 'DROP TABLE my_sample_table'
    tmp_tbl = 'my_sample_table'

    def setUp(self):
        connstring = odbc_connstring(self.connsettings)
        self.conn = pyodbc.connect(connstring)
        self.c = self.conn.cursor()
        self.c.execute(self.tmp_create)

    def tearDown(self):
        self.c.execute(self.tmp_drop)
        self.conn.close()

    def testCatalogTables(self):
        'Test bug fix reported/fixed in Vertica support case 2986.'
        s = ("SELECT table_schema, table_name FROM v_catalog.tables "
             "WHERE table_name = '%s'") % (self.tmp_tbl,)

        row = self.c.execute(s).fetchone()
        self.assertEqual(row.table_name, self.tmp_tbl)

    def testBoolType(self):
        s = ("SELECT is_nullable from v_catalog.columns "
             "WHERE table_name = '%s'") % (self.tmp_tbl,)
        # Bah! ODBC returns boolean types as Strings "1" or "0".
        # This wreaks some havoc on SQLAlchemy.
        for row in self.c.execute(s):
            self.assertTrue(row[0] in ('1', '0'))


class TestVerticaDialect(unittest.TestCase):
    test_tbl = 'my_sample_table'
    tmp_create = (
        "CREATE TABLE my_sample_table ("
        " pk INT NOT NULL PRIMARY KEY,"
        " firstcol INT NOT NULL,"
        " secondcol BOOLEAN DEFAULT 'f'"
        ")"
        )
    tmp_drop = 'DROP TABLE my_sample_table'
    def setUp(self):
        connstr = quote(odbc_connstring(TestVertica.connsettings))
        self.engine = create_engine('vertica:///?odbc_connect=%s' % (connstr,))
        self.engine.execute(self.tmp_create)
        self.dialect = VerticaDialect()

    def tearDown(self):
        self.engine.execute(self.tmp_drop)

    def testGetTableNames(self):
        'Test bug fix reported/fixed in Vertica support case 2986.'
        tables = self.dialect.get_table_names(self.engine, schema='public')
        self.assertTrue(self.test_tbl in tables)

    def testGetColumns(self):
        cols = self.dialect.get_columns(
            self.engine, self.test_tbl, schema='public')
        self.assertEqual(cols[0]['name'], 'pk')
        self.assertEqual(cols[1]['name'], 'firstcol')
        self.assertEqual(cols[2]['name'], 'secondcol')

        self.assertTrue(cols[0]['primary_key'])
        self.assertFalse(cols[1]['primary_key'])
        self.assertFalse(cols[2]['primary_key'])

        # TODO: check 'type', 'default' 
        # TODO: 'default' needs to be compiled into a type according to 'type'
        # TODO: boolean types don't work properly (e.g. nullable)


if __name__ == '__main__':
    unittest.main()