Skip to content

Instantly share code, notes, and snippets.

@jcjones
Last active February 22, 2016 23:29
Show Gist options
  • Save jcjones/f140919a4d2d41216bee to your computer and use it in GitHub Desktop.
Save jcjones/f140919a4d2d41216bee to your computer and use it in GitHub Desktop.
Useful views for the ct-sql database
CREATE VIEW `le_certificate` AS
SELECT
`certificate`.`certID` AS `certID`,
`certificate`.`serial` AS `serial`,
`certificate`.`issuerID` AS `issuerID`,
`certificate`.`subject` AS `subject`,
`certificate`.`notBefore` AS `notBefore`,
`certificate`.`notAfter` AS `notAfter`
FROM
`certificate`
WHERE
(`certificate`.`issuerID` = (SELECT
`issuer`.`issuerID`
FROM
`issuer`
WHERE
(`issuer`.`commonName` = 'Let\'s Encrypt Authority X1')));
CREATE VIEW `unexpired_certificate` AS
SELECT
`certificate`.`certID` AS `certID`,
`certificate`.`serial` AS `serial`,
`certificate`.`issuerID` AS `issuerID`,
`certificate`.`subject` AS `subject`,
`certificate`.`notBefore` AS `notBefore`,
`certificate`.`notAfter` AS `notAfter`
FROM
`certificate`
WHERE
(NOW() BETWEEN `certificate`.`notBefore` AND `certificate`.`notAfter`);
CREATE VIEW `le_current_certificate` AS
SELECT
`certificate`.`certID` AS `certID`,
`certificate`.`serial` AS `serial`,
`certificate`.`issuerID` AS `issuerID`,
`certificate`.`subject` AS `subject`,
`certificate`.`notBefore` AS `notBefore`,
`certificate`.`notAfter` AS `notAfter`
FROM
`certificate`
WHERE
((`certificate`.`issuerID` = (SELECT
`issuer`.`issuerID`
FROM
`issuer`
WHERE
(`issuer`.`commonName` = 'Let\'s Encrypt Authority X1')))
AND (NOW() BETWEEN `certificate`.`notBefore` AND `certificate`.`notAfter`));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment