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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

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