javascriptsqlitethunderbirdthunderbird-addon

Querying SQLite database in Thunderbird


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!


Solution

  • 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.