vcs.maemo.org Git - meabook/blob - database/db.py

   1 #!/usr/bin/python -tt
   2 # -*- coding: utf-8 -*-
   3 # vim: sw=4 ts=4 expandtab ai
   4
   5 """Module for work with database"""
   6
   7 import os
   8 import sqlite3
   9 import pygtk
  10 pygtk.require('2.0')
  11 import gtk
  12
  13 class DbSqlite:
  14
  15     """class for work with database"""
  16
  17     def __init__(self):
  18         self.conn = self.connect()
  19
  20     def connect(self):
  21
  22         """connect to database"""
  23         dbname = "meabookdb"
  24         if os.access(dbname, os.F_OK|os.R_OK|os.W_OK):
  25             conn = sqlite3.connect(dbname)
  26         else:
  27             print "access to database file error"
  28             conn = None
  29         return conn
  30
  31     def get_record(self,  id):
  32
  33         """return record field"""
  34         cur = self.conn.cursor()
  35         cur.execute("select field.name, data.value "
  36                     "from data left join field "
  37                     "on data.id_field=field.id "
  38                     "where data.id=?", (id,))
  39         self.data = {}
  40         for row in cur:
  41             print "key = %s value = %s" % (row[0], row[1])
  42             self.data[row[0]] = row[1]
  43         return self.data
  44
  45     def get_allrecords(self, field):
  46
  47         """return records"""
  48         cur = self.conn.cursor()
  49         cur.execute("select data.id, data.value "
  50                     "from data left join field "
  51                     "on data.id_field=field.id "
  52                     "where field.name=?", (field,))
  53         self.data = {}
  54         for row in cur:
  55             #print row
  56             self.data[row[0]] = row[1]
  57         return self.data
  58
  59     def get_id(self, table, value):
  60
  61         """get id for value into table"""
  62         if self.conn is None:
  63             return None
  64         cur = self.conn.cursor()
  65         query = "select id from %s where name='%s'" % (table, value)
  66         cur.execute(query)
  67         res = cur.fetchone()
  68         id = 0
  69         if res is None:
  70             query = "insert into %s (name) values ('%s')" % (table, value)
  71             cur.execute(query)
  72             id = cur.lastrowid
  73         else:
  74             id = res[0]
  75         return id
  76
  77     def ins_record(self, rec):
  78
  79         """insert record into database"""
  80         #get id new record
  81         #conn = self.connect()
  82         if self.conn is None:
  83             print "database connect error"
  84             return None
  85         cur = self.conn.cursor()
  86         cur.execute("select max(id) from data")
  87         id_record = cur.fetchone()[0]
  88         if id_record is None:
  89             id_record = 1
  90         else:
  91             id_record = id_record + 1
  92         print "id_record=%s" % (id_record,)
  93
  94         struct = ["", ""]
  95         for key, val in rec.items():
  96             #print "key=%s value=%s" % (key, val)
  97             #cur.execute("select id from field where name=?", (key,))
  98
  99             #id_field = cur.fetchone()
 100             #if id_field is None:
 101             #    #write new field ваы
 102             #    cur.execute("insert into field (name) values (?)", (key,))
 103             #    id_field = cur.lastrowid
 104             #else:
 105             #    id_field = id_field[0]
 106             # insert row into data
 107             #print "id=%s id_field=%s value=%s" % (id_record, id_field, val)
 108             id_field = self.get_id("field", key)
 109             if key == 'company':
 110                 struct[0] = val
 111             if key == 'department':
 112                 struct[1] = val
 113             for i in val:
 114                 cur.execute("insert into data (id, id_field, value) values (?, ?, ?)",
 115                 (id_record, id_field, i))
 116
 117         #insert comppane into table struct
 118         id0 = self.get_id("struct", struct[0][0])
 119         cur.execute("update struct set parent=0 where id=?", (id0,))
 120         id1 = self.get_id("struct", struct[1][0])
 121         cur.execute("update struct set parent=? where id=?", (id0, id1))
 122         cur.execute("insert into relation (id_data, id_struct) values (?, ?)", (id_record, id1))
 123         self.conn.commit()
 124         #conn.close()
 125
 126     def clear_data(self):
 127
 128         """clear all record from table data"""
 129         conn = self.connect()
 130         if conn is None:
 131             print "database connect error"
 132             return None
 133         cur = conn.cursor()
 134         cur.execute("delete from data")
 135         cur.execute("delete from field")
 136         cur.execute("delete from struct")
 137         cur.execute("delete from relation")
 138         conn.commit()
 139         conn.close()
 140
 141     def make_tree(self):
 142
 143         """create TreeStore from database table Struct"""
 144         ts = gtk.TreeStore(int, str)
 145
 146         conn = self.connect()
 147         if conn is None:
 148             return None
 149         cur = conn.cursor()
 150         cur.execute("select id, name, parent from struct")
 151         item = {}
 152         for row in cur:
 153             if (row[2] == 0):
 154                 item[row[0]] = ts.append(None, [row[0], row[1]])
 155             else :
 156                 ts.append(item[row[2]], [row[0], row[1]])
 157         return ts
 158
 159