From 0e2689378bd444e8788136e474cd5d6fc15da00e Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Fri, 15 Jul 2022 15:46:32 +0200 Subject: update.php neu, mehrere Spalten --- html/index.php | 4 ++-- html/maintenance.php | 8 +++++--- html/update.php | 44 ++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 51 insertions(+), 5 deletions(-) create mode 100644 html/update.php diff --git a/html/index.php b/html/index.php index 5021124..e0158e1 100644 --- a/html/index.php +++ b/html/index.php @@ -2,9 +2,9 @@ $db = new SQLite3('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sqlite.db'); -$columns = array('name', 'online', 'last_update'); +$columns = array('name', 'running', 'ping', 'power', 'last_update'); -$result = $db -> query('SELECT machines.name,machines.online,machines.last_update FROM machines;'); +$result = $db -> query('SELECT machines.' . implode(', machines.', $columns) . ' FROM machines;'); echo '' . "\n"; echo '' . "\n"; diff --git a/html/maintenance.php b/html/maintenance.php index 3eaf640..b5cc742 100644 --- a/html/maintenance.php +++ b/html/maintenance.php @@ -6,17 +6,19 @@ if ($_SERVER['REMOTE_ADDR'] !== $_SERVER['SERVER_ADDR']) { $db = new SQLite3('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sqlite.db'); +$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, online int not null default 0, last_update TIMESTAMP default CURRENT_TIMESTAMP);'); + $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 (array('running', 'ping', 'power') as $permission) { + foreach ($states as $permission) { $stm -> bindValue('permission', $permission); $stm -> execute(); } @@ -33,7 +35,7 @@ if (array_key_exists('machine',$_GET)) { if ($result[0] == 0) { $key = bin2hex(random_bytes(64)); - $stm = $db -> prepare('INSERT INTO machines (name,online) VALUES (:machine,0)'); + $stm = $db -> prepare('INSERT INTO machines (name) VALUES (:machine)'); $stm -> bindValue('machine', $_GET['machine']); $stm -> execute(); $machine_id = $db -> lastInsertRowID(); diff --git a/html/update.php b/html/update.php new file mode 100644 index 0000000..ac8f471 --- /dev/null +++ b/html/update.php @@ -0,0 +1,44 @@ + 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'); +$stm -> bindValue('key', $_GET['key']); +$stm -> bindValue('machine', $_GET['machine']); +$result = $stm -> execute(); + +while($row = $result -> fetchArray()) { + if (!array_key_exists($row['name'], $_GET)) + 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 -> close(); -- cgit v1.2.3-54-g00ecf