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

diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql
index cf46d042..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,32 +23,35 @@ 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`),
@@ -51,9 +63,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`),
@@ -67,7 +79,7 @@ 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
@@ -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,
@@ -162,7 +174,7 @@ CREATE TABLE `clientcerts` (
   `disablelogin` int(1) NOT NULL DEFAULT '0',
 
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `profiles`;
 CREATE TABLE `profiles` (
@@ -175,6 +187,7 @@ CREATE TABLE `profiles` (
   PRIMARY KEY (`id`),
   UNIQUE (`keyname`)
 ) 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`;
@@ -224,13 +244,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`),
@@ -274,10 +294,21 @@ 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','ttp-assuer','ttp-applicant', 'codesigning') 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;