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 <lahi...@wso2.com> 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 <dami...@wso2.com>
> 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
>> Architecture@wso2.org
>> 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
> Architecture@wso2.org
> 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>*
_______________________________________________
Architecture mailing list
Architecture@wso2.org
https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
