Re: [Devel] postgres problem with mysql proprietary SQL queries

Ingo Wolfsberger iwolfsberger at gmx.net
Wed Aug 10 19:43:00 CEST 2005


Hi Bogdan,

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

Yes, thats the main idea.
I have not tested the postgres functions because I have no lcr running
currently, but I would give remote support to one who has postgres & lcr.
Then we could add the functions to the postgres db init file.

bye,
   Ingo
 
> 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
> >
> >  
> >
> 

-- 
5 GB Mailbox, 50 FreeSMS http://www.gmx.net/de/go/promail
+++ GMX - die erste Adresse für Mail, Message, More +++



More information about the Devel mailing list