Statistiques auto-hébergement Paheko

Code source de ce fichier :

<?php
/**
 * Ce fichier contient le code source de la fonction "ping"
 * qui permet de suivre les versions installés de PHP et SQLite
 * des installations auto-hébergées.
 *
 * Aucune autre information n'est stockée que celles indiquées
 * dans ce code.
 */

// Initialisation de la base de données
$exists = file_exists('stats.sqlite');

$method = $_SERVER['REQUEST_METHOD'];

if (!$exists && $method == 'GET') {
    highlight_file(__FILE__);
    exit;
}

$open_mode = $method == 'GET' ? \SQLITE3_OPEN_READONLY : \SQLITE3_OPEN_READWRITE | \SQLITE3_OPEN_CREATE;
$db = new \SQLite3('stats.sqlite', $open_mode);
$db->busyTimeout(2*1000);
$db->exec('PRAGMA journal_mode = WAL;');

if (!$exists) {
    $db->exec('CREATE TABLE stats (
        id INTEGER NOT NULL PRIMARY KEY,
        hash TEXT NOT NULL,
        version INTEGER NOT NULL,
        sqlite INTEGER NOT NULL,
        php INTEGER NOT NULL,
        sqlite_options TEXT NOT NULL,
        created TEXT NOT NULL,
        updated TEXT NOT NULL
    );

    CREATE UNIQUE INDEX stats_hash ON stats (hash);');
}

function version_id(string $v): ?int
{
    $v = preg_replace('/(?:-|rc).*$/i', '', $v);
    $version = explode('.', $v);

    if (count($version) < 2) {
        return null;
    }

    return (intval($version[0]) * 10000 + intval($version[1]) * 100 + intval($version[2] ?? 0));
}

function version_number(int $id): string
{
    $a = floor($id / 10000);
    $b = floor(($id - $a * 10000) / 100);
    $c = $id - $a * 10000 - $b * 100;

    return $a . '.' . $b . '.' . $c;
}

function major_version(int $id): string
{
    $a = floor($id / 10000);
    $b = floor(($id - $a * 10000) / 100);

    return $a . '.' . $b;
}

