[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Noalyss-commit] [noalyss] 11/16: Force the login in lower case in table
From: |
Dany De Bontridder |
Subject: |
[Noalyss-commit] [noalyss] 11/16: Force the login in lower case in table where it is used |
Date: |
Tue, 20 Oct 2015 14:26:33 +0000 |
sparkyx pushed a commit to branch master
in repository noalyss.
commit 92717f98962534a33248efc1152f62ec94eaeb9a
Author: Dany De Bontridder <address@hidden>
Date: Tue Oct 20 11:38:07 2015 +0200
Force the login in lower case in table where it is used
---
sql/upgrade.sql | 147 ++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 146 insertions(+), 1 deletions(-)
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index 906e444..9fcce26 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -20,4 +20,149 @@ insert into profile_menu (me_code,p_id,p_type_display)
select 'RAW:document',p_i
insert into menu_ref(me_code,me_file,me_menu,me_description,me_type)
values ('RAW:document_template','export_document_template.php','Exporte le
modèle de document','export le modèle de document utilisé dans le suivi','PR');
-insert into profile_menu (me_code,p_id,p_type_display) select
'RAW:document_template',p_id,'P' from profile where p_id > 0;
\ No newline at end of file
+insert into profile_menu (me_code,p_id,p_type_display) select
'RAW:document_template',p_id,'P' from profile where p_id > 0;
+
+
+delete from PROFILE_USER where pu_id in (select b.pu_id
+ from profile_user as a , profile_user as b
+ where
+ upper(a.user_name) = b.user_name and a.pu_id <> b.pu_id );
+
+
+
+CREATE OR REPLACE FUNCTION comptaproc.trg_profile_user_ins_upd()
+ RETURNS trigger AS
+$BODY$
+
+begin
+
+NEW.user_name := lower(NEW.user_name);
+return NEW;
+
+end;
+$BODY$
+language plpgsql;
+
+CREATE TRIGGER profile_user_ins_upd
+ BEFORE INSERT OR UPDATE
+ ON profile_user
+ FOR EACH ROW
+ EXECUTE PROCEDURE comptaproc.trg_profile_user_ins_upd();
+COMMENT ON TRIGGER profile_user_ins_upd ON profile_user IS 'Force the column
user_name to lowercase';
+
+
+
+delete from user_sec_jrn where uj_id in (select b.uj_id
+ from user_sec_jrn as a , user_sec_jrn as b
+ where
+ upper(a.uj_login) = b.uj_login and a.uj_id<> b.uj_id);
+
+
+update user_sec_jrn set uj_login = lower(uj_login);
+
+ALTER TABLE user_sec_jrn
+ ADD CONSTRAINT uniq_user_ledger UNIQUE(uj_login , uj_jrn_id );
+COMMENT ON CONSTRAINT uniq_user_ledger ON user_sec_jrn IS 'Create an unique
combination user / ledger';
+
+CREATE OR REPLACE FUNCTION comptaproc.trg_user_sec_jrn_ins_upd()
+ RETURNS trigger AS
+$BODY$
+
+begin
+
+NEW.uj_login:= lower(NEW.uj_login);
+return NEW;
+
+end;
+$BODY$
+language plpgsql;
+
+
+CREATE TRIGGER user_sec_jrn_after_ins_upd
+ BEFORE INSERT OR UPDATE
+ ON user_sec_jrn
+ FOR EACH ROW
+ EXECUTE PROCEDURE comptaproc.trg_user_sec_jrn_ins_upd();
+COMMENT ON TRIGGER user_sec_jrn_ins_upd ON user_sec_jrn IS 'Force the column
uj_login to lowercase';
+
+
+delete from user_sec_act where ua_id in (select b.ua_id
+ from user_sec_act as a , user_sec_act as b
+ where
+ upper(a.ua_login) = b.ua_login and a.ua_id<> b.ua_id);
+
+update user_sec_act set ua_login = lower(ua_login);
+
+CREATE OR REPLACE FUNCTION comptaproc.trg_user_sec_act_ins_upd()
+ RETURNS trigger AS
+$BODY$
+
+begin
+
+NEW.ua_login:= lower(NEW.ua_login);
+return NEW;
+
+end;
+$BODY$
+language plpgsql;
+
+
+CREATE TRIGGER user_sec_act_ins_upd
+ BEFORE INSERT OR UPDATE
+ ON user_sec_act
+ FOR EACH ROW
+ EXECUTE PROCEDURE comptaproc.trg_user_sec_act_ins_upd();
+COMMENT ON TRIGGER user_sec_act_ins_upd ON user_sec_act IS 'Force the column
ua_login to lowercase';
+
+update todo_list set use_login = lower(use_login);
+
+CREATE OR REPLACE FUNCTION comptaproc.trg_todo_list_ins_upd()
+ RETURNS trigger AS
+$BODY$
+
+begin
+
+NEW.use_login:= lower(NEW.use_login);
+return NEW;
+
+end;
+$BODY$
+language plpgsql;
+
+
+CREATE TRIGGER todo_list_ins_upd
+ BEFORE INSERT OR UPDATE
+ ON todo_list
+ FOR EACH ROW
+ EXECUTE PROCEDURE comptaproc.trg_todo_list_ins_upd();
+COMMENT ON TRIGGER todo_list_ins_upd ON todo_list IS 'Force the column
use_login to lowercase';
+
+
+
+delete from todo_list_shared where id in (select b.id
+ from todo_list_shared as a , todo_list_shared as b
+ where
+ upper(a.use_login) = b.use_login and a.id<> b.id);
+
+update todo_list_shared set use_login = lower(use_login);
+
+CREATE OR REPLACE FUNCTION comptaproc.trg_todo_list_shared_ins_upd()
+ RETURNS trigger AS
+$BODY$
+
+begin
+
+NEW.use_login:= lower(NEW.use_login);
+return NEW;
+
+end;
+$BODY$
+language plpgsql;
+
+
+CREATE TRIGGER todo_list_shared_ins_upd
+ BEFORE INSERT OR UPDATE
+ ON todo_list_shared
+ FOR EACH ROW
+ EXECUTE PROCEDURE comptaproc.trg_todo_list_shared_ins_upd();
+COMMENT ON TRIGGER todo_list_shared_ins_upd ON todo_list_shared IS 'Force the
column ua_login to lowercase';
\ No newline at end of file
- [Noalyss-commit] [noalyss] branch master updated (659cbc6 -> 02d8767), Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 02/16: Rename file admin_repo by admin_repo.inc, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 05/16: Administration : add information about system, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 01/16: Move Admin_repo to include , add setup.php for redirecting Move user_menu to include/lib, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 06/16: System information : display variable from config.inc.php, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 08/16: Administration : add information about system update all the databases + account_repository, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 11/16: Force the login in lower case in table where it is used,
Dany De Bontridder <=
- [Noalyss-commit] [noalyss] 09/16: System information : display variable from config.inc.php, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 07/16: Administration : add information about system update all the databases + account_repository, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 04/16: Restructure file , remove html/admin/sql , backup ..., Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 10/16: Administration : update MULTI, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 14/16: remove obsolete file, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 15/16: Improve installation system, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 12/16: force login user to lower case for ACTION_GESTION, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 03/16: Restructure file , remove html/admin , backup ..., Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 16/16: Remove admin folder and use another system installation, it is now possible to upgrade DB thanks the administration page, Dany De Bontridder, 2015/10/20
- [Noalyss-commit] [noalyss] 13/16: Improve installation system, Dany De Bontridder, 2015/10/20