+#!/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;
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;
- create index i_fields on fields (id);
- create index i_data on data (id);
- create index i_struct_id on parent struct(id);
- create index i_relation_data on relation(data_id);
-
- 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()
def save(self):
def get_fields(self):
"""Returns all fields from FIELDS table."""
- return [item[0] for item in self.conn.execute( \
- "SELECT name FROM fields").fetchall()]
+ 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):
for field, values in entry.items():
# update FIELDS table
- field_id = execute("SELECT id, name FROM fields WHERE name=?", \
+ field_id = execute("SELECT id FROM fields WHERE name=?", \
(field,)).fetchone()
if field_id is None:
execute("INSERT INTO fields values(NULL, ?)", (field,))
(_id, field_id, value))
# update STRUCT table
- name = entry[TOPLEVEL_FIELD][0]
+ name = entry[FIELD_TOPLEVEL][0]
parent_id = execute("SELECT id FROM struct WHERE name=? ", \
(name,)).fetchone()
- if parent_id is None: # update STRUCT table (TOPLEVEL_FIELD)
+ 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:
parent_id = parent_id[0]
- name = entry[MIDDLELEVEL_FIELD][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 (MIDDLELEVEL_FIELD)
+ 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:
# 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)
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):
+
+ 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 id, field_id, value in data:
+ if not items_dict.has_key(id):
+ items_dict[id] = {}
+ items_dict[id][fields[int(field_id)]] = value
+ return items_dict
+
- def get_files(self, fields, parent=0):
+
+ def get_files(self, fields, parent=0, pattern=''):
"""Returns list of all files from DATA table."""
items_dict = {}
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()]
data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
- data").fetchall()]
+ data WHERE value LIKE '%s%%' LIMIT 50" % pattern.capitalize()).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()]
items_dict[data_id][field_name] = field_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 = fields + (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 id, field_id, value in data:
+ if not items_dict.has_key(id):
+ items_dict[id] = {}
+ items_dict[id][fields[int(field_id)]] = value
+ return items_dict
+
+
+ def get_files_by_pattern(self, pattern):
+ """Returns list of all files, matched pattern string."""
+
+ if not pattern:
+ return []
+ execute = self.conn.execute
+ field_id = execute("SELECT id FROM fields WHERE name=?", \
+ ('cn',)).fetchone()[0]
+ return execute("SELECT id, value FROM data WHERE value LIKE '%s%%' \
+ AND field_id=? LIMIT 50" % pattern.capitalize(), (field_id,)).fetchall()
+
def get_entry(self, _id):
"""Returns full entry by it id."""
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
+
+ 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
+ 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
-
-
-# test perfomance
if __name__ == "__main__":
- import profile
- database = SQLite('/tmp/')
- print database.get_entry(8)
- database.close()
-
+ db = SQLite('/tmp')
+ #print db.get_files(('cn', 'o', 'ou'), 0)
+ print db._get_files_by_pattern(('cn', 'o', 'ou'), 'cn', 'Ра', True)