Query

billing.db — monthly_summary

SQL

CREATE TABLE monthly_summary  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  year INTEGER NOT NULL,
  month INTEGER NOT NULL,
  total_customers INTEGER DEFAULT 0,
  active_customers INTEGER DEFAULT 0,
  monthly_invoices INTEGER DEFAULT 0,
  voucher_invoices INTEGER DEFAULT 0,
  paid_monthly_invoices INTEGER DEFAULT 0,
  paid_voucher_invoices INTEGER DEFAULT 0,
  unpaid_monthly_invoices INTEGER DEFAULT 0,
  unpaid_voucher_invoices INTEGER DEFAULT 0,
  monthly_revenue DECIMAL(15,2) DEFAULT 0,
  voucher_revenue DECIMAL(15,2) DEFAULT 0,
  monthly_unpaid DECIMAL(15,2) DEFAULT 0,
  voucher_unpaid DECIMAL(15,2) DEFAULT 0,
  total_revenue DECIMAL(15,2) DEFAULT 0,
  total_unpaid DECIMAL(15,2) DEFAULT 0,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(year, month)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
year INTEGER Rename | Drop
month INTEGER Rename | Drop
total_customers INTEGER ✓ Rename | Drop
active_customers INTEGER ✓ Rename | Drop
monthly_invoices INTEGER ✓ Rename | Drop
voucher_invoices INTEGER ✓ Rename | Drop
paid_monthly_invoices INTEGER ✓ Rename | Drop
paid_voucher_invoices INTEGER ✓ Rename | Drop
unpaid_monthly_invoices INTEGER ✓ Rename | Drop
unpaid_voucher_invoices INTEGER ✓ Rename | Drop
monthly_revenue DECIMAL(15,2) ✓ Rename | Drop
voucher_revenue DECIMAL(15,2) ✓ Rename | Drop
monthly_unpaid DECIMAL(15,2) ✓ Rename | Drop
voucher_unpaid DECIMAL(15,2) ✓ Rename | Drop
total_revenue DECIMAL(15,2) ✓ Rename | Drop
total_unpaid DECIMAL(15,2) ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_monthly_summary_created_at created_at SQL 
CREATE INDEX idx_monthly_summary_created_at
ON monthly_summary(created_at)
Drop
idx_monthly_summary_year_month
  • year
  • month
 SQL 
CREATE INDEX idx_monthly_summary_year_month
ON monthly_summary(year, month)
Drop
sqlite_autoindex_monthly_summary_1
  • year
  • month
 ✓ SQL
-- no sql found --
 Drop

Triggers

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