lundi 2 mai 2016

Sequelize special query that involves "last inserted row id"

I have the following database structure (expressed in SQLite dialect):

CREATE TABLE `Clocks` (`_id` INTEGER PRIMARY KEY AUTOINCREMENT, `time` DATETIME);
CREATE TABLE `Operations`
(
    `_id` UUID NOT NULL PRIMARY KEY,
    `finished` TINYINT(1) NOT NULL DEFAULT 0,
    `launchedOn` BIGINT REFERENCES `Clocks` (`_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    `finishedOn` BIGINT REFERENCES `Clocks` (`_id`) ON DELETE SET NULL ON UPDATE CASCADE
);

Now what I would like to achieve in Sequelize.js looks like the following SQL Query in SQLite:

BEGIN TRANSACTION;
    INSERT INTO Clocks(time) VALUES (date('now'));
    INSERT INTO Operations(_id, finished, userId, launchedOn) VALUES ('00000000-0000-0000-0000-000000000001',  0, last_insert_rowid());
COMMIT;

and following:

BEGIN TRANSACTION;
    INSERT INTO Clocks(time) VALUES (date('now'));
    UPDATE Operations
    SET     finished = 1,
            finishedOn = last_insert_rowid()
    WHERE _id = '00000000-0000-0000-0000-000000000001';
COMMIT;

I've did some investigation with Sequelize.js, I've got idea on how to organize the above transactions but how to reference

Aucun commentaire:

Enregistrer un commentaire