124 lines
6.1 KiB
Python
124 lines
6.1 KiB
Python
"""Small SQLite layer. Each operation owns its connection and transaction."""
|
|
import os
|
|
import sqlite3
|
|
from contextlib import contextmanager
|
|
from pathlib import Path
|
|
from models import canonical_name, name_key, ASSET_TYPES
|
|
|
|
|
|
def db_path():
|
|
return Path(os.environ.get('FINANCE_DB_PATH', '/data/finance.db'))
|
|
|
|
|
|
@contextmanager
|
|
def connect(path=None):
|
|
db = sqlite3.connect(path or db_path(), timeout=5)
|
|
db.row_factory = sqlite3.Row
|
|
db.execute('PRAGMA foreign_keys = ON')
|
|
db.execute('PRAGMA busy_timeout = 5000')
|
|
try:
|
|
with db:
|
|
yield db
|
|
finally:
|
|
db.close()
|
|
|
|
|
|
def validate_asset(name, asset_type='other', ticker=None):
|
|
name = canonical_name(name)
|
|
if asset_type not in ASSET_TYPES:
|
|
raise ValueError('Ungültiger Positionstyp.')
|
|
ticker = (ticker or '').strip()
|
|
if len(ticker) > 30:
|
|
raise ValueError('Ticker darf maximal 30 Zeichen enthalten.')
|
|
return name, ticker or None
|
|
|
|
|
|
def ensure_asset(db, name, asset_type='other', ticker=None):
|
|
name, ticker = validate_asset(name, asset_type, ticker)
|
|
db.execute('INSERT INTO assets (name, normalized_name, ticker, asset_type) VALUES (?, ?, ?, ?) ON CONFLICT(normalized_name) DO NOTHING',
|
|
(name, name_key(name), ticker or None, asset_type))
|
|
return db.execute('SELECT id FROM assets WHERE normalized_name = ?', (name_key(name),)).fetchone()['id']
|
|
|
|
|
|
def initialize(path=None):
|
|
target = Path(path or db_path())
|
|
target.parent.mkdir(parents=True, exist_ok=True)
|
|
with connect(target) as db:
|
|
first_setup = db.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name='assets'").fetchone() is None
|
|
db.execute('PRAGMA journal_mode = WAL')
|
|
db.executescript('''
|
|
CREATE TABLE IF NOT EXISTS assets (
|
|
id INTEGER PRIMARY KEY, name TEXT NOT NULL,
|
|
normalized_name TEXT NOT NULL UNIQUE, ticker TEXT,
|
|
asset_type TEXT NOT NULL CHECK(asset_type IN ('stock','etf','bond','crypto','interest','other')),
|
|
active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1)),
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS income_entries (
|
|
id INTEGER PRIMARY KEY, date TEXT NOT NULL,
|
|
asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE RESTRICT,
|
|
category TEXT NOT NULL CHECK(category IN ('dividend','interest','distribution','other')),
|
|
amount INTEGER NOT NULL CHECK(typeof(amount) = 'integer' AND abs(amount) <= 99999999999),
|
|
note TEXT, expected INTEGER NOT NULL DEFAULT 0 CHECK(expected IN (0,1)),
|
|
received INTEGER NOT NULL DEFAULT 1 CHECK(received IN (0,1)),
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
);
|
|
CREATE INDEX IF NOT EXISTS income_date ON income_entries(date DESC, id DESC);
|
|
CREATE INDEX IF NOT EXISTS income_asset ON income_entries(asset_id);
|
|
CREATE TABLE IF NOT EXISTS import_records (
|
|
fingerprint TEXT PRIMARY KEY,
|
|
entry_id INTEGER REFERENCES income_entries(id) ON DELETE SET NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS data_migrations (
|
|
name TEXT PRIMARY KEY,
|
|
applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
''')
|
|
db.execute('BEGIN IMMEDIATE')
|
|
if first_setup:
|
|
for name, kind in [('AGNC','stock'), ('Main Street Capital','stock'), ('Capital Southwest','stock'),
|
|
('Ares Capital','stock'), ('Realty Income','stock'), ('Enbridge','stock'),
|
|
('Bayer','stock'), ('STOXX Global Select Dividend 100','etf'), ('airBaltic','bond')]:
|
|
ensure_asset(db, name, kind)
|
|
_update_interest_positions(db)
|
|
_create_trading_schema(db)
|
|
if db.execute('PRAGMA user_version').fetchone()[0] < 3:
|
|
db.execute('PRAGMA user_version = 3')
|
|
|
|
|
|
def _update_interest_positions(db):
|
|
"""Apply the requested position changes once, preserving all income history."""
|
|
migration = '2026-09-09-interest-positions'
|
|
if db.execute('SELECT 1 FROM data_migrations WHERE name=?', (migration,)).fetchone():
|
|
return
|
|
for name, kind in [('Anleihezinsen', 'bond'), ('Zinsen NG', 'interest')]:
|
|
asset_id = ensure_asset(db, name, kind)
|
|
db.execute('UPDATE assets SET active=1 WHERE id=?', (asset_id,))
|
|
db.execute('UPDATE assets SET active=0 WHERE normalized_name IN (?,?)',
|
|
(name_key('Zinsen'), name_key('Steuerrückzahlung')))
|
|
db.execute('INSERT INTO data_migrations (name) VALUES (?)', (migration,))
|
|
|
|
|
|
def _create_trading_schema(db):
|
|
# Transactional DDL: retain every existing table and income entry.
|
|
db.execute("""CREATE TABLE IF NOT EXISTS transactions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
date TEXT NOT NULL,
|
|
asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE RESTRICT,
|
|
transaction_type TEXT NOT NULL CHECK(transaction_type IN ('buy','sell')),
|
|
quantity TEXT NOT NULL CHECK(typeof(quantity)='text'),
|
|
price_per_unit TEXT NOT NULL CHECK(typeof(price_per_unit)='text'),
|
|
currency TEXT NOT NULL CHECK(length(currency)=3),
|
|
fees TEXT NOT NULL DEFAULT '0.00' CHECK(typeof(fees)='text'),
|
|
source TEXT NOT NULL DEFAULT 'manual' CHECK(source IN ('manual','savings_plan','roundup','cashback','rebalancing','other')),
|
|
strategy_tag TEXT CHECK(strategy_tag IN ('core','income','conviction','dip_buy','speculation','rebalancing','other')),
|
|
note TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
|
|
)""")
|
|
db.execute('CREATE INDEX IF NOT EXISTS transactions_asset_date ON transactions(asset_id,date,id)')
|
|
db.execute('CREATE INDEX IF NOT EXISTS transactions_date ON transactions(date DESC,id DESC)')
|
|
db.execute('INSERT OR IGNORE INTO data_migrations(name) VALUES (?)', ('2026-09-09-trading-schema',))
|