Opened 17 years ago
Last modified 9 days ago
#470 assigned New feature
Add Field.db_default for defining database defaults
Reported by: | jws | Owned by: | Ayush Joshi |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | |
Severity: | normal | Keywords: | sql schema |
Cc: | Ryan Hiebert, Ian Foote, Ryan Moore, Václav Řehák, Hannes Ljungberg, Marcin Nowak, Adrian Turjak, Doug Harris, Leigh Brenecki, Johannes Maron, Charlie Denton, Matt Goldman, raydeal, Todor Velichkov | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
https://github.com/django/django/pull/13709
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)
Change History (50)
Changed 17 years ago by
Attachment: | management.diff added |
---|
comment:1 Changed 17 years ago by
Summary: | diff to cause 'default' values to be expressed in sql schema → [patch] "default" values should be expressed in SQL schema |
---|
comment:2 Changed 16 years ago by
Resolution: | → wontfix |
---|---|
Status: | new → closed |
comment:3 Changed 16 years ago by
Resolution: | wontfix |
---|---|
Status: | closed → 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 16 years ago by
Attachment: | 470-2.patch added |
---|
Second rev, with addition of escaping function
comment:4 Changed 16 years ago by
Resolution: | → wontfix |
---|---|
Status: | reopened → closed |
Re-marking as wontfix.
comment:5 Changed 16 years ago by
Resolution: | wontfix |
---|---|
Status: | closed → 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.
comment:6 Changed 16 years ago by
milestone: | → Version 0.92 |
---|---|
Version: | → 0.91 |
comment:7 Changed 16 years ago by
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:9 Changed 16 years ago by
Resolution: | → wontfix |
---|---|
Status: | reopened → closed |
Again, this is wontfix for the reasons Adrian articulated.
comment:11 Changed 15 years ago by
Patch needs improvement: | set |
---|---|
Resolution: | wontfix |
Status: | closed → 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 15 years ago by
Triage Stage: | Unreviewed → Design decision needed |
---|---|
Version: | 0.91 |
& once again around the Design-Decision-needed loop...
comment:13 Changed 15 years ago by
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 15 years ago by
Resolution: | → wontfix |
---|---|
Status: | reopened → closed |
Once again, I'm marking wontfix. Let's bring this up on django-dev if more debate is needed.
comment:15 Changed 8 years ago by
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?
comment:16 Changed 5 years ago by
Please reopen it and implement using INSERT ... RETURNING (for postgres). This may be optional behaviour dependent on db engine used.
Defaults computed at the database level are very important. This may be optional feature, but it should be available. The database is most important and long-lived part of any bigger system.
Python-like callbacks passed as defaults shouldn't be supported, of course. They may behave same as before and they should not be listed in RETURNING clause.
comment:17 Changed 5 years ago by
There was a discussion on this topic, with some partial work to support the feature, about two years ago: https://groups.google.com/d/topic/django-developers/3mcro17Gb40/discussion
comment:18 Changed 4 years ago by
Has patch: | unset |
---|---|
Patch needs improvement: | unset |
Summary: | [patch] "default" values should be expressed in SQL schema → Add Field.db_default for defining database defaults |
Triage Stage: | Design decision needed → Accepted |
Type: | enhancement → New feature |
Reopening as per the mailing list discussion in the previous comment.
comment:19 Changed 4 years ago by
Resolution: | wontfix |
---|---|
Status: | closed → new |
comment:20 Changed 4 years ago by
Further justification for this feature -- it makes zero-downtime DB migrations easier: #29266.
comment:21 Changed 4 years ago by
Cc: | Ryan Hiebert added |
---|
comment:22 Changed 4 years ago by
Cc: | Simon Charette added |
---|
comment:23 Changed 4 years ago by
Cc: | Ian Foote added |
---|
comment:24 Changed 3 years ago by
Cc: | Ryan Moore added |
---|
comment:25 Changed 2 years ago by
Cc: | Václav Řehák added |
---|
comment:26 Changed 2 years ago by
Cc: | Hannes Ljungberg added |
---|
comment:27 Changed 2 years ago by
Cc: | Marcin Nowak added |
---|
comment:28 Changed 2 years ago by
Hi.
Django 3.x generates sql with drop default:
ALTER TABLE "x" ADD COLUMN "y" integer DEFAULT 0 NOT NULL CHECK ("y" >= 0); ALTER TABLE "x" ALTER COLUMN "y" DROP DEFAULT;
The whole thing is about not adding DROP DEFAULT. How about some kind of option for database backend?
comment:29 Changed 2 years ago by
Currently using this https://github.com/3YOURMIND/django-add-default-value to run migrations without downtime using MySQL in strict mode.
comment:30 Changed 2 years ago by
Hi. 15 years have passed since this important bug report. Every time we're uploading new version of the application, which includes new not nullable fields in db tables, our service is failing. Db changes are applied first, then app services are restarting on all servers (it takes few minutes). During this time old version loaded into memory is failing due to missing defaults:
IntegrityError null value in column "X" violates not-null constraint DETAIL: Failing row contains (...)
For us, default values must be set at the database layer. Please add support of db_default attribute.
comment:31 Changed 2 years ago by
Cc: | Simon Charette removed |
---|
comment:32 Changed 18 months ago by
Cc: | Adrian Turjak added |
---|
comment:33 Changed 18 months ago by
Cc: | Doug Harris added |
---|
comment:34 Changed 18 months ago by
Owner: | changed from nobody to Ian Foote |
---|---|
Status: | new → assigned |
comment:35 Changed 18 months ago by
Has patch: | set |
---|---|
Needs documentation: | set |
comment:36 Changed 16 months ago by
Cc: | Leigh Brenecki added |
---|
comment:37 Changed 14 months ago by
Needs documentation: | unset |
---|
comment:38 Changed 12 months ago by
Cc: | Johannes Maron added |
---|
comment:39 Changed 10 months ago by
Patch needs improvement: | set |
---|
comment:40 Changed 10 months ago by
Cc: | Charlie Denton added |
---|
comment:41 Changed 8 months ago by
I am getting bitten by this quite a lot.
I would suggest that long term it would be a good idea to have one field for the default value set in the database schema and one that is dynamic and set on the Django level. Maybe default
and default_db
to keep backwards compatibility, or default_dynamic
and default
to improve the default.
comment:42 Changed 8 months ago by
Description: | modified (diff) |
---|
comment:43 Changed 5 months ago by
Cc: | Matt Goldman added |
---|
comment:44 Changed 3 months ago by
Owner: | changed from Ian Foote to Ayush Joshi |
---|
comment:45 Changed 7 weeks ago by
Cc: | raydeal added |
---|
comment:46 Changed 9 days ago by
Cc: | Todor Velichkov added |
---|
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.