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

   1 import os
   2 import sqlite3
   3
   4 DATABASE_NAME = 'contacts.db'
   5
   6 TOPLEVEL_FIELD = 'o'
   7 MIDDLELEVEL_FIELD = 'ou'
   8 LOWLEVEL_FIELD = 'cn'
   9
  10 SCHEMA = """
  11     begin;
  12
  13     create table data(
  14         id integer,
  15         field_id integer,
  16         value text
  17     );
  18
  19     create table fields(
  20         id integer primary key,
  21         name text
  22     );
  23
  24     create table relation (
  25         data_id integer,
  26         struct_id integer
  27     );
  28
  29     create table struct(
  30         id integer primary key,
  31         name text,
  32         parent integer
  33     );
  34
  35     commit;
  36 """
  37
  38
  39 class SQLite:
  40     def __init__(self, basedir):
  41         self._path = os.path.join(basedir, DATABASE_NAME)
  42         self.conn = None
  43         if not os.path.exists(self._path):
  44             self.new()
  45         else:
  46             self.conn = sqlite3.connect(self._path)
  47
  48     def new(self):
  49         """Creates new databse."""
  50
  51         self.conn = sqlite3.connect(self._path)
  52         self.conn.executescript(SCHEMA)
  53         self.conn.commit()
  54
  55     def close(self):
  56         """Closes connection with database."""
  57
  58         self.conn.commit()
  59         self.conn.close()
  60
  61     # operations with FIELDS table
  62     def update_field(self, field_names_list):
  63         """Adds new field name to database."""
  64
  65         fields = [item[0] for item in \
  66             self.conn.execute("SELECT name from fields").fetchall()]
  67         for field_name in field_names_list:
  68             if not field_name in fields:
  69                 self.conn.execute("INSERT INTO fields values(NULL, ?)", \
  70                 (field_name,))
  71         self.conn.commit()
  72
  73     def get_field_id_by_name(self, field_name):
  74         """Returns unique field id by its name."""
  75
  76         try:
  77             return self.conn.execute("SELECT id FROM fields WHERE name=?", \
  78                 (field_name,)).fetchone()[0]
  79         except TypeError:
  80             return None
  81
  82     def get_field_name_by_id(self, _id):
  83         """Returns field name by its id."""
  84
  85         try:
  86             return self.conn.execute("SELECT name FROM fields WHERE id=?", \
  87                 (_id,)).fetchone()[0]
  88         except TypeError:
  89             return None
  90
  91
  92     # operations with DATA table
  93     def add_entry(self, entry):
  94         """Adds new entry to database."""
  95
  96         if not 'uid' in entry:
  97             return
  98
  99         # check man existence in DATA table
 100         if self.conn.execute("SELECT field_id FROM data WHERE value=?", \
 101             (entry['uid'][0],)).fetchone():
 102             return
 103
 104         self.update_field(entry.keys())
 105         # check for empty table
 106         try:
 107             _id = self.conn.execute("SELECT MAX(id) FROM data").fetchone()[0] \
 108                 + 1
 109         except TypeError:
 110             _id = 1
 111
 112         # add entry (fill DATA table)
 113         for field_name in entry:
 114             for value in entry[field_name]:
 115                 self.conn.execute("INSERT INTO data values(?,?,?)", \
 116                     (_id, field_name, value))
 117
 118         self.conn.commit()
 119
 120     def get_toplevel_entries(self):
 121         """Returns list of all toplevel entries from DATA table."""
 122
 123         return set([item[0] for item in self.conn.execute("SELECT value FROM \
 124             data WHERE field_id=?", (TOPLEVEL_FIELD,)).fetchall()])
 125
 126     def get_middlelevel_entries(self, toplevel_item=None):
 127         """Returns list of all middlelevel entries from DATA table."""
 128
 129         if not toplevel_item:
 130             return set([item[0] for item in self.conn.execute("SELECT value \
 131                 FROM data WHERE field_id=?", (MIDDLELEVEL_FIELD,)).fetchall()])
 132         else:
 133             ids = [item[0] for item in self.conn.execute("SELECT id FROM data \
 134                 WHERE value=?", (toplevel_item,)).fetchall()]
 135             return set([self.conn.execute("SELECT value FROM data WHERE \
 136                 field_id=? AND id=?", (MIDDLELEVEL_FIELD, _id)).fetchone()[0] \
 137                 for _id in ids])
 138
 139     def get_lowlevel_entries(self):
 140         """Returns list of all lowlevel entries from DATA table."""
 141
 142         return set([item[0] for item in self.conn.execute("SELECT value FROM \
 143             data WHERE field_id=?", (LOWLEVEL_FIELD,)).fetchall()])
 144
 145
 146     def get_entry_by_id(self, _id):
 147         """Returns full entry by it id."""
 148
 149         sql_res = self.conn.execute("""SELECT id_field, value FROM data
 150             WHERE id=?""", (_id,)).fetchall()
 151         return dict([(self.get_field_name_by_id(field_id), value) for field_id, \
 152             value in sql_res])
 153
 154     def get_all_entries(self):
 155         """Returns list of all entries."""
 156
 157         ids = set([item[0] for item in self.conn.execute( \
 158             """SELECT id FROM data""").fetchall()])
 159         return [self.get_entry_by_id(_id) for _id in ids]
 160
 161
 162
 163
 164 if __name__ == "__main__":
 165     db = SQLite('/tmp/')
 166     #db.update_field('test')
 167     #print db.get_field_id_by_name('fax')
 168     #print db.get_entry_by_id(1)
 169     #print db.get_all_entries()
 170     db.close()
 171
 172