From d7262165e9e7799ac5e552eefcf06bcf9f9de008 Mon Sep 17 00:00:00 2001 From: Max Usachev Date: Tue, 15 Jun 2010 10:37:52 +0300 Subject: [PATCH] database optimization --- database/SQLite.py | 69 ++++++++++++++++++++++++++-------------------------- 1 file changed, 34 insertions(+), 35 deletions(-) diff --git a/database/SQLite.py b/database/SQLite.py index 7940f4b..f265298 100644 --- a/database/SQLite.py +++ b/database/SQLite.py @@ -36,11 +36,10 @@ SCHEMA = """ 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 parent struct(id); + create index i_relation_data on relation(data_id); commit; - """ @@ -136,14 +135,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) @@ -161,33 +152,41 @@ class SQLite: """Returns list of all files from DATA table.""" items_dict = {} - fields_string = ','.join('%r' % (field,) for field in fields) + 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 - 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) + data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \ + data").fetchall()] 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 + 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_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 = {} + 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 + + + +# test perfomance +if __name__ == "__main__": + import profile + database = SQLite('/tmp/') + print database.get_entry(8) + database.close() + -- 1.7.9.5