rpms / opendnssec

Clone
Source Code
GIT

Blame SOURCES/opendnssec-2.1.sqlite_convert.sql

c7 c7-beta c8-beta-stream-DL1 c8-beta-stream-client c8-stream-DL1 c8-stream-client c8s-stream-DL1 c8s-stream-client c9 c9-beta
  1.   db740667d38d421ae99ae3f36c2ae5b89d3a7aa0
  2.   SOURCES
  3.   opendnssec-2.1.sqlite_convert.sql
Blob History Raw
db7406 
INSERT INTO databaseVersion VALUES (NULL, 1, 1);
db7406
db7406 
-- ~ ************
db7406 
-- ~ ** policy table
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ ************
db7406
db7406 
INSERT INTO policy
db7406 
SELECT id, 1, name, description,
db7406 
0, 0, 0,
db7406 
0, 0, 0, 0,
db7406 
86400, 0, 0,
db7406 
0, 0, 0,
db7406 
0, 0, 0,
db7406 
0, 0, 0,
db7406 
0, 0, 0,
db7406 
0, 0, 0,
db7406 
0, 0, 0,
db7406 
0, 0, 0,
db7406 
0
db7406 
FROM REMOTE.policies;
db7406
db7406 
UPDATE policy
db7406 
SET signaturesResign = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 1
db7406 
		AND REMOTE.parameters.name = 'resign');
db7406
db7406 
UPDATE policy
db7406 
SET signaturesRefresh = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 1
db7406 
		AND REMOTE.parameters.name = 'refresh') ;
db7406
db7406 
UPDATE policy
db7406 
SET signaturesJitter = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 1
db7406 
		AND REMOTE.parameters.name = 'jitter');
db7406
db7406 
UPDATE policy
db7406 
SET signaturesInceptionOffset = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 1
db7406 
		AND REMOTE.parameters.name = 'clockskew');
db7406
db7406 
UPDATE policy
db7406 
SET signaturesValidityDefault = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 1
db7406 
		AND REMOTE.parameters.name = 'valdefault');
db7406
db7406 
UPDATE policy
db7406 
SET signaturesValidityDenial = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 1
db7406 
		AND REMOTE.parameters.name = 'valdenial');
db7406
db7406 
--MaxZoneTTL default 86400
db7406
db7406 
-- We need the following mapping 1.4 -> 2.0 for denialType
db7406 
-- 0 -> 1
db7406 
-- 3 -> 0
db7406
db7406 
UPDATE policy
db7406 
SET denialType = (
db7406 
	SELECT (~value)&1
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'version');
db7406
db7406 
-- I'm pretty sure this is not the correct way to do it. It is aweful but
db7406 
-- I can't figure it out how it would work for sqlite.
db7406 
UPDATE policy
db7406 
SET denialOptout = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'optout')
db7406 
WHERE null !=  (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'optout');
db7406
db7406 
UPDATE policy
db7406 
SET denialTtl = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'ttl')
db7406 
WHERE null != (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'ttl');
db7406
db7406 
UPDATE policy
db7406 
SET denialResalt = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'resalt')
db7406 
WHERE null != (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'resalt');
db7406
db7406 
UPDATE policy
db7406 
SET denialAlgorithm = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'algorithm')
db7406 
WHERE null != (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'algorithm');
db7406
db7406 
UPDATE policy
db7406 
SET denialIterations = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'iterations')
db7406 
WHERE null != (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'iterations');
db7406
db7406 
UPDATE policy
db7406 
SET denialSaltLength = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'saltlength')
db7406 
WHERE null != (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 2
db7406 
		AND REMOTE.parameters.name = 'saltlength');
db7406
db7406 
-- clumsy salt update. salt is optional in 1.4 but required in 2.0
db7406 
-- sqlite is limited in what it can do in an update. I hope there is a
db7406 
-- better way for this?
db7406
db7406 
UPDATE policy
db7406 
SET denialSalt = (
db7406 
	SELECT salt
db7406 
	FROM  REMOTE.policies
db7406 
	WHERE REMOTE.policies.id = policy.id)
db7406 
WHERE (
db7406 
	SELECT salt
db7406 
	FROM  REMOTE.policies
db7406 
	WHERE REMOTE.policies.id = policy.id) != null;
db7406
db7406 
UPDATE policy
db7406 
SET denialSaltLastChange = (
db7406 
	SELECT salt_stamp
db7406 
	FROM  REMOTE.policies
db7406 
	WHERE REMOTE.policies.id = policy.id)
