Opened 19 years ago

Closed 18 years ago

Last modified 17 years ago

#1468 closed defect (wontfix)

[patch] SQLite patch to use internal date conversion functions

Reported by: mildly@… Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version:
Severity: normal Keywords: sqlite
Cc: Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Some time ago I modified the SQLite driver to use SQLite's internal strftime(), instead of using type callbacks. It's mostly straight-forward code, however it did require a small change to core/meta/fields.py to ensure it only generated YY-MM-DD for range searches.

Index: django/core/db/backends/sqlite3.py
===================================================================
--- django/core/db/backends/sqlite3.py  (revision 2491)
+++ django/core/db/backends/sqlite3.py  (working copy)
@@ -33,9 +33,6 @@
         from django.conf.settings import DATABASE_NAME, DEBUG
         if self.connection is None:
             self.connection = Database.connect(DATABASE_NAME, detect_types=Database.PARSE_DECLTYPES)
-            # register extract and date_trun 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 DEBUG:
@@ -88,19 +85,17 @@
     # 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)
+    strfvals = {
+        'year': '%%Y',
+        'month': '%%m',
+        'day': '%%d',
+    }
+    return 'strftime( %s, "%s" ) ' % ( table_name, strfvals[lookup_type.lower()] )

-def _sqlite_extract(lookup_type, dt):
-    try:
-        dt = typecasts.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)
+    return 'strftime( "%%%%Y-%%%%m-%%%%d 00:00:00", %s, "start of %s" )' % (field_name, lookup_type.lower())

 def get_limit_offset_sql(limit, offset=None):
     sql = "LIMIT %s" % limit
@@ -111,18 +106,6 @@
 def get_random_function_sql():
     return "RANDOM()"

-def _sqlite_date_trunc(lookup_type, dt):
-    try:
-        dt = typecasts.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_table_list(cursor):
     cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
     return [row[0] for row in cursor.fetchall()]
Index: django/core/meta/fields.py
===================================================================
--- django/core/meta/fields.py  (revision 2491)
+++ django/core/meta/fields.py  (working copy)
@@ -389,7 +389,7 @@

     def get_db_prep_lookup(self, lookup_type, value):
         if lookup_type == 'range':                                                                                                  

Attachments (1)

sqlite.patch (3.0 KB ) - added by mildly@… 19 years ago.

Download all attachments as: .zip

Change History (8)

comment:1 by Malcolm Tredinnick <malcolm@…>, 19 years ago

Is this patch complete? It looks like the chunk for django/core/meta/fields.py got cut off (there are no changes in it). Also, please attach the patch as an attachment, rather than including the text in the body so that it is easier to test and apply.

by mildly@…, 19 years ago

Attachment: sqlite.patch added

comment:2 by mildly@…, 19 years ago

My appologies. Didn't notice the attach button there.

comment:3 by Go, 18 years ago

Type: enhancementdefect

comment:4 by Simon G. <dev@…>, 18 years ago

Has patch: set
Keywords: sqlite added
Needs tests: set

comment:5 by Simon G. <dev@…>, 18 years ago

Summary: SQLite patch to use internal date conversion functions[patch] SQLite patch to use internal date conversion functions

comment:6 by Simon G. <dev@…>, 18 years ago

Triage Stage: UnreviewedDesign decision needed

comment:7 by Malcolm Tredinnick, 18 years ago

Resolution: wontfix
Status: newclosed

Since the "range" query can take full datetime objects (including times, not just dates), we can't restrict it to just querying on year/month/day triples.

Note: See TracTickets for help on using tickets.
Back to Top