git.maemo.org Git - meabook/blob - database/SQLite.py

   1 #!/usr/bin/python
   2 # -*- coding: utf-8 -*-
   3
   4
   5 import os
   6 import sqlite3
   7 from meabook.constants import *
   8
   9 DATABASE_NAME = 'contacts.db'
  10
  11 SCHEMA = """
  12     begin;
  13
  14     create table data(
  15         id integer,
  16         field_id text,
  17         value text
  18     );
  19     create index i_data on data (id);
  20
  21     create table fields(
  22         id integer primary key,
  23         name text
  24     );
  25     create index i_fields on fields (id);
  26
  27     create table relation(
  28         data_id integer,
  29         struct_id integer
  30     );
  31     create index i_relation_data on relation(data_id);
  32
  33     create table struct(
  34         id integer primary key,
  35         name text,
  36         parent integer
  37     );
  38     create index i_struct_id on struct(parent);
  39     commit;
  40
  41 """
  42
  43
  44 class SQLite:
  45     def __init__(self, basedir):
  46         self._path = os.path.join(basedir, DATABASE_NAME)
  47         self.conn = None
  48         if not os.path.exists(self._path):
  49             self.new()
  50         else:
  51             self.connect()
  52
  53     def connect(self):
  54         """Connects to database."""
  55
  56         self.conn = sqlite3.connect(self._path, isolation_level="EXCLUSIVE")
  57
  58     def new(self):
  59         """Creates new databse."""
  60
  61         self.connect()
  62         self.conn.executescript(SCHEMA)
  63
  64     def close(self):
  65         """Closes connection with database."""
  66
  67         self.conn.close()
  68
  69     def save(self):
  70         """Save all changes."""
  71
  72         self.conn.commit()
  73
  74     def clear(self):
  75         """Clear all database tables."""
  76
  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")
  82         self.conn.commit()
  83
  84     def get_fields(self):
  85         """Returns all fields from FIELDS table."""
  86
  87         return [item[0] for item in self.conn.execute("SELECT name FROM \
  88             fields").fetchall() if item[0]!='image']
  89
  90     # operations with DATA table
  91     def add_entry(self, entry):
  92         """Adds new entry to database."""
  93
  94         execute = self.conn.execute
  95
  96         try:
  97             _id = execute("SELECT MAX(id) FROM data").fetchone()[0] \
  98                 + 1
  99         except TypeError:
 100             _id = 1
 101
 102         for field, values in entry.items():
 103             # update FIELDS table
 104             field_id = execute("SELECT id FROM fields WHERE name=?", \
 105                 (field,)).fetchone()
 106             if field_id is None:
 107                 execute("INSERT INTO fields values(NULL, ?)", (field,))
 108                 field_id = execute("SELECT last_insert_rowid()").fetchone()[0]
 109             else:
 110                 field_id = field_id[0]
 111
 112             # update DATA table
 113             for value in values:
 114                 execute("INSERT INTO data values(?,?,?)", \
 115                     (_id, field_id, value))
 116
 117         # update STRUCT table
 118         name = entry[FIELD_TOPLEVEL][0]
 119         parent_id = execute("SELECT id FROM struct WHERE name=? ", \
 120             (name,)).fetchone()
 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]
 124         else:
 125             parent_id = parent_id[0]
 126
 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]
 133         else:
 134             child_id = child_id[0]
 135
 136         # update RELATION table
 137         execute("INSERT INTO relation values(?,?)", (_id, child_id))
 138
 139
 140     def get_folders(self, parent=None):
 141         """
 142         Returns list of all folders (items with folders and files)
 143         from STRUCT table.
 144         """
 145
 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()
 149         else:
 150             return self.conn.execute("SELECT DISTINCT name, id FROM struct \
 151                 WHERE parent=? ORDER BY name ASC", (parent,)).fetchall()
 152
 153     def _get_files(self, fields, parent=0):
 154
 155         items_dict = {}
 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())
 159         """
 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)"
 163         """
 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)"
 165
 166         data = execute(query % ','.join('%r' % f for f in fields.keys()), (parent,)).fetchall()
 167
 168         for id, field_id, value in data:
 169             if not items_dict.has_key(id):
 170                 items_dict[id] = {}
 171             items_dict[id][fields[int(field_id)]] = value
 172         return items_dict
 173
 174
 175
 176     def get_files(self, fields, parent=0, pattern=''):
 177         """Returns list of all files from DATA table."""
 178
 179         items_dict = {}
 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             field_id = execute("SELECT id FROM fields WHERE name=?", \
 185                 ('cn',)).fetchone()[0]
 186             #data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
 187             #    data").fetchall()]
 188             data_ids = [_id[0] for _id in execute("SELECT DISTINCT id FROM \
 189                 data WHERE value LIKE '%s%%' AND field_id=? LIMIT 50" % pattern.capitalize(), (field_id,)).fetchall()]
 190         else: # get files for selected parent
 191             data_ids = [_id[0] for _id in execute("SELECT data_id FROM \
 192                 relation WHERE struct_id=?", (parent,)).fetchall()]
 193         for data_id in data_ids:
 194             items_dict[data_id] = {}
 195             for field_id, field_name in fields:
 196                 field_value = execute("SELECT value FROM data WHERE id=? \
 197                     AND field_id=?", (data_id, field_id)).fetchone()[0]
 198                 items_dict[data_id][field_name] = field_value
 199         return items_dict
 200
 201     def _get_files_by_pattern(self, fields, key='cn', pattern='', search_from_start=False):
 202         """Returns list of all files from DATA table."""
 203
 204         items_dict = {}
 205         if key not in fields:
 206             fields = fields + (key,)
 207         execute = self.conn.execute
 208         fields = dict(execute("SELECT id, name FROM fields WHERE name IN (%s)" \
 209             % ','.join('%r' % (field,) for field in fields)).fetchall())
 210         key_id = 1
 211         for k, val in fields.items():
 212             if val == key:
 213                 key_id = k
 214                 break
 215         if search_from_start:
 216             s = '%s%%' % pattern.capitalize()
 217         else:
 218             s = '%%%s%%' % pattern
 219
 220         data = execute("SELECT id, field_id, value FROM data WHERE id in \
 221             (select id from data where value LIKE '%s' and field_id=? LIMIT 50) \
 222             and field_id in (%s)" % (s, ','.join('%r' % f for f in fields.keys())), (key_id,)).fetchall()
 223
 224         for id, field_id, value in data:
 225             if not items_dict.has_key(id):
 226                 items_dict[id] = {}
 227             items_dict[id][fields[int(field_id)]] = value
 228         return items_dict
 229
 230
 231     def get_files_by_pattern(self, pattern):
 232         """Returns list of all files, matched pattern string."""
 233
 234         if not pattern:
 235             return []
 236         execute = self.conn.execute
 237         field_id = execute("SELECT id FROM fields WHERE name=?", \
 238             ('cn',)).fetchone()[0]
 239         return execute("SELECT id, value FROM data WHERE value LIKE '%s%%' \
 240             AND field_id=? LIMIT 50" % pattern.capitalize(), (field_id,)).fetchall()
 241
 242     def get_entry(self, _id):
 243         """Returns full entry by it id."""
 244
 245         execute = self.conn.execute
 246         entry_dict = {}
 247         entry_dict = dict(execute("select fields.name, value from data \
 248             left join fields on fields.id=data.field_id \
 249             where data.id=?", (_id,)).fetchall())
 250         sql_res = execute('select photo from photo where data_id=?', \
 251             (_id,)).fetchone()
 252         if sql_res is not None:
 253             entry_dict['image'] = sql_res[0]
 254         return entry_dict
 255
 256     def _get_entry(self, _id):
 257         """Returns full entry by it id."""
 258
 259         execute = self.conn.execute
 260         entry_dict = {}
 261         for field_id, value in execute("SELECT field_id, value FROM data WHERE \
 262             id=?", (_id,)).fetchall():
 263             field_name = execute("SELECT name FROM fields WHERE id=?", \
 264                 (field_id,)).fetchone()[0]
 265             entry_dict[field_name] = value
 266         sql_res = execute('select photo from photo where data_id=?', \
 267             (_id,)).fetchone()
 268         if sql_res is not None:
 269             entry_dict['image'] = sql_res[0]
 270         return entry_dict
 271
 272 if __name__ == "__main__":
 273     db = SQLite('/tmp')
 274     #print db.get_files(('cn', 'o', 'ou'), 0)
 275     print db._get_files_by_pattern(('cn', 'o', 'ou'), 'cn', 'Ра', True)