style: Apply SQL style guide to template files

NikolayS · claude · NikolayS · commit 889fdc398692 · 2025-09-29T18:06:44.000-07:00
Changed all SQL keywords to lowercase per project SQL style guide:
- create/grant/alter instead of CREATE/GRANT/ALTER
- select/from/where/join instead of SELECT/FROM/WHERE/JOIN
- Data types: timestamptz/text/jsonb instead of TIMESTAMPTZ/TEXT/JSONB
- PL/pgSQL keywords: declare/begin/end/if/return (lowercase)

Added explicit 'as' for aliases in queries.
Improved formatting for multi-line statements with proper indentation.

Follows .cursor/rules/development__db-sql-style-guide.mdc

Related to MR !56

Co-Authored-By: Claude &lt;noreply@anthropic.com&gt;
diff --git a/config/sink-postgres/init-template.sql b/config/sink-postgres/init-template.sql
@@ -1,79 +1,76 @@
--- Initialize PostgreSQL sink database for storing pgwatch measurements
--- This database will store all the monitoring metrics collected by PGWatch
+-- Initialize Postgres sink database for storing pgwatch measurements
+-- This database will store all the monitoring metrics collected by pgwatch
 -- Based on https://pgwat.ch/latest/howto/metrics_db_bootstrap.html
 
 -- Create the pgwatch role for measurements database
-CREATE ROLE ${PGWATCH_MONITOR_USER} WITH LOGIN PASSWORD '${PGWATCH_MONITOR_PASSWORD}';
+create role ${PGWATCH_MONITOR_USER} with login password '${PGWATCH_MONITOR_PASSWORD}';
 
 -- Create the measurements database owned by pgwatch
-CREATE DATABASE measurements OWNER ${PGWATCH_MONITOR_USER};
+create database measurements owner ${PGWATCH_MONITOR_USER};
 
 -- Switch to the measurements database context
 \c measurements;
 
 -- Create extensions that might be useful for metrics storage
-CREATE EXTENSION IF NOT EXISTS btree_gist;
-CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
+create extension if not exists btree_gist;
+create extension if not exists pg_stat_statements;
 
 -- Grant necessary permissions to pgwatch user
-GRANT ALL PRIVILEGES ON DATABASE measurements TO ${PGWATCH_MONITOR_USER};
-GRANT ALL PRIVILEGES ON SCHEMA public TO ${PGWATCH_MONITOR_USER};
+grant all privileges on database measurements to ${PGWATCH_MONITOR_USER};
+grant all privileges on schema public to ${PGWATCH_MONITOR_USER};
 
-
-
--- Create a partitioned table for queryid-to-query mappings with LIST partitioning by dbname
-CREATE TABLE IF NOT EXISTS public.pgss_queryid_queries (
-    time TIMESTAMPTZ NOT NULL,
-    dbname TEXT NOT NULL,
-    data JSONB NOT NULL,
-    tag_data JSONB
-) PARTITION BY LIST (dbname);
+-- Create a partitioned table for queryid-to-query mappings with list partitioning by dbname
+create table if not exists public.pgss_queryid_queries (
+  time timestamptz not null,
+  dbname text not null,
+  data jsonb not null,
+  tag_data jsonb
+) partition by list (dbname);
 
 -- Create indexes for efficient lookups
-CREATE INDEX IF NOT EXISTS pgss_queryid_queries_dbname_time_idx ON public.pgss_queryid_queries (dbname, time);
-
--- Use existing subpartitions schema
-
+create index if not exists pgss_queryid_queries_dbname_time_idx
+  on public.pgss_queryid_queries (dbname, time);
 
 -- Set ownership and grant permissions to pgwatch
-ALTER TABLE public.pgss_queryid_queries OWNER TO ${PGWATCH_MONITOR_USER};
-GRANT ALL PRIVILEGES ON TABLE public.pgss_queryid_queries TO ${PGWATCH_MONITOR_USER};
--- Ensure pgwatch can use sequences (if any are created)
-GRANT USAGE ON SCHEMA public TO ${PGWATCH_MONITOR_USER};
--- Grant permissions on all future tables in public schema
-ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO ${PGWATCH_MONITOR_USER};
-
-CREATE OR REPLACE FUNCTION enforce_queryid_uniqueness()
-RETURNS TRIGGER AS $$
-DECLARE
-    queryid_value TEXT;
-BEGIN
-    -- Extract queryid from the data JSONB
-    queryid_value := NEW.data->>'queryid';
-
-    -- Allow NULL queryids through
-    IF queryid_value IS NULL THEN
-        RETURN NEW;
-    END IF;
-
-    -- Silently skip if duplicate exists
-    IF EXISTS (
-        SELECT 1
-        FROM pgss_queryid_queries
-        WHERE dbname = NEW.dbname
-          AND data->>'queryid' = queryid_value
-        LIMIT 1
-    ) THEN
-        RETURN NULL;  -- Cancels INSERT silently
-    END IF;
-
-    RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
+alter table public.pgss_queryid_queries owner to ${PGWATCH_MONITOR_USER};
+grant all privileges on table public.pgss_queryid_queries to ${PGWATCH_MONITOR_USER};
 
