53 lines
2.1 KiB
SQL
Executable File
53 lines
2.1 KiB
SQL
Executable File
-- =====================================================================
|
|
-- SQL Init & Setup Script für MariaDB (wtc.dev.waiswuaschd.de)
|
|
-- Kochbuch & Essens-Zufallsgenerator (What To Cook?)
|
|
-- =====================================================================
|
|
-- Ausführung auf dem DB-Server:
|
|
-- mariadb --ssl=0 -u root -p < init_mariadb.sql
|
|
-- =====================================================================
|
|
|
|
-- 1. DATENBANK ERSTELLEN
|
|
CREATE DATABASE IF NOT EXISTS `v2_db_wtc`
|
|
DEFAULT CHARACTER SET utf8mb4
|
|
DEFAULT COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- 2. BENUTZER ERSTELLEN (Passwort nach Bedarf anpassen)
|
|
CREATE USER IF NOT EXISTS 'v2_usr_wtc'@'%' IDENTIFIED BY 'Wtc_Kochbuch_Pass_2026!';
|
|
GRANT ALL PRIVILEGES ON `v2_db_wtc`.* TO 'v2_usr_wtc'@'%';
|
|
FLUSH PRIVILEGES;
|
|
|
|
USE `v2_db_wtc`;
|
|
|
|
-- 3. TABELLE: DISHES (Zufallsgenerator)
|
|
CREATE TABLE IF NOT EXISTS `dishes` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL UNIQUE,
|
|
`category` VARCHAR(100) NOT NULL,
|
|
`effort` VARCHAR(20) NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX `idx_dish_category` (`category`),
|
|
INDEX `idx_dish_effort` (`effort`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 4. TABELLE: RECIPES (Digitales Kochbuch)
|
|
CREATE TABLE IF NOT EXISTS `recipes` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`category` VARCHAR(100) NOT NULL,
|
|
`chapter_number` INT NOT NULL,
|
|
`effort` VARCHAR(20) NOT NULL DEFAULT 'mittel',
|
|
`prep_time` VARCHAR(50) DEFAULT '',
|
|
`cook_time` VARCHAR(50) DEFAULT '',
|
|
`portions` INT DEFAULT 4,
|
|
`ingredients` LONGTEXT NOT NULL,
|
|
`instructions` LONGTEXT NOT NULL,
|
|
`notes` TEXT,
|
|
`source` VARCHAR(255) DEFAULT '',
|
|
`image_url` VARCHAR(500) DEFAULT '',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_recipe_chapter` (`chapter_number`),
|
|
INDEX `idx_recipe_category` (`category`),
|
|
INDEX `idx_recipe_title` (`title`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|