From 562f391c70a369b96b418868bbdfc34b426c1d2b Mon Sep 17 00:00:00 2001 From: plegall Date: Sat, 16 Apr 2011 21:22:39 +0000 Subject: merge r10430 from branch 2.2 to trunk bug 1818 fixed: fix "select distinct" queries for PostgreSQL. Patch by leloupv git-svn-id: http://piwigo.org/svn/trunk@10431 68402e56-0260-453c-a942-63ccdbb3a9ee --- include/dblayer/functions_pgsql.inc.php | 73 ++++++++++++++++++++++++++++++++- 1 file changed, 72 insertions(+), 1 deletion(-) (limited to 'include/dblayer') diff --git a/include/dblayer/functions_pgsql.inc.php b/include/dblayer/functions_pgsql.inc.php index dbbe3d52d..67c42eeb1 100644 --- a/include/dblayer/functions_pgsql.inc.php +++ b/include/dblayer/functions_pgsql.inc.php @@ -91,7 +91,8 @@ function pwg_query($query) { global $conf,$page,$debug,$t2; - $replace_pattern = '`REPLACE INTO\s(\S*)\s*([^)]*\))\s*VALUES\(([^,]*),(.*)\)\s*`mi'; + $replace_pattern = '`REPLACE INTO\s(\S*)\s*([^)]*\))\s*VALUES\(([^,]*),(.*)\)\s*`mi'; + $select_distinct_pattern = '/SELECT\s+DISTINCT\s*(\S[^;]*\S)\s*(FROM[^(;]+WHERE[^;]+)\s+ORDER\s+BY\s+([^;]*\S)\s*;?/i'; $start = get_moment(); @@ -117,6 +118,76 @@ INSERT INTO '.$matches[1].' } ( $result = pg_query($query)) or die($query."\n
".pg_last_error()); } + elseif (preg_match($select_distinct_pattern, $query, $matches)) + { + $select_fields_string=''; + $distinct_fields_string=''; + $orderby_fields_string=''; + + foreach (preg_split( '/\s*,\s*/', $matches[1]) as $field) + { + $split_field = preg_split( '/\s*AS\s*/i', $field); + if (isset($split_field[1])) + { + $distinct_fields[ $split_field[1] ] = $field; + } + else + { + $distinct_fields[ $field ] = $field; + } + } + + foreach (preg_split( '/\s*,\s*/', $matches[3]) as $field) + { + $kv = preg_split( '/\s+/', $field ); + $orderby_fields[ $kv[0] ] = $kv[1]; + } + + foreach ($distinct_fields as $as_field => $field) + { + if ($distinct_fields_string) + { + $distinct_fields_string=$distinct_fields_string.', '; + } + + $distinct_fields_string=$distinct_fields_string.$as_field; + + if ($select_fields_string) + { + $select_fields_string=$select_fields_string.', '; + } + + $select_fields_string=$select_fields_string.$field; + + if ($orderby_fields_string) + { + $orderby_fields_string=$orderby_fields_string.', '; + } + + $orderby_fields_string=$orderby_fields_string.$as_field.' '; + + if (isset($orderby_fields[$as_field])) + { + $orderby_fields_string=$orderby_fields_string.$orderby_fields[$as_field]; + unset($orderby_fields[$as_field]); + } + else + { + $orderby_fields_string=$orderby_fields_string.'ASC'; + } + } + + foreach ($orderby_fields as $field => $order) + { + $orderby_fields_string=$orderby_fields_string.', '.$field.' '.$order; + } + + $query = ' +SELECT DISTINCT ON ('.$distinct_fields_string.') '.$select_fields_string.' + '.$matches[2].' + ORDER BY '.$orderby_fields_string; + ($result = pg_query($query)) or die($query."\n
".pg_last_error()); + } else { ($result = pg_query($query)) or die($query."\n
".pg_last_error()); -- cgit v1.2.3