X-Git-Url: http://git.maemo.org/git/?a=blobdiff_plain;f=database%2FSQLite.py;h=66b25a11081e6daf652c5f80f591709157b2976d;hb=HEAD;hp=7940f4bde464a6d6bcb4a13e66c2d726f46fd0ab;hpb=ab87f83f5c67cfa07ea4bc7f2742711c290fbfbf;p=meabook diff --git a/database/SQLite.py b/database/SQLite.py index 7940f4b..66b25a1 100644 --- a/database/SQLite.py +++ b/database/SQLite.py @@ -1,12 +1,13 @@ +#!/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; @@ -15,30 +16,26 @@ SCHEMA = """ 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 ); - - 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); - + create index i_struct_id on struct(parent); commit; """ @@ -51,19 +48,22 @@ class SQLite: 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): @@ -84,8 +84,8 @@ class SQLite: 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): @@ -101,7 +101,7 @@ class SQLite: 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,)) @@ -115,19 +115,19 @@ class SQLite: (_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: @@ -136,14 +136,6 @@ class SQLite: # 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) @@ -158,36 +150,74 @@ class SQLite: 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 = {} - 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 + 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_by_id(self, _id): + def get_entry(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()) + 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') + # put test code here + db.close()