210 lines
11 KiB
Python
210 lines
11 KiB
Python
from sqlalchemy import inspect, text
|
|
from .database import engine
|
|
|
|
USER_COLUMNS = {
|
|
"allowed_mesh_groups": "JSON DEFAULT '[]'::json",
|
|
"asset_access_scopes": "JSON DEFAULT '[]'::json",
|
|
"profile_department": "VARCHAR(150) NULL",
|
|
"profile_location": "VARCHAR(150) NULL",
|
|
"language_code": "VARCHAR(10) DEFAULT 'en'",
|
|
"persist_browser_state": "BOOLEAN DEFAULT FALSE",
|
|
"toast_position": "VARCHAR(30) DEFAULT 'top-right'",
|
|
"toast_duration_seconds": "INTEGER DEFAULT 6",
|
|
"theme_mode": "VARCHAR(20) DEFAULT 'light'",
|
|
"is_protected": "BOOLEAN NOT NULL DEFAULT FALSE",
|
|
}
|
|
|
|
CHART_COLUMNS = {
|
|
"value_mode": "VARCHAR(30) DEFAULT 'raw'",
|
|
"value_mappings": "JSON DEFAULT '[]'::json",
|
|
"excluded_statuses": "JSON DEFAULT '[]'::json",
|
|
"is_system": "BOOLEAN DEFAULT FALSE",
|
|
}
|
|
|
|
FIELD_DEFINITION_COLUMNS = {
|
|
"is_default": "BOOLEAN NOT NULL DEFAULT FALSE",
|
|
"list_width": "INTEGER NULL",
|
|
}
|
|
|
|
CATEGORY_FIELD_COLUMNS = {
|
|
"readonly": "BOOLEAN DEFAULT FALSE",
|
|
"field_definition_id": "INTEGER NULL REFERENCES field_definitions(id) ON DELETE CASCADE",
|
|
}
|
|
|
|
STATUS_COLUMNS = {
|
|
"use_for_issue": "BOOLEAN DEFAULT FALSE",
|
|
"use_for_return": "BOOLEAN DEFAULT FALSE",
|
|
}
|
|
|
|
|
|
MESH_MAPPING_COLUMNS = {
|
|
"update_rule": "VARCHAR(30) NOT NULL DEFAULT 'fill_empty'",
|
|
"is_system": "BOOLEAN NOT NULL DEFAULT FALSE",
|
|
}
|
|
|
|
|
|
INSTALLED_SOFTWARE_COLUMNS = {
|
|
"platform": "VARCHAR(40) NOT NULL DEFAULT 'unknown'",
|
|
}
|
|
|
|
|
|
JOB_DEFINITION_COLUMNS = {
|
|
"job_kind": "VARCHAR(30) NOT NULL DEFAULT 'script'",
|
|
"configuration": "JSON DEFAULT '{}'::json",
|
|
}
|
|
|
|
SOFTWARE_JOB_COLUMNS = {
|
|
"action": "VARCHAR(30) NOT NULL DEFAULT 'run'",
|
|
"job_type": "VARCHAR(50) NOT NULL DEFAULT 'software_inventory'",
|
|
"priority": "INTEGER NOT NULL DEFAULT 100",
|
|
"result_data": "JSON DEFAULT '{}'::json",
|
|
"attempt_count": "INTEGER NOT NULL DEFAULT 0",
|
|
"max_attempts": "INTEGER NOT NULL DEFAULT 1",
|
|
"scheduled_at": "TIMESTAMP NULL",
|
|
"resolved_script": "TEXT NULL",
|
|
}
|
|
|
|
ASSET_COLUMNS = {
|
|
"mesh_last_seen": "TIMESTAMP NULL",
|
|
"mesh_online": "BOOLEAN NULL",
|
|
"mesh_online_state": "VARCHAR(20) NOT NULL DEFAULT 'unknown'",
|
|
"mesh_presence_updated_at": "TIMESTAMP NULL",
|
|
"mesh_last_online_at": "TIMESTAMP NULL",
|
|
"mesh_presence_source": "VARCHAR(40) NULL",
|
|
"mesh_sync_status": "VARCHAR(30) NULL",
|
|
"mesh_sync_message": "TEXT NULL",
|
|
"mesh_conflicts": "JSON DEFAULT '{}'::json",
|
|
"mesh_source_data": "JSON DEFAULT '{}'::json",
|
|
"mesh_hardware_data": "JSON DEFAULT '{}'::json",
|
|
"mesh_software_data": "JSON DEFAULT '[]'::json",
|
|
"mesh_inventory_updated_at": "TIMESTAMP NULL",
|
|
"storage_details": "TEXT NULL",
|
|
"gpu_name": "TEXT NULL",
|
|
"tpm_version": "VARCHAR(50) NULL",
|
|
"memory_details": "TEXT NULL",
|
|
"antivirus": "TEXT NULL",
|
|
"mesh_group": "VARCHAR(180) NULL",
|
|
"assigned_on": "VARCHAR(20) NULL",
|
|
"room_number": "VARCHAR(80) NULL",
|
|
"parent_asset_id": "INTEGER NULL REFERENCES assets(id) ON DELETE SET NULL",
|
|
"mesh_mtype": "INTEGER NULL",
|
|
}
|
|
|
|
|
|
def apply_lightweight_migrations() -> None:
|
|
inspector = inspect(engine)
|
|
if "assets" not in inspector.get_table_names():
|
|
return
|
|
existing = {column["name"] for column in inspector.get_columns("assets")}
|
|
with engine.begin() as connection:
|
|
for name, ddl in ASSET_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE assets ADD COLUMN "{name}" {ddl}'))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_assets_mesh_sync_status ON assets (mesh_sync_status)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_assets_mesh_online ON assets (mesh_online)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_assets_mesh_online_state ON assets (mesh_online_state)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_assets_room_number ON assets (room_number)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_assets_parent_asset_id ON assets (parent_asset_id)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_assets_mesh_mtype ON assets (mesh_mtype)"))
|
|
inspector = inspect(engine)
|
|
with engine.begin() as connection:
|
|
if "users" in inspector.get_table_names():
|
|
existing = {column["name"] for column in inspector.get_columns("users")}
|
|
for name, ddl in USER_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE users ADD COLUMN "{name}" {ddl}'))
|
|
if "field_definitions" in inspector.get_table_names():
|
|
existing = {column["name"] for column in inspector.get_columns("field_definitions")}
|
|
for name, ddl in FIELD_DEFINITION_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE field_definitions ADD COLUMN "{name}" {ddl}'))
|
|
connection.execute(text("""
|
|
UPDATE field_definitions
|
|
SET is_default = TRUE
|
|
WHERE field_name IN (
|
|
'asset_tag','name','manufacturer','model','serial_number',
|
|
'status','assigned_to','assigned_on','location','room_number','notes'
|
|
)
|
|
AND is_default = FALSE
|
|
"""))
|
|
if "category_fields" in inspector.get_table_names():
|
|
existing = {column["name"] for column in inspector.get_columns("category_fields")}
|
|
for name, ddl in CATEGORY_FIELD_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE category_fields ADD COLUMN "{name}" {ddl}'))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_category_fields_field_definition_id ON category_fields (field_definition_id)"))
|
|
if "status_options" in inspector.get_table_names():
|
|
existing = {column["name"] for column in inspector.get_columns("status_options")}
|
|
for name, ddl in STATUS_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE status_options ADD COLUMN "{name}" {ddl}'))
|
|
if "mesh_field_mappings" in inspector.get_table_names():
|
|
existing = {column["name"] for column in inspector.get_columns("mesh_field_mappings")}
|
|
for name, ddl in MESH_MAPPING_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE mesh_field_mappings ADD COLUMN "{name}" {ddl}'))
|
|
if "installed_software" in inspector.get_table_names():
|
|
existing = {column["name"] for column in inspector.get_columns("installed_software")}
|
|
for name, ddl in INSTALLED_SOFTWARE_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE installed_software ADD COLUMN "{name}" {ddl}'))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_installed_software_platform ON installed_software (platform)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_installed_software_summary ON installed_software (platform, name, publisher)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_installed_software_summary_version ON installed_software (name, publisher, platform, version)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_installed_software_list ON installed_software (name, version, asset_id, id)"))
|
|
if "software_jobs" in inspector.get_table_names():
|
|
connection.execute(text("""
|
|
UPDATE installed_software AS software
|
|
SET platform = COALESCE(
|
|
NULLIF((
|
|
SELECT LOWER(software_jobs.platform)
|
|
FROM software_jobs
|
|
WHERE software_jobs.asset_id = software.asset_id
|
|
ORDER BY software_jobs.created_at DESC
|
|
LIMIT 1
|
|
), ''),
|
|
'unknown'
|
|
)
|
|
WHERE software.platform IS NULL OR software.platform = '' OR software.platform = 'unknown'
|
|
"""))
|
|
if "job_definitions" in inspector.get_table_names():
|
|
existing = {column["name"] for column in inspector.get_columns("job_definitions")}
|
|
for name, ddl in JOB_DEFINITION_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE job_definitions ADD COLUMN "{name}" {ddl}'))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_job_definitions_job_kind ON job_definitions (job_kind)"))
|
|
connection.execute(text("UPDATE job_definitions SET job_kind = 'script' WHERE job_kind IS NULL OR job_kind = ''"))
|
|
if "software_jobs" in inspector.get_table_names():
|
|
existing = {column["name"] for column in inspector.get_columns("software_jobs")}
|
|
for name, ddl in SOFTWARE_JOB_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE software_jobs ADD COLUMN "{name}" {ddl}'))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_software_jobs_action ON software_jobs (action)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_software_jobs_job_type ON software_jobs (job_type)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_software_jobs_priority ON software_jobs (priority)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_software_jobs_scheduled_at ON software_jobs (scheduled_at)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_software_jobs_recent ON software_jobs (created_at DESC, id DESC)"))
|
|
connection.execute(text("CREATE INDEX IF NOT EXISTS ix_software_jobs_timeout_scan ON software_jobs (callback_completed_at, callback_expires_at, status)"))
|
|
connection.execute(text("UPDATE software_jobs SET job_type = 'software_inventory' WHERE job_type IS NULL OR job_type = ''"))
|
|
connection.execute(text("UPDATE software_jobs SET priority = 100 WHERE priority IS NULL"))
|
|
connection.execute(text("UPDATE software_jobs SET attempt_count = 0 WHERE attempt_count IS NULL"))
|
|
connection.execute(text("UPDATE software_jobs SET max_attempts = 1 WHERE max_attempts IS NULL"))
|
|
if "chart_definitions" in inspector.get_table_names():
|
|
existing = {column["name"] for column in inspector.get_columns("chart_definitions")}
|
|
for name, ddl in CHART_COLUMNS.items():
|
|
if name not in existing:
|
|
connection.execute(text(f'ALTER TABLE chart_definitions ADD COLUMN "{name}" {ddl}'))
|
|
# Das vorhandene Standarddiagramm wird nach dem Update sofort sinnvoll zusammengefasst.
|
|
connection.execute(text("""
|
|
UPDATE chart_definitions
|
|
SET value_mode = 'automatic'
|
|
WHERE group_field = 'operating_system'
|
|
AND name = 'Betriebssysteme'
|
|
AND COALESCE(value_mode, 'raw') = 'raw'
|
|
"""))
|
|
connection.execute(text("""
|
|
UPDATE chart_definitions
|
|
SET is_system = TRUE
|
|
WHERE name IN ('Betriebssysteme', 'Geräte nach Kategorie und Status', 'Hersteller', 'Statusübersicht')
|
|
"""))
|