List Files | Just Commands | Repo | Logs

← back | Copy

<template>
  <BaseLayout title="SQL Query">
    <ion-grid :fixed="true">
      <ion-row>
        <ion-col>
          <h2>SQL Query Interface</h2>
          <p>Enter SQL queries to run against the local database:</p>
        </ion-col>
      </ion-row>

      <ion-row>
        <ion-col>
          <ion-textarea
            v-model="sqlQuery"
            placeholder="Enter your SQL query here..."
            :rows="6"
            fill="outline"
          ></ion-textarea>
        </ion-col>
      </ion-row>

      <ion-row>
        <ion-col>
          <ion-button @click="executeQuery" :disabled="!sqlQuery.trim()">
            Execute Query
          </ion-button>
          <ion-button fill="clear" @click="clearQuery">
            Clear
          </ion-button>
          <ion-button fill="outline" @click="showSchema">
            Show Schema
          </ion-button>
        </ion-col>
      </ion-row>

      <ion-row v-if="loading">
        <ion-col>
          <ion-spinner></ion-spinner>
          <p>Executing query...</p>
        </ion-col>
      </ion-row>

      <ion-row v-if="error">
        <ion-col>
          <ion-card color="danger">
            <ion-card-header>
              <ion-card-title>Error</ion-card-title>
            </ion-card-header>
            <ion-card-content>
              <pre>{{ error }}</pre>
            </ion-card-content>
          </ion-card>
        </ion-col>
      </ion-row>

      <ion-row v-if="results">
        <ion-col>
          <ion-card>
            <ion-card-header>
              <ion-card-title>Results</ion-card-title>
              <ion-card-subtitle v-if="results.values">
                {{ results.values.length }} row(s) returned
              </ion-card-subtitle>
            </ion-card-header>
            <ion-card-content>
              <div v-if="results.values && results.values.length > 0" class="results-table">
                <table>
                  <thead>
                    <tr>
                      <th v-for="column in Object.keys(results.values[0])" :key="column">
                        {{ column }}
                      </th>
                    </tr>
                  </thead>
                  <tbody>
                    <tr v-for="(row, index) in results.values" :key="index">
                      <td v-for="column in Object.keys(row)" :key="column">
                        {{ row[column] }}
                      </td>
                    </tr>
                  </tbody>
                </table>
              </div>
              <div v-else-if="results.changes !== undefined">
                <p>Query executed successfully. Changes: {{ results.changes }}</p>
              </div>
              <div v-else>
                <p>Query executed successfully with no results.</p>
              </div>
            </ion-card-content>
          </ion-card>
        </ion-col>
      </ion-row>
    </ion-grid>
  </BaseLayout>
</template>

<script setup lang="ts">
import { ref } from "vue";
import {
  IonGrid,
  IonRow,
  IonCol,
  IonTextarea,
  IonButton,
  IonCard,
  IonCardHeader,
  IonCardTitle,
  IonCardSubtitle,
  IonCardContent,
  IonSpinner,
} from "@ionic/vue";
import BaseLayout from "@/components/BaseLayout.vue";
import { getSQLite } from "@/dataAccess/getSQLite";

const sqlQuery = ref("");
const results = ref<any>(null);
const error = ref("");
const loading = ref(false);

const executeQuery = async () => {
  if (!sqlQuery.value.trim()) return;

  loading.value = true;
  error.value = "";
  results.value = null;

  try {
    const { dbConn } = await getSQLite();

    // Determine if this is a SELECT query or not
    const trimmedQuery = sqlQuery.value.trim().toLowerCase();

    if (trimmedQuery.startsWith('select')) {
      // Use query for SELECT statements
      const result = await dbConn.query(sqlQuery.value);
      results.value = result;
    } else {
      // Use execute for INSERT, UPDATE, DELETE, etc.
      const result = await dbConn.execute(sqlQuery.value);
      results.value = result;
    }
  } catch (err: any) {
    error.value = err.message || "An error occurred while executing the query";
  } finally {
    loading.value = false;
  }
};

const clearQuery = () => {
  sqlQuery.value = "";
  results.value = null;
  error.value = "";
};

const showSchema = async () => {
  loading.value = true;
  error.value = "";
  results.value = null;

  try {
    const { dbConn } = await getSQLite();

    // Get list of all tables
    const tablesResult = await dbConn.query(
      "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
    );

    if (!tablesResult.values || tablesResult.values.length === 0) {
      results.value = { values: [{ schema: "No tables found" }] };
      return;
    }

    // Build schema output
    const schemaLines: string[] = [];

    for (const tableRow of tablesResult.values) {
      const tableName = tableRow.name;
      schemaLines.push(`\n=== ${tableName} ===`);

      // Get table info
      const tableInfo = await dbConn.query(`PRAGMA table_info(${tableName})`);

      if (tableInfo.values) {
        for (const col of tableInfo.values) {
          const pk = col.pk ? " PRIMARY KEY" : "";
          const notNull = col.notnull ? " NOT NULL" : "";
          const defaultVal = col.dflt_value ? ` DEFAULT ${col.dflt_value}` : "";
          schemaLines.push(`  ${col.name} ${col.type}${pk}${notNull}${defaultVal}`);
        }
      }
    }

    results.value = {
      values: [{ schema: schemaLines.join("\n") }]
    };
  } catch (err: any) {
    error.value = err.message || "An error occurred while fetching schema";
  } finally {
    loading.value = false;
  }
};
</script>

<style scoped>
.results-table {
  overflow-x: auto;
  max-width: 100%;
}

.results-table table {
  width: 100%;
  border-collapse: collapse;
  font-family: monospace;
  font-size: 12px;
}

.results-table th,
.results-table td {
  border: 1px solid #ddd;
  padding: 8px;
  text-align: left;
}

.results-table th {
  background-color: #f5f5f5;
  font-weight: bold;
}

.results-table td {
  word-break: break-word;
  max-width: 200px;
  white-space: pre-wrap;
}

pre {
  white-space: pre-wrap;
  word-wrap: break-word;
}
</style>