From 85602fe9bc9cceee56ba5d80a772edc43dd3ad17 Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Thu, 28 Jul 2022 09:44:01 +0200
Subject: add ddns key to all new machines

---
 html/maintenance.php | 22 +++++++++++++++++-----
 1 file changed, 17 insertions(+), 5 deletions(-)

diff --git a/html/maintenance.php b/html/maintenance.php
index eac6d73..6b70fc5 100644
--- a/html/maintenance.php
+++ b/html/maintenance.php
@@ -79,7 +79,7 @@ if (array_key_exists('machine',$_GET)) {
 
 if (array_key_exists('ddns', $_GET)) {
   $result = $db -> query(
-    'SELECT keys.key, COUNT(1) as cnt FROM keys' .
+    'SELECT keys.id, keys.key, COUNT(1) as cnt FROM keys' .
     ' JOIN permissions ON permissions.key_id = keys.id' .
     ' JOIN "values" ON permissions.value_id = "values".id' .
     ' JOIN states ON "values".state_id = states.id' .
@@ -87,26 +87,38 @@ if (array_key_exists('ddns', $_GET)) {
     ' GROUP BY keys.key' .
     ' ORDER BY cnt DESC'
   );
+  unset($key_id);
   while ($row = $result -> fetchArray()) {
     if ($row['cnt'] == 1)
       break;
     echo $row['key'] . "\n";
-    die();
+    $key_id = $row['id'];
+    break;
   }
 
-  $key_id = $db -> add_key();
+  if (! isset($key_id))
+    $key_id = $db -> add_key();
 
   $stm = $db -> prepare('INSERT INTO permissions (key_id, value_id) VALUES (:key_id, :value_id)');
   $stm -> bindValue('key_id', $key_id);
-  $result = $db -> query(
+  $getstm = $db -> prepare(
     'SELECT "values".id as value_id FROM "values"' .
     ' JOIN states ON states.id = "values".state_id' .
-    ' WHERE states.name = ' . "'" . 'running' . "'"
+    ' WHERE states.name = :state' .
+    ' AND NOT EXISTS (' .
+      'SELECT 1 FROM permissions' .
+      ' WHERE permissions.value_id = "values".id' .
+      ' AND permissions.key_id = :key_id' .
+    ')'
   );
+  $getstm -> bindValue('state', 'running');
+  $getstm -> bindValue('key_id', $key_id);
+  $result = $getstm -> execute();
   while ($row = $result -> fetchArray()) {
     $stm -> bindValue('value_id', $row['value_id']);
     $stm -> execute();
   }
+  $getstm -> close();
   $stm -> close();
 
   $stm = $db -> prepare('SELECT keys.key from keys WHERE keys.id = :key_id');
-- 
cgit v1.2.3-54-g00ecf