Difference between revisions of "DPL talk:Manual - DPL with PostgreSQL"

Revision as of 18:25, 29 September 2008

DPL broken with PostgreSQL and MW 1.12

Here's the example page on my wiki - any DPL I try to create on a page renders this error:

The DPL extension (version 1.6.5) produced a SQL statement which lead to a Database error.
The reason may be an internal error of DPL or an error which you made,
especially when using DPL options like titleregexp.
Query text is:
SELECT DISTINCT page.page_namespace as page_namespace,page.page_title as page_title, REPLACE(REPLACE(CONCAT( IF(page.page_namespace=0, , CONCAT(CASE page.page_namespace WHEN 1 THEN 'Talk' WHEN 2 THEN 'User' WHEN 3 THEN 'User_talk' WHEN 4 THEN 'DDOwiki' WHEN 5 THEN 'DDOwiki_talk' WHEN 6 THEN 'Image' WHEN 7 THEN 'Image_talk' WHEN 8 THEN 'MediaWiki' WHEN 9 THEN 'MediaWiki_talk' WHEN 10 THEN 'Template' WHEN 11 THEN 'Template_talk' WHEN 12 THEN 'Help' WHEN 13 THEN 'Help_talk' WHEN 14 THEN 'Category' WHEN 15 THEN 'Category_talk' WHEN 100 THEN 'Character' WHEN 101 THEN 'Character_talk' WHEN 102 THEN 'Guild' WHEN 103 THEN 'Guild_talk' END, ':')), page.page_title), '_', ' '),'♣','⣣') as sortkey FROM page INNER JOIN categorylinks AS cl0 ON page.page_id=cl0.cl_from AND (cl0.cl_to='Example_items') WHERE 1=1 AND page.page_is_redirect=0 ORDER BY sortkey ASC LIMIT 0, 500
Error message is:
ERROR: LIMIT #,# syntax is not supported HINT: Use separate LIMIT and OFFSET clauses.

I'd appreciate some help in getting this fixed, I don't know what's up! Elliottcable 23:45, 31 January 2008 (CET)

This seams to be fairly simple: change the SQL statement from "LIMIT 0, 500" to "OFFSET 0 LIMIT 500". Somewhere around line #2760 ...

If it works I will put it into the next release. Gero 10:44, 1 February 2008 (CET)

I can confirm the change to OFFSET # LIMIT # is needed and does help for postgres.--Danbrice 14:56, 15 June 2008 (CEST)

Media Wiki sets the search path

Seems that the open() function in includes/DatabasePostgres.php (at least in MW 1.11) sets the search path and overrides what ever you set it to - so instead of putting the if and concat functions in a new schema called mysql, I just added them to the public schema, which seems to work. Used the following sql:

create or replace function concat(text, text) returns text
as $function$
  begin
    return $1 || $2;
  end;
$function$ language plpgsql immutable strict;

create or replace function if(boolean, text, text) returns text
as $function$
  begin
    if $1 then
      return $2;
    else
      return $3;
    end if;
  end;
$function$ language plpgsql immutable strict;

--Danbrice 15:36, 15 June 2008 (CEST)

Regex with Posgtresq

Postgresql uses "~" instead of "REGEX" for doing regex searches. The following modifications worked for me on 1.7.4 for doing title regex (haven't tested the others):

<                     $sCategoryComparisonMode = ' REGEXP '; 
---
>                     $sCategoryComparisonMode = ' ~ ';

<                     $sNotCategoryComparisonMode = ' REGEXP ';
---
>                     $sNotCategoryComparisonMode = ' ~ ';

<                     $sTitleMatchMode = ' REGEXP ';
---
>                     $sTitleMatchMode = ' ~ ';

<                     $sNotTitleMatchMode = ' REGEXP ';
---
>                     $sNotTitleMatchMode = ' ~ ';