Opened 11 years ago

Closed 9 years ago

Last modified 2 years 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 11 years ago.
470-2.patch (2.7 KB) - added by jws 11 years ago.
Second rev, with addition of escaping function
470-2813.patch (5.8 KB) - added by Jws 10 years ago.
patch updated for pending release of 0.95
470-2900.diff (4.8 KB) - added by jws 10 years ago.
update for 2900

Download all attachments as: .zip

Change History (19)

Changed 11 years ago by jws

Attachment: management.diff added

comment:1 Changed 11 years ago by Adrian Holovaty

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

comment:2 Changed 11 years ago by Adrian Holovaty

Resolution: wontfix
Status: newclosed

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 11 years ago by jws

Resolution: wontfix
Status: closedreopened

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 11 years ago by jws

Attachment: 470-2.patch added

Second rev, with addition of escaping function

comment:4 Changed 11 years ago by Jacob

Resolution: wontfix
Status: reopenedclosed

Re-marking as wontfix.

Changed 10 years ago by Jws

Attachment: 470-2813.patch added

patch updated for pending release of 0.95

comment:5 Changed 10 years ago by jws

Resolution: wontfix
Status: closedreopened

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 10 years ago by jws

Attachment: 470-2900.diff added

update for 2900

comment:6 Changed 10 years ago by anonymous

milestone: Version 0.92
Version: 0.91

comment:7 Changed 10 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 10 years ago by Gary Wilson <gary.wilson@…>

milestone: Version 0.92Version 1.0

0.92 is long gone.

comment:9 Changed 10 years ago by Jacob

Resolution: wontfix
Status: reopenedclosed

Again, this is wontfix for the reasons Adrian articulated.

comment:10 Changed 10 years ago by (none)

milestone: Version 1.0

Milestone Version 1.0 deleted

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

Patch needs improvement: set
Resolution: wontfix
Status: closedreopened

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 10 years ago by Simon G. <dev@…>

Triage Stage: UnreviewedDesign decision needed
Version: 0.91

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

comment:13 Changed 9 years ago by James Bennett

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 9 years ago by Jacob

Resolution: wontfix
Status: reopenedclosed

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

comment:15 Changed 2 years 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