''' 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:

    # ??? how to coordinate with whichdb
    # ??? Any difference between blobs and text
    # ??? does default encoding affect str-->bytes or PySqlite3 always use UTF-8
    # ??? what is the correct isolation mode

'''

__all__ = ['error', 'open']

import sqlite3
import collections
from operator import itemgetter

error = sqlite3.DatabaseError

# Disabled: the INSERT trigger gives erroneours results when REPLACE
# is used for __setitem__.

#MAKE_SHELF = '''
#CREATE TABLE IF NOT EXISTS info
    #(key TEXT PRIMARY KEY,
     #value INFO NOT NULL);

#INSERT OR IGNORE INTO info (key, value) VALUES ('size', 0);

#CREATE TABLE IF NOT EXISTS shelf
    #(key TEXT PRIMARY KEY,
     #value TEXT NOT NULL);

#CREATE TRIGGER IF NOT EXISTS insert_shelf
    #AFTER INSERT ON shelf
    #BEGIN
        #UPDATE info SET value = value + 1 WHERE key = 'size';
    #END;

#CREATE TRIGGER IF NOT EXISTS delete_shelf
    #AFTER DELETE ON shelf
    #BEGIN
        #UPDATE info SET value = value - 1 WHERE key = 'size';
    #END;'''

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

GET_LEN =  'SELECT COUNT(*) FROM shelf'
#GET_LEN =  "SELECT value FROM info WHERE key = 'size'"
GET_BOOL =  'SELECT 1 FROM shelf LIMIT 1'

HAS_ITEM = 'SELECT 1 FROM shelf WHERE key = ?'
GET_ITEM = 'SELECT value FROM shelf WHERE key = ?'
SET_ITEM = 'REPLACE INTO shelf (key, value) VALUES (?,?)'
DEL_ITEM = 'DELETE FROM shelf WHERE key = ?'

ITER_KEYS = 'SELECT key from shelf'
ITER_VALUES = 'SELECT value from shelf'
ITER_ITEMS = 'SELECT key, value from shelf'

UPDATE_ITEMS = 'REPLACE INTO shelf (key, value) VALUES (?, ?)'

CLEAR_ALL = 'DELETE FROM shelf; VACUUM;'


class SQLHash(collections.MutableMapping):

    def __init__(self, filename=':memory:', flags='r', mode=None):
        # XXX add flag/mode handling
        #   c -- create if it doesn't exist
        #   n -- new empty
        #   w -- open existing
        #   r -- readonly
        self.conn = sqlite3.connect(filename)
        self.conn.text_factory = bytes
        self.conn.executescript(MAKE_SHELF)
        self.conn.commit()

    def __len__(self):
        return self.conn.execute(GET_LEN).fetchone()[0]

    def __bool__(self):
        return self.conn.execute(GET_BOOL).fetchone() is not None

    def keys(self):
        return map(itemgetter(0), self.conn.execute(ITER_KEYS))

    __iter__ = keys

    def values(self):
        return map(itemgetter(0), self.conn.execute(ITER_VALUES))

    def items(self):
        return iter(self.conn.execute(ITER_ITEMS))

    def __contains__(self, key):
        return self.conn.execute(HAS_ITEM, (key,)).fetchone() is not None

    def __getitem__(self, key):
        item = self.conn.execute(GET_ITEM, (key,)).fetchone()
        if item is None:
            raise KeyError(key)
        return item[0]

    def __setitem__(self, key, value):
        self.conn.execute(SET_ITEM, (key, value))

    def __delitem__(self, key):
        if key not in self:
            raise KeyError(key)
        self.conn.execute(DEL_ITEM, (key,))

    def update(self, items=(), **kwds):
        if isinstance(items, collections.Mapping):
            items = items.items()
        self.conn.executemany(UPDATE_ITEMS, items)
        self.conn.commit()
        if kwds:
            self.update(kwds)

    def clear(self):
        self.conn.executescript(CLEAR_ALL)
        self.conn.commit()

    def sync(self):
        if self.conn is not None:
            self.conn.commit()

    def close(self):
        if self.conn is not None:
            self.conn.commit()
            self.conn.close()
            self.conn = None

    def __del__(self):
        self.close()


def open(filename, _flag=None, mode=0o666):
    return SQLHash(filename, mode)