Querying SQLite database in Thunderbird

Question

I want to make a small Add-on for Thunderbird that uses an SQLite database. I've created my database with "SQLite Manager". The problem is that when I access the database, I get the message that it is empty. But it isn't!

Database:

----------------------------
| table1 | table2 | table3 |
----------------------------
|  ...   |  ...   |  ...   |
|  ...   |  ...   |  ...   |
----------------------------

Here is the JavaScript code. It should return the name of tables, but I get 0.

  Components.utils.import("resource://gre/modules/Sqlite.jsm");
  Components.utils.import("resource://gre/modules/Task.jsm");

  Task.spawn(function() {
    let db;
    let result;
    try {

      db = yield Sqlite.openConnection({ path: "db.sqlite" });
      result = yield db.execute("SELECT name FROM sqlite_master WHERE type='table'");

      alert(result.length);

    } catch (ex) {
      alert("error: " + ex);
    } finally {
      if (db) yield db.close();
    }
  });

Can one tell me what I'm doing wrong?

Is it possible to import and then to read an already existing database in Thunderbird?

Thanks!

Is the database in the current profile's directory? If not, the db will be created there and of course be empty. — Todd, Dec 15 '15 at 23:06
You're absolutely right. I thought it is relatively of my JavaScript file. The correct path is: extensions/db@example.de/chrome/content/db.sqlite! Is it possible to use Chrome URL as absolute path? — ArthurA, Dec 18 '15 at 10:54

score 1 · Accepted Answer · answered Jan 19 '16 at 23:06

Looking at the source code for Sqlite.jsm, the path is always relative to the profile. You can however access the backstage pass of the module and copy/paste the code from openConnection. This is the object that holds the unexported symbols available in the module. Like this:

Components.utils.import("resource://gre/modules/FileUtils.jsm");
Components.utils.import("resource://gre/modules/Services.jsm");
var {classes: Cc, interfaces: Ci, utils: Cu} = Components;

function openConnection2(path) {
  let Sqlite = Components.utils.import("resource://gre/modules/Sqlite.jsm", {});
  let file = FileUtils.File(path);
  let identifier = Sqlite.getIdentifierByPath(path);
  let openedOptions = {};

  return new Promise((resolve, reject) => {
    let dbOptions = null;
    Services.storage.openAsyncDatabase(file, dbOptions, (status, connection) => {
      if (!connection) {

        reject(new Error(`Could not open connection to ${path}: ${status}`));
        return;
      }
      log.info("Connection opened");
      try {
        resolve(
          new Sqlite.OpenedConnection(connection.QueryInterface(Ci.mozIStorageAsyncConnection),
                                      identifier, openedOptions));
      } catch (ex) {
        reject(ex);
      }
    });
  });
}

You can pass your own path here, this should be the full path. You can access your database by converting the chrome uri to a file uri:

let uri = Services.io.newURI("chrome://myext/content/db.sqlite", null, null);
let registry = Cc['@mozilla.org/chrome/chrome-registry;1']
                .getService(Ci.nsIChromeRegistry);
let path = registry.convertChromeURL(uri)
                   .QueryInterface(Ci.nsIFileURL)
                   .file.path;

yield openConnection2(path); // in your task

Note that you will need to specify em:unpack in your install.rdf to ensure that the converted uri is actually a file uri and not a jar: uri.

Querying SQLite database in Thunderbird

1 Answers1