Query

billing.db — network_segments

SQL

CREATE TABLE network_segments  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(100) NOT NULL,
  start_odp_id INTEGER NOT NULL,
  end_odp_id INTEGER,
  segment_type VARCHAR(50) DEFAULT 'Backbone' CHECK (segment_type IN ('Backbone', 'Distribution', 'Access')),
  cable_length DECIMAL(10,2),
  status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'maintenance', 'damaged', 'inactive')),
  installation_date DATE,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (start_odp_id) REFERENCES odps(id) ON DELETE CASCADE,
  FOREIGN KEY (end_odp_id) REFERENCES odps(id) ON DELETE CASCADE
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
name VARCHAR(100) Rename | Drop
start_odp_id INTEGER Rename | Drop
end_odp_id INTEGER ✓ Rename | Drop
segment_type VARCHAR(50) ✓ Rename | Drop
cable_length DECIMAL(10,2) ✓ 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
end_odp_id odps.id
start_odp_id odps.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_network_segments_end end_odp_id SQL 
CREATE INDEX idx_network_segments_end
ON network_segments(end_odp_id)
Drop
idx_network_segments_start start_odp_id SQL 
CREATE INDEX idx_network_segments_start
ON network_segments(start_odp_id)
Drop
idx_network_segments_status status SQL 
CREATE INDEX idx_network_segments_status
ON network_segments(status)
Drop

Triggers

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