[Noalyss-commit] [noalyss] 10/16: Data_SQL and Table_Data_SQL add virtual column, to allow transformation of columns in SQL, faster than transforming in PHP afterward

[dwm]

sparkyx pushed a commit to branch unstable
in repository noalyss.

commit bfdae33f16e346c3c9aa0d7147bdf51c483ad42f
Author: sparkyx <danydb@noalyss.eu>
AuthorDate: Wed May 21 11:15:13 2025 +0200

    Data_SQL and Table_Data_SQL add virtual column, to allow  transformation
    of columns in SQL, faster than transforming in PHP afterward
---
 include/lib/data_sql.class.php               |  97 +++++++++++--
 include/lib/table_data_sql.class.php         |  53 +++++--
 scenario/LIB/table_data_sql.php              | 153 ++++++++++++++++++++
 unit-test/include/lib/Table_Data_SQLTest.php | 204 +++++++++++++++++++++++++++
 unit-test/include/lib/noalyss_sqlTest.php    |   2 +-
 5 files changed, 480 insertions(+), 29 deletions(-)

diff --git a/include/lib/data_sql.class.php b/include/lib/data_sql.class.php
index 7b5e86d86..03e336efc 100644
--- a/include/lib/data_sql.class.php
+++ b/include/lib/data_sql.class.php
@@ -104,10 +104,10 @@ abstract class Data_SQL
    var $date_format;    //! defaullt date format
    var $default;
    var $table;
-
+   protected $a_virtual_col;
     public function __toString(): string
     {
-        $ret="values ";
+        $ret=" members: ";
         foreach ($this->name as $name) {
             $ret.="[ $name => {$this->$name} ]";
         }
@@ -116,6 +116,7 @@ abstract class Data_SQL
         $ret.="| default ".print_r($this->default,true);
         $ret.="| primary key ".$this->primary_key;
         $ret.="| date_format ".$this->date_format;
+        $ret.="| a_virtual_col".var_export($this->a_virtual_col,true);
         return $ret;
     }
 
@@ -125,6 +126,7 @@ abstract class Data_SQL
         $this->cn=$p_cn;
         $pk=$this->primary_key;
         $this->$pk=$p_id;
+         $this->a_virtual_col=array();
        // check that the definition is correct
        if (count($this->name) != count($this->type) ){
                throw new Exception (__FILE__." $this->table Cannot 
instantiate");
@@ -156,6 +158,25 @@ abstract class Data_SQL
         else
             $this->update();
     }
+    /**
+     * @brief return array of virtual cols (alias calculated, formatted cols)
+     * @return array
+     */
+    public function get_a_virtual_col() {
+        return $this->a_virtual_col;
+    }
+    /**
+     * @brief add a virtual column (formatted column, sum of 2 col, ...)
+     * @param $col_name (string) name of the column , will be use in get , getp
+     * @param $sql_expression (string) SQL Expression for the column 
+     * like "to_char(col1,'DD.MM.YY HH24:MI:SS')", col1+col2, ...
+     * @note sql expression and col_name must be valid , there is no futher 
+     * check
+     * @returns void
+     */
+    public function set_virtual_col($col_name,$sql_expression) {
+        $this->a_virtual_col[$col_name]=$sql_expression;
+    }
     /**
      *@brief get the value thanks the colum name and not the alias (name). 
      *@see getp
@@ -165,7 +186,10 @@ abstract class Data_SQL
         if (array_key_exists($cols, $this->type)) {
             return $this->$cols;
         }
-        else
+         if (array_key_exists($cols, $this->a_virtual_col))
+        {
+             return $this->$cols;
+        }
              throw new \Exception (" unknow cols [$cols] 
=".$this,EXC_DATA_SQL);
     }
 
@@ -197,7 +221,11 @@ abstract class Data_SQL
         if (array_key_exists($cols, $this->type)) {
             return $this->$cols;
         }
-        
+        if (array_key_exists($cols, $this->a_virtual_col))
+        {
+             return $this->$cols;
+        }
+
         throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
     }
 
@@ -225,15 +253,24 @@ abstract class Data_SQL
         if (array_key_exists($cols, $this->type))    {
             $this->$cols=$p_value;
             return $this;
-        }        else
+        }        
+          if (array_key_exists($cols, $this->a_virtual_col))
+        {
+             $this->$cols=$p_value;
+             return $this;
+        }
+        
            throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
     }
     public function __get($cols) {
          if (array_key_exists($cols, $this->type)) {
             return $this->$cols;
         }
-        else
-           throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
+         if (array_key_exists($cols, $this->a_virtual_col))
+        {
+             return $this->$cols;
+        }
+        throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
     }
     abstract function insert();
 
@@ -300,7 +337,7 @@ abstract class Data_SQL
     }
 
     /**
-     * Transform an array into object
+     * @brief Transform an array into object
      * @param type $p_array
      * @return object
      */
@@ -333,17 +370,34 @@ abstract class Data_SQL
             $nkey=$prefix.$key;
             $array[$key]=$this->$key;
         }
