From 3040883f2aee8403332a5ff6c8f148c1cbb56290 Mon Sep 17 00:00:00 2001
From: Nicolas Boisselier <nicolas.boisselier@semantico.com>
Date: Thu, 30 Jun 2016 11:29:25 +0100
Subject: [PATCH] dump sql_replace per db

---
 etc/dbs/ui.php             | 10 ++++++++++
 lib/mysql/functions.sql    | 13 +++++++++++++
 lib/php/db.php             |  2 +-
 lib/php/db/table.php       |  4 +++-
 lib/php/db/types/mysql.php |  2 +-
 5 files changed, 28 insertions(+), 3 deletions(-)

diff --git a/etc/dbs/ui.php b/etc/dbs/ui.php
index b2c4518c..575d2a87 100644
--- a/etc/dbs/ui.php
+++ b/etc/dbs/ui.php
@@ -3,6 +3,16 @@ $CONF['_ui'] = array(
   'title' => 'Semantico UI',
   'pdo' => 'sqlite:/opt/semantico/product/releases/sem_ui/db/semantico.db',
   'default_table' => 'node',
+
+  'sql_replace' => function($sql) {
+    $r = [];
+
+    $r["CAST(started as char(30)),duration||' seconds'"] = "from_unixtime(UNIX_TIMESTAMP(started)+duration)";
+
+    $sql = str_replace(array_keys($r),$r,$sql);
+    return $sql;
+  },
+
   'tables' => array(
 
     'aws_az' => array(
diff --git a/lib/mysql/functions.sql b/lib/mysql/functions.sql
index 90b96a4f..29a7d17f 100644
--- a/lib/mysql/functions.sql
+++ b/lib/mysql/functions.sql
@@ -1,4 +1,17 @@
 DELIMITER $$
+
+DROP FUNCTION IF EXISTS strftime$$
+CREATE FUNCTION strftime (f text,str text)
+RETURNS text DETERMINISTIC
+BEGIN
+  IF  ( f = '%s' )
+  THEN
+    RETURN UNIX_TIMESTAMP(str);
+  ELSE
+    RETURN DATE_FORMAT(str,f);
+  END IF;
+END$$
+
 DROP FUNCTION IF EXISTS to_char$$
 CREATE FUNCTION to_char (f text,s text)
 RETURNS text DETERMINISTIC
diff --git a/lib/php/db.php b/lib/php/db.php
index c12a3635..00edc87f 100644
--- a/lib/php/db.php
+++ b/lib/php/db.php
@@ -631,7 +631,7 @@ class Db extends nb {
       if (!empty($name) and !preg_match('/'.$name.'/',$t->name)) continue;
 
       if ($insert) {
-        echo "\n-- Table: ".$t->name."\n";
+        echo "\n-- ".strtoupper($t->type).": ".$t->name."\n";
       }
       #echo 'DROP TABLE IF EXISTS '.$t->sql_name().';'.NB_EOL;
       echo 'DROP '.strtoupper($t->type).' IF EXISTS '.$t->sql_name().';'.NB_EOL;
diff --git a/lib/php/db/table.php b/lib/php/db/table.php
index 6165a7a0..63855767 100644
--- a/lib/php/db/table.php
+++ b/lib/php/db/table.php
@@ -123,7 +123,9 @@ Class Table extends nb {
 
     // String replace function
     $sql_replace_fct = $this->db()->type('sql.replace',false);
-    $sql_replace = function($sql) use ($sql_replace_fct) {
+    $db_sql_replace_fct = empty($this->db()->sql_replace) ? '' : $this->db()->sql_replace;
+    $sql_replace = function($sql) use ($sql_replace_fct,$db_sql_replace_fct) {
+      if ($db_sql_replace_fct) $sql = $db_sql_replace_fct($sql);
       return $sql_replace_fct ? $sql_replace_fct($sql) : $sql;
     };
 
diff --git a/lib/php/db/types/mysql.php b/lib/php/db/types/mysql.php
index 9dd85f6a..27de8951 100644
--- a/lib/php/db/types/mysql.php
+++ b/lib/php/db/types/mysql.php
@@ -6,7 +6,7 @@ $DB_TYPES['mysql'] = array (
 
 'exec' => [
   'SET NAMES '.str_replace('utf-8','utf8',strtolower(Db::$encoding)),
-  (Db::p('db.type') ? 'SET sql_mode=PIPES_AS_CONCAT' : null),
+  (Db::p('db.type') ? 'SET SESSION sql_mode=PIPES_AS_CONCAT' : null),
 ],
 'extra_where' => 'having',
 
-- 
2.47.3