Validate PostgreSQL triggers, Clojure example

validate-postgresql-triggers.clj

(ns postgresql-util
  "Common db validations"
  (:require [korma.core :as sql]))

(defn validate-triggers
 "Checks that all tables in the database, except for Flyway's schema table, have triggers enabled."
 []
 (let [flyway-table "schema_version"
       invalid-tables (sql/exec-raw
                          (str "select table_name from information_schema.tables"
                            " where not exists ("
                            " select * from pg_class left outer join pg_trigger on tgrelid=pg_class.oid"
                            " where tgtype in(7,19) and relname = table_name) "
                            " and table_type='BASE TABLE' and table_schema='public' "
                            " and table_name != '" flyway-table "'"
                            " order by table_name") :results)]
   (when-not  (empty? invalid-tables)
     (println ".. perhaps you might want to write something like this?")
     (doseq [table-map invalid-tables]
       (let [table (:table_name table-map)]
         (println (str "alter table " table " add column change_user varchar(80) NOT NULL references user(oid);"))
         (println (str "alter table " table " add column create_user varchar(80) NOT NULL references user(oid);"))
         (println (str "alter table " table " add column changetime timestamptz NOT NULL;"))
         (println (str "alter table " table " add column createtime timestamptz NOT NULL;"))
         (println (str "create trigger " table "_update before update on " table " for each row execute procedure update_stamp() ;"))
         (println (str "create trigger " table "l_insert before insert on " table " for each row execute procedure update_created() ;"))
         (println (str "create trigger " table "m_insert before insert on " table " for each row execute procedure update_stamp() ;"))
         (println (str "create trigger " table "_mu_update before update on " table " for each row execute procedure update_modifier() ;"))
         (println (str "create trigger " table "_mu_insert before insert on " table " for each row execute procedure update_modifier() ;"))
         (println (str "create trigger " table "_cu_insert before insert on " table " for each row execute procedure update_creator() ;")))))
 invalid-tables))