SQLite 3 in azione: come aprire un DataBase Locale

Continuo con la mia esplorazione di SQLite 3 per browser. Ovvero la versione JavaScript che permette di usare il codice compilato in WebAssembly. Qualche giorno fa ho pubblicato i miei appunti su come creare un database, aggiungere tabelle e dati, e come estrarre dati da una tabella. Oggi riporto i miei appunti su come leggere un database salvato in locale, come ricavare l’elenco delle tabelle presenti e, infine, come capire quali sono le colonne di una tabella.

Come aprire un database

Per aprire un database mi serve un modo per passarlo alla pagina web. Il modo più semplice è farlo utilizzando un elemento input con type="file".

<input type="file" bind:files />

In questo modo, quando l’utente seleziona un file, il suo contenuto viene salvato in una variabile files che è un array di File (vedi File). Il primo elemento di questo array è il file selezionato.

$: if (files) {
  const file = files[0];
  console.log(file.name, file.size);
}

Però mi serve una funzione per leggere il contenuto del file. Creo una funzione specifica, readDatabase:

const readDatabase = (file) => {
    const r = new FileReader();
    r.addEventListener("load", function () {
      const arrayBuffer = this.result as ArrayBuffer;
      let bytes = new Uint8Array(arrayBuffer);
      const p = sqlite3.wasm.allocFromTypedArray(bytes);
      const db = new sqlite3.oo1.DB();
      let rc = sqlite3.capi.sqlite3_deserialize(
        db.pointer,
        "main",
        p,
        bytes.length,
        bytes.length,
        0
      );
      const dbName = file.name;
      return db;
    });
    r.readAsArrayBuffer(file);
  };

Andando a scomporre il codice, mi serve un evento che intercetti il file e lo legga come fosse un ArrayBuffer:

const readAsArrayBuffer = (file) => {
  return new Promise((resolve, reject) => {
    const r = new FileReader();
    r.addEventListener("load", () => resolve(r.result as ArrayBuffer));
    r.addEventListener("error", reject);
    r.readAsArrayBuffer(file);
  });
};

Dopo che ho letto il file lo converto in un array di byte:

const arrayBufferToBytes = (arrayBuffer) => {
  return new Uint8Array(arrayBuffer);
};

In questo modo posso usare il metodo sqlite3_deserialize

const deserialize = (db, bytes) => {
  const p = sqlite3.wasm.allocFromTypedArray(bytes);
  let rc = sqlite3.capi.sqlite3_deserialize(
    db.pointer,
    "main",
    p,
    bytes.length,
    bytes.length,
    sqlite3.capi.SQLITE_DESERIALIZE_FREEONCLOSE |
      sqlite3.capi.SQLITE_DESERIALIZE_RESIZEABLE
  );
  return db;
};

Quindi mettendo insieme i pezzi posso scrivere la funzione readDatabase:

const readAsArrayBuffer = (file) => {
  return new Promise((resolve, reject) => {
    const r = new FileReader();
    r.addEventListener("load", () => resolve(r.result as ArrayBuffer));
    r.addEventListener("error", reject);
    r.readAsArrayBuffer(file);
  });
};

const readDatabase = (arrayBuffer) => {
  const sqlite3 = self["sqlite3"];
  let bytes = new Uint8Array(arrayBuffer);
  const p = sqlite3.wasm.allocFromTypedArray(bytes);
  const db = new sqlite3.oo1.DB();
  let rc = sqlite3.capi.sqlite3_deserialize(
    db.pointer,
    "main",
    p,
    bytes.length,
    bytes.length,
    sqlite3.capi.SQLITE_DESERIALIZE_FREEONCLOSE |
      sqlite3.capi.SQLITE_DESERIALIZE_RESIZEABLE
  );
  return db;
};

let db;
let dbName;

$: if (files) {
  readAsArrayBuffer(files[0]).then((r) => {
    db = readDatabase(r);
    dbName = files[0].name;
    files = null;
    getTableList();
  }).catch((e) => {
    console.log(e);
  });
}

Estrarre l’elenco delle tabelle

Adesso che ho aperto il database, posso estrarre l’elenco delle tabelle. Ci sono due modi possibili. Il primo, quello che ho utilizzato, è quello di interrogare la tabella sqlite_master oppure la tabella sqlite_schema:

const getTableList = (db) => {
  let listTable = [];
  db.exec({
    sql: `select name from sqlite_master where type='table'`,
    rowMode: "array", // 'array' (default), 'object', or 'stmt'
    callback: function (row) {
      console.log("row ", ++this.counter, "=", row);
      listTable = [...listTable, row[0]];
    }.bind({ counter: 0 }),
  });
  return listTable;
};

Ho già spiegato come usare exec nell’altro post. Semplifico il codice eliminando il callback e utilizzando returnValue. Aggiungo quindi il metodo flat per convertire l’array di array in un array semplice:

const getTableList = (db) =>
  db
    .exec({
      sql: `select name from sqlite_master where type='table'`,
      returnValue: "resultRows",
    })
    .flat();

Estrarre i nomi delle colonne di una tabella

Il secondo modo per ricavare l’elenco dei nomi delle tabelle di un database prevede l’utilizzo dei PRAGMA Statements. Non sono altro che delle aggiunte alla sintassi SQL che permettono di interrogare il database. Per esempio, il comando PRAGMA table_list restituisce l’elenco delle tabelle contenute nel database. Invece il comando PRAGMA table_info(table_name) restituisce le informazioni sulle colonne di una tabella. In questo modo posso estrarre l’elenco dei nomi delle colonne di una tabella:

let idInfo = 0;

const getTableInfo = (tableName) => {
  let infoTable = [];
  idInfo++;
  db.exec({
    sql: `PRAGMA table_info("${tableName}")`,
    rowMode: "object",
    callback: function (row) {
      console.log("row ", ++this.counter, "=", JSON.stringify(row));
      infoTable = [...infoTable, row];
    }.bind({ counter: 0 }),
  });
  tableInfo = [...tableInfo, { infoTable, tableName, idInfo }];
};

Anche in questo caso, semplifico il codice eliminando il callback e utilizzando returnValue:

const getTableColumns = (db, tableName) => [
  {
    tableName,
    id: Math.random(),
    columns: db.exec({
      sql: `PRAGMA table_info("${tableName}")`,
      rowMode: "object",
      returnValue: "resultRows",
    }),
  },
];

E con questo per oggi è tutto.