Potential issue with SQLite triggers

[amatsagu]

No worries, somethings strange things happen, but I'm happy we could solve your problem 😅

Originally posted by @dyedgreen in https://github.com/dyedgreen/deno-sqlite/issues/170#issuecomment-1014887785

• I think I found potential issue... When I do just:

  const res = db.queryEntries("INSERT INTO Card (waifuId, ownerId, tier, power, health, stamina, luck, quality) VALUES (?, ?, ?, ?, ?, ?, ?, ?);", [78935, "490068558206992395", 1, 33, 240, 19, 19, 0]);
  console.log(res);

  Then it "works" - there are no errors and everything goes through but when I did it few times - it sometimes doesn't write new data... Like I got 3 new records on at least 7. Another issue I found is with try/catch blocks:

  try {
      const res = db.queryEntries("INSERT INTO Card (waifuId, ownerId, tier, power, health, stamina, luck, quality) VALUES (?, ?, ?, ?, ?, ?, ?, ?);", [78935, "490068558206992395", 1, 33, 240, 19, 19, 0]);
      console.log(res);
  }
  catch (err) {
      return $json({ message: `Query failure: ${err.message}` }, { statusCode: 422 });
  }

  ^ This code always throws error for some reason (which is very bad)...

[amatsagu]

My only idea is that maybe my main .db file got partially corrupted or something... But from other side I can still access everything through DB Browser 🤔

*UPDATE:

• It looks like triggers are main issue here. The reason why your code worked @dyedgreen was because you only created table and instantly inserted data. I'm not sure if it's because deno-sqlite has totally no support for triggers or there's bug in it or what. Trigger itself is definitely correct because I tried using it with sqlite3 cli tool, db browser & python std sqlite and in each place they worked fine.

Is there planned support for triggers? Or maybe it's there but like it appears - it's bugged?

[dyedgreen]

That’s interesting! Do you have a full example that reproduces the issue? (A database with ideally one table and trigger that causes the bug when trying to insert into it.)

[amatsagu]

That’s interesting! Do you have a full example that reproduces the issue? (A database with ideally one table and trigger that causes the bug when trying to insert into it.)

CREATE TRIGGER TR_burnedCounter
AFTER DELETE ON Card
BEGIN
UPDATE Waifu SET burned = burned + 1 WHERE id = old.waifuId;
UPDATE User SET usedSlots = usedSlots - 1 WHERE id = old.ownerId;
END;

CREATE TRIGGER TR_createdCounter
AFTER INSERT ON Card
BEGIN
UPDATE Waifu SET created = created + 1 WHERE id = new.waifuId;
INSERT INTO User (id, joinDate, usedSlots) VALUES (new.ownerId, strftime("%s","now"), 1) ON CONFLICT (id) DO UPDATE SET usedSlots = usedSlots + 1;
END;

About other tables:

CREATE TABLE Waifu (
    id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    animeIds TEXT NOT NULL,
    imgHash TEXT NOT NULL,
    created INT UNSIGNED NOT NULL DEFAULT 0,
    burned INT UNSIGNED NOT NULL DEFAULT 0,
    claimed INT UNSIGNED NOT NULL DEFAULT 0
);

CREATE TABLE User (
    id  VARCHAR(22) NOT NULL,
    joinDate    INT UNSIGNED NOT NULL,
    supporterTo INT UNSIGNED,
    sapphires   INT UNSIGNED NOT NULL DEFAULT 500,
    essence INT UNSIGNED NOT NULL DEFAULT 0,
    "keys"  INT UNSIGNED NOT NULL DEFAULT 0,
    chests  INT UNSIGNED NOT NULL DEFAULT 0,
    twigs   INT UNSIGNED NOT NULL DEFAULT 0,
    usedSlots   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    maxSlots    SMALLINT UNSIGNED NOT NULL DEFAULT 100,
    dailyStreak TINYINT UNSIGNED,
    voteStreak  TINYINT UNSIGNED,
    favoriteCardId  TEXT,
    lastDaily   INT UNSIGNED,
    lastVote    INT UNSIGNED,
    collectionPoints    INT UNSIGNED NOT NULL DEFAULT 0,
    lastActivity    INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY("id")
)

Here's example .db file where I dumped all the structures from my copy but without records themselves: https://drive.google.com/file/d/1PnXGZr5sTvjksxzXwMcS821QWYqdlkRU/view?usp=sharing

[amatsagu]

For now - I fixed it by completely removing all triggers and executing my sql code manually:

This works and it's kinda fast thanks to a single transaction but I still like triggers more :) Without automatic triggers I have to keep track of each statistics manually which can be a pain over time...

[dyedgreen]

I'm trying to make a minimal version that can reproduce your issue. But I'm struggling to reproduce the crash:

import { DB } from "https://deno.land/x/sqlite/mod.ts";

const db = new DB();

db.query(`
  CREATE TABLE fills (
    id         TEXT PRIMARY KEY,
    size       INTEGER NOT NULL,
    price      REAL NOT NULL,
    calculated BOOLEAN
  )
`);
db.query(`
  CREATE TABLE prices (
    id    INTEGER PRIMARY KEY,
    fill  TEXT UNIQUE NOT NULL REFERENCES fills (id),
    total REAL NOT NULL
  )
`);

db.query(`
  CREATE TRIGGER add_prices AFTER INSERT ON fills BEGIN
    UPDATE fills SET calculated = TRUE WHERE id = NEW.id;
    INSERT INTO prices (fill, total) VALUES (NEW.id, NEW.size * NEW.price);
  END
`);

db.query(
  "INSERT INTO fills (id, size, price) VALUES (:id, :size, :price)",
  {
    id: "490068558206992395",
    size: 42,
    price: 19.99,
  },
);

[dyedgreen]

Closing this, since I can't reproduce the issue locally