Useful views for the ct-sql database

letsencrypt-ct-sql-views.sql

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`));