summaryrefslogtreecommitdiffstats
path: root/etc/schema/mysql-upgrade/v1.0.0.sql
blob: 28b3e7d0af5966e91786694258ef5aaadb4db182 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
ALTER TABLE x509_target MODIFY COLUMN `port` smallint unsigned NOT NULL;

ALTER TABLE x509_certificate_subject_alt_name DROP FOREIGN KEY x509_fk_certificate_subject_alt_name_certificate_id;

ALTER TABLE x509_certificate_subject_alt_name DROP PRIMARY KEY;

ALTER TABLE x509_certificate_subject_alt_name ADD COLUMN hash binary(32) NOT NULL
  COMMENT 'sha256 hash of type=value'
  AFTER certificate_id;

UPDATE x509_certificate_subject_alt_name SET hash = UNHEX(SHA2(CONCAT(type, '=', value), 256));

ALTER TABLE x509_certificate_subject_alt_name ADD PRIMARY KEY(certificate_id, hash);

ALTER TABLE x509_certificate_subject_alt_name ADD
  CONSTRAINT x509_fk_certificate_subject_alt_name_certificate_id
  FOREIGN KEY (certificate_id)
  REFERENCES x509_certificate (id)
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE x509_certificate_subject_alt_name ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=default;

ALTER TABLE x509_target DROP INDEX x509_idx_target_ip_port_hostname;

ALTER TABLE x509_target ADD INDEX x509_idx_target_ip_port_hostname(ip,port,hostname(191));

ALTER TABLE x509_target ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=default;