added english localization

[meabook] / database / SQLite.py

diff --git a/database/SQLite.py b/database/SQLite.py
index e8d371b..66b25a1 100644 (file)
--- a/database/SQLite.py
+++ b/database/SQLite.py
@@ -150,45 +150,74 @@ class SQLite:
                 WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()
 
     def get_files(self, fields, parent=0):
-        """Returns list of all files from DATA table."""
+        """Finds all files with selected parent"""
 
         items_dict = {}
         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
-            data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
-                data").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()]
-        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
+        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, pattern):
-        """Returns list of all files, matched pattern string."""
+    def get_files_by_pattern(self, fields, key='cn', pattern='', \
+        search_from_start=False):
+        """Returns list of all files from DATA table."""
 
-        if not pattern:
-            return []
+        items_dict = {}
+        if key not in fields:
+            fields.append(str(key))
         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, (field_id,)).fetchall()
+        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(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
+        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()