db7406 
WHERE (
db7406 
	SELECT salt_stamp
db7406 
	FROM  REMOTE.policies
db7406 
	WHERE REMOTE.policies.id = policy.id) != null;
db7406
db7406 
UPDATE policy
db7406 
SET keysTtl = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 5
db7406 
		AND REMOTE.parameters.name = 'ttl');
db7406
db7406 
UPDATE policy
db7406 
SET keysRetireSafety = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 5
db7406 
		AND REMOTE.parameters.name = 'retiresafety');
db7406
db7406 
UPDATE policy
db7406 
SET keysPublishSafety = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 5
db7406 
		AND REMOTE.parameters.name = 'publishsafety');
db7406
db7406 
UPDATE policy
db7406 
SET keysShared = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 5
db7406 
		AND REMOTE.parameters.name = 'zones_share_keys');
db7406
db7406 
UPDATE policy
db7406 
SET keysPurgeAfter = COALESCE((
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 5
db7406 
		AND REMOTE.parameters.name = 'purge'), 0);
db7406
db7406 
UPDATE policy
db7406 
SET zonePropagationDelay = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 7
db7406 
		AND REMOTE.parameters.name = 'propagationdelay');
db7406
db7406 
UPDATE policy
db7406 
SET zoneSoaTtl = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 7
db7406 
		AND REMOTE.parameters.name = 'ttl');
db7406
db7406 
UPDATE policy
db7406 
SET zoneSoaMinimum = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 7
db7406 
		AND REMOTE.parameters.name = 'min');
db7406
db7406 
-- Temporary mapping table between 1.4 and 2.0 SOA serial strategy
db7406 
CREATE TABLE mapping (
db7406 
	soa14 INTEGER,
db7406 
	soa20 INTEGER
db7406 
);
db7406 
INSERT INTO mapping SELECT  1, 2;
db7406 
INSERT INTO mapping SELECT  2, 0;
db7406 
INSERT INTO mapping SELECT  3, 1;
db7406 
INSERT INTO mapping SELECT  4, 3;
db7406
db7406 
UPDATE policy
db7406 
SET zoneSoaSerial = (
db7406 
	SELECT mapping.soa20
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
        INNER JOIN mapping
db7406 
        ON REMOTE.parameters_policies.value = mapping.soa14
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 7
db7406 
		AND REMOTE.parameters.name = 'serial');
db7406
db7406 
DROP TABLE mapping;
db7406
db7406 
-- parentRegistrationDelay = 0 on 1.4
db7406
db7406 
UPDATE policy
db7406 
SET parentPropagationDelay = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 8
db7406 
		AND REMOTE.parameters.name = 'propagationdelay');
db7406
db7406 
UPDATE policy
db7406 
SET parentDsTtl = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 8
db7406 
		AND REMOTE.parameters.name = 'ttlds');
db7406
db7406 
UPDATE policy
db7406 
SET parentSoaTtl = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 8
db7406 
		AND REMOTE.parameters.name = 'ttl');
db7406
db7406 
UPDATE policy
db7406 
SET parentSoaMinimum = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policy.id
db7406 
		AND REMOTE.parameters.category_id = 8
db7406 
		AND REMOTE.parameters.name = 'min');
db7406
db7406 
-- passthrough = 0
db7406
db7406 
-- ~ ************
db7406 
-- ~ ** policyKey table
db7406 
-- ~ **
db7406 
-- ~ ** For each policy in 1.4 add two keys: KSK and ZSK
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ ************
db7406
db7406 
-- Insert each KSK
db7406 
INSERT INTO policyKey
db7406 
SELECT null, 1, id,
db7406 
		1, 0, 0,
db7406 
		0, 0, 0,
db7406 
		0, 0, 4
db7406 
FROM REMOTE.policies;
db7406
db7406 
-- Insert each ZSK
db7406 
INSERT INTO policyKey
db7406 
SELECT null, 1, id,
db7406 
		2, 0, 0,
db7406 
		0, 0, 0,
db7406 
		0, 0, 1
db7406 
FROM REMOTE.policies;
db7406
db7406 
UPDATE policyKey
db7406 
SET algorithm = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 3
db7406 
		AND REMOTE.parameters.name = 'algorithm')
db7406 
WHERE policyKey.role = 1;
db7406
db7406 
UPDATE policyKey
db7406 
SET algorithm = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 4
db7406 
		AND REMOTE.parameters.name = 'algorithm')
