Atakan Doğan Özban
e97a71e5b2
Hardens auth with PBKDF2, lockouts, local QR setup, and safer embeds while moving account security under the username menu.
118 lines
4.8 KiB
Transact-SQL
118 lines
4.8 KiB
Transact-SQL
-- atakanozban.com - full database schema
|
|
-- Target: Microsoft SQL Server
|
|
--
|
|
-- This project uses EF6 Code First with Database.SetInitializer(null), which means
|
|
-- Entity Framework will NOT create or migrate the database automatically. Run this
|
|
-- script once against a fresh database to create every table the application needs.
|
|
--
|
|
-- Usage:
|
|
-- 1. Create an empty database, e.g.:
|
|
-- CREATE DATABASE atakanozbancomdb;
|
|
-- 2. Run this whole script against that database (SSMS, Azure Data Studio, sqlcmd, etc.)
|
|
|
|
USE atakanozbancomdb;
|
|
GO
|
|
|
|
-- =========================================================
|
|
-- Admin users (used for the /login and /admin panel)
|
|
-- =========================================================
|
|
CREATE TABLE dbo.admins (
|
|
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
username NVARCHAR(100) NOT NULL,
|
|
password NVARCHAR(255) NOT NULL,
|
|
two_factor_enabled BIT NOT NULL CONSTRAINT DF_admins_two_factor_enabled DEFAULT(0),
|
|
two_factor_secret NVARCHAR(512) NULL,
|
|
login_failed_count INT NOT NULL CONSTRAINT DF_admins_login_failed_count DEFAULT(0),
|
|
login_lock_until DATETIME2 NULL,
|
|
totp_failed_count INT NOT NULL CONSTRAINT DF_admins_totp_failed_count DEFAULT(0),
|
|
totp_lock_until DATETIME2 NULL
|
|
);
|
|
GO
|
|
|
|
-- =========================================================
|
|
-- My Projects
|
|
-- =========================================================
|
|
CREATE TABLE dbo.myprojects (
|
|
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
image NVARCHAR(MAX) NULL, -- legacy, kept for backward compatibility
|
|
iframe NVARCHAR(MAX) NULL, -- legacy, kept for backward compatibility
|
|
title NVARCHAR(255) NULL,
|
|
description NVARCHAR(MAX) NULL
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE dbo.myprojectstranslations (
|
|
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
project_id INT NOT NULL,
|
|
culture NVARCHAR(10) NOT NULL,
|
|
title NVARCHAR(255) NULL,
|
|
description NVARCHAR(MAX) NULL,
|
|
CONSTRAINT FK_myprojectstranslations_myprojects
|
|
FOREIGN KEY (project_id) REFERENCES dbo.myprojects(id) ON DELETE CASCADE
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE dbo.projectmedia (
|
|
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
projectid INT NOT NULL,
|
|
mediatype INT NOT NULL, -- 1 = Image, 2 = Iframe
|
|
mediaurl NVARCHAR(1000) NOT NULL,
|
|
sortorder INT NOT NULL DEFAULT 0,
|
|
CONSTRAINT FK_projectmedia_myprojects
|
|
FOREIGN KEY (projectid) REFERENCES dbo.myprojects(id) ON DELETE CASCADE
|
|
);
|
|
GO
|
|
|
|
-- =========================================================
|
|
-- Affiliate Links
|
|
-- =========================================================
|
|
CREATE TABLE dbo.affiliatelinks (
|
|
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
image NVARCHAR(500) NOT NULL,
|
|
title NVARCHAR(200) NOT NULL,
|
|
description NVARCHAR(500) NULL,
|
|
url NVARCHAR(1000) NOT NULL,
|
|
sortorder INT NOT NULL DEFAULT 0
|
|
);
|
|
GO
|
|
|
|
CREATE TABLE dbo.affiliatelinktranslations (
|
|
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
affiliatelink_id INT NOT NULL,
|
|
culture NVARCHAR(10) NOT NULL,
|
|
title NVARCHAR(255) NULL,
|
|
description NVARCHAR(MAX) NULL,
|
|
CONSTRAINT FK_affiliatelinktranslations_affiliatelinks
|
|
FOREIGN KEY (affiliatelink_id) REFERENCES dbo.affiliatelinks(id) ON DELETE CASCADE
|
|
);
|
|
GO
|
|
|
|
-- =========================================================
|
|
-- Social Icons (footer icon row)
|
|
-- =========================================================
|
|
CREATE TABLE dbo.socialicons (
|
|
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
icon NVARCHAR(100) NOT NULL, -- Font Awesome class(es), e.g. "fa-brands fa-github"
|
|
url NVARCHAR(500) NOT NULL,
|
|
sortorder INT NOT NULL DEFAULT 0
|
|
);
|
|
GO
|
|
|
|
-- =========================================================
|
|
-- Site Settings (currently just the wallpaper; single row table)
|
|
-- =========================================================
|
|
CREATE TABLE dbo.sitesettings (
|
|
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
wallpaper NVARCHAR(500) NULL -- public URL of the current wallpaper; NULL = use bundled default
|
|
);
|
|
GO
|
|
|
|
-- =========================================================
|
|
-- Seed data: one admin account so you can log in to /admin
|
|
-- IMPORTANT: change the username/password below before/after running this,
|
|
-- and see the "Security notes" section in README.md - passwords are currently
|
|
-- stored and compared in plain text, so pick something you don't reuse elsewhere.
|
|
-- =========================================================
|
|
INSERT INTO dbo.admins (username, password) VALUES ('admin', 'change-this-password');
|
|
GO
|