id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
2519	aligning sqlite3 with postgresql_psycopg2 adapter	crankycoder@…	Adrian Holovaty	"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)',

}}}"	enhancement	closed	Database layer (models, ORM)		normal	fixed		victor.ng@… adurdin@…	Design decision needed	1	0	0	1	0	0