Re: [Gusdev-gusdev] Postgres GUS Scripts

[Michael S. <msa...@pc...>]

One quick clarification that Chris has brought to my attention:

I said:

"Ultimately, object orientated-ness in both GUS and Oracle appears to 
be, at best, an afterthought.  They are very much leveraging the 
relational..."

This should have been "... object orientated-ness in both Postgres and 
Oracle ..."

As Chris has pointed out, the object orientated nature of GUS has 
existed from the earliest days of GUS, and was very much carefully 
planned and implemented.  Indeed, much of GUS's object orientated-ness 
(from the object layer to how inheritance is implemented) predates any 
support Oracle or Postgres had for objects, as well as predating many 
(if not all) of the object/relational mapping tools out there such as 
Hibernate.

Sorry for the confusion and unintentional offense.

--Mike



Michael Saffitz wrote:
> 
> 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 sent 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