50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
-- Schéma initial : tables créées avant la mise en place du système de migrations.
|
|
-- Remplace tables_create.sql et interactions_create.sql.
|
|
|
|
CREATE TABLE IF NOT EXISTS posts (
|
|
id SERIAL PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
content TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP,
|
|
is_published BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS post_files (
|
|
id SERIAL PRIMARY KEY,
|
|
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
|
|
file_type TEXT,
|
|
file_path TEXT,
|
|
original_name TEXT,
|
|
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS article_reactions (
|
|
id SERIAL PRIMARY KEY,
|
|
article_uuid TEXT NOT NULL,
|
|
reaction_type TEXT NOT NULL CHECK (reaction_type IN ('useful', 'important', 'interesting')),
|
|
visitor_hash TEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
UNIQUE (article_uuid, reaction_type, visitor_hash)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS article_reactions_article_uuid_idx ON article_reactions (article_uuid);
|
|
|
|
CREATE TABLE IF NOT EXISTS comments (
|
|
id SERIAL PRIMARY KEY,
|
|
article_uuid TEXT NOT NULL,
|
|
author_name TEXT NOT NULL,
|
|
author_email TEXT NOT NULL,
|
|
content TEXT NOT NULL CHECK (LENGTH(content) <= 2000),
|
|
verify_token TEXT,
|
|
verification_code TEXT,
|
|
verify_attempts INTEGER NOT NULL DEFAULT 0,
|
|
verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
published BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
ip_address TEXT,
|
|
user_agent TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS comments_article_uuid_idx ON comments (article_uuid, verified, published);
|
|
CREATE INDEX IF NOT EXISTS comments_verify_token_idx ON comments (verify_token)
|
|
WHERE verified = FALSE AND verify_token IS NOT NULL;
|