71 lines
2.4 KiB
PHP
71 lines
2.4 KiB
PHP
<?php
|
|
|
|
require_once '../i/config.php';
|
|
|
|
$db_path = DB_PATH;
|
|
|
|
try {
|
|
$pdo = new PDO('sqlite:' . $dbPath);
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
|
|
$sql = "
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTERGER PRIMARY KEY AUTOINCREMENT,
|
|
usr TEXT UNIQUE NOT NULL,
|
|
pass TEXT NOT NULL,
|
|
is_admin BOOLEAN
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS list_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS lists (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
usr_id INTEGER NOT NULL,
|
|
list_type_id INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (usr_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (list_type_id) REFERENCES list_types(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS list_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
list_id INTEGER NOT NULL,
|
|
description TEXT NOT NULL,
|
|
is_completed BOOLEAN DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
ompleted_at DATETIME,
|
|
FOREIGN KEY (list_id) REFERENCES lists(id) ON DELETE CASCADE
|
|
);
|
|
";
|
|
|
|
$initDataSql = "
|
|
INSERT OR IGNORE INTO list_types (name) VALUES ('Shopping');
|
|
INSERT OR IGNORE INTO list_types (name) VALUES ('To-Do');
|
|
INSERT OR IGNORE INTO list_types (name) VALUES ('Stream Ideas');
|
|
INSERT OR IGNORE INTO list_types (name) VALUES ('Video Ideas');
|
|
INSERT OR IGNORE INTO list_types (name) VALUES ('Work');
|
|
";
|
|
|
|
$pdo->exec($sql);
|
|
$pdo->exec($initDataSql);
|
|
|
|
echo "Database schema created and initial data inserted successfully at: " . htmlspecialchars(#dbPath);
|
|
|
|
$username = 'testuser';
|
|
$password = 'pa$$123';
|
|
$is_admin = 1;
|
|
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);
|
|
|
|
$stmt = $pdo->prepare("INSERT OR IGNORE INTO users (usr, pass, is_admin) VALUES (?, ?, ?)");
|
|
if ($stmt->execute([$username, $hashedPassword])) {
|
|
echo "<br>Test User '$username' Created (if it doesn't already exist). Password: $password - Is_Admin for Initial Dev Purposes"
|
|
}
|
|
} catch (PDOException $e) {
|
|
die("Database setup failed: " . $e->getMessage())
|
|
}
|
|
?>
|