db7406 
WHERE policyKey.role = 2;
db7406
db7406 
UPDATE policyKey
db7406 
SET bits = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 3
db7406 
		AND REMOTE.parameters.name = 'bits')
db7406 
WHERE policyKey.role = 1;
db7406
db7406 
UPDATE policyKey
db7406 
SET bits = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 4
db7406 
		AND REMOTE.parameters.name = 'bits')
db7406 
WHERE policyKey.role = 2;
db7406
db7406 
UPDATE policyKey
db7406 
SET lifetime = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 3
db7406 
		AND REMOTE.parameters.name = 'lifetime')
db7406 
WHERE policyKey.role = 1;
db7406
db7406 
UPDATE policyKey
db7406 
SET lifetime = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 4
db7406 
		AND REMOTE.parameters.name = 'lifetime')
db7406 
WHERE policyKey.role = 2;
db7406
db7406 
UPDATE policyKey
db7406 
SET repository = (
db7406 
	SELECT REMOTE.securitymodules.name
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	INNER JOIN REMOTE.securitymodules
db7406 
	ON REMOTE.parameters_policies.value = REMOTE.securitymodules.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 3
db7406 
		AND REMOTE.parameters.name = 'repository')
db7406 
WHERE policyKey.role = 1;
db7406
db7406 
UPDATE policyKey
db7406 
SET repository = (
db7406 
	SELECT REMOTE.securitymodules.name
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	INNER JOIN REMOTE.securitymodules
db7406 
	ON REMOTE.parameters_policies.value = REMOTE.securitymodules.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 4
db7406 
		AND REMOTE.parameters.name = 'repository')
db7406 
WHERE policyKey.role = 2;
db7406
db7406 
UPDATE policyKey
db7406 
SET standby = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 3
db7406 
		AND REMOTE.parameters.name = 'standby')
db7406 
WHERE policyKey.role = 1;
db7406
db7406 
UPDATE policyKey
db7406 
SET standby = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 4
db7406 
		AND REMOTE.parameters.name = 'standby')
db7406 
WHERE policyKey.role = 2;
db7406
db7406 
UPDATE policyKey
db7406 
SET manualRollover = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 3
db7406 
		AND REMOTE.parameters.name = 'manual_rollover')
db7406 
WHERE policyKey.role = 1;
db7406
db7406 
UPDATE policyKey
db7406 
SET manualRollover = (
db7406 
	SELECT value
db7406 
	FROM  REMOTE.parameters_policies
db7406 
	INNER JOIN REMOTE.parameters
db7406 
	ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
db7406 
	WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
db7406 
		AND REMOTE.parameters.category_id = 4
db7406 
		AND REMOTE.parameters.name = 'manual_rollover')
db7406 
WHERE policyKey.role = 2;
db7406
db7406 
-- rfc5011 = 0. 2.0 has no support
db7406 
-- minimize already set
db7406
db7406 
-- ~ ************
db7406 
-- ~ ** hsmKey table
db7406 
-- ~ **
db7406 
-- ~ ** get from keypairs and dnsseckeys
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ ************
db7406
db7406 
INSERT INTO hsmKey
db7406 
SELECT DISTINCT REMOTE.keypairs.id, 1, REMOTE.keypairs.policy_id,
db7406 
REMOTE.keypairs.HSMkey_id, 2, REMOTE.keypairs.size,
db7406 
REMOTE.keypairs.algorithm,  (~(REMOTE.dnsseckeys.keytype)&1)+1,
db7406 
CASE WHEN REMOTE.keypairs.generate IS NOT NULL THEN
db7406 
	strftime('%s', REMOTE.keypairs.generate)
db7406 
	ELSE strftime("%s", "now") END,
db7406 
0,
db7406 
1, --only RSA supported
db7406 
 REMOTE.securitymodules.name,
db7406 
0 --assume no backup
db7406 
FROM REMOTE.keypairs
db7406 
JOIN REMOTE.dnsseckeys
db7406 
	ON REMOTE.keypairs.id = REMOTE.dnsseckeys.keypair_id
db7406 
JOIN REMOTE.securitymodules
db7406 
	ON REMOTE.securitymodules.id = REMOTE.keypairs.securitymodule_id;
db7406
db7406 
-- For some policies put the keys in a shared state
db7406 
UPDATE hsmKey
db7406 
SET state = 3
db7406 
WHERE EXISTS
db7406 
	(SELECT * FROM hsmKey AS h
db7406 
	JOIN policy ON policy.id = h.policyId
db7406 
	WHERE policy.keysShared AND hsmKey.id = h.id);
