templates
table
id | content |
---|---|
1 | Hey there |
2 | Good morning |
3 | Great work |
notifications
table
ALTER TABLE public.\\"user_to_service\\" ADD CONSTRAINT \\"uix_user_to_service\\" UNIQUE (user_id, service_id); | |
ALTER TABLE public.\\"user_to_organisation\\" ADD CONSTRAINT \\"uix_user_to_organisation\\" UNIQUE (user_id, organisation_id); | |
ALTER TABLE public.\\"template_folder\\" ADD CONSTRAINT \\"ix_id_service_id\\" UNIQUE (id, service_id); | |
ALTER TABLE public.\\"services\\" ADD CONSTRAINT \\"services_name_key\\" UNIQUE (name); | |
ALTER TABLE public.\\"services\\" ADD CONSTRAINT \\"services_email_from_key\\" UNIQUE (email_from); | |
ALTER TABLE public.\\"service_data_retention\\" ADD CONSTRAINT \\"uix_service_data_retention\\" UNIQUE (service_id, notification_type); | |
ALTER TABLE public.\\"service_callback_api\\" ADD CONSTRAINT \\"uix_service_callback_type\\" UNIQUE (service_id, callback_type); | |
ALTER TABLE public.\\"returned_letters\\" ADD CONSTRAINT \\"returned_letters_notification_id_key\\" UNIQUE (notification_id); | |
ALTER TABLE public.\\"permissions\\" ADD CONSTRAINT \\"uix_service_user_permission\\" UNIQUE (service_ |
ALTER TABLE public.annual_billing ADD CONSTRAINT "annual_billing_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id); | |
ALTER TABLE public.annual_billing ADD CONSTRAINT "uix_service_id_financial_year_start" UNIQUE (service_id, financial_year_start); | |
ALTER TABLE public.api_keys ADD CONSTRAINT "api_keys_key_type_fkey" FOREIGN KEY (key_type) REFERENCES key_types(name); | |
ALTER TABLE public.api_keys ADD CONSTRAINT "api_keys_secret_key" UNIQUE (secret); | |
ALTER TABLE public.api_keys ADD CONSTRAINT "api_keys_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id); | |
ALTER TABLE public.api_keys ADD CONSTRAINT "fk_api_keys_created_by_id" FOREIGN KEY (created_by_id) REFERENCES users(id); | |
ALTER TABLE public.broadcast_event ADD CONSTRAINT "broadcast_event_broadcast_message_id_fkey" FOREIGN KEY (broadcast_message_id) REFERENCES broadcast_message(id); | |
ALTER TABLE public.broadcast_event ADD CONSTRAINT "broadcast_event_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id); | |
ALTER TABLE public.broa |
templates
table
id | content |
---|---|
1 | Hey there |
2 | Good morning |
3 | Great work |
notifications
table
2023-02-17 14:38:39 UTC:10.0.84.9(46296):notifydb@notifydb:[12139]:ERROR: relation "awsdms_apply_exceptions" does not exist at character 22 | |
2023-02-17 14:38:39 UTC:10.0.84.9(46296):notifydb@notifydb:[12139]:STATEMENT: SELECT COUNT(*) FROM "awsdms_apply_exceptions" | |
2023-02-17 14:39:41 UTC::@:[370]:LOG: checkpoint starting: xlog | |
2023-02-17 14:40:03 UTC:10.0.84.9(53214):notifydb@notifydb:[14211]:ERROR: duplicate key value violates unique constraint "spatial_ref_sys_pkey" | |
2023-02-17 14:40:03 UTC:10.0.84.9(53214):notifydb@notifydb:[14211]:DETAIL: Key (srid)=(3819) already exists. | |
2023-02-17 14:40:03 UTC:10.0.84.9(53214):notifydb@notifydb:[14211]:CONTEXT: COPY spatial_ref_sys, line 1 | |
2023-02-17 14:40:03 UTC:10.0.84.9(53214):notifydb@notifydb:[14211]:STATEMENT: COPY "public"."spatial_ref_sys" FROM STDIN WITH DELIMITER ',' CSV NULL 'attNULL' ESCAPE '\' | |
2023-02-17 14:40:27 UTC::@:[370]:LOG: checkpoint complete: wrote 103556 buffers (5.1%); 0 WAL file(s) added, 13 removed, 17 recycled; write=46.066 s, sync=0.100 s, tot |
select | |
users.id, | |
users.name, | |
users.email_address, | |
users.logged_in_at as last_login, | |
count_of_logins.num_of_logins, | |
count(distinct services.id) as num_live_services_member_of, | |
count(distinct organisation.id) as num_of_orgs_member_of, | |
count(distinct organisation.organisation_type) as num_of_org_types_member_of, | |
count(CASE WHEN permissions.permission = 'manage_settings' THEN 1 END) as num_of_live_services_manager_of, |
-- We are aware that this query can return duplicate users (ie you would send an email twice to the same person). | |
-- This is if they have two services that have sent the same number of notifications. | |
-- They should be manually removed from the data before sending | |
select | |
users.id, | |
users.name, | |
users.email_address, | |
users.logged_in_at as last_login, | |
count_of_logins.num_of_logins, |
from datetime import date, timedelta | |
start_date = date(2016, 5, 17) | |
stop_date = date(2019, 9, 30) | |
date_to_process = start_date | |
while date_to_process <= stop_date: | |
print(f""" |