Sumedha, sure, I understand: I also developed products that had to support multiple RDBMS. But we ended up developing RDBMS specific code for performance reasons. This of course depends on customer requirements, i.e. as long as there are no customer complains, you are doing the right things :-)
Best regards, Frank 2014-09-14 14:12 GMT+02:00 Sumedha Rubasinghe <sume...@wso2.com>: > Frank, > To make testing and development simple, we try to avoid database specific > code as much as possible with in Carbon platform. Currently most of the > products support close to 9 RDBMS engines. > > 99% of the SQLs we have in our DAO classes are ANSI compatible. However > there are database specific DDLs under product-name/dbscripts folder. But > these only contain DDLs that creates tables, indexes, triggers for identity > in a db specific way. We have not used triggers for any other purpose. > > Although stored procedures provide heavy advantages, we have avoided using > them due to the complexity of managing them for different databases. > On Sep 14, 2014 4:19 PM, "Frank Leymann" <fr...@wso2.com> wrote: > >> This is true in general: even a simple statement like SELECT behaves a >> bit differently in different RDBMS implementations. I.e. minor differences >> in SQL statements is not restricted to triggers, however we use RDBMS... >> >> >> Best regards, >> Frank >> >> 2014-09-12 10:24 GMT+02:00 Sumedha Rubasinghe <sume...@wso2.com>: >> >>> If you use triggers, you cannot have a database engine independent >>> design. >>> >>> On Fri, Sep 12, 2014 at 1:21 PM, Asitha Nanayakkara <asi...@wso2.com> >>> wrote: >>> >>>> According to an offline chat I had with PrabathA SQL triggers should be >>>> avoided since SQL triggers execute outside SQL transactions. >>>> >>>> On Thu, Jul 24, 2014 at 6:30 PM, Hasitha Hiranya <hasit...@wso2.com> >>>> wrote: >>>> >>>>> Hi, >>>>> >>>>> According to mail arch@ "Removing Global Queue from MB", maybe we >>>>> need to update this design. >>>>> Better we discuss upfront if so. >>>>> >>>>> It will bring changes to messageStore interface. >>>>> >>>>> Thanks >>>>> >>>>> >>>>> On Thu, Jul 24, 2014 at 3:04 PM, Asitha Nanayakkara <asi...@wso2.com> >>>>> wrote: >>>>> >>>>>> With the addition of message expiration feature RDBMS design for >>>>>> Metadata in MB needs to be changed. >>>>>> Updated design is as follows. >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> * Design considerations* >>>>>> >>>>>> - Only a subset of messages comes with message expiration and a >>>>>> separate thread handles deletion of expired messages. >>>>>> - Periodically expired message deletion thread queries for a >>>>>> chunk of expired messages. The subset of messages to go through would >>>>>> be >>>>>> less when a separated Expiration table is used. However when deleting >>>>>> a >>>>>> message from Expiration table should trigger a delete of all the >>>>>> messages >>>>>> with the respective message_id from Metadata table. >>>>>> >>>>>> In addition, to update reference count of messages an SQL trigger >>>>>> will be created. Is there any performance hit using triggers? >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Thu, Jul 24, 2014 at 12:06 PM, Asitha Nanayakkara <asi...@wso2.com >>>>>> > wrote: >>>>>> >>>>>>> With current design in memory message store will be used only in >>>>>>> single node mode. >>>>>>> >>>>>>> >>>>>>> On Wed, Jul 23, 2014 at 11:36 AM, Dhanuka Ranasinghe < >>>>>>> dhan...@wso2.com> wrote: >>>>>>> >>>>>>>> Also, normally publisher mention whether to persist or not messages >>>>>>>> in message itself (delivery mode). So based on that MB will process >>>>>>>> messages in memory and/or persist to a persistence store. So if it's >>>>>>>> process in memory How do we communicate through the MB cluster? >>>>>>>> >>>>>>>> >>>>>>>> http://activemq.apache.org/what-is-the-difference-between-persistent-and-non-persistent-delivery.html >>>>>>>> >>>>>>>> >>>>>>>> *Dhanuka Ranasinghe* >>>>>>>> >>>>>>>> Senior Software Engineer >>>>>>>> WSO2 Inc. ; http://wso2.com >>>>>>>> lean . enterprise . middleware >>>>>>>> >>>>>>>> phone : +94 715381915 >>>>>>>> >>>>>>>> >>>>>>>> On Mon, Jul 21, 2014 at 12:36 PM, Dhanuka Ranasinghe < >>>>>>>> dhan...@wso2.com> wrote: >>>>>>>> >>>>>>>>> IMO, If we gonna keep huge messages as chunks in memory and insert >>>>>>>>> into DB as bulk it will heavily affect on MB heap memory. My >>>>>>>>> suggestion is >>>>>>>>> we need to handle this case by case. For example, if it's small >>>>>>>>> messages it >>>>>>>>> will be efficient to keep in memory while huge messages it will be >>>>>>>>> efficient to insert into DB early as possible and let others to use >>>>>>>>> heap >>>>>>>>> memory. For this we will have to make this functionality more >>>>>>>>> configurable >>>>>>>>> but again we will have to think about how gonna support fail over >>>>>>>>> (probably >>>>>>>>> have to change db schema). >>>>>>>>> >>>>>>>>> *Dhanuka Ranasinghe* >>>>>>>>> >>>>>>>>> Senior Software Engineer >>>>>>>>> WSO2 Inc. ; http://wso2.com >>>>>>>>> lean . enterprise . middleware >>>>>>>>> >>>>>>>>> phone : +94 715381915 >>>>>>>>> >>>>>>>>> >>>>>>>>> On Mon, Jul 21, 2014 at 9:00 AM, Asitha Nanayakkara < >>>>>>>>> asi...@wso2.com> wrote: >>>>>>>>> >>>>>>>>>> We are planning to insert message chunks as batch insert queries. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Sat, Jul 19, 2014 at 11:00 AM, Dhanuka Ranasinghe < >>>>>>>>>> dhan...@wso2.com> wrote: >>>>>>>>>> >>>>>>>>>>> Are we going to insert whole message or as chunks >>>>>>>>>>> On 18 Jul 2014 18:06, "Asitha Nanayakkara" <asi...@wso2.com> >>>>>>>>>>> wrote: >>>>>>>>>>> >>>>>>>>>>>> Hi, >>>>>>>>>>>> >>>>>>>>>>>> Following is the RDBMS design for WSO2 MB 3.0.0 >>>>>>>>>>>> >>>>>>>>>>>> Messages model >>>>>>>>>>>> >>>>>>>>>>>> Message metadata model >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Following are the concerns came across in the discussion >>>>>>>>>>>> >>>>>>>>>>>> *- Why we use reference counting for message meta data?* >>>>>>>>>>>> >>>>>>>>>>>> Reference counting is needed to delete topic messages from the >>>>>>>>>>>> database reliably in a cluster deployment >>>>>>>>>>>> >>>>>>>>>>>> *- How to manage a large tables like Messages table?* >>>>>>>>>>>> >>>>>>>>>>>> for Messages table use database partitioning >>>>>>>>>>>> >>>>>>>>>>>> For Metadata queries there will be no SQL joins, hence even if >>>>>>>>>>>> the table would grow large that won't be an issue. >>>>>>>>>>>> >>>>>>>>>>>> Inserts and delete operation can be done as batch operations. >>>>>>>>>>>> >>>>>>>>>>>> *- Following option to save metadata was rejected due to >>>>>>>>>>>> following reasons* >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> In the above design MB instance will create Node_Queue tables >>>>>>>>>>>> and Topic_Node_Queue >>>>>>>>>>>> tables when each node connects to a cluster. This design was >>>>>>>>>>>> rejected due to following reasons. >>>>>>>>>>>> It's DB admins tasks to create and delete tables. MB should not >>>>>>>>>>>> modify schema when joining to the cluster. There will be instances >>>>>>>>>>>> where MB >>>>>>>>>>>> users might not have privileges to create tables. >>>>>>>>>>>> >>>>>>>>>>>> *- Supporting several SQL implementations.* >>>>>>>>>>>> >>>>>>>>>>>> Since we are using simple SQL operations those will not become >>>>>>>>>>>> an issue. >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Thanks >>>>>>>>>>>> -- >>>>>>>>>>>> *Asitha Nanayakkara* >>>>>>>>>>>> Software Engineer >>>>>>>>>>>> WSO2, Inc. http://wso2.com/ >>>>>>>>>>>> Mob: + 94 77 85 30 682 >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> _______________________________________________ >>>>>>>>>>>> Architecture mailing list >>>>>>>>>>>> Architecture@wso2.org >>>>>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>> _______________________________________________ >>>>>>>>>>> Architecture mailing list >>>>>>>>>>> Architecture@wso2.org >>>>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> *Asitha Nanayakkara* >>>>>>>>>> Software Engineer >>>>>>>>>> WSO2, Inc. http://wso2.com/ >>>>>>>>>> Mob: + 94 77 85 30 682 >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> _______________________________________________ >>>>>>>>>> Architecture mailing list >>>>>>>>>> Architecture@wso2.org >>>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> Architecture mailing list >>>>>>>> Architecture@wso2.org >>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> *Asitha Nanayakkara* >>>>>>> Software Engineer >>>>>>> WSO2, Inc. http://wso2.com/ >>>>>>> Mob: + 94 77 85 30 682 >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> *Asitha Nanayakkara* >>>>>> Software Engineer >>>>>> WSO2, Inc. http://wso2.com/ >>>>>> Mob: + 94 77 85 30 682 >>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> Architecture mailing list >>>>>> Architecture@wso2.org >>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> *Hasitha Abeykoon* >>>>> Senior Software Engineer; WSO2, Inc.; http://wso2.com >>>>> *cell:* *+94 719363063* >>>>> *blog: **abeykoon.blogspot.com* <http://abeykoon.blogspot.com> >>>>> >>>>> >>>>> _______________________________________________ >>>>> Architecture mailing list >>>>> Architecture@wso2.org >>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>> >>>>> >>>> >>>> >>>> -- >>>> *Asitha Nanayakkara* >>>> Software Engineer >>>> WSO2, Inc. http://wso2.com/ >>>> Mob: + 94 77 85 30 682 >>>> >>>> >>>> _______________________________________________ >>>> Architecture mailing list >>>> Architecture@wso2.org >>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>> >>>> >>> >>> >>> -- >>> /sumedha >>> m: +94 773017743 >>> b : bit.ly/sumedha >>> >>> _______________________________________________ >>> Architecture mailing list >>> Architecture@wso2.org >>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>> >>> >> >> _______________________________________________ >> Architecture mailing list >> Architecture@wso2.org >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > _______________________________________________ > Architecture mailing list > Architecture@wso2.org > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > >
_______________________________________________ Architecture mailing list Architecture@wso2.org https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
