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 index i_data on data (id);
+
+ create table fields(
+ id integer primary key,
+ name text
+ );
+ create index i_fields on fields (id);
+
+ create table relation(
+ data_id integer,
+ struct_id integer
+ );
+ create index i_relation_data on relation(data_id);
+
+ create table struct(
+ id integer primary key,
+ name text,
+ parent integer
+ );
+ create index i_struct_id on struct(parent);
+ commit;
+
+"""
+
class SQLite:
def __init__(self, basedir):
if not os.path.exists(self._path):
self.new()
else:
- self.conn = sqlite3.connect(self._path)
+ self.connect()
+
+ def connect(self):
+ """Connects to database."""
+
+ self.conn = sqlite3.connect(self._path, isolation_level="EXCLUSIVE")
def new(self):
"""Creates new databse."""
- self.conn = sqlite3.connect(self._path)
- self.conn.execute("""CREATE TABLE data (user_id int, field_id int, \
- value str)""")
- self.conn.execute("""CREATE TABLE field (id int primary key, name str)""")
- self.conn.execute("""CREATE TABLE relation (data_id int, \
- struct_id int)""")
- self.conn.execute("""CREATE TABLE struct (id int, name str, \
- parent_id int)""")
- self.conn.commit()
+ self.connect()
+ self.conn.executescript(SCHEMA)
def close(self):
"""Closes connection with database."""
- self.conn.commit()
self.conn.close()
- def update_field(self, fname):
- """Adds new field to database."""
+ 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 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))
- fields = self.conn.execute("""SELECT name from field""").fetchall()
- if not fname in fields:
- self.conn.execute("""INSERT INTO field values((SELECT max(id)
- FROM field)+1, ?)""", (fname,))
+ # 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_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."""
-if __name__ == "__main__":
- db = SQLite('/tmp/')
- db.update_field('test')
- db.close()
+ items_dict = {}
+ execute = self.conn.execute
+ fields = execute("SELECT id, name FROM fields WHERE name IN (%s)" \
+ % ','.join('%r' % (field,) for field in fields)).fetchall()
+ if parent == 0: # get all files
+ data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
+ data").fetchall()]
+ else: # get files for selected parent
+ data_ids = [_id[0] for _id in execute("SELECT data_id FROM \
+ relation WHERE struct_id=?", (parent,)).fetchall()]
+ for data_id in data_ids:
+ items_dict[data_id] = {}
+ for field_id, field_name in fields:
+ field_value = execute("SELECT value FROM data WHERE id=? \
+ AND field_id=?", (data_id, field_id)).fetchone()[0]
+ items_dict[data_id][field_name] = field_value
+ return items_dict
+ def get_entry(self, _id):
+ """Returns full entry by it id."""
+ execute = self.conn.execute
+ entry_dict = {}
+ for field_id, value in execute("SELECT field_id, value FROM data WHERE \
+ id=?", (_id,)).fetchall():
+ field_name = execute("SELECT name FROM fields WHERE id=?", \
+ (field_id,)).fetchone()[0]
+ entry_dict[field_name] = value
+ return entry_dict
projects / meabook / blobdiff