75 lines
2.4 KiB
Python
75 lines
2.4 KiB
Python
import sqlite3
|
|
from pathlib import Path
|
|
from typing import Optional
|
|
|
|
from backend.config import DATABASE_PATH
|
|
|
|
|
|
def get_connection() -> sqlite3.Connection:
|
|
conn = sqlite3.connect(DATABASE_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
return conn
|
|
|
|
|
|
def init_db() -> None:
|
|
Path(DATABASE_PATH).parent.mkdir(parents=True, exist_ok=True)
|
|
conn = get_connection()
|
|
try:
|
|
conn.executescript("""
|
|
CREATE TABLE IF NOT EXISTS banks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
code TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
is_salary INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS accounts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
bank_id INTEGER NOT NULL REFERENCES banks(id),
|
|
external_id TEXT NOT NULL,
|
|
name TEXT,
|
|
UNIQUE(bank_id, external_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS opening_balances (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id),
|
|
period_start TEXT NOT NULL,
|
|
amount REAL NOT NULL,
|
|
UNIQUE(account_id, period_start)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id),
|
|
operation_date TEXT NOT NULL,
|
|
debit_date TEXT,
|
|
amount REAL NOT NULL,
|
|
currency TEXT DEFAULT 'RUB',
|
|
amount_card_currency REAL,
|
|
description TEXT,
|
|
source_file TEXT NOT NULL,
|
|
imported_at TEXT NOT NULL,
|
|
dedup_key TEXT NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_account ON transactions(account_id);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_operation_date ON transactions(operation_date);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_dedup ON transactions(dedup_key);
|
|
""")
|
|
conn.commit()
|
|
try:
|
|
conn.execute("ALTER TABLE transactions ADD COLUMN excluded_from_balance INTEGER NOT NULL DEFAULT 0")
|
|
conn.commit()
|
|
except sqlite3.OperationalError:
|
|
pass
|
|
conn.executescript("""
|
|
CREATE TABLE IF NOT EXISTS app_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT
|
|
);
|
|
""")
|
|
conn.commit()
|
|
finally:
|
|
conn.close()
|