Refactor critical Resql queries

[turnerrainer]

AS AN Architect
I WANT some of Bürokratt's current critical Resql queries to be refactored
SO THAT they wouldn't crash the system

Acceptance Criteria

• No JOIN queries can be used in SQL queries
• No UPDATE queries can be used in SQL queries
• No DELETE queries can be used in SQL queries
• Only INSERT and SELECT queries can be used in SQL queries
• Only denormalized databases can be used
• Must specify requested selectors, SELECT * is not allowed
• Every request must contain LIMIT from, to when listing results
• Reduce JOIN queries to minimum
• Database schema changes are out of scope of this issue

Scope of work

Chatbot

• Refactor get-cs-all-active-chats.sql Postgres query #564
• Refactor get-cs-all-ended-chats.sql Postgres query #565
• Refactor get-cs-all-pending-chats.sql Postgres query #566
• Refactor get-cs-all-unavailable-ended-chats.sql Postgres query #567
• Refactor all Chatbot's Postgres queries to replace SELECT * with SELECT {field_name} #583
• Refactor all Postgres Chatbot's queries to replace UPDATE queries with INSERT and SELECT #584
• Refactor all Postgres queries to replace DELETE queries with INSERT and SELECT #585

Analytics Module

• Refactor csa-chat-forwards.sql Postgres query Analytics-Module#213
• Refactor feedback-chats-with-negative-feedback.sql Postgres query Analytics-Module#214
• Refactor all Analytics Module's Postgres queries to replace SELECT * with SELECT {field_name} Analytics-Module#222
• Refactor all Analytics Module's Postgres queries to replace UPDATE queries with INSERT and SELECT Analytics-Module#223
• Refactor all Analytics Module's Postgres queries to replace DELETE queries with INSERT and SELECT Analytics-Module#224

Training Module

• Refactor all Training Module's Postgres queries to replace SELECT * with SELECT {field_name} Training-Module#486
• Refactor all Training Module's Postgres queries to replace UPDATE queries with INSERT and SELECT Training-Module#487
• Refactor all Training Module's Postgres queries to replace DELETE queries with INSERT and SELECT Training-Module#488

Service Module

• Refactor all Service Module's Postgres queries to replace SELECT * with SELECT {field_name} Service-Module#276
• Refactor all Service Module's Postgres queries to replace UPDATE queries with INSERT and SELECT Service-Module#277
• Refactor all Service Module's Postgres queries to replace DELETE queries with INSERT and SELECT Service-Module#278

[1AhmedYasser]

@turnerrainer @baha-a

Most Critical Queries:

Chatbot Module

1. get-cs-all-active-chats.sql
2. get-cs-all-ended-chats.sql
3. get-cs-all-pending-chats.sql
4. get-cs-all-unavailable-ended-chats.sql

Analytics Module

1. csa-chat-forwards.sql
2. feedback-chats-with-negative-feedback.sql

Notes:

1. Check for any select * and refactor it to use select {field_name}
2. The service module has some queries that use update and delete, we need to make sure only insert and select are used.