X-Git-Url: https://code.wpia.club/?a=blobdiff_plain;f=doc%2FtableStructure.sql;h=867a1c63704dc3bd70582d3a397c1a8f9fbc2373;hb=a1a980dd0cc65f33a6189eb81a164fe79abb647c;hp=2d78f9511521ec08ba3d3cca2cdea1e30fecf93a;hpb=123b7724f9b8ab944a7aa6ab2df9499f34b02a4b;p=gigi.git diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql index 2d78f951..867a1c63 100644 --- a/doc/tableStructure.sql +++ b/doc/tableStructure.sql @@ -40,6 +40,7 @@ CREATE TABLE IF NOT EXISTS `organisations` ( `state` varchar(2) NOT NULL, `province` varchar(100) NOT NULL, `city` varchar(100) NOT NULL, + `contactEmail` varchar(100) NOT NULL, `creator` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -50,8 +51,8 @@ CREATE TABLE `domains` ( `memid` int(11) NOT NULL, `domain` varchar(255) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `deleted` datetime DEFAULT NULL, + `modified` datetime NULL DEFAULT NULL, + `deleted` datetime NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `memid` (`memid`), KEY `domain` (`domain`), @@ -63,9 +64,9 @@ CREATE TABLE `emails` ( `id` int(11) NOT NULL AUTO_INCREMENT, `memid` int(11) NOT NULL DEFAULT '0', `email` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `modified` datetime NULL DEFAULT NULL, + `deleted` datetime NULL DEFAULT NULL, `hash` varchar(50) NOT NULL DEFAULT '', `attempts` int(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), @@ -91,7 +92,6 @@ CREATE TABLE `pingconfig` ( `domainid` int(11) NOT NULL, `type` enum('email', 'ssl', 'http', 'dns') NOT NULL, `info` varchar(255) NOT NULL, - `reping` enum('y','n') NOT NULL DEFAULT 'n', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -139,20 +139,19 @@ CREATE TABLE `certs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `memid` int(11) NOT NULL DEFAULT '0', `serial` varchar(50) NOT NULL DEFAULT '', - `CN` varchar(255) NOT NULL DEFAULT '', - `subject` varchar(1024) NOT NULL, `keytype` char(2) NOT NULL DEFAULT 'NS', `codesign` tinyint(1) NOT NULL DEFAULT '0', `md` enum('md5','sha1','sha256','sha512') NOT NULL DEFAULT 'sha512', `profile` int(3) NOT NULL, + `caid` int(3) NULL DEFAULT NULL, `csr_name` varchar(255) NOT NULL DEFAULT '', `csr_type` enum('CSR', 'SPKAC') NOT NULL, `crt_name` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `revoked` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `expire` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `created` timestamp NULL DEFAULT NULL, + `modified` datetime NULL DEFAULT NULL, + `revoked` datetime NULL DEFAULT NULL, + `expire` datetime NULL DEFAULT NULL, `renewed` tinyint(1) NOT NULL DEFAULT '0', `disablelogin` int(1) NOT NULL DEFAULT '0', `pkhash` char(40) DEFAULT NULL, @@ -168,6 +167,16 @@ CREATE TABLE `certs` ( KEY `emailcrt` (`crt_name`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; + +DROP TABLE IF EXISTS `certAvas`; +CREATE TABLE `certAvas` ( + `certid` int(11) NOT NULL, + `name` varchar(20) NOT NULL, + `value` varchar(255) NOT NULL, + + PRIMARY KEY (`certid`, `name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + DROP TABLE IF EXISTS `clientcerts`; CREATE TABLE `clientcerts` ( `id` int(11) NOT NULL, @@ -213,7 +222,16 @@ CREATE TABLE `subjectAlternativeNames` ( `type` enum('email','DNS') NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - +DROP TABLE IF EXISTS `cacerts`; +CREATE TABLE `cacerts` ( + `id` int(3) NOT NULL AUTO_INCREMENT, + `keyname` varchar(60) NOT NULL, + `subroot` int(2) NOT NULL, + `validFrom` datetime NULL DEFAULT NULL, + `validTo` datetime NULL DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE (`keyname`) +) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `jobs`; @@ -244,13 +262,13 @@ CREATE TABLE `notary` ( `location` varchar(255) NOT NULL DEFAULT '', `date` varchar(255) NOT NULL DEFAULT '', # date when assurance was entered - `when` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, #? - `expire` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `expire` datetime NULL DEFAULT NULL, #????????????????? `sponsor` int(11) NOT NULL DEFAULT '0', # date when assurance was deleted (or 0) - `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `deleted` datetime NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `from` (`from`), KEY `to` (`to`), @@ -294,7 +312,7 @@ DROP TABLE IF EXISTS `user_groups`; CREATE TABLE IF NOT EXISTS `user_groups` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, - `permission` enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning') NOT NULL, + `permission` enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer') NOT NULL, `granted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `deleted` timestamp NULL DEFAULT NULL, `grantedby` int(11) NOT NULL, @@ -306,8 +324,20 @@ DROP TABLE IF EXISTS `org_admin`; CREATE TABLE IF NOT EXISTS `org_admin` ( `orgid` int(11) NOT NULL, `memid` int(11) NOT NULL, + `master` enum('y', 'n') NOT NULL, `creator` int(11) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `deleter` int(11) NULL DEFAULT NULL, `deleted` timestamp NULL DEFAULT NULL, - PRIMARY KEY (`orgid`, `memid`) + KEY (`orgid`, `memid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +DROP TABLE IF EXISTS `adminLog`; +CREATE TABLE `adminLog` ( + `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `uid` int(11) unsigned NOT NULL, + `admin` int(11) unsigned NOT NULL, + `type` varchar(100) NOT NULL DEFAULT '', + `information` varchar(50) NOT NULL DEFAULT '', + PRIMARY KEY (`when`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;