maintenance.php: values tabelle neu

1 files changed, 33 insertions, 18 deletions

diff --git a/html/maintenance.php b/html/maintenance.php
index b5cc742..9dd5806 100644
--- a/html/maintenance.php
+++ b/html/maintenance.php
@@ -9,17 +9,19 @@ $db = new SQLite3('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sql
 $states = array('running', 'ping', 'power');
 
 if (array_key_exists('drop',$_GET) && ($_GET['drop']=='DROP')) {
-  $db -> exec('DROP TABLE machines');
-  $db -> exec('DROP TABLE keys');
-  $db -> exec('DROP TABLE permissions');
-  $db -> exec('DROP TABLE granted_permissions');
-  $db -> exec('CREATE TABLE machines (id integer primary key AUTOINCREMENT, name varchar(32) not null unique, ' . implode(' int not null default 0, ', $states) . ' int not null default 0, last_update TIMESTAMP default CURRENT_TIMESTAMP);');
-  $db -> exec('CREATE TABLE keys (id integer primary key AUTOINCREMENT, key varchar(128) not null unique);');
-  $db -> exec('CREATE TABLE permissions (id integer primary key AUTOINCREMENT, name varchar(32) not null unique)');
-  $db -> exec('CREATE TABLE granted_permissions (key_id integer not null, machine_id integer not null, permission_id integer not null)');
-  $stm = $db -> prepare('INSERT INTO permissions (name) VALUES (:permission)');
-  foreach ($states as $permission) {
-    $stm -> bindValue('permission', $permission);
+  $db -> exec('DROP TABLE "machines"');
+  $db -> exec('DROP TABLE "keys"');
+  $db -> exec('DROP TABLE "permissions"');
+  $db -> exec('DROP TABLE "states"');
+  $db -> exec('DROP TABLE "values"');
+  $db -> exec('CREATE TABLE "machines" (id integer primary key AUTOINCREMENT, name varchar(32) not null unique, ' . implode(' int not null default 0, ', $states) . ' int not null default 0, last_update TIMESTAMP default CURRENT_TIMESTAMP);');
+  $db -> exec('CREATE TABLE "keys" (id integer primary key AUTOINCREMENT, key varchar(128) not null unique);');
+  $db -> exec('CREATE TABLE "states" (id integer primary key AUTOINCREMENT, name varchar(32) not null unique)');
+  $db -> exec('CREATE TABLE "permissions" (key_id integer not null, machine_id integer not null, state_id integer not null)');
+  $db -> exec('CREATE TABLE "values" (machine_id integer not null, state_id integer not null, value integer)');
+  $stm = $db -> prepare('INSERT INTO states (name) VALUES (:state)');
+  foreach ($states as $state) {
+    $stm -> bindValue('state', $state);
     $stm -> execute();
   }
   echo 'ok' . "\n";
@@ -48,23 +50,36 @@ if (array_key_exists('machine',$_GET)) {
     $stm -> close();
 
     $stm = $db -> prepare(
-      'INSERT INTO granted_permissions (key_id, machine_id, permission_id) VALUES (:key,:machine,1)');
+      'INSERT INTO permissions (key_id, machine_id, state_id) VALUES (:key,:machine,1)');
     $stm -> bindValue('machine', $machine_id);
     $stm -> bindValue('key', $key_id);
     $stm -> execute();
     $stm -> close();
+
+    $stm = $db -> prepare(
+      'INSERT INTO "values" (machine_id, state_id) VALUES (:machine, :state)'
+    );
+    $stm -> bindValue('machine', $machine_id);
+    $result = $db -> query(
+      'SELECT states.id FROM states'
+    );
+    while ($row = $result -> fetchArray()) {
+      $stm -> bindValue('state', $row['id']);
+      $stm -> execute();
+    }
+    $stm -> close();
   }
 
   $stm = $db -> prepare(
-    'SELECT permissions.name, keys.key FROM machines' .
-    ' JOIN granted_permissions ON granted_permissions.machine_id = machines.id' .
-    ' JOIN permissions ON granted_permissions.permission_id = permissions.id' .
-    ' JOIN keys ON granted_permissions.key_id = keys.id' .
+    'SELECT states.name, keys.key FROM machines' .
+    ' JOIN permissions ON permissions.machine_id = machines.id' .
+    ' JOIN states ON permissions.state_id = states.id' .
+    ' JOIN keys ON permissions.key_id = keys.id' .
     ' WHERE machines.name=:machine');
   $stm -> bindValue('machine', $_GET['machine']);
   $result = $stm -> execute();
-  while ($result = $result -> fetchArray()) {
-    echo 'permission = "' . $result['name'] . '", key = "' . $result['key'] . '"' . "\n";
+  while ($row = $result -> fetchArray()) {
+    echo 'permission = "' . $row['name'] . '", key = "' . $row['key'] . '"' . "\n";
   }
   die();
 }