[Devel] postgres problem with mysql proprietary SQL queries

[Klaus Darilion]

There are 2 solution:
  1. Ingo's approach: create mysql-compatibility functions in postgres

  2. Klaus's approach: different SQL queries in lcr module (quering the 
db_url parameter and choosing the respective query)

IMO, this problem is in openser, thus it should be fixed in openser (not 
postgres). But on the other hand, fixing in postgres would allow usage 
of mysql functions also in other module.

Conclusion: The fast workaround is 1. A clean solution should handle 
this inside openser.

If no concerns, I will commit a fix for 1

regards
klaus

Bogdan-Andrei Iancu wrote:
> Hi Klaus,
> 
> I agree that the lcr module should stick to the DB API - by this all DB 
> implementation will be fully supported without any discrimination 
> (postgres, dbtext, ldap in the future, etc).
> 
> but looking into the code, I found this query:
>    "SELECT %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE '%.*s' LIKE 
> %.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = 
> %.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()"
> 
> which is quite impossible to implement via API :(....If someone had some 
> clue how to bring this query to a one supported by the DB API, I will be 
> happy to switch to it....
> 
> for the moment, for postgres, the additional functions are the only way 
> to make it work.....
> 
> 
> regards,
> bogdan
> 
> 
> Klaus Darilion wrote:
> 
>> 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
> 
> 
>