22 lines
827 B
SQL
22 lines
827 B
SQL
-- Support application logs that span multiple lawn sections.
|
|
|
|
CREATE TABLE IF NOT EXISTS application_log_sections (
|
|
id SERIAL PRIMARY KEY,
|
|
log_id INTEGER NOT NULL REFERENCES application_logs(id) ON DELETE CASCADE,
|
|
lawn_section_id INTEGER NOT NULL REFERENCES lawn_sections(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(log_id, lawn_section_id)
|
|
);
|
|
|
|
INSERT INTO application_log_sections (log_id, lawn_section_id)
|
|
SELECT al.id, al.lawn_section_id
|
|
FROM application_logs al
|
|
WHERE al.lawn_section_id IS NOT NULL
|
|
ON CONFLICT (log_id, lawn_section_id) DO NOTHING;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_application_log_sections_log_id
|
|
ON application_log_sections(log_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_application_log_sections_section_id
|
|
ON application_log_sections(lawn_section_id);
|