-- database_create - Create Database
--
-- Description:
--		This script creates the tables required for the KASP database.
--      Matches kaspimport.pl at 12/03/2009

-- Get rid of tables in reverse order

drop table if exists parameters_policies;
drop table if exists serialmodes;
drop table if exists parameters;
drop table if exists categories;
drop table if exists dnsseckeys;
drop table if exists zones;
drop table if exists keypairs;
drop table if exists securitymodules;
drop table if exists policies;
drop table if exists dbadmin;

-- Now for the tables that actually hold real data.

-- dbadmin - holds the version of the database
CREATE TABLE "dbadmin" (
    "version" INTEGER NOT NULL DEFAULT (1),
    "description" TEXT
);

insert into dbadmin values (2, "This needs to be in sync with the version defined in database.h");

-- security modules - store information about all the sms used
create table securitymodules (
  id            integer primary key autoincrement,    -- id for sm
  name          varchar(30) not null,  -- name of the sm
  capacity      mediumint not null,
  requirebackup tinyint default 1
);

-- categories - stores the possible categories (or uses) of parameters
create table categories (
  id            integer primary key autoincrement,    -- id for category_id
  name          varchar(30) not null  -- name of the category_id
);

-- parameters - stores the types of parameters available
create table parameters (
  id            integer primary key autoincrement,    -- id for parameters
  name          varchar(30) not null,  -- name of the parameter
  description   varchar(255), -- description of the paramter
  category_id         tinyint not null,      -- category_id of the parameter

  unique(name, category_id),
  foreign key (category_id) references categories (id)
);

create table serialmodes (
  id            integer primary key autoincrement,    -- id for serial mode
  name          varchar(30),  -- name of the serial mode
  description   varchar(255) -- description of the serial mode
);

-- policies - 
create table policies (
  id            integer primary key autoincrement,    -- id
  name          varchar(30) not null,  -- name of the policy
  description   varchar(255), -- description of the
  salt          varchar(512), -- value of the salt
  salt_stamp    varchar(64),  -- when the salt was generated
  audit         text, -- contents of <Audit>

  unique(name)
);

-- zones - stores the zones
create table zones(
  id            integer primary key autoincrement,    -- id
  name          varchar(300) not null ,  -- name of the parameter
  policy_id     mediumint not null,
  signconf      varchar(4096),  -- where is the signconf
  input         varchar(4096),  -- where is the input
  output        varchar(4096),  -- where is the output
  
  foreign key (policy_id) references policies (id)
);

-- stores the private key info
create table keypairs(
  id     integer primary key autoincrement,
  HSMkey_id  varchar(255) not null,
  algorithm     tinyint not null,             -- algorithm code
  size          smallint,
  securitymodule_id          tinyint,                      -- where the key is stored
  generate      varchar(64) null default null,  -- time key inserted into database
  policy_id        mediumint,
  compromisedflag tinyint,
  publickey     varchar(1024),                -- public key data
  pre_backup    varchar(64) null default null,  -- time when backup was started
  backup        varchar(64) null default null,  -- time when backup was finished
  fixedDate     tinyint default 0,            -- Set to 1 to stop dates from being set according to the policy timings        
  
  foreign key (securitymodule_id) references securitymodules (id),
  foreign key (policy_id) references policies (id)
);

-- stores meta data about keys (actual keys are in a (soft)hsm)
create table dnsseckeys (
  id            integer primary key autoincrement,  -- unique id of the key
  keypair_id    smallint,
  zone_id        mediumint,
  keytype       smallint not null,             -- zsk or ksk (use code in dnskey record)
  state         tinyint,                      -- state of the key (defines valid fields)
  publish       varchar(64) null default null,  -- time when key published into the zone
  ready         varchar(64) null default null,  -- time when the key is ready for use
  active        varchar(64) null default null,  -- time when the key was made active
  retire        varchar(64) null default null,  -- time when the key retires
  dead          varchar(64) null default null,  -- time when key is slated for removal

  foreign key (keypair_id) references keypairs (id)
);

-- parameters_policies - join table to hold the values of parameters
create table parameters_policies (
  id            integer primary key autoincrement,    -- id
        parameter_id mediumint not null,
        policy_id mediumint not null,
        value int,                                -- integer value of this key
        foreign key (parameter_id) references parameters (id),
        foreign key (policy_id) references policies (id)
);

-- The VIEWS
drop view if exists PARAMETER_VIEW;

create view PARAMETER_VIEW as
select p.name as name, c.name as category, pp.parameter_id as parameter_id, 
    pp.value as value, pp.policy_id as policy_id 
from parameters_policies pp, parameters p, categories c
where pp.parameter_id = p.id
and p.category_id = c.id;

drop view if exists PARAMETER_LIST;
create view PARAMETER_LIST as
select p.name as name, c.name as category, p.id as parameter_id
from parameters p, categories c
where p.category_id = c.id;

drop view if exists KEYDATA_VIEW;
create view KEYDATA_VIEW as
select k.id as id, d.state as state, k.generate as generate, d.publish as publish,
    d.ready as ready, d.active as active, d.retire as retire, d.dead as dead, 
    d.keytype as keytype, k.algorithm as algorithm, k.HSMkey_id as location,
    d.zone_id as zone_id, k.policy_id as policy_id, 
    k.securitymodule_id as securitymodule_id, k.size as size,
    k.compromisedflag as compromisedflag,
    k.fixedDate as fixedDate
from  keypairs k left outer join dnsseckeys d
on k.id = d.keypair_id;

drop view if exists KEYALLOC_VIEW;
create view KEYALLOC_VIEW as
select v.id as id, location, algorithm, policy_id, securitymodule_id, size, compromisedflag, d.zone_id as zone_id from
(select k.id as id, k.HSMkey_id as location, z.id as zone_id, k.algorithm as algorithm, k.policy_id as policy_id, k.securitymodule_id as securitymodule_id, k.size as size,
    k.compromisedflag as compromisedflag
from keypairs k left join zones z where k.policy_id = z.policy_id )  v 
left outer join dnsseckeys d
on d.zone_id = v.zone_id
and d.keypair_id = v.id;

-- insert default data

-- default categories
insert into categories (id, name) values (1, "signature");
insert into categories (id, name) values (2, "denial");
insert into categories (id, name) values (3, "ksk");
insert into categories (id, name) values (4, "zsk");
insert into categories (id, name) values (5, "keys");
insert into categories (id, name) values (6, "enforcer");
insert into categories (id, name) values (7, "zone");
insert into categories (id, name) values (8, "parent");

-- default serial number modes
insert into serialmodes (id, name, description) values (1, "unixtime", "seconds since 1 Jan 1970");
insert into serialmodes (id, name, description) values (2, "counter", "add one everytime updated");
insert into serialmodes (id, name, description) values (3, "datecounter", "YYYYMMDDXX");
insert into serialmodes (id, name, description) values (4, "keep", "Signer should not change the serial");

-- default parameters
insert into parameters (name, description, category_id) select "resign", "re-signing interval", id from categories where name="signature";
insert into parameters (name, description, category_id) select "refresh", "how old a signature may become before it needs to be re-signed",id from categories where name="signature";
insert into parameters (name, description, category_id) select "jitter", "jitter to use in signature inception and expiration times", id from categories where name="signature";
insert into parameters (name, description, category_id) select "clockskew", "estimated max clockskew expected in clients", id from categories where name="signature";
insert into parameters (name, description, category_id) select "ttl", "ttl for RRSIGS", id from categories where name="signature";
insert into parameters (name, description, category_id) select "valdefault", "signature validity period", id from categories where name="signature";
insert into parameters (name, description, category_id) select "valdenial", "nsec(3) validity period", id from categories where name="signature";

insert into parameters (name, description, category_id) select "ttl", "ttl for nsec(3) rrs", id from categories where name="denial";
insert into parameters (name, description, category_id) select "version", "nsec version (0 or 3)", id from categories where name="denial";
insert into parameters (name, description, category_id) select "optout", "opt out flag for nsec3", id from categories where name="denial";
insert into parameters (name, description, category_id) select "resalt", "re-salting interval", id from categories where name="denial";
insert into parameters (name, description, category_id) select "algorithm", "nsec3 algorithm", id from categories where name="denial";
insert into parameters (name, description, category_id) select "iterations", "nsec3 iterations", id from categories where name="denial";
insert into parameters (name, description, category_id) select "saltlength", "nsec3 salt length", id from categories where name="denial";

insert into parameters (name, description, category_id) select "ttl", "ttl for ksk rrs", id from categories where name="keys";
insert into parameters (name, description, category_id) select "retiresafety", "ksk retirement safety factor", id from categories where name="keys";
insert into parameters (name, description, category_id) select "publishsafety", "ksk publish safety factor", id from categories where name="keys";

insert into parameters (name, description, category_id) select "algorithm", "ksk algorithm", id from categories where name="ksk";
insert into parameters (name, description, category_id) select "bits", "ksk key size", id from categories where name="ksk";
insert into parameters (name, description, category_id) select "lifetime", "ksk lifetime", id from categories where name="ksk";
insert into parameters (name, description, category_id) select "standby", "number of ksks is use at any one time", id from categories where name="ksk";
insert into parameters (name, description, category_id) select "repository", "default ksk sm (for newly generated keys)", id from categories where name="ksk";
insert into parameters (name, description, category_id) select "rfc5011", "are we doing rfc5011?", id from categories where name="ksk";

insert into parameters (name, description, category_id) select "algorithm", "zsk algorithm", id from categories where name="zsk";
insert into parameters (name, description, category_id) select "bits", "zsk key size", id from categories where name="zsk";
insert into parameters (name, description, category_id) select "lifetime", "zsk lifetime", id from categories where name="zsk";
insert into parameters (name, description, category_id) select "standby", "number of zsks is use at any one time", id from categories where name="zsk";
insert into parameters (name, description, category_id) select "repository", "default zsk sm (for newly generated keys)", id from categories where name="zsk";

insert into parameters (name, description, category_id) select "propagationdelay", "Dp", id from categories where name="zone";
insert into parameters (name, description, category_id) select "ttl", "ttl of the soa", id from categories where name="zone";
insert into parameters (name, description, category_id) select "min", "min of the soa", id from categories where name="zone";
insert into parameters (name, description, category_id) select "serial", "how serial no are changed", id from categories where name="zone";

insert into parameters (name, description, category_id) select "propagationdelay", "Dp", id from categories where name="parent";
insert into parameters (name, description, category_id) select "ttl", "ttl of the soa", id from categories where name="parent";
insert into parameters (name, description, category_id) select "min", "min of the soa", id from categories where name="parent";
insert into parameters (name, description, category_id) select "ttlds", "ttl of the ds", id from categories where name="parent";

--insert into parameters (name, description, category_id) select "keycreate", "policy for key creation 0=fill the hsm, 1=only generate minimum needed", id from categories where name="enforcer";
insert into parameters (name, description, category_id) select "interval", "run interval", id from categories where name="enforcer";
insert into parameters (name, description, category_id) select "keygeninterval", "interval between key generation runs", id from categories where name="enforcer";
insert into parameters (name, description, category_id) select "backupdelay", "how old must a new key be before it can be assumed to have been backed up", id from categories where name="enforcer";

insert into parameters (name, description, category_id) select "zones_share_keys", "do all zones on this policy share the same keys", id from categories where name="keys";
insert into parameters (name, description, category_id) select "registrationdelay", "Dr", id from categories where name="parent";

insert into parameters (name, description, category_id) select "manual_rollover", "Do not automatically roll ksks when their time is up", id from categories where name="ksk";
insert into parameters (name, description, category_id) select "manual_rollover", "Do not automatically roll zsks when their time is up", id from categories where name="zsk";

insert into parameters (name, description, category_id) select "purge", "interval that dead keys can stay in the database", id from categories where name="keys";

--insert into parameters (name, description, category_id) select "audit", "placeholder for audit tag", id from categories where name="audit";

-- Indexes for foreign keys
CREATE INDEX idx1 on dnsseckeys ( zone_id );
CREATE INDEX idx2 on dnsseckeys ( keypair_id );
CREATE INDEX idx3 on keypairs ( securitymodule_id );
CREATE INDEX idx4 on keypairs ( policy_id );
CREATE INDEX idx5 on zones ( policy_id );
CREATE INDEX idx6 on parameters ( category_id );
CREATE INDEX idx7 on parameters_policies ( parameter_id );
CREATE INDEX idx8 on parameters_policies ( policy_id );