Files

158 lines
5.5 KiB
Python

"""
Street Model - Abstraktionsschicht für Datenbankzugriffe
"""
from cmbnet.db import get_db
def _chunks(values, size=400):
"""Teilt eine Liste in Blöcke, damit SQLite-Parameterlimits nicht greifen."""
for index in range(0, len(values), size):
yield values[index:index + size]
class StreetModel:
@staticmethod
def get_all_by_tenant(tenant_id):
db = get_db()
return db.execute(
'SELECT * FROM streets WHERE tenant_id = ? ORDER BY name',
(tenant_id,)
).fetchall()
@staticmethod
def get_by_id(street_id, tenant_id):
db = get_db()
return db.execute(
'SELECT * FROM streets WHERE id = ? AND tenant_id = ?',
(street_id, tenant_id)
).fetchone()
@staticmethod
def get_by_group_id(group_id, tenant_id):
db = get_db()
return db.execute(
'SELECT * FROM streets WHERE group_id = ? AND tenant_id = ?',
(group_id, tenant_id)
).fetchall()
@staticmethod
def create(tenant_id, name, hn_min=None, hn_max=None, bbox=None, state='unbearbeitet', group_id=None, zusatz=''):
db = get_db()
cursor = db.execute(
'INSERT INTO streets (tenant_id, name, hn_min, hn_max, bbox, state, group_id, zusatz) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
(tenant_id, name, hn_min, hn_max, bbox, state, group_id, zusatz or '')
)
db.commit()
return cursor.lastrowid
@staticmethod
def update(street_id, tenant_id, **kwargs):
db = get_db()
allowed = ['name', 'state', 'group_id', 'hn_min', 'hn_max', 'bbox', 'zusatz']
updates = []
values = []
for k, v in kwargs.items():
if k in allowed:
updates.append(f'{k} = ?')
values.append(v)
if not updates:
return
updates.append('updated_at = CURRENT_TIMESTAMP')
values.extend([street_id, tenant_id])
db.execute(f'UPDATE streets SET {", ".join(updates)} WHERE id = ? AND tenant_id = ?', values)
db.commit()
@staticmethod
def delete(street_id, tenant_id):
"""Löscht eine Straße samt ihrer Hausnummern-Einträge.
Die Hausnummern-Einträge werden nur entfernt, wenn danach kein
weiterer Abschnitt derselben Straße mehr existiert.
"""
return StreetModel.delete_many([street_id], tenant_id)
@staticmethod
def delete_many(street_ids, tenant_id):
"""Löscht mehrere Straßen samt ihrer Hausnummern-Einträge.
Die Hausnummern-Tabelle ist über den Straßennamen und nicht über die
Straßen-ID verknüpft. Deshalb werden die Namen vor dem Löschen der
Straßen gelesen und die passenden Hausnummern-Einträge mitgelöscht.
Rückgabe: ``{'streets': [...], 'house_numbers': <Anzahl>}``.
"""
ids = []
for value in street_ids:
try:
street_id = int(value)
except (TypeError, ValueError):
continue
if street_id > 0 and street_id not in ids:
ids.append(street_id)
result = {'streets': [], 'house_numbers': 0}
if not ids:
return result
db = get_db()
rows = []
try:
for chunk in _chunks(ids):
placeholders = ', '.join('?' for _ in chunk)
rows.extend(db.execute(
f'''SELECT id, name FROM streets
WHERE tenant_id = ? AND id IN ({placeholders})''',
(tenant_id, *chunk)
).fetchall())
if not rows:
return result
found_ids = [row['id'] for row in rows]
names = []
for row in rows:
if row['name'] not in names:
names.append(row['name'])
# Zuerst die Straßen löschen. Hausnummern sind nur über den Namen
# verknüpft und werden erst entfernt, wenn kein anderer Abschnitt
# derselben Straße mehr existiert.
for chunk in _chunks(found_ids):
placeholders = ', '.join('?' for _ in chunk)
db.execute(
f'''DELETE FROM streets
WHERE tenant_id = ? AND id IN ({placeholders})''',
(tenant_id, *chunk)
)
remaining_names = set()
for chunk in _chunks(names):
placeholders = ', '.join('?' for _ in chunk)
remaining_names.update(
row['name'] for row in db.execute(
f'''SELECT DISTINCT name FROM streets
WHERE tenant_id = ? AND name IN ({placeholders})''',
(tenant_id, *chunk)
).fetchall()
)
names_to_delete = [
name for name in names if name not in remaining_names
]
for chunk in _chunks(names_to_delete):
placeholders = ', '.join('?' for _ in chunk)
cursor = db.execute(
f'''DELETE FROM street_house_numbers
WHERE tenant_id = ? AND street_name IN ({placeholders})''',
(tenant_id, *chunk)
)
result['house_numbers'] += max(cursor.rowcount, 0)
db.commit()
except Exception:
db.rollback()
raise
result['streets'] = [dict(row) for row in rows]
return result