#590 Patched file for bug 3527034

closed-accepted
None
5
2013-04-16
2013-04-11
dyspd
No

Postgress sql engine Like is case sensitive In order for places autocomplete to work this uses iLike comparison.
Ugly but works!

Testing Procedure
SVN install on postgres sql environment, GeoNames set to false will result in the error described in bug 3527034.

Apply this file and autocomplete for places will work.

Changes below:

@@ -3039,12 +3039,44 @@
" from {$TBLPREFIX}places p1".
" join {$TBLPREFIX}places p2 ON (p1.p_parent_id=p2.p_id AND p1.p_file=p2.p_file)".
" join {$TBLPREFIX}places p3 ON (p2.p_parent_id=p3.p_id AND p2.p_file=p3.p_file)".
" join {$TBLPREFIX}places p4 ON (p3.p_parent_id=p4.p_id AND p3.p_file=p4.p_file)".
" join {$TBLPREFIX}places p5 ON (p4.p_parent_id=p5.p_id AND p4.p_file=p5.p_file)".
" where p1.p_place like ? and p5.p_parent_id=0 AND p1.p_file=?";
+ break;
+ case 'pgsql':
+ $sql=
+ "select p1.p_place".
+ " from {$TBLPREFIX}places p1".
+ " where p1.p_place ilike ? and p1.p_parent_id=0 AND p1.p_file=?".
+ " union ".
+ "select p1.p_place || ', ' || p2.p_place".
+ " from {$TBLPREFIX}places p1".
+ " join {$TBLPREFIX}places p2 ON (p1.p_parent_id=p2.p_id AND p1.p_file=p2.p_file)".
+ " where p1.p_place ilike ? and p2.p_parent_id=0 AND p1.p_file=?".
+ " union ".
+ "select p1.p_place || ', ' || p2.p_place || ', ' || p3.p_place".
+ " from {$TBLPREFIX}places p1".
+ " join {$TBLPREFIX}places p2 ON (p1.p_parent_id=p2.p_id AND p1.p_file=p2.p_file)".
+ " join {$TBLPREFIX}places p3 ON (p2.p_parent_id=p3.p_id AND p2.p_file=p3.p_file)".
+ " where p1.p_place ilike ? and p3.p_parent_id=0 AND p1.p_file=?".
+ " union ".
+ "select p1.p_place || ', ' || p2.p_place || ', ' || p3.p_place || ', ' || p4.p_place".
+ " from {$TBLPREFIX}places p1".
+ " join {$TBLPREFIX}places p2 ON (p1.p_parent_id=p2.p_id AND p1.p_file=p2.p_file)".
+ " join {$TBLPREFIX}places p3 ON (p2.p_parent_id=p3.p_id AND p2.p_file=p3.p_file)".
+ " join {$TBLPREFIX}places p4 ON (p3.p_parent_id=p4.p_id AND p3.p_file=p4.p_file)".
+ " where p1.p_place ilike ? and p4.p_parent_id=0 AND p1.p_file=?".
+ " union ".
+ "select p1.p_place || ', ' || p2.p_place || ', ' || p3.p_place || ', ' || p4.p_place || ', ' || p5.p_place".
+ " from {$TBLPREFIX}places p1".
+ " join {$TBLPREFIX}places p2 ON (p1.p_parent_id=p2.p_id AND p1.p_file=p2.p_file)".
+ " join {$TBLPREFIX}places p3 ON (p2.p_parent_id=p3.p_id AND p2.p_file=p3.p_file)".
+ " join {$TBLPREFIX}places p4 ON (p3.p_parent_id=p4.p_id AND p3.p_file=p4.p_file)".
+ " join {$TBLPREFIX}places p5 ON (p4.p_parent_id=p5.p_id AND p4.p_file=p5.p_file)".
+ " where p1.p_place ilike ? and p5.p_parent_id=0 AND p1.p_file=?";
break;
default:
$sql=
"select p1.p_place".
" from {$TBLPREFIX}places p1".
" where p1.p_place like ? and p1.p_parent_id=0 AND p1.p_file=?".

Discussion

  • Gerry Kroll

    Gerry Kroll - 2013-04-13

    This patch applies to "includes/functions/functionsdb.php", but which version?

     
  • dyspd

    dyspd - 2013-04-14

    This was from trunk in SVN, which does not look like it has been touched for over two years.

     
  • Gerry Kroll

    Gerry Kroll - 2013-04-15

    I'll merge your code into that for sqlite, which doesn't have a concat() function either.

     
  • Gerry Kroll

    Gerry Kroll - 2013-04-15

    Somewhat improved version of patched "functions_db.php"

     
  • Gerry Kroll

    Gerry Kroll - 2013-04-15

    Hi:
    I've improved (?) the code you supplied. Please try the attached file and let me know whether this works for you.

    Unzip the "functions_db.zip" file, and copy the result to the PGV install directory.

     
  • dyspd

    dyspd - 2013-04-15

    I though about doing that but as I had it working with the ugly code I left it at that.
    Yes it works fine!

     
  • Gerry Kroll

    Gerry Kroll - 2013-04-16

    File added to Subversion

     
  • Gerry Kroll

    Gerry Kroll - 2013-04-16

    Updated file added to SVN 7092.

     
  • Gerry Kroll

    Gerry Kroll - 2013-04-16
    • assigned_to: nobody --> canajun2eh
    • status: open --> closed-accepted
     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks