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 # operations with DATA table
79 def add_entry(self, entry):
80 """Adds new entry to database."""
82 execute = self.conn.execute
84 if not 'uid' in entry or not 'o' in entry:
88 _id = execute("SELECT MAX(id) FROM data").fetchone()[0] \
93 for field, values in entry.items():
95 if execute("SELECT name FROM fields WHERE name=?", \
96 (field,)).fetchone() is None:
97 execute("INSERT INTO fields values(NULL, ?)", (field,))
101 execute("INSERT INTO data values(?,?,?)", (_id, field, value))
103 # update STRUCT table
104 name = entry[TOPLEVEL_FIELD][0]
105 row_id = execute("SELECT id FROM struct WHERE name=? ", \
107 if row_id is None: # update STRUCT table (TOPLEVEL_FIELD)
108 execute("INSERT INTO struct values(NULL,?,0)", (name,))
109 row_id = execute("SELECT last_insert_rowid()").fetchone()[0]
113 name = entry[MIDDLELEVEL_FIELD][0]
114 sql_res = execute("SELECT id FROM struct WHERE name=? AND parent=?", \
115 (name, row_id)).fetchone()
116 if sql_res is None: # update STRUCT table (MIDDLELEVEL_FIELD)
117 execute("INSERT INTO struct values(NULL,?,?)", (name, row_id))
119 def get_entry(self, value):
120 """Gets full entry imformation from DATA table."""
122 _id = self.conn.execute("SELECT id FROM data WHERE value=?", \
123 (value,)).fetchone()[0]
124 return dict([(key, value) for key, value in self.conn.execute("\
125 SELECT field_id, value FROM data WHERE id=?", (_id,)).fetchall()])
127 def get_toplevel_entries(self):
128 """Returns list of all toplevel entries from DATA table."""
130 return [item[0] for item in self.conn.execute("SELECT name \
131 FROM struct WHERE parent=0 ORDER BY name ASC").fetchall()]
133 def get_middlelevel_entries(self, parent=None):
134 """Returns list of all middlelevel entries from DATA table."""
137 return [item[0] for item in self.conn.execute("SELECT DISTINCT \
138 name FROM struct WHERE parent!=0 ORDER BY name ASC").fetchall()]
140 return [item[0] for item in self.conn.execute("SELECT name FROM \
141 struct WHERE parent IN (SELECT id FROM struct WHERE name=?) \
142 ORDER BY name ASC", (parent,)).fetchall()]
144 def get_lowlevel_entries(self, parent=None):
145 """Returns list of all lowlevel entries from DATA table."""
148 return [item[0] for item in self.conn.execute("SELECT DISTINCT \
149 value FROM data WHERE field_id=? ORDER BY value ASC", \
150 (LOWLEVEL_FIELD,)).fetchall()]
153 return [item[0] for item in self.conn.execute("SELECT value FROM \
154 data WHERE id IN (SELECT id FROM data WHERE value=?) AND field_id='cn' \
155 ORDER BY value ASC", (parent, )).fetchall()]
157 def get_entry_by_id(self, _id):
158 """Returns full entry by it id."""
160 sql_res = self.conn.execute("""SELECT id_field, value FROM data
161 WHERE id=?""", (_id,)).fetchall()
162 return dict([(self.get_field_name_by_id(field_id), value) for field_id, \
165 def get_all_entries(self):
166 """Returns list of all entries."""
168 ids = [item[0] for item in self.conn.execute("""SELECT DISTINCT id \
169 FROM data""").fetchall()]
170 return [self.get_entry_by_id(_id) for _id in ids]
175 if __name__ == "__main__":
177 #db.update_field('test')
178 #print db.get_field_id_by_name('fax')
179 #print db.get_entry_by_id(1)
180 #print db.get_all_entries()