#!/usr/bin/env python3.0
''' Dbm based on sqlite -- Needed to support shelves

Key and values are always stored as bytes. This means that when strings are
used they are implicitly converted to the default encoding before being
stored.

Issues:

    # ??? sqlite3.OperationalError: database is locked
    # ??? how to commit after every insert
    # ??? which operations lock the db
    # ??? how to lock during paired queries like those in __setitem__
    # ??? how to coordinate with whichdb
    # ??? verify that implicit cursors are distinct
    # ??? Size of text fields fixed or varchar (do we need blobs)
    # ??? does default encoding affect str-->bytes or PySqlite3 always use UTF-8

'''

__all__ = ['error', 'open', 'SQLhash']

import sqlite3
import collections
from operator import itemgetter

error = sqlite3.DatabaseError

class SQLhash(collections.MutableMapping):

    def __init__(self, filename=':memory:'):
        # XXX add flags and mode
        #   c -- create if it doesn't exist
        #   n -- new empty
        #   w -- open existing
        #   r -- readonly

        SHELF_EXISTS = '''
            SELECT COUNT(*) FROM sqlite_master
                WHERE type = "table"
                AND name = "shelf"
        '''

        MAKE_SHELF = '''
            CREATE TABLE shelf (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL)
        '''

        self.conn = sqlite3.connect(filename)
        if self.conn.execute(SHELF_EXISTS).fetchone()[0] != 1:
            self.conn.execute(MAKE_SHELF)
        # http://docs.python.org/3.0/library/sqlite3.html#sqlite3.Connection.text_factory
        self.conn.text_factory = bytes
        # http://glyphy.com/sqlite-isolation-levels-2008-03-27
        self.conn.isolation_level = 'DEFERRED' 

    def __len__(self):
        # XXX is there a faster way using sqlite_master?
        GET_LEN =  'SELECT COUNT(*) FROM shelf'
        return self.conn.execute(GET_LEN).fetchone()[0]

    def keys(self):
        return SQLhashKeysView(self)

    def values(self):
        return SQLhashValuesView(self)

    def items(self):
        return SQLhashItemsView(self)

    def __iter__(self):
        return iter(self.keys())

    def __contains__(self, key):
        # XXX is the getitem approach faster?
        HAS_ITEM = 'SELECT COUNT(*) FROM shelf WHERE key = ?'
        return self.conn.execute(HAS_ITEM, (key,)).fetchone()[0] == 1

    def __getitem__(self, key):
        GET_ITEM = 'SELECT value FROM shelf WHERE key = ?'
        item = self.conn.execute(GET_ITEM, (key,)).fetchone()
        if not item:
            raise KeyError(key)
        return item[0]

    def __setitem__(self, key, value):
        # XXX need a lock or begin/commit between the two queries?
        if key in self:
            del self[key]
        ADD_ITEM = 'INSERT INTO shelf VALUES (?,?)'
        self.conn.execute(ADD_ITEM, (key, value))

    def __delitem__(self, key):
        if key not in self:
            raise KeyError(key)
        DEL_ITEM = 'DELETE FROM shelf WHERE key = ?'
        self.conn.execute(DEL_ITEM, (key,))

    def update(self, items=(), **kwds):
        # XXX must also remove existing or use UPDATE
        # XXX handle conflicts arising during the UPDATE
        if isinstance(items, collections.Mapping):
            items = items.items()
        self.conn.executemany("INSERT INTO shelf VALUES (?, ?)", items)
        if kwds:
            self.update(kwds)

    def clear(self):
        CLEAR_ALL = 'DELETE FROM shelf; VACUUM;'
        self.conn.executescript(CLEAR_ALL)

    def close(self):
        self.conn.close()

    def __del__(self):
        self.close()    

class ListRepr:

    def __repr__(self):
        return repr(list(self))    

class SQLhashKeysView(collections.KeysView, ListRepr):
    
    def __iter__(self):
        GET_KEYS = 'SELECT key FROM shelf'
        return map(itemgetter(0), self._mapping.conn.execute(GET_KEYS))

class SQLhashValuesView(collections.ValuesView, ListRepr):
    
    def __iter__(self):
        GET_VALUES = 'SELECT value FROM shelf'
        return map(itemgetter(0), self._mapping.conn.execute(GET_VALUES))

class SQLhashItemsView(collections.ValuesView, ListRepr):
    
    def __iter__(self):
        GET_ITEMS = 'SELECT key, value FROM shelf'
        return iter(self._mapping.conn.execute(GET_ITEMS))

def open(file=None, *args):
    if file is not None:
        return SQLhash(file)
    return SQLhash()

if __name__ in '__main___':
    for d in SQLhash(), SQLhash('example'):
        d['abc'] = 'lmno'
        print(d['abc'])    
        d['abc'] = 'rsvp'
        d['xyz'] = 'pdq'
        print(d.items())
        print(d.values())
        print(d.keys())
        print(list(d), 'list')
        d.update(p='x', q='y', r='z')
        print(d.items())
        
        del d['abc']
        try:
            print(d['abc'])
        except KeyError:
            pass
        else:
            raise Exception('oh noooo!')
        
        try:
            del d['abc']
        except KeyError:
            pass
        else:
            raise Exception('drat!')
        
        d.clear()
        print(list(d))
        d.update(p='x', q='y', r='z')        
        

        print()
        d.close()