name text
);
+ create table relation(
+ data_id integer,
+ struct_id integer
+ );
+
create table struct(
id integer primary key,
name text,
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;
execute("DELETE from data")
execute("DELETE from fields")
execute("DELETE from struct")
+ execute("DELETE from relation")
self.conn.commit()
def get_fields(self):
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."""
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."""