2 # -*- coding: utf-8 -*-
7 from meabook.constants import *
9 DATABASE_NAME = 'contacts.db'
19 create index i_data on data (id);
22 id integer primary key,
25 create index i_fields on fields (id);
27 create table relation(
31 create index i_relation_data on relation(data_id);
34 id integer primary key,
38 create index i_struct_id on struct(parent);
45 def __init__(self, basedir):
46 self._path = os.path.join(basedir, DATABASE_NAME)
48 if not os.path.exists(self._path):
54 """Connects to database."""
56 self.conn = sqlite3.connect(self._path, isolation_level="EXCLUSIVE")
59 """Creates new databse."""
62 self.conn.executescript(SCHEMA)
65 """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("SELECT name FROM \
88 fields").fetchall() if item[0]!='image']
90 # operations with DATA table
91 def add_entry(self, entry):
92 """Adds new entry to database."""
94 execute = self.conn.execute
97 _id = execute("SELECT MAX(id) FROM data").fetchone()[0] \
102 for field, values in entry.items():
103 # update FIELDS table
104 field_id = execute("SELECT id FROM fields WHERE name=?", \
107 execute("INSERT INTO fields values(NULL, ?)", (field,))
108 field_id = execute("SELECT last_insert_rowid()").fetchone()[0]
110 field_id = field_id[0]
114 execute("INSERT INTO data values(?,?,?)", \
115 (_id, field_id, value))
117 # update STRUCT table
118 name = entry[FIELD_TOPLEVEL][0]
119 parent_id = execute("SELECT id FROM struct WHERE name=? ", \
121 if parent_id is None: # update STRUCT table (FIELD_TOPLEVEL)
122 execute("INSERT INTO struct values(NULL,?,0)", (name,))
123 parent_id = execute("SELECT last_insert_rowid()").fetchone()[0]
125 parent_id = parent_id[0]
127 name = entry[FIELD_MIDDLELEVEL][0]
128 child_id = execute("SELECT id FROM struct WHERE name=? AND parent=?", \
129 (name, parent_id)).fetchone()
130 if child_id is None: # update STRUCT table (FIELD_MIDDLELEVEL)
131 execute("INSERT INTO struct values(NULL,?,?)", (name, parent_id))
132 child_id = execute("SELECT last_insert_rowid()").fetchone()[0]
134 child_id = child_id[0]
136 # update RELATION table
137 execute("INSERT INTO relation values(?,?)", (_id, child_id))
140 def get_folders(self, parent=None):
142 Returns list of all folders (items with folders and files)
146 if parent is None: # return all folders on level2
147 return self.conn.execute("SELECT DISTINCT name, id FROM struct \
148 WHERE parent!=0 ORDER BY name ASC").fetchall()
150 return self.conn.execute("SELECT DISTINCT name, id FROM struct \
151 WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()
153 def _get_files(self, fields, parent=0):
156 execute = self.conn.execute
157 fields = dict(execute("SELECT id, name FROM fields WHERE name IN (%s)" \
158 % ','.join('%r' % (field,) for field in fields)).fetchall())
160 query = "SELECT id, field_id, value FROM data \
161 left join relation on relation.data_id=data.id \
162 where relation.struct_id=? and field_id in (%s)"
164 query = "select id, field_id, value from data where id in (select data_id from relation where struct_id=?) and field_id in (%s)"
166 data = execute(query % ','.join('%r' % f for f in fields.keys()), (parent,)).fetchall()
168 for id, field_id, value in data:
169 if not items_dict.has_key(id):
171 items_dict[id][fields[int(field_id)]] = value
176 def get_files(self, fields, parent=0, pattern=''):
177 """Returns list of all files from DATA table."""
180 execute = self.conn.execute
181 fields = execute("SELECT id, name FROM fields WHERE name IN (%s)" \
182 % ','.join('%r' % (field,) for field in fields)).fetchall()
183 if parent == 0: # get all files
184 #data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
186 data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
187 data WHERE value LIKE '%s%%' LIMIT 50" % pattern.capitalize()).fetchall()]
188 else: # get files for selected parent
189 data_ids = [_id[0] for _id in execute("SELECT data_id FROM \
190 relation WHERE struct_id=?", (parent,)).fetchall()]
191 for data_id in data_ids:
192 items_dict[data_id] = {}
193 for field_id, field_name in fields:
194 field_value = execute("SELECT value FROM data WHERE id=? \
195 AND field_id=?", (data_id, field_id)).fetchone()[0]
196 items_dict[data_id][field_name] = field_value
199 def _get_files_by_pattern(self, fields, key='cn', pattern='', search_from_start=False):
200 """Returns list of all files from DATA table."""
203 if key not in fields:
204 fields = fields + (key,)
205 execute = self.conn.execute
206 fields = dict(execute("SELECT id, name FROM fields WHERE name IN (%s)" \
207 % ','.join('%r' % (field,) for field in fields)).fetchall())
209 for k, val in fields.items():
213 if search_from_start:
214 s = '%s%%' % pattern.capitalize()
216 s = '%%%s%%' % pattern
218 data = execute("SELECT id, field_id, value FROM data WHERE id in \
219 (select id from data where value LIKE '%s' and field_id=? LIMIT 50) \
220 and field_id in (%s)" % (s, ','.join('%r' % f for f in fields.keys())), (key_id,)).fetchall()
222 for id, field_id, value in data:
223 if not items_dict.has_key(id):
225 items_dict[id][fields[int(field_id)]] = value
229 def get_files_by_pattern(self, pattern):
230 """Returns list of all files, matched pattern string."""
234 execute = self.conn.execute
235 field_id = execute("SELECT id FROM fields WHERE name=?", \
236 ('cn',)).fetchone()[0]
237 return execute("SELECT id, value FROM data WHERE value LIKE '%s%%' \
238 AND field_id=? LIMIT 50" % pattern.capitalize(), (field_id,)).fetchall()
240 def get_entry(self, _id):
241 """Returns full entry by it id."""
243 execute = self.conn.execute
245 entry_dict = dict(execute("select fields.name, value from data \
246 left join fields on fields.id=data.field_id \
247 where data.id=?", (_id,)).fetchall())
248 sql_res = execute('select photo from photo where data_id=?', \
250 if sql_res is not None:
251 entry_dict['image'] = sql_res[0]
254 def _get_entry(self, _id):
255 """Returns full entry by it id."""
257 execute = self.conn.execute
259 for field_id, value in execute("SELECT field_id, value FROM data WHERE \
260 id=?", (_id,)).fetchall():
261 field_name = execute("SELECT name FROM fields WHERE id=?", \
262 (field_id,)).fetchone()[0]
263 entry_dict[field_name] = value
264 sql_res = execute('select photo from photo where data_id=?', \
266 if sql_res is not None:
267 entry_dict['image'] = sql_res[0]
270 if __name__ == "__main__":
272 #print db.get_files(('cn', 'o', 'ou'), 0)
273 print db._get_files_by_pattern(('cn', 'o', 'ou'), 'cn', 'Ра', True)