#969337 - Soundex search - Code not in Soundex table

4 files changed, 115 insertions, 16 deletions

diff --git a/includes/db_schema/db_schema_25_26.php b/includes/db_schema/db_schema_25_26.php
index 9a0a437812..caea80200b 100644
--- a/includes/db_schema/db_schema_25_26.php
+++ b/includes/db_schema/db_schema_25_26.php
@@ -1,5 +1,5 @@
 <?php
-// Update the database schema from version 24-25
+// Update the database schema from version 25-26
 // - delete unused settings and update indexes
 //
 // The script should assume that it can be interrupted at
diff --git a/includes/db_schema/db_schema_26_27.php b/includes/db_schema/db_schema_26_27.php
new file mode 100644
index 0000000000..b5c917ff37
--- /dev/null
+++ b/includes/db_schema/db_schema_26_27.php
@@ -0,0 +1,86 @@
+<?php
+// Update the database schema from version 26-27
+// - delete unused settings and update indexes
+//
+// The script should assume that it can be interrupted at
+// any point, and be able to continue by re-running the script.
+// Fatal errors, however, should be allowed to throw exceptions,
+// which will be caught by the framework.
+// It shouldn't do anything that might take more than a few
+// seconds, for systems with low timeout values.
+//
+// webtrees: Web based Family History software
+// Copyright (C) 2013 Greg Roach
+//
+// This program is free software; you can redistribute it and/or modify
+// it under the terms of the GNU General Public License as published by
+// the Free Software Foundation; either version 2 of the License, or
+// (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+
+if (!defined('WT_WEBTREES')) {
+	header('HTTP/1.0 403 Forbidden');
+	exit;
+}
+
+// Earlier versions of webtrees put quote marks round soundex codes.
+// These are harmless, but clean them up for consistency.
+self::exec(
+	"UPDATE `##name` SET" .
+	" n_soundex_givn_std = TRIM('''' FROM n_soundex_givn_std)," .
+	" n_soundex_surn_std = TRIM('''' FROM n_soundex_surn_std)," .
+	" n_soundex_givn_dm  = TRIM('''' FROM n_soundex_givn_dm )," .
+	" n_soundex_surn_dm  = TRIM('''' FROM n_soundex_surn_dm )"
+);
+
+// Earlier versions of webtrees added zero codes for names without phonetic content.
+// These are harmless, but clean them up for consistency.
+self::exec(
+	"UPDATE `##name` SET" .
+	" n_soundex_givn_std = REPLACE(n_soundex_givn_std, '0000:',   '')," .
+	" n_soundex_surn_std = REPLACE(n_soundex_surn_std, '0000:',   '')," .
+	" n_soundex_givn_dm  = REPLACE(n_soundex_givn_dm,  '000000:', '')," .
+	" n_soundex_surn_dm  = REPLACE(n_soundex_surn_dm,  '000000:', '')"
+);
+self::exec(
+	"UPDATE `##name` SET" .
+	" n_soundex_givn_std = REPLACE(n_soundex_givn_std, ':0000',   '')," .
+	" n_soundex_surn_std = REPLACE(n_soundex_surn_std, ':0000',   '')," .
+	" n_soundex_givn_dm  = REPLACE(n_soundex_givn_dm,  ':000000', '')," .
+	" n_soundex_surn_dm  = REPLACE(n_soundex_surn_dm,  ':000000', '')"
+);
+self::exec(
+	"UPDATE `##name` SET" .
+	" n_soundex_givn_std = NULLIF(n_soundex_givn_std, '0000'  )," .
+	" n_soundex_surn_std = NULLIF(n_soundex_surn_std, '0000'  )," .
+	" n_soundex_givn_dm  = NULLIF(n_soundex_givn_dm,  '000000')," .
+	" n_soundex_surn_dm  = NULLIF(n_soundex_surn_dm,  '000000')"
+);
+
+self::exec(
+	"UPDATE `##places` SET" .
+	" p_std_soundex = REPLACE(p_std_soundex, '0000:',   '')," .
+	" p_dm_soundex  = REPLACE(p_dm_soundex,  '000000:', '')"
+);
+self::exec(
+	"UPDATE `##places` SET" .
+	" p_std_soundex = REPLACE(p_std_soundex, ':0000',   '')," .
+	" p_dm_soundex  = REPLACE(p_dm_soundex,  ':000000', '')"
+);
+self::exec(
+	"UPDATE `##places` SET" .
+	" p_std_soundex = NULLIF(p_std_soundex, '0000'  )," .
+	" p_dm_soundex  = NULLIF(p_dm_soundex,  '000000')"
+);
+
+
+// Update the version to indicate success
+WT_Site::preference($schema_name, $next_version);
diff --git a/includes/functions/functions_db.php b/includes/functions/functions_db.php
index 06f90854c2..a742c1a91e 100644
--- a/includes/functions/functions_db.php
+++ b/includes/functions/functions_db.php
@@ -281,43 +281,56 @@ function search_indis_soundex($soundex, $lastname, $firstname, $place, $geds) {
 	$sql.=' WHERE i_file IN ('.implode(',', $geds).')';
 	switch ($soundex) {
 	case 'Russell':
-		$givn_sdx=explode(':', WT_Soundex::soundex_std($firstname));
-		$surn_sdx=explode(':', WT_Soundex::soundex_std($lastname));
-		$plac_sdx=explode(':', WT_Soundex::soundex_std($place));
-		$field='std';
+		$givn_sdx = WT_Soundex::soundex_std($firstname);
+		$surn_sdx = WT_Soundex::soundex_std($lastname);
+		$plac_sdx = WT_Soundex::soundex_std($place);
+		$field    = 'std';
 		break;
 	default:
 	case 'DaitchM':
-		$givn_sdx=explode(':', WT_Soundex::soundex_dm($firstname));
-		$surn_sdx=explode(':', WT_Soundex::soundex_dm($lastname));
-		$plac_sdx=explode(':', WT_Soundex::soundex_dm($place));
-		$field='dm';
+		$givn_sdx = WT_Soundex::soundex_dm($firstname);
+		$surn_sdx = WT_Soundex::soundex_dm($lastname);
+		$plac_sdx = WT_Soundex::soundex_dm($place);
+		$field    = 'dm';
 		break;
 	}
+
+	// Nothing to search for?  Return nothing.
+	if (!$givn_sdx && !$surn_sdx && !$plac_sdx) {
+		return array();
+	}
+
+	$sql_args = array();
 	if ($firstname && $givn_sdx) {
+		$givn_sdx = explode(':', $givn_sdx);
 		foreach ($givn_sdx as $k=>$v) {
-			$givn_sdx[$k]="n_soundex_givn_{$field} LIKE ".WT_DB::quote("%{$v}%");
+			$givn_sdx[$k] = "n_soundex_givn_{$field} LIKE CONCAT('%', ?, '%')";
+			$sql_args[]   = $v;
 	}
 		$sql.=' AND ('.implode(' OR ', $givn_sdx).')';
 		}
 	if ($lastname && $surn_sdx) {
+		$surn_sdx = explode(':', $surn_sdx);
 		foreach ($surn_sdx as $k=>$v) {
-			$surn_sdx[$k]="n_soundex_surn_{$field} LIKE ".WT_DB::quote("%{$v}%");
+			$surn_sdx[$k] = "n_soundex_surn_{$field} LIKE CONCAT('%', ?, '%')";
+			$sql_args[]   = $v;
 		}
 		$sql.=' AND ('.implode(' OR ', $surn_sdx).')';
 			}
 	if ($place && $plac_sdx) {
+		$plac_sdx = explode(':', $plac_sdx);
 		foreach ($plac_sdx as $k=>$v) {
-			$plac_sdx[$k]="p_{$field}_soundex LIKE ".WT_DB::quote("%{$v}%");
+			$plac_sdx[$k] = "p_{$field}_soundex LIKE CONCAT('%', ?, '%')";
+			$sql_args[]   = $v;
 		}
-		$sql.=' AND ('.implode(' OR ', $plac_sdx).')';
+		$sql .= ' AND (' . implode(' OR ', $plac_sdx) . ')';
 	}
 
 	// Group results by gedcom, to minimise switching between privacy files
-	$sql.=' ORDER BY gedcom_id';
+	$sql .= ' ORDER BY gedcom_id';
 
 	$list=array();
-	$rows=WT_DB::prepare($sql)->fetchAll();
+	$rows=WT_DB::prepare($sql)->execute($sql_args)->fetchAll();
 	$GED_ID=WT_GED_ID;
 	foreach ($rows as $row) {
 		// Switch privacy file if necessary
diff --git a/includes/session.php b/includes/session.php
index 7b70ea393b..a131154a83 100755
--- a/includes/session.php
+++ b/includes/session.php
@@ -71,7 +71,7 @@ define('WT_DEBUG_LANG', false);
 define('WT_ERROR_LEVEL', 2); // 0=none, 1=minimal, 2=full
 
 // Required version of database tables/columns/indexes/etc.
-define('WT_SCHEMA_VERSION', 26);
+define('WT_SCHEMA_VERSION', 27);
 
 // Regular expressions for validating user input, etc.
 define('WT_MINIMUM_PASSWORD_LENGTH', 6);