Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.