68 lines
1.8 KiB
Python
68 lines
1.8 KiB
Python
import os
|
|
import sqlite3
|
|
|
|
from flask import g
|
|
|
|
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
|
|
DATA_DIR = os.path.join(BASE_DIR, "data")
|
|
UPLOAD_DIR = os.path.join(DATA_DIR, "uploads")
|
|
DB_PATH = os.path.join(DATA_DIR, "portfolio.db")
|
|
|
|
SCHEMA = """
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE COLLATE NOCASE,
|
|
display_name TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
title TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
tags TEXT NOT NULL DEFAULT '',
|
|
link TEXT NOT NULL DEFAULT '',
|
|
stored_name TEXT,
|
|
orig_name TEXT,
|
|
mime TEXT,
|
|
size INTEGER,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS requests (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sender_name TEXT NOT NULL,
|
|
sender_email TEXT NOT NULL,
|
|
subject TEXT NOT NULL,
|
|
body TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_projects_created ON projects(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_requests_created ON requests(created_at DESC);
|
|
"""
|
|
|
|
|
|
def init_db():
|
|
os.makedirs(DATA_DIR, exist_ok=True)
|
|
os.makedirs(UPLOAD_DIR, exist_ok=True)
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.executescript(SCHEMA)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def get_db():
|
|
if "db" not in g:
|
|
g.db = sqlite3.connect(DB_PATH)
|
|
g.db.row_factory = sqlite3.Row
|
|
g.db.execute("PRAGMA foreign_keys = ON")
|
|
return g.db
|
|
|
|
|
|
def close_db(exc=None):
|
|
db = g.pop("db", None)
|
|
if db is not None:
|
|
db.close()
|