Skip to content

Instantly share code, notes, and snippets.

@jcjones
Created April 5, 2016 22:21
Show Gist options
  • Save jcjones/ffa526bcb8bc6b1e69954794bafb0f76 to your computer and use it in GitHub Desktop.
Save jcjones/ffa526bcb8bc6b1e69954794bafb0f76 to your computer and use it in GitHub Desktop.
DROP TABLE IF EXISTS `company`;
DROP TABLE IF EXISTS `issuercompany`;
CREATE TABLE IF NOT EXISTS `company` (
`companyID` INT NOT NULL AUTO_INCREMENT,
`companyName` VARCHAR(255) NOT NULL,
PRIMARY KEY (`companyID`));
CREATE TABLE IF NOT EXISTS `issuercompany` (
`companyID` INT NOT NULL,
`issuerID` INT NOT NULL,
UNIQUE INDEX `issuerID_UNIQUE` (`issuerID` ASC));
INSERT INTO `company` (`companyName`) VALUES ('Let\'s Encrypt');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Let\'s Encrypt' WHERE `commonName` LIKE '%Let\'s Encrypt%';
INSERT INTO `company` (`companyName`) VALUES ('Comodo');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%COMODO%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%PositiveSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%GlobalTrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%GlobeSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%TrustAsia%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%TBS X509%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%InterContinental Hotels Group%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%USERTrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%SSL.com%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%McAfee%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%SecureCore%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%USERFirst%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%AAA Certificate%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%icewarp%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Crazy Domains%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%DOMENY%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%K Software%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%ReliaSite%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%BlackCert%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Western Digital%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%SignSec%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%InCommon%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Gandi%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Digi-Sign%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Terena%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%TrustSign%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%EssentialSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Trusted Secure Certificate Authority%';
INSERT INTO `company` (`companyName`) VALUES ('DigiCert');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'DigiCert' WHERE `commonName` LIKE '%DigiCert%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'DigiCert' WHERE `commonName` LIKE 'Cybertrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'DigiCert' WHERE `commonName` LIKE '%GTE Cybertrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'DigiCert' WHERE `commonName` LIKE '%Baltimore Cybertrust%';
INSERT INTO `company` (`companyName`) VALUES ('Entrust');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Entrust' WHERE `commonName` LIKE '%Entrust%';
-- http://www.businesswire.com/news/home/20160322005639/en/Entrust-Datacard-Trend-Micro-Partner-Provide-Comprehensive
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Entrust' WHERE `commonName` LIKE '%Trend Micro%';
INSERT INTO `company` (`companyName`) VALUES ('Symantec');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%Symantec%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%GeoTrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%AlphaSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%RapidSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%VeriSign%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%Thawte%';
INSERT INTO `company` (`companyName`) VALUES ('GlobalSign');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'GlobalSign' WHERE `commonName` LIKE '%GlobalSign%';
INSERT INTO `company` (`companyName`) VALUES ('GoDaddy');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'GoDaddy' WHERE `commonName` LIKE '%GoDaddy%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'GoDaddy' WHERE `commonName` LIKE '%Go Daddy%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'GoDaddy' WHERE `commonName` LIKE '%Starfield%';
INSERT INTO `company` (`companyName`) VALUES ('StartSSL');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'StartSSL' WHERE `commonName` LIKE '%StartCom%';
INSERT INTO `company` (`companyName`) VALUES ('WoSign');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'WoSign' WHERE `commonName` LIKE '%WoSign%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'WoSign' WHERE `commonName` LIKE '%沃通免费%';
INSERT INTO `company` (`companyName`) VALUES ('Network Solutions');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Network Solutions' WHERE `commonName` LIKE '%Network Solutions%';
INSERT INTO `company` (`companyName`) VALUES ('QuoVadis');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'QuoVadis' WHERE `commonName` LIKE '%QuoVadis%';
INSERT INTO `company` (`companyName`) VALUES ('Microsoft');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Microsoft' WHERE `commonName` LIKE '%Microsoft%';
INSERT INTO `company` (`companyName`) VALUES ('Google');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Google' WHERE `commonName` LIKE '%Google%';
INSERT INTO `company` (`companyName`) VALUES ('TrustWave');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'TrustWave' WHERE `commonName` LIKE '%TrustWave%';
INSERT INTO `company` (`companyName`) VALUES ('Verizon');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Verizon' WHERE `commonName` LIKE '%Verizon%';
INSERT INTO `company` (`companyName`) VALUES ('Amazon');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Amazon' WHERE `commonName` LIKE '%Amazon%';
INSERT INTO `company` (`companyName`) VALUES ('US DOD');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'US DOD' WHERE `commonName` LIKE '%DOD%';
INSERT INTO `company` (`companyName`) VALUES ('SECOM');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'SECOM' WHERE `commonName` LIKE '%SECOM%';
INSERT INTO `company` (`companyName`) VALUES ('TWCA');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'TWCA' WHERE `commonName` LIKE '%TWCA%';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment