#
# Copyright INRA-URGI 2009-2010
# 
# This software is governed by the CeCILL license under French law and
# abiding by the rules of distribution of free software. You can use,
# modify and/ or redistribute the software under the terms of the CeCILL
# license as circulated by CEA, CNRS and INRIA at the following URL
# "http://www.cecill.info".
# 
# As a counterpart to the access to the source code and rights to copy,
# modify and redistribute granted by the license, users are provided only
# with a limited warranty and the software's author, the holder of the
# economic rights, and the successive licensors have only limited
# liability.
# 
# In this respect, the user's attention is drawn to the risks associated
# with loading, using, modifying and/or developing or reproducing the
# software by the user in light of its specific status of free software,
# that may mean that it is complicated to manipulate, and that also
# therefore means that it is reserved for developers and experienced
# professionals having in-depth computer knowledge. Users are therefore
# encouraged to load and test the software's suitability as regards their
# requirements in conditions enabling the security of their systems and/or
# data to be ensured and, more generally, to use and operate it in the
# same conditions as regards security.
# 
# The fact that you are presently reading this means that you have had
# knowledge of the CeCILL license and that you accept its terms.
#
#! /usr/bin/env python
import os
import random
import sqlite3
from SMART.Java.Python.mySql.MySqlQuery import MySqlQuery


class MySqlConnection(object):
    """Connection to a database"""

    def __init__(self, verbosity = 0):
        self.verbosity = verbosity
        self.databaseName = os.path.join(os.environ.get("SMARTTMPPATH", "."), "smartdb%d" % random.randint(0, 100000))
        self.connection = sqlite3.connect(self.databaseName)
        self.executeQuery("PRAGMA journal_mode = OFF")
        self.executeQuery("PRAGMA synchronous = 0")
        self.executeQuery("PRAGMA locking_mode = EXCLUSIVE")
        self.executeQuery("PRAGMA count_change = OFF")
        self.executeQuery("PRAGMA temp_store = 2")

    def __del__(self):
        self.connection.close()


    def createDatabase(self):
        pass


    def deleteDatabase(self):
        if os.path.exists(self.databaseName):
            os.remove(self.databaseName)


    def executeQuery(self, command, insertion = False):
        cursor = self.connection.cursor()
        query = MySqlQuery(cursor, self.verbosity)
        try:
            result = query.execute(command, insertion)
            self.connection.commit()
        except:
            result = query.execute(command, insertion)
            self.connection.commit()
        if insertion:
            return result
        else:
            return query


    def executeManyQueries(self, commands):
        cursor = self.connection.cursor()
        query = MySqlQuery(cursor, self.verbosity)
        try:
            for cpt, command in enumerate(commands):
                query.execute(command)
            self.connection.commit()
        except:
            for cpt, command in enumerate(commands):
                query.execute(command)
            self.connection.commit()


    def executeManyFormattedQueries(self, command, lines, insertion = False):
        cursor = self.connection.cursor()
        query = MySqlQuery(cursor, self.verbosity)
        for line in lines:
            result = query.executeFormat(command, line)
        self.connection.commit()
        if insertion:
            return result
        else:
            return query


    def executeManyQueriesIterator(self, table):
        cursor = self.connection.cursor()
        query = MySqlQuery(cursor, self.verbosity)
        try:
            for command in table.getIterator():
                query.execute(command)
            self.connection.commit()
        except:
            for command in table.getIterator():
                query.execute(command)
            self.connection.commit()


    def executeManyFormattedQueriesIterator(self, table):
        cursor = self.connection.cursor()
        query = MySqlQuery(cursor, self.verbosity)
        try:
            for command, values in table.getIterator():
                query.executeFormat(command, values)
            self.connection.commit()
        except:
            for command, values in table.getIterator():
                query.execute(command, values)
            self.connection.commit()


    def executeFormattedQuery(self, command, parameters, insertion = False):
        cursor = self.connection.cursor()
        query = MySqlQuery(cursor, self.verbosity)
        result = query.executeFormat(command, parameters)
        self.connection.commit()
        if insertion:
            return result
        else:
            return query