cx-oracle-users

Re: [cx-oracle-users] Query Result Change Notification

[Glen W. <gl...@wa...>]

Hi,

2012/11/15 Yann Cézard <yann.cezard@un...>:

> I have recently discovered the Oracle "Database Change Notification"
> functionnality, which is really useful for one of my project, and looking at
> some docs to understand more about it, I found about QRCN (Query Result Change
> Notification) which might responds to my needs even more than DCN.
>
> Searching the web about QRCN and cx_Oracle, I found this really useful message
> which was posted one year and a half ago on this same list :
> (http://osdir.com/ml/python-db-cx-oracle/2011-04/msg00004.html)

> So now I was wondering if there are any plan to introduce the QRCN feature in
> cx_Oracle in the future, or if anybody tried (and succeed ?) to make the
> suggested code modification ? Or is such a functionnality is include under
> another name in cx_Oracle ?

As luck would have it a project I am working on would also benefit from QRCN.
After reading the message from 2011-04 a few days ago I decided to implement
this myself and contribute it to the project. I have now finished the update
and need to know the best way to submit a pull request or patch. I have just
joined the mailing list, so perhaps a list regular could point me in the right
direction - I see that cx_Oracle is on Bitbucket:
   https://bitbucket.org/anthony_tuininga/cx_oracle/
as well as Sourceforge:
   http://sourceforge.net/projects/cx-oracle/

Regards,
Glen

Re: [cx-oracle-users] Query Result Change Notification

[Anthony T. <ant...@gm...>]

On Fri, Nov 16, 2012 at 6:20 AM, Glen Walker <gl...@wa...> wrote:

>  Hi,
>
> 2012/11/15 Yann Cézard <yann.cezard@un...> <yann.cezard@un...>:
>
> > I have recently discovered the Oracle "Database Change Notification" >
> functionnality, which is really useful for one of my project, and looking
> at > some docs to understand more about it, I found about QRCN (Query
> Result Change > Notification) which might responds to my needs even more
> than DCN. > > Searching the web about QRCN and cx_Oracle, I found this
> really useful message > which was posted one year and a half ago on this
> same list : > (
> http://osdir.com/ml/python-db-cx-oracle/2011-04/msg00004.html
> ) > So now I was wondering if there are any plan to introduce the QRCN
> feature in > cx_Oracle in the future, or if anybody tried (and succeed ?)
> to make the > suggested code modification ? Or is such a functionnality is
> include under > another name in cx_Oracle ? As luck would have it a project
> I am working on would also benefit from QRCN. After reading the message
> from 2011-04 a few days ago I decided to implement this myself and
> contribute it to the project. I have now finished the update and need to
> know the best way to submit a pull request or patch. I have just joined the
> mailing list, so perhaps a list regular could point me in the right
> direction - I see that cx_Oracle is on Bitbucket:
> https://bitbucket.org/anthony_tuininga/cx_oracle/ as well as Sourceforge:
>  http://sourceforge.net/projects/cx-oracle/ Regards, Glen
>
>
The source is on bitbucket. If you submit a pull request from there that
would be ideal. If you don't have a bitbucket account and don't care to
acquire one, you can also send the changes as a diff directly to me -- so
long as the diff is against the latest source, please. Thanks!

Anthony

Re: [cx-oracle-users] Query Result Change Notification

[Yann C. <yan...@un...>]

Le 16/11/2012 14:20, Glen Walker a écrit :
> Hi,
>
> 2012/11/15 Yann Cézard<yann.cezard@un...>:
> >  I have recently discovered the Oracle "Database Change Notification"
> >  functionnality, which is really useful for one of my project, and looking at
> >  some docs to understand more about it, I found about QRCN (Query Result Change
> >  Notification) which might responds to my needs even more than DCN.
> >
> >  Searching the web about QRCN and cx_Oracle, I found this really useful message
> >  which was posted one year and a half ago on this same list :
> >  (http://osdir.com/ml/python-db-cx-oracle/2011-04/msg00004.html)
>
> >  So now I was wondering if there are any plan to introduce the QRCN feature in
> >  cx_Oracle in the future, or if anybody tried (and succeed ?) to make the
> >  suggested code modification ? Or is such a functionnality is include under
> >  another name in cx_Oracle ?
>
> As luck would have it a project I am working on would also benefit from QRCN.
> After reading the message from 2011-04 a few days ago I decided to implement
> this myself and contribute it to the project. I have now finished the update
> and need to know the best way to submit a pull request or patch. I have just
> joined the mailing list, so perhaps a list regular could point me in the right
> direction - I see that cx_Oracle is on Bitbucket:
>    https://bitbucket.org/anthony_tuininga/cx_oracle/
> as well as Sourceforge:
>    http://sourceforge.net/projects/cx-oracle/
>
> Regards,
> Glen
>
Great, that's very good news !

I will test as soon as it is available. Thanks a lot !

Best regards,

Yann

Re: [cx-oracle-users] Query Result Change Notification

[Glen W. <gl...@wa...>]

On 17/11/12 9:17 AM, Yann Cézard wrote:
> Le 16/11/2012 14:20, Glen Walker a écrit :
>> Hi,
>>
>> 2012/11/15 Yann Cézard<yann.cezard@un...>:
>> > I have recently discovered the Oracle "Database Change Notification"
>> > functionnality, which is really useful for one of my project, and looking at
>> > some docs to understand more about it, I found about QRCN (Query Result Change
>> > Notification) which might responds to my needs even more than DCN.
>> >
>> > Searching the web about QRCN and cx_Oracle, I found this really useful message
>> > which was posted one year and a half ago on this same list :
>> > (http://osdir.com/ml/python-db-cx-oracle/2011-04/msg00004.html)
>>
>> > So now I was wondering if there are any plan to introduce the QRCN feature in
>> > cx_Oracle in the future, or if anybody tried (and succeed ?) to make the
>> > suggested code modification ? Or is such a functionnality is include under
>> > another name in cx_Oracle ?
>>
>> As luck would have it a project I am working on would also benefit from QRCN.
>> After reading the message from 2011-04 a few days ago I decided to implement
>> this myself and contribute it to the project. I have now finished the update
>> and need to know the best way to submit a pull request or patch. I have just
>> joined the mailing list, so perhaps a list regular could point me in the right
>> direction - I see that cx_Oracle is on Bitbucket:
>>    https://bitbucket.org/anthony_tuininga/cx_oracle/
>> as well as Sourceforge:
>>    http://sourceforge.net/projects/cx-oracle/
>>
>> Regards,
>> Glen
>>
> Great, that's very good news !
>
> I will test as soon as it is available. Thanks a lot !
>
> Best regards,
>
> Yann

Hi,

I've submitted a pull request with my changes to support query result 
set change notification. You can find it on Bitbucket 
<https://bitbucket.org/anthony_tuininga/cx_oracle/pull-request/2/> if 
you don't want to wait. Sample code below.

Kind regards,
Glen



import time
import cx_Oracle


def EventToString(event):
     if event == cx_Oracle.EVENT_NONE:
         return 'None'
     elif event == cx_Oracle.EVENT_STARTUP:
         return 'Startup'
     elif event == cx_Oracle.EVENT_SHUTDOWN:
         return 'Shutdown'
     elif event == cx_Oracle.EVENT_SHUTDOWN_ANY:
         return 'ShutdownAny'
     elif event == cx_Oracle.EVENT_DEREG:
         return 'Dereg'
     elif event == cx_Oracle.EVENT_OBJCHANGE:
         return 'ObjChange'
     elif event == cx_Oracle.EVENT_QUERYCHANGE:
         return 'QueryChange'
     return 'Unknown'


def OperationToString(operation):
     operations = []
     if operation & cx_Oracle.OPCODE_INSERT:
         operations.append("insert")
     if operation & cx_Oracle.OPCODE_UPDATE:
         operations.append("update")
     if operation & cx_Oracle.OPCODE_DELETE:
         operations.append("delete")
     if operation & cx_Oracle.OPCODE_ALTER:
         operations.append("alter")
     if operation & cx_Oracle.OPCODE_DROP:
         operations.append("drop")
     if operation & cx_Oracle.OPCODE_ALLOPS:
         operations.append("all operations")
     if operation & cx_Oracle.OPCODE_ALLROWS:
         operations.append("all rows")
     return ", ".join(operations)


def onChange(message):
print "=" * 60
     print 'onChange subscription id %d' % (message.subscription.id, )

     print "Message type:", message.type, EventToString(message.type)
     print "Message database name:", message.dbname
     if message.tables is not None:
         print "Message tables:"
         for table in message.tables:
             print "--> Table Name:", table.name
             print "--> Table Operation:", table.operation, 
OperationToString(table.operation)
             if table.rows is not None:
                 print "--> Table Rows:"
                 for row in table.rows:
                     print "--> --> Row RowId:", row.rowid
                     print "--> --> Row Operation:", row.operation, 
OperationToString(row.operation)
                     print "-" * 60
             print "=" * 60
     if message.queries is not None:
         print "Message queries:"
         for query in message.queries:
             print "--> Query ID:", query.id
             print "--> Query Operation:", query.operation, 
EventToString(query.operation)
             if query.tables is not None:
                 print "--> Query tables:"
                 for table in query.tables:
                     print "--> --> Table Name:", table.name
                     print "--> --> Table Operation:", table.operation, 
OperationToString(table.operation)
                     if table.rows is not None:
                         print "--> --> Table Rows:"
                         for row in table.rows:
                             print "--> --> --> Row RowId:", row.rowid
                             print "--> --> --> Row Operation:", 
row.operation, OperationToString(row.operation)
                             print "-" * 60
             print "=" * 60


print 'Connecting'

dsn = cx_Oracle.makedsn(...)
connection = cx_Oracle.connect(user=username, password=password, 
dsn=dsn, threaded=True, events=True)


subscription = connection.subscribe(callback=onChange, 
operations=cx_Oracle.OPCODE_ALLOPS,
         
qos=cx_Oracle.SUBSCR_QOS_PURGE_ON_NTFN,cqqos=cx_Oracle.SUBSCR_CQ_QOS_QUERY,
         rowids=True)

print "Subscription:", subscription
print "--> Connection:", subscription.connection
print "--> Callback:", subscription.callback
print "--> Namespace:", subscription.namespace
print "--> Protocol:", subscription.protocol
print "--> Port:", subscription.port
print "--> Timeout:", subscription.timeout
print "--> Operations:", subscription.operations
print "--> QOS:", subscription.qos
print "--> CQ QOS:", subscription.cqqos
print "--> Rowids:", subscription.rowids
print "--> Id:", subscription.id

queryId1 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE 
TEST_INT <= 3')
print "Query ID 1:", queryId1

queryId2 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE 
TEST_INT > 3')
print "Query ID 2:", queryId2

print 'Waiting'
try:
     time.sleep(6000)
except KeyboardInterrupt:
     pass


print 'Unsubscribing'
subscription = None

print 'Disconnecting'
connection.close()

Re: [cx-oracle-users] Query Result Change Notification

[Yann C. <yan...@un...>]

Le 23/11/2012 03:43, Glen Walker a écrit :
>
> On 17/11/12 9:17 AM, Yann Cézard wrote:
>> Le 16/11/2012 14:20, Glen Walker a écrit :
>>> Hi,
>>>
>>> 2012/11/15 Yann Cézard <yann.cezard@un...>:
>>> > I have recently discovered the Oracle "Database Change Notification"
>>> > functionnality, which is really useful for one of my project, and looking at
>>> > some docs to understand more about it, I found about QRCN (Query Result Change
>>> > Notification) which might responds to my needs even more than DCN.
>>> > 
>>> > Searching the web about QRCN and cx_Oracle, I found this really useful message
>>> > which was posted one year and a half ago on this same list :
>>> > (http://osdir.com/ml/python-db-cx-oracle/2011-04/msg00004.html)
>>>
>>> > So now I was wondering if there are any plan to introduce the QRCN feature in
>>> > cx_Oracle in the future, or if anybody tried (and succeed ?) to make the
>>> > suggested code modification ? Or is such a functionnality is include under
>>> > another name in cx_Oracle ?
>>>
>>> As luck would have it a project I am working on would also benefit from QRCN.
>>> After reading the message from 2011-04 a few days ago I decided to implement
>>> this myself and contribute it to the project. I have now finished the update
>>> and need to know the best way to submit a pull request or patch. I have just
>>> joined the mailing list, so perhaps a list regular could point me in the right
>>> direction - I see that cx_Oracle is on Bitbucket:
>>>   https://bitbucket.org/anthony_tuininga/cx_oracle/
>>> as well as Sourceforge:
>>>   http://sourceforge.net/projects/cx-oracle/
>>>
>>> Regards,
>>> Glen
>>>
>> Great, that's very good news !
>>
>> I will test as soon as it is available. Thanks a lot !
>>
>> Best regards,
>>
>> Yann
>
> Hi,
>
> I've submitted a pull request with my changes to support query result
> set change notification. You can find it on Bitbucket
> <https://bitbucket.org/anthony_tuininga/cx_oracle/pull-request/2/> if
> you don't want to wait. Sample code below.
>
> Kind regards,
> Glen
>
Thanks a lot, Glen.
I'll do some tests as soon as possible (this afternoon if I can) and
I will give my feedback.

Regards,
>
>
> import time
> import cx_Oracle
>
>
> def EventToString(event):
>     if event == cx_Oracle.EVENT_NONE:
>         return 'None'
>     elif event == cx_Oracle.EVENT_STARTUP:
>         return 'Startup'
>     elif event == cx_Oracle.EVENT_SHUTDOWN:
>         return 'Shutdown'
>     elif event == cx_Oracle.EVENT_SHUTDOWN_ANY:
>         return 'ShutdownAny'
>     elif event == cx_Oracle.EVENT_DEREG:
>         return 'Dereg'
>     elif event == cx_Oracle.EVENT_OBJCHANGE:
>         return 'ObjChange'
>     elif event == cx_Oracle.EVENT_QUERYCHANGE:
>         return 'QueryChange'
>     return 'Unknown'
>
>
> def OperationToString(operation):
>     operations = []
>     if operation & cx_Oracle.OPCODE_INSERT:
>         operations.append("insert")
>     if operation & cx_Oracle.OPCODE_UPDATE:
>         operations.append("update")
>     if operation & cx_Oracle.OPCODE_DELETE:
>         operations.append("delete")
>     if operation & cx_Oracle.OPCODE_ALTER:
>         operations.append("alter")
>     if operation & cx_Oracle.OPCODE_DROP:
>         operations.append("drop")
>     if operation & cx_Oracle.OPCODE_ALLOPS:
>         operations.append("all operations")
>     if operation & cx_Oracle.OPCODE_ALLROWS:
>         operations.append("all rows")
>     return ", ".join(operations)
>
>
> def onChange(message):
>    print "=" * 60
>     print 'onChange subscription id %d' % (message.subscription.id, )
>
>     print "Message type:", message.type, EventToString(message.type)
>     print "Message database name:", message.dbname
>     if message.tables is not None:
>         print "Message tables:"
>         for table in message.tables:
>             print "--> Table Name:", table.name
>             print "--> Table Operation:", table.operation,
> OperationToString(table.operation)
>             if table.rows is not None:
>                 print "--> Table Rows:"
>                 for row in table.rows:
>                     print "--> --> Row RowId:", row.rowid
>                     print "--> --> Row Operation:", row.operation,
> OperationToString(row.operation)
>                     print "-" * 60
>             print "=" * 60
>     if message.queries is not None:
>         print "Message queries:"
>         for query in message.queries:
>             print "--> Query ID:", query.id
>             print "--> Query Operation:", query.operation,
> EventToString(query.operation)
>             if query.tables is not None:
>                 print "--> Query tables:"
>                 for table in query.tables:
>                     print "--> --> Table Name:", table.name
>                     print "--> --> Table Operation:", table.operation,
> OperationToString(table.operation)
>                     if table.rows is not None:
>                         print "--> --> Table Rows:"
>                         for row in table.rows:
>                             print "--> --> --> Row RowId:", row.rowid
>                             print "--> --> --> Row Operation:",
> row.operation, OperationToString(row.operation)
>                             print "-" * 60
>             print "=" * 60
>
>
> print 'Connecting'
>
> dsn = cx_Oracle.makedsn(...)
> connection = cx_Oracle.connect(user=username, password=password,
> dsn=dsn, threaded=True, events=True)
>
>
> subscription = connection.subscribe(callback=onChange,
> operations=cx_Oracle.OPCODE_ALLOPS,
>        
> qos=cx_Oracle.SUBSCR_QOS_PURGE_ON_NTFN,cqqos=cx_Oracle.SUBSCR_CQ_QOS_QUERY,
>         rowids=True)
>
> print "Subscription:", subscription
> print "--> Connection:", subscription.connection
> print "--> Callback:", subscription.callback
> print "--> Namespace:", subscription.namespace
> print "--> Protocol:", subscription.protocol
> print "--> Port:", subscription.port
> print "--> Timeout:", subscription.timeout
> print "--> Operations:", subscription.operations
> print "--> QOS:", subscription.qos
> print "--> CQ QOS:", subscription.cqqos
> print "--> Rowids:", subscription.rowids
> print "--> Id:", subscription.id
>
> queryId1 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE
> TEST_INT <= 3')
> print "Query ID 1:", queryId1
>
> queryId2 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE
> TEST_INT > 3')
> print "Query ID 2:", queryId2
>
> print 'Waiting'
> try:
>     time.sleep(6000)
> except KeyboardInterrupt:
>     pass
>
>
> print 'Unsubscribing'
> subscription = None
>
> print 'Disconnecting'
> connection.close()


-- 
Yann Cézard - administrateur systèmes serveurs
Centre de ressources informatiques    -     http://cri.univ-pau.fr
Université de Pau et des pays de l'Adour -  http://www.univ-pau.fr
bâtiment d'Alembert (anciennement IFR), rue Jules Ferry, 64000 Pau
Téléphone : +33 (0)5 59 40 77 94

Re: [cx-oracle-users] Query Result Change Notification

[Yann C. <yan...@un...>]

Le 23/11/2012 09:09, Yann Cézard a écrit :
> Le 23/11/2012 03:43, Glen Walker a écrit :
>>
>> [...]
>> Hi,
>>
>> I've submitted a pull request with my changes to support query result
>> set change notification. You can find it on Bitbucket
>> <https://bitbucket.org/anthony_tuininga/cx_oracle/pull-request/2/> if
>> you don't want to wait. Sample code below.
>>
>> Kind regards,
>> Glen
>>
> Thanks a lot, Glen.
> I'll do some tests as soon as possible (this afternoon if I can) and
> I will give my feedback.
>
> Regards,
Ok, so I did try a similar example as yours, I just updated the query to
my use case,
and it works ! but only one time... :-)
I mean, the first change in the database that modifies the query result
set is well
sent to my client. But than, any other change that affects the query
result set isn't
notified anymore. If I close the client, and restart it, the first
change is notified and
then nothing.

It's as if the server deregistered my subscription after the first
notification.

If I look at the USER_CHANGE_NOTIFICATION_REGS table, after the client
is launched,
the query is there. After the first notification, it isn't anymore.

And as a matter of fact, in the USER_CHANGE_NOTIFICATION_REGS table,
REGFLAGS is
6 (if rowids is enabled) or 2 (if rowids is disabled) in QRCN mode, and
it's 0 (rowids disabled)
or 4 (rowids enabled) in OCN mode (which doesn't have this behaviour).
So it looks like :
QOS_DEREG_NFY    BINARY_INTEGER    2    Purge registration on first
notification
is enabled by default ?
(http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_cqn.htm#autoId21)

Is it a wanted behaviour ? Am I the only one to have this behaviour by
default ?
And is there a way to disable it ? (I didn't find any mention of
QOS_DEREG_NFY
in the code... I'll try to look further !).

Thanks for your work anyway, it is so close to what I was expecting !

Regards,

-- 
Yann Cézard - administrateur systèmes serveurs
Centre de ressources informatiques    -     http://cri.univ-pau.fr
Université de Pau et des pays de l'Adour -  http://www.univ-pau.fr

Re: [cx-oracle-users] Query Result Change Notification

[Glen W. <gl...@wa...>]

On 24/11/12 3:56 AM, Yann Cézard wrote:
> Le 23/11/2012 09:09, Yann Cézard a écrit :
>> Le 23/11/2012 03:43, Glen Walker a écrit :
>>>
>>> [...]
>>> Hi,
>>>
>>> I've submitted a pull request with my changes to support query 
>>> result set change notification. You can find it on Bitbucket 
>>> <https://bitbucket.org/anthony_tuininga/cx_oracle/pull-request/2/> 
>>> if you don't want to wait. Sample code below.
>>>
>>> Kind regards,
>>> Glen
>>>
>> Thanks a lot, Glen.
>> I'll do some tests as soon as possible (this afternoon if I can) and
>> I will give my feedback.
>>
>> Regards,
> Ok, so I did try a similar example as yours, I just updated the query 
> to my use case,
> and it works ! but only one time... :-)
> I mean, the first change in the database that modifies the query 
> result set is well
> sent to my client. But than, any other change that affects the query 
> result set isn't
> notified anymore. If I close the client, and restart it, the first 
> change is notified and
> then nothing.
>
> It's as if the server deregistered my subscription after the first 
> notification.
>
> If I look at the USER_CHANGE_NOTIFICATION_REGS table, after the client 
> is launched,
> the query is there. After the first notification, it isn't anymore.
>
> And as a matter of fact, in the USER_CHANGE_NOTIFICATION_REGS table, 
> REGFLAGS is
> 6 (if rowids is enabled) or 2 (if rowids is disabled) in QRCN mode, 
> and it's 0 (rowids disabled)
> or 4 (rowids enabled) in OCN mode (which doesn't have this behaviour).
> So it looks like :
> QOS_DEREG_NFY    BINARY_INTEGER    2    Purge registration on first 
> notification
> is enabled by default ?
> (http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_cqn.htm#autoId21)
>
> Is it a wanted behaviour ? Am I the only one to have this behaviour by 
> default ?
> And is there a way to disable it ? (I didn't find any mention of 
> QOS_DEREG_NFY
> in the code... I'll try to look further !).
>
> Thanks for your work anyway, it is so close to what I was expecting !
>
> Regards,
> -- 
> Yann Cézard - administrateur systèmes serveurs
> Centre de ressources informatiques    -http://cri.univ-pau.fr
> Université de Pau et des pays de l'Adour -http://www.univ-pau.fr
Hi Yann,

In the example code I used qos=cx_Oracle.SUBSCR_QOS_PURGE_ON_NTFN in the 
call to connection.subscribe. This was intended to demonstrate use of 
the option, but in your case you don't want this behaviour. If you 
remove the qos argument from the call to connection.subscribe you should 
continue to receive notifications until you unsubscribe.

Regards,
Glen

Re: [cx-oracle-users] Query Result Change Notification

[Yann C. <yan...@un...>]

Le 24/11/2012 04:53, Glen Walker a écrit :
> Hi Yann,
>
> In the example code I used qos=cx_Oracle.SUBSCR_QOS_PURGE_ON_NTFN in 
> the call to connection.subscribe. This was intended to demonstrate use 
> of the option, but in your case you don't want this behaviour. If you 
> remove the qos argument from the call to connection.subscribe you 
> should continue to receive notifications until you unsubscribe.
>
> Regards,
> Glen
>
I spent so much time trying to understand the behaviour focusing
on the low-level layer, that I didn't even look at the options I was 
using...
I'm feeling so stupid now...

Thanks a lot !

-- 
Yann Cézard  -  infrastructures - administrateur systèmes serveurs
Centre de ressources informatiques    -     http://cri.univ-pau.fr
Université de Pau et des pays de l'Adour -  http://www.univ-pau.fr