Query

billing.db — customers

SQL

CREATE TABLE customers  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  phone TEXT UNIQUE NOT NULL,
  pppoe_username TEXT,
  email TEXT,
  address TEXT,
  latitude DECIMAL(10,8),
  longitude DECIMAL(11,8),
  package_id INTEGER,
  pppoe_profile TEXT,
  status TEXT DEFAULT 'active',
  join_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  -- Cable connection fields
                cable_type TEXT,
  cable_length INTEGER,
  port_number INTEGER,
  cable_status TEXT DEFAULT 'connected',
  cable_notes TEXT,
  billing_day INTEGER DEFAULT 15,
  auto_suspension BOOLEAN DEFAULT 1,
  odp_id INTEGER,
  static_ip TEXT,
  mac_address TEXT,
  assigned_ip TEXT,
  whatsapp_lid TEXT,
  password TEXT,
  FOREIGN KEY (package_id) REFERENCES packages (id)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
username TEXT Rename | Drop
name TEXT Rename | Drop
phone TEXT Rename | Drop
pppoe_username TEXT ✓ Rename | Drop
email TEXT ✓ Rename | Drop
address TEXT ✓ Rename | Drop
latitude DECIMAL(10,8) ✓ Rename | Drop
longitude DECIMAL(11,8) ✓ Rename | Drop
package_id INTEGER ✓ Rename | Drop
pppoe_profile TEXT ✓ Rename | Drop
status TEXT ✓ Rename | Drop
join_date DATETIME ✓ Rename | Drop
cable_type TEXT ✓ Rename | Drop
cable_length INTEGER ✓ Rename | Drop
port_number INTEGER ✓ Rename | Drop
cable_status TEXT ✓ Rename | Drop
cable_notes TEXT ✓ Rename | Drop
billing_day INTEGER ✓ Rename | Drop
auto_suspension BOOLEAN ✓ Rename | Drop
odp_id INTEGER ✓ Rename | Drop
static_ip TEXT ✓ Rename | Drop
mac_address TEXT ✓ Rename | Drop
assigned_ip TEXT ✓ Rename | Drop
whatsapp_lid TEXT ✓ Rename | Drop
password TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
package_id packages.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_customers_package package_id SQL 
CREATE INDEX idx_customers_package
ON customers(package_id)
Drop
idx_customers_status status SQL 
CREATE INDEX idx_customers_status
ON customers(status)
Drop
idx_customers_whatsapp_lid whatsapp_lid ✓ SQL 
CREATE UNIQUE INDEX idx_customers_whatsapp_lid
ON customers(whatsapp_lid)
Drop
sqlite_autoindex_customers_1 username ✓ SQL
-- no sql found --
 Drop
sqlite_autoindex_customers_2 phone ✓ SQL
-- no sql found --
 Drop