[Noalyss-commit] [noalyss] 18/33: Bug : currency_id was nullable script compatible PSQL 10

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit 096cfc4a20cdab91d36dcacd5b4b2860b6d0596e
Author: sparkyx <danydb@noalyss.eu>
AuthorDate: Mon Oct 11 18:28:17 2021 +0200

    Bug : currency_id was nullable
    script compatible PSQL 10
---
 include/constant.php             |  2 +-
 include/sql/patch/upgrade168.sql | 34 ++++++++++++++++++++++++++++++++++
 2 files changed, 35 insertions(+), 1 deletion(-)

diff --git a/include/constant.php b/include/constant.php
index 819f8b5..ce5670e 100644
--- a/include/constant.php
+++ b/include/constant.php
@@ -116,7 +116,7 @@ if ( !defined ("NOALYSS_PACKAGE_REPOSITORY")) {
 if ( ! defined ("SYSINFO_DISPLAY")) {
     define ("SYSINFO_DISPLAY",TRUE);
 }
-define ("DBVERSION",168);
+define ("DBVERSION",169);
 define ("MONO_DATABASE",25);
 define ("DBVERSIONREPO",20);
 define ('NOTFOUND','--not found--');
diff --git a/include/sql/patch/upgrade168.sql b/include/sql/patch/upgrade168.sql
new file mode 100644
index 0000000..9d884bf
--- /dev/null
+++ b/include/sql/patch/upgrade168.sql
@@ -0,0 +1,34 @@
+begin;
+
+update jrn set currency_id = 0,currency_rate=1,currency_rate_ref = 1  where 
currency_id  is null or currency_rate is null or currency_rate_ref is null;
+
+
+    
+   
+CREATE OR REPLACE FUNCTION comptaproc.jrn_currency()
+ RETURNS trigger
+ AS $function$
+begin 
+       if new.currency_id is null then 
+               new.currency_id := 0;
+                new.currency_rate := 1;
+                new.currency_rate_ref := 1;
+       end if;
+       return new;
+end;
+$function$
+LANGUAGE plpgsql;
+
+create trigger t_jrn_currency before
+insert or update
+    on
+    public.jrn for each row execute procedure comptaproc.jrn_currency();
+
+
+ALTER TABLE public.jrn ALTER COLUMN currency_id SET NOT NULL;
+ALTER TABLE public.jrn ALTER COLUMN currency_rate SET NOT NULL;
+ALTER TABLE public.jrn ALTER COLUMN currency_rate_ref SET NOT NULL;
+
+
+insert into version (val,v_description) values (169,'Fix bug currency_id is 
null, from IMPORTBANK');
+commit ;