Create Challenge Log Table And Triggers

 

Logstash will poll Informix on regular intervals (something between 1 and 5 minutes), and every time a challenge is inserted, updated or deleted, we need to insert, update, or delete that information from/to the ElasticSearch index.

 

Our data structure in ElasticSearch goes beyond the challenge data (project and project info tables), it includes phases, members with their respective roles (submitter, observer, copilot, reviewer, etc.), prizes, etc.

 

For instance, a challenge object will have an array that represents the list of members who are registered for the challenge. When an insertion happens in the resource table for that challenge, we need to add that user as a resource to the member list in the challenge record in ElasticSearch. If a member is unregistered for a challenge, we need to remove that entry.

 

Handling deletes is also challenging, since the deleted record simply doesn't exist in the source database anymore.

To handle all of this we'd like to have a new table called project_event_log in tcs_catalog database with the following structure:

 

PROJECT_ID: Long PK referencing project.project_id

OPERATION: String representing the operation that is being performed on the challenge: INSERT, UPDATE, or DELETE

DATE: date / time at which the operation happened

SOURCE: String with the name of the source table in which the change happened, this could be the resource table in case of a new person registering for a challenge, the project_info table in case the challenge name changed, phase in case the phase changed from submission to review etc.

 

Please define the project_event_log table properly, then add triggers to the project_info, project, project_phase, resource, prize, project_spec, project_studio_specification on insert, update, and delete to populate a record in the project_event_log table for each operation that happens.

 

Ideally, please use a stored procedure to centralize common functionality and make the triggers simpler.

 

 

The changes should be applied on https://coder.topcoder.com/internal/database/scripts/trunk (Please just use the revision provided in VM or Docker).

 

 

Competitors are expected to use the Informix Docker to make the changes and test your solutions. You are expected to provide all the test data and steps to make sure all the triggers are working properly

 

 

1. docker run -it -P 2020:2020 mdesiderio/arena:informix bash
2. Inside the container, switch to informix user: sudo su - informix
3. in the informix user's home folder, run the start-informix.sh script
4. you can now use dbaccess to use informix.