1 """

     2 SQLite3 backend for django.

     3 

     4 Python 2.4 requires pysqlite2 (http://pysqlite.org/).

     5 

     6 Python 2.5 and later can use a pysqlite2 module or the sqlite3 module in the

     7 standard library.

     8 """

     9 

    10 import re

    11 import sys

    12 

    13 from django.db import utils

    14 from django.db.backends import *

    15 from django.db.backends.signals import connection_created

    16 from django.db.backends.sqlite3.client import DatabaseClient

    17 from django.db.backends.sqlite3.creation import DatabaseCreation

    18 from django.db.backends.sqlite3.introspection import DatabaseIntrospection

    19 from django.utils.safestring import SafeString

    20 

    21 try:

    22     try:

    23         from pysqlite2 import dbapi2 as Database

    24     except ImportError, e1:

    25         from sqlite3 import dbapi2 as Database

    26 except ImportError, exc:

    27     import sys

    28     from django.core.exceptions import ImproperlyConfigured

    29     if sys.version_info < (2, 5, 0):

    30         module = 'pysqlite2 module'

    31         exc = e1

    32     else:

    33         module = 'either pysqlite2 or sqlite3 modules (tried in that order)'

    34     raise ImproperlyConfigured("Error loading %s: %s" % (module, exc))

    35 

    36 

    37 DatabaseError = Database.DatabaseError

    38 IntegrityError = Database.IntegrityError

    39 

    40 Database.register_converter("bool", lambda s: str(s) == '1')

    41 Database.register_converter("time", util.typecast_time)

    42 Database.register_converter("date", util.typecast_date)

    43 Database.register_converter("datetime", util.typecast_timestamp)

    44 Database.register_converter("timestamp", util.typecast_timestamp)

    45 Database.register_converter("TIMESTAMP", util.typecast_timestamp)

    46 Database.register_converter("decimal", util.typecast_decimal)

    47 Database.register_adapter(decimal.Decimal, util.rev_typecast_decimal)

    48 if Database.version_info >= (2,4,1):

    49     # Starting in 2.4.1, the str type is not accepted anymore, therefore,

    50     # we convert all str objects to Unicode

    51     # As registering a adapter for a primitive type causes a small

    52     # slow-down, this adapter is only registered for sqlite3 versions

    53     # needing it.

    54     Database.register_adapter(str, lambda s:s.decode('utf-8'))

    55     Database.register_adapter(SafeString, lambda s:s.decode('utf-8'))

    56 

    57 class DatabaseFeatures(BaseDatabaseFeatures):

    58     # SQLite cannot handle us only partially reading from a cursor's result set

    59     # and then writing the same rows to the database in another cursor. This

    60     # setting ensures we always read result sets fully into memory all in one

    61     # go.

    62     can_use_chunked_reads = False

    63 

    64 class DatabaseOperations(BaseDatabaseOperations):

    65     def date_extract_sql(self, lookup_type, field_name):

    66         # sqlite doesn't support extract, so we fake it with the user-defined

    67         # function django_extract that's registered in connect(). Note that

    68         # single quotes are used because this is a string (and could otherwise

    69         # cause a collision with a field name).

    70         return "django_extract('%s', %s)" % (lookup_type.lower(), field_name)

    71 

    72     def date_trunc_sql(self, lookup_type, field_name):

    73         # sqlite doesn't support DATE_TRUNC, so we fake it with a user-defined

    74         # function django_date_trunc that's registered in connect(). Note that

    75         # single quotes are used because this is a string (and could otherwise

    76         # cause a collision with a field name).

    77         return "django_date_trunc('%s', %s)" % (lookup_type.lower(), field_name)

    78 

    79     def drop_foreignkey_sql(self):

    80         return ""

    81 

    82     def pk_default_value(self):

    83         return 'NULL'

    84 

    85     def quote_name(self, name):

    86         if name.startswith('"') and name.endswith('"'):

    87             return name # Quoting once is enough.

    88         return '"%s"' % name

    89 

    90     def no_limit_value(self):

    91         return -1

    92 

    93     def sql_flush(self, style, tables, sequences):

    94         # NB: The generated SQL below is specific to SQLite

    95         # Note: The DELETE FROM... SQL generated below works for SQLite databases

    96         # because constraints don't exist

    97         sql = ['%s %s %s;' % \

    98                 (style.SQL_KEYWORD('DELETE'),

    99                  style.SQL_KEYWORD('FROM'),

   100                  style.SQL_FIELD(self.quote_name(table))

   101                  ) for table in tables]

   102         # Note: No requirement for reset of auto-incremented indices (cf. other

   103         # sql_flush() implementations). Just return SQL at this point

   104         return sql

   105 

   106     def year_lookup_bounds(self, value):

   107         first = '%s-01-01'

   108         second = '%s-12-31 23:59:59.999999'

   109         return [first % value, second % value]

   110 

   111     def convert_values(self, value, field):

   112         """SQLite returns floats when it should be returning decimals,

   113         and gets dates and datetimes wrong.

   114         For consistency with other backends, coerce when required.

   115         """

   116         internal_type = field.get_internal_type()

   117         if internal_type == 'DecimalField':

   118             return util.typecast_decimal(field.format_number(value))

   119         elif internal_type and internal_type.endswith('IntegerField') or internal_type == 'AutoField':

   120             return int(value)

   121         elif internal_type == 'DateField':

   122             return util.typecast_date(value)

   123         elif internal_type == 'DateTimeField':

   124             return util.typecast_timestamp(value)

   125         elif internal_type == 'TimeField':

   126             return util.typecast_time(value)

   127 

   128         # No field, or the field isn't known to be a decimal or integer

   129         return value

   130 

   131 class DatabaseWrapper(BaseDatabaseWrapper):

   132 

   133     # SQLite requires LIKE statements to include an ESCAPE clause if the value

   134     # being escaped has a percent or underscore in it.

   135     # See http://www.sqlite.org/lang_expr.html for an explanation.

   136     operators = {

   137         'exact': '= %s',

   138         'iexact': "LIKE %s ESCAPE '\\'",

   139         'contains': "LIKE %s ESCAPE '\\'",

   140         'icontains': "LIKE %s ESCAPE '\\'",

   141         'regex': 'REGEXP %s',

   142         'iregex': "REGEXP '(?i)' || %s",

   143         'gt': '> %s',

   144         'gte': '>= %s',

   145         'lt': '< %s',

   146         'lte': '<= %s',

   147         'startswith': "LIKE %s ESCAPE '\\'",

   148         'endswith': "LIKE %s ESCAPE '\\'",

   149         'istartswith': "LIKE %s ESCAPE '\\'",

   150         'iendswith': "LIKE %s ESCAPE '\\'",

   151     }

   152 

   153     def __init__(self, *args, **kwargs):

   154         super(DatabaseWrapper, self).__init__(*args, **kwargs)

   155 

   156         self.features = DatabaseFeatures()

   157         self.ops = DatabaseOperations()

   158         self.client = DatabaseClient(self)

   159         self.creation = DatabaseCreation(self)

   160         self.introspection = DatabaseIntrospection(self)

   161         self.validation = BaseDatabaseValidation(self)

   162 

   163     def _cursor(self):

   164         if self.connection is None:

   165             settings_dict = self.settings_dict

   166             if not settings_dict['NAME']:

   167                 from django.core.exceptions import ImproperlyConfigured

   168                 raise ImproperlyConfigured("Please fill out the database NAME in the settings module before using the database.")

   169             kwargs = {

   170                 'database': settings_dict['NAME'],

   171                 'detect_types': Database.PARSE_DECLTYPES | Database.PARSE_COLNAMES,

   172             }

   173             kwargs.update(settings_dict['OPTIONS'])

   174             self.connection = Database.connect(**kwargs)

   175             # Register extract, date_trunc, and regexp functions.

   176             self.connection.create_function("django_extract", 2, _sqlite_extract)

   177             self.connection.create_function("django_date_trunc", 2, _sqlite_date_trunc)

   178             self.connection.create_function("regexp", 2, _sqlite_regexp)

   179             connection_created.send(sender=self.__class__)

   180         return self.connection.cursor(factory=SQLiteCursorWrapper)

   181 

   182     def close(self):

   183         # If database is in memory, closing the connection destroys the

   184         # database. To prevent accidental data loss, ignore close requests on

   185         # an in-memory db.

   186         if self.settings_dict['NAME'] != ":memory:":

   187             BaseDatabaseWrapper.close(self)

   188 

   189 FORMAT_QMARK_REGEX = re.compile(r'(?![^%])%s')

   190 

   191 class SQLiteCursorWrapper(Database.Cursor):

   192     """

   193     Django uses "format" style placeholders, but pysqlite2 uses "qmark" style.

   194     This fixes it -- but note that if you want to use a literal "%s" in a query,

   195     you'll need to use "%%s".

   196     """

   197     def execute(self, query, params=()):

   198         query = self.convert_query(query)

   199         try:

   200             return Database.Cursor.execute(self, query, params)

   201         except Database.IntegrityError, e:

   202             raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]

   203         except Database.DatabaseError, e:

   204             raise utils.DatabaseError, utils.DatabaseError(*tuple(e)), sys.exc_info()[2]

   205 

   206     def executemany(self, query, param_list):

   207         query = self.convert_query(query)

   208         try:

   209             return Database.Cursor.executemany(self, query, param_list)

   210         except Database.IntegrityError, e:

   211             raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]

   212         except Database.DatabaseError, e:

   213             raise utils.DatabaseError, utils.DatabaseError(*tuple(e)), sys.exc_info()[2]

   214 

   215     def convert_query(self, query):

   216         return FORMAT_QMARK_REGEX.sub('?', query).replace('%%','%')

   217 

   218 def _sqlite_extract(lookup_type, dt):

   219     if dt is None:

   220         return None

   221     try:

   222         dt = util.typecast_timestamp(dt)

   223     except (ValueError, TypeError):

   224         return None

   225     if lookup_type == 'week_day':

   226         return (dt.isoweekday() % 7) + 1

   227     else:

   228         return getattr(dt, lookup_type)

   229 

   230 def _sqlite_date_trunc(lookup_type, dt):

   231     try:

   232         dt = util.typecast_timestamp(dt)

   233     except (ValueError, TypeError):

   234         return None

   235     if lookup_type == 'year':

   236         return "%i-01-01 00:00:00" % dt.year

   237     elif lookup_type == 'month':

   238         return "%i-%02i-01 00:00:00" % (dt.year, dt.month)

   239     elif lookup_type == 'day':

   240         return "%i-%02i-%02i 00:00:00" % (dt.year, dt.month, dt.day)

   241 

   242 def _sqlite_regexp(re_pattern, re_string):

   243     import re

   244     try:

   245         return bool(re.search(re_pattern, re_string))

   246     except:

   247         return False