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
