[usb-lock-off] Switched to the _hw db structure. Adding a device and db contraints working properly. WIP

5 files changed, 195 insertions, 14 deletions

diff --git a/modules-available/statistics/inc/devicetype.inc.php b/modules-available/statistics/inc/devicetype.inc.php
index 41ee237d..571d6d2c 100644
--- a/modules-available/statistics/inc/devicetype.inc.php
+++ b/modules-available/statistics/inc/devicetype.inc.php
@@ -3,4 +3,5 @@
 class DeviceType
 {
 	const SCREEN = 'SCREEN';
+	const USB = 'USB';
 }
diff --git a/modules-available/usblockoff/api.inc.php b/modules-available/usblockoff/api.inc.php
index 14bc6805..318f21a1 100644
--- a/modules-available/usblockoff/api.inc.php
+++ b/modules-available/usblockoff/api.inc.php
@@ -10,18 +10,40 @@ function HandleParameters()
 		$serial = Request::get('serial', '', 'sting');
 		$name = Request::get('name', '', 'string');
 		$ip = Request::get('ip', 0, 'string');
-		$ruleInformation['hash'] = Request::get('hash', '', 'string');
-		$ruleInformation['parent-hash'] = Request::get('parent-hash', '', 'string');
-		$ruleInformation['via-port'] = Request::get('via-port', '', 'string');
-		$ruleInformation['with-interface'] = Request::get('with-interface', '', 'string');
-		$ruleInformation['interface-policy'] = Request::get('interface-policy', '', 'string');
-		newDevice($id, $serial, $name, $ip, $ruleInformation);
+		$client = Database::queryFirst("SELECT m.machineuuid AS 'muid', m.currentuser AS 'user' FROM machine AS m WHERE m.clientip=:ip", array('ip' => $ip));
+
+		// $ruleInformation['hash'] = Request::get('hash', '', 'string');
+		// $ruleInformation['parent-hash'] = Request::get('parent-hash', '', 'string');
+		// $ruleInformation['via-port'] = Request::get('via-port', '', 'string');
+		// $ruleInformation['with-interface'] = Request::get('with-interface', '', 'string');
+		// $ruleInformation['interface-policy'] = Request::get('interface-policy', '', 'string');
+		// newDevice($id, $serial, $name, $ip, $ruleInformation);
+		// TODO: product and vendor id necessary? It's already in the hwname part.
+		list($vid, $pid) = explode(':', $id);
+		$hwProps = array(
+			'vendorid' => $vid,
+			'productid' => $pid,
+			'name' => $name
+		);
+		// TODO: WITH INTERFACE in the HW table?! Should be equal for every device but not guaranteed (ODROID).
+		$deviceProps = array(
+			'hash' => Request::get('hash', '', 'string'),
+			'parent-hash' => Request::get('parent-hash', '', 'string'),
+			'via-port' => Request::get('via-port', '', 'string'),
+			'with-interface' => Request::get('with-interface', '', 'string'),
+			'interface-policy' => Request::get('interface-policy', '', 'string'),
+			'machineuuid' => $client['muid'],
+			'user' => $client['user'],
+			'lastseen' => time()
+		);
+		newDevice($id, $serial, $hwProps, $deviceProps);
 	} elseif ($getAction == "deletedevice") {
 		$serial = Request::get('serial', '', 'string');
 		deleteDevice($serial);
 	}
 }
 
+
 /**
  * Adds a new USB-Device to the db.
  *
@@ -29,6 +51,73 @@ function HandleParameters()
  * @param string $serial USB-Device serial number.
  * @param string $name USB-Device name.
  */
