return self.conn.execute("SELECT DISTINCT name, id FROM struct \
WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()
- def get_files(self, fields, parent=0, pattern=''):
- """Returns list of all files from DATA table."""
+ def get_files(self, fields, parent=0):
+ """Finds all files with selected parent"""
items_dict = {}
execute = self.conn.execute
- fields = execute("SELECT id, name FROM fields WHERE name IN (%s)" \
- % ','.join('%r' % (field,) for field in fields)).fetchall()
- if parent == 0: # get all files
- field_id = execute("SELECT id FROM fields WHERE name=?", \
- ('cn',)).fetchone()[0]
- #data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
- # data").fetchall()]
- data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
- data WHERE value LIKE '%s%%' AND field_id=? LIMIT 50" % pattern.capitalize(), (field_id,)).fetchall()]
- else: # get files for selected parent
- data_ids = [_id[0] for _id in execute("SELECT data_id FROM \
- relation WHERE struct_id=?", (parent,)).fetchall()]
- for data_id in data_ids:
- items_dict[data_id] = {}
- for field_id, field_name in fields:
- field_value = execute("SELECT value FROM data WHERE id=? \
- AND field_id=?", (data_id, field_id)).fetchone()[0]
- items_dict[data_id][field_name] = field_value
+ fields = dict(execute("SELECT id, name FROM fields WHERE name IN (%s)" \
+ % ','.join('%r' % (field,) for field in fields)).fetchall())
+ """
+ query = "SELECT id, field_id, value FROM data \
+ left join relation on relation.data_id=data.id \
+ where relation.struct_id=? and field_id in (%s)"
+ """
+ query = "SELECT id, field_id, value FROM data \
+ WHERE id IN (SELECT data_id FROM relation WHERE struct_id=?) \
+ AND field_id IN (%s)"
+ data = execute(query % ','.join('%r' % f for f in fields.keys()), \
+ (parent,)).fetchall()
+ for data_id, field_id, value in data:
+ if not items_dict.has_key(data_id):
+ items_dict[data_id] = {}
+ items_dict[data_id][fields[int(field_id)]] = value
return items_dict
- def get_files_by_pattern(self, pattern):
- """Returns list of all files, matched pattern string."""
+ def get_files_by_pattern(self, fields, key='cn', pattern='', \
+ search_from_start=False):
+ """Returns list of all files from DATA table."""
- if not pattern:
- return []
+ items_dict = {}
+ if key not in fields:
+ fields.append(str(key))
execute = self.conn.execute
- field_id = execute("SELECT id FROM fields WHERE name=?", \
- ('cn',)).fetchone()[0]
- return execute("SELECT id, value FROM data WHERE value LIKE '%s%%' \
- AND field_id=? LIMIT 50" % pattern.capitalize(), (field_id,)).fetchall()
+ fields = dict(execute("SELECT id, name FROM fields WHERE name IN (%s)" \
+ % ','.join('%r' % (field,) for field in fields)).fetchall())
+ key_id = 1
+ for k, val in fields.items():
+ if val == key:
+ key_id = k
+ break
+ if search_from_start:
+ s = '%s%%' % pattern.capitalize()
+ else:
+ s = '%%%s%%' % pattern
+ data = execute("SELECT id, field_id, value FROM data WHERE id IN \
+ (SELECT id FROM data WHERE value LIKE '%s' AND field_id=? LIMIT \
+ 50) AND field_id in (%s)" % (s, ','.join('%r' % f for f in \
+ fields.keys())), (key_id,)).fetchall()
+ for data_id, field_id, value in data:
+ if not items_dict.has_key(data_id):
+ items_dict[data_id] = {}
+ items_dict[data_id][fields[int(field_id)]] = value
+ return items_dict
def get_entry(self, _id):
"""Returns full entry by it id."""
execute = self.conn.execute
entry_dict = {}
- for field_id, value in execute("SELECT field_id, value FROM data WHERE \
- id=?", (_id,)).fetchall():
- field_name = execute("SELECT name FROM fields WHERE id=?", \
- (field_id,)).fetchone()[0]
- entry_dict[field_name] = value
- sql_res = execute('select photo from photo where data_id=?', \
+ entry_dict = dict(execute("SELECT fields.name, value FROM data \
+ LEFT JOIN fields ON fields.id=data.field_id \
+ WHERE data.id=?", (_id,)).fetchall())
+ sql_res = execute('SELECT photo FROM photo WHERE data_id=?', \
(_id,)).fetchone()
if sql_res is not None:
entry_dict['image'] = sql_res[0]
return entry_dict
+
+
+
+if __name__ == "__main__":
+ db = SQLite('/tmp')
+ # put test code here
+ db.close()