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

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

(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
>
>  
>