69 lines
1.4 KiB
MySQL
69 lines
1.4 KiB
MySQL
|
-- Enable Extensions
|
||
|
|
CREATE EXTENSION IF NOT EXISTS pg_cron;
|
||
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
|
|
|
||
|
|
-- Create table
|
||
|
|
CREATE TABLE if NOT EXISTS auctions (
|
||
|
|
id UUID PRIMARY KEY,
|
||
|
|
auctionid VARCHAR(255) NOT NULL,
|
||
|
|
lbin DECIMAL(65,5),
|
||
|
|
modified TIMESTAMP(2),
|
||
|
|
UNIQUE (auctionid)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Update trigger function
|
||
|
|
CREATE OR REPLACE FUNCTION public.fn_update_modified_column()
|
||
|
|
RETURNS TRIGGER AS $$
|
||
|
|
BEGIN
|
||
|
|
NEW.modified = now();
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$ language 'plpgsql';
|
||
|
|
|
||
|
|
-- Update trigger
|
||
|
|
DROP TRIGGER IF EXISTS update_auction_modtime on auctions;
|
||
|
|
CREATE TRIGGER update_auction_modtime
|
||
|
|
BEFORE UPDATE ON auctions
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE PROCEDURE public.fn_update_modified_column();
|
||
|
|
|
||
|
|
-- Cleanup function
|
||
|
|
CREATE OR REPLACE FUNCTION public.cleanup_old_entries()
|
||
|
|
RETURNS void
|
||
|
|
LANGUAGE plpgsql
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
temp_id UUID;
|
||
|
|
BEGIN
|
||
|
|
FOR temp_id IN
|
||
|
|
SELECT id
|
||
|
|
FROM auctions
|
||
|
|
WHERE EXTRACT(EPOCH FROM (NOW() - modified))/3600 >= 6 -- 6 hours
|
||
|
|
LOOP
|
||
|
|
DELETE FROM auctions
|
||
|
|
WHERE auctions.id = temp_id;
|
||
|
|
END LOOP;
|
||
|
|
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$;
|
||
|
|
|
||
|
|
-- Add job to pg_cron
|
||
|
|
DO $outer$
|
||
|
|
DECLARE job_exists boolean := false;
|
||
|
|
BEGIN
|
||
|
|
SELECT (count(*) > 0)
|
||
|
|
INTO job_exists
|
||
|
|
FROM cron.job
|
||
|
|
WHERE jobname = 'cleanup_old_entries_event';
|
||
|
|
|
||
|
|
IF NOT job_exists THEN
|
||
|
|
PERFORM cron.schedule(
|
||
|
|
'cleanup_old_entries_event',
|
||
|
|
'*/2 * * * *',
|
||
|
|
$sql$ SELECT public.cleanup_old_entries(); $sql$
|
||
|
|
);
|
||
|
|
END IF;
|
||
|
|
END;
|
||
|
|
$outer$;
|