[Devel] postgres problem with mysql proprietary SQL queries

[Klaus Darilion]

Ingo Wolfsberger wrote:
> Hi
> 
> Postgres is a real db ;)
> 
> It should be possible to "create" the needed functions.
> see: http://www.openrdf.org/issues/browse/SES-112
> 
> Not tested:
> 
> CONCAT:
> CREATE FUNCTION "concat" (text,text) RETURNS text AS 
>     'SELECT $1 || $2;' LANGUAGE 'sql';
> 
> RAND:
> CREATE FUNCTION "rand" () RETURNS dp AS 
>  'SELECT random();' LANGUAGE 'sql';

This must be changed to:
CREATE FUNCTION "rand" () RETURNS double precision AS
   'SELECT random();' LANGUAGE 'sql';

Then it works :-)

Nevertheless, is "building mysql functions in postgres" the proper way 
or should we make 2 different SQL queries in lcr module?

Will there be any performance issues in postgres because of the self 
created functions?

klaus




> 
> Please test and tell me if it works.
> 
> bye,
>    Ingo
> 
>>--- Ursprüngliche Nachricht ---
>>Von: Klaus Darilion <klaus.mailinglists at pernau.at>
>>An: Bogdan-Andrei Iancu <bogdan at voice-system.ro>
>>Kopie: devel at openser.org
>>Betreff: Re: [Devel] postgres problem with mysql proprietary SQL queries
>>Datum: Mon, 01 Aug 2005 12:32:01 +0200
>>
>>Bogdan-Andrei Iancu wrote:
>>
>>>Hi Klaus,
>>>
>>>IMHO, the best way to go is to align all DB-dependent module to the DB 
>>>interface and to avoid using raw queries which depends of the DB driver.
>>>
>>>first option should be to try to make lcr to use only predefined DB 
>>>interface (if possible)- not sure if the mysql proprietary are used only
>>>for simplicity or there is no other way.
>>
>>Some parts are because of simplicity (CONCAT) and be solved in C.
>>For other parts I do not know a solution in C (RAND).
>>
>>klaus
>>
>>>second option, if first fails, will your option nr. 1. I really do not 
>>>like it very much since the module will become aware of the underlaying 
>>>DB driver, which suppose to be transparent :(.
>>>
>>>regards,
>>>Bogdan
>>>
>>>Klaus Darilion wrote:
>>>
>>>
>>>>Hi all!
>>>>
>>>>I tried to use the lcr module with postgres and found the following 
>>>>problem, which is a generic problem.
>>>>
>>>>The lcr module uses SQL queries, which are mysql proprietary. (e.g. 
>>>>CONCAT, RAND). These queries must be adopted to fit postgresql.
>>>>
>>>>There are several options how to do this:
>>>>1. The module should parse the db_url, and according to the service 
>>>>(mysql, postgres, ...) the proper SQL query should be used.
>>>>
>>>>2. a compilation option
>>>>
>>>>3. ???
>>>>
>>>>Please give us your comments and other ideas to solve this problem.
>>>>
>>>>regards,
>>>>klaus
>>>>
>>>>
>>>>
>>>>-------- Original Message --------
>>>>Hi Juha!
>>>>
>>>>I do not have commited the lcr module as I found an open issues: The
>>>>SELECT queries are not compatibel with postgresql. postgres has other
>>>>syntax for CONCAT and RAND:
>>>>
>>>>CONCAT --> ||
>>>>RAND() --> RANDOM()
>>>>
>>>>Thus, the lcr module would have to query the db_url if it is postgres
>>
>>or
>>
>>>>mysql and use the proper SELECT query.
>>>>
>>>>Attached is the lcr_mod.c patched with URI scheme and transport, and
>>>>also the SELECT was changed to fit postgres. Maybe you can take a look
>>>>at this problem.
>>>>
>>>>btw: openser crashes with the original mysql SELECT sent to postgres. I
>>>>think this is a bug in the postgres module.
>>>>
>>>>regards,
>>>>klaus
> 
> l
>