SQLite3 behaves slightly differently than the postgresql_psycopg2 adapter with regards to handling decimal types and using SELECT statements with IN clauses that are empty.
SQLite3 will allow something like :
SELECT * FROM foo WHERE id in ()
That fails horribly in Postgresql.
SQLite3 also returns float back instead of decimal.Decimal for the Django decimal column type. The current driver can't marshall the values into a decimal.Decimal type because the underlying schema definition for SQL decimal types in sqlite is invalid. SQLite can only understand single word type names.
The following is a patch to make the sqlite3 driver use decimal.Decimal type for SQL decimal backed columns as well as raising a SyntaxError? exception if a SELECT with an empty IN clause is used.
Using float for a SQL decimal type is not correct since IEEE float values are imprecise and cause problems when dealing with monetary amounts. Cases like dealing with the Euro legally require handling 6 significant digits of precision for all currency exchange - so you really need to use a decimal type.
Index: base.py
===================================================================
--- base.py (revision 3549)
+++ base.py (working copy)
@@ -17,7 +17,20 @@
Database.register_converter("datetime", util.typecast_timestamp)
Database.register_converter("timestamp", util.typecast_timestamp)
Database.register_converter("TIMESTAMP", util.typecast_timestamp)
+try:
+ # this only exists in 2.4+
+ from decimal import Decimal
+ Database.register_converter("numeric", util.typecast_numeric)
+ Database.register_adapter(Decimal, util.rev_typecast_decimal)
+except:
+ pass
+import re
+
+# We need to trap any queries with empty parenthesis because they're invalid in
+# other database - like Postgresql
+empty_in_clause = re.compile(".* +in +\( *\).*", re.I)
+
def utf8rowFactory(cursor, row):
def utf8(s):
if type(s) == unicode:
@@ -74,6 +87,8 @@
"""
def execute(self, query, params=()):
query = self.convert_query(query, len(params))
+ if empty_in_clause.match(query):
+ raise SyntaxError, "Invalid SQL query. Your IN clause is empty. [%s]" % query
return Database.Cursor.execute(self, query, params)
def executemany(self, query, param_list):
Index: creation.py
===================================================================
--- creation.py (revision 3549)
+++ creation.py (working copy)
@@ -10,7 +10,7 @@
'DateTimeField': 'datetime',
'FileField': 'varchar(100)',
'FilePathField': 'varchar(100)',
- 'FloatField': 'numeric(%(max_digits)s, %(decimal_places)s)',
+ 'FloatField': 'numeric',
'ImageField': 'varchar(100)',
'IntegerField': 'integer',
'IPAddressField': 'char(15)',
