Code

Opened 8 years ago

Closed 7 years ago

Last modified 7 years ago

#1468 closed defect (wontfix)

[patch] SQLite patch to use internal date conversion functions

Reported by: mildly@… Owned by: adrian
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: UI/UX:

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@… 8 years ago.

Download all attachments as: .zip

Change History (8)

comment:1 Changed 8 years ago by Malcolm Tredinnick <malcolm@…>

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.

Changed 8 years ago by mildly@…

comment:2 Changed 8 years ago by mildly@…

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

comment:3 Changed 8 years ago by Go

  • Type changed from enhancement to defect

comment:4 Changed 7 years ago by Simon G. <dev@…>

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

comment:5 Changed 7 years ago by Simon G. <dev@…>

  • Summary changed from SQLite patch to use internal date conversion functions to [patch] SQLite patch to use internal date conversion functions

comment:6 Changed 7 years ago by Simon G. <dev@…>

  • Triage Stage changed from Unreviewed to Design decision needed

comment:7 Changed 7 years ago by mtredinnick

  • Resolution set to wontfix
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.