42 lines
1.8 KiB
SQL
42 lines
1.8 KiB
SQL
-- encodage base
|
||
CREATE DATABASE IF NOT EXISTS forum_database
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE forum_database;
|
||
|
||
-- table utilisateurs
|
||
CREATE TABLE IF NOT EXISTS utilisateurs (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
username VARCHAR(50) NOT NULL UNIQUE,
|
||
password VARCHAR(255) NOT NULL,
|
||
email VARCHAR(100) UNIQUE,
|
||
role VARCHAR(50) DEFAULT 'user',
|
||
bio TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
|
||
profile_picture VARCHAR(255),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
-- table posts
|
||
CREATE TABLE IF NOT EXISTS posts (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
|
||
content LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
|
||
image_url VARCHAR(255),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES utilisateurs(id) ON DELETE CASCADE
|
||
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
-- données initiales
|
||
INSERT INTO utilisateurs (username, password, email, role)
|
||
VALUES
|
||
('admin', '$2y$10$4U8a64xTrD/GQDnj8tCNsemA47765p.5gtflWWBKl6UGl2aj/uOlC', 'admin@example.com', 'admin'),
|
||
('aya', '$2y$10$vhtGydzlL8AFMbRTeRVC2OtDkud47TZCHt98HcAjjGGVlLlm2Bn66', 'aya@example.com', 'user');
|
||
|
||
INSERT INTO posts (user_id, title, content)
|
||
VALUES
|
||
(1, 'Bienvenue sur le Mini CMS', 'Ceci est un article de démonstration publié par l’administrateur !'),
|
||
(2, 'Un premier article test', 'Bienvenue dans notre CMS simplifié. Partagez vos idées ici !');
|