+        if ( ! empty ($this->a_virtual_col )) {
+            $a_column= array_keys($this->a_virtual_col);
+            foreach( $a_column as $column){
+                $array[$column]=$this->a_virtual_col[$column];
+            }
+        }
         return $array;
     }
 
     /**
-     * @brief turns a row fetched from the DB into a SQL object in updating 
all his attribute
-     * @param $p_array
+     * @brief update the data member of current object with the value from the 
array.
+     * includes the virtual column, usefull if need to update several columns 
in once
+     * @param $p_array (array) associative key = column_vale, value new value 
for this col.
      * @return void
      */
     public function to_row($p_array) {
         foreach ($this->name as $name) {
-            $this->$name=$p_array[$name];
+            if (isset ($p_array[$name])) {
+                $this->$name=$p_array[$name];
+            }
+        }
+         if ( ! empty ($this->a_virtual_col )) {
+                $a_column= array_keys($this->a_virtual_col);
+                foreach( $a_column as $column){
+                    if ( isset ($p_array[$column])) {
+                    $this->$column = $p_array[$column];
+                    }
+                }
         }
     }
     /**
@@ -357,8 +411,8 @@ abstract class Data_SQL
     abstract  function seek($cond='', $p_array=null);
 
     /**
-     * @brief get_seek return the next object, the return of the query must 
have all the column
-     * of the object
+     * @brief get_seek return the next object, the return of the query must 
have 
+     * all the column of the object including the virtual columns
      * @param $p_ret is the return value of an exec_sql
      * @param $idx is the index
      * @see seek
@@ -367,7 +421,18 @@ abstract class Data_SQL
     public function next($ret, $i)
     {
         $array=$this->cn->fetch_array($ret, $i);
-        return $this->from_array($array);
+        $this->from_array($array);
+        if ( ! empty ($this->a_virtual_col )) {
+                $a_column= array_keys($this->a_virtual_col);
+                foreach( $a_column as $column){
+                    if ( isset  ($array[$column] )) {
+                        $this->$column = $array[$column];
+                    } else {
+                        $this->$column =   null;
+                    }
+                }
+        }
+        return $this;
     }
 
     /**
@@ -397,7 +462,9 @@ abstract class Data_SQL
         $a_return=array();
         for ($i=0; $i<$max; $i++)
         {
-            $a_return[$i]=clone $this->next($ret, $i);
+            $x=clone $this->next($ret, $i);
+           
+            $a_return[$i]=$x;
         }
         return $a_return;
     }
diff --git a/include/lib/table_data_sql.class.php 
b/include/lib/table_data_sql.class.php
index 0c4737860..f81d4c0ed 100644
--- a/include/lib/table_data_sql.class.php
+++ b/include/lib/table_data_sql.class.php
@@ -88,15 +88,29 @@
 #[AllowDynamicProperties]
 abstract class Table_Data_SQL extends Data_SQL
 {
-
+    
     
     function __construct($p_cn, $p_id=-1)
     {
         parent::__construct($p_cn, $p_id);
-        
+       
     }
+      public function __toString(): string
+    {
+        $ret=" members : ";
+        foreach ($this->name as $name) {
+            $ret.="[ $name => {$this->$name} ]";
+        }
 
+        $ret.="| type ".var_export($this->type,true);
+        $ret.="| default ".var_export($this->default,true);
+        $ret.="| primary key ".$this->primary_key;
+        $ret.="| date_format ".$this->date_format;
+        $ret.="| a_virtual_col".var_export($this->a_virtual_col,true);
+        return $ret;
+    }
 
+    
     public function insert()
     {
         $this->verify();
@@ -156,6 +170,9 @@ abstract class Table_Data_SQL extends Data_SQL
         return $this;
 
     }
+    /**
+     * @brief update the row but not the column with a default value
+     */
     public function update()
     {
         $this->verify();
@@ -191,22 +208,23 @@ abstract class Table_Data_SQL extends Data_SQL
     */
     public function load():bool
     {
-        $sql=$this->build_query();
+        
         $pk=$this->primary_key;
         // primary cannot be null or empty
         if (trim($this->$pk??"")==="" || $this->$pk===null)  {
             $this->$pk=-1;
             return false;
         }
-       
-        $result=$this->cn->get_array($sql,array ($this->$pk));
+        $sql=$this->build_query();
+        $sql.=" where ".$this->primary_key." = $1";
+        $result=$this->cn->get_row($sql,array ($this->$pk));
         if ($this->cn->count()==0)
         {
             $this->$pk=-1;
             return false;
         }
 
-        foreach ($result[0] as $key=> $value)
+        foreach ($result  as $key=> $value)
         {
             $this->$key=$value;
         }
@@ -224,7 +242,8 @@ abstract class Table_Data_SQL extends Data_SQL
      */
     function seek($cond='', $p_array=null)
     {
-        $sql="select * from ".$this->table."  $cond";
+        $sql=$this->build_query();
+        $sql.="  $cond ";
         $ret=$this->cn->exec_sql($sql, $p_array);
         return $ret;
     }
@@ -270,12 +289,22 @@ abstract class Table_Data_SQL extends Data_SQL
             $sep=",";
         }
         $pk=$this->primary_key;
+        /**
+         * add virtual column
+         */
+        if ( ! empty( $this->a_virtual_col)){
+            $nb_virtual_col=count($this->a_virtual_col);
+            $a_col= array_keys($this->a_virtual_col);
+            for ($x=0;$x<$nb_virtual_col ;$x++) {
+                $col=$a_col[$x];
+                $expr=sprintf("$sep %s as %s ",$this->a_virtual_col[$col], 
$col);
+                $sql.=" $expr ";
+            }
+        }
         $sql.=" from ".$this->table;
-        
-        $sql.=" where ".$this->primary_key." = $1";
-        
         return $sql;
     }
