Hi Shakila, Please consider databases like Oracle [1] and how they implement concurrency and consistency when using timestamps to identify changed rows. One might miss updated rows with queries that search for timestamps like this pseudo code: *select* *all records with timestamp>=max(timestamps of last query) *as there could be records with timestamp < max(timestamps of last query) that are not in the record set of "last query" and will not be in the record set of "now query" as they got committed between "last query" and "now".
Thanks, Jochen [1] https://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm Shakila Sasikaran <[email protected]> schrieb am Di. 15. Aug. 2017 um 09:08: > Hi Malaka, > > We don't hard code the column names. We have a parameter to define the > column name. > > Thanks > > On Tue, Aug 15, 2017 at 11:59 AM, Malaka Silva <[email protected]> wrote: > >> Hi Shakila, >> >> For use case 1 and 2 better to take the column name from a parameter >> rather than hard coding the column names. >> >> On Tue, Aug 15, 2017 at 11:51 AM, Shakila Sasikaran <[email protected]> >> wrote: >> >>> Hi all, >>> >>> Please find the use-cases that we are going to support with the DB event >>> listener. Appreciate your comments on this. >>> >>> 1. Take record based on timestamp. Have a field in records called >>> 'lastmodifieddatetime' and based on that take records in each polling >>> cycle: This is supported by the current implementation. >>> 2. Have a boolean column 'isChanged' in the record and by default, it >>> will be 'true'. Each polling cycle takes only records with 'true' value and >>> updates as 'false' once it is polled. >>> 3. Give an option to delete the record after polling it. We can have a >>> boolean type parameter 'deleteAfterRead' and if it is set to true, it will >>> delete the record after polling it. >>> >>> Thanks >>> >>> On Sat, Aug 12, 2017 at 6:36 AM, Malaka Silva <[email protected]> wrote: >>> >>>> Hi All, >>>> >>>> We have identified users still need a solution from integration layer. >>>> We are planning to continue a generic solution for this. >>>> >>>> On Tue, Sep 6, 2016 at 11:12 AM, Anjana Fernando <[email protected]> >>>> wrote: >>>> >>>>> Hi, >>>>> >>>>> If we say, we monitor one table, that means, we can simply say, give >>>>> the table name to be monitored in the inbound endpoint. And probably we >>>>> have to give the logic as to what warrants a change in the database, is it >>>>> a row value change, a new row being added etc.. and doing complex >>>>> operations can be very tricky as the database grows, and not to mention >>>>> expressing on what exactly to do to figure out a change in the database. >>>>> So >>>>> I'm just thinking if we can do this in an efficient way at all, and how >>>>> useful it would be at the end. For example, even running a SELECT COUNT >>>>> (*) >>>>> can be an expensive operation in a large table. So, by any chance, if the >>>>> database server doesn't give any native trigger functionality, I can see >>>>> an >>>>> user just writing his custom extension to poll a database and run his >>>>> custom logic to figure out changes. >>>>> >>>>> Cheers, >>>>> Anjana. >>>>> >>>>> On Wed, Aug 31, 2016 at 12:00 PM, Srinath Perera <[email protected]> >>>>> wrote: >>>>> >>>>>> +1 .. we can say that we only monitor one table and recommend to >>>>>> setup triggers if they need to detect lot of conditions. >>>>>> >>>>>> On Wed, Aug 31, 2016 at 3:12 AM, Chamila De Alwis <[email protected]> >>>>>> wrote: >>>>>> >>>>>>> One hybrid solution would be to have db triggers adding records to a >>>>>>> single "monitor" table in which a polling inbound endpoint can >>>>>>> periodically >>>>>>> look check for changes [1]. Based on the new records, the consequent >>>>>>> sequence can decide which actions to execute. >>>>>>> >>>>>>> [1] - >>>>>>> http://stackoverflow.com/questions/6153330/can-a-sql-trigger-call-a-web-service >>>>>>> >>>>>>> >>>>>>> Regards, >>>>>>> Chamila de Alwis >>>>>>> Committer and PMC Member - Apache Stratos >>>>>>> Senior Software Engineer | WSO2 >>>>>>> Blog: https://medium.com/@chamilad >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Tue, Aug 30, 2016 at 4:52 AM, Srinath Perera <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>>> Hi Malaka, >>>>>>>> >>>>>>>> If it is done using triggers, it can be done without us doing >>>>>>>> anything. I assume trigger can hit a URL in the ESB that trigger >>>>>>>> processing. >>>>>>>> >>>>>>>> Adding a DB listener as an inbound endpoint is OK. >>>>>>>> >>>>>>>> I suggest we only do DB listener. >>>>>>>> >>>>>>>> --Srinath >>>>>>>> >>>>>>>> On Tue, Aug 30, 2016 at 3:13 PM, Malaka Silva <[email protected]> >>>>>>>> wrote: >>>>>>>> >>>>>>>>> Hi, >>>>>>>>> >>>>>>>>> There are requirements to do additional operations when there >>>>>>>>> are changes done to organization data. >>>>>>>>> >>>>>>>>> One way to do this is to create triggers at database level. >>>>>>>>> However there are limitations on actions users can perform using >>>>>>>>> triggers. >>>>>>>>> >>>>>>>>> So if we implement custom inbound endpoint we can cover most of >>>>>>>>> the use cases. >>>>>>>>> [image: Inline image 1] >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> There are several ways to do that. But we already know using JDBC >>>>>>>>> is impossible at the moment. One way to achieve this is implementing a >>>>>>>>> polling inbound to monitor the changes in the database object (such >>>>>>>>> as a >>>>>>>>> table in the database). If any change occurred, that inbound can >>>>>>>>> invoke a >>>>>>>>> sequence. But this is not a good practice. What if your database has >>>>>>>>> more >>>>>>>>> than ten tables? Then users have to create ten threads for each table >>>>>>>>> and >>>>>>>>> that would be a great mess regarding to the performance. >>>>>>>>> >>>>>>>>> There are also vendor specific solutions provided. [1] [2] >>>>>>>>> >>>>>>>>> JPA also provide this capability [3] However with this users need >>>>>>>>> to create entities for there environment and using those with ESB is >>>>>>>>> complex. >>>>>>>>> >>>>>>>>> Using Hibernate we can do the same and maintain the configuration >>>>>>>>> in XML. >>>>>>>>> >>>>>>>>> Thoughts about this inbound are welcome? >>>>>>>>> >>>>>>>>> [1] >>>>>>>>> http://stackoverflow.com/questions/12618915/how-to-implement-a-db-listener-in-java >>>>>>>>> [2] >>>>>>>>> http://www.ibm.com/support/knowledgecenter/SSSHYH_5.1.1/com.ibm.netcoolimpact.doc5.1.1/solution/imsg_db_listeners_database_listener_overview_c.html >>>>>>>>> [3] >>>>>>>>> https://docs.jboss.org/hibernate/entitymanager/3.6/reference/en/html/listeners.html >>>>>>>>> >>>>>>>>> [4] >>>>>>>>> https://dunithd.wordpress.com/2009/10/27/create-database-triggers-like-features-using-hibernate-events/ >>>>>>>>> >>>>>>>>> >>>>>>>>> Best Regards, >>>>>>>>> >>>>>>>>> Malaka Silva >>>>>>>>> Senior Technical Lead >>>>>>>>> M: +94 777 219 791 >>>>>>>>> Tel : 94 11 214 5345 >>>>>>>>> Fax :94 11 2145300 >>>>>>>>> Skype : malaka.sampath.silva >>>>>>>>> LinkedIn : http://www.linkedin.com/pub/malaka-silva/6/33/77 >>>>>>>>> Blog : http://mrmalakasilva.blogspot.com/ >>>>>>>>> >>>>>>>>> WSO2, Inc. >>>>>>>>> lean . enterprise . middleware >>>>>>>>> https://wso2.com/signature >>>>>>>>> http://www.wso2.com/about/team/malaka-silva/ >>>>>>>>> <http://wso2.com/about/team/malaka-silva/> >>>>>>>>> https://store.wso2.com/store/ >>>>>>>>> >>>>>>>>> Don't make Trees rare, we should keep them with care >>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> Architecture mailing list >>>>>>>>> [email protected] >>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> ============================ >>>>>>>> Srinath Perera, Ph.D. >>>>>>>> http://people.apache.org/~hemapani/ >>>>>>>> http://srinathsview.blogspot.com/ >>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> Architecture mailing list >>>>>>>> [email protected] >>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> Architecture mailing list >>>>>>> [email protected] >>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> ============================ >>>>>> Srinath Perera, Ph.D. >>>>>> http://people.apache.org/~hemapani/ >>>>>> http://srinathsview.blogspot.com/ >>>>>> >>>>>> _______________________________________________ >>>>>> Architecture mailing list >>>>>> [email protected] >>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> *Anjana Fernando* >>>>> Associate Director / Architect >>>>> WSO2 Inc. | http://wso2.com >>>>> lean . enterprise . middleware >>>>> >>>>> _______________________________________________ >>>>> Architecture mailing list >>>>> [email protected] >>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>> >>>>> >>>> >>>> >>>> -- >>>> >>>> Best Regards, >>>> >>>> Malaka Silva >>>> Associate Director / Architect >>>> M: +94 777 219 791 <+94%2077%20721%209791> >>>> Tel : 94 11 214 5345 >>>> Fax :94 11 2145300 <011%202%20145300> >>>> Skype : malaka.sampath.silva >>>> LinkedIn : http://www.linkedin.com/pub/malaka-silva/6/33/77 >>>> Blog : http://mrmalakasilva.blogspot.com/ >>>> >>>> WSO2, Inc. >>>> lean . enterprise . middleware >>>> https://wso2.com/signature >>>> http://www.wso2.com/about/team/malaka-silva/ >>>> <http://wso2.com/about/team/malaka-silva/> >>>> https://store.wso2.com/store/ >>>> >>>> Don't make Trees rare, we should keep them with care >>>> >>>> _______________________________________________ >>>> Architecture mailing list >>>> [email protected] >>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>> >>>> >>> >>> >>> -- >>> Shakila Sasikaran >>> Software Engineer >>> Mobile :+94 (0) 77 526 6848 <+94%2077%20526%206848> >>> [email protected] >>> WSO2, Inc. >>> lean . enterprise . middleware >>> http://www.wso2.com/ >>> >> >> >> >> -- >> >> Best Regards, >> >> Malaka Silva >> Associate Director / Architect >> M: +94 777 219 791 <+94%2077%20721%209791> >> Tel : 94 11 214 5345 >> Fax :94 11 2145300 >> Skype : malaka.sampath.silva >> LinkedIn : http://www.linkedin.com/pub/malaka-silva/6/33/77 >> Blog : http://mrmalakasilva.blogspot.com/ >> >> WSO2, Inc. >> lean . enterprise . middleware >> https://wso2.com/signature >> http://www.wso2.com/about/team/malaka-silva/ >> <http://wso2.com/about/team/malaka-silva/> >> https://store.wso2.com/store/ >> >> Don't make Trees rare, we should keep them with care >> > > > > -- > Shakila Sasikaran > Software Engineer > Mobile :+94 (0) 77 526 6848 > [email protected] > WSO2, Inc. > lean . enterprise . middleware > http://www.wso2.com/ > _______________________________________________ > Architecture mailing list > [email protected] > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > -- Gruss / regards Jochen Traunecker mailto: [email protected]
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
