[Noalyss-commit] [noalyss] 31/33: Database upgrade

[dwm]

sparkyx pushed a commit to branch unstable
in repository noalyss.

commit 5f13e6ede58c0f6396e45d79905a6442ef8a5810
Author: sparkyx <danydb@noalyss.eu>
AuthorDate: Sat Jul 13 22:00:39 2024 +0200

    Database upgrade
---
 include/constant.php                               |  2 +-
 .../sql/patch/upgrade197.sql                       |  7 +-
 sql/upgrade.sql                                    | 84 ----------------------
 3 files changed, 7 insertions(+), 86 deletions(-)

diff --git a/include/constant.php b/include/constant.php
index 020df53e8..81b85cf0f 100644
--- a/include/constant.php
+++ b/include/constant.php
@@ -28,7 +28,7 @@ global $version_noalyss;
 define('NOALYSS_VERSION', 9206 );
 
 // Database schema version 
-define("DBVERSION", 197);
+define("DBVERSION", 198);
 
 // version for MONO_DATABASE
 define("MONO_DATABASE", 25);
diff --git a/sql/upgrade.sql b/include/sql/patch/upgrade197.sql
similarity index 95%
copy from sql/upgrade.sql
copy to include/sql/patch/upgrade197.sql
index 44c48d457..8863e4d60 100644
--- a/sql/upgrade.sql
+++ b/include/sql/patch/upgrade197.sql
@@ -1,9 +1,10 @@
+begin;
 
 update menu_ref set me_menu='Navigateur &#x1F9ED;' where me_code ~ 'NAVI';
 
 update menu_ref set me_menu='Configuration &#x1F527;' where me_code='CFG';
 
-update menu_ref set me_menu='Recherech  &#x1F50E;' where me_code='SEARCH';
+update menu_ref set me_menu='Recherche  &#x1F50E;' where me_code='SEARCH';
 
 update menu_ref set me_menu='Agenda  &#x1f4c5;' where me_code='AGENDA';
 
@@ -82,3 +83,7 @@ AS SELECT a.f_id,
                         WHERE fiche_detail.ad_id = 5) l ON a.f_id = l.f_id;
 
 COMMENT ON VIEW public.vw_fiche_attr IS 'Some attribute for all cards';
+
+
+insert into version (val,v_description) values (198,'Cosmetic and VAT');
+commit;
\ No newline at end of file
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index 44c48d457..e69de29bb 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -1,84 +0,0 @@
-
-update menu_ref set me_menu='Navigateur &#x1F9ED;' where me_code ~ 'NAVI';
-
-update menu_ref set me_menu='Configuration &#x1F527;' where me_code='CFG';
-
-update menu_ref set me_menu='Recherech  &#x1F50E;' where me_code='SEARCH';
-
-update menu_ref set me_menu='Agenda  &#x1f4c5;' where me_code='AGENDA';
-
-
-ALTER TABLE public.tva_rate ADD CONSTRAINT tva_code_number_check CHECK 
(tva_code::text !~ '^([0-9]+)$');
-
--- drop view if exists  public.vw_fiche_attr;
-
-CREATE OR REPLACE VIEW public.vw_fiche_attr
-AS SELECT a.f_id,
-          a.fd_id,
-          a.ad_value AS vw_name,
-          k.ad_value AS vw_first_name,
-          b.ad_value AS vw_sell,
-          c.ad_value AS vw_buy,
-          d.ad_value AS tva_code,
-          tva_rate.tva_id,
-          tva_rate.tva_rate,
-          tva_rate.tva_label,
-          e.ad_value AS vw_addr,
-          f.ad_value AS vw_cp,
-          j.ad_value AS quick_code,
-          h.ad_value AS vw_description,
-          i.ad_value AS tva_num,
-          fiche_def.frd_id,
-          l.ad_value AS accounting,
-          a.f_enable
-   FROM ( SELECT fiche.f_id,
-                 fiche.fd_id,
-                 fiche.f_enable,
-                 fiche_detail.ad_value
-          FROM fiche
-                   LEFT JOIN fiche_detail USING (f_id)
-          WHERE fiche_detail.ad_id = 1) a
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 6) b ON a.f_id = b.f_id
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 7) c ON a.f_id = c.f_id
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 2) d ON a.f_id = d.f_id
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 14) e ON a.f_id = e.f_id
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 15) f ON a.f_id = f.f_id
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 23) j ON a.f_id = j.f_id
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 9) h ON a.f_id = h.f_id
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 13) i ON a.f_id = i.f_id
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 32) k ON a.f_id = k.f_id
-            LEFT JOIN tva_rate ON d.ad_value = tva_rate.tva_id::text
-            JOIN fiche_def USING (fd_id)
-            LEFT JOIN ( SELECT fiche_detail.f_id,
-                               fiche_detail.ad_value
-                        FROM fiche_detail
-                        WHERE fiche_detail.ad_id = 5) l ON a.f_id = l.f_id;
-
-COMMENT ON VIEW public.vw_fiche_attr IS 'Some attribute for all cards';