X-Git-Url: https://code.wpia.club/?p=gigi.git;a=blobdiff_plain;f=doc%2FtableStructure.sql;h=55fdfdfc8cfd280803a549b7fe170cec27aad40a;hp=9ad4d13474a831bddc1770070df2400a27349cf1;hb=fa1ae652fbff9c7caeded3fc89680703d1da381b;hpb=74f10c9db6cf2fcb5002ef4d2fa7f3c43abd6fb0

diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql
index 9ad4d134..55fdfdfc 100644
--- a/doc/tableStructure.sql
+++ b/doc/tableStructure.sql
@@ -1,6 +1,15 @@
+DROP TABLE IF EXISTS `certOwners`;
+CREATE TABLE `certOwners` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `modified` timestamp NULL DEFAULT NULL,
+  `deleted` timestamp NULL DEFAULT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
+
 DROP TABLE IF EXISTS `users`;
 CREATE TABLE `users` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `id` int(11) NOT NULL,
   `email` varchar(255) NOT NULL DEFAULT '',
   `password` varchar(255) NOT NULL DEFAULT '',
   `fname` varchar(255) NOT NULL DEFAULT '',
@@ -14,46 +23,49 @@ CREATE TABLE `users` (
   `locid` int(7) NOT NULL DEFAULT '0',
   `listme` int(1) NOT NULL DEFAULT '0',
   `contactinfo` varchar(255) NOT NULL DEFAULT '',
-  `admin` tinyint(1) NOT NULL DEFAULT '0',
   `language` varchar(5) 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',
-  `locked` tinyint(1) NOT NULL DEFAULT '0',
-  `assurer_blocked` tinyint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   KEY `ccid` (`ccid`),
   KEY `regid` (`regid`),
   KEY `locid` (`locid`),
   KEY `email` (`email`),
-  KEY `stats_users_created` (`created`),
-  KEY `stats_users_verified` (`verified`),
-  KEY `userverified` (`verified`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
+  KEY `stats_users_verified` (`verified`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
+DROP TABLE IF EXISTS `organisations`;
+CREATE TABLE IF NOT EXISTS `organisations` (
+  `id` int(11) NOT NULL,
+  `name` varchar(100) NOT NULL,
+  `state` varchar(2) NOT NULL,
+  `province` varchar(100) NOT NULL,
+  `city` varchar(100) NOT NULL,
+  `creator` int(11) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
 DROP TABLE IF EXISTS `domains`;
 CREATE TABLE `domains` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `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`),
   KEY `stats_domains_deleted` (`deleted`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `emails`;
 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`),
@@ -61,17 +73,17 @@ CREATE TABLE `emails` (
   KEY `stats_email_hash` (`hash`),
   KEY `stats_email_deleted` (`deleted`),
   KEY `email` (`email`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `emailPinglog`;
 CREATE TABLE `emailPinglog` (
   `when` datetime NOT NULL,
   `uid` int(11) NOT NULL,
-  `emailid` int(11) NOT NULL,
+  `email` varchar(255) NOT NULL,
   `type` enum('fast', 'active') NOT NULL,
   `status` enum('open', 'success', 'failed') NOT NULL,
   `result` varchar(255) NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `pingconfig`;
 CREATE TABLE `pingconfig` (
@@ -81,7 +93,7 @@ CREATE TABLE `pingconfig` (
   `info` varchar(255) NOT NULL,
   `reping` enum('y','n') NOT NULL DEFAULT 'n',
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 DROP TABLE IF EXISTS `domainPinglog`;
@@ -91,12 +103,12 @@ CREATE TABLE `domainPinglog` (
   `state` enum('open', 'success', 'failed') NOT NULL,
   `challenge` varchar(16),
   `result` varchar(255)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `baddomains`;
 CREATE TABLE `baddomains` (
   `domain` varchar(255) NOT NULL DEFAULT ''
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 DROP TABLE IF EXISTS `alerts`;
@@ -107,7 +119,7 @@ CREATE TABLE `alerts` (
   `regional` tinyint(1) NOT NULL DEFAULT '0',
   `radius` tinyint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`memid`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `user_agreements`;
 CREATE TABLE `user_agreements` (
@@ -120,7 +132,7 @@ CREATE TABLE `user_agreements` (
   `method` varchar(100) NOT NULL,
   `comment` varchar(100) DEFAULT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `certs`;
 CREATE TABLE `certs` (
@@ -128,7 +140,7 @@ CREATE TABLE `certs` (
   `memid` int(11) NOT NULL DEFAULT '0',
   `serial` varchar(50) NOT NULL DEFAULT '',
   `CN` varchar(255) NOT NULL DEFAULT '',
-  `subject` text NOT NULL,
+  `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',
@@ -137,10 +149,10 @@ CREATE TABLE `certs` (
   `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,
@@ -154,7 +166,7 @@ CREATE TABLE `certs` (
   KEY `serial` (`serial`),
   KEY `stats_emailcerts_expire` (`expire`),
   KEY `emailcrt` (`crt_name`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `clientcerts`;
 CREATE TABLE `clientcerts` (
@@ -162,7 +174,7 @@ CREATE TABLE `clientcerts` (
   `disablelogin` int(1) NOT NULL DEFAULT '0',
 
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `profiles`;
 CREATE TABLE `profiles` (
@@ -174,7 +186,8 @@ CREATE TABLE `profiles` (
   `name` varchar(100) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE (`keyname`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
+
 INSERT INTO `profiles` SET rootcert=0, keyname='client', name='ssl-client (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth';
 INSERT INTO `profiles` SET rootcert=0, keyname='mail',  name='mail (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='emailProtection';
 INSERT INTO `profiles` SET rootcert=0, keyname='client-mail', name='ssl-client + mail (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth, emailProtection';
@@ -184,6 +197,13 @@ INSERT INTO `profiles` SET rootcert=1, keyname='client-a', name='ssl-client (ass
 INSERT INTO `profiles` SET rootcert=1, keyname='mail-a',  name='mail (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='emailProtection';
 INSERT INTO `profiles` SET rootcert=1, keyname='client-mail-a', name='ssl-client + mail(assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth, emailProtection';
 INSERT INTO `profiles` SET rootcert=1, keyname='server-a', name='ssl-server (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='serverAuth';
+INSERT INTO `profiles` SET rootcert=2, keyname='code-a', name='codesign (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='codeSigning, msCodeInd, msCodeCom';
+
+INSERT INTO `profiles` SET rootcert=3, keyname='client-orga', name='ssl-client (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth';
+INSERT INTO `profiles` SET rootcert=3, keyname='mail-orga',  name='mail (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='emailProtection';
+INSERT INTO `profiles` SET rootcert=3, keyname='client-mail-orga', name='ssl-client + mail(orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth, emailProtection';
+INSERT INTO `profiles` SET rootcert=3, keyname='server-orga', name='ssl-server (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='serverAuth';
+INSERT INTO `profiles` SET rootcert=4, keyname='code-orga', name='codesign (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='codeSigning, msCodeInd, msCodeCom';
 
 -- 0=unassured, 1=assured, 2=codesign, 3=orga, 4=orga-sign
 DROP TABLE IF EXISTS `subjectAlternativeNames`;
@@ -191,7 +211,7 @@ CREATE TABLE `subjectAlternativeNames` (
   `certId` int(11) NOT NULL,
   `contents` varchar(50) NOT NULL,
   `type` enum('email','DNS') NOT NULL
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
 
 
 
@@ -207,7 +227,7 @@ CREATE TABLE `jobs` (
   `executeTo` VARCHAR(11),
   PRIMARY KEY (`id`),
   KEY `state` (`state`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
 
 
 DROP TABLE IF EXISTS `notary`;
@@ -224,19 +244,19 @@ 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`),
   KEY `stats_notary_when` (`when`),
   KEY `stats_notary_method` (`method`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
 
 
 DROP TABLE IF EXISTS `cats_passed`;
@@ -247,7 +267,7 @@ CREATE TABLE `cats_passed` (
   `pass_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   UNIQUE KEY `test_passed` (`user_id`,`variant_id`,`pass_date`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
 
 # --------------------------------------------------------
 
@@ -261,4 +281,34 @@ CREATE TABLE `cats_type` (
   `type_text` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `type_text` (`type_text`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
+
+DROP TABLE IF EXISTS `arbitrations`;
+CREATE TABLE IF NOT EXISTS `arbitrations` (
+  `user` int(11) NOT NULL,
+  `arbitration` varchar(20) NOT NULL,
+  PRIMARY KEY (`user`,`arbitration`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+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,
+  `granted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `deleted` timestamp NULL DEFAULT NULL,
+  `grantedby` int(11) NOT NULL,
+  `revokedby` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+DROP TABLE IF EXISTS `org_admin`;
+CREATE TABLE IF NOT EXISTS `org_admin` (
+  `orgid` int(11) NOT NULL,
+  `memid` int(11) 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`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;