4 DATABASE_NAME = 'contacts.db'
7 MIDDLELEVEL_FIELD = 'ou'
20 id integer primary key,
25 id integer primary key,
32 create index i_fields on fields (id);
33 create index i_data on data (id);
34 create index i_struct on struct(parent);
42 def __init__(self, basedir):
43 self._path = os.path.join(basedir, DATABASE_NAME)
45 if not os.path.exists(self._path):
48 self.conn = sqlite3.connect(self._path)
51 """Creates new databse."""
53 self.conn = sqlite3.connect(self._path)
54 self.conn.executescript(SCHEMA)
58 """Closes connection with database."""
64 """Save all changes."""
69 """Clear all database tables."""
71 execute = self.conn.execute
72 execute("DELETE from data")
73 execute("DELETE from fields")
74 execute("DELETE from struct")
78 """Returns all fields from FIELDS table."""
80 return [item[0] for item in self.conn.execute( \
81 "SELECT name FROM fields").fetchall()]
83 # operations with DATA table
84 def add_entry(self, entry):
85 """Adds new entry to database."""
87 execute = self.conn.execute
89 if not 'uid' in entry or not 'o' in entry:
93 _id = execute("SELECT MAX(id) FROM data").fetchone()[0] \
98 for field, values in entry.items():
100 if execute("SELECT name FROM fields WHERE name=?", \
101 (field,)).fetchone() is None:
102 execute("INSERT INTO fields values(NULL, ?)", (field,))
106 execute("INSERT INTO data values(?,?,?)", (_id, field, value))
108 # update STRUCT table
109 name = entry[TOPLEVEL_FIELD][0]
110 row_id = execute("SELECT id FROM struct WHERE name=? ", \
112 if row_id is None: # update STRUCT table (TOPLEVEL_FIELD)
113 execute("INSERT INTO struct values(NULL,?,0)", (name,))
114 row_id = execute("SELECT last_insert_rowid()").fetchone()[0]
118 name = entry[MIDDLELEVEL_FIELD][0]
119 sql_res = execute("SELECT id FROM struct WHERE name=? AND parent=?", \
120 (name, row_id)).fetchone()
121 if sql_res is None: # update STRUCT table (MIDDLELEVEL_FIELD)
122 execute("INSERT INTO struct values(NULL,?,?)", (name, row_id))
124 def get_entry(self, value):
125 """Gets full entry imformation from DATA table."""
127 _id = self.conn.execute("SELECT id FROM data WHERE value=?", \
128 (value,)).fetchone()[0]
129 return dict([(key, value) for key, value in self.conn.execute("\
130 SELECT field_id, value FROM data WHERE id=?", (_id,)).fetchall()])
132 def get_toplevel_entries(self):
133 """Returns list of all toplevel entries from DATA table."""
135 return [item[0] for item in self.conn.execute("SELECT name \
136 FROM struct WHERE parent=0 ORDER BY name ASC").fetchall()]
138 def get_middlelevel_entries(self, parent=None):
139 """Returns list of all middlelevel entries from DATA table."""
142 return [item[0] for item in self.conn.execute("SELECT DISTINCT \
143 name FROM struct WHERE parent!=0 ORDER BY name ASC").fetchall()]
145 return [item[0] for item in self.conn.execute("SELECT name FROM \
146 struct WHERE parent IN (SELECT id FROM struct WHERE name=?) \
147 ORDER BY name ASC", (parent,)).fetchall()]
149 def get_lowlevel_entries(self, parent=None):
150 """Returns list of all lowlevel entries from DATA table."""
153 return [item[0] for item in self.conn.execute("SELECT DISTINCT \
154 value FROM data WHERE field_id=? ORDER BY value ASC", \
155 (LOWLEVEL_FIELD,)).fetchall()]
158 return [item[0] for item in self.conn.execute("SELECT value FROM \
159 data WHERE id IN (SELECT id FROM data WHERE value=?) AND field_id='cn' \
160 ORDER BY value ASC", (parent, )).fetchall()]
162 def get_entry_by_id(self, _id):
163 """Returns full entry by it id."""
165 sql_res = self.conn.execute("""SELECT id_field, value FROM data
166 WHERE id=?""", (_id,)).fetchall()
167 return dict([(self.get_field_name_by_id(field_id), value) for field_id, \
170 def get_all_entries(self):
171 """Returns list of all entries."""
173 ids = [item[0] for item in self.conn.execute("""SELECT DISTINCT id \
174 FROM data""").fetchall()]
175 return [self.get_entry_by_id(_id) for _id in ids]
180 if __name__ == "__main__":
182 #db.update_field('test')
183 #print db.get_field_id_by_name('fax')
184 #print db.get_entry_by_id(1)
185 #print db.get_all_entries()