#29170: Oracle - Unable to add triggers in migrations, semicolon removed.
-------------------------------------+-------------------------------------
Reporter: Danny | Owner: nobody
Willems |
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) | Keywords: oracle trigger
Severity: Normal | database
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
We have several database triggers we need to insert into our Oracle
database using a custom migration. The migration runs without raising an
error and the triggers are created in the database, however, when they are
invoked as a result of various operations in the Django admin we see an
error that says that the trigger cannot be compiled. After investigation
we realised that a required semicolon was being removed from the SQL
defined in the migration. Ordinarily this is removed from standard SQL
statements such as SELECT, INSERT etc but in the case of triggers it is
required as a way to delimit multiple BEGIN...END statements.
After debugging the issue we found the cause in this line of code:
https://github.com/django/django/blob/master/django/db/backends/oracle/base.py#L481
It appears that the blanket assumption that cx_Oracle does not require
semicolons does not hold for triggers.
Here is a simplified migration that shows the issue:
{{{
from django.db import migrations
class Migration(migrations.Migration):
dependencies = []
create_trigger_insert_entry = """
CREATE OR REPLACE TRIGGER UPDATE_BALANCE
AFTER DELETE OR INSERT OR UPDATE OF AMOUNT ON ENTRY
BEGIN
UPDATE ACCOUNT_BALANCE B
SET (B.BALANCE, B.ACCOUNT) = (SELECT SUM(AMOUNT) sum_amount, account
FROM ENTRY e WHERE e.ACCOUNT = B.ACCOUNT
GROUP BY ACCOUNT)
WHERE EXISTS (SELECT 1 FROM ENTRY e WHERE e.ACCOUNT = B.ACCOUNT);
END;
"""
delete_trigger_insert_entry = "DROP TRIGGER UPDATE_BALANCE"
operations = [
migrations.CreateModel(
name='Account',
fields=[
('name', models.CharField(max_length=32, primary_key=True,
serialize=False)),
],
options={
'db_table': 'account',
'managed': True,
},
),
migrations.CreateModel(
name='Entry',
fields=[
('value_date', models.DateTimeField()),
('amount', models.DecimalField(decimal_places=8,
max_digits=23)),
],
options={
'db_table': 'entry',
'managed': True,
},
),
migrations.CreateModel(
name='AccountBalance',
fields=[
('account', models.OneToOneField(db_column='account',
on_delete=django.db.models.deletion.DO_NOTHING, primary_key=True,
serialize=False, to='app.Account')),
('balance', models.DecimalField(decimal_places=14,
max_digits=38)),
],
options={
'db_table': 'account_balance',
'managed': True,
},
),
migrations.AddField(
model_name='entry',
name='account',
field=models.ForeignKey(db_column='account',
on_delete=django.db.models.deletion.CASCADE, to='app.Account'),
),
migrations.RunSQL(sql=create_trigger_insert_entry,
reverse_sql=delete_trigger_insert_entry),
]
}}}
As a workaround, we « fixed » this issue by overriding the method
`_fix_for_params` with the following code:
{{{
def _fix_for_params(self, query, params, unify_by_values=False):
# cx_Oracle wants no trailing ';' for SQL statements. For PL/SQL, it
# it does want a trailing ';' but not a trailing '/'. However, these
# characters must be included in the original query in case the query
# is being passed to SQL*Plus.
# ---> Fix this issue
if query.endswith(" END;"):
pass
elif query.endswith(';') or query.endswith('/'):
query = query[:-1]
if params is None:
params = []
query = query
[...]
}}}
and we used
{{{
django.db.backends.oracle.base.FormatStylePlaceholderCursor._fix_for_params
= _fix_for_params
}}}
in the migration file as it doesn't impact all the Django project. We
would be happy to raise a pull request to get this fixed and obviously if
anyone has a better way of doing this, we'd gladly oblige.
--
Ticket URL: <https://code.djangoproject.com/ticket/29170>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/055.c4892dd76296fc491d07cd464cd29c48%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
