[DB]: Add orgAdmin management code

[gigi.git] / doc / tableStructure.sql

diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql
index a6f78ed00acdcc9987c4c09e21478caf82721f9c..55fdfdfc8cfd280803a549b7fe170cec27aad40a 100644 (file)
--- 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 '',
@@ -15,28 +24,34 @@ CREATE TABLE `users` (
   `listme` int(1) NOT NULL DEFAULT '0',
   `contactinfo` varchar(255) NOT NULL DEFAULT '',
   `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',
   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`),
@@ -48,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`),
@@ -134,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,
@@ -159,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` (
@@ -172,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';
@@ -181,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`;
@@ -221,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`),
@@ -278,3 +301,14 @@ CREATE TABLE IF NOT EXISTS `user_groups` (
   `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;