From e6a6ab8c1d8bc08c526374f4188103bba788fce4 Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Thu, 28 Jul 2022 10:35:42 +0200
Subject: add comment to keys

---
 html/db.php          |  9 +++++----
 html/maintenance.php | 30 ++++++++++--------------------
 2 files changed, 15 insertions(+), 24 deletions(-)

diff --git a/html/db.php b/html/db.php
index 7af33f9..f638286 100644
--- a/html/db.php
+++ b/html/db.php
@@ -25,9 +25,10 @@ class MyDB extends SQLite3 {
       die();
     return $result;
   }
-  function add_key() {
-    $stm = $this -> prepare('INSERT INTO keys (key) VALUES (:key)');
+  function add_key($comment) {
+    $stm = $this -> prepare('INSERT INTO keys (key) VALUES (:key, :comment)');
     $stm -> bindValue('key', bin2hex(random_bytes(64)));
+    $stm -> bindValue('comment', $comment);
     $stm -> execute();
     $key_id = $this -> lastInsertRowID();
     $stm -> close();
@@ -62,8 +63,8 @@ class MyDB extends SQLite3 {
     $stm -> close();
     return $result['value_id'];
   }
-  function add_key_for($machine, $state) {
-    $key_id = $this -> add_key();
+  function add_key_for($machine, $state, $comment) {
+    $key_id = $this -> add_key($comment);
     $value_id = $this -> get_value_id_of($machine, $state);
     $stm = $this -> prepare(
       'INSERT INTO permissions (key_id, value_id) VALUES (:key,:value)');
diff --git a/html/maintenance.php b/html/maintenance.php
index 422fcae..b849814 100644
--- a/html/maintenance.php
+++ b/html/maintenance.php
@@ -13,7 +13,7 @@ if (array_key_exists('drop',$_GET) && ($_GET['drop']=='DROP')) {
   $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 "keys" (id integer primary key AUTOINCREMENT, key varchar(128) not null unique, comment varchar(128) not null);');
   $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, 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)');
@@ -59,11 +59,11 @@ if (array_key_exists('machine',$_GET)) {
     }
     $stm -> close();
 
-    $db -> add_key_for($machine_id, $running_id);
+    $db -> add_key_for($machine_id, $running_id, 'self');
   }
 
-  if (array_key_exists('new_key', $_GET))
-    $db -> add_key_for($_GET['machine'], $_GET['new_key']);
+  if (array_key_exists('new_key', $_GET) && array_key_exists('comment', $_GET))
+    $db -> add_key_for($_GET['machine'], $_GET['new_key'], $_GET['comment']);
 
   $stm = $db -> prepare(
     'SELECT states.name, keys.key FROM machines' .
@@ -83,24 +83,14 @@ if (array_key_exists('machine',$_GET)) {
 
 if (array_key_exists('ddns', $_GET)) {
   $result = $db -> query(
-    'SELECT keys.id, 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' .
-    ' WHERE states.name = ' . "'" . 'running' . "'" .
-    ' GROUP BY keys.key' .
-    ' ORDER BY cnt DESC'
+    'SELECT keys.id FROM keys' .
+    ' WHERE keys.comment = ' . "'" . 'ddns' . "'";
   );
-  unset($key_id);
-  while ($row = $result -> fetchArray()) {
-    if ($row['cnt'] == 1)
-      break;
+  $row = $result -> fetchArray();
+  if (! $row)
+    $key_id = $db -> add_key('ddns');
+  else
     $key_id = $row['id'];
-    break;
-  }
-
-  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);
-- 
cgit v1.2.3-54-g00ecf