+function newDevice($id, $serial, $hwProps, $deviceProps)
+{
+	// Add or Update the usb device in the statistic_hw table.
+	$hwid = (int)Database::insertIgnore('statistic_hw', 'hwid', array(
+		'hwtype' => DeviceType::USB,
+		'hwname' => $id));
+	// TODO: Is it okay to use the id (vendor:product) as hwname to identify a usb device?
+
+	// Add all the global prop values to the statistics_hw_prop table.
+	// productid, vendorid, name, interfaces
+	// TODO:
+	addHwProps('statistic_hw_prop', $hwid, $hwProps);
+
+	// Add the hwid -> serial in the usblockoff_hw table if not already existent.
+	$dbquery2 = Database::queryFirst("Select * FROM `usblockoff_hw` WHERE hwid=:hwid AND serial=:serial", array(
+		'hwid' => $hwid,
+		'serial' => $serial));
+
+	if (empty($dbquery2)) {
+		Database::exec("INSERT INTO `usblockoff_hw` (hwid, serial) VALUES (:hwid, :serial)", array(
+			'hwid' => $hwid,
+			'serial' => $serial
+		));
+	}
+
+	// Add all the prop values to the usblockoff_hw_prop table.
+	// PROP: serial, machineuuid, time, user, ruleInformation, Port, hash, interface-policy
+	addUSBHwProps('usblockoff_hw_prop', $hwid, $serial, $deviceProps);
+
+	echo "Successfully added";
+}
+
+function addHwProps($table, $hwid, $propArray) {
+	foreach ($propArray as $prop => $value) {
+		if (empty($value)) {
+			continue;
+		}
+		Database::exec("INSERT INTO " . $table . " (hwid, prop, value) VALUES (:hwid, :prop, :value) ON DUPLICATE KEY UPDATE value=:value", array(
+			'hwid' => $hwid,
+			'prop' => $prop,
+			'value' => $value
+		));
+	}
+}
+
+function addUSBHwProps($table, $hwid, $serial, $propArray) {
+	foreach ($propArray as $prop => $value) {
+		if (empty($value)) {
+			continue;
+		}
+		Database::exec("INSERT INTO " . $table . " (hwid, serial, prop, value) VALUES (:hwid, :serial, :prop, :value) ON DUPLICATE KEY UPDATE value=:value", array(
+			'hwid' => $hwid,
+			'serial' => $serial,
+			'prop' => $prop,
+			'value' => $value
+		));
+	}
+}
+
+/**
+ * Adds a new USB-Device to the db.
+ *
+ * @param string $id USB-Device id.
+ * @param string $serial USB-Device serial number.
+ * @param string $name USB-Device name.
+ */
+/* VERSION WITH OLD DB ---------------------------------------------------------------------------------------
 function newDevice($id, $serial, $name, $ip, $ruleInformation)
 {
 	$NOW = time();
@@ -63,12 +152,13 @@ function newDevice($id, $serial, $name, $ip, $ruleInformation)
 	}
 
 }
-
+*/
 /**
  * Deletes a device from the db given a serial number.
  *
  * @param string $serial USB-Device serial number.
  */
