use constants

[meabook] / database / SQLite.py

diff --git a/database/SQLite.py b/database/SQLite.py
index f265298..e8d371b 100644 (file)
--- 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,31 +16,28 @@ 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
     );
-
+    create index i_struct_id on struct(parent);
     commit;
 
-    create index i_fields on fields (id);
-    create index i_data on data (id);
-    create index i_struct_id on parent struct(id);
-    create index i_relation_data on relation(data_id);
-
-    commit;
 """
 
 
@@ -50,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):
@@ -83,8 +84,8 @@ class SQLite:
     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):
@@ -100,7 +101,7 @@ class SQLite:
 
         for field, values in entry.items():
             # update FIELDS table
-            field_id = execute("SELECT id, name FROM fields WHERE name=?", \
+            field_id = execute("SELECT id FROM fields WHERE name=?", \
                 (field,)).fetchone()
             if field_id is None:
                 execute("INSERT INTO fields values(NULL, ?)", (field,))
@@ -114,19 +115,19 @@ class SQLite:
                     (_id, field_id, value))
 
         # update STRUCT table
-        name = entry[TOPLEVEL_FIELD][0]
+        name = entry[FIELD_TOPLEVEL][0]
         parent_id = execute("SELECT id FROM struct WHERE name=? ", \
             (name,)).fetchone()
-        if parent_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,))
             parent_id = execute("SELECT last_insert_rowid()").fetchone()[0]
         else:
             parent_id = parent_id[0]
 
-        name = entry[MIDDLELEVEL_FIELD][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 (MIDDLELEVEL_FIELD)
+        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:
@@ -169,6 +170,17 @@ class SQLite:
                 items_dict[data_id][field_name] = field_value
         return items_dict
 
+    def get_files_by_pattern(self, pattern):
+        """Returns list of all files, matched pattern string."""
+
+        if not pattern:
+            return []
+        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()
+
     def get_entry(self, _id):
         """Returns full entry by it id."""
 
@@ -180,13 +192,3 @@ class SQLite:
                 (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()
-