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

   1 import os
   2 import sqlite3
   3
   4 DATABASE_NAME = 'contacts.db'
   5
   6 SCHEMA = """
   7     begin;
   8
   9     create table data(
  10         id integer primary key,
  11         field_id integer,
  12         value text
  13     );
  14
  15     create table fields(
  16         id integer primary key,
  17         name text
  18     );
  19
  20     create table relation (
  21         data_id integer,
  22         struct_id integer
  23     );
  24
  25     create table struct(
  26         id integer primary key,
  27         name text,
  28         parent integer
  29     );
  30
  31     commit;
  32 """
  33
  34
  35 class SQLite:
  36     def __init__(self, basedir):
  37         self._path = os.path.join(basedir, DATABASE_NAME)
  38         self.conn = None
  39         if not os.path.exists(self._path):
  40             self.new()
  41         else:
  42             self.conn = sqlite3.connect(self._path)
  43
  44     def new(self):
  45         """Creates new databse."""
  46
  47         self.conn = sqlite3.connect(self._path)
  48         self.conn.executescript(SCHEMA)
  49         self.conn.commit()
  50
  51     def close(self):
  52         """Closes connection with database."""
  53
  54         self.conn.commit()
  55         self.conn.close()
  56
  57     def update_field(self, fname):
  58         """Adds new field to database."""
  59
  60         fields = self.conn.execute("""SELECT name from field""").fetchall()
  61         if not fname in fields:
  62             self.conn.execute("""INSERT INTO field values((SELECT max(id)
  63             FROM field)+1, ?)""", (fname,))
  64
  65     def get_field_id_by_name(self, fname):
  66         """Returns field id by it name."""
  67
  68         return self.conn.execute("""SELECT id FROM field WHERE name=?""", \
  69             (fname,)).fetchone()[0]
  70
  71     def get_field_name_by_id(self, _id):
  72         """Returns field id by it name."""
  73
  74         return self.conn.execute("""SELECT name FROM field WHERE id=?""", \
  75             (_id,)).fetchone()[0]
  76
  77     def get_entry_by_id(self, _id):
  78         """Returns full entry by it id."""
  79
  80         sql_res = self.conn.execute("""SELECT id_field, value FROM data
  81             WHERE id=?""", (_id,)).fetchall()
  82         return dict([(self.get_field_name_by_id(field_id), value) for field_id, \
  83             value in sql_res])
  84
  85     def get_all_entries(self):
  86         """Returns list of all entries."""
  87
  88         ids = set([item[0] for item in self.conn.execute( \
  89             """SELECT id FROM data""").fetchall()])
  90         return [self.get_entry_by_id(_id) for _id in ids]
  91
  92
  93
  94
  95 if __name__ == "__main__":
  96     db = SQLite('/tmp/')
  97     #db.update_field('test')
  98     #print db.get_field_id_by_name('fax')
  99     #print db.get_entry_by_id(1)
 100     #print db.get_all_entries()
 101     db.close()
 102
 103