#590 Patched file for bug 3527034

closed-accepted
Gerry Kroll
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"

     
    Attachments
  • 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