db7406
db7406 
-- ~ ************
db7406 
-- ~ ** zone table
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ ************
db7406
db7406 
INSERT INTO zone
db7406 
SELECT zones.id, 1, zones.policy_id,
db7406 
	zones.name, 1, zones.signconf, 0,
db7406 
	0,0,0,
db7406 
	0,0,0,
db7406 
	zones.in_type, zones.input,
db7406 
	zones.out_type, zones.output,
db7406 
	0,0,0
db7406 
	FROM REMOTE.zones;
db7406
db7406 
-- ~ ************
db7406 
-- ~ ** keyData table
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ ************
db7406
db7406 
-- Temporary mapping table between 1.4 states and 2.0 ds_at_parent states
db7406 
-- We are ignoring the fact this may set a DS state for a ZSK; We don't care
db7406 
CREATE TABLE mapping (
db7406 
	state INTEGER,
db7406 
	ds_state INTEGER
db7406 
);
db7406 
INSERT INTO mapping SELECT  1, 0;
db7406 
INSERT INTO mapping SELECT  2, 0;
db7406 
INSERT INTO mapping SELECT  3, 1;
db7406 
INSERT INTO mapping SELECT  4, 3;
db7406 
INSERT INTO mapping SELECT  5, 5;
db7406 
INSERT INTO mapping SELECT  6, 5;
db7406 
INSERT INTO mapping SELECT  7, 5;
db7406 
INSERT INTO mapping SELECT  8, 5;
db7406 
INSERT INTO mapping SELECT  9, 5;
db7406 
INSERT INTO mapping SELECT 10, 5;
db7406
db7406 
INSERT INTO keyData
db7406 
SELECT
db7406 
	NULL, 1, REMOTE.dnsseckeys.zone_id,
db7406 
	REMOTE.dnsseckeys.keypair_id, REMOTE.keypairs.algorithm,
db7406 
	CASE WHEN REMOTE.dnsseckeys.publish IS NOT NULL THEN
db7406 
		strftime('%s', REMOTE.dnsseckeys.publish)
db7406 
		ELSE strftime("%s", "now") END,
db7406 
	(~REMOTE.dnsseckeys.keytype&1)+1,
db7406 
	REMOTE.dnsseckeys.state <= 4, -- introducing
db7406 
	0, -- should revoke, not used
db7406 
	0, -- standby
db7406 
	REMOTE.dnsseckeys.state  = 4 AND REMOTE.dnsseckeys.keytype = 256, --activeZSK:
db7406 
	REMOTE.dnsseckeys.state >= 2 AND REMOTE.dnsseckeys.state <= 5, --publish
db7406 
	REMOTE.dnsseckeys.state  = 4 AND REMOTE.dnsseckeys.keytype = 257, --activeKSK:
db7406 
	mapping.ds_state, --dsatparent
db7406 
	1<<16, --keytag (crap, will 2.0 regenerate this?)
db7406 
	(REMOTE.dnsseckeys.keytype&1)*3+1 --minimize
db7406 
FROM REMOTE.dnsseckeys
db7406 
JOIN REMOTE.keypairs
db7406 
	ON REMOTE.dnsseckeys.keypair_id = REMOTE.keypairs.id
db7406 
JOIN mapping
db7406 
	ON REMOTE.dnsseckeys.state = mapping.state
db7406 
WHERE EXISTS(select REMOTE.zones.id FROM REMOTE.zones WHERE REMOTE.zones.id = REMOTE.dnsseckeys.zone_id);
db7406
db7406 
-- Everything that is just a ZSK must not have dsatparent set.
db7406 
UPDATE keyData
db7406 
SET dsatparent = 0
db7406 
WHERE role = 2;
db7406
db7406 
DROP TABLE mapping;
db7406
db7406 
-- If a active time is set for a ready KSK dsAtParent is submitted
db7406 
-- instead of submit
db7406 
UPDATE keyData
db7406 
SET dsatparent = 2
db7406 
WHERE keyData.dsAtParent = 1 AND keyData.id IN (
db7406 
	SELECT keyData.id
db7406 
	FROM keyData
db7406 
	JOIN REMOTE.dnsseckeys
db7406 
	ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
db7406 
	WHERE REMOTE.dnsseckeys.active IS NOT NULL);
