-- pgbench -i SQL script

-- Variables:
--  - :scale int 1-... (MANDATORY)
--  - :fillfactor int 1-100
--  - :unlogged bool
--  - :no_vaccum bool
--  - :no_primary_keys bool
--  - :no_foreign_keys bool
--  - :tablespace str

\echo 'initializing pgbench tables...'

-- model scaling constants
\set nbranches 1
\set ntellers 10
\set naccounts 100000

\if :unlogged
\set unlogged 'UNLOGGED'
\else
\set unlogged ''
\endif

\if :fillfactor
\else
\echo 'setting fillfactor to 100'
\set fillfactor 100
\endif

\if :tablespace
\echo 'setting default tablespace as ' :'tablespace'
\endif

-- cleanup

-- handle BIGINT aid...
SELECT CASE WHEN :scale >= 20000 THEN 'bigint' ELSE 'int' END AS "AID_TYPE" \gset
SELECT CASE WHEN :scale >= 20000 THEN 84 ELSE 88 END AS "pgbench_accounts_fill_size" \gset

\echo 'creating tables...'

CREATE :unlogged TABLE pgbench_branches(
  bid INT NOT NULL,
  bbalance INT,
  filler CHAR(88)
);

CREATE :unlogged TABLE pgbench_tellers(
  tid INT NOT NULL,
  bid INT,
  tbalance INT,
  filler CHAR(84)
);

CREATE :unlogged TABLE pgbench_accounts(
  aid :AID_TYPE NOT NULL,
  bid INT,
  abalance INT,
  filler CHAR(:pgbench_accounts_fill_size)
) WITH (FILLFACTOR = :fillfactor);

CREATE :unlogged TABLE pgbench_history(
  tid INT,
  bid INT,
  aid :AID_TYPE,
  delta INT,
  mtime TIMESTAMP,
  filler CHAR(22)
);

BEGIN;

-- "filler" column defaults to NULL
INSERT INTO pgbench_branches(bid, bbalance)
  SELECT i, 0
  FROM generate_series(1, :nbranches * :scale) AS i;

-- "filler" column defaults to NULL
INSERT INTO pgbench_tellers(tid, bid, tbalance)
  SELECT i, (i-1) / :ntellers + 1, 0
  FROM generate_series(1, :ntellers * :scale) AS i;

COMMIT;

BEGIN;

TRUNCATE pgbench_accounts;

INSERT INTO pgbench_accounts(aid, bid, abalance)
  SELECT i, 1 + (i - 1) / :naccounts, 0
  FROM generate_series(1, :naccounts * :scale) AS i;
COMMIT;

\if :no_vacuum
\echo 'skipping vacuum...'
\else
\echo 'vacuum...'
VACUUM ANALYZE pgbench_branches;
VACUUM ANALYZE pgbench_tellers;
VACUUM ANALYZE pgbench_accounts;
VACUUM ANALYZE pgbench_history;
\endif

\if :no_primary_keys
\echo 'skipping primary keys...'
\else
\echo 'set primary keys...'
-- primary keys (none on pgbench_history)
ALTER TABLE pgbench_branches ADD PRIMARY KEY (bid);
ALTER TABLE pgbench_tellers ADD PRIMARY KEY (tid);
ALTER TABLE pgbench_accounts ADD PRIMARY KEY (aid);
\endif

\if :no_foreign_keys
\echo 'skipping foreign keys...'
\else
\echo 'set foreign keys...'
ALTER TABLE pgbench_tellers ADD FOREIGN KEY (bid) REFERENCES pgbench_branches;
ALTER TABLE pgbench_accounts ADD FOREIGN KEY (bid) REFERENCES pgbench_branches;
ALTER TABLE pgbench_history ADD FOREIGN KEY (bid) REFERENCES pgbench_branches;
ALTER TABLE pgbench_history ADD FOREIGN KEY (tid) REFERENCES pgbench_tellers;
ALTER TABLE pgbench_history ADD FOREIGN KEY (aid) REFERENCES pgbench_accounts;
\endif

\echo 'done.'
