Opened 10 years ago

Closed 8 years ago

Last modified 11 months ago

#470 closed enhancement (wontfix)

[patch] "default" values should be expressed in SQL schema

Reported by: jws Owned by: nobody
Component: Database layer (models, ORM) Version:
Severity: normal Keywords: sql schema
Cc: Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

Apply this diff to django/core/mamagement.py

Should work on any database.

75a76,77

if f.default <> meta.fields.NOT_PROVIDED:

field_output.append("DEFAULT '%s'" % (f.default,))

Attachments (4)

management.diff (145 bytes) - added by jws 10 years ago.
470-2.patch (2.7 KB) - added by jws 9 years ago.
Second rev, with addition of escaping function
470-2813.patch (5.8 KB) - added by Jws 9 years ago.
patch updated for pending release of 0.95
470-2900.diff (4.8 KB) - added by jws 9 years ago.
update for 2900

Download all attachments as: .zip

Change History (19)

Changed 10 years ago by jws

comment:1 Changed 10 years ago by adrian

  • Summary changed from diff to cause 'default' values to be expressed in sql schema to [patch] "default" values should be expressed in SQL schema

comment:2 Changed 9 years ago by adrian

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

I'm marking this as a wontfix because we don't have a way of converting Python objects to SQL-friendly syntax, for insertion into the "DEFAULT" clause in a CREATE TABLE statement. For example, the default value "John's test", which has a quote in it, may have to be represented differently in SQL, depending on the backend. Unfortunately, not all of the database modules (psycopg, MySQLdb, etc.) expose functionality that quotes the values.

comment:3 Changed 9 years ago by jws

  • Resolution wontfix deleted
  • Status changed from closed to reopened

GOAL:
Django should express the structure and relationships of the models in the database schema as fully as possible within the capabilities of a specific backend. This is important when working with other tools that do not use the Django ORM.

PROBLEM:
When django-admin creates sql statements in response to the 'sql','sqlclear' and related sub-commands, string concatenation is used rather than the parameterized queries that are used in the normal object manipulation code. If default values for fields contain delimiting characters(',",;,etc), the resulting sql will contain errors. The string describing the default value must be processed by a backend-specific character-escaping method that will alter any problem characters before it can be inserted into the sql statement.

SOLUTION:
When describing a field with a default value, if the backend has a method called escapechars(), a sql 'DEFAULT' clause is assembled and inserted. If no such method exists, no additional clause is inserted, though the Django objects still produce default values.

IMPLEMENTATION:
Postgresql: Updated and tested
Sqlite: Updated, not tested
Mysql: Not updated
MS-MSQL: Not updated

Extra notes:

pymysqldb exposes a quoting function that could simply be wrapped.

I'll update this patch again when I get the ability to test the other backends. Help is appreciated. However, patch only adds new functionality. Unmodified backends should continue to work as before.

Changed 9 years ago by jws

Second rev, with addition of escaping function

comment:4 Changed 9 years ago by jacob

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

Re-marking as wontfix.

Changed 9 years ago by Jws

patch updated for pending release of 0.95

comment:5 Changed 9 years ago by jws

  • Resolution wontfix deleted
  • Status changed from closed to reopened

I have updated my patch to the current svn with magic-removal mainlined in. The patch now supports all backends, as well. I would like to get this in in time for the 0.95 release.

Changed 9 years ago by jws

update for 2900

comment:6 Changed 9 years ago by anonymous

  • milestone set to Version 0.92
  • Version set to 0.91

comment:7 Changed 9 years ago by newpers

it would be nice to see this merged into the trunk. as i stated in #django, an ORM can't do everything so there is a need for custom sql. it frustrates me when i do an INSERT, but it doesn't know what to default to. try doing:

I actually needed this today: INSERT INTO tbl DEFAULT VALUES;

comment:8 Changed 9 years ago by Gary Wilson <gary.wilson@…>

  • milestone changed from Version 0.92 to Version 1.0

0.92 is long gone.

comment:9 Changed 9 years ago by jacob

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

Again, this is wontfix for the reasons Adrian articulated.

comment:10 Changed 8 years ago by anonymous

  • milestone Version 1.0 deleted

Milestone Version 1.0 deleted

comment:11 Changed 8 years ago by Cedric Shock <cedric@…>

  • Patch needs improvement set
  • Resolution wontfix deleted
  • Status changed from closed to reopened

I am reopening this as the reasons Adrian articulated were addressed in later patches without further rebuttal. Default values would be very useful in introspective upgrading of databases, although other means of providing values to new fields per existing row through a more complete upgrade architecture would also be desirable.

I have a few issues with how this patch works. The first is that the user is to provide the default value in the format of a string to the database, not in the format of a value of the field. This can be solved easily with the Field get_db_prep_save:

newest patch, management,py, new line 173:

escaped_string =  django.db.backend.escapechars(f.get_db_prep_save(f.default))

Furthermore the code makes the assumption that values shall be quoted with single quotes. It would be more appropriate for the escapechars interface to return an entire quoted string usable as a value (move the single quotes into each escapechars function, making new line 174 in the same file like:)

field_output.append(style.SQL_KEYWORD("DEFAULT %s" % (escaped_string,)))

Furthermore, the implementation of the escapechars (or otherwise named) function should be done as much as is possible and appropriate using routines provided by the various backends (psycodb, etc.).

The DB-API way of dealing with escaped values is via the cursor execute. That would change these two lines to something like:

field_output.append(style.SQL_KEYWORD("DEFAULT %%s")
field_output_params.append(f.get_db_prep_save(f.default))

However this last way would require complete reworking of the management.py everything to support passing these params around with the sql strings, and emulation of DB-API execute substitution, for each backend, to output SQL statements. It would be nice if the DB-API interface also provided something like execute_sql(...) and executemany_sql(...) which returned strings such that execute(execute_sql(...)) and execute(...) were functionally equivalent.

I'd also be tempted to rename escapechars to escapedefault due to its very specific intent, or to escapeparam if it is intended to work on what DB-API calls parameters in general.

Furthermore, default values for fields should be reflected in the admin interface.

comment:12 Changed 8 years ago by Simon G. <dev@…>

  • Triage Stage changed from Unreviewed to Design decision needed
  • Version 0.91 deleted

& once again around the Design-Decision-needed loop...

comment:13 Changed 8 years ago by ubernostrum

Still needs to address the issue of callable defaults -- since those are calculated on the fly at the time of insertion, it's not going to be possible to express them in SQL.

comment:14 Changed 8 years ago by jacob

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

Once again, I'm marking wontfix. Let's bring this up on django-dev if more debate is needed.

comment:15 Changed 11 months ago by aron45

  • Easy pickings unset
  • UI/UX unset

Hi, bumping up here old ticket.
I think this is a very important feature, as I'm writing the sql my self for this reason.

any solutions?

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