4 DATABASE_NAME = 'contacts.db'
7 MIDDLELEVEL_FIELD = 'ou'
20 id integer primary key,
24 create table relation (
30 id integer primary key,
40 def __init__(self, basedir):
41 self._path = os.path.join(basedir, DATABASE_NAME)
43 if not os.path.exists(self._path):
46 self.conn = sqlite3.connect(self._path)
49 """Creates new databse."""
51 self.conn = sqlite3.connect(self._path)
52 self.conn.executescript(SCHEMA)
56 """Closes connection with database."""
61 # operations with FIELDS table
62 def update_field(self, field_names_list):
63 """Adds new field name to database."""
65 fields = [item[0] for item in \
66 self.conn.execute("SELECT name from fields").fetchall()]
67 for field_name in field_names_list:
68 if not field_name in fields:
69 self.conn.execute("INSERT INTO fields values(NULL, ?)", \
73 def get_field_id_by_name(self, field_name):
74 """Returns unique field id by its name."""
77 return self.conn.execute("SELECT id FROM fields WHERE name=?", \
78 (field_name,)).fetchone()[0]
82 def get_field_name_by_id(self, _id):
83 """Returns field name by its id."""
86 return self.conn.execute("SELECT name FROM fields WHERE id=?", \
92 """Returns all fields from FIELDS table."""
94 return [item[0] for item in self.conn.execute( \
95 "SELECT name FROM fields").fetchall()]
99 # operations with DATA table
100 def add_entry(self, entry):
101 """Adds new entry to database."""
103 if not 'uid' in entry:
106 # check man existence in DATA table
107 if self.conn.execute("SELECT field_id FROM data WHERE value=?", \
108 (entry['uid'][0],)).fetchone():
111 self.update_field(entry.keys())
112 # check for empty table
114 _id = self.conn.execute("SELECT MAX(id) FROM data").fetchone()[0] \
119 # add entry (fill DATA table)
120 for field_name in entry:
121 for value in entry[field_name]:
122 self.conn.execute("INSERT INTO data values(?,?,?)", \
123 (_id, field_name, value))
127 def get_entry(self, value):
128 """Gets full entry imformation from DATA table."""
130 _id = self.conn.execute("SELECT id FROM data WHERE value=?", \
131 (value,)).fetchone()[0]
132 return dict([(key, value) for key, value in self.conn.execute("\
133 SELECT field_id, value FROM data WHERE id=?", (_id,)).fetchall()])
135 def get_toplevel_entries(self):
136 """Returns list of all toplevel entries from DATA table."""
138 return set([item[0] for item in self.conn.execute("SELECT value FROM \
139 data WHERE field_id=?", (TOPLEVEL_FIELD,)).fetchall()])
141 def get_middlelevel_entries(self, toplevel_item=None):
142 """Returns list of all middlelevel entries from DATA table."""
144 if not toplevel_item:
145 return set([item[0] for item in self.conn.execute("SELECT value \
146 FROM data WHERE field_id=?", (MIDDLELEVEL_FIELD,)).fetchall()])
148 ids = [item[0] for item in self.conn.execute("SELECT id FROM data \
149 WHERE value=?", (toplevel_item,)).fetchall()]
150 return set([self.conn.execute("SELECT value FROM data WHERE \
151 field_id=? AND id=?", (MIDDLELEVEL_FIELD, _id)).fetchone()[0] \
154 def get_lowlevel_entries(self):
155 """Returns list of all lowlevel entries from DATA table."""
157 return set([item[0] for item in self.conn.execute("SELECT value FROM \
158 data WHERE field_id=?", (LOWLEVEL_FIELD,)).fetchall()])
161 def get_entry_by_id(self, _id):
162 """Returns full entry by it id."""
164 sql_res = self.conn.execute("""SELECT id_field, value FROM data
165 WHERE id=?""", (_id,)).fetchall()
166 return dict([(self.get_field_name_by_id(field_id), value) for field_id, \
169 def get_all_entries(self):
170 """Returns list of all entries."""
172 ids = set([item[0] for item in self.conn.execute( \
173 """SELECT id FROM data""").fetchall()])
174 return [self.get_entry_by_id(_id) for _id in ids]
179 if __name__ == "__main__":
181 #db.update_field('test')
182 #print db.get_field_id_by_name('fax')
183 #print db.get_entry_by_id(1)
184 #print db.get_all_entries()