79 Zeilen
3.1 KiB
SQL
79 Zeilen
3.1 KiB
SQL
-- Migration: Create license_heartbeats partitioned table
|
|
-- Date: 2025-06-19
|
|
-- Description: Creates the license_heartbeats table with monthly partitioning
|
|
|
|
-- Create the partitioned table
|
|
CREATE TABLE IF NOT EXISTS license_heartbeats (
|
|
id BIGSERIAL,
|
|
license_id INTEGER NOT NULL,
|
|
hardware_id VARCHAR(255) NOT NULL,
|
|
ip_address INET NOT NULL,
|
|
timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
session_data JSONB,
|
|
PRIMARY KEY (id, timestamp),
|
|
FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
|
|
) PARTITION BY RANGE (timestamp);
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX IF NOT EXISTS idx_license_heartbeats_license_id_timestamp
|
|
ON license_heartbeats (license_id, timestamp DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_license_heartbeats_timestamp
|
|
ON license_heartbeats (timestamp DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_license_heartbeats_hardware_id
|
|
ON license_heartbeats (hardware_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_license_heartbeats_ip_address
|
|
ON license_heartbeats (ip_address);
|
|
|
|
-- Create partitions for current and next month
|
|
DO $$
|
|
DECLARE
|
|
current_year INTEGER;
|
|
current_month INTEGER;
|
|
next_year INTEGER;
|
|
next_month INTEGER;
|
|
partition_name TEXT;
|
|
start_date DATE;
|
|
end_date DATE;
|
|
BEGIN
|
|
-- Get current date info
|
|
current_year := EXTRACT(YEAR FROM CURRENT_DATE);
|
|
current_month := EXTRACT(MONTH FROM CURRENT_DATE);
|
|
|
|
-- Calculate next month
|
|
IF current_month = 12 THEN
|
|
next_year := current_year + 1;
|
|
next_month := 1;
|
|
ELSE
|
|
next_year := current_year;
|
|
next_month := current_month + 1;
|
|
END IF;
|
|
|
|
-- Create current month partition
|
|
partition_name := 'license_heartbeats_' || current_year || '_' || LPAD(current_month::TEXT, 2, '0');
|
|
start_date := DATE_TRUNC('month', CURRENT_DATE);
|
|
end_date := DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';
|
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF license_heartbeats FOR VALUES FROM (%L) TO (%L)',
|
|
partition_name, start_date, end_date);
|
|
|
|
-- Create next month partition
|
|
partition_name := 'license_heartbeats_' || next_year || '_' || LPAD(next_month::TEXT, 2, '0');
|
|
start_date := end_date;
|
|
end_date := start_date + INTERVAL '1 month';
|
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF license_heartbeats FOR VALUES FROM (%L) TO (%L)',
|
|
partition_name, start_date, end_date);
|
|
|
|
RAISE NOTICE 'Created partitions for current and next month';
|
|
END $$;
|
|
|
|
-- Add comment to the table
|
|
COMMENT ON TABLE license_heartbeats IS 'Stores heartbeat data from license validations for real-time monitoring';
|
|
COMMENT ON COLUMN license_heartbeats.license_id IS 'Foreign key to licenses table';
|
|
COMMENT ON COLUMN license_heartbeats.hardware_id IS 'Hardware identifier of the device';
|
|
COMMENT ON COLUMN license_heartbeats.ip_address IS 'IP address from which the heartbeat was sent';
|
|
COMMENT ON COLUMN license_heartbeats.timestamp IS 'Timestamp of the heartbeat';
|
|
COMMENT ON COLUMN license_heartbeats.session_data IS 'Additional session data in JSON format'; |