2 # -*- coding: utf-8 -*-
8 DATABASE_NAME = 'contacts.db'
11 MIDDLELEVEL_FIELD = 'ou'
22 create index i_data on data (id);
25 id integer primary key,
28 create index i_fields on fields (id);
30 create table relation(
34 create index i_relation_data on relation(data_id);
37 id integer primary key,
41 create index i_struct_id on struct(parent);
48 def __init__(self, basedir):
49 self._path = os.path.join(basedir, DATABASE_NAME)
51 if not os.path.exists(self._path):
57 """Connects to database."""
59 self.conn = sqlite3.connect(self._path, isolation_level="EXCLUSIVE")
62 """Creates new databse."""
65 self.conn.executescript(SCHEMA)
68 """Closes connection with database."""
73 """Save all changes."""
78 """Clear all database tables."""
80 execute = self.conn.execute
81 execute("DELETE from data")
82 execute("DELETE from fields")
83 execute("DELETE from struct")
84 execute("DELETE from relation")
88 """Returns all fields from FIELDS table."""
90 return [item[0] for item in self.conn.execute("SELECT name FROM \
91 fields").fetchall() if item[0]!='image']
93 # operations with DATA table
94 def add_entry(self, entry):
95 """Adds new entry to database."""
97 execute = self.conn.execute
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 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(?,?,?)", \
118 (_id, field_id, value))
120 # update STRUCT table
121 name = entry[TOPLEVEL_FIELD][0]
122 parent_id = execute("SELECT id FROM struct WHERE name=? ", \
124 if parent_id is None: # update STRUCT table (TOPLEVEL_FIELD)
125 execute("INSERT INTO struct values(NULL,?,0)", (name,))
126 parent_id = execute("SELECT last_insert_rowid()").fetchone()[0]
128 parent_id = parent_id[0]
130 name = entry[MIDDLELEVEL_FIELD][0]
131 child_id = execute("SELECT id FROM struct WHERE name=? AND parent=?", \
132 (name, parent_id)).fetchone()
133 if child_id is None: # update STRUCT table (MIDDLELEVEL_FIELD)
134 execute("INSERT INTO struct values(NULL,?,?)", (name, parent_id))
135 child_id = execute("SELECT last_insert_rowid()").fetchone()[0]
137 child_id = child_id[0]
139 # update RELATION table
140 execute("INSERT INTO relation values(?,?)", (_id, child_id))
142 def get_folders(self, parent=None):
144 Returns list of all folders (items with folders and files)
148 if parent is None: # return all folders on level2
149 return self.conn.execute("SELECT DISTINCT name, id FROM struct \
150 WHERE parent!=0 ORDER BY name ASC").fetchall()
152 return self.conn.execute("SELECT DISTINCT name, id FROM struct \
153 WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()
155 def get_files(self, fields, parent=0):
156 """Returns list of all files from DATA table."""
159 execute = self.conn.execute
160 fields = execute("SELECT id, name FROM fields WHERE name IN (%s)" \
161 % ','.join('%r' % (field,) for field in fields)).fetchall()
162 if parent == 0: # get all files
163 data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
165 else: # get files for selected parent
166 data_ids = [_id[0] for _id in execute("SELECT data_id FROM \
167 relation WHERE struct_id=?", (parent,)).fetchall()]
168 for data_id in data_ids:
169 items_dict[data_id] = {}
170 for field_id, field_name in fields:
171 field_value = execute("SELECT value FROM data WHERE id=? \
172 AND field_id=?", (data_id, field_id)).fetchone()[0]
173 items_dict[data_id][field_name] = field_value
176 def get_files_by_pattern(self, pattern):
177 """Returns list of all files, matched pattern string."""
181 execute = self.conn.execute
182 field_id = execute("SELECT id FROM fields WHERE name=?", \
183 ('cn',)).fetchone()[0]
184 return execute("SELECT id, value FROM data WHERE value LIKE '%%%s%%' \
185 AND field_id=? LIMIT 50" % pattern, (field_id,)).fetchall()
187 def get_entry(self, _id):
188 """Returns full entry by it id."""
190 execute = self.conn.execute
192 for field_id, value in execute("SELECT field_id, value FROM data WHERE \
193 id=?", (_id,)).fetchall():
194 field_name = execute("SELECT name FROM fields WHERE id=?", \
195 (field_id,)).fetchone()[0]
196 entry_dict[field_name] = value