4.1. Requêtes SQL
4.1.1. Before Starting
Before proceeding to actions described further, SQL-queries for this step should be performed.
4.1.2. Creating Table to Store Customers Data
CREATE TABLE t_clients
(
client_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
client_name text, -- Customer first name
client_surname text, -- Customer last name
CONSTRAINT pk_t_clients_client_id PRIMARY KEY (client_id)
);
COMMENT ON TABLE t_clients
IS 'Keeps customer data';
COMMENT ON COLUMN t_clients.client_id IS 'Row ID';
COMMENT ON COLUMN t_clients.dttmcr IS 'Date and time when the row was created';
COMMENT ON COLUMN t_clients.dttmup IS 'Date and time when the row was updated';
COMMENT ON COLUMN t_clients.dttmcl IS 'Date and time when the row was closed';
COMMENT ON COLUMN t_clients.userid IS 'ID of the user who created row';
COMMENT ON COLUMN t_clients.client_name IS 'Customer first name';
COMMENT ON COLUMN t_clients.client_surname IS 'Customer last name';
4.1.3. Creating View to Display Customers Data
CREATE OR REPLACE VIEW vw_clients AS
SELECT t_clients.client_id,
t_clients.dttmcr,
t_clients.dttmup,
t_clients.dttmcl,
t_clients.userid,
t_clients.client_name,
t_clients.client_surname,
(COALESCE(t_clients.client_name, ''::text) || ' '::text) || COALESCE(t_clients.client_surname, ''::text) AS client_fullname
FROM t_clients;
4.1.4. Creating Table to Store Status Types
CREATE TABLE t_status_types
(
status_type_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
status_type_name text, -- Status type
CONSTRAINT pk_t_status_types_status_type_id PRIMARY KEY (status_type_id)
);
COMMENT ON TABLE t_status_types
IS 'Keep status types data';
COMMENT ON COLUMN t_status_types.status_type_id IS 'Row ID';
COMMENT ON COLUMN t_status_types.dttmcr IS 'Date and time when the row was created';
COMMENT ON COLUMN t_status_types.dttmup IS 'Date and time when the row was updated';
COMMENT ON COLUMN t_status_types.dttmcl IS 'Date and time when the row was closed';
COMMENT ON COLUMN t_status_types.userid IS 'ID of the user who created row';
COMMENT ON COLUMN t_status_types.status_type_name IS 'Status type';
4.1.5. Creating Table to Store Comment Types
CREATE TABLE t_comment_types
(
comment_type_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
comment_type_name text, -- Comment type name
CONSTRAINT pk_t_comment_types_comment_type_id PRIMARY KEY (comment_type_id)
);
COMMENT ON TABLE t_comment_types
IS 'Keeps comment types';
COMMENT ON COLUMN t_comment_types.comment_type_id IS 'ID записи';
COMMENT ON COLUMN t_comment_types.dttmcr IS 'Date and time when the row was created';
COMMENT ON COLUMN t_comment_types.dttmup IS 'Date and time when the row was updated';
COMMENT ON COLUMN t_comment_types.dttmcl IS 'Date and time when the row was closed';
COMMENT ON COLUMN t_comment_types.userid IS 'ID of the user who created row';
COMMENT ON COLUMN t_comment_types.comment_type_name IS 'Comment type name';
4.1.6. Adding Status Types Used in the System
INSERT INTO t_status_types (dttmcr, dttmup, userid, status_type_name) VALUES
(now(), now(), 1, 'New'),
(now(), now(), 1, 'In progress'),
(now(), now(), 1, 'Answered'),
(now(), now(), 1, 'Closed');
4.1.7. Adding Comment Types Used in the System
INSERT INTO t_comment_types (dttmcr, dttmup, userid, comment_type_name) VALUES
(now(), now(), 1, 'Incoming mail'),
(now(), now(), 1, 'Outgoing mail'),
(now(), now(), 1, 'Comment');
4.1.8. Next