Tagesendstand

author: Erich Eckner <git@eckner.net> 2022-07-15 21:29:16 +0200
committer: Erich Eckner <git@eckner.net> 2022-07-15 21:29:16 +0200
commit: 8de8c576da92f6943e7ab5339b6286d3162b48d9 (patch)
tree: c3a897e964ca8683aa44dfce994eb6d9c4b4be5e
parent: 00c2358f7d8844942174199a132dab7804bdc48c (diff)
download: colocation-8de8c576da92f6943e7ab5339b6286d3162b48d9.tar.xz
4 files changed, 109 insertions, 64 deletions
diff --git a/html/db.php b/html/db.php
new file mode 100644
index 0000000..9f80d6b
--- /dev/null
+++ b/html/db.php
@@ -0,0 +1,34 @@
+<?php
+
+if (isset($db))
+  return;
+
+class MyDB extends SQLite3 {
+  function __construct() {
+    parent::__construct('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sqlite.db');
+  }
+  function exec($query) {
+    $result = parent::exec($query);
+    if (!$result)
+      die();
+    return $result;
+  }
+  function query($query) {
+    $result = parent::query($query);
+    if (!$result)
+      die();
+    return $result;
+  }
+  function prepare($query) {
+    $result = parent::prepare($query);
+    if (!$result)
+      die();
+    return $result;
+  }
+}
+
+$db = new MyDB();
+if(!$db) {
+  echo $db->lastErrorMsg();
+  die();
+}
diff --git a/html/index.php b/html/index.php
index e0158e1..38f4f3d 100644
--- a/html/index.php
+++ b/html/index.php
@@ -1,27 +1,42 @@
 <?php
 
-$db = new SQLite3('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sqlite.db');
+include "db.php";
 
-$columns = array('name', 'running', 'ping', 'power', 'last_update');
+$result = $db -> query(
+  'SELECT machines.id as machine, MIN(states.id) as min_state' .
+  ' FROM machines' .
+  ' JOIN "values" ON machines.id = "values".machine_id' .
+  ' JOIN states ON "values".state_id = states.id' .
+  ' WHERE "values".value IS NOT NULL' .
+  ' GROUP BY machines.id'
+);
 
-$result = $db -> query('SELECT machines.' . implode(', machines.', $columns) . ' FROM machines;');
+$result = $db -> query(
+//  'SELECT machines.name as machine, GROUP_CONCAT(states.name || \': \' || "values".value, \',\') as state, last_update' .
+  'SELECT machines.name as machine, GROUP_CONCAT(states.name || \': \' || IFNULL("values".value, \'NULL\'), \',\') as state, machines.last_update' .
+  ' FROM machines' .
+  ' JOIN "values" ON machines.id = "values".machine_id' .
+  ' JOIN states ON "values".state_id = states.id' .
+  ' GROUP BY machines.id'
+);
 
-echo '<html>' . "\n";
-echo '<body>' . "\n";
-echo '<table>' . "\n";
-echo '  <tr>' . "\n";
-foreach ($columns as $column) {
-  echo '    <th>' . $column . '</th>' . "\n";
-}
-echo '  </tr>' . "\n";
+?>
+<html><body><table>
+<tr>
+  <th>machine</th>
+  <th>state</th>
+  <th>last update</th>
+</tr>
+<?php
 
 while ($row = $result -> fetchArray()) {
-  echo '  <tr>' . "\n";
-  foreach ($columns as $column) {
-    echo '    <td>' . $row[$column] . '</td>' . "\n";
-  }
-  echo '  </tr>' . "\n";
+?>
+<tr>
+  <td><?php echo $row['machine']; ?></td>
+  <td><?php echo $row['state']; ?></td>
+  <td><?php echo $row['last_update']; ?></td>
+</tr>
+<?php
 }
-echo '</table>' . "\n";
-echo '</body>' . "\n";
-echo '</html>' . "\n";
+?>
+</table></body></html>
diff --git a/html/maintenance.php b/html/maintenance.php
index 9dd5806..6d36eb0 100644
--- a/html/maintenance.php
+++ b/html/maintenance.php
@@ -4,25 +4,27 @@ if ($_SERVER['REMOTE_ADDR'] !== $_SERVER['SERVER_ADDR']) {
   die();
 }
 
-$db = new SQLite3('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sqlite.db');
-
-$states = array('running', 'ping', 'power');
+include "db.php";
 
 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 "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('DROP TABLE IF EXISTS "machines"');
+  $db -> exec('DROP TABLE IF EXISTS "keys"');
+  $db -> exec('DROP TABLE IF EXISTS "permissions"');
+  $db -> exec('DROP TABLE IF EXISTS "states"');
+  $db -> exec('DROP TABLE IF EXISTS "values"');
+  $db -> exec('CREATE TABLE "machines" (id integer primary key AUTOINCREMENT, name varchar(32) not null unique, 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)');
+  $db -> exec('CREATE TABLE "permissions" (key_id integer not null, value_id integer not null)');
+  $db -> exec('CREATE TABLE "values" (id integer primary key AUTOINCREMENT, 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) {
+  if (! $stm)
+    die();
+  foreach (array('running', 'ping', 'power') as $state) {
     $stm -> bindValue('state', $state);
-    $stm -> execute();
+    if (! $stm -> execute())
+    die();
   }
   echo 'ok' . "\n";
   die();
@@ -50,24 +52,26 @@ if (array_key_exists('machine',$_GET)) {
     $stm -> close();
 
     $stm = $db -> prepare(
-      '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'
+      'SELECT states.name, states.id FROM states'
     );
     while ($row = $result -> fetchArray()) {
+      if ($row['name'] == 'running')
+        $running_id = $row['id'];
       $stm -> bindValue('state', $row['id']);
       $stm -> execute();
     }
     $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();
   }
 
   $stm = $db -> prepare(
diff --git a/html/update.php b/html/update.php
index ac8f471..9e9d39b 100644
--- a/html/update.php
+++ b/html/update.php
@@ -10,35 +10,27 @@ if (!array_key_exists('machine', $_GET)) {
   die();
 }
 
-$db = new SQLite3('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sqlite.db');
+include "db.php";
 
 $stm = $db -> prepare(
-  'SELECT permissions.name' .
-  ' FROM machines' .
-  ' JOIN granted_permissions ON granted_permissions.machine_id = machines.id' .
-  ' JOIN keys on granted_permissions.key_id = keys.id' .
-  ' JOIN permissions on granted_permissions.permission_id = permissions.id' .
-  ' WHERE keys.key=:key' .
-  ' AND machines.name=:machine');
+  'SELECT permissions.machine_id, permissions.state_id' .
+  ' FROM permissions' .
+  ' JOIN machines 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' .
+  ' AND states.name = :state' .
+  ' AND keys.key = :key'
+);
 $stm -> bindValue('key', $_GET['key']);
 $stm -> bindValue('machine', $_GET['machine']);
-$result = $stm -> execute();
 
-while($row = $result -> fetchArray()) {
-  if (!array_key_exists($row['name'], $_GET))
+foreach ($_GET as $state => $value) {
+  if (($state == 'key') || ($state == 'machine'))
     continue;
-
-  $value = $_GET[$row['name']];
-  if (($value != 0) && ($value != 1))
-    continue;
-
-  $updstm = $db -> prepare(
-    'UPDATE machines SET ' . $row['name'] . ' = :value WHERE machines.name=:machine'
-  );
-  $updstm -> bindValue('value', $value);
-  $updstm -> bindValue('machine', $_GET['machine']);
-  $updstm -> execute();
-  $updstm -> close();
+  $stm -> bindValue('state', $state);
+  $stm -> bindValue('value', $value);
+  $stm -> execute();
 }
 
-$stm -> close();
+echo 'ok' . "\n";
author	Erich Eckner <git@eckner.net>	2022-07-15 21:29:16 +0200
committer	Erich Eckner <git@eckner.net>	2022-07-15 21:29:16 +0200
commit	8de8c576da92f6943e7ab5339b6286d3162b48d9 (patch)
tree	c3a897e964ca8683aa44dfce994eb6d9c4b4be5e
parent	00c2358f7d8844942174199a132dab7804bdc48c (diff)
download	colocation-8de8c576da92f6943e7ab5339b6286d3162b48d9.tar.xz