db/sql_preview

source < >

return function () {
    $self = url('/@object/@action/' . urlencode('db/sql_preview'));
    $preview = _class('sample_sql_preview', YF_PATH . '.dev/samples/classes/');
    $p = $preview->params($_GET);
    $fields = $preview->TABLES[$p['table']];

    $intro = '<p class="docs-demo-intro">Build a query with the query builder and see the SQL it renders for MySQL / MariaDB and for
        PostgreSQL side by side. Table, fields, operator, order and limit come from fixed lists; only the where value is free text, and it
        is escaped the way the framework expects: <code>db()->es_for_builder()</code> escapes it while
        <code>ESCAPE_BUILDER_VALUES</code> is off (the default, the builder only quotes), and the builder escapes it itself when the flag is
        on; the SQL is the same either way. Try a value like <code>O\'Reilly</code> or <code>a\\b</code>. The queries are only rendered
        with <code>->sql()</code> on database copies that have no connection, never run.
        Classes: <code>yf_db</code>, <code>yf_db_query_builder_mysql</code>, <code>yf_db_query_builder_pgsql</code>.</p>';

    $options = function (array $values, $selected, $empty = null) {
        $html = $empty !== null ? '<option value="">' . $empty . '</option>' : '';
        foreach ($values as $value => $label) {
            $html .= '<option value="' . _prepare_html((string) $value, 0) . '"' . ((string) $value === (string) $selected ? ' selected' : '') . '>' . _prepare_html((string) $label, 0) . '</option>';
        }
        return $html;
    };
    $tables = array_combine(array_keys($preview->TABLES), array_keys($preview->TABLES));
    $fields_kv = array_combine($fields, $fields);
    $checks = '';
    foreach ($fields as $f) {
        $checks .= '<label class="checkbox-inline form-check-inline"><input type="checkbox" class="form-check-input" name="fields[]" value="' . $f . '"'
            . (in_array($f, $p['fields'], true) ? ' checked' : '') . '> ' . $f . '</label> ';
    }
    $limits = [];
    foreach ($preview->LIMITS as $l) {
        $limits[$l] = $l ? $l : 'no limit';
    }
    $form = '<form method="get" action="' . $self . '" class="docs-demo-sql-form">'
        . '<div class="docs-demo-sql-row"><label>Table <select name="table" class="form-control form-select" onchange="this.form.submit()">' . $options($tables, $p['table']) . '</select></label>'
        . '<span class="docs-demo-sql-fields">Fields (none = *): ' . $checks . '</span></div>'
        . '<div class="docs-demo-sql-row"><label>Where <select name="where_field" class="form-control form-select">' . $options($fields_kv, $p['where_field'], '(no condition)') . '</select></label>'
        . '<select name="op" class="form-control form-select" aria-label="Operator">' . $options(array_combine($preview->OPS, $preview->OPS), $p['op']) . '</select>'
        . '<input type="text" name="value" class="form-control" maxlength="' . $preview->MAX_VALUE_LEN . '" placeholder="value" aria-label="Value" value="' . _prepare_html($p['value'], 0) . '"></div>'
        . '<div class="docs-demo-sql-row"><label>Order by <select name="order" class="form-control form-select">' . $options($fields_kv, $p['order'], '(none)') . '</select></label>'
        . '<select name="dir" class="form-control form-select" aria-label="Direction">' . $options(['asc' => 'ASC', 'desc' => 'DESC'], $p['dir']) . '</select>'
        . '<label>Limit <select name="limit" class="form-control form-select">' . $options($limits, $p['limit']) . '</select></label>'
        . '<label class="checkbox-inline form-check-inline"><input type="checkbox" class="form-check-input" name="escape_flag" value="1"' . ($p['escape_flag'] ? ' checked' : '') . '> ESCAPE_BUILDER_VALUES on</label>'
        . '<button type="submit" class="btn btn-primary">Show SQL</button></div></form>';

    $out = '<div class="row docs-demo-sql-out">';
    foreach (['mysql' => 'MySQL / MariaDB', 'pgsql' => 'PostgreSQL'] as $family => $label) {
        $out .= '<div class="col-md-6"><h4>' . $label . '</h4><pre class="docs-demo-sql"><code class="sql">' . _prepare_html($preview->sql($family, $p), 0) . '</code></pre></div>';
    }
    $out .= '</div>';
    $note = '';
    if ($p['where_field'] !== '' && ! $preview->where_applies($p)) {
        $note = '<div class="alert alert-warning">Values with a comma are not rendered here: <code>where()</code> splits its arguments at
            commas, so such a value belongs in <code>where_raw()</code> with <code>db()->escape_key()</code> and <code>escape_val()</code>.</div>';
    } elseif ($p['where_field'] !== '' && $p['value'] === '' && ! in_array($p['op'], ['is null', 'is not null'], true)) {
        $note = '<div class="alert alert-info">An empty value drops the condition: the builder leaves out <code>where()</code> calls whose value is empty.</div>';
    }
    css('.docs-demo-sql-form .docs-demo-sql-row { display:flex; flex-wrap:wrap; align-items:center; gap:8px; margin-bottom:8px; }
        .docs-demo-sql-form label { font-weight:normal; margin:0; }
        .docs-demo-sql-form .form-control { display:inline-block; width:auto; }
        .docs-demo-sql-form .docs-demo-sql-fields .checkbox-inline { margin:0 6px 0 0; padding-left:0; }
        .docs-demo-sql-form label input[type=checkbox] { float:none; margin:0 3px 0 0; position:static; vertical-align:middle; }
        pre.docs-demo-sql code { white-space:pre-wrap; word-break:break-word; }');
    return $intro . $form . '<h4>PHP</h4><pre class="docs-demo-sql"><code class="php">' . _prepare_html($preview->code($p), 0) . '</code></pre>' . $note . $out;
};