126 lines
3.3 KiB
Python
126 lines
3.3 KiB
Python
#!/usr/bin/env python3
|
|
import json
|
|
|
|
with open('/home/mdares/.node-red/flows.json', 'r') as f:
|
|
flows = json.load(f)
|
|
|
|
# SQL to create the table
|
|
create_table_sql = """CREATE TABLE IF NOT EXISTS anomaly_events (
|
|
event_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
timestamp BIGINT NOT NULL,
|
|
work_order_id VARCHAR(50),
|
|
anomaly_type VARCHAR(50) NOT NULL,
|
|
severity ENUM('info', 'warning', 'critical') NOT NULL DEFAULT 'warning',
|
|
title VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
data_json TEXT,
|
|
kpi_snapshot_json TEXT,
|
|
status ENUM('active', 'acknowledged', 'resolved') DEFAULT 'active',
|
|
acknowledged_at BIGINT,
|
|
resolved_at BIGINT,
|
|
auto_resolved BOOLEAN DEFAULT FALSE,
|
|
cycle_count INT,
|
|
occurrence_count INT DEFAULT 1,
|
|
last_occurrence BIGINT,
|
|
notes TEXT,
|
|
INDEX idx_timestamp (timestamp),
|
|
INDEX idx_work_order (work_order_id),
|
|
INDEX idx_status (status),
|
|
INDEX idx_type (anomaly_type),
|
|
INDEX idx_severity (severity)
|
|
)"""
|
|
|
|
# Find the tab where we'll add the setup node (use the same tab as other nodes)
|
|
tab_id = None
|
|
for node in flows:
|
|
if node.get('type') == 'tab':
|
|
tab_id = node['id']
|
|
break
|
|
|
|
if not tab_id:
|
|
print("✗ Could not find a tab to add the node to")
|
|
exit(1)
|
|
|
|
# Create an inject node to trigger the table creation
|
|
inject_node = {
|
|
"id": "create_table_inject_temp",
|
|
"type": "inject",
|
|
"z": tab_id,
|
|
"name": "CREATE anomaly_events table (run once)",
|
|
"props": [{"p": "payload"}],
|
|
"repeat": "",
|
|
"crontab": "",
|
|
"once": False,
|
|
"onceDelay": 0.1,
|
|
"topic": "",
|
|
"payload": "",
|
|
"payloadType": "date",
|
|
"x": 250,
|
|
"y": 900,
|
|
"wires": [["create_table_function_temp"]]
|
|
}
|
|
|
|
# Create a function node with the SQL
|
|
function_node = {
|
|
"id": "create_table_function_temp",
|
|
"type": "function",
|
|
"z": tab_id,
|
|
"name": "Create Table SQL",
|
|
"func": f"""// Create anomaly_events table
|
|
msg.topic = `{create_table_sql}`;
|
|
msg.payload = [];
|
|
return msg;""",
|
|
"outputs": 1,
|
|
"timeout": 0,
|
|
"noerr": 0,
|
|
"initialize": "",
|
|
"finalize": "",
|
|
"libs": [],
|
|
"x": 500,
|
|
"y": 900,
|
|
"wires": [["create_table_mysql_temp"]]
|
|
}
|
|
|
|
# Create a MySQL node to execute the SQL
|
|
mysql_node = {
|
|
"id": "create_table_mysql_temp",
|
|
"type": "mysql",
|
|
"z": tab_id,
|
|
"mydb": "00d8ad2b0277f906",
|
|
"name": "Execute Create Table",
|
|
"x": 730,
|
|
"y": 900,
|
|
"wires": [["create_table_debug_temp"]]
|
|
}
|
|
|
|
# Create a debug node to show result
|
|
debug_node = {
|
|
"id": "create_table_debug_temp",
|
|
"type": "debug",
|
|
"z": tab_id,
|
|
"name": "Table Created",
|
|
"active": True,
|
|
"tosidebar": True,
|
|
"console": False,
|
|
"tostatus": False,
|
|
"complete": "true",
|
|
"targetType": "full",
|
|
"statusVal": "",
|
|
"statusType": "auto",
|
|
"x": 960,
|
|
"y": 900,
|
|
"wires": []
|
|
}
|
|
|
|
# Add all nodes to flows
|
|
flows.extend([inject_node, function_node, mysql_node, debug_node])
|
|
|
|
with open('/home/mdares/.node-red/flows.json', 'w') as f:
|
|
json.dump(flows, f, indent=4)
|
|
|
|
print("✓ Added CREATE TABLE nodes to flows.json")
|
|
print(" - Inject node: 'CREATE anomaly_events table (run once)'")
|
|
print(" - After Node-RED restarts, click this inject button ONCE to create the table")
|
|
print(" - Check debug panel to confirm table creation")
|
|
print(" - These temporary nodes can be deleted after use")
|