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;
-
"""
# 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)
"""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()
+