1 """

     2 SQLite3 backend for django.

     3 

     4 Python 2.3 and 2.4 require pysqlite2 (http://pysqlite.org/).

     5 

     6 Python 2.5 and later use the sqlite3 module in the standard library.

     7 """

     8 

     9 from django.db.backends import BaseDatabaseWrapper, BaseDatabaseFeatures, BaseDatabaseOperations, util

    10 try:

    11     try:

    12         from sqlite3 import dbapi2 as Database

    13     except ImportError:

    14         from pysqlite2 import dbapi2 as Database

    15 except ImportError, e:

    16     import sys

    17     from django.core.exceptions import ImproperlyConfigured

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

    19         module = 'pysqlite2'

    20     else:

    21         module = 'sqlite3'

    22     raise ImproperlyConfigured, "Error loading %s module: %s" % (module, e)

    23 

    24 try:

    25     import decimal

    26 except ImportError:

    27     from django.utils import _decimal as decimal # for Python 2.3

    28 

    29 DatabaseError = Database.DatabaseError

    30 IntegrityError = Database.IntegrityError

    31 

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

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

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

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

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

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

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

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

    40 

    41 class DatabaseFeatures(BaseDatabaseFeatures):

    42     supports_constraints = False

    43 

    44 class DatabaseOperations(BaseDatabaseOperations):

    45     def date_extract_sql(self, lookup_type, field_name):

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

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

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

    49 

    50     def date_trunc_sql(self, lookup_type, field_name):

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

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

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

    54 

    55     def drop_foreignkey_sql(self):

    56         return ""

    57 

    58     def pk_default_value(self):

    59         return 'NULL'

    60 

    61     def quote_name(self, name):

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

    63             return name # Quoting once is enough.

    64         return '"%s"' % name

    65 

    66     def no_limit_value(self):

    67         return -1

    68 

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

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

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

    72         # because constraints don't exist

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

    74                 (style.SQL_KEYWORD('DELETE'),

    75                  style.SQL_KEYWORD('FROM'),

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

    77                  ) for table in tables]

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

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

    80         return sql

    81 

    82 class DatabaseWrapper(BaseDatabaseWrapper):

    83     features = DatabaseFeatures()

    84     ops = DatabaseOperations()

    85 

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

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

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

    89     operators = {

    90         'exact': '= %s',

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

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

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

    94         'regex': 'REGEXP %s',

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

    96         'gt': '> %s',

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

    98         'lt': '< %s',

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

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

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

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

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

   104     }

   105 

   106     def _cursor(self, settings):

   107         if self.connection is None:

   108             kwargs = {

   109                 'database': settings.DATABASE_NAME,

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

   111             }

   112             kwargs.update(self.options)

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

   114             # Register extract, date_trunc, and regexp functions.

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

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

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

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

   119 

   120     def close(self):

   121         from django.conf import settings

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

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

   124         # an in-memory db.

   125         if settings.DATABASE_NAME != ":memory:":

   126             BaseDatabaseWrapper.close(self)

   127 

   128 class SQLiteCursorWrapper(Database.Cursor):

   129     """

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

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

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

   133     """

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

   135         query = self.convert_query(query, len(params))

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

   137 

   138     def executemany(self, query, param_list):

   139         try:

   140           query = self.convert_query(query, len(param_list[0]))

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

   142         except (IndexError,TypeError):

   143           # No parameter list provided

   144           return None

   145 

   146     def convert_query(self, query, num_params):

   147         return query % tuple("?" * num_params)

   148 

   149 def _sqlite_extract(lookup_type, dt):

   150     try:

   151         dt = util.typecast_timestamp(dt)

   152     except (ValueError, TypeError):

   153         return None

   154     return str(getattr(dt, lookup_type))

   155 

   156 def _sqlite_date_trunc(lookup_type, dt):

   157     try:

   158         dt = util.typecast_timestamp(dt)

   159     except (ValueError, TypeError):

   160         return None

   161     if lookup_type == 'year':

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

   163     elif lookup_type == 'month':

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

   165     elif lookup_type == 'day':

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

   167 

   168 def _sqlite_regexp(re_pattern, re_string):

   169     import re

   170     try:

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

   172     except:

   173         return False