create table data(
id integer,
- field_id integer,
+ field_id text,
value text
);
name text
);
- create table relation (
- data_id integer,
- struct_id integer
- );
-
create table struct(
id integer primary key,
name text,
);
commit;
+
+ create index i_fields on fields (id);
+ create index i_data on data (id);
+ create index i_struct on struct(parent);
+
+ commit;
+
"""
self.conn.commit()
self.conn.close()
- # operations with FIELDS table
- def update_field(self, field_names_list):
- """Adds new field name to database."""
+ def save(self):
+ """Save all changes."""
- fields = [item[0] for item in \
- self.conn.execute("SELECT name from fields").fetchall()]
- for field_name in field_names_list:
- if not field_name in fields:
- self.conn.execute("INSERT INTO fields values(NULL, ?)", \
- (field_name,))
self.conn.commit()
- def get_field_id_by_name(self, field_name):
- """Returns unique field id by its name."""
-
- try:
- return self.conn.execute("SELECT id FROM fields WHERE name=?", \
- (field_name,)).fetchone()[0]
- except TypeError:
- return None
-
- def get_field_name_by_id(self, _id):
- """Returns field name by its id."""
-
- try:
- return self.conn.execute("SELECT name FROM fields WHERE id=?", \
- (_id,)).fetchone()[0]
- except TypeError:
- return None
-
- def get_fields(self):
- """Returns all fields from FIELDS table."""
-
- return [item[0] for item in self.conn.execute( \
- "SELECT name FROM fields").fetchall()]
+ def clear(self):
+ """Clear all database tables."""
+ execute = self.conn.execute
+ execute("DELETE from data")
+ execute("DELETE from fields")
+ execute("DELETE from struct")
+ self.conn.commit()
# operations with DATA table
def add_entry(self, entry):
"""Adds new entry to database."""
- if not 'uid' in entry:
- return
+ execute = self.conn.execute
- # check man existence in DATA table
- if self.conn.execute("SELECT field_id FROM data WHERE value=?", \
- (entry['uid'][0],)).fetchone():
+ if not 'uid' in entry or not 'o' in entry:
return
- self.update_field(entry.keys())
- # check for empty table
try:
- _id = self.conn.execute("SELECT MAX(id) FROM data").fetchone()[0] \
+ _id = execute("SELECT MAX(id) FROM data").fetchone()[0] \
+ 1
except TypeError:
_id = 1
- # add entry (fill DATA table)
- for field_name in entry:
- for value in entry[field_name]:
- self.conn.execute("INSERT INTO data values(?,?,?)", \
- (_id, field_name, value))
+ for field, values in entry.items():
+ # update FIELDS table
+ if execute("SELECT name FROM fields WHERE name=?", \
+ (field,)).fetchone() is None:
+ execute("INSERT INTO fields values(NULL, ?)", (field,))
+
+ # update DATA table
+ for value in values:
+ execute("INSERT INTO data values(?,?,?)", (_id, field, value))
+
+ # update STRUCT table
+ name = entry[TOPLEVEL_FIELD][0]
+ row_id = execute("SELECT id FROM struct WHERE name=? ", \
+ (name,)).fetchone()
+ if row_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]
+ else:
+ row_id = row_id[0]
- self.conn.commit()
+ 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."""
def get_toplevel_entries(self):
"""Returns list of all toplevel entries from DATA table."""
- return set([item[0] for item in self.conn.execute("SELECT value FROM \
- data WHERE field_id=?", (TOPLEVEL_FIELD,)).fetchall()])
+ return [item[0] for item in self.conn.execute("SELECT name \
+ FROM struct WHERE parent=0 ORDER BY name ASC").fetchall()]
- def get_middlelevel_entries(self, toplevel_item=None):
+ def get_middlelevel_entries(self, parent=None):
"""Returns list of all middlelevel entries from DATA table."""
- if not toplevel_item:
- return set([item[0] for item in self.conn.execute("SELECT value \
- FROM data WHERE field_id=?", (MIDDLELEVEL_FIELD,)).fetchall()])
+ 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()]
else:
- ids = [item[0] for item in self.conn.execute("SELECT id FROM data \
- WHERE value=?", (toplevel_item,)).fetchall()]
- return set([self.conn.execute("SELECT value FROM data WHERE \
- field_id=? AND id=?", (MIDDLELEVEL_FIELD, _id)).fetchone()[0] \
- for _id in ids])
+ 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()]
- def get_lowlevel_entries(self):
+ def get_lowlevel_entries(self, parent=None):
"""Returns list of all lowlevel entries from DATA table."""
- return set([item[0] for item in self.conn.execute("SELECT value FROM \
- data WHERE field_id=?", (LOWLEVEL_FIELD,)).fetchall()])
-
+ 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()]
+ 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):
"""Returns full entry by it id."""
def get_all_entries(self):
"""Returns list of all entries."""
- ids = set([item[0] for item in self.conn.execute( \
- """SELECT id FROM data""").fetchall()])
+ 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]
projects / meabook / commitdiff