Re: [Gusdev-gusdev] Postgres GUS Scripts

[Jeetendra S. <so...@vb...>]

Michael and All,

       I have attached the scripts for Postgresql schema of GUS (with
views defined on Implementation tables). Just modifying and running
create-db.sh script should install the entire schema.
       I have tested this schema with a limited number of plugins and
seems to be working ok. I have had to make a few changes to the
schema as and when I found a problem. Therefore, I would request
anyone using this schema to post any problems/bugs that you might
find with the schema.

       Michael, I would be glad to have a developer account for GUS CVS. I
would appreciate if you could let me know the procedure for the
same.

Thanks,
Jeetendra.










>
> Jeetendra, Alberto, and all:
>
> First, my apologies for not being more responsive-- I've been
> extraordinarily busy with other matters and haven't had the time or
> opportunity to properly be involved with this work.  This work should,
> for the most part, now be complete, and focusing on the Postgres stuff
> is a high priority for me when I return from vacation in early January.
>
> My comments are below.
>
>
> Jeetendra Soneja wrote:
>> Hi Michael,
>>
>>      I somehow managed to get a Postgresql version of GUS working,
>> however, this schema is the mirror of the current Oracle GUS schema
>> and not the scripts that were mailed to the list. The project that I
>> am working on requires us to have Postgresql as the backend, and I
>> really wanted something working for the time being. Therefore, I
>> tried to modify a number of things with the Postgresql scripts that
>> you had sent on the list. However, I faced some major issues such as,
>> the inherited tables did not have the primary key contraint defined
>> even though the base table has it.
>
> Yes, this is a limitation, and a serious one at that, that we've
> discovered in Postgres.  It (and other issues) have been motivating
> discussions of how we implement inheritance in GUS, and we hope to have
> to a proposal for an alternative to the list sometime in January.
>
>   Similarly, they do not inherit the
>> children associated with the base table via foreign key relationship.
>> Even after defining the primary keys and the child relationships for
>> all the inherited tables, it didn't work. I would like to
>> point one more thing here. If we insert a row into the inherited table,
>> although this row does appear in the base table too, this row neither
>> obeys the foreign key contraint nor the primary key constraint that is
>> defined on the base table.
>
> Ultimately, object orientated-ness in both GUS and Oracle appears to be,
> at best, an afterthought.  They are very much leveraging the relational
> nature of the system, and as such, a multi-table constraint doesn't fit
> nicely in their paradigm.
>
>>      So, finally I generated the Postgresql version of the existing
>> Oracle
>> schema that has views defined on the implementation tables. After
>> this, I generated insert, update and delete rules for all the views.
>
> Good to hear.  This is most likely the best option for those that need
> working Postgres instances of GUS today.  Would you mind sharing your
> scripts with the list?
>
>>      After some modifications to plugins, I have it finally working with
>> the existing gus application. However, I have tested it only with
>> SubmitRow, GBParser, LoadTaxon and LoadBlastSimFast plugins since these
>> are the ones that I will be using the most.
>>
>> Here is the list of issues that I faced while getting the plugins run
>> with
>> Postgresql schema :-
>>
>> --I changed the date field from 'sysdate' to 'now()' in
>> LoadBlastSimFast.pm
>>
>> --In GBParser.pm I changed the following line from
>> $release_date = sysdate;
>> to
>> $release_date = $externalDatabaseRow->getDatabase()->getDateFunction();
>>
>> --Inserted the following lines since in Genbank format, BASE COUNT line
>> is
>> not mandatory and if this line is not present, it gives error on trying
>> to
>> insert emptry string for numeric datatype.
>>
>>   $h->{'a_count'} = "null" if(!$h->{'a_count'});
>>   $h->{'c_count'} = "null" if(!$h->{'c_count'});
>>   $h->{'g_count'} = "null" if(!$h->{'g_count'});
>>   $h->{'t_count'} = "null" if(!$h->{'t_count'});
>>
>> --Changed the columns names left to leftcol and right to rightcol for
>> the
>> view RAD3.Scan (and its version table too).
>>
>> --The most significant problem that I faced was with the view columns
>> that
>> are NOT nullable. Is postgresql, even if the actual table has a
>> particular
>> column as NOT NULLABLE, this information is not present in the view
>> description (or its metadata). Therefore, the ViewName_Table.pm modules
>> generated for the views have all the columns as NULLABLE. Therefore, I
>> have just replaced all the '_Table.pm' modules for all the views by the
>> modules generated with the Oracle schema. [Though this is not the
>> correct
>> way, I will have to resort to it for the time being at least. So, rather
>> than building each time, I would just zip the gus-application whereever
>> I
>> need to run it with the postgresql version].
>>
>>
>
> Thanks for all your work-- it's very helpful to us and everyone working
> on the Postgres stuff.  We'll need to all work together to ensure that
> the plugins are cross platform, and in cases like the date issue, we'll
> likely need to provide some logic so that they behave properly
> regardless of the underlying RDBMS.  Do you have a developer account on
> the GUS CVS?
>
>> Please let me know your suggestions, comments about the same.
>>
>>
>> Thanks a lot,
>> Jeetendra.
>>
>>
>>
>>
>>
>>
>>>Hi Alberto,
>>>
>>>I don't expect to release any new Postgres work until after the first of
>>>the year.  The largest issue at this point is how to address subclassing
>>>in GUS and the implementation tables.  It's not specific to Postgres--
>>>it's an open area of discussion here at CBIL.
>>>
>>>Continue to follow the GUSDEV list for releases and announcements, and
>>>thanks for the interest.
>>>
>>>--Mike
>>>
>>>
>>>Alberto Davila wrote:
>>>
>>>>Hi Michael, Jeetendra,
>>>>
>>>>Recently one of our students (Pablo Mendes) posted a error log in
>>>> gusdev
>>>>regarding the PostgreSQL version of GUS, but no luck to get any reply
>>>>:-(
>>>>
>>>>I wonder to know any of you would have plans to release a new version
>>>> of
>>>>the PosgreSQL GUS, then we can track it and finish our installation...
>>>>hopefully, this postgreSQL version can help GUS to become more popular,
>>>>then attract more programmers to help with the development/improvement
>>>>of it,
>>>>
>>>>Thanks, Alberto
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>Jeetendra Soneja wrote:
>>>>
>>>>
>>>>>Hi Michael,
>>>>>
>>>>>Thanks for your reply. I had another problem running the plugins using
>>>>>the
>>>>>Postgresql version. I have generated all the objects by following the
>>>>>normal build procedure. However, I am getting the following error
>>>>> while
>>>>>running the plugin:
>>>>>
>>>>>Error:  attempting to access a child 'GUS::Model::DoTS::NAEntry' of
>>>>>table
>>>>>'GUS::Model::DoTS::ExternalNASequence' , but that table does not have
>>>>> a
>>>>>child of that type. at
>>>>>/home/soneja.local/gusHomePG/lib/perl/GUS/Model/GusRow.pm line 396,
>>>>><GEN0>
>>>>>line 163.
>>>>>
>>>>>When I opened the automatically generated ExternalNASequence_Table.pm,
>>>>>it's child list is empty.
>>>>>
>>>>>Did I miss something during the build process ?
>>>>>
>>>>>Thank you for your help.
>>>>>
>>>>>Jeetendra.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Hi Jeetendra:
>>>>>>
>>>>>>Jeetendra Soneja wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Hi Michael,
>>>>>>>
>>>>>>>  Thanks for the updates. After downloading the latest code from the
>>>>>>>CVS,
>>>>>>>I was able to register a couple of plugins successfully. Just a
>>>>>>> small
>>>>>>>change while registering GBParser, I had to comment out 'use
>>>>>>>GUS::Model::DoTS::NAFeatureImp' line.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>Right, and further, while this technically works in Oracle, it
>>>>>>represents an incorrect usage of the data model:  The IMP tables
>>>>>>should
>>>>>>NEVER be used, either directly in SQL queries, nor through the object
>>>>>>layer.  Instead, the appropriate superclass should be used
>>>>>>(GUS::Model::DoTS::NAFeature in this case)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>I am trying to run GBParser and am having the following problems :-
>>>>>>>
>>>>>>>--An oracle-specific call setOracleDateFormat() in the run mode of
>>>>>>>the
>>>>>>>plugin was causing problems. (For the time being I have commented
>>>>>>> it)
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>In addition, it looks like GBParser uses Oracle's sysdate in setting
>>>>>>the
>>>>>>release date.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>--It's having problems inserting rows into the tables. The value ''
>>>>>>>is
>>>>>>>being inserted into the NUMERIC type columns. Postgresql doesn't
>>>>>>>seem to
>>>>>>>interprete this as a null value, but an emptry string.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>Thanks--- This will need to be resolved higher up in the object
>>>>>> layer.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Thank you once again for your help.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>Thank you for all your testing!!  I have a growing list of issues and
>>>>>>fixes for the Postgres version-- I'll keep this list updated as fixes
>>>>>>are committed to CVS, and please continue to mail the list with your
>>>>>>progress in getting this stuff working.
>>>>>>
>>>>>>--Mike
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Jeetendra.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>All,
>>>>>>>>
>>>>>>>>I've updated DbiDbHandle in CVS to address the capitalization issue
>>>>>>>>(thanks to Angel for the pointer here).
>>>>>>>>
>>>>>>>>This should then allow plugins to be registered with postgres GUS
>>>>>>>>schemas.
>>>>>>>>
>>>>>>>>Just to recap:
>>>>>>>>
>>>>>>>>1) You'll need to use the latest SQL scripts s
> ent by me on 11/30,
>>>>>>>>and
>>>>>>>>the latest CVS (as of now!)
>>>>>>>>2) You'll need to change line 34 of GusApplication such that the
>>>>>>>>requiredDbVersion for Core is 3.0, not 3.
>>>>>>>>3) You'll need to manually add rows as outlined in VBI's GUS
>>>>>>>>installation document.  In addition, you'll need to add these rows:
>>>>>>>>
>>>>>>>>INSERT INTO Core.AlgorithmParamKeyType
>>>>>>>>VALUES(0,'string',now(),1,1,1,1,1,0,1, 1, 1, 1);
>>>>>>>>INSERT INTO Core.AlgorithmParamKeyType
>>>>>>>>VALUES(1,'float',now(),1,1,1,1,1,0,1, 1, 1, 1);
>>>>>>>>INSERT INTO Core.AlgorithmParamKeyType
>>>>>>>>VALUES(2,'int',now(),1,1,1,1,1,0,1, 1, 1, 1);
>>>>>>>>INSERT INTO Core.AlgorithmParamKeyType
>>>>>>>>VALUES(3,'ref',now(),1,1,1,1,1,0,1, 1, 1, 1);
>>>>>>>>INSERT INTO Core.AlgorithmParamKeyType
>>>>>>>>VALUES(4,'boolean',now(),1,1,1,1,1,0,1, 1, 1, 1);
>>>>>>>>INSERT INTO Core.AlgorithmParamKeyType
>>>>>>>>VALUES(5,'date',now(),1,1,1,1,1,0,1, 1, 1, 1);
>>>>>>>>
>>>>>>>>Thanks,
>>>>>>>>
>>>>>>>>Mike
>>>>>>>>
>>>>>>>>
>>>>>>>>Jeetendra Soneja wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>Hi all,
>>>>>>>>>
>>>>>>>>>The reason that I got errors that I posted earlier was that I did
>>>>>>>>>not
>>>>>>>>>do
>>>>>>>>>a
>>>>>>>>>build. I had just created the Postgresql GUS schema and was trying
>>>>>>>>>to
>>>>>>>>>register the gus application. However, after doing a build, things
>>>>>>>>>worked
>>>>>>>>>out fine.
>>>>>>>>>
>>>>>>>>>I would like to list some of the problems that I have faced till
>>>>>>>>>this
>>>>>>>>>point in using the Postgresql version:-
>>>>>>>>>
>>>>>>>>>-- The rows included in the script gus-rows.pl need a minor
>>>>>>>>>modification.
>>>>>>>>>Values in the rows that are being inserted into core.databaseinfo
>>>>>>>>>need
>>>>>>>>>to
>>>>>>>>>be changed. The names of the schemas CORE, DOTS and SRES should be
>>>>>>>>>changed
>>>>>>>>>Core, DoTS, SRes respectively. Also the values for the version
>>>>>>>>>numbers
>>>>>>>>>should be changed from 1.0 and 3.0 to 1 and 3 respectively.
>>>>>>>>>
>>>>>>>>>-- Currently, I am having trouble registering a plugin (any
>>>>>>>>>plugin). A
>>>>>>>>>number of modules/subroutines are retrieving row as a
>>>>>>>>>hash-reference
>>>>>>>>>and
>>>>>>>>>access a value in a row using key, i.e. column name of the table.
>>>>>>>>>And
>>>>>>>>>all
>>>>>>>>>the keys used in the code are in upper case. This works in case of
>>>>>>>>>oracle,
>>>>>>>>>however, select statements in Postgresql return all the column
>>>>>>>>>names in
>>>>>>>>>lower case. Therefore, in this case, it couldn't find the row for
>>>>>>>>>ga in
>>>>>>>>>the core.algorithmimplementation table (although it's there since
>>>>>>>>> I
>>>>>>>>>have
>>>>>>>>>already registered ga). And when I change the key/column name to
>>>>>>>>>lower
>>>>>>>>>case in GusApplication.pm code, it worked fine.
>>>>>>>>>
>>>>>>>>>I would appreciate any suggestion or comments.
>>>>>>>>>
>>>>>>>>>Thanks a lot,
>>>>>>>>>Jeetendra.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>Hi Jeetendra,
>>>>>>>>>>
>>>>>>>>>>Comments below....
>>>>>>>>>>
>>>>>>>>>>Jeetendra Soneja wrote:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>Hi Michael,
>>>>>>>>>>>     I am glad that the PostgreSQL version has been released.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>This is not an official release!!  These are preliminary scripts,
>>>>>>>>>>which
>>>>>>>>>>have had very little testing and are likely to change in the
>>>>>>>>>>future
>>>>>>>>>>(although probably not significantly).
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>     I had a question about using it. Could you tell me if any
>>>>>>>>>>> of
>>>>>>>>>>>the
>>>>>>>>>>>existing plugins (especially those in Common::Plugin) would work
>>>>>>>>>>>with PostgreSQL version of GUS ?
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>No existing plugins have been tested with PGGUS.  Please report
>>>>>>>>>>any
>>>>>>>>>>successes or failures you have to the list.
>>>>>>>>>>
>>>>>>>>>>--Mike
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>Thanks,
>>>>>>>>>>>Jeetendra.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>>All,
>>>>>>>>>>>>
>>>>>>>>>>>>Postgres GUS scripts are attached.  The first, pggus.sql
>>>>>>>>>>>>contains
>>>>>>>>>>>>DDL
>>>>>>>>>>>>for creating GUS tables, indexes, constraints, and sequences.
>>>>>>>>>>>>The
>>>>>>>>>>>>second, gus-rows.sql, contains DML for populating the
>>>>>>>>>>>>core.tableinfo
>>>>>>>>>>>>and
>>>>>>>>>>>>core.databaseinfo tables.
>>>>>>>>>>>>
>>>>>>>>>>>>Consider these early beta-- this is not part of an official
>>>>>>>>>>>>release,
>>>>>>>>>>>>they've had very limited testing, and they are unsupported.  In
>>>>>>>>>>>>general
>>>>>>>>>>>>the existing documentation should provide the information
>>>>>>>>>>>>needed to
>>>>>>>>>>>>create an instance with these and GUS with it, but there are
>>>>>>>>>>>>derivations
>>>>>>>>>>>>that will be necessary.
>>>>>>>>>>>>
>>>>>>>>>>>>More information on Postgres and GUS, including an official
>>>>>>>>>>>>release,
>>>>>>>>>>>>should be coming in the next several weeks.
>>>>>>>>>>>>
>>>>>>>>>>>>Thanks,
>>>>>>>>>>>>
>>>>>>>>>>>>Mike
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>-------------------
>>>
>>>------------------------------------
>>>
>>>>>>>>SF email is sponsored by - The IT Product Guide
>>>>>>>>Read honest & candid reviews on hundreds of IT Products from real
>>>>>>>>users.
>>>>>>>>Discover which products truly live up to the hype. Start reading
>>>>>>>>now.
>>>>>>>>http://productguide.itmanagersjournal.com/
>>>>>>>>_______________________________________________
>>>>>>>>Gusdev-gusdev mailing list
>>>>>>>>Gus...@li...
>>>>>>>>https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>-------------------------------------------------------
>>>>>>SF email is sponsored by - The IT Product Guide
>>>>>>Read honest & candid reviews on hundreds of IT Products from real
>>>>>>users.
>>>>>>Discover which products truly live up to the hype. Start reading now.
>>>>>>http://productguide.itmanagersjournal.com/
>>>>>>_______________________________________________
>>>>>>Gusdev-gusdev mailing list
>>>>>>Gus...@li...
>>>>>>https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>
>>
>
>
>
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://productguide.itmanagersjournal.com/
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev


-- 
Jeetendra Soneja
Research Associate
Virginia Bioinformatics Institute
1880 Pratt Dr., Building XV
Blacksburg, VA 24060, USA
Phone: (540)-231-2789
http://www.vbi.vt.edu