db7406
db7406
db7406 
-- ~ ************
db7406 
-- ~ ** Keystate table
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ **
db7406 
-- ~ ************
db7406
db7406 
CREATE TABLE mapping (
db7406 
	state INTEGER,
db7406 
	ds INTEGER,
db7406 
	dk INTEGER,
db7406 
	ks INTEGER,
db7406 
	rs INTEGER
db7406 
);
db7406 
INSERT INTO mapping SELECT  1, 0, 0, 0, 0;
db7406 
INSERT INTO mapping SELECT  2, 0, 1, 1, 1;
db7406 
INSERT INTO mapping SELECT  3, 0, 2, 2, 1;
db7406 
INSERT INTO mapping SELECT  4, 2, 2, 2, 1;
db7406 
INSERT INTO mapping SELECT  5, 3, 2, 2, 3;
db7406 
INSERT INTO mapping SELECT  6, 0, 3, 3, 0;
db7406 
INSERT INTO mapping SELECT  7, 3, 0, 0, 0;
db7406 
INSERT INTO mapping SELECT  8, 3, 0, 0, 0;
db7406 
INSERT INTO mapping SELECT  9, 3, 0, 0, 0;
db7406 
INSERT INTO mapping SELECT 10, 3, 0, 0, 0;
db7406
db7406 
-- DS RECORDS
db7406 
INSERT INTO keyState
db7406 
SELECT NULL, 1, keyData.id, 0, mapping.ds, strftime("%s", "now"), (keyData.minimize>>2)&1, policy.parentDsTtl
db7406 
FROM keyData
db7406 
JOIN zone
db7406 
	ON zone.id = keyData.zoneId
db7406 
JOIN policy
db7406 
	ON policy.id = zone.policyId
db7406 
JOIN REMOTE.dnsseckeys
db7406 
	ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
db7406 
JOIN mapping
db7406 
	ON mapping.state = REMOTE.dnsseckeys.state;
db7406
db7406 
UPDATE keyState
db7406 
SET state = 1
db7406 
WHERE keyState.state = 0 AND keyState.type = 0 AND keyState.id IN (
db7406 
	SELECT keyState.id
db7406 
	FROM keyState
db7406 
	JOIN keyData
db7406 
		ON keyData.id = keyState.keydataId
db7406 
	JOIN REMOTE.dnsseckeys
db7406 
		ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
db7406 
	WHERE REMOTE.dnsseckeys.active IS NOT NULL);
db7406
db7406 
-- DNSKEY RECORDS
db7406 
INSERT INTO keyState
db7406 
SELECT NULL, 1, keyData.id, 2, mapping.dk, strftime("%s", "now"), (keyData.minimize>>1)&1, policy.keysTtl
db7406 
FROM keyData
db7406 
JOIN zone
db7406 
	ON zone.id = keyData.zoneId
db7406 
JOIN policy
db7406 
	ON policy.id = zone.policyId
db7406 
JOIN REMOTE.dnsseckeys
db7406 
	ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
db7406 
JOIN mapping
db7406 
	ON mapping.state = REMOTE.dnsseckeys.state;
db7406
db7406 
-- RRSIG DNSKEY RECORDS
db7406 
INSERT INTO keyState
db7406 
SELECT NULL, 1, keyData.id, 3, mapping.ks, strftime("%s", "now"), (keyData.minimize>>1)&1, policy.keysTtl
db7406 
FROM keyData
db7406 
JOIN zone
db7406 
	ON zone.id = keyData.zoneId
db7406 
JOIN policy
db7406 
	ON policy.id = zone.policyId
db7406 
JOIN REMOTE.dnsseckeys
db7406 
	ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
db7406 
JOIN mapping
db7406 
	ON mapping.state = REMOTE.dnsseckeys.state;
db7406
db7406 
-- RRSIG RECORDS
db7406 
INSERT INTO keyState
db7406 
SELECT NULL, 1, keyData.id, 1, mapping.rs, strftime("%s", "now"), (keyData.minimize>>0)&1, policy.signaturesMaxZoneTtl
db7406 
FROM keyData
db7406 
JOIN zone
db7406 
	ON zone.id = keyData.zoneId
db7406 
JOIN policy
db7406 
	ON policy.id = zone.policyId
db7406 
JOIN REMOTE.dnsseckeys
db7406 
	ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
db7406 
JOIN mapping
db7406 
	ON mapping.state = REMOTE.dnsseckeys.state;
