Created
July 19, 2023 19:23
-
-
Save paoliniluis/06f309e6535c1089fadb766e996e1cad to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE IF NOT EXISTS public.activity_history ( | |
id integer, | |
topic character varying(32) not null, | |
timestamp timestamp with time zone not null, | |
user_id integer, | |
model character varying(16), | |
model_id integer, | |
database_id integer, | |
table_id integer, | |
custom_id character varying(48), | |
details text not null | |
); | |
CREATE TABLE IF NOT EXISTS public.application_permissions_revision_history ( | |
id integer, | |
before text not null, | |
after text not null, | |
user_id integer not null, | |
created_at timestamp without time zone not null, | |
remark text | |
); | |
CREATE TABLE IF NOT EXISTS public.collection_permission_graph_revision_history ( | |
id integer, | |
before text not null, | |
after text not null, | |
user_id integer not null, | |
created_at timestamp without time zone not null, | |
remark text | |
); | |
CREATE TABLE IF NOT EXISTS public.core_session_history ( | |
id character varying(254), | |
user_id integer not null, | |
created_at timestamp with time zone not null, | |
anti_csrf_token text | |
); | |
CREATE TABLE IF NOT EXISTS public.login_history ( | |
id integer, | |
timestamp timestamp with time zone not null , | |
user_id integer not null, | |
session_id character varying(254), | |
device_id character(36) not null, | |
device_description text not null, | |
ip_address text not null | |
); | |
CREATE TABLE IF NOT EXISTS public.query_history ( | |
query_hash bytea not null, | |
average_execution_time integer not null, | |
query text | |
); | |
CREATE TABLE IF NOT EXISTS public.query_execution_history ( | |
id integer , | |
hash bytea not null, | |
started_at timestamp with time zone not null, | |
running_time integer not null, | |
result_rows integer not null, | |
native boolean not null, | |
context character varying(32), | |
error text, | |
executor_id integer, | |
card_id integer, | |
dashboard_id integer, | |
pulse_id integer, | |
database_id integer, | |
cache_hit boolean | |
); | |
CREATE TABLE IF NOT EXISTS public.revision_history ( | |
id integer, | |
model character varying(16) not null, | |
model_id integer not null, | |
user_id integer not null, | |
timestamp timestamp with time zone not null, | |
object text not null, | |
is_reversion boolean not null default false, | |
is_creation boolean not null default false, | |
message text | |
); | |
CREATE TABLE IF NOT EXISTS public.task_history_history ( | |
id integer, | |
task character varying(254) not null, | |
db_id integer, | |
started_at timestamp with time zone not null, | |
ended_at timestamp with time zone not null, | |
duration integer not null, | |
task_details text | |
); | |
CREATE TABLE IF NOT EXISTS public.view_log_history ( | |
id integer, | |
user_id integer, | |
model character varying(16) not null, | |
model_id integer not null, | |
timestamp timestamp with time zone not null, | |
metadata text | |
); | |
-- This needs to be run as a cron job every 30 days | |
INSERT INTO public.activity_history (SELECT * FROM public.activity WHERE "timestamp" < NOW() - INTERVAL '30 day'); | |
DELETE FROM public.activity WHERE "timestamp" < NOW() - INTERVAL '30 day'; | |
VACUUM FULL public.activity; | |
INSERT INTO public.application_permissions_revision_history (SELECT * FROM public.application_permissions_revision WHERE "created_at" < NOW() - INTERVAL '30 day'); | |
DELETE FROM public.application_permissions_revision WHERE "created_at" < NOW() - INTERVAL '30 day'; | |
VACUUM FULL public.application_permissions_revision; | |
INSERT INTO public.collection_permission_graph_revision_history (SELECT * FROM public.collection_permission_graph_revision WHERE "created_at" < NOW() - INTERVAL '30 day'); | |
DELETE FROM public.collection_permission_graph_revision WHERE "created_at" < NOW() - INTERVAL '30 day'; | |
VACUUM FULL public.collection_permission_graph_revision; | |
INSERT INTO public.core_session_history (SELECT * FROM public.core_session WHERE "created_at" < NOW() - INTERVAL '30 day'); | |
DELETE FROM public.core_session WHERE "created_at" < NOW() - INTERVAL '30 day'; | |
VACUUM FULL public.core_session; | |
INSERT INTO public.login_history_history (SELECT * FROM public.login_history WHERE "timestamp" < NOW() - INTERVAL '30 day'); | |
DELETE FROM public.login_history WHERE "timestamp" < NOW() - INTERVAL '30 day'; | |
VACUUM FULL public.login_history; | |
INSERT INTO public.query_execution_history (SELECT * FROM public.query_execution WHERE "started_at" < NOW() - INTERVAL '30 day'); | |
DELETE FROM public.query_execution WHERE "started_at" < NOW() - INTERVAL '30 day'; | |
VACUUM FULL public.query_execution; | |
INSERT INTO public.revision_history (SELECT * FROM public.revision WHERE "timestamp" < NOW() - INTERVAL '30 day'); | |
DELETE FROM public.revision WHERE "timestamp" < NOW() - INTERVAL '30 day'; | |
VACUUM FULL public.revision; | |
INSERT INTO public.task_history_history (SELECT * FROM public.task_history WHERE "ended_at" < NOW() - INTERVAL '30 day'); | |
DELETE FROM public.task_history WHERE "ended_at" < NOW() - INTERVAL '30 day'; | |
VACUUM FULL public.task_history; | |
INSERT INTO public.view_log_history (SELECT * FROM public.view_log WHERE "timestamp" < NOW() - INTERVAL '30 day'); | |
DELETE FROM public.view_log WHERE "timestamp" < NOW() - INTERVAL '30 day'; | |
VACUUM FULL public.view_log; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment