1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
|
"""
SQLite3 backend for django. Requires pysqlite2 (http://pysqlite.org/).
"""
from django.db.backends import util
try:
try:
from sqlite3 import dbapi2 as Database
except ImportError:
from pysqlite2 import dbapi2 as Database
except ImportError, e:
import sys
from django.core.exceptions import ImproperlyConfigured
if sys.version_info < (2, 5, 0):
module = 'pysqlite2'
else:
module = 'sqlite3'
raise ImproperlyConfigured, "Error loading %s module: %s" % (module, e)
DatabaseError = Database.DatabaseError
Database.register_converter("bool", lambda s: str(s) == '1')
Database.register_converter("time", util.typecast_time)
Database.register_converter("date", util.typecast_date)
Database.register_converter("datetime", util.typecast_timestamp)
Database.register_converter("timestamp", util.typecast_timestamp)
Database.register_converter("TIMESTAMP", util.typecast_timestamp)
def utf8rowFactory(cursor, row):
def utf8(s):
if type(s) == unicode:
return s.encode("utf-8")
else:
return s
return [utf8(r) for r in row]
try:
# Only exists in Python 2.4+
from threading import local
except ImportError:
# Import copy of _thread_local.py from Python 2.4
from django.utils._threading_local import local
class DatabaseWrapper(local):
def __init__(self):
self.connection = None
self.queries = []
def cursor(self):
from django.conf import settings
if self.connection is None:
self.connection = Database.connect(settings.DATABASE_NAME,
detect_types=Database.PARSE_DECLTYPES | Database.PARSE_COLNAMES)
# Register extract and date_trunc functions.
self.connection.create_function("django_extract", 2, _sqlite_extract)
self.connection.create_function("django_date_trunc", 2, _sqlite_date_trunc)
cursor = self.connection.cursor(factory=SQLiteCursorWrapper)
cursor.row_factory = utf8rowFactory
if settings.DEBUG:
return util.CursorDebugWrapper(cursor, self)
else:
return cursor
def _commit(self):
self.connection.commit()
def _rollback(self):
if self.connection:
self.connection.rollback()
def close(self):
from django.conf import settings
# If database is in memory, closing the connection destroys the database.
# To prevent accidental data loss, ignore close requests on an in-memory db.
if self.connection is not None and settings.DATABASE_NAME != ":memory:":
self.connection.close()
self.connection = None
class SQLiteCursorWrapper(Database.Cursor):
"""
Django uses "format" style placeholders, but pysqlite2 uses "qmark" style.
This fixes it -- but note that if you want to use a literal "%s" in a query,
you'll need to use "%%s".
"""
def execute(self, query, params=()):
query = self.convert_query(query, len(params))
return Database.Cursor.execute(self, query, params)
def executemany(self, query, param_list):
query = self.convert_query(query, len(param_list[0]))
return Database.Cursor.executemany(self, query, param_list)
def convert_query(self, query, num_params):
return query % tuple("?" * num_params)
supports_constraints = False
def quote_name(name):
if name.startswith('"') and name.endswith('"'):
return name # Quoting once is enough.
return '"%s"' % name
dictfetchone = util.dictfetchone
dictfetchmany = util.dictfetchmany
dictfetchall = util.dictfetchall
def get_last_insert_id(cursor, table_name, pk_name):
return cursor.lastrowid
def get_date_extract_sql(lookup_type, table_name):
# lookup_type is 'year', 'month', 'day'
# sqlite doesn't support extract, so we fake it with the user-defined
# function _sqlite_extract that's registered in connect(), above.
return 'django_extract("%s", %s)' % (lookup_type.lower(), table_name)
def _sqlite_extract(lookup_type, dt):
try:
dt = util.typecast_timestamp(dt)
except (ValueError, TypeError):
return None
return str(getattr(dt, lookup_type))
def get_date_trunc_sql(lookup_type, field_name):
# lookup_type is 'year', 'month', 'day'
# sqlite doesn't support DATE_TRUNC, so we fake it as above.
return 'django_date_trunc("%s", %s)' % (lookup_type.lower(), field_name)
def get_limit_offset_sql(limit, offset=None):
sql = "LIMIT %s" % limit
if offset and offset != 0:
sql += " OFFSET %s" % offset
return sql
def get_random_function_sql():
return "RANDOM()"
def get_fulltext_search_sql(field_name):
raise NotImplementedError
def get_drop_foreignkey_sql():
return ""
def get_pk_default_value():
return "NULL"
def _sqlite_date_trunc(lookup_type, dt):
try:
dt = util.typecast_timestamp(dt)
except (ValueError, TypeError):
return None
if lookup_type == 'year':
return "%i-01-01 00:00:00" % dt.year
elif lookup_type == 'month':
return "%i-%02i-01 00:00:00" % (dt.year, dt.month)
elif lookup_type == 'day':
return "%i-%02i-%02i 00:00:00" % (dt.year, dt.month, dt.day)
def get_change_column_name_sql( table_name, indexes, old_col_name, new_col_name, col_def ):
# sqlite doesn't support column renames, so we fake it
# TODO: only supports a single primary key so far
pk_name = None
for key in indexes.keys():
if indexes[key]['primary_key']: pk_name = key
output = []
output.append( 'ALTER TABLE '+ quote_name(table_name) +' ADD COLUMN '+ quote_name(new_col_name) +' '+ col_def + ';' )
output.append( 'UPDATE '+ quote_name(table_name) +' SET '+ new_col_name +' = '+ old_col_name +' WHERE '+ pk_name +'=(select '+ pk_name +' from '+ table_name +');' )
output.append( '-- FYI: sqlite does not support deleting columns, so '+ quote_name(old_col_name) +' remains as cruft' )
# use the following when sqlite gets drop support
#output.append( 'ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(old_col_name) )
return '\n'.join(output)
def get_change_column_def_sql( table_name, col_name, col_def ):
# sqlite doesn't support column modifications, so we fake it
output = []
# TODO: fake via renaming the table, building a new one and deleting the old
output.append('-- sqlite does not support column modifications '+ quote_name(table_name) +'.'+ quote_name(col_name) +' to '+ col_def)
return '\n'.join(output)
def get_add_column_sql( table_name, col_name, col_type, null, unique, primary_key ):
output = []
field_output = []
field_output.append('ALTER TABLE')
field_output.append(quote_name(table_name))
field_output.append('ADD COLUMN')
field_output.append(quote_name(col_name))
field_output.append(col_type)
field_output.append(('%sNULL' % (not null and 'NOT ' or '')))
if unique:
field_output.append(('UNIQUE'))
if primary_key:
field_output.append(('PRIMARY KEY'))
output.append(' '.join(field_output) + ';')
return '\n'.join(output)
def get_drop_column_sql( table_name, col_name ):
output = []
output.append( '-- FYI: sqlite does not support deleting columns, so '+ quote_name(old_col_name) +' remains as cruft' )
# use the following when sqlite gets drop support
# output.append( '-- ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(col_name) )
return '\n'.join(output)
# SQLite requires LIKE statements to include an ESCAPE clause if the value
# being escaped has a percent or underscore in it.
# See http://www.sqlite.org/lang_expr.html for an explanation.
OPERATOR_MAPPING = {
'exact': '= %s',
'iexact': "LIKE %s ESCAPE '\\'",
'contains': "LIKE %s ESCAPE '\\'",
'icontains': "LIKE %s ESCAPE '\\'",
'gt': '> %s',
'gte': '>= %s',
'lt': '< %s',
'lte': '<= %s',
'startswith': "LIKE %s ESCAPE '\\'",
'endswith': "LIKE %s ESCAPE '\\'",
'istartswith': "LIKE %s ESCAPE '\\'",
'iendswith': "LIKE %s ESCAPE '\\'",
}
|
