/bangkokhotel/lib/python2.5/site-packages/django/db/backends/sqlite3/base.py
Python | 404 lines | 336 code | 13 blank | 55 comment | 14 complexity | bc5be2daac837af62d6e63d0df2f7547 MD5 | raw file
1"""
2SQLite3 backend for django.
3
4Works with either the pysqlite2 module or the sqlite3 module in the
5standard library.
6"""
7
8import datetime
9import decimal
10import warnings
11import re
12import sys
13
14from django.db import utils
15from django.db.backends import *
16from django.db.backends.signals import connection_created
17from django.db.backends.sqlite3.client import DatabaseClient
18from django.db.backends.sqlite3.creation import DatabaseCreation
19from django.db.backends.sqlite3.introspection import DatabaseIntrospection
20from django.utils.dateparse import parse_date, parse_datetime, parse_time
21from django.utils.safestring import SafeString
22from django.utils import timezone
23
24try:
25 try:
26 from pysqlite2 import dbapi2 as Database
27 except ImportError, e1:
28 from sqlite3 import dbapi2 as Database
29except ImportError, exc:
30 from django.core.exceptions import ImproperlyConfigured
31 raise ImproperlyConfigured("Error loading either pysqlite2 or sqlite3 modules (tried in that order): %s" % exc)
32
33
34DatabaseError = Database.DatabaseError
35IntegrityError = Database.IntegrityError
36
37def parse_datetime_with_timezone_support(value):
38 dt = parse_datetime(value)
39 # Confirm that dt is naive before overwriting its tzinfo.
40 if dt is not None and settings.USE_TZ and timezone.is_naive(dt):
41 dt = dt.replace(tzinfo=timezone.utc)
42 return dt
43
44def adapt_datetime_with_timezone_support(value):
45 # Equivalent to DateTimeField.get_db_prep_value. Used only by raw SQL.
46 if settings.USE_TZ:
47 if timezone.is_naive(value):
48 warnings.warn(u"SQLite received a naive datetime (%s)"
49 u" while time zone support is active." % value,
50 RuntimeWarning)
51 default_timezone = timezone.get_default_timezone()
52 value = timezone.make_aware(value, default_timezone)
53 value = value.astimezone(timezone.utc).replace(tzinfo=None)
54 return value.isoformat(" ")
55
56Database.register_converter("bool", lambda s: str(s) == '1')
57Database.register_converter("time", parse_time)
58Database.register_converter("date", parse_date)
59Database.register_converter("datetime", parse_datetime_with_timezone_support)
60Database.register_converter("timestamp", parse_datetime_with_timezone_support)
61Database.register_converter("TIMESTAMP", parse_datetime_with_timezone_support)
62Database.register_converter("decimal", util.typecast_decimal)
63Database.register_adapter(datetime.datetime, adapt_datetime_with_timezone_support)
64Database.register_adapter(decimal.Decimal, util.rev_typecast_decimal)
65if Database.version_info >= (2, 4, 1):
66 # Starting in 2.4.1, the str type is not accepted anymore, therefore,
67 # we convert all str objects to Unicode
68 # As registering a adapter for a primitive type causes a small
69 # slow-down, this adapter is only registered for sqlite3 versions
70 # needing it (Python 2.6 and up).
71 Database.register_adapter(str, lambda s: s.decode('utf-8'))
72 Database.register_adapter(SafeString, lambda s: s.decode('utf-8'))
73
74class DatabaseFeatures(BaseDatabaseFeatures):
75 # SQLite cannot handle us only partially reading from a cursor's result set
76 # and then writing the same rows to the database in another cursor. This
77 # setting ensures we always read result sets fully into memory all in one
78 # go.
79 can_use_chunked_reads = False
80 test_db_allows_multiple_connections = False
81 supports_unspecified_pk = True
82 supports_timezones = False
83 supports_1000_query_parameters = False
84 supports_mixed_date_datetime_comparisons = False
85 has_bulk_insert = True
86 can_combine_inserts_with_and_without_auto_increment_pk = False
87
88 def _supports_stddev(self):
89 """Confirm support for STDDEV and related stats functions
90
91 SQLite supports STDDEV as an extension package; so
92 connection.ops.check_aggregate_support() can't unilaterally
93 rule out support for STDDEV. We need to manually check
94 whether the call works.
95 """
96 cursor = self.connection.cursor()
97 cursor.execute('CREATE TABLE STDDEV_TEST (X INT)')
98 try:
99 cursor.execute('SELECT STDDEV(*) FROM STDDEV_TEST')
100 has_support = True
101 except utils.DatabaseError:
102 has_support = False
103 cursor.execute('DROP TABLE STDDEV_TEST')
104 return has_support
105
106class DatabaseOperations(BaseDatabaseOperations):
107 def bulk_batch_size(self, fields, objs):
108 """
109 SQLite has a compile-time default (SQLITE_LIMIT_VARIABLE_NUMBER) of
110 999 variables per query.
111 """
112 return (999 // len(fields)) if len(fields) > 0 else len(objs)
113
114 def date_extract_sql(self, lookup_type, field_name):
115 # sqlite doesn't support extract, so we fake it with the user-defined
116 # function django_extract that's registered in connect(). Note that
117 # single quotes are used because this is a string (and could otherwise
118 # cause a collision with a field name).
119 return "django_extract('%s', %s)" % (lookup_type.lower(), field_name)
120
121 def date_interval_sql(self, sql, connector, timedelta):
122 # It would be more straightforward if we could use the sqlite strftime
123 # function, but it does not allow for keeping six digits of fractional
124 # second information, nor does it allow for formatting date and datetime
125 # values differently. So instead we register our own function that
126 # formats the datetime combined with the delta in a manner suitable
127 # for comparisons.
128 return u'django_format_dtdelta(%s, "%s", "%d", "%d", "%d")' % (sql,
129 connector, timedelta.days, timedelta.seconds, timedelta.microseconds)
130
131 def date_trunc_sql(self, lookup_type, field_name):
132 # sqlite doesn't support DATE_TRUNC, so we fake it with a user-defined
133 # function django_date_trunc that's registered in connect(). Note that
134 # single quotes are used because this is a string (and could otherwise
135 # cause a collision with a field name).
136 return "django_date_trunc('%s', %s)" % (lookup_type.lower(), field_name)
137
138 def drop_foreignkey_sql(self):
139 return ""
140
141 def pk_default_value(self):
142 return "NULL"
143
144 def quote_name(self, name):
145 if name.startswith('"') and name.endswith('"'):
146 return name # Quoting once is enough.
147 return '"%s"' % name
148
149 def no_limit_value(self):
150 return -1
151
152 def sql_flush(self, style, tables, sequences):
153 # NB: The generated SQL below is specific to SQLite
154 # Note: The DELETE FROM... SQL generated below works for SQLite databases
155 # because constraints don't exist
156 sql = ['%s %s %s;' % \
157 (style.SQL_KEYWORD('DELETE'),
158 style.SQL_KEYWORD('FROM'),
159 style.SQL_FIELD(self.quote_name(table))
160 ) for table in tables]
161 # Note: No requirement for reset of auto-incremented indices (cf. other
162 # sql_flush() implementations). Just return SQL at this point
163 return sql
164
165 def value_to_db_datetime(self, value):
166 if value is None:
167 return None
168
169 # SQLite doesn't support tz-aware datetimes
170 if timezone.is_aware(value):
171 if settings.USE_TZ:
172 value = value.astimezone(timezone.utc).replace(tzinfo=None)
173 else:
174 raise ValueError("SQLite backend does not support timezone-aware datetimes when USE_TZ is False.")
175
176 return unicode(value)
177
178 def value_to_db_time(self, value):
179 if value is None:
180 return None
181
182 # SQLite doesn't support tz-aware datetimes
183 if timezone.is_aware(value):
184 raise ValueError("SQLite backend does not support timezone-aware times.")
185
186 return unicode(value)
187
188 def year_lookup_bounds(self, value):
189 first = '%s-01-01'
190 second = '%s-12-31 23:59:59.999999'
191 return [first % value, second % value]
192
193 def convert_values(self, value, field):
194 """SQLite returns floats when it should be returning decimals,
195 and gets dates and datetimes wrong.
196 For consistency with other backends, coerce when required.
197 """
198 internal_type = field.get_internal_type()
199 if internal_type == 'DecimalField':
200 return util.typecast_decimal(field.format_number(value))
201 elif internal_type and internal_type.endswith('IntegerField') or internal_type == 'AutoField':
202 return int(value)
203 elif internal_type == 'DateField':
204 return parse_date(value)
205 elif internal_type == 'DateTimeField':
206 return parse_datetime_with_timezone_support(value)
207 elif internal_type == 'TimeField':
208 return parse_time(value)
209
210 # No field, or the field isn't known to be a decimal or integer
211 return value
212
213 def bulk_insert_sql(self, fields, num_values):
214 res = []
215 res.append("SELECT %s" % ", ".join(
216 "%%s AS %s" % self.quote_name(f.column) for f in fields
217 ))
218 res.extend(["UNION SELECT %s" % ", ".join(["%s"] * len(fields))] * (num_values - 1))
219 return " ".join(res)
220
221class DatabaseWrapper(BaseDatabaseWrapper):
222 vendor = 'sqlite'
223 # SQLite requires LIKE statements to include an ESCAPE clause if the value
224 # being escaped has a percent or underscore in it.
225 # See http://www.sqlite.org/lang_expr.html for an explanation.
226 operators = {
227 'exact': '= %s',
228 'iexact': "LIKE %s ESCAPE '\\'",
229 'contains': "LIKE %s ESCAPE '\\'",
230 'icontains': "LIKE %s ESCAPE '\\'",
231 'regex': 'REGEXP %s',
232 'iregex': "REGEXP '(?i)' || %s",
233 'gt': '> %s',
234 'gte': '>= %s',
235 'lt': '< %s',
236 'lte': '<= %s',
237 'startswith': "LIKE %s ESCAPE '\\'",
238 'endswith': "LIKE %s ESCAPE '\\'",
239 'istartswith': "LIKE %s ESCAPE '\\'",
240 'iendswith': "LIKE %s ESCAPE '\\'",
241 }
242
243 def __init__(self, *args, **kwargs):
244 super(DatabaseWrapper, self).__init__(*args, **kwargs)
245
246 self.features = DatabaseFeatures(self)
247 self.ops = DatabaseOperations(self)
248 self.client = DatabaseClient(self)
249 self.creation = DatabaseCreation(self)
250 self.introspection = DatabaseIntrospection(self)
251 self.validation = BaseDatabaseValidation(self)
252
253 def _sqlite_create_connection(self):
254 settings_dict = self.settings_dict
255 if not settings_dict['NAME']:
256 from django.core.exceptions import ImproperlyConfigured
257 raise ImproperlyConfigured("Please fill out the database NAME in the settings module before using the database.")
258 kwargs = {
259 'database': settings_dict['NAME'],
260 'detect_types': Database.PARSE_DECLTYPES | Database.PARSE_COLNAMES,
261 }
262 kwargs.update(settings_dict['OPTIONS'])
263 # Always allow the underlying SQLite connection to be shareable
264 # between multiple threads. The safe-guarding will be handled at a
265 # higher level by the `BaseDatabaseWrapper.allow_thread_sharing`
266 # property. This is necessary as the shareability is disabled by
267 # default in pysqlite and it cannot be changed once a connection is
268 # opened.
269 if 'check_same_thread' in kwargs and kwargs['check_same_thread']:
270 warnings.warn(
271 'The `check_same_thread` option was provided and set to '
272 'True. It will be overriden with False. Use the '
273 '`DatabaseWrapper.allow_thread_sharing` property instead '
274 'for controlling thread shareability.',
275 RuntimeWarning
276 )
277 kwargs.update({'check_same_thread': False})
278 self.connection = Database.connect(**kwargs)
279 # Register extract, date_trunc, and regexp functions.
280 self.connection.create_function("django_extract", 2, _sqlite_extract)
281 self.connection.create_function("django_date_trunc", 2, _sqlite_date_trunc)
282 self.connection.create_function("regexp", 2, _sqlite_regexp)
283 self.connection.create_function("django_format_dtdelta", 5, _sqlite_format_dtdelta)
284 connection_created.send(sender=self.__class__, connection=self)
285
286 def _cursor(self):
287 if self.connection is None:
288 self._sqlite_create_connection()
289 return self.connection.cursor(factory=SQLiteCursorWrapper)
290
291 def check_constraints(self, table_names=None):
292 """
293 Checks each table name in `table_names` for rows with invalid foreign key references. This method is
294 intended to be used in conjunction with `disable_constraint_checking()` and `enable_constraint_checking()`, to
295 determine if rows with invalid references were entered while constraint checks were off.
296
297 Raises an IntegrityError on the first invalid foreign key reference encountered (if any) and provides
298 detailed information about the invalid reference in the error message.
299
300 Backends can override this method if they can more directly apply constraint checking (e.g. via "SET CONSTRAINTS
301 ALL IMMEDIATE")
302 """
303 cursor = self.cursor()
304 if table_names is None:
305 table_names = self.introspection.get_table_list(cursor)
306 for table_name in table_names:
307 primary_key_column_name = self.introspection.get_primary_key_column(cursor, table_name)
308 if not primary_key_column_name:
309 continue
310 key_columns = self.introspection.get_key_columns(cursor, table_name)
311 for column_name, referenced_table_name, referenced_column_name in key_columns:
312 cursor.execute("""
313 SELECT REFERRING.`%s`, REFERRING.`%s` FROM `%s` as REFERRING
314 LEFT JOIN `%s` as REFERRED
315 ON (REFERRING.`%s` = REFERRED.`%s`)
316 WHERE REFERRING.`%s` IS NOT NULL AND REFERRED.`%s` IS NULL"""
317 % (primary_key_column_name, column_name, table_name, referenced_table_name,
318 column_name, referenced_column_name, column_name, referenced_column_name))
319 for bad_row in cursor.fetchall():
320 raise utils.IntegrityError("The row in table '%s' with primary key '%s' has an invalid "
321 "foreign key: %s.%s contains a value '%s' that does not have a corresponding value in %s.%s."
322 % (table_name, bad_row[0], table_name, column_name, bad_row[1],
323 referenced_table_name, referenced_column_name))
324
325 def close(self):
326 self.validate_thread_sharing()
327 # If database is in memory, closing the connection destroys the
328 # database. To prevent accidental data loss, ignore close requests on
329 # an in-memory db.
330 if self.settings_dict['NAME'] != ":memory:":
331 BaseDatabaseWrapper.close(self)
332
333FORMAT_QMARK_REGEX = re.compile(r'(?<!%)%s')
334
335class SQLiteCursorWrapper(Database.Cursor):
336 """
337 Django uses "format" style placeholders, but pysqlite2 uses "qmark" style.
338 This fixes it -- but note that if you want to use a literal "%s" in a query,
339 you'll need to use "%%s".
340 """
341 def execute(self, query, params=()):
342 query = self.convert_query(query)
343 try:
344 return Database.Cursor.execute(self, query, params)
345 except Database.IntegrityError, e:
346 raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]
347 except Database.DatabaseError, e:
348 raise utils.DatabaseError, utils.DatabaseError(*tuple(e)), sys.exc_info()[2]
349
350 def executemany(self, query, param_list):
351 query = self.convert_query(query)
352 try:
353 return Database.Cursor.executemany(self, query, param_list)
354 except Database.IntegrityError, e:
355 raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]
356 except Database.DatabaseError, e:
357 raise utils.DatabaseError, utils.DatabaseError(*tuple(e)), sys.exc_info()[2]
358
359 def convert_query(self, query):
360 return FORMAT_QMARK_REGEX.sub('?', query).replace('%%','%')
361
362def _sqlite_extract(lookup_type, dt):
363 if dt is None:
364 return None
365 try:
366 dt = util.typecast_timestamp(dt)
367 except (ValueError, TypeError):
368 return None
369 if lookup_type == 'week_day':
370 return (dt.isoweekday() % 7) + 1
371 else:
372 return getattr(dt, lookup_type)
373
374def _sqlite_date_trunc(lookup_type, dt):
375 try:
376 dt = util.typecast_timestamp(dt)
377 except (ValueError, TypeError):
378 return None
379 if lookup_type == 'year':
380 return "%i-01-01 00:00:00" % dt.year
381 elif lookup_type == 'month':
382 return "%i-%02i-01 00:00:00" % (dt.year, dt.month)
383 elif lookup_type == 'day':
384 return "%i-%02i-%02i 00:00:00" % (dt.year, dt.month, dt.day)
385
386def _sqlite_format_dtdelta(dt, conn, days, secs, usecs):
387 try:
388 dt = util.typecast_timestamp(dt)
389 delta = datetime.timedelta(int(days), int(secs), int(usecs))
390 if conn.strip() == '+':
391 dt = dt + delta
392 else:
393 dt = dt - delta
394 except (ValueError, TypeError):
395 return None
396 # typecast_timestamp returns a date or a datetime without timezone.
397 # It will be formatted as "%Y-%m-%d" or "%Y-%m-%d %H:%M:%S[.%f]"
398 return str(dt)
399
400def _sqlite_regexp(re_pattern, re_string):
401 try:
402 return bool(re.search(re_pattern, re_string))
403 except:
404 return False