+#!/usr/bin/python
+# -*- coding: utf-8 -*-
+
+
import os
import sqlite3
+from meabook.constants import *
DATABASE_NAME = 'contacts.db'
-TOPLEVEL_FIELD = 'o'
-MIDDLELEVEL_FIELD = 'ou'
-LOWLEVEL_FIELD = 'cn'
-
SCHEMA = """
begin;
create table data(
id integer,
- field_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 (
+ 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;
+
"""
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.connect()
self.conn.executescript(SCHEMA)
- self.conn.commit()
def close(self):
"""Closes connection with database."""
- self.conn.commit()
self.conn.close()
- # operations with FIELDS table
- def update_field(self, field_names_list):
- """Adds new field name to database."""
+ def save(self):
+ """Save all changes."""
- fields = [item[0] for item in \
- self.conn.execute("SELECT name from fields").fetchall()]
- for field_name in field_names_list:
- if not field_name in fields:
- self.conn.execute("INSERT INTO fields values(NULL, ?)", \
- (field_name,))
self.conn.commit()
- def get_field_id_by_name(self, field_name):
- """Returns unique field id by its name."""
-
- try:
- return self.conn.execute("SELECT id FROM fields WHERE name=?", \
- (field_name,)).fetchone()[0]
- except TypeError:
- return None
+ def clear(self):
+ """Clear all database tables."""
- def get_field_name_by_id(self, _id):
- """Returns field name by its id."""
+ execute = self.conn.execute
+ execute("DELETE from data")
+ execute("DELETE from fields")
+ execute("DELETE from struct")
+ execute("DELETE from relation")
+ self.conn.commit()
- try:
- return self.conn.execute("SELECT name FROM fields WHERE id=?", \
- (_id,)).fetchone()[0]
- except TypeError:
- return None
+ def get_fields(self):
+ """Returns all fields from FIELDS table."""
+ return [item[0] for item in self.conn.execute("SELECT name FROM \
+ fields").fetchall() if item[0]!='image']
# operations with DATA table
def add_entry(self, entry):
"""Adds new entry to database."""
- if not 'uid' in entry:
- return
-
- # check man existence in DATA table
- if self.conn.execute("SELECT field_id FROM data WHERE value=?", \
- (entry['uid'][0],)).fetchone():
- return
+ execute = self.conn.execute
- self.update_field(entry.keys())
- # check for empty table
try:
- _id = self.conn.execute("SELECT MAX(id) FROM data").fetchone()[0] \
+ _id = execute("SELECT MAX(id) FROM data").fetchone()[0] \
+ 1
except TypeError:
_id = 1
- # add entry (fill DATA table)
- for field_name in entry:
- for value in entry[field_name]:
- self.conn.execute("INSERT INTO data values(?,?,?)", \
- (_id, field_name, value))
-
- self.conn.commit()
-
- def get_toplevel_entries(self):
- """Returns list of all toplevel entries from DATA table."""
-
- return set([item[0] for item in self.conn.execute("SELECT value FROM \
- data WHERE field_id=?", (TOPLEVEL_FIELD,)).fetchall()])
-
- def get_middlelevel_entries(self, toplevel_item=None):
- """Returns list of all middlelevel entries from DATA table."""
-
- if not toplevel_item:
- return set([item[0] for item in self.conn.execute("SELECT value \
- FROM data WHERE field_id=?", (MIDDLELEVEL_FIELD,)).fetchall()])
+ 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))
+
+ # update STRUCT table
+ name = entry[FIELD_TOPLEVEL][0]
+ parent_id = execute("SELECT id FROM struct WHERE name=? ", \
+ (name,)).fetchone()
+ if parent_id is None: # update STRUCT table (FIELD_TOPLEVEL)
+ execute("INSERT INTO struct values(NULL,?,0)", (name,))
+ parent_id = execute("SELECT last_insert_rowid()").fetchone()[0]
else:
- ids = [item[0] for item in self.conn.execute("SELECT id FROM data \
- WHERE value=?", (toplevel_item,)).fetchall()]
- return set([self.conn.execute("SELECT value FROM data WHERE \
- field_id=? AND id=?", (MIDDLELEVEL_FIELD, _id)).fetchone()[0] \
- for _id in ids])
-
- def get_lowlevel_entries(self):
- """Returns list of all lowlevel entries from DATA table."""
+ parent_id = parent_id[0]
+
+ name = entry[FIELD_MIDDLELEVEL][0]
+ child_id = execute("SELECT id FROM struct WHERE name=? AND parent=?", \
+ (name, parent_id)).fetchone()
+ if child_id is None: # update STRUCT table (FIELD_MIDDLELEVEL)
+ execute("INSERT INTO struct values(NULL,?,?)", (name, parent_id))
+ child_id = execute("SELECT last_insert_rowid()").fetchone()[0]
+ else:
+ child_id = child_id[0]
- return set([item[0] for item in self.conn.execute("SELECT value FROM \
- data WHERE field_id=?", (LOWLEVEL_FIELD,)).fetchall()])
+ # 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.
+ """
- def get_entry_by_id(self, _id):
+ 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):
+ """Finds all files with selected parent"""
+
+ items_dict = {}
+ execute = self.conn.execute
+ fields = dict(execute("SELECT id, name FROM fields WHERE name IN (%s)" \
+ % ','.join('%r' % (field,) for field in fields)).fetchall())
+ """
+ query = "SELECT id, field_id, value FROM data \
+ left join relation on relation.data_id=data.id \
+ where relation.struct_id=? and field_id in (%s)"
+ """
+ query = "SELECT id, field_id, value FROM data \
+ WHERE id IN (SELECT data_id FROM relation WHERE struct_id=?) \
+ AND field_id IN (%s)"
+ data = execute(query % ','.join('%r' % f for f in fields.keys()), \
+ (parent,)).fetchall()
+ for data_id, field_id, value in data:
+ if not items_dict.has_key(data_id):
+ items_dict[data_id] = {}
+ items_dict[data_id][fields[int(field_id)]] = value
+ return items_dict
+
+ def get_files_by_pattern(self, fields, key='cn', pattern='', \
+ search_from_start=False):
+ """Returns list of all files from DATA table."""
+
+ items_dict = {}
+ if key not in fields:
+ fields.append(str(key))
+ execute = self.conn.execute
+ fields = dict(execute("SELECT id, name FROM fields WHERE name IN (%s)" \
+ % ','.join('%r' % (field,) for field in fields)).fetchall())
+ key_id = 1
+ for k, val in fields.items():
+ if val == key:
+ key_id = k
+ break
+ if search_from_start:
+ s = '%s%%' % pattern.capitalize()
+ else:
+ s = '%%%s%%' % pattern
+ data = execute("SELECT id, field_id, value FROM data WHERE id IN \
+ (SELECT id FROM data WHERE value LIKE '%s' AND field_id=? LIMIT \
+ 50) AND field_id in (%s)" % (s, ','.join('%r' % f for f in \
+ fields.keys())), (key_id,)).fetchall()
+ for data_id, field_id, value in data:
+ if not items_dict.has_key(data_id):
+ items_dict[data_id] = {}
+ items_dict[data_id][fields[int(field_id)]] = value
+ return items_dict
+
+ def get_entry(self, _id):
"""Returns full entry by it id."""
- sql_res = self.conn.execute("""SELECT id_field, value FROM data
- WHERE id=?""", (_id,)).fetchall()
- return dict([(self.get_field_name_by_id(field_id), value) for field_id, \
- value in sql_res])
-
- def get_all_entries(self):
- """Returns list of all entries."""
-
- ids = set([item[0] for item in self.conn.execute( \
- """SELECT id FROM data""").fetchall()])
- return [self.get_entry_by_id(_id) for _id in ids]
-
+ execute = self.conn.execute
+ entry_dict = {}
+ entry_dict = dict(execute("SELECT fields.name, value FROM data \
+ LEFT JOIN fields ON fields.id=data.field_id \
+ WHERE data.id=?", (_id,)).fetchall())
+ sql_res = execute('SELECT photo FROM photo WHERE data_id=?', \
+ (_id,)).fetchone()
+ if sql_res is not None:
+ entry_dict['image'] = sql_res[0]
+ return entry_dict
if __name__ == "__main__":
- db = SQLite('/tmp/')
- #db.update_field('test')
- #print db.get_field_id_by_name('fax')
- #print db.get_entry_by_id(1)
- #print db.get_all_entries()
+ db = SQLite('/tmp')
+ # put test code here
db.close()
-
-
projects / meabook / blobdiff