db7406
db7406 
--Set to OMN if Tactive + Dttl < Tnow
db7406 
UPDATE keyState
db7406 
SET state = 2
db7406 
WHERE keyState.state = 1 AND keyState.type = 1 AND keyState.id IN (
db7406 
        SELECT keyState.id
db7406 
        FROM keyState
db7406 
        JOIN keyData
db7406 
                ON keyData.id = keyState.keydataId
db7406 
        JOIN REMOTE.dnsseckeys
db7406 
                ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
db7406 
        JOIN zone
db7406 
                ON keyData.zoneId = zone.id
db7406 
        JOIN policy
db7406 
                ON policy.id = zone.policyId
db7406 
        WHERE CAST(strftime("%s", REMOTE.dnsseckeys.active) + policy.signaturesValidityDefault as INTEGER) < strftime("%s", "now"));
db7406
db7406 
--Force the RRSIG state in omnipresent if rumoured and there is no old ZSK
db7406 
-- unretentive
db7406 
UPDATE keyState
db7406 
SET state = 2
db7406 
WHERE keyState.id IN (
db7406 
SELECT rs.id FROM keyState AS rs
db7406 
JOIN keystate AS dk ON dk.keyDataId == rs.keyDataId
db7406 
WHERE rs.type == 1 AND dk.type == 2 AND rs.state == 1 AND dk.state == 2
db7406 
AND NOT EXISTS(
db7406 
	SELECT* FROM keystate AS rs2
db7406 
	JOIN keystate AS dk2 ON dk2.keyDataId == rs2.keyDataId
db7406 
	WHERE rs2.type == 1 AND dk2.type == 2 AND rs2.state == 3 AND dk2.state == 2
db7406 
));
db7406
db7406 
DROP TABLE mapping;
db7406
db7406 
-- We need to create records in the keydependency table in case we are in a
db7406 
-- rollover. Only done for ZSK. For every introducing ZSK with RRSIG rumoured
db7406 
-- that has an outroducing ZSK with RRSIG unretentive, we add a record.
db7406 
INSERT INTO keyDependency
db7406 
SELECT NULL, 0, keyData.zoneID, SUB.IDout, keyData.id, 1
db7406 
FROM keyData
db7406 
JOIN keyState AS KS1
db7406 
	ON KS1.keyDataId == keyData.id
db7406 
JOIN keyState AS KS2
db7406 
	ON KS2.keyDataId == keyData.id
db7406 
JOIN (
db7406 
	SELECT keyData.id AS IDout, keyData.zoneID
db7406 
	FROM keyData
db7406 
	JOIN keyState AS KS1
db7406 
		ON KS1.keyDataId == keyData.id
db7406 
	JOIN keyState AS KS2
db7406 
		ON KS2.keyDataId == keyData.id
db7406 
	WHERE KS1.type == 2
db7406 
		AND ks1.state = 2
db7406 
		AND KS2.type == 1
db7406 
		AND KS2.state == 3
db7406 
		AND keyData.introducing == 0
db7406 
		AND keyData.role == 2
db7406 
) AS SUB
db7406 
	ON SUB.zoneId == keyData.zoneId
db7406 
WHERE
db7406 
	KS1.type == 2
db7406 
	AND ks1.state = 2
db7406 
	AND KS2.type == 1
db7406 
	AND KS2.state == 1
db7406 
	AND keyData.introducing == 1
db7406 
	AND keyData.role == 2;
db7406
db7406 
-- ZSK
db7406 
UPDATE keyState
db7406 
SET state = 4
db7406 
WHERE (keyState.type = 0 OR keyState.type = 3) AND keyDataId IN (
db7406 
	SELECT keyData.id
db7406 
	FROM keyData
db7406 
	WHERE keyData.role = 2);
db7406
db7406 
--KSK
db7406 
UPDATE keyState
db7406 
SET state = 4
db7406 
WHERE keyState.type = 1 AND keyDataId IN (
db7406 
	SELECT keyData.id
db7406 
	FROM keyData
db7406 
	WHERE keyData.role = 1);
db7406
db7406 
-- For rpm based systems to see if db was migrated already. store opendnssec major minor version
db7406 
CREATE TABLE rpm_migration (
db7406 
        major INTEGER,
db7406 
        minor INTEGER
db7406 
);
db7406 
INSERT INTO rpm_migration VALUES(2, 1);
db7406

Powered by Pagure 5.13.3

SSH Hostkey/Fingerprint | Documentation