// Gérer le stockage des données envoyées
if ($method == 'POST') {
    // Vérification que les infos nécessaires sont fournies
    if (!isset($_POST['version'], $_POST['id'], $_POST['sqlite'], $_POST['php'], $_POST['sqlite_options'])) {
        http_response_code(400);
        exit;
    }

    $data = [
        'id'             => $_POST['id'],
        'version'        => version_id($_POST['version']),
        'sqlite'         => version_id($_POST['sqlite']),
        'php'            => version_id($_POST['php']),
        'sqlite_options' => $_POST['sqlite_options'],
    ];

    // restrict string length
    $data = array_map(fn($a) => substr($a, 0, 200), $data);

    $data[5] = json_encode(explode(',', $data[5]));

    // Stockage des données dans la base de données
    $st = $db->prepare('INSERT INTO stats (hash, version, sqlite, php, sqlite_options, updated, created)
        VALUES (:id, :version, :sqlite, :php, :sqlite_options, datetime(), datetime())
        ON CONFLICT (hash) DO UPDATE SET
            version = :version,
            sqlite = :sqlite,
            php = :php,
            sqlite_options = :sqlite_options,
            updated = datetime();');

    foreach ($data as $key => $value) {
        $st->bindValue($key, $value);
    }

    $st->execute();

    $db->close();

    http_response_code(204);
    exit;
}

$db->createFunction('version_id', 'version_id');
$db->createFunction('version_number', 'version_number');
$db->createFunction('major_version', 'major_version');

$json_clause = '(sqlite_options LIKE \'%JSON1%\' OR (sqlite >= 33800 AND sqlite_options NOT LIKE \'OMIT_JSON\'))';

// Afficher le code de ce fichier et les stats
$reports = [
    'Nombre d\'installations actives (12 mois)' => 'SELECT COUNT(*) FROM stats WHERE created >= datetime(\'now\', \'-1 year\') OR updated >= datetime(\'now\', \'-1 year\');',
    'Nombre d\'installations totales' => 'SELECT COUNT(*) FROM stats;',
    //'Nombre de nouvelles installations (12 mois)' => 'SELECT COUNT(*) FROM stats WHERE created >= datetime(\'now\', \'-1 year\');',
    'Versions Paheko les plus courantes (12 mois)' => 'SELECT GROUP_CONCAT(a, \', \') FROM (SELECT version_number(version) AS a, COUNT(*) AS b FROM stats WHERE @LAST_YEAR GROUP BY version ORDER BY b DESC);',
    'Part de Paheko 1.1 (total)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE version >= 10100 AND version < 10200) / @TOTAL, 3)*100) || \' %\';',
    'Part de Paheko 1.2 (total)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE version >= 10200 AND version < 10300) / @TOTAL, 3)*100) || \' %\';',
    'Part de Paheko 1.3 (total)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE version >= 10300 AND version < 10400) / @TOTAL, 3)*100) || \' %\';',
    'Part de Paheko 1.4 (total)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE version >= 10400 AND version < 10500) / @TOTAL, 3)*100) || \' %\';',
    'Part de Paheko 1.2 (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE version >= 10200 AND version < 10300 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de Paheko 1.3 (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE version >= 10300 AND version < 10400 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de PHP 7.4 (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE php >= 70400 AND php < 80000 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de PHP 8.0+ (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE php >= 80000 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de PHP 8.0+ (6 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE php >= 80000 AND updated >= datetime(\'now\', \'-6 month\')) / (SELECT COUNT(*) FROM stats WHERE updated >= datetime(\'now\', \'-6 month\')), 3)*100) || \' %\';',
    'Part de PHP 8.1+ (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE php >= 80100 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de PHP 8.2+ (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE php >= 80200 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de PHP 8.3+ (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE php >= 80300 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de PHP 8.4+ (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE php >= 80400 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Versions SQLite les plus courantes (12 mois)' => 'SELECT GROUP_CONCAT(a, \', \') FROM (SELECT major_version(sqlite) AS a, COUNT(*) AS b FROM stats WHERE @LAST_YEAR GROUP BY major_version(sqlite) ORDER BY b DESC);',
    'Part de SQLite < 3.27 (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE sqlite < 32700 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de SQLite >= 3.27 (Debian Buster) (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE sqlite >= 32700 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de SQLite >= 3.34 (Debian Bullseye)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE sqlite >= 33400 AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de SQLite >= 3.25 + JSON1 + FTS4 (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE @LAST_YEAR AND sqlite >= 32500 AND ' . $json_clause . ' AND (sqlite_options LIKE \'%FTS4%\' OR sqlite_options LIKE \'%FTS3%\')) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Options SQLite les plus courantes (12 mois)' => 'SELECT GROUP_CONCAT(a, \', \') FROM (SELECT value AS a, COUNT(*) AS b FROM stats, json_each(sqlite_options) WHERE @LAST_YEAR GROUP BY value ORDER BY b DESC);',
    'Part de support de JSON1 (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE @LAST_YEAR AND ' . $json_clause . ') / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de support de FTS4 (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE @LAST_YEAR AND (sqlite_options LIKE \'%FTS4%\' OR sqlite_options LIKE \'%FTS3%\')) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de support de FTS5 (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE sqlite_options LIKE \'%FTS5%\' AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
    'Part de support de UPDATE_DELETE_LIMIT (12 mois)' => 'SELECT (ROUND(1.0*(SELECT COUNT(*) FROM stats WHERE sqlite_options LIKE \'%UPDATE_DELETE_LIMIT%\' AND @LAST_YEAR) / @TOTAL_LAST_YEAR, 3)*100) || \' %\';',
];

function graph(string $title, string $where)
{
    global $db;
    printf('<figure class="graph"><div>');

    $max = $db->querySingle('SELECT COUNT(*) FROM stats GROUP BY strftime(\'%Y%m\', updated) ORDER BY COUNT(*) DESC LIMIT 1');
    $res = $db->query('SELECT COUNT(*) AS total, updated FROM stats WHERE '. $where . ' GROUP BY strftime(\'%Y%m\', updated) ORDER BY updated;');

    while ($row = $res->fetchArray(\SQLITE3_ASSOC)) {
        $row = (object) $row;
        printf('<span style="height: %d%%;"><i>%s</i></span>', round(($row->total / $max) * 100), substr($row->updated, 0, 7));
    }

    printf('</div><figcaption>%s</figcaption></figure>', htmlspecialchars($title));
}

echo '<html><head><title>Statistiques</title>
<style type="text/css">
body {
    font-family: sans-serif;
}
figcaption {
    text-align: center;
    font-size: .9em;
    padding: .5em;
}
.graph {
    display: inline-flex;
    flex-direction: column;
}
.graph div {
    display: flex;
    align-items: flex-end;
    height: 100px;
}
div span {
    display: block;
    width: 7px;
    position: relative;
    background: darkorange;
}
div span i {
    display: none;
}
</style>
</head>
<body>
<h2>Statistiques auto-hébergement Paheko</h2>';

graph('Installations actives', '1');
graph('Version 1.2', 'version >= 10200 AND version < 10300');
graph('Version 1.3', 'version >= 10300 AND version < 10400');
graph('PHP 7.4', 'php >= 70400 AND php < 80000');
graph('PHP 8.0+', 'php >= 80000');
graph('PHP 8.1+', 'php >= 80100');

echo '
<table cellpadding=5 border=1 style="font-size: 1.3em">';

foreach ($reports as $label => $sql) {
    $sql = str_replace('@TOTAL_LAST_YEAR', '(SELECT COUNT (*) FROM stats WHERE @LAST_YEAR)', $sql);
    $sql = str_replace('@TOTAL', '(SELECT COUNT (*) FROM stats)', $sql);
    $sql = str_replace('@LAST_YEAR', 'updated >= datetime(\'now\', \'-1 year\')', $sql);
    printf('<tr><th style="text-align: left; width: 18em">%s</th><td>%s</td></tr>', $label, $db->querySingle($sql));
}

$db->close();

echo '</table><hr /><h2>Code source de ce fichier :</h2>';
highlight_file(__FILE__);