datasette_write.html

{% extends "base.html" %}

{% block title %}{% if custom_title %}{{ custom_title }}{% else %}Write to {{ database_name }} with SQL{% endif %}{% endblock %}

{% block extra_head %}
<style>
#query-parameters-area {
    max-width: 600px; 
}
#query-parameters-area label {
    display: inline;
    max-width: 10em;
    font-weight: bold;
}
#query-parameters-area {
    display: grid;
    grid-template-columns: auto 1fr;
    grid-gap: 10px;
    align-items: start;
    margin-bottom: 10px;
}
#query-parameters-area input[type="text"],
#query-parameters-area textarea {
    width: 100%;
    box-sizing: border-box;
}
#query-parameters-area textarea {
    height: 20em;
}
.submit-container {
    padding-top: 1em;
}
.database-select label {
    display: inline;
}
</style>
{% endblock %}

{% block crumbs %}
{{ crumbs.nav(request=request, database=database_name) }}
{% endblock %}

{% block content %}
<h1>{% if custom_title %}{{ custom_title }}{% else %}Write to {{ database_name }} with SQL{% endif %}</h1>

<form class="write-form core" action="{{ request.path }}" method="post" style="margin-bottom: 1em">
    <input type="hidden" name="csrftoken" value="{{ csrftoken() }}">
    {% if custom_title %}<details style="margin-bottom: 1em"><summary>SQL query</summary>{% endif %}
        <p><textarea name="sql" style="box-sizing: border-box; width: 100%; padding-right: 10px; max-width: 600px; height: {{ sql_textarea_height }}em; padding: 6px;">{{ sql_from_args }}</textarea></p>
    {% if custom_title %}</details>{% endif %}
    <div class="query-parameters">
        <div id="query-parameters-area">
            {% for parameter in parameters %}
                <label for="qp_{{ parameter.name }}">{{ parameter.label }}</label>
                {% if parameter.type == "text" %}
                    <input type="text" id="qp_{{ parameter.name }}" name="qp_{{ parameter.name }}" value="{{ parameter.value }}">
                {% elif parameter.type == "hidden" %}
                    <input type="text" disabled id="qp_{{ parameter.name }}" name="qp_{{ parameter.name }}" value="{{ parameter.value }}">
                {% elif parameter.type == "textarea" %}
                    <textarea id="qp_{{ parameter.name }}" name="qp_{{ parameter.name }}">{{ parameter.value }}</textarea>
                {% endif %}
            {% endfor %}
        </div>
    </div>
    <div class="submit-container">
        <input type="hidden" name="_redirect_to" value="{{ redirect_to }}">
        <input type="submit" value="Execute query">
    </div>
</form>

{% if tables %}
    <p><strong>Tables</strong>:
        {% for table in tables %}
            <a href="{{ urls.table(database_name, table) }}">{{ table }}</a>{% if not loop.last %}, {% endif %}
        {% endfor %}
    </p>
{% endif %}

{% if views %}
    <p><strong>Views</strong>:
        {% for view in views %}
            <a href="{{ urls.table(database_name, view) }}">{{ view }}</a>{% if not loop.last %}, {% endif %}
        {% endfor %}
    </p>
{% endif %}

<script>
function buildQueryParametersHtml(parameters) {
    let htmlString = '';
    var qsParams = new URLSearchParams(location.search);
    parameters.forEach(param => {
        const value = escapeHtml(qsParams.get(param.name) || '');
        if (param.type === 'text') {
            htmlString += `
              <label for="qp_${param.name}">${param.label}</label>
              <input type="text" id="qp_${param.name}" name="qp_${param.name}" value="${value}">
            `;
        } else if (param.type === 'textarea') {
            htmlString += `
              <label for="qp_${param.name}">${param.label}</label>
              <textarea id="qp_${param.name}" name="qp_${param.name}">${value}</textarea>
            `;
        } else if (param.type === 'hidden') {
            htmlString += `
              <span></span>
              <input type="hidden" id="qp_${param.name}" name="qp_${param.name}" value="${value}">
            `;
        }
    });
    return htmlString;
}

function escapeHtml(str) {
    return str.replace(/[&<>"']/g, function(match) {
        switch (match) {
            case '&': return '&amp;';
            case '<': return '&lt;';
            case '>': return '&gt;';
            case '"': return '&quot;';
            case "'": return '&#39;';
            default: return match;
        }
    });
}

const sqlTextArea = document.querySelector('textarea[name="sql"]');
const queryParametersArea = document.getElementById('query-parameters-area');
let lastRequestTime = 0;
let timeoutId = null;

sqlTextArea.addEventListener('input', () => {
    const currentTime = Date.now();
    if (currentTime - lastRequestTime > 1000) {
        lastRequestTime = currentTime;
        updateForm(sqlTextArea.value);
    } else {
        clearTimeout(timeoutId);
        timeoutId = setTimeout(() => {
            lastRequestTime = Date.now();
            updateForm(sqlTextArea.value);
        }, 1000 - (currentTime - lastRequestTime));
    }
});

function updateForm(sqlQuery) {
    const apiUrl = `/-/write/derive-parameters?sql=${encodeURIComponent(sqlTextArea.value)}`;
    fetch(apiUrl)
        .then(response => response.json())
        .then(data => {
            if (data.parameters.length) {
                queryParametersArea.innerHTML = buildQueryParametersHtml(data.parameters);
            } else {
                queryParametersArea.innerHTML = '';
            }
        })
        .catch(error => console.error('Error fetching API:', error));
}
updateForm();


/* If there is a positive message-info, fade and shrink after 2s */
function fadeAndShrink(element) {
    if (element) {
        element.style.transition = 'opacity 0.2s, height 0.8s, padding-top 0.8s, padding-bottom 0.8s';
        element.style.opacity = '0';
        element.style.height = '0';
        element.style.paddingTop = '0';
        element.style.paddingBottom = '0';
        setTimeout(() => {
            element.remove();
        }, 800);
    }
}
const messageInfo = document.querySelector('.message-info');
if (messageInfo) {
    setTimeout(() => {
        fadeAndShrink(messageInfo);
    }, 2000);
}
</script>

{% endblock %}