From 1e7aeb0c06f5dbde5ad182fac65932e671afabc2 Mon Sep 17 00:00:00 2001
From: Nicolas Boisselier <nicolas.boisselier@gmail.com>
Date: Tue, 12 Dec 2017 21:29:16 +0000
Subject: [PATCH] lib/php/db/table.php

---
 lib/php/db.php             | 12 ++++----
 lib/php/db/table.php       | 58 +++++++++++++++++++++++++++++++++++++-
 lib/php/db/types/pgsql.php |  8 ++++++
 www/dbq/dbq.php            |  5 ++++
 4 files changed, 76 insertions(+), 7 deletions(-)

diff --git a/lib/php/db.php b/lib/php/db.php
index 45acaf4e..4bbc159e 100644
--- a/lib/php/db.php
+++ b/lib/php/db.php
@@ -472,12 +472,12 @@ class Db extends nb {
     return $this->unvar($db_type[$key]);
   }
 
-  public function type($key=null,$die=false,$type=null) {
-    if (empty($type)) $type = $this->type;
-    if (!empty($key) and $key == 'type') return $type;
-
-    return $this->conf_type($key,$die);
-  }
+# NB 12.12.17   public function type($key=null,$die=false,$type=null) {
+# NB 12.12.17     if (empty($type)) $type = $this->type;
+# NB 12.12.17     if (!empty($key) and $key == 'type') return $type;
+# NB 12.12.17 
+# NB 12.12.17     return $this->conf_type($key,$die);
+# NB 12.12.17   }
 
   public function table_exist($table) {
   # NB 02.11.16: TODO NEVEW USED 
diff --git a/lib/php/db/table.php b/lib/php/db/table.php
index 4129e24c..4abbeb24 100644
--- a/lib/php/db/table.php
+++ b/lib/php/db/table.php
@@ -1581,7 +1581,57 @@ Class Table extends nb {
     return $this->db()->sql_name($value === null ? $this->name : $value);
   }
 
+  private function _post2sql($post) {
+    $keys = [];
+    $keys_values = [];
+    $fields = [];
+    $fields_values = [];
+    foreach ($this->fields() as $name => $field) {
+
+      if ($field->extras) continue;
+
+      $value = $post[$this->field_preff.$name];
+
+      if ($field->key) {
+        $keys[] = $name;
+
+        if (!isset($post[$this->key_preff.$name])) {
+          if ($field->key) $this->bye("Missing `$name`!");
+          continue;
+        }
+
+        if (isset($post[$this->key_preff.$name])) {
+          $keys_values[] = $post[$this->key_preff.$name];
+
+        } else {
+          $keys_values[] = $value;
+        }
+
+      } else {
+
+        $fields[] = $name;
+        $fields_values[] = $value;
+
+      }
+		}
+
+		return [
+    	'keys' => $keys,
+    	'keys_values' => $keys_values,
+    	'fields' => $fields,
+    	'fields_values' => $fields_values,
+    	'all_keys' => array_combine($keys,$keys_values),
+    	'all_fields' => array_combine($fields,$fields_values),
+		];
+  }
+
   public function replace($hvalues,&$info=[]) {
+		# NB 12.12.17: When REPLACE is not supported where call a personalized function 
+		$fct = $this->db()->conf_type('replace_insert');
+		if (!empty($fct)) {
+			$info['sql_fct'] = $fct;
+			return $this->insert($hvalues,$info);
+		}
     return $this->insert($hvalues,$info,'REPLACE');
   }
 
@@ -1609,6 +1659,11 @@ Class Table extends nb {
       $insert_word.' INTO '. $this->sql_name() . ' (' . join(',',array_values($sql_names)).')'
       .' VALUES (' . join(',',ar_map('":$a"',array_keys($fields))) . ')'
     ;
+
+		if (!empty($info['sql_fct'])) {
+			$fct = $info['sql_fct'];
+			$fct($sql,$this);
+		}
     $info['sql'] = $sql;
 
     if (!($query = $this->db()->conn->prepare($sql))) {
@@ -1761,7 +1816,8 @@ Class Table extends nb {
     }
 
 		# NB 07.12.17: Add LIMIT 1. If a table as not primary key when want to delete only on record
-    $sql = 'DELETE FROM ' . $this->sql_name() . $where . ($this->db()->type == 'pgsql' ? '' : ' LIMIT 1');
+# NB 12.12.17     $sql = 'DELETE FROM ' . $this->sql_name() . $where . ($this->db()->type == 'pgsql' ? '' : ' LIMIT 1');
+    $sql = 'DELETE FROM ' . $this->sql_name() . $where . ($this->db()->conf_type('delete_no_limit') ? '' : ' LIMIT 1');
     $info['sql'] = $sql;
 
     if (self::p('debug')) {
diff --git a/lib/php/db/types/pgsql.php b/lib/php/db/types/pgsql.php
index f267337c..4f423aa5 100644
--- a/lib/php/db/types/pgsql.php
+++ b/lib/php/db/types/pgsql.php
@@ -1,5 +1,13 @@
 <?php
 $DB_TYPES['pgsql'] = array (
+'delete_no_limit' => true,
+'replace_insert' => function(&$sql,&$table) {
+	$keys = $table->fields_keys($others);
+	#if ($table->p('debug')) {
+	$sql .= ' ON CONFLICT ('.join(',',array_keys($keys)).') DO UPDATE ';
+  $sql .= ' SET '   . join(',',$table->ar_map('"$a=:$a"',array_keys($others)));
+	#$info['debug'] = [$keys,$others];
+},
 'extra_where' => 'denorm',
 'regexp' => '~',
 # NB 04.12.17: Overwrite db->quote because of bugs with INSERT INTO 
diff --git a/www/dbq/dbq.php b/www/dbq/dbq.php
index 1da6588f..a10b7554 100644
--- a/www/dbq/dbq.php
+++ b/www/dbq/dbq.php
@@ -1135,6 +1135,11 @@ EOF;
 			#header('Location: '.$this->table->base.'/');
 			$this->page($info);
 
+		} elseif ($action == 'replace' and $this->perm >= self::WRITE) {
+			if (!$this->table->replace($_POST,$info)) $this->error('replace: '.print_r($info,true));
+			header('Location: '.$this->table->base.'/');
+			$this->page($info);
+
 		} elseif ($action == 'rm' and $this->perm >= self::DELETE) {
 			if (!$this->table->delete($_POST,$info)) $this->error('rm: '.print_r($info,true));
 			header('Location: '.$this->table->base.'/');
-- 
2.47.3