+
     /**
      * @brief Get all the row and use the p_key_code are the key value of 
array. 
      * The key column is usually the primary key or any unique key. 
@@ -316,9 +345,7 @@ abstract class Table_Data_SQL extends Data_SQL
         }
         catch (Exception $exc)
         {
-            echo $exc->getMessage();
-            record_log($exc->getMessage());
-            record_log($exc->getTraceAsString());
+            record_log($exc);
             throw $exc;
         }
         return $a_result;
diff --git a/scenario/LIB/table_data_sql.php b/scenario/LIB/table_data_sql.php
new file mode 100644
index 000000000..680c7c5ee
--- /dev/null
+++ b/scenario/LIB/table_data_sql.php
@@ -0,0 +1,153 @@
+<?php
+//@description: test Table_Data_SQL and Data_SQL for virtual columns
+
+/*
+ *   This file is part of NOALYSS.
+ *
+ *   NOALYSS is free software; you can redistribute it and/or modify
+ *   it under the terms of the GNU General Public License as published by
+ *   the Free Software Foundation; either version 2 of the License, or
+ *   (at your option) any later version.
+ *
+ *   NOALYSS is distributed in the hope that it will be useful,
+ *   but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ *   GNU General Public License for more details.
+ *
+ *   You should have received a copy of the GNU General Public License
+ *   along with NOALYSS; if not, write to the Free Software
+ *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+*/
+// Copyright Author Dany De Bontridder danydb@aevalys.eu 22/10/23
+
+
+/**
+ * @file
+ * @brief Test Table_Data_SQL
+ */
+
+if (!defined('ALLOWED'))
+    die('Appel direct ne sont pas permis');
+
+class FakeData2_SQL extends \Table_Data_SQL {
+
+    function __construct(Database $p_cn, $p_id = -1) {
+        $this->table = "public.jrn";
+        $this->primary_key = "jr_id";
+        /*
+         * List of columns
+         */
+        $this->name = array(
+            "jr_id" => "jr_id"
+            , "jr_def_id" => "jr_def_id"
+            , "jr_montant" => "jr_montant"
+            , "jr_comment" => "jr_comment"
+            , "jr_date" => "jr_date"
+            , "jr_grpt_id" => "jr_grpt_id"
+            , "jr_internal" => "jr_internal"
+            , "jr_tech_date" => "jr_tech_date"
+            , "jr_tech_per" => "jr_tech_per"
+            , "jrn_ech" => "jrn_ech"
+            , "jr_ech" => "jr_ech"
+            , "jr_rapt" => "jr_rapt"
+            , "jr_valid" => "jr_valid"
+            , "jr_opid" => "jr_opid"
+            , "jr_c_opid" => "jr_c_opid"
+            , "jr_pj" => "jr_pj"
+            , "jr_pj_name" => "jr_pj_name"
+            , "jr_pj_type" => "jr_pj_type"
+            , "jr_pj_number" => "jr_pj_number"
+            , "jr_mt" => "jr_mt"
+            , "jr_date_paid" => "jr_date_paid"
+            , "jr_optype" => "jr_optype"
+            , "currency_id" => "currency_id"
+            , "currency_rate" => "currency_rate"
+            , "currency_rate_ref" => "currency_rate_ref"
+        );
+
+        /*
+         * Type of columns
+         */
+        $this->type = array(
+            "jr_id" => "numeric"
+            , "jr_def_id" => "numeric"
+            , "jr_montant" => "numeric"
+            , "jr_comment" => "text"
+            , "jr_date" => "date"
+            , "jr_grpt_id" => "numeric"
+            , "jr_internal" => "text"
+            , "jr_tech_date" => "date"
+            , "jr_tech_per" => "numeric"
+            , "jrn_ech" => "date"
+            , "jr_ech" => "date"
+            , "jr_rapt" => "text"
+            , "jr_valid" => "boolean"
+            , "jr_opid" => "numeric"
+            , "jr_c_opid" => "numeric"
+            , "jr_pj" => "oid"
+            , "jr_pj_name" => "text"
+            , "jr_pj_type" => "text"
+            , "jr_pj_number" => "text"
+            , "jr_mt" => "text"
+            , "jr_date_paid" => "date"
+            , "jr_optype" => "text"
+            , "currency_id" => "numeric"
+            , "currency_rate" => "numeric"
+            , "currency_rate_ref" => "numeric"
+        );
+
+        $this->default = array("jr_id" => "auto");
+
+        $this->date_format = "DD.MM.YYYY";
+        parent::__construct($p_cn, $p_id);
+    }
+}
+
+ global $g_connection;
+ $g_connection=$cn;
+ echo h1("Ajout une colonne virtuelle ");
+$jrn=new FakeData2_SQL($g_connection,911);
+$jrn->set_virtual_col("str_date", " to_char(jr_tech_date,'DD/MM/YY HH24:MI')");
+$jrn->load();
+echo '<pre>'.'$jrn->set_virtual_col("str_date", " 
to_char(jr_tech_date,\'DD/MM/YY HH24:MI\')");'.'</pre>';
+echo p( 'jrn->str_date '.$jrn->str_date);
+echo p( '$jrn->get("str_date") '.$jrn->get("str_date"));
+echo p( '$jrn->getp("str_date")'.$jrn->getp("str_date"));
+
+echo h1("to_array");
+echo '<pre>';
+var_dump($jrn->to_array());
+echo '</pre>';
+$a_result=$jrn->to_array();
+echo h1("to_row ");
+$a_result['str_date']=" CHANGED ";
+$jrn->to_row($a_result);
+if ( $jrn->str_date != ' CHANGED ' ) {    
+    echo_warning("erreur changement non pris en compte");}
+else {
+    echo p('Changement Ok');
+}
+$jrn->update();
+$jrn->load();
+if ( $jrn->str_date != '05/01/22 00:00' ) {    
+    echo_warning("erreur changement non pris en compte");
+    var_dump($jrn);
+}
+else {
+    echo p('Ok : update and load');
+}
+
+$jrn->to_row(['str_date'=>"Autre date"]);
+var_dump($jrn);
+echo h1("Donnée inexistante ");
+$jrn=new FakeData2_SQL($g_connection,22911);
+print $jrn;
+
+echo h1("collect objects");
+$jrn_collect = new FakeData2_SQL($g_connection);
+$jrn_collect->set_virtual_col("str_date", " to_char(jr_tech_date,'DD/MM/YY 
HH24:MI')");
+$a_jrn = $jrn_collect->collect_objects(" where jr_montant > 0 order by 
jr_montant desc ");
+$nb_jrn = count($a_jrn);
+for ($i = 0 ;$i < 20 && $i < $nb_jrn ; $i++ ) {
+    echo p($a_jrn[$i]->jr_tech_date." = ".$a_jrn[$i]->str_date);
+}
diff --git a/unit-test/include/lib/Table_Data_SQLTest.php 
b/unit-test/include/lib/Table_Data_SQLTest.php
new file mode 100644
index 000000000..d90306ce0
--- /dev/null
+++ b/unit-test/include/lib/Table_Data_SQLTest.php
@@ -0,0 +1,204 @@
+<?php
+
+/*
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License
+ * as published by the Free Software Foundation; either version 2
+ * of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
+ *
+ * 
+ * Author : Dany De Bontridder danydb@noalyss.eu
+ * Copyright (C) 2025 Dany De Bontridder <dany@alchimerys.be>
+ * 
+ */
+
+/**
+ * @file
+ * @brief noalyss
+ */
+use PHPUnit\Framework\TestCase;
+
+require DIRTEST . '/global.php';
+
+class FakeData1_SQL extends \Table_Data_SQL {
+
+    function __construct(Database $p_cn, $p_id = -1) {
+        $this->table = "public.jrn";
+        $this->primary_key = "jr_id";
+        /*
+         * List of columns
+         */
+        $this->name = array(
+            "jr_id" => "jr_id"
+            , "jr_def_id" => "jr_def_id"
+            , "jr_montant" => "jr_montant"
+            , "jr_comment" => "jr_comment"
+            , "jr_date" => "jr_date"
+            , "jr_grpt_id" => "jr_grpt_id"
+            , "jr_internal" => "jr_internal"
+            , "jr_tech_date" => "jr_tech_date"
+            , "jr_tech_per" => "jr_tech_per"
+            , "jrn_ech" => "jrn_ech"
+            , "jr_ech" => "jr_ech"
+            , "jr_rapt" => "jr_rapt"
+            , "jr_valid" => "jr_valid"
+            , "jr_opid" => "jr_opid"
+            , "jr_c_opid" => "jr_c_opid"
+            , "jr_pj" => "jr_pj"
+            , "jr_pj_name" => "jr_pj_name"
+            , "jr_pj_type" => "jr_pj_type"
+            , "jr_pj_number" => "jr_pj_number"
+            , "jr_mt" => "jr_mt"
+            , "jr_date_paid" => "jr_date_paid"
+            , "jr_optype" => "jr_optype"
+            , "currency_id" => "currency_id"
+            , "currency_rate" => "currency_rate"
+            , "currency_rate_ref" => "currency_rate_ref"
+        );
+
+        /*
+         * Type of columns
+         */
+        $this->type = array(
+            "jr_id" => "numeric"
+            , "jr_def_id" => "numeric"
+            , "jr_montant" => "numeric"
+            , "jr_comment" => "text"
+            , "jr_date" => "date"
+            , "jr_grpt_id" => "numeric"
+            , "jr_internal" => "text"
+            , "jr_tech_date" => "date"
+            , "jr_tech_per" => "numeric"
+            , "jrn_ech" => "date"
+            , "jr_ech" => "date"
+            , "jr_rapt" => "text"
+            , "jr_valid" => "boolean"
+            , "jr_opid" => "numeric"
+            , "jr_c_opid" => "numeric"
+            , "jr_pj" => "oid"
+            , "jr_pj_name" => "text"
+            , "jr_pj_type" => "text"
+            , "jr_pj_number" => "text"
+            , "jr_mt" => "text"
+            , "jr_date_paid" => "date"
+            , "jr_optype" => "text"
+            , "currency_id" => "numeric"
+            , "currency_rate" => "numeric"
+            , "currency_rate_ref" => "numeric"
+        );
+
+        $this->default = array("jr_id" => "auto");
+
+        $this->date_format = "DD.MM.YYYY";
+        parent::__construct($p_cn, $p_id);
+    }
+}
+
+/**
+ * @testdox Test Table_Data_SQL
+ * @coversDefaultClass
+ * @global $g_connection
+ */
+class Table_Data_SQLTest extends TestCase {
+
+    /**
+     * @testdox Load existing row
+     * @covers load(), __construct
+    
+     */
+    function testLoadExisting()
+    {
+        global $g_connection;
+        $jrn=new FakeData1_SQL($g_connection,911);
+        $this->assertTrue($jrn->jr_id==911," Load existing doesn't load 911");
+        $this->assertTrue($jrn->jr_internal=='V000410'," jr_internal not 
correct");
+    }
+    /**
+     * @testdox Not existing row
+     * @covers load(), __construct
+     */
+    function testLoadNotExists()
+    {
+        global $g_connection;
+        $jrn=new FakeData1_SQL($g_connection,191911);
+        $this->assertTrue($jrn->jr_id==-1," Load existing doesn't load 911");
+        $this->assertTrue($jrn->jr_internal==null," jr_internal not correct");
+    }
+    /**
+     * @testdox Virtual Column : formatted date
+     * @covers load(), __construct ,set_virtual_col
+     */
+    function testVirtualColFormattedDate()
+    {
+        global $g_connection;
+        
+        $jrn=new FakeData1_SQL($g_connection);
+        $jrn->jr_id=915;
+        $jrn->set_virtual_col("str_date", " to_char(jr_tech_date,'DD.MM.YY 
HH24:MI:SS')");
+        $jrn->load();
+        $this->assertTrue($jrn->str_date=='05.01.22 13:01:46',"virtual col 
fails gets [{$jrn->str_date}]");
+    }
+     /**
+     * @testdox Virtual Column : calc
+     * @covers load(), __construct, set_virtual_col
+     */
+    function testVirtualColCalc()
+    {
+        global $g_connection;
+        $jrn=new FakeData1_SQL($g_connection);
+        $jrn->jr_id=911;
+        $jrn->set_virtual_col("double_amount", " jr_montant  * 2 ");
+        $jrn->load();
+        $this->assertTrue($jrn->double_amount==2*$jrn->jr_montant,"virtual col 
fails gets [{$jrn->double_amount}]");
+    }
+    /**
+     * @testdox Virtual Column : when case
+     * @covers load(), __construct, set_virtual_col
+     */
+    function testVirtualColWhenCase()
+    {
+        global $g_connection;
+        $jrn=new FakeData1_SQL($g_connection);
+        $jrn->set_virtual_col('day_date'," to_char(jr_date,'DD')");
+        $jrn->set_virtual_col('day_event',"  case when 
to_char(jr_date,'DD')::int  % 2 = 0 then 'EVEN' else 'ODD' end ");
+        $r=$jrn->seek();
+        
+        $nb=\Database::num_row($r);
+        $this->assertTrue ($nb > 0 , "nothing found");
+        for ($x=0;$x < $nb ; $x++)
+        {
+            $a_jrn=\Database::fetch_array($r, $x);
+            if ( $a_jrn['day_date'] % 2 == 0 ) $this->assertTrue( 
$a_jrn['day_event'] =='EVEN',"virtual col fails gets event 
".var_export($a_jrn,true));
+            if ( $a_jrn['day_date'] % 2 != 0 ) $this->assertTrue( 
$a_jrn['day_event']=='ODD',"virtual col fails gets event 
".var_export($a_jrn,true));
+        }
+    }
+    /**
+     * @testdox Data_SQL::to_row and update : 
+     * @covers Data_SQL->to_row() and Table_Data_SQL->update()
+     */
+    function testTo_Row()
+    {
+        global $g_connection;
+        $jrn=new FakeData1_SQL($g_connection);
+        $jrn->set_virtual_col("double_amount", " jr_montant  * 2 ");
+        $jrn->jr_id=911;
+        $jrn->load();
+        $this->assertTrue($jrn->jr_id==911," Load existing doesn't load 911");
+        $this->assertTrue($jrn->jr_internal=='V000410'," jr_internal not 
correct");
+        $this->assertTrue($jrn->double_amount==$jrn->jr_montant *2," virtual 
column double_amount not correct {$jrn->double_amount} and jr_montant is 
{$jrn->jr_montant}");
+        $jrn->to_row(array("double_amount"=>-1));
+        $this->assertTrue($jrn->double_amount==-1," virtual column 
double_amount not changed {$jrn->double_amount}");
+        $this->assertTrue($jrn->jr_internal=='V000410'," jr_internal has 
changed");
+        
+    }
+}
diff --git a/unit-test/include/lib/noalyss_sqlTest.php 
b/unit-test/include/lib/noalyss_sqlTest.php
index c17ae5d00..c779a790b 100644
--- a/unit-test/include/lib/noalyss_sqlTest.php
+++ b/unit-test/include/lib/noalyss_sqlTest.php
@@ -294,7 +294,7 @@ VALUES(1, 'ACTIVITE', 'Activité commerciale Alchimerys 
sprl');
     public function testbuild_query()
     {
         $str=$this->object->build_query();
-        $expected=" select 
po_id,po_name,pa_id,po_amount,po_description,ga_id,po_state from 
public.poste_analytique where po_id = $1";
+        $expected=" select 
po_id,po_name,pa_id,po_amount,po_description,ga_id,po_state from 
public.poste_analytique";
         $this->assertEquals($str,$expected);
     }