- collecte : collecter_meteofrance, extraire_infoclimat, extraire_vp2, archiver_historique, importer_opendata ; WeatherLink allégé (alleger) - tests : meteofrance, infoclimat, vp2, archive, opendata (tous verts) - maquettes/accueil-carte : sources de la maquette (générateurs de tuiles, page, À propos, CGU, confidentialité) - forum : noyau local (rôles, droits, SQLite, serveur de test, 18 tests) - deploy : Caddyfile.vercors.me.v2 (non appliqué) Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
69 lignes
2.4 KiB
Python
69 lignes
2.4 KiB
Python
"""Schéma SQLite et accès aux données."""
|
|
import sqlite3
|
|
|
|
SCHEMA = """
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY,
|
|
hub_id TEXT NOT NULL UNIQUE, -- identifiant du compte Gastinel (Hub)
|
|
display_name TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'membre'
|
|
CHECK (role IN ('membre','adherent','moderateur','admin')),
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id INTEGER PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
min_role_topic TEXT NOT NULL DEFAULT 'adherent',
|
|
position INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS topics (
|
|
id INTEGER PRIMARY KEY,
|
|
category_id INTEGER NOT NULL REFERENCES categories(id),
|
|
title TEXT NOT NULL,
|
|
author_id INTEGER NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
locked INTEGER NOT NULL DEFAULT 0,
|
|
hidden INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS posts (
|
|
id INTEGER PRIMARY KEY,
|
|
topic_id INTEGER NOT NULL REFERENCES topics(id),
|
|
author_id INTEGER NOT NULL REFERENCES users(id),
|
|
body TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
edited_at TEXT,
|
|
hidden INTEGER NOT NULL DEFAULT 0,
|
|
ip TEXT -- purgée après 13 mois (purger_ips)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS reports (
|
|
id INTEGER PRIMARY KEY,
|
|
post_id INTEGER NOT NULL REFERENCES posts(id),
|
|
reporter_id INTEGER NOT NULL REFERENCES users(id),
|
|
reason TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE (post_id, reporter_id)
|
|
);
|
|
-- Prévu plus tard : attachments(id, post_id, filename, mime, size, sha256, stored_path)
|
|
CREATE INDEX IF NOT EXISTS idx_topics_cat ON topics(category_id, id DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_posts_topic ON posts(topic_id, id);
|
|
"""
|
|
|
|
CATEGORIES_DEFAUT = [
|
|
("recapitulatif-semaine", "Récapitulatif de la semaine", "adherent", 0),
|
|
("observations", "Observations", "adherent", 1),
|
|
("discussion", "Discussion générale", "adherent", 2),
|
|
]
|
|
|
|
|
|
def connecter(chemin=":memory:"):
|
|
con = sqlite3.connect(chemin, check_same_thread=False)
|
|
con.row_factory = sqlite3.Row
|
|
con.execute("PRAGMA foreign_keys = ON")
|
|
con.executescript(SCHEMA)
|
|
for slug, nom, role, pos in CATEGORIES_DEFAUT:
|
|
con.execute("INSERT OR IGNORE INTO categories(slug,name,min_role_topic,position)"
|
|
" VALUES (?,?,?,?)", (slug, nom, role, pos))
|
|
con.commit()
|
|
return con
|