Re: [Gusdev-gusdev] Re: [Fwd: Porting GUS to a Open SourceDB ?]

[Steve F. <st...@pc...>]

I think we need to find out ASAP whether postgres and/or mysql have 
system tables that offer the kind of info we need.

steve

Angel Pizarro wrote:

> Steve,
> This is a very accurate and complete synopsis of the issues we have 
> discussed so far. Without actually starting the project, I don't 
> foresee us coming up with anything else.
>
> As for translation between DB platforms, there is a Sourceforge.net 
> project that Allen Day belongs to named SQLFairy. See more about it at 
> http://sqlfairy.sourceforge.net/
>
> They claim to translate between Oracle and PostgreSQL, but this has 
> not worked in practice for me when I tried to translate the GUS schema 
> files to postgresql. Part of the problem is that we separate tables, 
> views and constraints into their own files in addition to separate 
> file groups for schemas. It also had trouble with multi-line comments.
>
> They have listed a platform independent XML syntax, but they as yet do 
> not have a formal DTD or XML schema (I asked already) . They said they 
> were willing to formalize on an XML schema if we provide it and is 
> close to what they have already. I don't see any reason to re-invent 
> the wheel here.
>
> Angel
>
> Steve Fischer wrote:
>
>> (Cesare and Giuseppe, are you on the gusdev mailing list?  If not, 
>> you can subscribe at: 
>> http://lists.sourceforge.net/lists/listinfo/gusdev-gusdev)
>>
>> Folks-
>>
>> Here is a quick outline of what might need to be done to make GUS 
>> RDBMS independent.    I discuss the two major subsystems of GUS:  the 
>> schema and the application framework.
>>
>> Please bear in mind that i am not a DB expert.   Comments and 
>> corrections encouraged.
>>
>> GUS Schema
>>   - schema definition.     it appears that different RDBMSs have 
>> differences in their data types and in the way they create tables and 
>> specify constraints.   it seems that this we need to define the 
>> schema in a system neutral syntax, say, xml, and then transform that 
>> into system-specific syntax.   i wonder if there are third party 
>> tools to do this.
>>
>>    - schema spaces.   the definition of the schema currently assumes 
>> "schema name spaces."     for example, we have RAD.Process.    For 
>> systems that don't support this, we may need to define the schema as, 
>> say, RAD_Process.   In other words, prepend the schema name to the 
>> table names.
>>
>>   - schema installation.    this will need to be system-specific, and 
>> will probably be generated by transforming a schema definition in a 
>> neutral syntax
>>
>>
>> GUS Application Framework
>>    - the application framework currently uses DBI and JDBC.   these 
>> are fine.
>>
>>   - the object layer API (and all the code that uses it) assumes that 
>> there is a schema space.   worse still, it hard codes the names as 
>> DoTS, RAD, Core, TESS and SRes.   The changes I envision here are:
>>           - removing hard coded references to schema names, replacing 
>> them with logical names.
>>           - allow the API to *keep* the schema space assumption.    
>> however, inside the object layer, use a RDBMS-specific "driver" to 
>> handle them in a system specific way.   for example, it would use '.' 
>> as the concatenation character for oracle and '_' for mySql.
>>            - the object layer's code generator (generates all the 
>> objects) has a system-specific module for discovering 
>> meta-information about the tables.   this is segregated into system 
>> specific modules.   for any new system, we would need to write new 
>> ones.    note:  any system that we support would need to offer this 
>> meta information somehow!!!!
>>
>>    - sql code.   there is plenty of direct SQL in our application 
>> code and in plugins.   here are the problems:
>>           - Wherever this sql refers to table names, the sql would 
>> need to be modified to get the table names from an API which can 
>> provide them in correct syntax.            - some of the code relies 
>> on system specific optimization directives.   i don't know how we'll 
>> handle this.
>>           - are there any other sql incompatibilities?   how about 
>> join and/or outer join syntax?
>>
>> Any other ideas?
>>
>> Steve
>>
>>
>>
>> Giuseppe Jurman wrote:
>>
>>>> Thanks Elisabetta,
>>>>
>>>> An important question would be also why PostgreSQL and not MySQL, the
>>>> latter appears to be evolved well and have now a good support to
>>>> transactions via InnoDB, etc. I am very inclined to use Gmod and chado
>>>> por our project, but would like to have a final check on the potential
>>>> of GUS before starting things.
>>>>   
>>>
>>>
>>> ITC-irst has started a working group for a full open source version of
>>> GUS, so to allow the substitution of the Oracle database management
>>> system for data tables and relations within GUS with an open source
>>> solution. In principle we are not oriented to the substitution
>>> of Oracle with another specific DMBS.
>>>
>>> It might be worthwhile to consider the possibility to turn DB
>>> independent the whole object-related part of GUS and then choosing the
>>> suitable DB-dedicated driver depending on the user's system. The first
>>> (heavy) step towards this option requires to check the existing code
>>> looking for the Oracle-specific SQL, and then designing an ad-hoc
>>> strategy to transform into either a DBI (better) or into a DBD (here
>>> the choice of the DBMS becomes a constraint).
>>>
>>> An important issue, for us, is how to align the DBD portions of the
>>> code along the next releases of GUS. In this view, we see the need of
>>> a DBI-DBD separation really relevant.
>>>
>>> We would like to perform the Oracle-PostgreSQL conversion in the most
>>> automated way possible. Before, the Oracle proprietary SQL code has to
>>> be somehow moved to the upper Perl Object Layer level, or to a new
>>> Perl Object Layer.
>>>
>>> Comments welcomed
>>>
>>>
>>> Giuseppe Jurman
>>> Cersare Furlanello
>>>
>>>  
>>>
>
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev