5.1. Requêtes SQL

 

5.1.1. Before Starting

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

 

 

5.1.2. Creating a Sequence for Assigning a Number to a Ticket

CREATE SEQUENCE t_objects_object_ref_nr_seq

INCREMENT 1

MINVALUE 1

MAXVALUE 9223372036854775807

START 300001

CACHE 1;

 

 

5.1.3. Creating a Table for Storing Ticket Data 

CREATE TABLE t_objects

(

object_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

object_ref_nr integer DEFAULT nextval('t_objects_object_ref_nr_seq'::regclass), -- Ticket number

object_cur_status_id integer, -- Current status ID - this is updated with trigger when a new status is inserted into t_statuses table

client_id integer, -- Customer ID - FK t_clients.client_id

object_to_id integer, -- Employee ID - FK t_users.user_id

object_remark text, -- Ticket comment

CONSTRAINT pk_t_objects_object_id PRIMARY KEY (object_id)

);

COMMENT ON TABLE t_objects

IS 'Keeps tickets';

COMMENT ON COLUMN t_objects.object_id IS 'Row ID';

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

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

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

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

COMMENT ON COLUMN t_objects.object_ref_nr IS 'Ticket number';

COMMENT ON COLUMN t_objects.object_cur_status_id IS 'Current status ID - this is updated with trigger when a new status is inserted into t_statuses table';

COMMENT ON COLUMN t_objects.client_id IS 'Customer ID - FK t_clients.client_id';

COMMENT ON COLUMN t_objects.object_to_id IS 'Employee ID - FK t_users.user_id';

COMMENT ON COLUMN t_objects.object_remark IS 'Ticket comment';  

 

 

5.1.4. Creating a View to Display Tickets

CREATE OR REPLACE VIEW vw_objects AS 

SELECT o.object_id,

o.dttmcr,

o.dttmup,

o.dttmcl,

o.userid,

o.object_ref_nr,

o.object_cur_status_id,

o.object_remark,

st.status_type_name,

(COALESCE(c.client_name, ''::text) || ' '::text) || COALESCE(c.client_surname, ''::text) AS client_fullname,

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

FROM t_objects o

LEFT JOIN t_status_types st ON o.object_cur_status_id = st.status_type_id

LEFT JOIN t_clients c USING (client_id)

LEFT JOIN t_users u ON o.object_to_id = u.user_id;

 

 

5.1.5. Creating a Table for Storing Ticket Statuses 

CREATE TABLE t_statuses

(

status_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

object_id integer NOT NULL, -- Ticket ID - FK t_objects.object_id

status_type_id integer NOT NULL, -- Status type ID

CONSTRAINT pk_t_statuses_status_id PRIMARY KEY (status_id)

);

COMMENT ON TABLE t_statuses

IS 'Keeps ticket statuses';

COMMENT ON COLUMN t_statuses.status_id IS 'Row ID';

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

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

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

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

COMMENT ON COLUMN t_statuses.object_id IS 'Ticket ID - FK t_objects.object_id';

COMMENT ON COLUMN t_statuses.status_type_id IS 'Status type ID';  

 

 

5.1.6. Create a View to Display Ticket Statuses

CREATE OR REPLACE VIEW vw_statuses AS 

SELECT s.status_id,

s.dttmcr,

s.dttmup,

s.dttmcl,

s.userid,

s.object_id,

st.status_type_name

FROM t_statuses s

LEFT JOIN t_status_types st USING (status_type_id);

 

 

5.1.7. Creating a Table for Storing Ticket Comments

CREATE TABLE t_comments

(

comment_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

object_id integer, -- Ticket ID - FK t_objects.object_id

comment_type_id integer NOT NULL, -- Comment type ID - FK t_comment_type.comment_type_id

comment_from text, -- From

comment_to text, -- To

comment_cc text, -- Cc

comment_bcc text, -- Bcc

comment_subject text, -- Subject

comment_text text, -- Body

comment_diag_mes text, -- System message which is generated upon subject analysis when a message is uploaded to the system

CONSTRAINT pk_t_comments_comment_id PRIMARY KEY (comment_id)

);

COMMENT ON TABLE t_comments

IS 'Keeps ticket comments';

COMMENT ON COLUMN t_comments.comment_id IS 'Row ID';

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

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

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

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

COMMENT ON COLUMN t_comments.object_id IS 'Ticket ID - FK t_objects.object_id';

COMMENT ON COLUMN t_comments.comment_type_id IS 'Comment type ID - FK t_comment_type.comment_type_id';

COMMENT ON COLUMN t_comments.comment_from IS 'From';

COMMENT ON COLUMN t_comments.comment_to IS 'To';

COMMENT ON COLUMN t_comments.comment_cc IS 'Cc';

COMMENT ON COLUMN t_comments.comment_bcc IS 'Bcc';

COMMENT ON COLUMN t_comments.comment_subject IS 'Subject';

COMMENT ON COLUMN t_comments.comment_text IS 'Body';

COMMENT ON COLUMN t_comments.comment_diag_mes IS 'System message which is generated upon subject analysis when a message is uploaded to the system'; 

 

 

5.1.8. Creating a Table for Storing Files

CREATE TABLE t_files

(

file_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_id integer, -- Comment ID - FK t_comments.comment_id

comment_file bytea, -- File

CONSTRAINT pk_t_files_file_id PRIMARY KEY (file_id)

);

COMMENT ON TABLE t_files

IS 'Keeps files';

COMMENT ON COLUMN t_files.file_id IS 'Row ID';

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

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

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

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

COMMENT ON COLUMN t_files.comment_id IS 'Comment ID - FK t_comments.comment_id';

COMMENT ON COLUMN t_files.comment_file IS 'File';

 

 

5.1.9. Creating a View to Display Ticket Comments

CREATE OR REPLACE VIEW vw_comments AS 

SELECT c.comment_id,

c.dttmcr,

c.dttmup,

c.dttmcl,

c.userid,

c.object_id,

c.comment_type_id,

c.comment_from,

c.comment_to,

c.comment_cc,

c.comment_bcc,

c.comment_subject,

c.comment_diag_mes,

c.comment_text,

ct.comment_type_name,

CASE

WHEN (( SELECT DISTINCT t_files.comment_id

FROM t_files

WHERE t_files.comment_id = c.comment_id AND t_files.dttmcl IS NULL)) IS NULL THEN '-'::text

ELSE 'Yes'::text

END AS comment_file_present

FROM t_comments c

LEFT JOIN t_comment_types ct USING (comment_type_id);

 

 

5.1.10. Next

5.2. Creating Object 1001 Tickets (base)  

© ProjectDB, 1996 — 2024