aboutsummaryrefslogtreecommitdiffstats
path: root/include/dblayer/functions_pgsql.inc.php
diff options
context:
space:
mode:
authorplegall <plg@piwigo.org>2011-04-16 21:22:09 +0000
committerplegall <plg@piwigo.org>2011-04-16 21:22:09 +0000
commit8098f24b047e79a7281bb2ac1b5cbd50fab22389 (patch)
tree9067ebe69d87c95d346e7bdeb1cc1ce2b109cf42 /include/dblayer/functions_pgsql.inc.php
parent3594e552d73ff20d4d8be119d38b29c0ae55ffdd (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 '')
-rw-r--r--include/dblayer/functions_pgsql.inc.php73
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());