82 lines
2.9 KiB
Python
82 lines
2.9 KiB
Python
import sqlite3
|
|
from typing import Iterable
|
|
from app.config import DB_NAME
|
|
|
|
def get_connection() -> sqlite3.Connection:
|
|
connection = sqlite3.connect(DB_NAME)
|
|
connection.row_factory = sqlite3.Row
|
|
connection.execute("PRAGMA foreign_keys = ON")
|
|
return connection
|
|
|
|
DEFAULT_CATEGORIES: Iterable[tuple[str, str]] = [
|
|
("Продукты", "Расход"),
|
|
("Транспорт", "Расход"),
|
|
("Кафе", "Расход"),
|
|
("Жильё", "Расход"),
|
|
("Здоровье", "Расход"),
|
|
("Развлечения", "Расход"),
|
|
("Одежда", "Расход"),
|
|
("Образование", "Расход"),
|
|
("Зарплата", "Доход"),
|
|
("Фриланс", "Доход"),
|
|
("Подарки", "Доход"),
|
|
("Возврат", "Доход"),
|
|
]
|
|
|
|
def init_database() -> None:
|
|
with get_connection() as connection:
|
|
cursor = connection.cursor()
|
|
cursor.executescript(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS accounts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
account_type TEXT NOT NULL,
|
|
balance REAL NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
category_type TEXT NOT NULL,
|
|
UNIQUE(name, category_type)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
title TEXT NOT NULL,
|
|
amount REAL NOT NULL,
|
|
transaction_type TEXT NOT NULL,
|
|
account_id INTEGER NOT NULL,
|
|
category_id INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE RESTRICT,
|
|
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS subscriptions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
amount REAL NOT NULL,
|
|
billing_day INTEGER NOT NULL,
|
|
period TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'Активна'
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS budgets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
category_id INTEGER NOT NULL UNIQUE,
|
|
limit_amount REAL NOT NULL,
|
|
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
|
|
);
|
|
"""
|
|
)
|
|
cursor.executemany(
|
|
"""
|
|
INSERT OR IGNORE INTO categories (name, category_type)
|
|
VALUES (?, ?)
|
|
""",
|
|
DEFAULT_CATEGORIES,
|
|
)
|
|
connection.commit()
|