2.1. Requêtes SQL

2.1.1. Before Starting

Before proceeding to actions described further, SQL-queries for this step should be performed.

2.1.2. Creating Table to Store System Users

CREATE TABLE t_users

(

user_id serial NOT NULL, -- Row ID

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Date and time when the row was created

dttmup timestamp with time zone, -- Date and time when the row was updated

dttmcl timestamp with time zone, -- Date and time when the row was closed

userid integer, -- ID of the user who created row

user_name text, -- User first name

user_surname text, -- User last name

user_login text, -- Login

user_password text, -- Password

user_signature text, -- User signature

CONSTRAINT pk_t_users_user_id PRIMARY KEY (user_id)

);

COMMENT ON TABLE t_users

IS 'Keeps users data';

COMMENT ON COLUMN t_users.user_id IS 'Row ID';

COMMENT ON COLUMN t_users.dttmcr IS 'Date and time when the row was created';

COMMENT ON COLUMN t_users.dttmup IS 'Date and time when the row was updated';

COMMENT ON COLUMN t_users.dttmcl IS 'Date and time when the row was closed';

COMMENT ON COLUMN t_users.userid IS 'ID of the user who created row';

COMMENT ON COLUMN t_users.user_name IS 'User first name';

COMMENT ON COLUMN t_users.user_surname IS 'User last name';

COMMENT ON COLUMN t_users.user_login IS 'Login';

COMMENT ON COLUMN t_users.user_password IS 'Password';

COMMENT ON COLUMN t_users.user_signature IS 'User signature'; 

2.1.3. Creating View to Display System Users

CREATE OR REPLACE VIEW vw_users AS 

SELECT t_users.user_id,

t_users.dttmcr,

t_users.dttmup,

t_users.dttmcl,

t_users.userid,

t_users.user_name,

t_users.user_surname,

(COALESCE(t_users.user_name, ''::text) || ' '::text) || COALESCE(t_users.user_surname, ''::text) AS user_fullname,

t_users.user_login,

t_users.user_password,

t_users.user_signature

FROM t_users;

2.1.4. Next

2.2. Configuration Development