+-- Ensure pgwatch can use sequences (if any are created)
+grant usage on schema public to ${PGWATCH_MONITOR_USER};
 
-CREATE OR REPLACE TRIGGER enforce_queryid_uniqueness_trigger
-    BEFORE INSERT
-    ON pgss_queryid_queries
-    FOR EACH ROW
-    EXECUTE FUNCTION enforce_queryid_uniqueness();
+-- Grant permissions on all future tables in public schema
+alter default privileges in schema public grant all on tables to ${PGWATCH_MONITOR_USER};
+
+create or replace function enforce_queryid_uniqueness()
+returns trigger as $$
+declare
+  queryid_value text;
+begin
+  -- Extract queryid from the data jsonb
+  queryid_value := new.data->>'queryid';
+
+  -- Allow null queryids through
+  if queryid_value is null then
+    return new;
+  end if;
+
+  -- Silently skip if duplicate exists
+  if exists (
+    select 1
+    from pgss_queryid_queries
+    where
+      dbname = new.dbname
+      and data->>'queryid' = queryid_value
+    limit 1
+  ) then
+    return null;  -- Cancels insert silently
+  end if;
+
+  return new;
+end;
+$$ language plpgsql;
+
+create or replace trigger enforce_queryid_uniqueness_trigger
+  before insert on pgss_queryid_queries
+  for each row
+  execute function enforce_queryid_uniqueness();
diff --git a/config/target-db/init-template.sql b/config/target-db/init-template.sql
@@ -1,49 +1,53 @@
 -- Initialize target database for monitoring
 -- Enable pg_stat_statements extension for query monitoring
-CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
+create extension if not exists pg_stat_statements;
 
 -- Create a sample table for demonstration
-CREATE TABLE IF NOT EXISTS sample_data (
-    id SERIAL PRIMARY KEY,
-    name VARCHAR(100),
-    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+create table if not exists sample_data (
+  id serial primary key,
+  name varchar(100),
+  created_at timestamp default current_timestamp
 );
 
 -- Insert some sample data
-INSERT INTO sample_data (name) VALUES
-    ('Sample Record 1'),
-    ('Sample Record 2'),
-    ('Sample Record 3');
+insert into sample_data (name)
+values
+  ('Sample Record 1'),
+  ('Sample Record 2'),
+  ('Sample Record 3');
 
--- Create a user for PGWatch monitoring
-CREATE USER ${TARGET_MONITOR_USER} WITH PASSWORD '${TARGET_MONITOR_PASSWORD}';
-GRANT CONNECT ON DATABASE target_database TO ${TARGET_MONITOR_USER};
-GRANT USAGE ON SCHEMA public TO ${TARGET_MONITOR_USER};
+-- Create a user for pgwatch monitoring
+create user ${TARGET_MONITOR_USER} with password '${TARGET_MONITOR_PASSWORD}';
+grant connect on database target_database to ${TARGET_MONITOR_USER};
+grant usage on schema public to ${TARGET_MONITOR_USER};
 
 -- Create a public view for pg_statistic access
-CREATE OR REPLACE VIEW public.pg_statistic AS
-SELECT
-    n.nspname as schemaname,
-    c.relname as tablename,
-    a.attname,
-    s.stanullfrac as null_frac,
-    s.stawidth as avg_width,
-    false as inherited
-FROM pg_statistic s
-JOIN pg_class c ON c.oid = s.starelid
-JOIN pg_namespace n ON n.oid = c.relnamespace
-JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
-WHERE a.attnum > 0 AND NOT a.attisdropped;
+create or replace view public.pg_statistic as
+select
+  n.nspname as schemaname,
+  c.relname as tablename,
+  a.attname,
+  s.stanullfrac as null_frac,
+  s.stawidth as avg_width,
+  false as inherited
+from pg_statistic as s
+join pg_class as c on c.oid = s.starelid
+join pg_namespace as n on n.oid = c.relnamespace
+join pg_attribute as a on a.attrelid = s.starelid and a.attnum = s.staattnum
+where
+  a.attnum > 0
+  and not a.attisdropped;
 
 -- Grant specific access instead of all tables
-GRANT SELECT ON public.pg_statistic TO pg_monitor;
+grant select on public.pg_statistic to pg_monitor;
 
 -- Grant access to monitoring views
-GRANT SELECT ON pg_stat_statements TO ${TARGET_MONITOR_USER};
-GRANT SELECT ON pg_stat_database TO ${TARGET_MONITOR_USER};
-GRANT SELECT ON pg_stat_user_tables TO ${TARGET_MONITOR_USER};
+grant select on pg_stat_statements to ${TARGET_MONITOR_USER};
+grant select on pg_stat_database to ${TARGET_MONITOR_USER};
+grant select on pg_stat_user_tables to ${TARGET_MONITOR_USER};
+
 -- Grant pg_monitor role to monitor user for enhanced monitoring capabilities
-GRANT pg_monitor TO ${TARGET_MONITOR_USER};
+grant pg_monitor to ${TARGET_MONITOR_USER};
 
 -- Set search path for the monitor user
-ALTER USER ${TARGET_MONITOR_USER} SET search_path = "$user", public, pg_catalog;
+alter user ${TARGET_MONITOR_USER} set search_path = "$user", public, pg_catalog;
