4 DATABASE_NAME = 'contacts.db'
7 MIDDLELEVEL_FIELD = 'ou'
20 id integer primary key,
24 create table relation(
30 id integer primary key,
37 create index i_fields on fields (id);
38 create index i_data on data (id);
39 create index i_struct on struct(parent);
40 create index i_relation on relation(data_id);
48 def __init__(self, basedir):
49 self._path = os.path.join(basedir, DATABASE_NAME)
51 if not os.path.exists(self._path):
54 self.conn = sqlite3.connect(self._path)
57 """Creates new databse."""
59 self.conn = sqlite3.connect(self._path)
60 self.conn.executescript(SCHEMA)
64 """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( \
88 "SELECT name FROM fields").fetchall()]
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, name 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[TOPLEVEL_FIELD][0]
119 parent_id = execute("SELECT id FROM struct WHERE name=? ", \
121 if parent_id is None: # update STRUCT table (TOPLEVEL_FIELD)
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[MIDDLELEVEL_FIELD][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 (MIDDLELEVEL_FIELD)
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))
139 def get_entry(self, value):
140 """Gets full entry imformation from DATA table."""
142 _id = self.conn.execute("SELECT id FROM data WHERE value=?", \
143 (value,)).fetchone()[0]
144 return dict([(key, value) for key, value in self.conn.execute("\
145 SELECT field_id, value FROM data WHERE id=?", (_id,)).fetchall()])
147 def get_folders(self, parent=None):
149 Returns list of all folders (items with folders and files)
153 if parent is None: # return all folders on level2
154 return self.conn.execute("SELECT DISTINCT name, id FROM struct \
155 WHERE parent!=0 ORDER BY name ASC").fetchall()
157 return self.conn.execute("SELECT DISTINCT name, id FROM struct \
158 WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()
160 def get_files(self, fields, parent=0):
161 """Returns list of all files from DATA table."""
164 fields_string = ','.join('%r' % (field,) for field in fields)
165 if parent == 0: # get all files
166 sql_res = self.conn.execute("select data.id as id, fields.name, \
167 data.value as name from data left join fields on data.field_id=\
168 fields.id left join relation on data.id=relation.data_id where \
169 fields.name in (%s)" % fields_string)
170 else: # get files for selected parent
171 sql_res = self.conn.execute("select data.id as id, fields.name, \
172 data.value as name from data left join fields on data.field_id=\
173 fields.id left join relation on data.id=relation.data_id where \
174 fields.name in (%s) and relation.struct_id=?" % fields_string, \
178 for _id, fname, fvalue in sql_res.fetchall():
179 if not items_dict.has_key(_id):
181 items_dict[_id][fname] = fvalue
184 def get_entry_by_id(self, _id):
185 """Returns full entry by it id."""
187 sql_res = self.conn.execute("SELECT fields.name, data.value AS name \
188 FROM data LEFT JOIN fields ON data.field_id=fields.id LEFT JOIN \
189 relation ON data.id=relation.data_id WHERE data.id=?", (_id,))
193 return dict(sql_res.fetchall())