+// TODO: Edit for the new db struct.
 function deleteDevice($serial)
 {
 	$dbquery = Database::exec("DELETE FROM `usb_devices` WHERE serial=:serial", array('serial' => $serial));
diff --git a/modules-available/usblockoff/config.json b/modules-available/usblockoff/config.json
index 59b54a80..f15ba11d 100644
--- a/modules-available/usblockoff/config.json
+++ b/modules-available/usblockoff/config.json
@@ -1,4 +1,4 @@
 {
 	"category":"main.beta",
-	"dependencies": ["bootstrap_switch", "bootstrap_dialog"]
+	"dependencies": ["bootstrap_switch", "bootstrap_dialog", "statistics"]
 }
diff --git a/modules-available/usblockoff/install.inc.php b/modules-available/usblockoff/install.inc.php
index 690f45d4..967771d1 100644
--- a/modules-available/usblockoff/install.inc.php
+++ b/modules-available/usblockoff/install.inc.php
@@ -1,7 +1,7 @@
 <?php
 
 $res = array();
-
+/*
 $t1 = $res[] = tableCreate('usb_devices', '
 	`uid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 	`id` varchar(50),
@@ -13,8 +13,25 @@ $t1 = $res[] = tableCreate('usb_devices', '
 	`ruleInformation` varchar(1024),
 	PRIMARY KEY (`uid`)
 ');
+*/
+
+$t1 = $res[] = tableCreate('usblockoff_hw', '
+	`hwid` INT(10) UNSIGNED NOT NULL ,
+	`serial` VARCHAR(128),
+	PRIMARY KEY (`hwid`, `serial`)
+');
+
+$t2 = $res[] = tableCreate('usblockoff_hw_prop', '
+	`hwid` INT(10) UNSIGNED NOT NULL ,
+	`serial` VARCHAR(128),
+	`prop` CHAR(16),
+	`value` VARCHAR(500),
+	PRIMARY KEY (`hwid`, `serial`, `prop`)
+');
 
-$t2 = $res[] = tableCreate('usb_configs', '
+// TODO: ADD CONSTRAINT
+
+$res[] = tableCreate('usb_configs', '
 	`configid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 	`configname` VARCHAR(200) NOT NULL,
 	`rulesconfig` BLOB,
@@ -23,6 +40,24 @@ $t2 = $res[] = tableCreate('usb_configs', '
 ');
 
 //$ret = Database::exec("DROP TABLE `usb_devices`");
+//$ret = Database::exec("DROP TABLE `usblockoff_hw`");
+//$ret = Database::exec("DROP TABLE `usblockoff_hw_prop`");
+
+if ($t1 === UPDATE_DONE || $t2 === UPDATE_DONE) {
+	$ret = Database::exec('ALTER TABLE `usblockoff_hw`
+		ADD CONSTRAINT `usblockoff_hw_ibfk_1` FOREIGN KEY (`hwid`) REFERENCES `statistic_hw` (`hwid`) ON DELETE CASCADE');
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Adding constraints to usblockoff_hw failed: ' . Database::lastError());
+	}
+
+	$ret = Database::exec('ALTER TABLE `usblockoff_hw_prop`
+		ADD CONSTRAINT `usblockoff_hw__prop_ibfk_1` FOREIGN KEY (`hwid`, `serial`) REFERENCES `usblockoff_hw` (`hwid`, `serial`) ON DELETE CASCADE');
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Adding constraints to usblockoff_hw_prop failed: ' . Database::lastError());
+	}
+	$res[] = UPDATE_DONE;
+}
+
 
 if (in_array(UPDATE_DONE, $res)) {
 	finalResponse(UPDATE_DONE, 'Table created successfully');
diff --git a/modules-available/usblockoff/page.inc.php b/modules-available/usblockoff/page.inc.php
index efb85fb1..fc805fe0 100644
--- a/modules-available/usblockoff/page.inc.php
+++ b/modules-available/usblockoff/page.inc.php
@@ -145,7 +145,7 @@ class Page_usblockoff extends Page
 	{
 
 		$form = array();
-		$rulesConf;
+		$rulesConf = null;
 
 		if ($id == 0) {
 			$currentdir = getcwd();
@@ -188,8 +188,9 @@ class Page_usblockoff extends Page
 	private function ajaxDeviceList()
 	{
 
-		$usbdevices = array();
-
+		//$usbdevices = array();
+		$usbdevices = $this->getUsbDeviceList();
+/*
 		$dbquery = Database::simpleQuery("SELECT * FROM `usb_devices`");
 		while ($entry = $dbquery->fetch(PDO::FETCH_ASSOC)) {
 			$locationquery = Database::queryFirst("SELECT l.locationname AS 'name', m.clientip AS 'ip' FROM machine AS m JOIN location AS l ON l.locationid=m.locationid
@@ -211,7 +212,7 @@ class Page_usblockoff extends Page
 			$device['via-port'] = $ruleInformation['via-port'];
 			$device['with-interface'] = $ruleInformation['with-interface'];
 			$usbdevices[] = $device;
-		}
+		}*/
 
 		$settings = array();
 		$setting = array();
@@ -255,4 +256,58 @@ class Page_usblockoff extends Page
 			'settings' => array_values($settings)
 		));
 	}
+
+	private function getUsbDeviceList() {
+		$usbdevices = array();
+
+		// TODO: Per USB Device 3 querys are executed.. better build a more complex sql query?
+
+		$dbquery = Database::simpleQuery("SELECT * FROM `usblockoff_hw`");
+		while ($entry = $dbquery->fetch(PDO::FETCH_ASSOC)) {
+
+			$device = array();
+
+			// Get all props from the hw table.
+			$dbquery2 = Database::simpleQuery("SELECT * FROM `statistic_hw_prop` WHERE hwid=:hwid", array(
+				'hwid' => $entry['hwid']
+			));
+
+			while ($prop = $dbquery2->fetch(PDO::FETCH_ASSOC)) {
+				$device[$prop['prop']] = $prop['value'];
+			}
+
+			// Get all props from the device table.
+			$dbquery3 = Database::simpleQuery("SELECT * FROM `usblockoff_hw_prop` WHERE hwid=:hwid AND serial=:serial", array(
+				'hwid' => $entry['hwid'],
+				'serial' => $entry['serial']
+			));
+
+			while ($prop = $dbquery3->fetch(PDO::FETCH_ASSOC)) {
+				$device[$prop['prop']] = $prop['value'];
+			}
+			if (!empty($device['machineuuid'])) {
+				$locationquery = Database::queryFirst("SELECT l.locationname AS 'name', m.clientip AS 'ip' FROM machine AS m JOIN location AS l ON l.locationid=m.locationid
+				 WHERE m.machineuuid=:machineuuid", array('machineuuid' => $entry['machineuuid']));
+				$device['clientip'] = $locationquery['ip'];
+				$device['location'] = $locationquery['name'];
+			}
+
+			//$device['uid'] = $entry['uid'];
+			$device['id'] = $device['vendorid'] . ":" . $device['productid'];
+			//$device['name'] = $entry['name'];
+			$device['serial'] = $entry['serial'];
+			//$device['machineuuid'] = $entry['machineuuid'];
+			//$device['user'] = $entry['user'];
+			$device['date'] = date('d.m.Y', $device['lastseen']);
+			$device['time'] = date('G:i', $device['lastseen']);
+			//$ruleInformation = json_decode($entry['ruleInformation'], true);
+			//$device['hash'] = $ruleInformation['hash'];
+			//$device['parent-hash'] = $ruleInformation['parent-hash'];
+			//$device['via-port'] = $ruleInformation['via-port'];
+			//$device['with-interface'] = $ruleInformation['with-interface'];
+			$usbdevices[] = $device;
+		}
+
+		return $usbdevices;
+	}
 }