[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Noalyss-commit] [noalyss] 109/323: update account_repository (to review
From: |
Dany De Bontridder |
Subject: |
[Noalyss-commit] [noalyss] 109/323: update account_repository (to review) |
Date: |
Wed, 14 Mar 2018 17:38:34 -0400 (EDT) |
sparkyx pushed a commit to branch master
in repository noalyss.
commit cdd7259cf62a0f279c1e09f17b8a5f09ea969b64
Author: Dany De Bontridder <address@hidden>
Date: Tue Jan 30 19:37:35 2018 +0100
update account_repository (to review)
---
include/dossier.inc.php | 2 +-
include/sql/account_repository/comment.sql | 8 +
include/sql/account_repository/constraint.sql | 25 ++-
include/sql/account_repository/data.sql | 99 ++++-----
include/sql/account_repository/function.sql | 22 ++
include/sql/account_repository/index.sql | 2 +
include/sql/account_repository/make-sql | 29 ++-
include/sql/account_repository/schema.sql | 280 ++++++++++++++++++++++++--
include/sql/account_repository/sequence.sql | 27 ++-
include/sql/account_repository/table.sql | 33 ++-
include/sql/mod1/schema.sql | 4 -
11 files changed, 433 insertions(+), 98 deletions(-)
diff --git a/include/dossier.inc.php b/include/dossier.inc.php
index 18f1257..d754b1a 100644
--- a/include/dossier.inc.php
+++ b/include/dossier.inc.php
@@ -39,7 +39,7 @@ if ( isset ($_POST['upd']) && isNumber($dossier_id) == 1 &&
$dossier_id != -1)
$dos=new dossier($dossier_id);
$name=$http->post('name');
$desc=$http->post('desc');
- $max_email=$http->post("max_email");
+ $max_email=$http->post("max_email");
$dos->set_parameter('name',$name);
$dos->set_parameter('desc',$desc);
$dos->set_parameter("max_email", $max_email);
diff --git a/include/sql/account_repository/comment.sql
b/include/sql/account_repository/comment.sql
index fe1826f..bd59b1c 100644
--- a/include/sql/account_repository/comment.sql
+++ b/include/sql/account_repository/comment.sql
@@ -1,3 +1,11 @@
+COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
+COMMENT ON COLUMN ac_dossier.dos_email IS 'Max emails per day : 0 none , -1
unlimited or max value';
+COMMENT ON COLUMN ac_users.use_email IS 'Email of the user';
+COMMENT ON TABLE dossier_sent_email IS 'Count the sent email by folder';
+COMMENT ON COLUMN dossier_sent_email.id IS 'primary key';
+COMMENT ON COLUMN dossier_sent_email.de_date IS 'Date YYYYMMDD';
+COMMENT ON COLUMN dossier_sent_email.de_sent_email IS 'Number of sent emails';
+COMMENT ON COLUMN dossier_sent_email.dos_id IS 'Link to ac_dossier';
COMMENT ON TABLE user_global_pref IS 'The user''s global parameter ';
COMMENT ON COLUMN user_global_pref.user_id IS 'user''s login ';
COMMENT ON COLUMN user_global_pref.parameter_type IS 'the type of parameter ';
diff --git a/include/sql/account_repository/constraint.sql
b/include/sql/account_repository/constraint.sql
index c13d9d6..87dedde 100644
--- a/include/sql/account_repository/constraint.sql
+++ b/include/sql/account_repository/constraint.sql
@@ -2,6 +2,8 @@
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
+ALTER TABLE ONLY audit_connect ALTER COLUMN ac_id SET DEFAULT
nextval('audit_connect_ac_id_seq'::regclass);
+ALTER TABLE ONLY dossier_sent_email ALTER COLUMN id SET DEFAULT
nextval('dossier_sent_email_id_seq'::regclass);
ALTER TABLE ONLY ac_dossier
ADD CONSTRAINT ac_dossier_dos_name_key UNIQUE (dos_name);
ALTER TABLE ONLY ac_dossier
@@ -10,13 +12,30 @@ ALTER TABLE ONLY ac_users
ADD CONSTRAINT ac_users_pkey PRIMARY KEY (use_id);
ALTER TABLE ONLY ac_users
ADD CONSTRAINT ac_users_use_login_key UNIQUE (use_login);
+ALTER TABLE ONLY audit_connect
+ ADD CONSTRAINT audit_connect_pkey PRIMARY KEY (ac_id);
+ALTER TABLE ONLY dossier_sent_email
+ ADD CONSTRAINT de_date_dos_id_ux UNIQUE (de_date, dos_id);
+ALTER TABLE ONLY dossier_sent_email
+ ADD CONSTRAINT dossier_sent_email_pkey PRIMARY KEY (id);
ALTER TABLE ONLY jnt_use_dos
- ADD CONSTRAINT jnt_use_dos_pkey PRIMARY KEY (use_id, dos_id);
+ ADD CONSTRAINT jnt_use_dos_pkey PRIMARY KEY (jnt_id);
ALTER TABLE ONLY modeledef
ADD CONSTRAINT modeledef_pkey PRIMARY KEY (mod_id);
ALTER TABLE ONLY user_global_pref
ADD CONSTRAINT pk_user_global_pref PRIMARY KEY (user_id, parameter_type);
-ALTER TABLE ONLY priv_user
- ADD CONSTRAINT priv_user_pkey PRIMARY KEY (priv_id);
+ALTER TABLE ONLY recover_pass
+ ADD CONSTRAINT recover_pass_pkey PRIMARY KEY (request);
+ALTER TABLE ONLY version
+ ADD CONSTRAINT version_pkey PRIMARY KEY (val);
+ALTER TABLE ONLY recover_pass
+ ADD CONSTRAINT ac_users_recover_pass_fk FOREIGN KEY (use_id) REFERENCES
ac_users(use_id) ON UPDATE CASCADE ON DELETE CASCADE;
+ALTER TABLE ONLY dossier_sent_email
+ ADD CONSTRAINT de_ac_dossier_fk FOREIGN KEY (dos_id) REFERENCES
ac_dossier(dos_id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY user_global_pref
ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES
ac_users(use_login) ON UPDATE CASCADE ON DELETE CASCADE;
+ALTER TABLE ONLY jnt_use_dos
+ ADD CONSTRAINT jnt_use_dos_dos_id_fkey FOREIGN KEY (dos_id) REFERENCES
ac_dossier(dos_id) ON UPDATE CASCADE ON DELETE CASCADE;
+ALTER TABLE ONLY jnt_use_dos
+ ADD CONSTRAINT jnt_use_dos_use_id_fkey FOREIGN KEY (use_id) REFERENCES
ac_users(use_id);
+CREATE TRIGGER limit_user_trg BEFORE INSERT OR UPDATE ON audit_connect FOR
EACH ROW EXECUTE PROCEDURE limit_user();
diff --git a/include/sql/account_repository/data.sql
b/include/sql/account_repository/data.sql
index c7e6946..16093cf 100644
--- a/include/sql/account_repository/data.sql
+++ b/include/sql/account_repository/data.sql
@@ -1,114 +1,87 @@
---
--- PostgreSQL database dump
---
+set search_path = public, comptaproc,pg_catalog ;
+SET statement_timeout = 0;
SET client_encoding = 'UTF8';
-SET standard_conforming_strings = off;
+SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
-SET escape_string_warning = off;
-SET search_path = public, pg_catalog;
---
--- Name: dossier_id; Type: SEQUENCE SET; Schema: public; Owner: -
---
-SELECT pg_catalog.setval('dossier_id', 24, true);
+INSERT INTO ac_dossier (dos_id, dos_name, dos_description, dos_email) VALUES
(25, 'Création dossier 1', '', -1);
---
--- Name: s_modid; Type: SEQUENCE SET; Schema: public; Owner: -
---
-SELECT pg_catalog.setval('s_modid', 8, true);
+INSERT INTO ac_users (use_id, use_first_name, use_name, use_login, use_active,
use_pass, use_admin, use_email) VALUES (4, 'demo', 'demo', 'demo', 1,
'fe01ce2a7fbac8fafaed7c982a04e229', 0, NULL);
+INSERT INTO ac_users (use_id, use_first_name, use_name, use_login, use_active,
use_pass, use_admin, use_email) VALUES (1, NULL, NULL, 'admin', 1,
'b1cc88e1907cde80cb2595fa793b3da9', 1, NULL);
---
--- Name: seq_jnt_use_dos; Type: SEQUENCE SET; Schema: public; Owner: -
---
-SELECT pg_catalog.setval('seq_jnt_use_dos', 28, true);
+INSERT INTO audit_connect (ac_id, ac_user, ac_date, ac_ip, ac_state,
ac_module, ac_url) VALUES (1, 'admin', '2017-12-20 09:22:59.042664',
'127.0.0.1', 'SUCCESS', 'LOGIN',
'/developpement/phpcompta/accept/noalyss-6.9.1.9/html/login.php');
---
--- Name: seq_priv_user; Type: SEQUENCE SET; Schema: public; Owner: -
---
-SELECT pg_catalog.setval('seq_priv_user', 12, true);
+SELECT pg_catalog.setval('audit_connect_ac_id_seq', 1, true);
---
--- Name: users_id; Type: SEQUENCE SET; Schema: public; Owner: -
---
-SELECT pg_catalog.setval('users_id', 5, true);
+SELECT pg_catalog.setval('dossier_id', 25, true);
---
--- Data for Name: ac_dossier; Type: TABLE DATA; Schema: public; Owner: -
---
---
--- Data for Name: ac_users; Type: TABLE DATA; Schema: public; Owner: -
---
-INSERT INTO ac_users (use_id, use_first_name, use_name, use_login, use_active,
use_pass, use_admin) VALUES (4, 'demo', 'demo', 'demo', 1,
'fe01ce2a7fbac8fafaed7c982a04e229', 0);
-INSERT INTO ac_users (use_id, use_first_name, use_name, use_login, use_active,
use_pass, use_admin) VALUES (1, NULL, NULL, 'phpcompta', 1,
'b1cc88e1907cde80cb2595fa793b3da9', 1);
+SELECT pg_catalog.setval('dossier_sent_email_id_seq', 1, false);
---
--- Data for Name: jnt_use_dos; Type: TABLE DATA; Schema: public; Owner: -
---
+INSERT INTO jnt_use_dos (jnt_id, use_id, dos_id) VALUES (29, 1, 25);
---
--- Data for Name: modeledef; Type: TABLE DATA; Schema: public; Owner: -
---
INSERT INTO modeledef (mod_id, mod_name, mod_desc) VALUES (1, '(BE) Basique',
'Comptabilité Belge, à adapter');
INSERT INTO modeledef (mod_id, mod_name, mod_desc) VALUES (2, '(FR) Basique',
'Comptabilité Française, à adapter');
---
--- Data for Name: priv_user; Type: TABLE DATA; Schema: public; Owner: -
---
---
--- Data for Name: theme; Type: TABLE DATA; Schema: public; Owner: -
---
-INSERT INTO theme (the_name, the_filestyle, the_filebutton) VALUES ('classic',
'style.css', NULL);
+SELECT pg_catalog.setval('s_modid', 8, true);
+
+
+
+SELECT pg_catalog.setval('seq_jnt_use_dos', 29, true);
+
+
+
+SELECT pg_catalog.setval('seq_priv_user', 12, true);
+
+
+
INSERT INTO theme (the_name, the_filestyle, the_filebutton) VALUES ('Light',
'style-light.css', NULL);
-INSERT INTO theme (the_name, the_filestyle, the_filebutton) VALUES ('Colored',
'style-color.css', NULL);
+INSERT INTO theme (the_name, the_filestyle, the_filebutton) VALUES
('Mandarine', 'style-mandarine.css', NULL);
+INSERT INTO theme (the_name, the_filestyle, the_filebutton) VALUES ('Mobile',
'style-mobile.css', NULL);
+INSERT INTO theme (the_name, the_filestyle, the_filebutton) VALUES
('Classique', 'style-classic.css', NULL);
---
--- Data for Name: user_global_pref; Type: TABLE DATA; Schema: public; Owner: -
---
INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('demo', 'PAGESIZE', '50');
-INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('phpcompta', 'PAGESIZE', '50');
-INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('demo', 'THEME', 'classic');
-INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('phpcompta', 'THEME', 'classic');
INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('demo', 'LANG', 'fr_FR.utf8');
-INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('phpcompta', 'LANG', 'fr_FR.utf8');
-INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('phpcompta', 'TOPMENU', 'TEXT');
+INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('admin', 'PAGESIZE', '50');
+INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('admin', 'LANG', 'fr_FR.utf8');
+INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('admin', 'TOPMENU', 'TEXT');
+INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('demo', 'THEME', 'Classique');
+INSERT INTO user_global_pref (user_id, parameter_type, parameter_value) VALUES
('admin', 'THEME', 'Classique');
+
+
+
+SELECT pg_catalog.setval('users_id', 5, true);
---
--- Data for Name: version; Type: TABLE DATA; Schema: public; Owner: -
---
-INSERT INTO version (val) VALUES (11);
+INSERT INTO version (val) VALUES (17);
---
--- PostgreSQL database dump complete
---
diff --git a/include/sql/account_repository/function.sql
b/include/sql/account_repository/function.sql
index e69de29..f45b639 100644
--- a/include/sql/account_repository/function.sql
+++ b/include/sql/account_repository/function.sql
@@ -0,0 +1,22 @@
+CREATE FUNCTION limit_user() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+
+begin
+NEW.ac_user := substring(NEW.ac_user from 1 for 80);
+return NEW;
+end; $$;
+CREATE FUNCTION upgrade_repo(p_version integer) RETURNS void
+ LANGUAGE plpgsql
+ AS $$
+declare
+ is_mono integer;
+begin
+ select count (*) into is_mono from information_schema.tables where
table_name='repo_version';
+ if is_mono = 1 then
+ update repo_version set val=p_version;
+ else
+ update version set val=p_version;
+ end if;
+end;
+$$;
diff --git a/include/sql/account_repository/index.sql
b/include/sql/account_repository/index.sql
index 5f56296..45898c2 100644
--- a/include/sql/account_repository/index.sql
+++ b/include/sql/account_repository/index.sql
@@ -1,2 +1,4 @@
+CREATE INDEX audit_connect_ac_user ON audit_connect USING btree (ac_user);
CREATE INDEX fk_jnt_dos_id ON jnt_use_dos USING btree (dos_id);
CREATE INDEX fk_jnt_use_dos ON jnt_use_dos USING btree (use_id);
+CREATE INDEX fki_ac_users_recover_pass_fk ON recover_pass USING btree (use_id);
diff --git a/include/sql/account_repository/make-sql
b/include/sql/account_repository/make-sql
index 6d2bd31..b661af2 100755
--- a/include/sql/account_repository/make-sql
+++ b/include/sql/account_repository/make-sql
@@ -1,5 +1,13 @@
-pg_dump -O -U phpcompta -s ${DOMAIN}account_repository | awk '!/--/ {if ( !
/^$/) print $0;}'|grep -v "COMMENT ON SCHEMA public IS 'Standard public
schema';" > schema.sql
-awk '/SEQUENCE/,/;/ { print $0;}' < schema.sql > sequence.sql
+#!/bin/bash
+# Create script for exporting a new template
+# DDB 2018-01-28
+# under GPL license
+#
+
+export TEMPLATE=${DOMAIN}account_repository
+
+pg_dump -O -U dany -s $TEMPLATE|grep -v "COMMENT ON SCHEMA public IS 'Standard
public schema';" |sed "/^--/d" > schema.sql
+awk '/SEQUENCE/,/;/ { print $0;}' < schema.sql > sequence.sql
awk '/CREATE DOMAIN/,/;/ { print $0;}' < schema.sql > table.sql
awk '/CREATE TABLE/,/;/ { print $0;}' < schema.sql >> table.sql
awk '/CREATE VIEW/,/;/ { print $0;}' < schema.sql > view.sql
@@ -11,11 +19,22 @@ echo " SET client_min_messages = warning;"
echo "SET search_path = public, pg_catalog;"
)> constraint.sql
+
awk '/ALTER TABLE/,/;/ { print $0;}' < schema.sql >> constraint.sql
-awk '/CREATE FUNCTION/,/LANGUAGE/ { print $0;}' < schema.sql > function.sql
+awk '/CREATE TRIGGER/,/;/ { print $0;}' < schema.sql >> constraint.sql
+awk '/COMMENT ON CONSTRAINT/,/;/ { print $0;}' < schema.sql >> constraint.sql
+awk '/COMMENT ON TRIGGER/,/;/ { print $0;}' < schema.sql >> constraint.sql
+
+# function in XML
+## awk 'BEGIN{print "<PROCEDURES>";} /CREATE FUNCTION/,/\$\$;/ { if (/CREATE
FUNCTION/) {print "<procedure>";} if (/\$\$;/) {print "</procedure>";} print
$0;} END { print "</PROCEDURES>";}' < schema.sql
+awk '/CREATE FUNCTION/,/\$\$;/ { print $0;}' < schema.sql > function.sql
awk '/COMMENT/,/;/ {print $0;}' < schema.sql > comment.sql
-sed -i -e "/ALTER TABLE.*/d" -e "/ADD CONSTRAINT/d" -e "/CREATE
PROCEDURAL.*/d" schema.sql
+
+sed -i -e "/REVOKE /d" -e "/GRANT /d" -e "/ALTER TABLE.*/d" -e "/ADD
CONSTRAINT/d" -e "/CREATE PROCEDURAL/d" -e "/CREATE TRIGGER/d" -e "/COMMENT ON
CONSTRAINT/d" -e "/COMMENT ON TRIGGER/d" schema.sql
+
grep setval schema.sql >> sequence.sql
-pg_dump -U phpcompta -D -a -O ${DOMAIN}account_repository > data.sql
+echo "set search_path = public, comptaproc,pg_catalog ;" > data.sql
+pg_dump -O -U dany --data-only --column-inserts -O ${TEMPLATE}|sed "/^--/d" |
sed -e "/SET search_path/d" >> data.sql
+
diff --git a/include/sql/account_repository/schema.sql
b/include/sql/account_repository/schema.sql
index 0a9f8ba..26b9d8c 100644
--- a/include/sql/account_repository/schema.sql
+++ b/include/sql/account_repository/schema.sql
@@ -1,17 +1,66 @@
+
+SET statement_timeout = 0;
SET client_encoding = 'UTF8';
-SET standard_conforming_strings = off;
+SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
-SET escape_string_warning = off;
+
+
+CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
+
+
+
+COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
+
+
SET search_path = public, pg_catalog;
+
+
+CREATE FUNCTION limit_user() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+
+begin
+NEW.ac_user := substring(NEW.ac_user from 1 for 80);
+return NEW;
+end; $$;
+
+
+
+CREATE FUNCTION upgrade_repo(p_version integer) RETURNS void
+ LANGUAGE plpgsql
+ AS $$
+declare
+ is_mono integer;
+begin
+ select count (*) into is_mono from information_schema.tables where
table_name='repo_version';
+ if is_mono = 1 then
+ update repo_version set val=p_version;
+ else
+ update version set val=p_version;
+ end if;
+end;
+$$;
+
+
SET default_tablespace = '';
+
SET default_with_oids = false;
+
+
CREATE TABLE ac_dossier (
dos_id integer DEFAULT nextval(('dossier_id'::text)::regclass) NOT NULL,
dos_name text NOT NULL,
dos_description text,
- dos_jnt_user integer DEFAULT 0
+ dos_email integer DEFAULT (-1)
);
+
+
+
+COMMENT ON COLUMN ac_dossier.dos_email IS 'Max emails per day : 0 none , -1
unlimited or max value';
+
+
+
CREATE TABLE ac_users (
use_id integer DEFAULT nextval(('users_id'::text)::regclass) NOT NULL,
use_first_name text,
@@ -20,64 +69,271 @@ CREATE TABLE ac_users (
use_active integer DEFAULT 0,
use_pass text,
use_admin integer DEFAULT 0,
+ use_email text,
CONSTRAINT ac_users_use_active_check CHECK (((use_active = 0) OR
(use_active = 1)))
);
+
+
+
+COMMENT ON COLUMN ac_users.use_email IS 'Email of the user';
+
+
+
+CREATE TABLE audit_connect (
+ ac_id integer NOT NULL,
+ ac_user text,
+ ac_date timestamp without time zone DEFAULT now(),
+ ac_ip text,
+ ac_state text,
+ ac_module text,
+ ac_url text,
+ CONSTRAINT valid_state CHECK ((((ac_state = 'FAIL'::text) OR (ac_state =
'SUCCESS'::text)) OR (ac_state = 'AUDIT'::text)))
+);
+
+
+
+CREATE SEQUENCE audit_connect_ac_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+
+ALTER SEQUENCE audit_connect_ac_id_seq OWNED BY audit_connect.ac_id;
+
+
+
CREATE SEQUENCE dossier_id
+ START WITH 1
INCREMENT BY 1
+ NO MINVALUE
NO MAXVALUE
+ CACHE 1;
+
+
+
+CREATE TABLE dossier_sent_email (
+ id integer NOT NULL,
+ de_date character varying(8) NOT NULL,
+ de_sent_email integer NOT NULL,
+ dos_id integer NOT NULL
+);
+
+
+
+COMMENT ON TABLE dossier_sent_email IS 'Count the sent email by folder';
+
+
+
+COMMENT ON COLUMN dossier_sent_email.id IS 'primary key';
+
+
+
+COMMENT ON COLUMN dossier_sent_email.de_date IS 'Date YYYYMMDD';
+
+
+
+COMMENT ON COLUMN dossier_sent_email.de_sent_email IS 'Number of sent emails';
+
+
+
+COMMENT ON COLUMN dossier_sent_email.dos_id IS 'Link to ac_dossier';
+
+
+
+CREATE SEQUENCE dossier_sent_email_id_seq
+ START WITH 1
+ INCREMENT BY 1
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
+
+
+
+ALTER SEQUENCE dossier_sent_email_id_seq OWNED BY dossier_sent_email.id;
+
+
+
CREATE TABLE jnt_use_dos (
jnt_id integer DEFAULT nextval(('seq_jnt_use_dos'::text)::regclass) NOT
NULL,
use_id integer NOT NULL,
dos_id integer NOT NULL
);
+
+
+
CREATE TABLE modeledef (
mod_id integer DEFAULT nextval(('s_modid'::text)::regclass) NOT NULL,
mod_name text NOT NULL,
mod_desc text
);
-CREATE TABLE priv_user (
- priv_id integer DEFAULT nextval(('seq_priv_user'::text)::regclass) NOT
NULL,
- priv_jnt integer NOT NULL,
- priv_priv text
+
+
+
+CREATE TABLE recover_pass (
+ use_id bigint NOT NULL,
+ request text NOT NULL,
+ password text NOT NULL,
+ created_on timestamp with time zone,
+ created_host text,
+ recover_on timestamp with time zone,
+ recover_by text
);
+
+
+
CREATE SEQUENCE s_modid
+ START WITH 1
INCREMENT BY 1
- NO MAXVALUE
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
+
+
+
CREATE SEQUENCE seq_jnt_use_dos
+ START WITH 1
INCREMENT BY 1
- NO MAXVALUE
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
+
+
+
CREATE SEQUENCE seq_priv_user
+ START WITH 1
INCREMENT BY 1
- NO MAXVALUE
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
+
+
+
CREATE TABLE theme (
the_name text NOT NULL,
the_filestyle text,
the_filebutton text
);
+
+
+
CREATE TABLE user_global_pref (
user_id text NOT NULL,
parameter_type text NOT NULL,
parameter_value text
);
+
+
+
COMMENT ON TABLE user_global_pref IS 'The user''s global parameter ';
+
+
+
COMMENT ON COLUMN user_global_pref.user_id IS 'user''s login ';
+
+
+
COMMENT ON COLUMN user_global_pref.parameter_type IS 'the type of parameter ';
+
+
+
COMMENT ON COLUMN user_global_pref.parameter_value IS 'the value of parameter
';
+
+
+
CREATE SEQUENCE users_id
+ START WITH 1
INCREMENT BY 1
- NO MAXVALUE
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
+
+
+
CREATE TABLE version (
- val integer
+ val integer NOT NULL
);
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+CREATE INDEX audit_connect_ac_user ON audit_connect USING btree (ac_user);
+
+
+
CREATE INDEX fk_jnt_dos_id ON jnt_use_dos USING btree (dos_id);
+
+
+
CREATE INDEX fk_jnt_use_dos ON jnt_use_dos USING btree (use_id);
+
+
+
+CREATE INDEX fki_ac_users_recover_pass_fk ON recover_pass USING btree (use_id);
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
diff --git a/include/sql/account_repository/sequence.sql
b/include/sql/account_repository/sequence.sql
index dedfacf..32ecd65 100644
--- a/include/sql/account_repository/sequence.sql
+++ b/include/sql/account_repository/sequence.sql
@@ -1,25 +1,44 @@
+CREATE SEQUENCE audit_connect_ac_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+ALTER SEQUENCE audit_connect_ac_id_seq OWNED BY audit_connect.ac_id;
CREATE SEQUENCE dossier_id
+ START WITH 1
INCREMENT BY 1
+ NO MINVALUE
NO MAXVALUE
+ CACHE 1;
+CREATE SEQUENCE dossier_sent_email_id_seq
+ START WITH 1
+ INCREMENT BY 1
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
+ALTER SEQUENCE dossier_sent_email_id_seq OWNED BY dossier_sent_email.id;
CREATE SEQUENCE s_modid
+ START WITH 1
INCREMENT BY 1
- NO MAXVALUE
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
CREATE SEQUENCE seq_jnt_use_dos
+ START WITH 1
INCREMENT BY 1
- NO MAXVALUE
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
CREATE SEQUENCE seq_priv_user
+ START WITH 1
INCREMENT BY 1
- NO MAXVALUE
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
CREATE SEQUENCE users_id
+ START WITH 1
INCREMENT BY 1
- NO MAXVALUE
NO MINVALUE
+ NO MAXVALUE
CACHE 1;
diff --git a/include/sql/account_repository/table.sql
b/include/sql/account_repository/table.sql
index d56f15b..6f60124 100644
--- a/include/sql/account_repository/table.sql
+++ b/include/sql/account_repository/table.sql
@@ -2,7 +2,7 @@ CREATE TABLE ac_dossier (
dos_id integer DEFAULT nextval(('dossier_id'::text)::regclass) NOT NULL,
dos_name text NOT NULL,
dos_description text,
- dos_jnt_user integer DEFAULT 0
+ dos_email integer DEFAULT (-1)
);
CREATE TABLE ac_users (
use_id integer DEFAULT nextval(('users_id'::text)::regclass) NOT NULL,
@@ -12,8 +12,25 @@ CREATE TABLE ac_users (
use_active integer DEFAULT 0,
use_pass text,
use_admin integer DEFAULT 0,
+ use_email text,
CONSTRAINT ac_users_use_active_check CHECK (((use_active = 0) OR
(use_active = 1)))
);
+CREATE TABLE audit_connect (
+ ac_id integer NOT NULL,
+ ac_user text,
+ ac_date timestamp without time zone DEFAULT now(),
+ ac_ip text,
+ ac_state text,
+ ac_module text,
+ ac_url text,
+ CONSTRAINT valid_state CHECK ((((ac_state = 'FAIL'::text) OR (ac_state =
'SUCCESS'::text)) OR (ac_state = 'AUDIT'::text)))
+);
+CREATE TABLE dossier_sent_email (
+ id integer NOT NULL,
+ de_date character varying(8) NOT NULL,
+ de_sent_email integer NOT NULL,
+ dos_id integer NOT NULL
+);
CREATE TABLE jnt_use_dos (
jnt_id integer DEFAULT nextval(('seq_jnt_use_dos'::text)::regclass) NOT
NULL,
use_id integer NOT NULL,
@@ -24,10 +41,14 @@ CREATE TABLE modeledef (
mod_name text NOT NULL,
mod_desc text
);
-CREATE TABLE priv_user (
- priv_id integer DEFAULT nextval(('seq_priv_user'::text)::regclass) NOT
NULL,
- priv_jnt integer NOT NULL,
- priv_priv text
+CREATE TABLE recover_pass (
+ use_id bigint NOT NULL,
+ request text NOT NULL,
+ password text NOT NULL,
+ created_on timestamp with time zone,
+ created_host text,
+ recover_on timestamp with time zone,
+ recover_by text
);
CREATE TABLE theme (
the_name text NOT NULL,
@@ -40,5 +61,5 @@ CREATE TABLE user_global_pref (
parameter_value text
);
CREATE TABLE version (
- val integer
+ val integer NOT NULL
);
diff --git a/include/sql/mod1/schema.sql b/include/sql/mod1/schema.sql
index 0b3ce96..84b4fe1 100644
--- a/include/sql/mod1/schema.sql
+++ b/include/sql/mod1/schema.sql
@@ -6338,10 +6338,6 @@ CREATE INDEX x_poste ON jrnx USING btree (j_poste);
-REVOKE ALL ON SCHEMA public FROM PUBLIC;
-REVOKE ALL ON SCHEMA public FROM postgres;
-GRANT ALL ON SCHEMA public TO postgres;
-GRANT ALL ON SCHEMA public TO PUBLIC;
- [Noalyss-commit] [noalyss] 111/323: History of analytic account the same way as the history for card or accounting, (continued)
- [Noalyss-commit] [noalyss] 111/323: History of analytic account the same way as the history for card or accounting, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 120/323: New LOGO, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 122/323: cosmetic menu level2, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 124/323: Fix : logo and new style by default, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 125/323: fix : managetable correct position, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 114/323: New : code for progress bar, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 129/323: Small bug : new structure for table version, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 126/323: fix : typo default theme Classic7, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 121/323: use icon_action::icon_magnify instead of icon, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 128/323: Improve CSS, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 109/323: update account_repository (to review),
Dany De Bontridder <=
- [Noalyss-commit] [noalyss] 131/323: upgrade SQL : syntax on FK, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 127/323: Esthetic, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 130/323: upgrade SQL : issue with trigger on tmp_pcmn, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 132/323: set version, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 133/323: Fix bug for updating, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 123/323: SQL : patch for new version CSS : install new theme, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 113/323: indent, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 134/323: Bug : cannot update payment, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 135/323: CSS : cosmetic menu, Dany De Bontridder, 2018/03/14
- [Noalyss-commit] [noalyss] 138/323: CSS : reduce height menu, Dany De Bontridder, 2018/03/14