mirror of
https://github.com/jokob-sk/NetAlertX.git
synced 2026-07-26 15:38:18 -04:00
18ec0ce96c
- Added db_history.py to manage DevicesHistory table and triggers for INSERT and UPDATE operations. - Created device_history_instance.py for querying and grouping DevicesHistory records. - Developed change_history.php for displaying device change history with filtering and pagination. - Introduced skel_device_details_tab_history.php for skeleton loading state in device details tab. - Added unit tests in test_device_history.py to validate trigger functionality and history management. - Implemented filter population and pagination in the change history UI.
230 lines
8.4 KiB
Python
230 lines
8.4 KiB
Python
"""
|
|
db_history.py — DevicesHistory table/trigger management.
|
|
|
|
Creates and maintains:
|
|
- DevicesHistory table + indexes
|
|
- AFTER UPDATE trigger (trg_devhist_update)
|
|
- AFTER INSERT trigger (trg_devhist_insert)
|
|
|
|
Both triggers are driven entirely by two Settings values:
|
|
DEV_HIST_DAYS — 0 disables the engine entirely (WHEN guard)
|
|
DEV_HIST_TRACKED — comma-separated list of field names to audit
|
|
|
|
Attribution uses existing *Source columns where available, falling back
|
|
to hard-coded constants for user-only and auto-computed fields.
|
|
"""
|
|
|
|
from logger import mylog
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Field → changedBy attribution mapping
|
|
#
|
|
# Each entry: (field_name, changedBy_sql_expression)
|
|
# - Source-tracked fields: read NEW.<field>Source at trigger time
|
|
# - User-only fields: hard-coded 'user:api'
|
|
# - Auto-computed fields: hard-coded 'system'
|
|
# - *Source fields: hard-coded 'system'
|
|
# ---------------------------------------------------------------------------
|
|
_HIST_FIELDS = [
|
|
# Source-tracked fields (FIELD_SOURCE_MAP)
|
|
("devMac", "COALESCE(NULLIF(TRIM(NEW.devMacSource), ''), 'system')"),
|
|
("devName", "COALESCE(NULLIF(TRIM(NEW.devNameSource), ''), 'system')"),
|
|
("devFQDN", "COALESCE(NULLIF(TRIM(NEW.devFQDNSource), ''), 'system')"),
|
|
("devLastIP", "COALESCE(NULLIF(TRIM(NEW.devLastIPSource), ''), 'system')"),
|
|
("devVendor", "COALESCE(NULLIF(TRIM(NEW.devVendorSource), ''), 'system')"),
|
|
("devSSID", "COALESCE(NULLIF(TRIM(NEW.devSSIDSource), ''), 'system')"),
|
|
("devParentMAC", "COALESCE(NULLIF(TRIM(NEW.devParentMACSource), ''), 'system')"),
|
|
("devParentPort", "COALESCE(NULLIF(TRIM(NEW.devParentPortSource), ''), 'system')"),
|
|
("devParentRelType", "COALESCE(NULLIF(TRIM(NEW.devParentRelTypeSource), ''), 'system')"),
|
|
("devVlan", "COALESCE(NULLIF(TRIM(NEW.devVlanSource), ''), 'system')"),
|
|
# User-only fields (only setDeviceData() / updateField() touch these)
|
|
("devOwner", "'user:api'"),
|
|
("devType", "'user:api'"),
|
|
("devFavorite", "'user:api'"),
|
|
("devGroup", "'user:api'"),
|
|
("devComments", "'user:api'"),
|
|
("devForceStatus", "'user:api'"),
|
|
("devStaticIP", "'user:api'"),
|
|
("devScan", "'user:api'"),
|
|
("devAlertDown", "'user:api'"),
|
|
("devCanSleep", "'user:api'"),
|
|
("devSkipRepeated", "'user:api'"),
|
|
("devLocation", "'user:api'"),
|
|
("devIsArchived", "'user:api'"),
|
|
("devReqNicsOnline", "'user:api'"),
|
|
("devCustomProps", "'user:api'"),
|
|
# Auto-computed / scan-only fields (no *Source column)
|
|
("devPrimaryIPv4", "'system'"),
|
|
("devPrimaryIPv6", "'system'"),
|
|
("devIcon", "'system'"),
|
|
("devSite", "'system'"),
|
|
("devSyncHubNode", "'system'"),
|
|
("devSourcePlugin", "'system'"),
|
|
# *Source fields themselves
|
|
("devMacSource", "'system'"),
|
|
("devNameSource", "'system'"),
|
|
("devFQDNSource", "'system'"),
|
|
("devLastIPSource", "'system'"),
|
|
("devVendorSource", "'system'"),
|
|
("devSSIDSource", "'system'"),
|
|
("devParentMACSource", "'system'"),
|
|
("devParentPortSource", "'system'"),
|
|
("devParentRelTypeSource", "'system'"),
|
|
("devVlanSource", "'system'"),
|
|
]
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# SQL builders
|
|
# ---------------------------------------------------------------------------
|
|
|
|
_HIST_DAYS_GUARD = (
|
|
"(SELECT CAST(COALESCE(setValue, '0') AS INTEGER) "
|
|
"FROM Settings WHERE setKey = 'DEV_HIST_DAYS') > 0"
|
|
)
|
|
|
|
_TRACKED_CHECK = (
|
|
"instr((SELECT COALESCE(setValue, '') FROM Settings "
|
|
"WHERE setKey = 'DEV_HIST_TRACKED'), '''{field}''') > 0"
|
|
)
|
|
|
|
|
|
def _build_update_trigger_sql() -> str:
|
|
"""Generate CREATE TRIGGER SQL for AFTER UPDATE on Devices."""
|
|
blocks = []
|
|
for field, attribution in _HIST_FIELDS:
|
|
tracked_check = _TRACKED_CHECK.format(field=field)
|
|
blocks.append(
|
|
f" INSERT INTO DevicesHistory "
|
|
f"(devGUID, changedColumn, oldValue, newValue, changedBy, timestamp)\n"
|
|
f" SELECT NEW.devGUID, '{field}', "
|
|
f"CAST(OLD.{field} AS TEXT), CAST(NEW.{field} AS TEXT),\n"
|
|
f" {attribution},\n"
|
|
f" datetime('now')\n"
|
|
f" WHERE (OLD.{field} IS NOT NEW.{field})\n"
|
|
f" AND COALESCE(NEW.devGUID, '') != ''\n"
|
|
f" AND {tracked_check};"
|
|
)
|
|
body = "\n".join(blocks)
|
|
return (
|
|
"CREATE TRIGGER trg_devhist_update\n"
|
|
"AFTER UPDATE ON Devices\n"
|
|
"FOR EACH ROW\n"
|
|
f"WHEN {_HIST_DAYS_GUARD}\n"
|
|
"BEGIN\n"
|
|
f"{body}\n"
|
|
"END;"
|
|
)
|
|
|
|
|
|
def _build_insert_trigger_sql() -> str:
|
|
"""Generate CREATE TRIGGER SQL for AFTER INSERT on Devices."""
|
|
blocks = []
|
|
for field, attribution in _HIST_FIELDS:
|
|
tracked_check = _TRACKED_CHECK.format(field=field)
|
|
blocks.append(
|
|
f" INSERT INTO DevicesHistory "
|
|
f"(devGUID, changedColumn, oldValue, newValue, changedBy, timestamp)\n"
|
|
f" SELECT NEW.devGUID, '{field}', NULL, CAST(NEW.{field} AS TEXT),\n"
|
|
f" {attribution},\n"
|
|
f" datetime('now')\n"
|
|
f" WHERE NEW.{field} IS NOT NULL\n"
|
|
f" AND COALESCE(NEW.devGUID, '') != ''\n"
|
|
f" AND {tracked_check};"
|
|
)
|
|
body = "\n".join(blocks)
|
|
return (
|
|
"CREATE TRIGGER trg_devhist_insert\n"
|
|
"AFTER INSERT ON Devices\n"
|
|
"FOR EACH ROW\n"
|
|
f"WHEN {_HIST_DAYS_GUARD}\n"
|
|
"BEGIN\n"
|
|
f"{body}\n"
|
|
"END;"
|
|
)
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Public ensure_* functions (called from database.py initDB)
|
|
# ---------------------------------------------------------------------------
|
|
|
|
def ensure_deviceshistory_table(sql) -> bool:
|
|
"""
|
|
Ensures DevicesHistory table and its indexes exist.
|
|
|
|
Also backfills any Devices rows that have NULL/empty devGUID so that
|
|
the history triggers can write records for them.
|
|
|
|
Idempotent — uses IF NOT EXISTS throughout.
|
|
|
|
Parameters:
|
|
sql: database cursor (must support execute()).
|
|
"""
|
|
try:
|
|
sql.execute("""
|
|
CREATE TABLE IF NOT EXISTS DevicesHistory (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
devGUID TEXT NOT NULL,
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
changedBy TEXT NOT NULL,
|
|
changedColumn TEXT NOT NULL,
|
|
oldValue TEXT,
|
|
newValue TEXT,
|
|
FOREIGN KEY (devGUID) REFERENCES Devices(devGUID) ON DELETE CASCADE
|
|
)
|
|
""")
|
|
|
|
sql.execute(
|
|
"CREATE INDEX IF NOT EXISTS idx_devhist_guid_column "
|
|
"ON DevicesHistory(devGUID, changedColumn)"
|
|
)
|
|
sql.execute(
|
|
"CREATE INDEX IF NOT EXISTS idx_devhist_timestamp "
|
|
"ON DevicesHistory(timestamp)"
|
|
)
|
|
|
|
# Backfill any Devices rows that are missing a GUID so the history
|
|
# triggers can record changes for them.
|
|
sql.execute("""
|
|
UPDATE Devices
|
|
SET devGUID = lower(
|
|
hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-' || '4' ||
|
|
substr(hex(randomblob(2)), 2) || '-' ||
|
|
substr('AB89', 1 + (abs(random()) % 4), 1) ||
|
|
substr(hex(randomblob(2)), 2) || '-' ||
|
|
hex(randomblob(6))
|
|
)
|
|
WHERE devGUID IS NULL OR TRIM(devGUID) = ''
|
|
""")
|
|
|
|
mylog("verbose", ["[db_history] DevicesHistory table and indexes ensured"])
|
|
return True
|
|
except Exception as e:
|
|
mylog("none", [f"[db_history] ERROR ensuring DevicesHistory table: {e}"])
|
|
return False
|
|
|
|
|
|
def ensure_deviceshistory_triggers(sql) -> bool:
|
|
"""
|
|
Drops and recreates the AFTER UPDATE and AFTER INSERT triggers on Devices.
|
|
|
|
Always drops first so that trigger logic is refreshed on every upgrade.
|
|
|
|
Parameters:
|
|
sql: database cursor (must support execute()).
|
|
"""
|
|
try:
|
|
sql.execute("DROP TRIGGER IF EXISTS trg_devhist_update")
|
|
sql.execute("DROP TRIGGER IF EXISTS trg_devhist_insert")
|
|
|
|
update_sql = _build_update_trigger_sql()
|
|
insert_sql = _build_insert_trigger_sql()
|
|
|
|
sql.execute(update_sql)
|
|
sql.execute(insert_sql)
|
|
|
|
mylog("verbose", ["[db_history] DevicesHistory triggers created"])
|
|
return True
|
|
except Exception as e:
|
|
mylog("none", [f"[db_history] ERROR creating DevicesHistory triggers: {e}"])
|
|
return False
|