"""
$URL$
$Id$

SqliteStorage compares very favourably with ShelfStorage/FileStorage2 for
performance, based on limited tests with 50,000 records. The principal
difference is in database startup -- start up time is very low; random record
retrieval is likely close enough to the performance FileStorage delivers.

Usage:
    #    sql_connection = sqlite3.connect(':memory:')
    sql_connection = sqlite3.connect('/tmp/sqlite_durus.sql')
    connection = Connection(SqliteStorage(sql_connection))
    root = connection.get_root()
    ...
"""

from durus.utils import int8_to_str, str_to_int8
from dbapi_storage import DBAPIStorage

class SqliteStorage(DBAPIStorage):
    """
    Provides a Sqlite storage backend for Durus. Requires a DBAPI compatible
    Sqlite interface; both the Python 2.5 native sqlite3 DBAPI module and
    pysqlite2 have been tested with success (and no appreciable performance
    difference between the two modules).

    At 2007-08-24 this storage should be considered experimental and unproven.
    """
    def __init__(self, sql_connection, table_name='durus_records', **kwargs):
        """(sql_connection:dbapi sql connection, table_name:string) -> SqliteStorage

        Recognized kwargs (and their defaults) implemented as PRAGMA options to
        the database:
            autovacuum=0
            synchronous=0
            fullfsync=0
            default_cache_size=20000
        """
        DBAPIStorage.__init__(self, sql_connection, table_name=table_name, **kwargs)
        self._oid_zero_exists = False
        # isolation_level must be forced to None to allow transactions to be used
        sql_connection.isolation_level = None
        self.options.update(dict(
            autovacuum=0,
            synchronous=0,
            fullfsync=0,
            default_cache_size=20000))
        self.options.update(kwargs)
        cursor = self._sql_get_cursor()
        for key, value in self.options.items():
            if key in ['default_cache_size', 'fullfsync', 'synchronous', 'autovacuum']:
                cursor.execute("PRAGMA %s = %s" % (key, value))

    def _sql_create_table(self):
        """() -> None

        Creates the durus records table. Sqlite AUTOINCREMENT keyword ensures that
        rowid's (which become OID's) will never be reused again in the same table,
        in the same database. http://www.sqlite.org/autoinc.html
        """
        cursor = self._sql_get_cursor()
        cursor.execute('''CREATE TABLE %s
                            (p_oid INTEGER PRIMARY KEY AUTOINCREMENT,
                            pack INTEGER DEFAULT 0,
                            record BLOB)''' % self._sql_table_name)

    def _sql_table_exists(self):
        """
        Uses sqlite PRAGMA table_info() function to determine if the durus records
        table already exists.
        """
        cursor = self._sql_get_cursor()
        cursor.execute('PRAGMA table_info(%s)' % self._sql_table_name)
        result = cursor.fetchone()
        result = result and 'p_oid' in result or False
        if result:
            # has oid 0 (root) been created?
            cursor.execute('SELECT p_oid from %s WHERE p_oid = 0' % self._sql_table_name)
            result = cursor.fetchone()
            if result:
                self._oid_zero_exists = True
            print "OID", result
            return True
        else:
            return False

    def new_oid(self):
        cursor = self._sql_get_cursor()
        # Cannot force sqlite autoincrement sequence to start at 0 so a hack...
        if self._oid_zero_exists:
            cursor.execute('INSERT INTO %s DEFAULT VALUES' % self._sql_table_name)
            return int8_to_str(cursor.lastrowid)
        else:
            cursor.execute('INSERT INTO %s (p_oid) values (0)' % self._sql_table_name)
            self._oid_zero_exists = True
            return int8_to_str(0)

    def end(self, handle_invalidations=None):
        def gen_items():
            for oid, record in self.transaction.iteritems():
                yield (buffer(record), str_to_int8(oid))

        cursor = self._sql_get_cursor()
        cursor.executemany('''UPDATE %s set record=? WHERE p_oid = ?''' %\
                           self._sql_table_name, gen_items())
        self.transaction = None
        cursor.execute('COMMIT')