Re: [Django] #29170: Oracle - Unable to add triggers in migrations, semicolon removed.

2020-03-30 Thread Django 
#29170: Oracle - Unable to add triggers in migrations, semicolon removed.
-+-
 Reporter:  Danny Willems|Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  3.0
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  oracle trigger   | Triage Stage:
  database   |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Václav Řehák):

 * status:  closed => new
 * version:  1.11 => 3.0
 * resolution:  worksforme =>


Comment:

 Hi,

 I encountered exactly the same issue. To reproduce see migration
 0002_add_trigger in https://github.com/washeck/django-tutorial

 manage.py migrate fails with

 {{{

   Applying polls.0002_add_trigger...Traceback (most recent call last):
   File "/home/vaclav/.local/share/virtualenvs/django-tutorial-
 eSoqXmQ3/lib/python3.6/site-packages/django/db/backends/utils.py", line
 84, in _execute
 return self.cursor.execute(sql)
   File "/home/vaclav/.local/share/virtualenvs/django-tutorial-
 eSoqXmQ3/lib/python3.6/site-packages/django/db/backends/oracle/base.py",
 line 517, in execute
 return self.cursor.execute(query, self._param_generator(params))
 cx_Oracle.DatabaseError: ORA-24373: invalid length specified for statement

 }}}

 When I remove the slash, the migration applies but the trigger is
 incorrect and


 {{{
 select * from user_errors where type = 'TRIGGER' and name =
 'TRIG_PREVENT_QUESTION_DELETE';
 }}}

 shows

 {{{

 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
 following:

;  
 The symbol ";" was substituted for "end-of-file" to continue.
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.45e482191e10f765f6e9c156a73119f4%40djangoproject.com.

Re: [Django] #29170: Oracle - Unable to add triggers in migrations, semicolon removed.

2018-03-06 Thread Django 
#29170: Oracle - Unable to add triggers in migrations, semicolon removed.
-+-
 Reporter:  Danny Willems|Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  1.11
  (models, ORM)  |   Resolution:
 Severity:  Normal   |  worksforme
 Keywords:  oracle trigger   | Triage Stage:
  database   |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * status:  new => closed
 * resolution:   => worksforme


Comment:

 I cannot reproduce this issue. Please reopen this ticket if you can
 provide a falling test.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.eb5e9e2f34d6dccf1b2dbd6c7e839ccf%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29170: Oracle - Unable to add triggers in migrations, semicolon removed.

2018-03-01 Thread Django 
#29170: Oracle - Unable to add triggers in migrations, semicolon removed.
-+-
 Reporter:  Danny Willems|Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.11
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  oracle trigger   | Triage Stage:
  database   |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by felixxm):

 樂 `ORA-24373` cannot be related with the reported issue. If you pass a
 trigger statement with trailing `/` , than `_fix_for_params` returns the
 same statement as in the ticket description
 {{{
 >>> from django.db import connection
 >>> from django.db.backends.oracle.base import
 FormatStylePlaceholderCursor
 >>> 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;
 /"""
 >>> query, _ =
 
FormatStylePlaceholderCursor(connection)._fix_for_params(create_trigger_insert_entry,
 [])
 >>> print(query)

 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;
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.6912123489b725c1a2a6571f5bff6c8c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29170: Oracle - Unable to add triggers in migrations, semicolon removed.

2018-03-01 Thread Django 
#29170: Oracle - Unable to add triggers in migrations, semicolon removed.
-+-
 Reporter:  Danny Willems|Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.11
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  oracle trigger   | Triage Stage:
  database   |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Danny Willems):

 Replying to [comment:1 felixxm]:
 > IMO you missed trailing "/" in the trigger statement:
 >
 > {{{
 > 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;
 > /"""
 > }}}
 >
 > Can you confirm that it doesn't work with trailing slash?

 I confirm it doesn't work. We have this exception:

 {{{
 django.db.utils.DatabaseError: ORA-24373: invalid length specified for
 statement
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.d5e95af8db5543d1fab572519fb78fda%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29170: Oracle - Unable to add triggers in migrations, semicolon removed.

2018-03-01 Thread Django 
#29170: Oracle - Unable to add triggers in migrations, semicolon removed.
-+-
 Reporter:  Danny Willems|Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.11
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  oracle trigger   | Triage Stage:
  database   |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * cc: felixxm (added)


Comment:

 IMO you missed trailing "/" in the trigger statement:

 {{{
 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;
 /"""
 }}}

 Can you confirm that it doesn't work with trailing slash?

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.91ae707c56ec5f9602fbd02cb8ca91ba%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #29170: Oracle - Unable to add triggers in migrations, semicolon removed.

2018-02-28 Thread Django 
#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: