INSERT INTO databaseVersion VALUES (NULL, 1, 1);

-- ~ ************

-- ~ ** policy table

-- ~ **

-- ~ **

-- ~ **

-- ~ **

-- ~ ************

INSERT INTO policy 

SELECT id, 1, name, description,

0, 0, 0,

0, 0, 0, 0,

86400, 0, 0,

0, 0, 0,

0, 0, 0,

0, 0, 0,

0, 0, 0,

0, 0, 0,

0, 0, 0,

0, 0, 0,

0

FROM REMOTE.policies;

UPDATE policy

SET signaturesResign = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 1

		AND REMOTE.parameters.name = 'resign');

UPDATE policy

SET signaturesRefresh = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 1

		AND REMOTE.parameters.name = 'refresh') ;

UPDATE policy

SET signaturesJitter = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 1

		AND REMOTE.parameters.name = 'jitter');

UPDATE policy

SET signaturesInceptionOffset = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 1

		AND REMOTE.parameters.name = 'clockskew');

UPDATE policy

SET signaturesValidityDefault = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 1

		AND REMOTE.parameters.name = 'valdefault');

UPDATE policy

SET signaturesValidityDenial = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 1

		AND REMOTE.parameters.name = 'valdenial');

--MaxZoneTTL default 86400

-- We need the following mapping 1.4 -> 2.0 for denialType

-- 0 -> 1

-- 3 -> 0

UPDATE policy

SET denialType = (

	SELECT (~value)&1

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'version');

-- I'm pretty sure this is not the correct way to do it. It is aweful but

-- I can't figure it out how it would work for sqlite.

UPDATE policy

SET denialOptout = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'optout')

WHERE null !=  (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'optout');

UPDATE policy

SET denialTtl = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'ttl')

WHERE null != (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'ttl');

UPDATE policy

SET denialResalt = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'resalt')

WHERE null != (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'resalt');

UPDATE policy

SET denialAlgorithm = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'algorithm')

WHERE null != (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'algorithm');

UPDATE policy

SET denialIterations = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'iterations')

WHERE null != (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'iterations');

UPDATE policy

SET denialSaltLength = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'saltlength')

WHERE null != (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 2

		AND REMOTE.parameters.name = 'saltlength');

-- clumsy salt update. salt is optional in 1.4 but required in 2.0

-- sqlite is limited in what it can do in an update. I hope there is a

-- better way for this?

UPDATE policy

SET denialSalt = (

	SELECT salt

	FROM  REMOTE.policies

	WHERE REMOTE.policies.id = policy.id)

WHERE (

	SELECT salt

	FROM  REMOTE.policies

	WHERE REMOTE.policies.id = policy.id) != null;

UPDATE policy

SET denialSaltLastChange = (

	SELECT salt_stamp

	FROM  REMOTE.policies

	WHERE REMOTE.policies.id = policy.id)

WHERE (

	SELECT salt_stamp

	FROM  REMOTE.policies

	WHERE REMOTE.policies.id = policy.id) != null;

UPDATE policy

SET keysTtl = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 5

		AND REMOTE.parameters.name = 'ttl');

UPDATE policy

SET keysRetireSafety = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 5

		AND REMOTE.parameters.name = 'retiresafety');

UPDATE policy

SET keysPublishSafety = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 5

		AND REMOTE.parameters.name = 'publishsafety');

UPDATE policy

SET keysShared = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 5

		AND REMOTE.parameters.name = 'zones_share_keys');

UPDATE policy

SET keysPurgeAfter = COALESCE((

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 5

		AND REMOTE.parameters.name = 'purge'), 0);

UPDATE policy

SET zonePropagationDelay = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 7

		AND REMOTE.parameters.name = 'propagationdelay');

UPDATE policy

SET zoneSoaTtl = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 7

		AND REMOTE.parameters.name = 'ttl');

UPDATE policy

SET zoneSoaMinimum = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 7

		AND REMOTE.parameters.name = 'min');

-- Temporary mapping table between 1.4 and 2.0 SOA serial strategy

CREATE TABLE mapping (

	soa14 INTEGER,

	soa20 INTEGER

);

INSERT INTO mapping SELECT  1, 2;

INSERT INTO mapping SELECT  2, 0;

INSERT INTO mapping SELECT  3, 1;

INSERT INTO mapping SELECT  4, 3;

UPDATE policy

SET zoneSoaSerial = (

	SELECT mapping.soa20

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

        INNER JOIN mapping

        ON REMOTE.parameters_policies.value = mapping.soa14

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 7

		AND REMOTE.parameters.name = 'serial');

DROP TABLE mapping;

-- parentRegistrationDelay = 0 on 1.4

UPDATE policy

SET parentPropagationDelay = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 8

		AND REMOTE.parameters.name = 'propagationdelay');

UPDATE policy

SET parentDsTtl = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 8

		AND REMOTE.parameters.name = 'ttlds');

UPDATE policy

SET parentSoaTtl = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 8

		AND REMOTE.parameters.name = 'ttl');

UPDATE policy

SET parentSoaMinimum = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id 

	WHERE REMOTE.parameters_policies.policy_id = policy.id 

		AND REMOTE.parameters.category_id = 8

		AND REMOTE.parameters.name = 'min');

-- passthrough = 0

-- ~ ************

-- ~ ** policyKey table

-- ~ **

-- ~ ** For each policy in 1.4 add two keys: KSK and ZSK

-- ~ **

-- ~ **

-- ~ ************

-- Insert each KSK

INSERT INTO policyKey

SELECT null, 1, id,

		1, 0, 0,

		0, 0, 0,

		0, 0, 4

FROM REMOTE.policies;

-- Insert each ZSK

INSERT INTO policyKey

SELECT null, 1, id,

		2, 0, 0,

		0, 0, 0,

		0, 0, 1

FROM REMOTE.policies;

UPDATE policyKey

SET algorithm = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 3

		AND REMOTE.parameters.name = 'algorithm')

WHERE policyKey.role = 1;

UPDATE policyKey

SET algorithm = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 4

		AND REMOTE.parameters.name = 'algorithm')

WHERE policyKey.role = 2;

UPDATE policyKey

SET bits = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 3

		AND REMOTE.parameters.name = 'bits')

WHERE policyKey.role = 1;

UPDATE policyKey

SET bits = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 4

		AND REMOTE.parameters.name = 'bits')

WHERE policyKey.role = 2;

UPDATE policyKey

SET lifetime = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 3

		AND REMOTE.parameters.name = 'lifetime')

WHERE policyKey.role = 1;

UPDATE policyKey

SET lifetime = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 4

		AND REMOTE.parameters.name = 'lifetime')

WHERE policyKey.role = 2;

UPDATE policyKey

SET repository = (

	SELECT REMOTE.securitymodules.name

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	INNER JOIN REMOTE.securitymodules

	ON REMOTE.parameters_policies.value = REMOTE.securitymodules.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 3

		AND REMOTE.parameters.name = 'repository')

WHERE policyKey.role = 1;

UPDATE policyKey

SET repository = (

	SELECT REMOTE.securitymodules.name

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	INNER JOIN REMOTE.securitymodules

	ON REMOTE.parameters_policies.value = REMOTE.securitymodules.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 4

		AND REMOTE.parameters.name = 'repository')

WHERE policyKey.role = 2;

UPDATE policyKey

SET standby = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 3

		AND REMOTE.parameters.name = 'standby')

WHERE policyKey.role = 1;

UPDATE policyKey

SET standby = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 4

		AND REMOTE.parameters.name = 'standby')

WHERE policyKey.role = 2;

UPDATE policyKey

SET manualRollover = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 3

		AND REMOTE.parameters.name = 'manual_rollover')

WHERE policyKey.role = 1;

UPDATE policyKey

SET manualRollover = (

	SELECT value

	FROM  REMOTE.parameters_policies

	INNER JOIN REMOTE.parameters

	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id

	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId

		AND REMOTE.parameters.category_id = 4

		AND REMOTE.parameters.name = 'manual_rollover')

WHERE policyKey.role = 2;

-- rfc5011 = 0. 2.0 has no support

-- minimize already set

-- ~ ************

-- ~ ** hsmKey table

-- ~ **

-- ~ ** get from keypairs and dnsseckeys

-- ~ **

-- ~ **

-- ~ ************

INSERT INTO hsmKey

SELECT DISTINCT REMOTE.keypairs.id, 1, REMOTE.keypairs.policy_id,

REMOTE.keypairs.HSMkey_id, 2, REMOTE.keypairs.size, 

REMOTE.keypairs.algorithm,  (~(REMOTE.dnsseckeys.keytype)&1)+1, 

CASE WHEN REMOTE.keypairs.generate IS NOT NULL THEN 

	strftime('%s', REMOTE.keypairs.generate) 

	ELSE strftime("%s", "now") END, 

0, 

1, --only RSA supported

 REMOTE.securitymodules.name, 

0 --assume no backup 

FROM REMOTE.keypairs

JOIN REMOTE.dnsseckeys 

	ON REMOTE.keypairs.id = REMOTE.dnsseckeys.keypair_id

JOIN REMOTE.securitymodules 

	ON REMOTE.securitymodules.id = REMOTE.keypairs.securitymodule_id;

-- For some policies put the keys in a shared state

UPDATE hsmKey 

SET state = 3

WHERE EXISTS 

	(SELECT * FROM hsmKey AS h 

	JOIN policy ON policy.id = h.policyId 

	WHERE policy.keysShared AND hsmKey.id = h.id);

-- ~ ************

-- ~ ** zone table

-- ~ **

-- ~ ** 

-- ~ **

-- ~ **

-- ~ ************

INSERT INTO zone

SELECT zones.id, 1, zones.policy_id, 

	zones.name, 1, zones.signconf, 0,  

	0,0,0, 

	0,0,0,

	zones.in_type, zones.input,

	zones.out_type, zones.output,

	0,0,0

	FROM REMOTE.zones;

-- ~ ************

-- ~ ** keyData table

-- ~ **

-- ~ ** 

-- ~ **

-- ~ **

-- ~ ************

-- Temporary mapping table between 1.4 states and 2.0 ds_at_parent states

-- We are ignoring the fact this may set a DS state for a ZSK; We don't care

CREATE TABLE mapping (

	state INTEGER,

	ds_state INTEGER

);

INSERT INTO mapping SELECT  1, 0;

INSERT INTO mapping SELECT  2, 0;

INSERT INTO mapping SELECT  3, 1;

INSERT INTO mapping SELECT  4, 3;

INSERT INTO mapping SELECT  5, 5;

INSERT INTO mapping SELECT  6, 5;

INSERT INTO mapping SELECT  7, 5;

INSERT INTO mapping SELECT  8, 5;

INSERT INTO mapping SELECT  9, 5;

INSERT INTO mapping SELECT 10, 5;

INSERT INTO keyData

SELECT 

	NULL, 1, REMOTE.dnsseckeys.zone_id,

	REMOTE.dnsseckeys.keypair_id, REMOTE.keypairs.algorithm,

	CASE WHEN REMOTE.dnsseckeys.publish IS NOT NULL THEN 

		strftime('%s', REMOTE.dnsseckeys.publish) 

		ELSE strftime("%s", "now") END, 

	(~REMOTE.dnsseckeys.keytype&1)+1,

	REMOTE.dnsseckeys.state <= 4, -- introducing

	0, -- should revoke, not used

	0, -- standby

	REMOTE.dnsseckeys.state  = 4 AND REMOTE.dnsseckeys.keytype = 256, --activeZSK: 

	REMOTE.dnsseckeys.state >= 2 AND REMOTE.dnsseckeys.state <= 5, --publish

	REMOTE.dnsseckeys.state  = 4 AND REMOTE.dnsseckeys.keytype = 257, --activeKSK: 

	mapping.ds_state, --dsatparent

	1<<16, --keytag (crap, will 2.0 regenerate this?)

	(REMOTE.dnsseckeys.keytype&1)*3+1 --minimize

FROM REMOTE.dnsseckeys

JOIN REMOTE.keypairs 

	ON REMOTE.dnsseckeys.keypair_id = REMOTE.keypairs.id

JOIN mapping 

	ON REMOTE.dnsseckeys.state = mapping.state

WHERE EXISTS(select REMOTE.zones.id FROM REMOTE.zones WHERE REMOTE.zones.id = REMOTE.dnsseckeys.zone_id);

-- Everything that is just a ZSK must not have dsatparent set.

UPDATE keyData

SET dsatparent = 0

WHERE role = 2;

DROP TABLE mapping;

-- If a active time is set for a ready KSK dsAtParent is submitted 

-- instead of submit

UPDATE keyData

SET dsatparent = 2

WHERE keyData.dsAtParent = 1 AND keyData.id IN (

	SELECT keyData.id

	FROM keyData

	JOIN REMOTE.dnsseckeys

	ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid

	WHERE REMOTE.dnsseckeys.active IS NOT NULL);

-- ~ ************

-- ~ ** Keystate table

-- ~ **

-- ~ ** 

-- ~ **

-- ~ **

-- ~ ************

CREATE TABLE mapping (

	state INTEGER,

	ds INTEGER,

	dk INTEGER,

	ks INTEGER,

	rs INTEGER

);

INSERT INTO mapping SELECT  1, 0, 0, 0, 0;

INSERT INTO mapping SELECT  2, 0, 1, 1, 1;

INSERT INTO mapping SELECT  3, 0, 2, 2, 1;

INSERT INTO mapping SELECT  4, 2, 2, 2, 1;

INSERT INTO mapping SELECT  5, 3, 2, 2, 3;

INSERT INTO mapping SELECT  6, 0, 3, 3, 0;

INSERT INTO mapping SELECT  7, 3, 0, 0, 0;

INSERT INTO mapping SELECT  8, 3, 0, 0, 0;

INSERT INTO mapping SELECT  9, 3, 0, 0, 0;

INSERT INTO mapping SELECT 10, 3, 0, 0, 0;

-- DS RECORDS

INSERT INTO keyState

SELECT NULL, 1, keyData.id, 0, mapping.ds, strftime("%s", "now"), (keyData.minimize>>2)&1, policy.parentDsTtl

FROM keyData

JOIN zone

	ON zone.id = keyData.zoneId

JOIN policy

	ON policy.id = zone.policyId

JOIN REMOTE.dnsseckeys

	ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid

JOIN mapping

	ON mapping.state = REMOTE.dnsseckeys.state;

UPDATE keyState

SET state = 1

WHERE keyState.state = 0 AND keyState.type = 0 AND keyState.id IN (

	SELECT keyState.id

	FROM keyState

	JOIN keyData

		ON keyData.id = keyState.keydataId

	JOIN REMOTE.dnsseckeys

		ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid

	WHERE REMOTE.dnsseckeys.active IS NOT NULL);

-- DNSKEY RECORDS

INSERT INTO keyState

SELECT NULL, 1, keyData.id, 2, mapping.dk, strftime("%s", "now"), (keyData.minimize>>1)&1, policy.keysTtl

FROM keyData