41 lines
1.5 KiB
MySQL
41 lines
1.5 KiB
MySQL
|
-- Cached UISP inventory and the one-to-one link to a hardware asset.
|
||
|
|
CREATE TABLE IF NOT EXISTS uisp_devices (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
external_id VARCHAR(255) NOT NULL UNIQUE,
|
||
|
|
name VARCHAR(255),
|
||
|
|
display_name VARCHAR(255),
|
||
|
|
hostname VARCHAR(255),
|
||
|
|
mac_address VARCHAR(64),
|
||
|
|
serial_number VARCHAR(255),
|
||
|
|
vendor VARCHAR(255),
|
||
|
|
model VARCHAR(255),
|
||
|
|
platform VARCHAR(255),
|
||
|
|
device_type VARCHAR(100),
|
||
|
|
device_role VARCHAR(100),
|
||
|
|
ip_addresses JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
|
|
status VARCHAR(100),
|
||
|
|
last_seen TIMESTAMPTZ,
|
||
|
|
device_link TEXT,
|
||
|
|
raw_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_uisp_devices_name ON uisp_devices(name);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_uisp_devices_serial_number ON uisp_devices(serial_number);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_uisp_devices_mac_address ON uisp_devices(mac_address);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS hardware_uisp_links (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
hardware_id INTEGER NOT NULL REFERENCES hardware_assets(id) ON DELETE CASCADE,
|
||
|
|
uisp_device_id INTEGER NOT NULL REFERENCES uisp_devices(id) ON DELETE CASCADE,
|
||
|
|
linked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
linked_by_user_id INTEGER,
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE (hardware_id),
|
||
|
|
UNIQUE (uisp_device_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_hardware_uisp_links_hardware ON hardware_uisp_links(hardware_id);
|