Query

billing.db — odp_connections

SQL

CREATE TABLE odp_connections  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  from_odp_id INTEGER NOT NULL,
  to_odp_id INTEGER NOT NULL,
  connection_type VARCHAR(50) DEFAULT 'fiber' CHECK (connection_type IN ('fiber', 'copper', 'wireless', 'microwave')),
  cable_length DECIMAL(8,2),
  cable_capacity VARCHAR(20) DEFAULT '1G' CHECK (cable_capacity IN ('100M', '1G', '10G', '100G')),
  status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'maintenance', 'inactive', 'damaged')),
  installation_date DATE,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (from_odp_id) REFERENCES odps(id) ON DELETE CASCADE,
  FOREIGN KEY (to_odp_id) REFERENCES odps(id) ON DELETE CASCADE,
  UNIQUE(from_odp_id, to_odp_id)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
from_odp_id INTEGER Rename | Drop
to_odp_id INTEGER Rename | Drop
connection_type VARCHAR(50) ✓ Rename | Drop
cable_length DECIMAL(8,2) ✓ Rename | Drop
cable_capacity VARCHAR(20) ✓ Rename | Drop
status VARCHAR(20) ✓ Rename | Drop
installation_date DATE ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
to_odp_id odps.id
from_odp_id odps.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
sqlite_autoindex_odp_connections_1
  • from_odp_id
  • to_odp_id
 ✓ SQL
-- no sql found --
 Drop

Triggers

Name SQL Drop?
update_odp_connections_updated_at SQL 
CREATE TRIGGER update_odp_connections_updated_at 
                AFTER UPDATE ON odp_connections
                FOR EACH ROW
            BEGIN
                UPDATE odp_connections SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
            END
Drop