Database creation exercise , consisting on creating a DB, with triggers for password encryption, views and backup jobs
List of instructions
##Create user CREATE USER myuser WITH PASSWORD 'password'; ##Create Database CREATE DATABASE challenge WITH OWNER postgres; ##Create Tables CREATE TABLE "User" ( id uuid PRIMARY KEY, name varchar(50), email varchar(20), password varchar(100), "createdAt" timestamp DEFAULT current_timestamp, "updatedAt" timestamp DEFAULT current_timestamp );
CREATE TABLE "Team" ( id uuid PRIMARY KEY, name varchar(50), "createdBy" uuid, Constraint team_user_fkey FOREIGN KEY ("createdBy") REFERENCES "User"(id), "createdAt" timestamp DEFAULT current_timestamp, "updatedAt" timestamp DEFAULT current_timestamp );
CREATE TABLE "Account" ( id uuid PRIMARY KEY, name varchar(50), "clientName" varchar(50), "leaderName" varchar(50), team uuid, Constraint account_team_fkey FOREIGN KEY ("team") REFERENCES "Team"(id), "createdAt" timestamp DEFAULT current_timestamp, "updatedAt" timestamp DEFAULT current_timestamp
);
CREATE TABLE "UserTeam" ( id uuid Primary Key, "userId" uuid, "teamId" uuid, "createdAt" timestamp DEFAULT current_timestamp, "updatedAt" timestamp DEFAULT current_timestamp, Constraint user_fk FOREIGN KEY("userId") REFERENCES "User"(id), Constraint team_fk FOREIGN KEY("teamId") REFERENCES "Team"(id) );
CREATE TABLE "MovementLog" ( id uuid Primary Key, "userId" uuid, "teamId" uuid, "movementType" varchar(10) NOT NULL check( "movementType" IN ('INSERT', 'DELETE')), "createdAt" timestamp DEFAULT current_timestamp, "updatedAt" timestamp DEFAULT current_timestamp, Constraint user_fk FOREIGN KEY("userId") REFERENCES "User"(id), Constraint team_fk FOREIGN KEY("teamId") REFERENCES "Team"(id) );
##Create View CREATE VIEW "UserTeamHistory" AS SELECT * FROM public."UserTeam"
##Create Function/Tigger CREATE OR REPLACE FUNCTION hash_password() RETURNS trigger AS $$ BEGIN IF tg_op ='INSERT' OR tg_op = 'UPDATE' THEN NEW.password = MD5(NEW.password || NEW.id); RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER password_hash BEFORE INSERT OR UPDATE Of password ON public."User" FOR EACH ROW EXECUTE PROCEDURE hash_password();
##Create Log table procedure
CREATE OR REPLACE FUNCTION if_modified_func() RETURNS trigger AS
EXCEPTION
WHEN data_exception THEN
RAISE WARNING '[IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN unique_violation THEN
RAISE WARNING '[IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN others THEN
RAISE WARNING '[IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
DROP TRIGGER IF EXISTS movement_log_trigger ON "UserTeam"; CREATE TRIGGER movement_log_trigger AFTER INSERT OR UPDATE OR DELETE ON "UserTeam" FOR EACH ROW EXECUTE PROCEDURE if_modified_func();
##Create Backup Job in terminal ##Note: this works in Unix/Linux based OS, if you are using something differente you can use the pgAgent extension of pgAdmin crontab -e
00 00 * * * /usr/local/bin/pg_dump -U postgres challenge > backup.sql
##create .pgpass file
hostname:port:database:username:password
##give it permisssions chmod 600 .pgpass
export PGPASSFILE=~/.pgpass