Query

billing.db — onu_devices

SQL

CREATE TABLE onu_devices  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(100) NOT NULL,
  serial_number VARCHAR(100) UNIQUE,
  mac_address VARCHAR(17),
  ip_address VARCHAR(15),
  status VARCHAR(20) DEFAULT 'online' CHECK (status IN ('online', 'offline', 'maintenance')),
  latitude DECIMAL(10,8),
  longitude DECIMAL(11,8),
  customer_id INTEGER,
  odp_id INTEGER,
  ssid VARCHAR(50),
  password VARCHAR(100),
  model VARCHAR(100),
  firmware_version VARCHAR(50),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  FOREIGN KEY (odp_id) REFERENCES odps(id) ON DELETE SET NULL
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
name VARCHAR(100) Rename | Drop
serial_number VARCHAR(100) ✓ Rename | Drop
mac_address VARCHAR(17) ✓ Rename | Drop
ip_address VARCHAR(15) ✓ Rename | Drop
status VARCHAR(20) ✓ Rename | Drop
latitude DECIMAL(10,8) ✓ Rename | Drop
longitude DECIMAL(11,8) ✓ Rename | Drop
customer_id INTEGER ✓ Rename | Drop
odp_id INTEGER ✓ Rename | Drop
ssid VARCHAR(50) ✓ Rename | Drop
password VARCHAR(100) ✓ Rename | Drop
model VARCHAR(100) ✓ Rename | Drop
firmware_version VARCHAR(50) ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
odp_id odps.id
customer_id customers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_onu_devices_coordinates
  • latitude
  • longitude
 SQL 
CREATE INDEX idx_onu_devices_coordinates
ON onu_devices(latitude, longitude)
Drop
idx_onu_devices_customer_id customer_id SQL 
CREATE INDEX idx_onu_devices_customer_id
ON onu_devices(customer_id)
Drop
idx_onu_devices_odp_id odp_id SQL 
CREATE INDEX idx_onu_devices_odp_id
ON onu_devices(odp_id)
Drop
idx_onu_devices_status status SQL 
CREATE INDEX idx_onu_devices_status
ON onu_devices(status)
Drop
sqlite_autoindex_onu_devices_1 serial_number ✓ SQL
-- no sql found --
 Drop