From: Max Usachev Date: Thu, 10 Jun 2010 13:08:32 +0000 (+0300) Subject: more improvements with SQLite X-Git-Url: https://vcs.maemo.org/git/?p=meabook;a=commitdiff_plain;h=7ef832876ff8ba8a0f24ba8bac24cad5dcb3f062 more improvements with SQLite --- diff --git a/database/SQLite.py b/database/SQLite.py index d337060..5cd8bb4 100644 --- a/database/SQLite.py +++ b/database/SQLite.py @@ -12,7 +12,7 @@ SCHEMA = """ create table data( id integer, - field_id integer, + field_id text, value text ); @@ -21,11 +21,6 @@ SCHEMA = """ name text ); - create table relation ( - data_id integer, - struct_id integer - ); - create table struct( id integer primary key, name text, @@ -33,6 +28,13 @@ SCHEMA = """ ); commit; + + create index i_fields on fields (id); + create index i_data on data (id); + create index i_struct on struct(parent); + + commit; + """ @@ -58,71 +60,61 @@ class SQLite: 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.""" @@ -135,28 +127,32 @@ class SQLite: 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.""" @@ -169,8 +165,8 @@ class SQLite: 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]