enkel/sqldb/sqlite_adapter.py

   1 # This file is part of the Enkel web programming library.
   2 #
   3 # Copyright (C) 2007 Espen Angell Kristiansen (espeak@users.sourceforge.net)
   4 #
   5 # This program is free software; you can redistribute it and/or
   6 # modify it under the terms of the GNU General Public License
   7 # as published by the Free Software Foundation; either version 2
   8 # of the License, or (at your option) any later version.
   9 #
  10 # This program is distributed in the hope that it will be useful,
  11 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13 # GNU General Public License for more details.
  14 #
  15 # You should have received a copy of the GNU General Public License
  16 # along with this program; if not, write to the Free Software
  17 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  18
  19 from datetime import time
  20 try:
  21         import sqlite3 as sqlite
  22 except ImportError:
  23         from pysqlite2 import dbapi2 as sqlite
  24
  25 from enkel.model.field.base import Date, DateTime, Time
  26
  27 from std_adapter import StdAdapter
  28 from errors import *
  29 from table import Table
  30 from query import paramgen_qmark
  31 from dbfields import AutoincPk, DbLong, DbInt
  32
  33
  34 def adapt_time(t):
  35         """ Adapter to support datetime.date insertion on TIME cols. """
  36         return t.strftime("%H:%M:%S")
  37 sqlite.register_adapter(time, adapt_time)
  38
  39
  40 class SqliteAdapter(StdAdapter):
  41         def __init__(self, dbpath):
  42                 self.dbpath = dbpath
  43                 self.echo = False
  44                 self.connection = sqlite.connect(dbpath)
  45                 self.connection.isolation_level = "DEFERRED"
  46                 self.cursor = self.new_cursor()
  47
  48         def paramgen(self, index):
  49                 return paramgen_qmark(index)
  50
  51
  52         def execute(self, query, params=None, cursor=None):
  53                 cursor = cursor or self.cursor
  54                 if self.echo:
  55                         print query
  56                         if params:
  57                                 print params
  58                         print
  59                 try:
  60                         if params:
  61                                 cursor.execute(query, params)
  62                         else:
  63                                 cursor.execute(query)
  64                 except sqlite.DataError, e:
  65                         raise DataError(str(e))
  66                 except sqlite.OperationalError, e:
  67                         raise OperationalError(str(e))
  68                 except sqlite.IntegrityError, e:
  69                         raise IntegrityError(str(e))
  70                 except sqlite.InternalError, e:
  71                         raise InternalError(str(e))
  72                 except sqlite.ProgrammingError, e:
  73                         raise ProgrammingError(str(e))
  74
  75
  76         def _get_sqltype(self, fieldtype, fieldname, field):
  77                 if isinstance(field, DbInt) or isinstance(field, DbLong):
  78                         return "INTEGER"
  79                 else:
  80                         super(SqliteAdapter, self)._get_sqltype(
  81                                         fieldtype, fieldname, field)
  82
  83
  84         def _create_triggers(self, table):
  85                 """ Used by L{create_table} to create the required
  86                 foreign key triggers. """
  87                 table_name = table.name
  88                 for fieldname, field in table.iter_fks():
  89                         ftable = field.datasource
  90                         ftable_pk = ftable.get_pk()
  91                         ftable_name = ftable.name
  92                         constraint = "fk_%s" % fieldname
  93                         err = 'on table "%(table_name)s" violates foreign key '\
  94                                         'constraint on the "%(fieldname)s" field' % vars()
  95
  96                         qry = \
  97 """CREATE TRIGGER fk%(act)s_%(table_name)s_%(fieldname)s
  98 BEFORE %(ACT)s ON %(table_name)s
  99 FOR EACH ROW BEGIN
 100         SELECT RAISE(
 101                 FAIL,
 102                 'update %(err)s'
 103         ) WHERE NEW.%(fieldname)s IS NOT NULL AND
 104                 (SELECT %(ftable_pk)s FROM %(ftable_name)s WHERE %(ftable_pk)s = NEW.%(fieldname)s) IS NULL;
 105 END;
 106 """
 107                         act = "ins"
 108                         ACT = "INSERT"
 109                         self.execute(qry % vars())
 110
 111                         act = "upd"
 112                         ACT = "UPDATE"
 113                         self.execute(qry % vars())
 114
 115                         qry = \
 116 """CREATE TRIGGER fkdel_%(table_name)s_%(fieldname)s
 117 BEFORE DELETE ON %(ftable_name)s
 118 FOR EACH ROW BEGIN
 119         SELECT RAISE(
 120                 FAIL,
 121                 'delete on table "%(ftable_name)s" violates foreign key constraint on the "%(table_name)s.%(fieldname)s" field'
 122         ) WHERE (SELECT %(fieldname)s FROM %(table_name)s WHERE %(fieldname)s = OLD.%(ftable_pk)s) IS NOT NULL;
 123 END;
 124 """ % vars()
 125                         self.execute(qry)
 126
 127
 128         def _get_sqlfield(self, fieldtype, fieldname, field):
 129                 if fieldtype == Table.FT_FK:
 130                         if field.required:
 131                                 n = " NOT NULL"
 132                         else:
 133                                 n = ""
 134                         ftable = field.datasource
 135                         c = "CONSTRAINT fk_%s REFERENCES %s(%s) ON DELETE CASCADE" % (
 136                                         fieldname, ftable.name, ftable.get_pk())
 137                         d = self._get_sqltype(Table.FT_NORM, fieldname,
 138                                         field.datasource.field)
 139                         return "%s %s%s\n\t\t%s" % (fieldname, d, n, c)
 140                 return super(SqliteAdapter, self)._get_sqlfield(
 141                                 fieldtype, fieldname, field)
 142
 143         def create_table(self, table):
 144                 cols = []
 145                 for t, fieldname, field in table.iter_fields():
 146                         if t == Table.FT_MM:
 147                                 continue
 148                         d = self._get_sqlfield(t, fieldname, field)
 149                         cols.append(d)
 150                 cols.append("PRIMARY KEY(%s)" % ",".join(table.primary_keys))
 151                 if table.unique:
 152                         cols.append("UNIQUE(%s)" % ",".join(table.unique))
 153                 qry = "CREATE TABLE %s (\n\t%s\n);" % (table.name, ",\n\t".join(cols))
 154                 self.execute(qry)
 155                 self._create_triggers(table)
 156                 self._create_indexes(table)
 157
 158
 159         def from_db(self, field, value):
 160
 161                 # use the base classes to ensure that subclasses cannot
 162                 # with different from_unicode cannot fuck things up
 163                 if isinstance(field, Date):
 164                         return Date.from_unicode(field, value)
 165                 if isinstance(field, DateTime):
 166                         return DateTime.from_unicode(field, value)
 167                 if isinstance(field, Time):
 168                         return Time.from_unicode(field, value)
 169
 170                 return value