sql.js

/**
 * Centralized SQL. Dynamic parts (activity `backend_type`, pg_stat_statements time columns)
 * follow flags from `getServerInfo()` in `version.js`.
 * PG13+ uses total_exec_time / mean_exec_time; PG12 and older use total_time / mean_time.
 */

/** Check pg_stat_statements extension */
export const SQL_HAS_STATEMENTS_EXT = `
  SELECT EXISTS (
    SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements'
  ) AS has_ext
`;

/**
 * @param {{ hasBackendType: boolean }} info from getServerInfo()
 */
export function buildActivitySql(info) {
  const backend = info.hasBackendType
    ? "a.backend_type"
    : "NULL::text AS backend_type";
  return `
  SELECT
    a.pid,
    a.usename,
    a.state,
    LEFT(a.query, 2048) AS query,
    a.query_start,
    EXTRACT(EPOCH FROM (clock_timestamp() - a.query_start))::double precision AS duration_sec,
    a.wait_event_type,
    a.wait_event,
    ${backend}
  FROM pg_stat_activity a
  WHERE a.datname = current_database()
    AND a.pid <> pg_backend_pid()
  ORDER BY a.query_start NULLS LAST
  LIMIT 60
`;
}

/**
 * @param {{ hasTotalExecTimeColumns: boolean }} info from getServerInfo()
 */
export function buildStatementsSql(info) {
  const timeSelect = info.hasTotalExecTimeColumns
    ? `s.total_exec_time::double precision AS total_exec_time,
    s.mean_exec_time::double precision AS mean_exec_time`
    : `s.total_time::double precision AS total_exec_time,
    s.mean_time::double precision AS mean_exec_time`;
  const orderBy = info.hasTotalExecTimeColumns
    ? "s.total_exec_time DESC"
    : "s.total_time DESC";

  return `
  SELECT
    s.userid,
    s.dbid,
    s.queryid,
    LEFT(s.query, 2048) AS query,
    s.calls::bigint AS calls,
    ${timeSelect},
    s.rows::bigint AS rows,
    s.shared_blks_hit::bigint AS shared_blks_hit,
    s.shared_blks_read::bigint AS shared_blks_read
  FROM pg_stat_statements s
  WHERE s.dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  ORDER BY ${orderBy}
  LIMIT 40
`;
}

/** Blocking relationships — classic pg_locks join pattern */
export const SQL_LOCKS = `
  SELECT DISTINCT
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_act.usename AS blocked_user,
    blocking_act.usename AS blocking_user,
    COALESCE(blocked_locks.relation::regclass::text, '') AS relation_name,
    EXTRACT(EPOCH FROM (clock_timestamp() - blocked_act.query_start))::double precision AS wait_sec,
    LEFT(blocked_act.query, 200) AS blocked_query
  FROM pg_catalog.pg_locks blocked_locks
  JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
  JOIN pg_catalog.pg_stat_activity blocking_act ON blocking_act.pid = blocking_locks.pid
  JOIN pg_catalog.pg_stat_activity blocked_act ON blocked_act.pid = blocked_locks.pid
  WHERE NOT blocked_locks.granted
    AND blocking_locks.granted
    AND blocked_act.datname = current_database()
  LIMIT 50
`;

export const SQL_TABLES_SUMMARY = `
  SELECT
    t.schemaname,
    t.relname,
    t.seq_scan::bigint AS seq_scan,
    t.idx_scan::bigint AS idx_scan,
    t.n_live_tup::bigint AS n_live_tup,
    t.n_dead_tup::bigint AS n_dead_tup,
    t.last_vacuum,
    t.last_autovacuum,
    COALESCE(s.heap_blks_hit, 0)::bigint AS heap_blks_hit,
    COALESCE(s.heap_blks_read, 0)::bigint AS heap_blks_read,
    pg_total_relation_size(t.relid)::bigint AS total_bytes
  FROM pg_stat_user_tables t
  LEFT JOIN pg_statio_user_tables s ON s.relid = t.relid
  ORDER BY t.seq_scan DESC
  LIMIT 15
`;

export const SQL_INDEXES_UNUSED = `
  SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan::bigint AS idx_scan,
    idx_tup_read::bigint AS idx_tup_read
  FROM pg_stat_user_indexes
  WHERE idx_scan = 0
  ORDER BY pg_relation_size(indexrelid) DESC
  LIMIT 8
`;

/** All indexes for one table (table analysis modal) */
export const SQL_INDEXES_FOR_TABLE = `
  SELECT
    indexrelname,
    idx_scan::bigint AS idx_scan,
    idx_tup_read::bigint AS idx_tup_read,
    pg_relation_size(indexrelid)::bigint AS index_bytes
  FROM pg_stat_user_indexes
  WHERE schemaname = $1 AND relname = $2
  ORDER BY idx_scan DESC
`;

/** Current database I/O + sessions (not OS CPU/RAM — buffer cache effectiveness) */
export const SQL_PG_STAT_DATABASE = `
  SELECT
    numbackends::int AS numbackends,
    blks_read::bigint AS blks_read,
    blks_hit::bigint AS blks_hit,
    deadlocks::bigint AS deadlocks,
    conflicts::bigint AS conflicts,
    temp_files::bigint AS temp_files
  FROM pg_stat_database
  WHERE datname = current_database()
`;

/** PG16 and older: single bgwriter row */
export const SQL_PG_STAT_BGWRITER = `
  SELECT
    checkpoints_timed::bigint AS checkpoints_timed,
    checkpoints_req::bigint AS checkpoints_req,
    buffers_checkpoint::bigint AS buffers_checkpoint,
    buffers_clean::bigint AS buffers_clean,
    buffers_backend::bigint AS buffers_backend,
    maxwritten_clean::bigint AS maxwritten_clean
  FROM pg_stat_bgwriter
`;

/**
 * PG17+: pg_stat_bgwriter removed; checkpoint stats live here.
 * Aliases match SQL_PG_STAT_BGWRITER shape for the footer.
 */
export const SQL_PG_STAT_CHECKPOINTER = `
  SELECT
    num_timed::bigint AS checkpoints_timed,
    num_requested::bigint AS checkpoints_req,
    buffers_written::bigint AS buffers_checkpoint,
    0::bigint AS buffers_clean,
    0::bigint AS buffers_backend,
    0::bigint AS maxwritten_clean
  FROM pg_stat_checkpointer
`;

/** Memory-related GUCs (format sizes in app) */
export const SQL_PG_SETTINGS_MEMORY = `
  SELECT name, setting, unit
  FROM pg_settings
  WHERE name IN ('shared_buffers', 'work_mem', 'max_connections')
`;

export const SQL_EXPLAIN = (queryText) => {
  const safe = String(queryText).replace(/;\s*$/, "");
  return { text: `EXPLAIN (FORMAT TEXT) ${safe}`, values: [] };
};