From: Max Usachev <maxusachev@gmail.com>
Date: Fri, 11 Jun 2010 11:00:42 +0000 (+0300)
Subject: modified database structure
X-Git-Url: http://vcs.maemo.org/git/?p=meabook;a=commitdiff_plain;h=5e68e2f94100c3ab05b8a346361f664b580bbfab

modified database structure
---

diff --git a/database/SQLite.py b/database/SQLite.py
index f26d176..42a0379 100644
--- a/database/SQLite.py
+++ b/database/SQLite.py
@@ -21,6 +21,11 @@ SCHEMA = """
         name text
     );
 
+    create table relation(
+        data_id integer,
+        struct_id integer
+    );
+
     create table struct(
         id integer primary key,
         name text,
@@ -32,6 +37,7 @@ 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);
 
     commit;
 
@@ -72,6 +78,7 @@ 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):
@@ -97,29 +104,39 @@ 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, name 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=? ", \
+        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 (TOPLEVEL_FIELD)
             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]
+            parent_id = parent_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))
+        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)
+            execute("INSERT INTO struct values(NULL,?,?)", (name, parent_id))
+            child_id = execute("SELECT last_insert_rowid()").fetchone()[0]
+        else:
+            child_id = child_id[0]
+
+        # update RELATION table
+        execute("INSERT INTO relation values(?,?)", (_id, child_id))
 
     def get_entry(self, value):
         """Gets full entry imformation from DATA table."""
@@ -129,35 +146,33 @@ class SQLite:
         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."""
+    def get_structs(self, parent=0):
+        """Returns list of all struct from STRUCT table."""
 
-        return [item[0] for item in self.conn.execute("SELECT name \
-            FROM struct WHERE parent=0 ORDER BY name ASC").fetchall()]
+        return self.conn.execute("SELECT DISTINCT name, id FROM struct \
+            WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()
 
     def get_middlelevel_entries(self, parent=None):
         """Returns list of all middlelevel entries from DATA 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()]
+            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 name, id FROM struct WHERE \
+                parent=? ORDER BY name ASC", (parent[1],)).fetchall()
 
     def get_lowlevel_entries(self, parent=None):
         """Returns list of all lowlevel entries from DATA table."""
 
         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()]
+            return self.conn.execute("SELECT DISTINCT value FROM data WHERE \
+                field_id=? ORDER BY value ASC", (LOWLEVEL_FIELD,)).fetchall()
         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()]
+            return self.conn.execute("SELECT value FROM \
+                data WHERE id IN (SELECT DISTINCT id FROM data WHERE value=?) AND field_id='cn' \
+                ORDER BY value ASC", (parent, )).fetchall()
 
     def get_entry_by_id(self, _id):
         """Returns full entry by it id."""