new functions

2 files changed, 40 insertions, 8 deletions

diff --git a/html/db.php b/html/db.php
index b10f679..cca6d74 100644
--- a/html/db.php
+++ b/html/db.php
@@ -33,6 +33,45 @@ class MyDB extends SQLite3 {
     $stm -> close();
     return $key_id;
   }
+  function get_value_id_of($machine, $state) {
+    if (is_int($machine) && is_int($state)) {
+      $stm = $db -> prepare(
+        'SELECT "values".id as value_id FROM "values"' .
+        ' WHERE "values".state_id = :state_id' .
+        ' AND "values".machine_id = :machine_id'
+      );
+      $stm -> bindValue('state_id', $state);
+      $stm -> bindValue('machine_id', $machine);
+    } else {
+      $stm = $db -> prepare(
+        'SELECT "values".id as value_id FROM "values"' .
+        ' JOIN states ON states.id = "values".state_id' .
+        ' JOIN machines ON machines.id = "values".machine_id' .
+        ' WHERE states.name = :state' .
+        ' AND machines.name = :machine'
+      );
+      $stm -> bindValue('state', $state);
+      $stm -> bindValue('machine', $machine);
+    }
+    $result = $stm -> execute();
+    if (!$result)
+      die();
+    $result = $result -> fetchArray();
+    if (!$result)
+      die();
+    $stm -> close();
+    return $result['value_id'];
+  }
+  function add_key_for($machine, $state) {
+    $key_id = add_key();
+    $value_id = get_value_id_of($machine, $state);
+    $stm = $db -> prepare(
+      'INSERT INTO permissions (key_id, value_id) VALUES (:key,:value)');
+    $stm -> bindValue('value', $value_id);
+    $stm -> bindValue('key', $key_id);
+    $stm -> execute();
+    $stm -> close();
+  }
 }
 
 $db = new MyDB();
diff --git a/html/maintenance.php b/html/maintenance.php
index 8e32090..8c64713 100644
--- a/html/maintenance.php
+++ b/html/maintenance.php
@@ -44,8 +44,6 @@ if (array_key_exists('machine',$_GET)) {
     $machine_id = $db -> lastInsertRowID();
     $stm -> close();
 
-    $key_id = $db -> add_key();
-
     $stm = $db -> prepare(
       'INSERT INTO "values" (machine_id, state_id) VALUES (:machine, :state)'
     );
@@ -61,12 +59,7 @@ if (array_key_exists('machine',$_GET)) {
     }
     $stm -> close();
 
-    $stm = $db -> prepare(
-      'INSERT INTO permissions (key_id, value_id) VALUES (:key,:value)');
-    $stm -> bindValue('value', $running_id);
-    $stm -> bindValue('key', $key_id);
-    $stm -> execute();
-    $stm -> close();
+    $db -> add_key_for($machine_id, $running_id);
   }
 
   $stm = $db -> prepare(