Commits

Toni Ruža committed 70906de

New schema for the SQLite backend.

Comments (0)

Files changed (1)

roktar/backends/sqlite_backend.py

 import os
 import sqlite3
 from cPickle import loads, dumps
+import time
 
 from _interface import RoktarBackend
 
 schema_definition = """
 CREATE TABLE Entities (
     entity_id INTEGER PRIMARY KEY,
-    entity BLOB
-)
+    time_created INTEGER,
+    cached_value BLOB
+);
+
+CREATE TABLE Fields (
+    field_id INTEGER PRIMARY KEY,
+    entity_id INTEGER,
+    name TEXT,
+    integer_value INTEGER,
+    real_value REAL,
+    text_value TEXT,
+    text_value_for_indexing TEXT
+);
+
+CREATE TABLE Changes (
+    change_uuid TEXT PRIMARY KEY,
+    parent_uuid TEXT,
+    change_time INTEGER,
+    entity_id INTEGER,
+    field_name TEXT,
+    field_type TEXT,
+    old_value BLOB,
+    new_value BLOB
+);
+
+CREATE INDEX Entities_time_created ON Entities (time_created ASC);
+CREATE INDEX Fields_entity_id ON Fields (entity_id ASC);
+CREATE INDEX Fields_integer_value ON Fields (integer_value ASC);
+CREATE INDEX Fields_real_value ON Fields (real_value ASC);
+CREATE INDEX Fields_text_value1 ON Fields (text_value ASC);
+CREATE INDEX Fields_text_value2 ON Fields (text_value_for_indexing ASC);
 """
 
 
     def commit(self):
         self.db_connection.commit()
 
-    def insert(self, **kwargs):
+    def insert(self, table_name, **kwargs):
         self.cursor.execute(
-            "INSERT INTO Entities (%s) VALUES (%s)" % (
+            "INSERT INTO %s (%s) VALUES (%s)" % (
+                table_name,
                 ",".join(kwargs.keys()),
                 ",".join("?" * len(kwargs))
             ), kwargs.values()
             "SELECT MAX(entity_id) AS _max_id FROM Entities"
         ).fetchone()["_max_id"]
 
-    def select(self, **conditions):
+    def select(self, table_name, **conditions):
         if conditions:
             return self.cursor.execute(
-                "SELECT * FROM Entities WHERE %s" % (
-                    " AND ".join(
+                "SELECT * FROM %s WHERE %s" % (
+                    table_name, " AND ".join(
                         ["%s=?" % k for k in conditions.keys()]
                     )
                 ), conditions.values()
             )
         else:
-            return self.cursor.execute("SELECT * FROM Entities")
+            return self.cursor.execute("SELECT * FROM %s" % table_name)
 
-    def select_one(self, key_name, identity):
-        return self.select(**{key_name: identity}).fetchone()
+    def select_one(self, table_name, key_name, identity):
+        return self.select(table_name, **{key_name: identity}).fetchone()
 
 
 class SQLiteBackend(RoktarBackend, BrokerMixin):
             cursor.executescript(schema_definition)
 
         cursor.executescript("""
+            PRAGMA synchronous = OFF;
             PRAGMA temp_store = MEMORY;
             PRAGMA count_changes = OFF;
             PRAGMA locking_mode = EXCLUSIVE;
         self.db_connection.close()
 
     def create_entity(self, data, author=None, data_for_indexing=None):
-        self.insert(entity=dumps(data))
+        self.insert(
+            "Entities", time_created=int(time.time()), cached_value=dumps(data)
+        )
         self.commit()
-        return self.get_last_entity_id()
+        entity_id = self.get_last_entity_id()
+        return entity_id
 
     def get_entity(self, entity_id):
-        data = self.select_one("entity_id", entity_id)
-        return loads(str(data["entity"]))
+        data = self.select_one("Entities", "entity_id", entity_id)
+        return loads(str(data["cached_value"]))