Need help to convert some code to Sqlite.jsm

Add-ons Development
#1

This is for addon “CupidonMC”. I need to convert initDB(), which is in profil.js, because I now need to use Sqlite.jsm. Then, I also need to convert some functions that are doing SQL operations. Here is what I want to do:

  • Open SQLite connection and initialize any necessary table. I want to do this in a reusable “promise” function.
  • Sqlite.openConnection()
  • Test is some table exists
  • If table does not exist
    • Create table
  • else
    • Remove any old data in the table
  • Do any SQL request
  • Close connection

Here is what i tried to do:

initComments: function() {
  Components.utils.import("resource://gre/modules/Sqlite.jsm");

  return new Promise(function(resolve, reject) {
    let conn = yield Sqlite.openConnection({path: "cupidonmc.sqlite"});
    let tableExists = yield conn.tableExists("comments");

    if (!tableExists) {
      yield conn.execute("CREATE TABLE comments " +
        "(myName TEXT NOT NULL, targetId TEXT NOT NULL, " +
        "myComments TEXT, myType INTEGER, timestamp INTEGER NOT NULL, " +
        "PRIMARY KEY (myName, targetId))");
    }
    else {
      yield conn.execute("DELETE FROM comments WHERE timestamp <= :old_time",
        { old_time: (new Date()).getTime() - 91 * 24 * 3600 * 1000 });
    }

    resolve(conn);
  });
}

Actually, this function does not even create the SQLite file when it does not exist. I do not know what is wrong. Here is how I call this promise function:

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

Task.spawn(function* () {
  try {
    let conn = yield CupidonMC_profil.initComments();
    // Some operations with conn
  }
  finally {
    yield conn.close();
  }
});

Please, let me know what I am doing wrong. In Sqlite.jsm documentation, there is no example similar to this use case.

Thanks.

1 Like
#2

SQLite.jsm is some stuff I haven’t got a chance to get into yet, Im going to be watching topic closely :slight_smile:

#3

Because the documentation (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise) does not explicitly state that the executor given to the Promise constructor can be a generator function, I would suspect this being the Problem.

Try to either change the executor to a normal function, or simply use Task.spawn() instead of new Promise() to create the promise to return.

I am not a JavaScript expert, but I think your code also has a potential race condition in case initComments() is called more than one time:

  1. A executes conn.tableExists("comments"); (Result is false).
  2. Execution of A is paused by calling let tableExists = yield ...
  3. B executes conn.tableExists("comments"); (Result is false).
  4. Execution of B is paused by calling let tableExists = yield ...

Now both A and B think the table does not yet exist after they resume execution.

Simplest solution for your case would probably be to always make a CREATE TABLE IF NOT EXISTS ... and then the DELETE FROM ... call.

1 Like
#4

Hi lieser! Thank you for your help. I think I have something that works:

Task.spawn(function* () {
  let h = { conn: null };
  try {
    yield CupidonMC_profil.initComments(h);

    // Some operations with ** h.conn **
  }
  finally {
    yield h.conn.close();
  }
});

Then, I followed your instructions:

initComments: function(h) {
  Components.utils.import("resource://gre/modules/Sqlite.jsm");
  let conn = yield Sqlite.openConnection({path: "cupidonmc.sqlite"});

  yield conn.execute("CREATE TABLE IF NOT EXISTS comments " +
    "(myName TEXT NOT NULL, targetId TEXT NOT NULL, " +
    "myComments TEXT, myType INTEGER, timestamp INTEGER NOT NULL, " +
    "PRIMARY KEY (myName, targetId))");

  yield conn.execute("DELETE FROM comments WHERE timestamp <= :old_time",
    { old_time: (new Date()).getTime() - 91 * 24 * 3600 * 1000 });

  h.conn = conn;
}

I will continue to verify and test this code, but it seems to work properly.

Thanks again!

1 Like
#5

My best solution without using h:

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

Task.spawn(function* () {
  let conn = null;

  try {
    conn = yield Task.spawn(CupidonMC_profil.initComments);
    // Some operations with conn
  }
  finally {
    yield conn.close();
  }
});

Then, initComments() becomes an anonymous generator function:

initComments: function*() {
  Components.utils.import("resource://gre/modules/Sqlite.jsm");
  let conn = yield Sqlite.openConnection({path: "cupidonmc.sqlite"});

  if (!CupidonMC_profil.commentsReady) {
    yield conn.execute("CREATE TABLE IF NOT EXISTS ...");
    yield conn.execute("DELETE FROM comments WHERE ...", { ... });
    CupidonMC_profil.commentsReady = true;
  }

  return Promise.resolve(conn);
}
1 Like
#6

Are you sure the second Task.spawn() and Promise.resolve() are needed?
The following should also work:

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

Task.spawn(function* () {
  let conn = null;

  try {
    conn = yield CupidonMC_profil.initComments();
    // Some operations with conn
  }
  finally {
    yield conn.close();
  }
});

initComments: function*() {
  Components.utils.import("resource://gre/modules/Sqlite.jsm");
  let conn = yield Sqlite.openConnection({path: "cupidonmc.sqlite"});

  if (!CupidonMC_profil.commentsReady) {
    yield conn.execute("CREATE TABLE IF NOT EXISTS ...");
    yield conn.execute("DELETE FROM comments WHERE ...", { ... });
    CupidonMC_profil.commentsReady = true;
  }

  return conn;
}

Tested it with the following code in Scratchpad:

CupidonMC_profil = {};
Components.utils.import("resource://gre/modules/Task.jsm");

CupidonMC_profil.initComments = function*() {
  let myPromise = Promise.resolve("Value");
  let result = yield myPromise;
  return result;
}

Task.spawn(function* () {
  let conn = null;

  try {
    conn = yield CupidonMC_profil.initComments();
    // Some operations with conn
  }
  finally {
    console.log(conn)
  }
});
1 Like
#7

Hi lieser,

Yes, removing the second Task.spawn() and Promise.resolve() works! The last time I tried that solution, initComments was probably a function() instead of function*(). The * is important.

Thanks a lot!

1 Like