[Devel] postgres problem with mysql proprietary SQL queries

Bogdan-Andrei Iancu bogdan at voice-system.ro
Tue Aug 9 11:37:58 CEST 2005


Hi Ingo,

so, if the functions are created along with the LCR tables, you say the 
module does not need any changing and the DB raw queries will work for 
both mysql and postgres?

regards,
bogdan

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';
>
>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
>
>  
>




More information about the Devel mailing list