database optimization

diff --git a/database/SQLite.py b/database/SQLite.py
index 7940f4b..f265298 100644 (file)
--- 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()
+