Query

billing.db — odps

SQL

CREATE TABLE odps  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE,
  code VARCHAR(50) NOT NULL UNIQUE,
  latitude DECIMAL(10,8) NOT NULL,
  longitude DECIMAL(11,8) NOT NULL,
  address TEXT,
  capacity INTEGER DEFAULT 64,
  used_ports INTEGER DEFAULT 0,
  status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'maintenance', 'inactive')),
  installation_date DATE,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  parent_odp_id INTEGER,
  is_pole INTEGER DEFAULT 0
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
name VARCHAR(100) Rename | Drop
code VARCHAR(50) Rename | Drop
latitude DECIMAL(10,8) Rename | Drop
longitude DECIMAL(11,8) Rename | Drop
address TEXT ✓ Rename | Drop
capacity INTEGER ✓ Rename | Drop
used_ports INTEGER ✓ 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
parent_odp_id INTEGER ✓ Rename | Drop
is_pole INTEGER ✓ Rename | Drop

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_odps_location
  • latitude
  • longitude
 SQL 
CREATE INDEX idx_odps_location
ON odps(latitude, longitude)
Drop
idx_odps_status status SQL 
CREATE INDEX idx_odps_status
ON odps(status)
Drop
sqlite_autoindex_odps_1 name ✓ SQL
-- no sql found --
 Drop
sqlite_autoindex_odps_2 code ✓ SQL
-- no sql found --
 Drop

Triggers

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