From 203a8fa7acc12bfc3debf6337362f3fb7900bab9 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Mon, 21 Dec 2015 18:00:38 +0100
Subject: [statistics] Add table definition, bump db version

---
 apis/clientlog.inc.php                       |  38 ++-
 apis/cron.inc.php                            |   6 +
 apis/update.inc.php                          |  49 +++-
 inc/database.inc.php                         |   2 +-
 install/content.sql                          |   2 +-
 install/schema.sql                           | 359 ++++++++++++++-------------
 lang/de/templates/statistics/clientlist.json |   2 +-
 7 files changed, 277 insertions(+), 181 deletions(-)

diff --git a/apis/clientlog.inc.php b/apis/clientlog.inc.php
index 01a470f0..842e3469 100644
--- a/apis/clientlog.inc.php
+++ b/apis/clientlog.inc.php
@@ -31,6 +31,7 @@ if ($type{0} === '~') {
 		}
 	}
 	$NOW = time();
+	$old = Database::queryFirst('SELECT logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid));
 	// Handle event type
 	if ($type === '~poweron') {
 		// Poweron & hw stats
@@ -45,6 +46,7 @@ if ($type{0} === '~') {
 		$valid = array('UNKNOWN', 'UNSUPPORTED', 'DISABLED', 'ENABLED');
 		if (!in_array($kvmstate, $valid)) $kvmstate = 'UNKNOWN';
 		$cpumodel = Request::post('cpumodel', '', 'string');
+		$systemmodel = Request::post('systemmodel', '', 'string');
 		$id44mb = Request::post('id44mb', 0, 'integer');
 		if ($id44mb < 0 || $id44mb > 10240000) $id44mb = 0;
 		$badsectors = Request::post('badsectors', 0, 'integer');
@@ -54,29 +56,43 @@ if ($type{0} === '~') {
 			$hostname = '';
 		}
 		$data = Request::post('data', '', 'string');
-		// See if we have a lingering session, create statistic entry if so
 		if ($uptime < 120) {
-			$old = Database::queryFirst('SELECT logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid));
+			// See if we have a lingering session, create statistic entry if so
 			if ($old !== false && (int)$old['logintime'] !== 0) {
 				$sessionLength = $old['lastseen'] - $old['logintime'];
 				if ($sessionLength > 0) {
 					$start = $old['logintime'];
 					if ($start === 0) $start = $NOW;
-					Database::exec('INSERT INTO statistic (dateline, typeid, clientip, username, data)'
-						. " VALUES (:start, '.session-length', :clientip, '', :length)", array(
+					Database::exec('INSERT INTO statistic (dateline, typeid, machineuuid, clientip, username, data)'
+						. " VALUES (:start, '~session-length', :uuid, :clientip, '', :length)", array(
 							'start'     => $start,
+							'uuid'     => $uuid,
 							'clientip'  => $ip,
 							'length'    => $sessionLength
 					));
 				}
 			}
+			// Write poweroff period length to statistic table
+			if ($old !== false) {
+				$lastSeen = $old['lastseen'] + 300;
+				$offtime = ($NOW - $uptime) - $lastSeen;
+				if ($offtime > 600 && $offtime < 86400 * 90) {
+					Database::exec('INSERT INTO statistic (dateline, typeid, machineuuid, clientip, username, data)'
+						. " VALUES (:shutdown, '~offline-length', :uuid, :clientip, '', :length)", array(
+							'shutdown' => $lastSeen,
+							'uuid'     => $uuid,
+							'clientip' => $ip,
+							'length'   => $offtime
+						));
+				}
+			}
 		}
 		// Create/update machine entry
 		Database::exec('INSERT INTO machine '
 			. '(machineuuid, macaddr, clientip, firstseen, lastseen, logintime, position, lastboot, realcores, mbram,'
 			. ' kvmstate, cpumodel, id44mb, badsectors, data, hostname) VALUES '
 			. "(:uuid, :macaddr, :clientip, :firstseen, :lastseen, 0, '', :lastboot, :realcores, :mbram,"
-			. ' :kvmstate, :cpumodel, :id44mb, :badsectors, :data, :hostname)'
+			. ' :kvmstate, :cpumodel, :systemmodel, :id44mb, :badsectors, :data, :hostname)'
 			. ' ON DUPLICATE KEY UPDATE'
 			. ' macaddr = VALUES(macaddr),'
 			. ' clientip = VALUES(clientip),'
@@ -87,6 +103,7 @@ if ($type{0} === '~') {
 			. ' mbram = VALUES(mbram),'
 			. ' kvmstate = VALUES(kvmstate),'
 			. ' cpumodel = VALUES(cpumodel),'
+			. ' systemmodel = VALUES(systemmodel),'
 			. ' id44mb = VALUES(id44mb),'
 			. ' badsectors = VALUES(badsectors),'
 			. ' data = VALUES(data),'
@@ -101,16 +118,18 @@ if ($type{0} === '~') {
 			'mbram'      => $mbram,
 			'kvmstate'   => $kvmstate,
 			'cpumodel'   => $cpumodel,
+			'systemmodel'=> $systemmodel,
 			'id44mb'     => $id44mb,
 			'badsectors' => $badsectors,
 			'data'       => $data,
 			'hostname'   => $hostname,
 		));
+		// Write statistics data
+		
 	} else if ($type === '~runstate') {
 		// Usage (occupied/free)
 		$sessionLength = 0;
 		$used = Request::post('used', 0, 'integer');
-		$old = Database::queryFirst('SELECT logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid));
 		if ($old === false) die("Unknown machine.\n");
 		settype($old['logintime'], 'integer');
 		settype($old['lastseen'], 'integer');
@@ -146,9 +165,10 @@ if ($type{0} === '~') {
 		if ($sessionLength > 0) {
 			$start = $old['logintime'];
 			if ($start === 0) $start = $NOW;
-			Database::exec('INSERT INTO statistic (dateline, typeid, clientip, username, data)'
-				. " VALUES (:start, '.session-length', :clientip, '', :length)", array(
+			Database::exec('INSERT INTO statistic (dateline, typeid, machineuuid, clientip, username, data)'
+				. " VALUES (:start, '~session-length', :uuid, :clientip, '', :length)", array(
 					'start'     => $start,
+					'uuid'     => $uuid,
 					'clientip'  => $ip,
 					'length'    => $sessionLength
 			));
@@ -161,7 +181,7 @@ if ($type{0} === '~') {
 				$start = $old['logintime'];
 				if ($start === 0) $start = $NOW;
 				Database::exec('INSERT INTO statistic (dateline, typeid, clientip, username, data)'
-					. " VALUES (:start, '.session-length', :clientip, '', :length)", array(
+					. " VALUES (:start, '~session-length', :clientip, '', :length)", array(
 						'start'     => $start,
 						'clientip'  => $ip,
 						'length'    => $sessionLength
diff --git a/apis/cron.inc.php b/apis/cron.inc.php
index 3553abdb..318fb67f 100644
--- a/apis/cron.inc.php
+++ b/apis/cron.inc.php
@@ -23,6 +23,12 @@ case 3:
 case 4:
 	Database::exec("DELETE FROM callback WHERE (UNIX_TIMESTAMP() - dateline) > 86400");
 	break;
+case 5:
+	Database::exec("DELETE FROM statistic WHERE (UNIX_TIMESTAMP() - dateline) > 86400 * 90");
+	break;
+case 6:
+	Database::exec("DELETE FROM machine WHERE (UNIX_TIMESTAMP() - lastseen) > 86400 * 365");
+	break;
 }
 
 Trigger::checkCallbacks();
diff --git a/apis/update.inc.php b/apis/update.inc.php
index 134e7ad8..a7212961 100644
--- a/apis/update.inc.php
+++ b/apis/update.inc.php
@@ -226,7 +226,7 @@ function update_8()
 
 // #######################
 // ##### 2015-05-21
-// Add statistics table, for future logging of eg. 
+// Add statistics table, for logging of session length and idle times
 function update_9()
 {
 	Database::exec("CREATE TABLE IF NOT EXISTS `statistic` (
@@ -242,4 +242,49 @@ function update_9()
   KEY `clientip` (`clientip`,`dateline`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8");
 	return true;
-}
\ No newline at end of file
+}
+
+// #######################
+// ##### 2015-12-21
+// Add machine uuid column to statistics table
+function update_10()
+{
+	if (!tableHasColumn('statistic', 'machineuuid')) {
+		Database::exec('ALTER TABLE statistic'
+			. ' ADD COLUMN `machineuuid` varchar(36) CHARACTER SET ascii DEFAULT NULL AFTER clientip,'
+			. ' ADD INDEX `machineuuid` (`machineuuid`,`dateline`)');
+	}
+	Database::exec("CREATE TABLE IF NOT EXISTS `machine` (
+  `machineuuid` char(36) CHARACTER SET ascii NOT NULL,
+  `roomid` int(10) unsigned DEFAULT NULL,
+  `macaddr` char(17) CHARACTER SET ascii NOT NULL,
+  `clientip` varchar(45) CHARACTER SET ascii NOT NULL,
+  `firstseen` int(10) unsigned NOT NULL,
+  `lastseen` int(10) unsigned NOT NULL,
+  `logintime` int(10) unsigned NOT NULL,
+  `position` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
+  `lastboot` int(10) unsigned NOT NULL,
+  `realcores` smallint(5) unsigned NOT NULL,
+  `mbram` int(10) unsigned NOT NULL,
+  `kvmstate` enum('UNKNOWN','UNSUPPORTED','DISABLED','ENABLED') NOT NULL,
+  `cpumodel` varchar(120) NOT NULL,
+  `systemmodel` varchar(120) NOT NULL DEFAULT '',
+  `id44mb` int(10) unsigned NOT NULL,
+  `badsectors` int(10) unsigned NOT NULL,
+  `data` mediumtext NOT NULL,
+  `hostname` varchar(200) NOT NULL DEFAULT '',
+  `notes` text,
+  PRIMARY KEY (`machineuuid`),
+  KEY `macaddr` (`macaddr`),
+  KEY `clientip` (`clientip`),
+  KEY `realcores` (`realcores`),
+  KEY `mbram` (`mbram`),
+  KEY `kvmstate` (`kvmstate`),
+  KEY `id44mb` (`id44mb`),
+  KEY `roomid` (`roomid`),
+  KEY `lastseen` (`lastseen`),
+  KEY `cpumodel` (`cpumodel`),
+  KEY `systemmodel` (`systemmodel`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+	return true;
+}
diff --git a/inc/database.inc.php b/inc/database.inc.php
index 3bd42636..6addb931 100644
--- a/inc/database.inc.php
+++ b/inc/database.inc.php
@@ -20,7 +20,7 @@ class Database
 	 */
 	public static function getExpectedSchemaVersion()
 	{
-		return 10;
+		return 11;
 	}
 	
 	public static function needSchemaUpdate()
diff --git a/install/content.sql b/install/content.sql
index d5c5f2ac..bf54a711 100644
--- a/install/content.sql
+++ b/install/content.sql
@@ -1,2 +1,2 @@
-INSERT INTO property (name, dateline, value) VALUES ('webif-version', 0, 10);
+INSERT INTO property (name, dateline, value) VALUES ('webif-version', 0, 11);
 
diff --git a/install/schema.sql b/install/schema.sql
index 2cc68861..c4ca99c5 100644
--- a/install/schema.sql
+++ b/install/schema.sql
@@ -1,167 +1,192 @@
-SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
-SET time_zone = "+00:00";
-
-/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
-/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
-/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
-/*!40101 SET NAMES utf8 */;
-
-
-CREATE TABLE `callback` (
-  `taskid` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
-  `dateline` int(10) unsigned NOT NULL,
-  `cbfunction` varchar(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
-  `args` text NOT NULL,
-  PRIMARY KEY (`taskid`,`cbfunction`),
-  KEY `dateline` (`dateline`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-CREATE TABLE `cat_setting` (
-  `catid` int(10) unsigned NOT NULL,
-  `sortval` smallint(5) unsigned NOT NULL,
-  PRIMARY KEY (`catid`),
-  KEY `sortval` (`sortval`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-CREATE TABLE `clientlog` (
-  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `dateline` int(10) unsigned NOT NULL,
-  `logtypeid` varchar(30) NOT NULL,
-  `clientip` varchar(40) NOT NULL,
-  `description` varchar(255) NOT NULL,
-  `extra` text NOT NULL,
-  PRIMARY KEY (`logid`),
-  KEY `dateline` (`dateline`),
-  KEY `logtypeid` (`logtypeid`,`dateline`),
-  KEY `clientip` (`clientip`,`dateline`)
-) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-
-CREATE TABLE `configtgz` (
-  `configid` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `title` varchar(200) NOT NULL,
-  `filepath` varchar(255) NOT NULL,
-  `status` enum('OK','OUTDATED','MISSING') NOT NULL DEFAULT 'MISSING',
-  PRIMARY KEY (`configid`)
-) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-
-CREATE TABLE `configtgz_module` (
-  `moduleid` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `title` varchar(200) NOT NULL,
-  `moduletype` varchar(16) NOT NULL,
-  `filepath` varchar(250) NOT NULL,
-  `contents` text NOT NULL,
-  `version` int(10) unsigned NOT NULL DEFAULT '0',
-  `status` enum('OK','MISSING','OUTDATED') NOT NULL DEFAULT 'MISSING',
-  PRIMARY KEY (`moduleid`),
-  KEY `title` (`title`),
-  KEY `moduletype` (`moduletype`,`title`)
-) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-
-CREATE TABLE `configtgz_x_module` (
-  `configid` int(10) unsigned NOT NULL,
-  `moduleid` int(10) unsigned NOT NULL,
-  PRIMARY KEY (`configid`,`moduleid`),
-  KEY `moduleid` (`moduleid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-CREATE TABLE `eventlog` (
-  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `dateline` int(10) unsigned NOT NULL,
-  `logtypeid` varchar(30) NOT NULL,
-  `description` varchar(255) NOT NULL,
-  `extra` text NOT NULL,
-  PRIMARY KEY (`logid`),
-  KEY `dateline` (`dateline`),
-  KEY `logtypeid` (`logtypeid`,`dateline`)
-) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-
-CREATE TABLE `news` (
-  `newsid` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `dateline` int(10) unsigned NOT NULL,
-  `title` varchar(200) DEFAULT NULL,
-  `content` text,
-  PRIMARY KEY (`newsid`),
-  KEY `dateline` (`dateline`)
-) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-
-CREATE TABLE `permission` (
-  `mask` int(10) unsigned NOT NULL,
-  `identifier` varchar(32) NOT NULL,
-  PRIMARY KEY (`mask`),
-  UNIQUE KEY `identifier` (`identifier`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-CREATE TABLE `property` (
-  `name` varchar(50) NOT NULL,
-  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
-  `value` text NOT NULL,
-  PRIMARY KEY (`name`),
-  KEY `dateline` (`dateline`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-CREATE TABLE `setting` (
-  `setting` varchar(28) NOT NULL,
-  `catid` int(10) unsigned NOT NULL,
-  `defaultvalue` text NOT NULL,
-  `permissions` int(10) unsigned NOT NULL,
-  `validator` varchar(250) NOT NULL DEFAULT '',
-  PRIMARY KEY (`setting`),
-  KEY `catid` (`catid`,`setting`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-CREATE TABLE `setting_distro` (
-  `distroid` int(10) unsigned NOT NULL,
-  `setting` varchar(28) NOT NULL,
-  `value` text NOT NULL,
-  `displayvalue` text NOT NULL,
-  PRIMARY KEY (`distroid`,`setting`),
-  KEY `setting` (`setting`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-CREATE TABLE `setting_global` (
-  `setting` varchar(28) NOT NULL,
-  `value` text NOT NULL,
-  `displayvalue` text NOT NULL,
-  PRIMARY KEY (`setting`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-CREATE TABLE `statistic` (
-  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `dateline` int(10) unsigned NOT NULL,
-  `typeid` varchar(30) NOT NULL,
-  `clientip` varchar(40) NOT NULL,
-  `username` varchar(255) NOT NULL,
-  `data` varchar(255) NOT NULL,
-  PRIMARY KEY (`logid`),
-  KEY `dateline` (`dateline`),
-  KEY `logtypeid` (`typeid`,`dateline`),
-  KEY `clientip` (`clientip`,`dateline`)
-) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-
-CREATE TABLE `user` (
-  `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `login` varchar(100) NOT NULL,
-  `passwd` varchar(150) NOT NULL,
-  `fullname` varchar(100) DEFAULT NULL,
-  `phone` varchar(100) DEFAULT NULL,
-  `email` varchar(100) DEFAULT NULL,
-  `permissions` int(10) unsigned NOT NULL,
-  `lasteventid` int(10) unsigned NOT NULL DEFAULT '0',
-  PRIMARY KEY (`userid`),
-  UNIQUE KEY `login` (`login`)
-) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-
-
-ALTER TABLE `configtgz_x_module`
-  ADD CONSTRAINT `configtgz_x_module_ibfk_1` FOREIGN KEY (`configid`) REFERENCES `configtgz` (`configid`) ON DELETE CASCADE,
-  ADD CONSTRAINT `configtgz_x_module_ibfk_2` FOREIGN KEY (`moduleid`) REFERENCES `configtgz_module` (`moduleid`);
-
-ALTER TABLE `setting`
-  ADD CONSTRAINT `setting_ibfk_1` FOREIGN KEY (`catid`) REFERENCES `cat_setting` (`catid`) ON UPDATE CASCADE;
-
-ALTER TABLE `setting_distro`
-  ADD CONSTRAINT `setting_distro_ibfk_1` FOREIGN KEY (`setting`) REFERENCES `setting` (`setting`) ON DELETE CASCADE ON UPDATE CASCADE;
-
-/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
-/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
-/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
+SET time_zone = "+00:00";
+
+CREATE TABLE `callback` (
+  `taskid` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
+  `dateline` int(10) unsigned NOT NULL,
+  `cbfunction` varchar(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
+  `args` text NOT NULL,
+  PRIMARY KEY (`taskid`,`cbfunction`),
+  KEY `dateline` (`dateline`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `cat_setting` (
+  `catid` int(10) unsigned NOT NULL,
+  `sortval` smallint(5) unsigned NOT NULL,
+  PRIMARY KEY (`catid`),
+  KEY `sortval` (`sortval`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `clientlog` (
+  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `dateline` int(10) unsigned NOT NULL,
+  `logtypeid` varchar(30) NOT NULL,
+  `clientip` varchar(40) NOT NULL,
+  `description` varchar(255) NOT NULL,
+  `extra` text NOT NULL,
+  PRIMARY KEY (`logid`),
+  KEY `dateline` (`dateline`),
+  KEY `logtypeid` (`logtypeid`,`dateline`),
+  KEY `clientip` (`clientip`,`dateline`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
+
+CREATE TABLE `configtgz` (
+  `configid` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `title` varchar(200) NOT NULL,
+  `filepath` varchar(255) NOT NULL,
+  `status` enum('OK','OUTDATED','MISSING') NOT NULL DEFAULT 'MISSING',
+  PRIMARY KEY (`configid`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
+
+CREATE TABLE `configtgz_module` (
+  `moduleid` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `title` varchar(200) NOT NULL,
+  `moduletype` varchar(16) NOT NULL,
+  `filepath` varchar(250) NOT NULL,
+  `contents` text NOT NULL,
+  `version` int(10) unsigned NOT NULL DEFAULT '0',
+  `status` enum('OK','MISSING','OUTDATED') NOT NULL DEFAULT 'MISSING',
+  PRIMARY KEY (`moduleid`),
+  KEY `title` (`title`),
+  KEY `moduletype` (`moduletype`,`title`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
+
+CREATE TABLE `configtgz_x_module` (
+  `configid` int(10) unsigned NOT NULL,
+  `moduleid` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`configid`,`moduleid`),
+  KEY `moduleid` (`moduleid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `eventlog` (
+  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `dateline` int(10) unsigned NOT NULL,
+  `logtypeid` varchar(30) NOT NULL,
+  `description` varchar(255) NOT NULL,
+  `extra` text NOT NULL,
+  PRIMARY KEY (`logid`),
+  KEY `dateline` (`dateline`),
+  KEY `logtypeid` (`logtypeid`,`dateline`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
+
+CREATE TABLE `machine` (
+  `machineuuid` char(36) CHARACTER SET ascii NOT NULL,
+  `roomid` int(10) unsigned DEFAULT NULL,
+  `macaddr` char(17) CHARACTER SET ascii NOT NULL,
+  `clientip` varchar(45) CHARACTER SET ascii NOT NULL,
+  `firstseen` int(10) unsigned NOT NULL,
+  `lastseen` int(10) unsigned NOT NULL,
+  `logintime` int(10) unsigned NOT NULL,
+  `position` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
+  `lastboot` int(10) unsigned NOT NULL,
+  `realcores` smallint(5) unsigned NOT NULL,
+  `mbram` int(10) unsigned NOT NULL,
+  `kvmstate` enum('UNKNOWN','UNSUPPORTED','DISABLED','ENABLED') NOT NULL,
+  `cpumodel` varchar(120) NOT NULL,
+  `systemmodel` varchar(120) NOT NULL DEFAULT '',
+  `id44mb` int(10) unsigned NOT NULL,
+  `badsectors` int(10) unsigned NOT NULL,
+  `data` mediumtext NOT NULL,
+  `hostname` varchar(200) NOT NULL DEFAULT '',
+  `notes` text,
+  PRIMARY KEY (`machineuuid`),
+  KEY `macaddr` (`macaddr`),
+  KEY `clientip` (`clientip`),
+  KEY `realcores` (`realcores`),
+  KEY `mbram` (`mbram`),
+  KEY `kvmstate` (`kvmstate`),
+  KEY `id44mb` (`id44mb`),
+  KEY `roomid` (`roomid`),
+  KEY `lastseen` (`lastseen`),
+  KEY `cpumodel` (`cpumodel`),
+  KEY `systemmodel` (`systemmodel`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `news` (
+  `newsid` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `dateline` int(10) unsigned NOT NULL,
+  `title` varchar(200) DEFAULT NULL,
+  `content` text,
+  PRIMARY KEY (`newsid`),
+  KEY `dateline` (`dateline`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
+
+CREATE TABLE `permission` (
+  `mask` int(10) unsigned NOT NULL,
+  `identifier` varchar(32) NOT NULL,
+  PRIMARY KEY (`mask`),
+  UNIQUE KEY `identifier` (`identifier`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `property` (
+  `name` varchar(50) NOT NULL,
+  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
+  `value` text NOT NULL,
+  PRIMARY KEY (`name`),
+  KEY `dateline` (`dateline`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `setting` (
+  `setting` varchar(28) NOT NULL,
+  `catid` int(10) unsigned NOT NULL,
+  `defaultvalue` text NOT NULL,
+  `permissions` int(10) unsigned NOT NULL,
+  `validator` varchar(250) NOT NULL DEFAULT '',
+  PRIMARY KEY (`setting`),
+  KEY `catid` (`catid`,`setting`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `setting_distro` (
+  `distroid` int(10) unsigned NOT NULL,
+  `setting` varchar(28) NOT NULL,
+  `value` text NOT NULL,
+  `displayvalue` text NOT NULL,
+  PRIMARY KEY (`distroid`,`setting`),
+  KEY `setting` (`setting`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `setting_global` (
+  `setting` varchar(28) NOT NULL,
+  `value` text NOT NULL,
+  `displayvalue` text NOT NULL,
+  PRIMARY KEY (`setting`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `statistic` (
+  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `dateline` int(10) unsigned NOT NULL,
+  `typeid` varchar(30) NOT NULL,
+  `machineuuid` varchar(36) CHARACTER SET ascii DEFAULT NULL,
+  `clientip` varchar(40) NOT NULL,
+  `username` varchar(255) NOT NULL,
+  `data` varchar(255) NOT NULL,
+  PRIMARY KEY (`logid`),
+  KEY `dateline` (`dateline`),
+  KEY `logtypeid` (`typeid`,`dateline`),
+  KEY `clientip` (`clientip`,`dateline`),
+  KEY `machineuuid` (`machineuuid`,`dateline`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
+
+CREATE TABLE `user` (
+  `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `login` varchar(100) NOT NULL,
+  `passwd` varchar(150) NOT NULL,
+  `fullname` varchar(100) DEFAULT NULL,
+  `phone` varchar(100) DEFAULT NULL,
+  `email` varchar(100) DEFAULT NULL,
+  `permissions` int(10) unsigned NOT NULL,
+  `lasteventid` int(10) unsigned NOT NULL DEFAULT '0',
+  PRIMARY KEY (`userid`),
+  UNIQUE KEY `login` (`login`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
+
+
+ALTER TABLE `configtgz_x_module`
+  ADD CONSTRAINT `configtgz_x_module_ibfk_1` FOREIGN KEY (`configid`) REFERENCES `configtgz` (`configid`) ON DELETE CASCADE,
+  ADD CONSTRAINT `configtgz_x_module_ibfk_2` FOREIGN KEY (`moduleid`) REFERENCES `configtgz_module` (`moduleid`);
+
+ALTER TABLE `setting`
+  ADD CONSTRAINT `setting_ibfk_1` FOREIGN KEY (`catid`) REFERENCES `cat_setting` (`catid`) ON UPDATE CASCADE;
+
+ALTER TABLE `setting_distro`
+  ADD CONSTRAINT `setting_distro_ibfk_1` FOREIGN KEY (`setting`) REFERENCES `setting` (`setting`) ON DELETE CASCADE ON UPDATE CASCADE;
diff --git a/lang/de/templates/statistics/clientlist.json b/lang/de/templates/statistics/clientlist.json
index 8d9c4cbe..a1022e69 100644
--- a/lang/de/templates/statistics/clientlist.json
+++ b/lang/de/templates/statistics/clientlist.json
@@ -4,7 +4,7 @@
     "lang_cpuModel": "CPU",
     "lang_gbRam": "RAM",
     "lang_kvmSupport": "64\u2009Bit G\u00e4ste",
-    "lang_lastSeen": "Zuletzt gesehn",
+    "lang_lastSeen": "Zuletzt gesehen",
     "lang_machine": "Rechner",
     "lang_machineIdle": "Der Rechner ist eingeschaltet und wird zur Zeit nicht benutzt",
     "lang_machineOccupied": "Der Rechner ist eingeschaltet und wird benutzt",
-- 
cgit v1.2.3-55-g7522