X-Git-Url: http://git.maemo.org/git/?a=blobdiff_plain;f=database%2FSQLite.py;h=66b25a11081e6daf652c5f80f591709157b2976d;hb=HEAD;hp=f26d1763cdaee0485ed81050bb2be5cb6bcc4154;hpb=18eef3299409c6f8da9b15e4d551f894d1c223c9;p=meabook

diff --git a/database/SQLite.py b/database/SQLite.py
index f26d176..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,24 +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_struct_id on struct(parent);
     commit;
 
 """
@@ -45,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):
@@ -72,13 +78,14 @@ class SQLite:
         execute("DELETE from data")
         execute("DELETE from fields")
         execute("DELETE from struct")
+        execute("DELETE from relation")
         self.conn.commit()
 
     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):
@@ -86,9 +93,6 @@ class SQLite:
 
         execute = self.conn.execute
 
-        if not 'uid' in entry or not 'o' in entry:
-            return
-
         try:
             _id = execute("SELECT MAX(id) FROM data").fetchone()[0] \
                 + 1
@@ -97,92 +101,123 @@ class SQLite:
 
         for field, values in entry.items():
             # update FIELDS table
-            if execute("SELECT name FROM fields WHERE name=?", \
-                (field,)).fetchone() is None:
+            field_id = execute("SELECT id FROM fields WHERE name=?", \
+                (field,)).fetchone()
+            if field_id is None:
                 execute("INSERT INTO fields values(NULL, ?)", (field,))
+                field_id = execute("SELECT last_insert_rowid()").fetchone()[0]
+            else:
+                field_id = field_id[0]
 
             # update DATA table
             for value in values:
-                execute("INSERT INTO data values(?,?,?)", (_id, field, value))
+                execute("INSERT INTO data values(?,?,?)", \
+                    (_id, field_id, value))
 
         # update STRUCT table
-        name = entry[TOPLEVEL_FIELD][0]
-        row_id = execute("SELECT id FROM struct WHERE name=? ", \
+        name = entry[FIELD_TOPLEVEL][0]
+        parent_id = execute("SELECT id FROM struct WHERE name=? ", \
             (name,)).fetchone()
-        if row_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,))
-            row_id = execute("SELECT last_insert_rowid()").fetchone()[0]
+            parent_id = execute("SELECT last_insert_rowid()").fetchone()[0]
         else:
-            row_id = row_id[0]
-
-        name = entry[MIDDLELEVEL_FIELD][0]
-        sql_res = execute("SELECT id FROM struct WHERE name=? AND parent=?", \
-            (name, row_id)).fetchone()
-        if sql_res is None: # update STRUCT table (MIDDLELEVEL_FIELD)
-             execute("INSERT INTO struct values(NULL,?,?)", (name, row_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_toplevel_entries(self):
-        """Returns list of all toplevel entries from DATA table."""
+            parent_id = parent_id[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 (FIELD_MIDDLELEVEL)
+            execute("INSERT INTO struct values(NULL,?,?)", (name, parent_id))
+            child_id = execute("SELECT last_insert_rowid()").fetchone()[0]
+        else:
+            child_id = child_id[0]
 
-        return [item[0] for item in self.conn.execute("SELECT name \
-            FROM struct WHERE parent=0 ORDER BY name ASC").fetchall()]
+        # update RELATION table
+        execute("INSERT INTO relation values(?,?)", (_id, child_id))
 
-    def get_middlelevel_entries(self, parent=None):
-        """Returns list of all middlelevel entries from DATA table."""
+    def get_folders(self, parent=None):
+        """
+        Returns list of all folders (items with folders and files)
+        from STRUCT table.
+        """
 
-        if not parent:
-            return [item[0] for item in self.conn.execute("SELECT DISTINCT \
-                name FROM struct WHERE parent!=0 ORDER BY name ASC").fetchall()]
+        if parent is None:  # return all folders on level2
+            return self.conn.execute("SELECT DISTINCT name, id FROM struct \
+                WHERE parent!=0 ORDER BY name ASC").fetchall()
         else:
-            return [item[0] for item in self.conn.execute("SELECT name FROM \
-                struct WHERE parent IN (SELECT id FROM struct WHERE name=?) \
-                ORDER BY name ASC", (parent,)).fetchall()]
+            return self.conn.execute("SELECT DISTINCT name, id FROM struct \
+                WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()
 
-    def get_lowlevel_entries(self, parent=None):
-        """Returns list of all lowlevel entries from DATA table."""
+    def get_files(self, fields, parent=0):
+        """Finds all files with selected parent"""
 
-        if not parent:
-            return [item[0] for item in self.conn.execute("SELECT DISTINCT \
-                value FROM data WHERE field_id=? ORDER BY value ASC", \
-                (LOWLEVEL_FIELD,)).fetchall()]
+        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 = {}
+        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:
-            print parent
-            return [item[0] for item in self.conn.execute("SELECT value FROM \
-                data WHERE id IN (SELECT id FROM data WHERE value=?) AND field_id='cn' \
-                ORDER BY value ASC", (parent, )).fetchall()]
-
-    def get_entry_by_id(self, _id):
+            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."""
 
-        sql_res = self.conn.execute("""SELECT id_field, value FROM data 
-            WHERE id=?""", (_id,)).fetchall()
-        return dict([(self.get_field_name_by_id(field_id), value) for field_id, \
-            value in sql_res])
-
-    def get_all_entries(self):
-        """Returns list of all entries."""
-
-        ids = [item[0] for item in self.conn.execute("""SELECT DISTINCT id \
-            FROM data""").fetchall()]
-        return [self.get_entry_by_id(_id) for _id in ids]
-
+        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/')
-    #db.update_field('test')
-    #print db.get_field_id_by_name('fax')
-    #print db.get_entry_by_id(1)
-    #print db.get_all_entries()
+    db = SQLite('/tmp')
+    # put test code here
     db.close()
-
-