How does cascade work?

[jghauser]

First of all, thanks a lot for this plugin!

I'm in the process of writing a neovim plugin using sqlite.lua and I'm unsure how to get on_delete = "cascade" to work.
I've created the below minimal example:

local tbl = require("sqlite.tbl") --- for constructing sql tables
local db = require("sqlite.db") --- for constructing sql databases

-- the metadata table
local tbl_metadata = tbl("metadata", {
	id = true,
	entry = {
		type = "integer",
		unique = true,
		reference = "data.id",
		on_update = "cascade",
		on_delete = "cascade",
	},
})

-- the data table
local tbl_data = tbl("data", {
	id = true,
	test = { "text", required = true, unique = true },
})

local tbls = {
	uri = "~/test.sqlite3",
	data = tbl_data,
	metadata = tbl_metadata,
}

local data = db(tbls)

data.data:remove() -- to ensure tbl is empty
data.metadata:remove() -- to ensure tbl is empty
data.data:insert({ test = "test" })
data.metadata:insert({ entry = 1 })
data.data:remove({ id = 1 })

If I understand things correctly, this should create a data and metadata row (which it does, I checked), and it should then delete the data row and (because 'cascade') also delete the metadata row. For me, the metadata row is still left however.

Am I doing something wrong? I'm very much a noob when it comes to SQL so maybe that's where I go wrong. I would be really thankful for any pointers. :)

[kkharji]

Hey @jghauser , Sorry for the later replay. at first glance, everything looked okay, but after testing the code, I couldn't understand why it didn't work 😆. Luckily after reviewing the tests files on cascading, it turns you are missing pk key in id.

Try data.id = { type = "integer", pk = true }. pk enable a field to be treated as a primary key.

Edit: referenced key can not have unique = true as in data.entry.unique

[kkharji]

Oh, I see, no you did a great job this time 🤣. Thanks

-- First Run
-- "data": { { id = 1, test = "test" } }
-- "metadata": { { entry = 1, id = 1 } }

-- Second Run
-- "data": {}
-- "metadata": { { entry = 1, id = 1 } }

This issue is entry key can not be unique in the same table, you can have many rows referencing the same key

So after removing unique key from entry key in the metadata scheme. It returned empty data and metadata.

local sqlite = require "sqlite"

local db = sqlite {
  uri = "~/test.sqlite3",
}

-- the metadata table
local metadata = db:tbl("metadata", {
  id = { "integer", pk = true },
  entry = {
    type = "integer",
    reference = "data.id",
    on_update = "cascade",
    on_delete = "cascade",
  },
})

-- the data table
local data = db:tbl("data", {
  id = { "integer", pk = true },
  test = { "text", required = true, unique = true },
})

data:insert { test = "test" }
metadata:insert { entry = 1 }
data:remove { id = 1 }

vim.pretty_print {"data", data:get() }
vim.pretty_print {"metadata", metadata:get() }

metadata:remove()
data:remove()

With the suggestion I made in the last comment

[jghauser]

Thanks a lot for your help, I'll try this out!

[jghauser]

I've played around with your script and still come across the problem I had before. Basically, I see a different behaviour depending on whether I remove the data row in the same run as when the data is inserted or whether I do it in a second run.

If I run the below file, everything works as expected (both data and metadata are empty).

local sqlite = require("sqlite")

local db = sqlite({
	uri = "~/test.sqlite3",
})

-- the metadata table
local metadata = db:tbl("metadata", {
	id = { "integer", pk = true },
	entry = {
		type = "integer",
		reference = "data.id",
		on_update = "cascade",
		on_delete = "cascade",
	},
})

-- the data table
local data = db:tbl("data", {
	id = { "integer", pk = true },
	test = { "text", required = true, unique = true },
})

data:insert({ test = "test" })
metadata:insert({ entry = 1 })
data:remove({ id = 1 })

vim.pretty_print({ "data", data:get() })
vim.pretty_print({ "metadata", metadata:get() })

I get this (expected) output:

{ "data", {} }
{ "metadata", {} }

But if I run the insert and remove commands in different runs of the file, I get a different behaviour. The data row is deleted and the metadata row remains.

Here are the steps I did:

1. I delete the sqlite file to make sure we start from a clean slate.
2. I execute the above file but with the following changed lines (so I only insert data at this step):

data:insert({ test = "test" })
metadata:insert({ entry = 1 })
-- data:remove({ id = 1 })

I get the following (expected) output:

{ "data", { {
      id = 1,
      test = "test"
    } } }
{ "metadata", { {
      entry = 1,
      id = 1
    } } }

3. I change the file as follows, save it, and execute it (so I only remove at this step).

-- data:insert({ test = "test" })
-- metadata:insert({ entry = 1 })
data:remove({ id = 1 })

I get the following output:

{ "data", {} }
{ "metadata", { {
      entry = 1,
      id = 1
    } } }

What I'm wondering is why I get different results depending on whether I execute all commands in one go or when I do it in two steps. In my plugin usually the commands will be run in two steps and therefore cascading seems to fail.

Thank you so much for helping me unpuzzle this. Definitely very much possible I'm missing something obvious haha.

[kkharji]

I think the setup only works when metadata field changes, isn't that the same case with sqlite? So when you delete data table, there is no action associated with it. I think most likely an except behavior

Sorry for later response, I lost a family member to heart attach few days ago

[jghauser]

Hey @kkharji, I'm really sorry to hear that. And in any case, you're doing all this in your own free time, so there's no need to be sorry at all!

I'm very much a noob in sqlite, so take what I say with a rather large grain of salt. As far as I understand, on_delete = cascade should delete a row in a child table when the row with the referenced field gets deleted in the parent table. At 4.3:

CASCADE: A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an "ON UPDATE CASCADE" action, it means that the values stored in each dependent child key are modified to match the new parent key values.

And that's (I think) what I'm doing. Note that I didn't drop the entire data table, I just removed the relevant row in it. And when I do everything in one go (the first way of doing it in my last message), then deleting the row in data does delete the corresponding row in metadata. It's only when I do it in two steps that the metadata row is left over. At the least, even if I'm misunderstanding things, it still seems odd that the behaviour is different in the two cases I described in my last message. To me they seem equivalent -- or am I missing something there?

Thank you so much for all the help! Also, let me know if this is taking too much of your time. I could definitely implement the behaviour I want in my plugin and manually delete child rows in the way I want. :)