4 DATABASE_NAME = 'contacts.db'
7 MIDDLELEVEL_FIELD = 'ou'
20 id integer primary key,
24 create table relation(
30 id integer primary key,
37 create index i_fields on fields (id);
38 create index i_data on data (id);
39 create index i_struct on struct(parent);
40 create index i_relation on relation(data_id);
48 def __init__(self, basedir):
49 self._path = os.path.join(basedir, DATABASE_NAME)
51 if not os.path.exists(self._path):
54 self.conn = sqlite3.connect(self._path)
57 """Creates new databse."""
59 self.conn = sqlite3.connect(self._path)
60 self.conn.executescript(SCHEMA)
64 """Closes connection with database."""
70 """Save all changes."""
75 """Clear all database tables."""
77 execute = self.conn.execute
78 execute("DELETE from data")
79 execute("DELETE from fields")
80 execute("DELETE from struct")
81 execute("DELETE from relation")
85 """Returns all fields from FIELDS table."""
87 return [item[0] for item in self.conn.execute( \
88 "SELECT name FROM fields").fetchall()]
90 # operations with DATA table
91 def add_entry(self, entry):
92 """Adds new entry to database."""
94 execute = self.conn.execute
96 if not 'uid' in entry or not 'o' in entry:
100 _id = execute("SELECT MAX(id) FROM data").fetchone()[0] \
105 for field, values in entry.items():
106 # update FIELDS table
107 field_id = execute("SELECT id, name FROM fields WHERE name=?", \
110 execute("INSERT INTO fields values(NULL, ?)", (field,))
111 field_id = execute("SELECT last_insert_rowid()").fetchone()[0]
113 field_id = field_id[0]
117 execute("INSERT INTO data values(?,?,?)", (_id, field_id, value))
119 # update STRUCT table
120 name = entry[TOPLEVEL_FIELD][0]
121 parent_id = execute("SELECT id FROM struct WHERE name=? ", \
123 if parent_id is None: # update STRUCT table (TOPLEVEL_FIELD)
124 execute("INSERT INTO struct values(NULL,?,0)", (name,))
125 parent_id = execute("SELECT last_insert_rowid()").fetchone()[0]
127 parent_id = parent_id[0]
129 name = entry[MIDDLELEVEL_FIELD][0]
130 child_id = execute("SELECT id FROM struct WHERE name=? AND parent=?", \
131 (name, parent_id)).fetchone()
132 if child_id is None: # update STRUCT table (MIDDLELEVEL_FIELD)
133 execute("INSERT INTO struct values(NULL,?,?)", (name, parent_id))
134 child_id = execute("SELECT last_insert_rowid()").fetchone()[0]
136 child_id = child_id[0]
138 # update RELATION table
139 execute("INSERT INTO relation values(?,?)", (_id, child_id))
141 def get_entry(self, value):
142 """Gets full entry imformation from DATA table."""
144 _id = self.conn.execute("SELECT id FROM data WHERE value=?", \
145 (value,)).fetchone()[0]
146 return dict([(key, value) for key, value in self.conn.execute("\
147 SELECT field_id, value FROM data WHERE id=?", (_id,)).fetchall()])
149 def get_structs(self, parent=0):
150 """Returns list of all struct from STRUCT table."""
152 return self.conn.execute("SELECT DISTINCT name, id FROM struct \
153 WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()
155 def get_middlelevel_entries(self, parent=None):
156 """Returns list of all middlelevel entries from DATA table."""
159 return self.conn.execute("SELECT DISTINCT name, id FROM struct \
160 WHERE parent!=0 ORDER BY name ASC").fetchall()
162 return self.conn.execute("SELECT name, id FROM struct WHERE \
163 parent=? ORDER BY name ASC", (parent[1],)).fetchall()
165 def get_lowlevel_entries(self, parent=None):
166 """Returns list of all lowlevel entries from DATA table."""
169 return self.conn.execute("SELECT DISTINCT value FROM data WHERE \
170 field_id=? ORDER BY value ASC", (LOWLEVEL_FIELD,)).fetchall()
173 return self.conn.execute("SELECT value FROM \
174 data WHERE id IN (SELECT DISTINCT id FROM data WHERE value=?) AND field_id='cn' \
175 ORDER BY value ASC", (parent, )).fetchall()
177 def get_entry_by_id(self, _id):
178 """Returns full entry by it id."""
180 sql_res = self.conn.execute("""SELECT id_field, value FROM data
181 WHERE id=?""", (_id,)).fetchall()
182 return dict([(self.get_field_name_by_id(field_id), value) for field_id, \
185 def get_all_entries(self):
186 """Returns list of all entries."""
188 ids = [item[0] for item in self.conn.execute("""SELECT DISTINCT id \
189 FROM data""").fetchall()]
190 return [self.get_entry_by_id(_id) for _id in ids]
195 if __name__ == "__main__":
197 #db.update_field('test')
198 #print db.get_field_id_by_name('fax')
199 #print db.get_entry_by_id(1)
200 #print db.get_all_entries()