[meabook] / database / SQLite.py

import os
import sqlite3

DATABASE_NAME = 'contacts.db'

TOPLEVEL_FIELD = 'o'
MIDDLELEVEL_FIELD = 'ou'
LOWLEVEL_FIELD = 'cn'

SCHEMA = """
    begin;

    create table data(
        id integer,
        field_id text,
        value text
    );

    create table fields(
        id integer primary key,
        name text
    );

    create table relation(
        data_id integer,
        struct_id integer
    );

    create table struct(
        id integer primary key,
        name text,
        parent integer
    );

    commit;

    create index i_fields on fields (id);
    create index i_data on data (id);
    create index i_struct on struct(parent);
    create index i_relation on relation(data_id);

    commit;

"""


class SQLite:
    def __init__(self, basedir):
        self._path = os.path.join(basedir, DATABASE_NAME)
        self.conn = None
        if not os.path.exists(self._path):
            self.new()
        else:
            self.conn = sqlite3.connect(self._path)

    def new(self):
        """Creates new databse."""

        self.conn = sqlite3.connect(self._path)
        self.conn.executescript(SCHEMA)
        self.conn.commit()

    def close(self):
        """Closes connection with database."""

        self.conn.commit()
        self.conn.close()

    def save(self):
        """Save all changes."""

        self.conn.commit()

    def clear(self):
        """Clear all database tables."""

        execute = self.conn.execute
        execute("DELETE from data")
        execute("DELETE from fields")
        execute("DELETE from struct")
        execute("DELETE from relation")
        self.conn.commit()

    def get_fields(self):
        """Returns all fields from FIELDS table."""

        return [item[0] for item in self.conn.execute( \
            "SELECT name FROM fields").fetchall()]

    # operations with DATA table
    def add_entry(self, entry):
        """Adds new entry to database."""

        execute = self.conn.execute

        try:
            _id = execute("SELECT MAX(id) FROM data").fetchone()[0] \
                + 1
        except TypeError:
            _id = 1

        for field, values in entry.items():
            # update FIELDS table
            field_id = execute("SELECT id, name FROM fields WHERE name=?", \
                (field,)).fetchone()
            if field_id is None:
                execute("INSERT INTO fields values(NULL, ?)", (field,))
                field_id = execute("SELECT last_insert_rowid()").fetchone()[0]
            else:
                field_id = field_id[0]

            # update DATA table
            for value in values:
                execute("INSERT INTO data values(?,?,?)", \
                    (_id, field_id, value))

        # update STRUCT table
        name = entry[TOPLEVEL_FIELD][0]
        parent_id = execute("SELECT id FROM struct WHERE name=? ", \
            (name,)).fetchone()
        if parent_id is None: # update STRUCT table (TOPLEVEL_FIELD)
            execute("INSERT INTO struct values(NULL,?,0)", (name,))
            parent_id = execute("SELECT last_insert_rowid()").fetchone()[0]
        else:
            parent_id = parent_id[0]

        name = entry[MIDDLELEVEL_FIELD][0]
        child_id = execute("SELECT id FROM struct WHERE name=? AND parent=?", \
            (name, parent_id)).fetchone()
        if child_id is None: # update STRUCT table (MIDDLELEVEL_FIELD)
            execute("INSERT INTO struct values(NULL,?,?)", (name, parent_id))
            child_id = execute("SELECT last_insert_rowid()").fetchone()[0]
        else:
            child_id = child_id[0]

        # update RELATION table
        execute("INSERT INTO relation values(?,?)", (_id, child_id))

    def get_entry(self, value):
        """Gets full entry imformation from DATA table."""

        _id = self.conn.execute("SELECT id FROM data WHERE value=?", \
            (value,)).fetchone()[0]
        return dict([(key, value) for key, value in self.conn.execute("\
            SELECT field_id, value FROM data WHERE id=?", (_id,)).fetchall()])

    def get_folders(self, parent=None):
        """
        Returns list of all folders (items with folders and files)
        from STRUCT table.
        """

        if parent is None:  # return all folders on level2
            return self.conn.execute("SELECT DISTINCT name, id FROM struct \
                WHERE parent!=0 ORDER BY name ASC").fetchall()
        else:
            return self.conn.execute("SELECT DISTINCT name, id FROM struct \
                WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()

    def get_files(self, fields, parent=0):
        """Returns list of all files from DATA table."""

        items_dict = {}
        fields_string = ','.join('%r' % (field,) for field in fields)
        if parent == 0: # get all files
            sql_res = self.conn.execute("select data.id as id, fields.name, \
            data.value as name from data left join fields on data.field_id=\
            fields.id left join relation on data.id=relation.data_id where \
            fields.name in (%s)" % fields_string)
        else: # get files for selected parent
            sql_res = self.conn.execute("select data.id as id, fields.name, \
            data.value as name from data left join fields on data.field_id=\
            fields.id left join relation on data.id=relation.data_id where \
            fields.name in (%s) and relation.struct_id=?" % fields_string, \
            (parent,))
        if sql_res is None:
            return {}
        for _id, fname, fvalue in sql_res.fetchall():
            if not items_dict.has_key(_id):
                items_dict[_id] = {}
            items_dict[_id][fname] = fvalue
        return items_dict

    def get_entry_by_id(self, _id):
        """Returns full entry by it id."""

        sql_res = self.conn.execute("SELECT fields.name, data.value AS name \
        FROM data LEFT JOIN fields ON data.field_id=fields.id LEFT JOIN \
        relation ON data.id=relation.data_id WHERE data.id=?", (_id,))
        if sql_res is None:
            return {}
        else:
            return dict(sql_res.fetchall())