PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
-DROP TABLE IF EXISTS `emailcerts`;
-CREATE TABLE `emailcerts` (
+DROP TABLE IF EXISTS `certs`;
+CREATE TABLE `certs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`memid` int(11) NOT NULL DEFAULT '0',
`serial` varchar(50) NOT NULL DEFAULT '',
private Certificate(String serial) {
try {
- PreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id,subject, md, csr_name, crt_name,memid FROM `emailcerts` WHERE serial=?");
+ PreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id,subject, md, csr_name, crt_name,memid FROM `certs` WHERE serial=?");
ps.setString(1, serial);
ResultSet rs = ps.executeQuery();
if ( !rs.next()) {
if (id == 0) {
return CertificateStatus.DRAFT;
}
- PreparedStatement searcher = DatabaseConnection.getInstance().prepare("SELECT crt_name, created, revoked, serial FROM emailcerts WHERE id=?");
+ PreparedStatement searcher = DatabaseConnection.getInstance().prepare("SELECT crt_name, created, revoked, serial FROM certs WHERE id=?");
searcher.setInt(1, id);
ResultSet rs = searcher.executeQuery();
if ( !rs.next()) {
}
Notary.writeUserAgreement(ownerId, "CCA", "issue certificate", "", true, 0);
- PreparedStatement inserter = DatabaseConnection.getInstance().prepare("INSERT INTO emailcerts SET md=?, subject=?, csr_type=?, crt_name='', memid=?, profile=1");
+ PreparedStatement inserter = DatabaseConnection.getInstance().prepare("INSERT INTO certs SET md=?, subject=?, csr_type=?, crt_name='', memid=?, profile=1");
inserter.setString(1, md);
inserter.setString(2, dn);
inserter.setString(3, csrType.toString());
san.execute();
}
- PreparedStatement updater = DatabaseConnection.getInstance().prepare("UPDATE emailcerts SET csr_name=? WHERE id=?");
+ PreparedStatement updater = DatabaseConnection.getInstance().prepare("UPDATE certs SET csr_name=? WHERE id=?");
updater.setString(1, csrName);
updater.setInt(2, id);
updater.execute();
private void tryAuthWithCertificate(HttpServletRequest req, X509Certificate x509Certificate) {
String serial = x509Certificate.getSerialNumber().toString(16).toUpperCase();
try {
- PreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `emailcerts` WHERE `serial`=? AND `disablelogin`='0' AND `revoked` = " + "'0000-00-00 00:00:00'");
+ PreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `certs` WHERE `serial`=? AND `disablelogin`='0' AND `revoked` = " + "'0000-00-00 00:00:00'");
ps.setString(1, serial);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
HashMap<String, Object> vars = new HashMap<String, Object>();
User us = LoginPage.getUser(req);
try {
- PreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id`, `CN`, `serial`, `revoked`, `expire`, `disablelogin` FROM `emailcerts` WHERE `memid`=?");
+ PreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id`, `CN`, `serial`, `revoked`, `expire`, `disablelogin` FROM `certs` WHERE `memid`=?");
ps.setInt(1, us.getId());
ResultSet rs = ps.executeQuery();
vars.put("mailcerts", rs);
throw new IllegalStateException("already running");
}
running = true;
- readyCerts = DatabaseConnection.getInstance().prepare("SELECT emailcerts.id AS id, emailcerts.csr_name, emailcerts.subject, jobs.id AS jobid, csr_type, md, keyUsage, extendedKeyUsage FROM jobs " + //
- "INNER JOIN emailcerts ON emailcerts.id=jobs.targetId " + //
- "INNER JOIN profiles ON profiles.id=emailcerts.profile " + //
+ readyCerts = DatabaseConnection.getInstance().prepare("SELECT certs.id AS id, certs.csr_name, certs.subject, jobs.id AS jobid, csr_type, md, keyUsage, extendedKeyUsage FROM jobs " + //
+ "INNER JOIN certs ON certs.id=jobs.targetId " + //
+ "INNER JOIN profiles ON profiles.id=certs.profile " + //
"WHERE jobs.state='open' "//
+ "AND task='sign'");
getSANSs = DatabaseConnection.getInstance().prepare("SELECT contents, type FROM subjectAlternativeNames " + //
"WHERE certId=?");
- updateMail = DatabaseConnection.getInstance().prepare("UPDATE emailcerts SET crt_name=?," + " created=NOW(), serial=? WHERE id=?");
+ updateMail = DatabaseConnection.getInstance().prepare("UPDATE certs SET crt_name=?," + " created=NOW(), serial=? WHERE id=?");
warnMail = DatabaseConnection.getInstance().prepare("UPDATE jobs SET warning=warning+1, state=IF(warning<3, 'open','error') WHERE id=?");
- revoke = DatabaseConnection.getInstance().prepare("SELECT emailcerts.id, emailcerts.csr_name,jobs.id FROM jobs INNER JOIN emailcerts ON jobs.targetId=emailcerts.id" + " WHERE jobs.state='open' AND task='revoke'");
- revokeCompleted = DatabaseConnection.getInstance().prepare("UPDATE emailcerts SET revoked=NOW() WHERE id=?");
+ revoke = DatabaseConnection.getInstance().prepare("SELECT certs.id, certs.csr_name,jobs.id FROM jobs INNER JOIN certs ON jobs.targetId=certs.id" + " WHERE jobs.state='open' AND task='revoke'");
+ revokeCompleted = DatabaseConnection.getInstance().prepare("UPDATE certs SET revoked=NOW() WHERE id=?");
finishJob = DatabaseConnection.getInstance().prepare("UPDATE jobs SET state='done' WHERE id=?");