xaostube/public/grid.php

<?php

$db = new SQLite3('xaos.sqlite3');

$exists = $db->querySingle("SELECT TRUE FROM pragma_table_info('links')");
if (!$exists) {
    $db->exec(<<<EOD
        CREATE TABLE links(
            id     INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            url    TEXT                              NOT NULL,
            audio  BOOLEAN    NOT NULL CHECK (audio IN (0, 1)),
            video  BOOLEAN    NOT NULL CHECK (video IN (0, 1)),
            ts     NUMBER,
            works  BOOLEAN    NOT NULL CHECK (works IN (0, 1))
        );
    EOD);
}

switch ($_SERVER['REQUEST_METHOD']) {
    case "POST":
        $data = json_decode(file_get_contents('php://input'));

        $url = $data->url;
        $audio = $data->audio;
        $video = $data->video;

        if ($url !== null && $audio !== null && $video !== null) {
            $stmt = $db->prepare('INSERT INTO links (url, audio, video, ts, works) VALUES (:url, :audio, :video, :ts, TRUE);');
            $stmt->bindValue(':url', $url);
            $stmt->bindValue(':audio', $audio);
            $stmt->bindValue(':video', $video);
            $stmt->bindValue(':ts', time());
            $result = $stmt->execute();
            if (!$result) {
                http_response_code(500);
                die();
            }
            $result = $db->querySingle("SELECT last_insert_rowid()");
            print_r($result);
        } else {
            http_response_code(400);
            die();
        }
        break;
    case "DELETE":
        $stmt = $db->prepare('UPDATE links SET works=0 WHERE id=:id');
        $stmt->bindValue(':id', $_GET["id"], SQLITE3_INTEGER);
        $stmt->execute();
        break;
    default:
        $result = [];

        $db_result = $db->query("SELECT * FROM links WHERE works != 0;");
        while ($row = $db_result->fetchArray(SQLITE3_ASSOC)) {
            $row["audio"] = $row["audio"] == 1;
            $row["video"] = $row["video"] == 1;
            array_push($result, $row);
        }

        header('Content-type: application/json; charset=utf-8');
        echo json_encode($result);
        break;
}