28 lines
1.2 KiB
PL/PgSQL
28 lines
1.2 KiB
PL/PgSQL
CREATE TABLE IF NOT EXISTS tasks (
|
|
id SERIAL PRIMARY KEY,
|
|
title VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'todo'
|
|
CHECK (status IN ('todo', 'in_progress', 'done')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION update_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS set_updated_at ON tasks;
|
|
CREATE TRIGGER set_updated_at
|
|
BEFORE UPDATE ON tasks
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
|
|
INSERT INTO tasks (title, description, status) VALUES
|
|
('Set up Azure resources', 'Create App Service and PostgreSQL Flexible Server', 'done'),
|
|
('Build Docker image', 'Push image to Azure Container Registry', 'done'),
|
|
('Configure App Service', 'Set environment variables and container settings', 'in_progress'),
|
|
('Enable HTTPS', 'Built-in on azurewebsites.net — automatic', 'todo'),
|
|
('Write README', 'Document deployment and cost analysis', 'todo')
|
|
ON CONFLICT DO NOTHING;
|