5bebad45b8
- FastAPI backend with JWT authentication - ElGamal, RSA-PSS, ZK-proofs crypto modules - HTML5/JS frontend SPA - MariaDB database with 5 tables - Docker Compose with 3 services (frontend, backend, mariadb) - Comprehensive tests for cryptography - Typst technical report (30+ pages) - Makefile with development commands
97 lines
3.4 KiB
SQL
97 lines
3.4 KiB
SQL
-- ================================================================
|
|
-- Configuration initiale de la base de données MariaDB.
|
|
-- À exécuter automatiquement au démarrage du conteneur.
|
|
-- ================================================================
|
|
|
|
-- Créer les tables
|
|
CREATE TABLE IF NOT EXISTS voters (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
first_name VARCHAR(100),
|
|
last_name VARCHAR(100),
|
|
citizen_id VARCHAR(50) UNIQUE,
|
|
public_key LONGBLOB,
|
|
has_voted BOOLEAN DEFAULT FALSE,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_email (email),
|
|
INDEX idx_citizen_id (citizen_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS elections (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
start_date DATETIME NOT NULL,
|
|
end_date DATETIME NOT NULL,
|
|
elgamal_p INT,
|
|
elgamal_g INT,
|
|
public_key LONGBLOB,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
results_published BOOLEAN DEFAULT FALSE,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_active (is_active)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS candidates (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
election_id INT NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
`order` INT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
|
|
INDEX idx_election (election_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS votes (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
voter_id INT NOT NULL,
|
|
election_id INT NOT NULL,
|
|
candidate_id INT NOT NULL,
|
|
encrypted_vote LONGBLOB NOT NULL,
|
|
zero_knowledge_proof LONGBLOB,
|
|
ballot_hash VARCHAR(64),
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
ip_address VARCHAR(45),
|
|
FOREIGN KEY (voter_id) REFERENCES voters(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
|
|
INDEX idx_voter_election (voter_id, election_id),
|
|
INDEX idx_election (election_id),
|
|
UNIQUE KEY unique_vote (voter_id, election_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
action VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
user_id INT,
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
ip_address VARCHAR(45),
|
|
user_agent VARCHAR(255),
|
|
FOREIGN KEY (user_id) REFERENCES voters(id) ON DELETE SET NULL,
|
|
INDEX idx_timestamp (timestamp)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Insérer des données de test
|
|
INSERT INTO elections (name, description, start_date, end_date, elgamal_p, elgamal_g, is_active)
|
|
VALUES (
|
|
'Élection Présidentielle 2025',
|
|
'Vote pour la présidence',
|
|
NOW(),
|
|
DATE_ADD(NOW(), INTERVAL 7 DAY),
|
|
23,
|
|
5,
|
|
TRUE
|
|
);
|
|
|
|
INSERT INTO candidates (election_id, name, description, `order`)
|
|
VALUES
|
|
(1, 'Alice Dupont', 'Candidate pour le changement', 1),
|
|
(1, 'Bob Martin', 'Candidate pour la stabilité', 2),
|
|
(1, 'Charlie Leclerc', 'Candidate pour l''innovation', 3),
|
|
(1, 'Diana Fontaine', 'Candidate pour l''environnement', 4);
|