Hi, Thanks Anjana and Akalanka for the suggestions. @Anjana As per the off-line discussion we had, I will add the necessary for the implementation.
Regards, Damith. On Mon, Dec 1, 2014 at 5:49 PM, Akalanka Pagoda Arachchi <[email protected] > wrote: > Hi Damitha, > > As Anjana suggested, separating only the data types would lead to some > confusions. Furthermore might, sometimes lead to not using a specific > database system to it's fullest potential. For example, given that there > are a huge amount of built-in functions in MSSQL we might be able to use a > simpler, efficient query to complete our task in MSSQL that in other > database systems (just an example). Also apart from the functions you have > mentioned, in the future the system might need some more functions and we > have to make sure that extensibility is supported. Therefore, having a > separate XML for queries in each database system is more suitable in my > opinion too. > > Thanks, > Akalanka > > On Mon, Dec 1, 2014 at 5:26 PM, Anjana Fernando <[email protected]> wrote: > >> Hi Damith, >> >> I'm personally biased towards just using the whole queries in the XML, >> where we may get some subtle changes in queries that may not be possible >> with your parameterized approach. For example, in MySQL, you can give the >> storage table engine as a part in the DDL query. Likewise, there maybe >> special sections that is dependent on the RDBMS. So we may not want to >> over-engineer this that much, a simple mapping of all the queries to a DBMS >> is much simpler, and has more functionality. The redundancy of possible >> duplication of standard queries across DBMS mappings can be ignored, >> looking at the other potential advantages. Also, these table creation >> queries, you may want to have a set of queries that will initialize the >> tables, because you would want required indexes to be created too, and >> index creation is anyway dependent on the target RDBMS. >> >> Cheers, >> Anjana. >> >> On Mon, Dec 1, 2014 at 5:17 PM, Damith Wickramasinghe <[email protected]> >> wrote: >> >>> Hi, >>> >>> please note above varchar length will be given as default. eg:- >>> varchar(250). >>> >>> Regards, >>> damith. >>> >>> On Mon, Dec 1, 2014 at 5:10 PM, Damith Wickramasinghe <[email protected]> >>> wrote: >>> >>>> Hi, >>>> >>>> Thanks Anjana and Lahiru for the suggestions. Please find below the >>>> found observations. >>>> >>>> >>>> Following are the DB actions. >>>> >>>> - DB creation (IF NOT EXIST) >>>> - Table creation (IF NOT EXIST) >>>> - Insert data to table >>>> - update data to table >>>> >>>> Since all the DBMS's are using same DDL and DML query formats we can >>>> keep the query formats static for all DB types. Also we are only >>>> considering above db actions only >>>> >>>> eg:- >>>> >>>> CREATE TABLE table_name >>>> ( >>>> column1 datatype [ NULL | NOT NULL ], >>>> column2 datatype [ NULL | NOT NULL ], >>>> ... >>>> ); >>>> >>>> UPDATE table >>>> SET column1 = expression1, >>>> column2 = expression2, >>>> ... >>>> WHERE conditions; >>>> >>>> >>>> INSERT INTO table >>>> (column1, column2, ... ) >>>> VALUES >>>> (expression1, expression2, ... ), >>>> (expression1, expression2, ... ), >>>> ...; >>>> >>>> >>>> issue for the above implementation is the query syntax differs(Some) >>>> from DB type to another (eg.data types) . So decided to use a XML file >>>> which has DB type specific data types. So dynamically we can access the db >>>> type and retrieve data types to generate the query in prepared statement. >>>> >>>> Xml file would be like following. >>>> >>>> <mappings> >>>> <mapping db='ansi'> >>>> <types> >>>> <type> >>>> <from>string</from> >>>> <to>VARCHAR</to> >>>> </type> >>>> <type> >>>> <from>double</from> >>>> <to>DOUBLE</to> >>>> </type> >>>> >>>> </types> >>>> </mapping> >>>> <mapping db='mysql'> >>>> <types> >>>> //mysqi specific data types if any >>>> >>>> </types> >>>> </mapping> >>>> <mapping db='oracle'> >>>> <types> >>>> <type> >>>> <from>string</from> >>>> <to>varchar2</to> >>>> </type> >>>> </types> >>>> </mapping> >>>> <mapping db='mssql'> >>>> <types> >>>> <type> >>>> <from>string</from> >>>> <to>varchar2</to> >>>> </type> >>>> </types> >>>> </mapping> >>>> <mapping db='h2'> >>>> <types> >>>> //h2 specific data types if any >>>> </types> >>>> </mapping> >>>> </mappings> >>>> >>>> Like wise above xml can be created. Main idea is to use a separate XML >>>> to manipulate different data types. According to above XML first checks >>>> for specific data type in specific db mappings. If not found then the value >>>> is read from the generic data type mapping as stated as <mapping >>>> db='ansi'>. All the data type mappings are not included in above >>>> configuration. >>>> >>>> Your suggestions & feedbacks are highly appreciated. >>>> >>>> >>>> >>>> >>>> >>>> >>>> On Sat, Nov 29, 2014 at 11:15 AM, Lahiru Cooray <[email protected]> >>>> wrote: >>>> >>>>> Hi, >>>>> Maintenance wise it's nice to keep queries in separate XML. >>>>> And I think it's better to keep all the generic queries in a one >>>>> section of the XML and keep DBMS specific queries in separate sections. So >>>>> you don't need to rewrite common queries for each type. Also better to >>>>> consider MS SQL Server as well in testing ( if it's supported DB type) as >>>>> there are many syntax differences compared to Oracle and mysql >>>>> >>>>> On Fri, Nov 28, 2014 at 4:49 PM, Damith Wickramasinghe < >>>>> [email protected]> wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> Currently we have the support only for Mysql and it is decided to >>>>>> implement a generic adapter to support any RDBMS database. For now >>>>>> adapter >>>>>> implementation will be focused on supporting Oracle, Mysql and H2. >>>>>> >>>>>> I will update the thread on decided architecture for the said >>>>>> requirement soon. Any feedbacks on the requirement will be greatly >>>>>> appreciated. >>>>>> >>>>>> Regards, >>>>>> Damith. >>>>>> >>>>>> -- >>>>>> Software Engineer >>>>>> WSO2 Inc.; http://wso2.com >>>>>> <http://www.google.com/url?q=http%3A%2F%2Fwso2.com&sa=D&sntz=1&usg=AFQjCNEZvyc0uMD1HhBaEGCBxs6e9fBObg> >>>>>> lean.enterprise.middleware >>>>>> >>>>>> mobile: *+94728671315 <%2B94728671315>* >>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> Architecture mailing list >>>>>> [email protected] >>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> *Lahiru Cooray* >>>>> Software Engineer >>>>> WSO2, Inc.;http://wso2.com/ >>>>> lean.enterprise.middleware >>>>> >>>>> Mobile: +94 715 654154 >>>>> >>>>> _______________________________________________ >>>>> Architecture mailing list >>>>> [email protected] >>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>> >>>>> >>>> >>>> >>>> -- >>>> Software Engineer >>>> WSO2 Inc.; http://wso2.com >>>> <http://www.google.com/url?q=http%3A%2F%2Fwso2.com&sa=D&sntz=1&usg=AFQjCNEZvyc0uMD1HhBaEGCBxs6e9fBObg> >>>> lean.enterprise.middleware >>>> >>>> mobile: *+94728671315 <%2B94728671315>* >>>> >>>> >>> >>> >>> -- >>> Software Engineer >>> WSO2 Inc.; http://wso2.com >>> <http://www.google.com/url?q=http%3A%2F%2Fwso2.com&sa=D&sntz=1&usg=AFQjCNEZvyc0uMD1HhBaEGCBxs6e9fBObg> >>> lean.enterprise.middleware >>> >>> mobile: *+94728671315 <%2B94728671315>* >>> >>> >>> _______________________________________________ >>> Architecture mailing list >>> [email protected] >>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>> >>> >> >> >> -- >> *Anjana Fernando* >> Senior Technical Lead >> WSO2 Inc. | http://wso2.com >> lean . enterprise . middleware >> >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > > -- > *Darshana Akalanka Pagoda Arachchi,* > *Software Engineer* > *078-4721791* > > _______________________________________________ > Architecture mailing list > [email protected] > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > > -- Software Engineer WSO2 Inc.; http://wso2.com <http://www.google.com/url?q=http%3A%2F%2Fwso2.com&sa=D&sntz=1&usg=AFQjCNEZvyc0uMD1HhBaEGCBxs6e9fBObg> lean.enterprise.middleware mobile: *+94728671315*
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
