import unittest2 as unittest
import difflib
import sys
import os
import warnings

from cStringIO import StringIO

import MySQLdb as mysql

from ltpdarepo.config import USERNAME, PASSWORD, HOSTNAME
from ltpdarepo.admin import wipe, install, createdb, useradd, grant, upgrade, dump


class TestCase(unittest.TestCase):

    def setUp(self):
        wipe()

    def tearDown(self):
        wipe()

    def test_schema_upgrade(self):
        warnings.simplefilter('ignore', category=mysql.Warning)

        # load dump obtained with `mysqldump --add-drop-database --skip-comments --databases mysql ltpda db1`
        conn = mysql.connect(host=HOSTNAME, db='', user=USERNAME, passwd=PASSWORD, charset='utf8')
        curs = conn.cursor()
        pwd = os.path.dirname(__file__)
        sql = open(os.path.join(pwd, '..', 'sql', 'dump-v2.4.sql'))
        for stmt in [x.strip() for x in sql.read().split(';\n')]:
            if stmt:
                curs.execute(stmt)
        conn.commit()

        # upgrade
        upgrade()

        curs = conn.cursor()

        # no user accounts specified for host '127.0.0.1'
        curs.execute("""SELECT Host FROM mysql.user WHERE User = 'u1'""")
        hosts = sorted([row[0] for row in curs.fetchall()])
        self.assertEqual(hosts, ['localhost'])
        curs.execute("""SELECT Host FROM mysql.user WHERE User = 'u2'""")
        hosts = sorted([row[0] for row in curs.fetchall()])
        self.assertEqual(hosts, ['%', 'localhost'])

        # check privileges
        curs.execute("""SELECT Db FROM mysql.db WHERE User = 'u2'""")
        dbs = [row[0] for row in curs.fetchall()]
        self.assertEqual(dbs, ['db1'])

        # no explicit privileges on transactions table
        curs.execute("""SELECT Table_name FROM mysql.tables_priv WHERE User = 'u2'""")
        tables = [row[0] for row in curs.fetchall()]
        self.assertEqual(tables, [])

        # dump database structure
        upgraded = StringIO()
        dump('ltpda', out=upgraded)
        dump('db1', out=upgraded)
        upgraded.seek(0)

        # install
        wipe()
        install()
        useradd('u1', password='u1', name='One', surname='User', email='u1@example.net')
        grant('u1', '%', ['admin', ])
        createdb('db1', description=u'Test database one')
        grant('u1', 'db1', ['select', 'insert', 'update', 'delete'])

        # dump database structure
        new = StringIO()
        dump('ltpda', out=new)
        dump('db1', out=new)
        new.seek(0)

        # compare
        self.assertTrue(upgraded.getvalue() == new.getvalue())