diff options
author | plegall <plg@piwigo.org> | 2011-04-16 21:22:09 +0000 |
---|---|---|
committer | plegall <plg@piwigo.org> | 2011-04-16 21:22:09 +0000 |
commit | 8098f24b047e79a7281bb2ac1b5cbd50fab22389 (patch) | |
tree | 9067ebe69d87c95d346e7bdeb1cc1ce2b109cf42 /include/dblayer/functions_pgsql.inc.php | |
parent | 3594e552d73ff20d4d8be119d38b29c0ae55ffdd (diff) |
bug 1818 fixed: fix "select distinct" queries for PostgreSQL. Patch by leloupv
git-svn-id: http://piwigo.org/svn/branches/2.2@10430 68402e56-0260-453c-a942-63ccdbb3a9ee
Diffstat (limited to 'include/dblayer/functions_pgsql.inc.php')
-rw-r--r-- | include/dblayer/functions_pgsql.inc.php | 73 |
1 files changed, 72 insertions, 1 deletions
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<br>".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<br>".pg_last_error()); + } else { ($result = pg_query($query)) or die($query."\n<br>".pg_last_error()); |