"""
$URL$
$Id$

PostgresqlStorage isn't a speed demon, particularly when performing many
Persistent object commits, but read performance remains quite good and its
an industrial strength back end which some may feel more comfortable with than
Sqlite.

Usage:
    sql_connection = psycopg2.connect('dbname=test')
    connection = Connection(PostgresqlStorage(sql_connection))
    root = connection.get_root()
    ...
"""

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


class PostgresqlStorage(DBAPIStorage):
    """
    Provides a Postgresql storage backend for Durus. Requires a DBAPI
    compatible Postgresql interface; after evaluating several DBAPI interfaces,
    at this time only psycopg2 is supported.

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

    def _sql_create_table(self):
        if True:
            cursor = self._sql_get_cursor()
            cursor.execute('''CREATE TABLE %s
                                (p_oid SERIAL UNIQUE PRIMARY KEY,
                                 pack INTEGER DEFAULT 0,
                                 record BYTEA)''' % (self._sql_table_name,))
            cursor.execute('''ALTER SEQUENCE %s_p_oid_seq
                                minvalue 0 restart with 0''' % (self._sql_table_name,))

    def _sql_table_exists(self):
        """
        Uses Postgres pg_tables; not portable.
        """
        if True:
            cursor = self._sql_get_cursor()
            cursor.execute("""SELECT tablename from pg_tables
                                WHERE schemaname='public'
                                AND tablename=%s""", (self._sql_table_name,))
            result = cursor.fetchone()
            return result and result[0] or False

    def new_oid(self):
        cursor = self._sql_get_cursor()
        cursor.execute('INSERT INTO %s DEFAULT VALUES RETURNING p_oid' %\
                       self._sql_table_name)
        result = cursor.fetchone()
        return int8_to_str(result[0])

    def end(self, handle_invalidations=None):
        #print "in end"
        def gen_transaction():
            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=%%s WHERE p_oid = %%s''' %\
                           self._sql_table_name, gen_transaction())
        self.transaction = None
        cursor.execute('COMMIT')