From 9ab208e78c62306ad796dc74cb995e9f727c84ed Mon Sep 17 00:00:00 2001 From: Timothy Pearson Date: Wed, 3 Sep 2014 18:50:02 -0500 Subject: Properly handle recoverable MySQL connection interruptions Properly track arbiter use --- database/mysql/remotelab.sql | 279 ++++++++++++++++++++++--------------------- 1 file changed, 143 insertions(+), 136 deletions(-) (limited to 'database') diff --git a/database/mysql/remotelab.sql b/database/mysql/remotelab.sql index 7a7a654..e7de595 100644 --- a/database/mysql/remotelab.sql +++ b/database/mysql/remotelab.sql @@ -1,136 +1,143 @@ -/* - * Remote Laboratory Database Definition - * - * This program is free software; you can redistribute it and/or modify - * it under the terms of the GNU General Public License as published by - * the Free Software Foundation; either version 3 of the License, or - * (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU General Public License for more details. - * - * You should have received a copy of the GNU General Public License along - * with this program; if not, write to the Free Software Foundation, Inc., - * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. - * - * (c) 2014 Timothy Pearson - * Raptor Engineering - * http://www.raptorengineeringinc.com - */ - - -/*!40101 SET NAMES utf8 */; - -/*!40101 SET SQL_MODE=''*/; - -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -CREATE DATABASE /*!32312 IF NOT EXISTS*/`remotelab` /*!40100 DEFAULT CHARACTER SET latin1 */; - -USE `remotelab`; - -/*Table structure for table `activity` */ - -DROP TABLE IF EXISTS `activity`; - -CREATE TABLE `activity` ( - `pk` int(20) NOT NULL AUTO_INCREMENT, - `station` bigint(20) NOT NULL, - `username` text, - `realmname` text, - `logontime` bigint(20) DEFAULT NULL, - `serverid` bigint(20) DEFAULT NULL, - `serviceid` bigint(20) DEFAULT NULL, - `terminate` bigint(20) NOT NULL, - PRIMARY KEY (`pk`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -/*Table structure for table `permissions` */ - -DROP TABLE IF EXISTS `permissions`; - -CREATE TABLE `permissions` ( - `pk` bigint(20) NOT NULL AUTO_INCREMENT, - `station` bigint(20) NOT NULL, - `groupname` text NOT NULL, - `realm` text, - `restrictions` text, - PRIMARY KEY (`pk`) -) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; - -/*Table structure for table `services` */ - -DROP TABLE IF EXISTS `services`; - -CREATE TABLE `services` ( - `pk` bigint(20) NOT NULL AUTO_INCREMENT, - `station` bigint(20) NOT NULL, - `servicetype` bigint(20) NOT NULL, - `hostname` text, - `port` bigint(20) DEFAULT NULL, - PRIMARY KEY (`pk`) -) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; - -/*Table structure for table `servicetypes` */ - -DROP TABLE IF EXISTS `servicetypes`; - -CREATE TABLE `servicetypes` ( - `serviceid` bigint(20) NOT NULL AUTO_INCREMENT, - `name` text, - `description` text, - `client_library` text NOT NULL, - `version` bigint(20) DEFAULT NULL, - `single_instance` bigint(20) DEFAULT NULL, - PRIMARY KEY (`serviceid`) -) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1; - -/*Data for the table `servicetypes` */ - -insert into `servicetypes`(`serviceid`,`name`,`description`,`client_library`,`version`,`single_instance`) values (1,'Remote FPGA','Remote FPGA access','libremotelab_fpgaviewer',1,1),(2,'Spectrum Analyzer','GPIB spectrum analyzer access','libremotelab_commanalyzer',1,1),(3,'FPGA Programmer','Remote FPGA programming','libremotelab_fpgaprogrammer',1,1),(4,'Oscilloscope','GPIB oscilloscope access','libremotelab_scope',1,1),(5,'Sensor Monitor','Sensor monitor access','libremotelab_sensormonitor',1,1),(6,'Administration Console','View and control active sessions','libremotelab_adminconsole',1,0),(7,'User Management Console','Manage user permissions','libremotelab_adminusermgmt',1,0),(8,'Logic Analyzer','View digital waveforms','libremotelab_logicanalyzer',1,1),(9,'Serial Console','Communicate via a serial connection','libremotelab_serialconsole',1,1); - -/*Table structure for table `stations` */ - -DROP TABLE IF EXISTS `stations`; - -CREATE TABLE `stations` ( - `pk` bigint(20) NOT NULL AUTO_INCREMENT, - `name` text, - `description` text, - PRIMARY KEY (`pk`) -) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; - -/*Table structure for table `statistics` */ - -DROP TABLE IF EXISTS `statistics`; - -CREATE TABLE `statistics` ( - `pk` bigint(20) NOT NULL AUTO_INCREMENT, - `timestamp` bigint(20) NOT NULL, - `eventtypeid` bigint(20) NOT NULL, - `serverid` bigint(20) DEFAULT NULL, - `sessionid` bigint(20) DEFAULT NULL, - `typeid` bigint(20) DEFAULT NULL, - `userid` text, - PRIMARY KEY (`pk`) -) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1; - -/*Table structure for table `status` */ - -DROP TABLE IF EXISTS `status`; - -CREATE TABLE `status` ( - `pk` bigint(20) NOT NULL AUTO_INCREMENT, - `hostname` text NOT NULL, - `online` tinyint(4) NOT NULL, - PRIMARY KEY (`pk`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; -/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; +/* + * Remote Laboratory Database Definition + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License along + * with this program; if not, write to the Free Software Foundation, Inc., + * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + * + * (c) 2014 Timothy Pearson + * Raptor Engineering + * http://www.raptorengineeringinc.com + */ + +/*!40101 SET NAMES utf8 */; + +/*!40101 SET SQL_MODE=''*/; + +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +CREATE DATABASE /*!32312 IF NOT EXISTS*/`remotelab` /*!40100 DEFAULT CHARACTER SET latin1 */; + +USE `remotelab`; + +/*Table structure for table `activity` */ + +DROP TABLE IF EXISTS `activity`; + +CREATE TABLE `activity` ( + `pk` int(20) NOT NULL AUTO_INCREMENT, + `station` bigint(20) NOT NULL, + `arbiter` text, + `username` text, + `realmname` text, + `logontime` bigint(20) DEFAULT NULL, + `serviceid` bigint(20) DEFAULT NULL, + `terminate` bigint(20) NOT NULL, + PRIMARY KEY (`pk`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +/*Table structure for table `dbschema` */ + +DROP TABLE IF EXISTS `dbschema`; + +CREATE TABLE `dbschema` ( + `pk` bigint(20) NOT NULL, + `skey` text NOT NULL, + `value` text NOT NULL, + PRIMARY KEY (`pk`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +/*Table structure for table `permissions` */ + +DROP TABLE IF EXISTS `permissions`; + +CREATE TABLE `permissions` ( + `pk` bigint(20) NOT NULL AUTO_INCREMENT, + `station` bigint(20) NOT NULL, + `groupname` text NOT NULL, + `realm` text, + `restrictions` text, + PRIMARY KEY (`pk`) +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1; + +/*Table structure for table `services` */ + +DROP TABLE IF EXISTS `services`; + +CREATE TABLE `services` ( + `pk` bigint(20) NOT NULL AUTO_INCREMENT, + `station` bigint(20) NOT NULL, + `servicetype` bigint(20) NOT NULL, + `hostname` text, + `port` bigint(20) DEFAULT NULL, + PRIMARY KEY (`pk`) +) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; + +/*Table structure for table `servicetypes` */ + +DROP TABLE IF EXISTS `servicetypes`; + +CREATE TABLE `servicetypes` ( + `serviceid` bigint(20) NOT NULL AUTO_INCREMENT, + `name` text, + `description` text, + `client_library` text NOT NULL, + `version` bigint(20) DEFAULT NULL, + `single_instance` bigint(20) DEFAULT NULL, + PRIMARY KEY (`serviceid`) +) ENGINE=InnoDB AUTO_INCREMENT=257 DEFAULT CHARSET=latin1; + +/*Table structure for table `stations` */ + +DROP TABLE IF EXISTS `stations`; + +CREATE TABLE `stations` ( + `pk` bigint(20) NOT NULL AUTO_INCREMENT, + `name` text, + `description` text, + PRIMARY KEY (`pk`) +) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1; + +/*Table structure for table `statistics` */ + +DROP TABLE IF EXISTS `statistics`; + +CREATE TABLE `statistics` ( + `pk` bigint(20) NOT NULL AUTO_INCREMENT, + `timestamp` bigint(20) NOT NULL, + `eventtypeid` bigint(20) NOT NULL, + `arbiter` text, + `stationid` bigint(20) DEFAULT NULL, + `sessionid` bigint(20) DEFAULT NULL, + `typeid` bigint(20) DEFAULT NULL, + `userid` text, + PRIMARY KEY (`pk`) +) ENGINE=InnoDB AUTO_INCREMENT=2339 DEFAULT CHARSET=latin1; + +/*Table structure for table `status` */ + +DROP TABLE IF EXISTS `status`; + +CREATE TABLE `status` ( + `pk` bigint(20) NOT NULL AUTO_INCREMENT, + `hostname` text NOT NULL, + `online` tinyint(4) NOT NULL, + PRIMARY KEY (`pk`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- cgit v1.2.1