it seems that litedb sequel driver ignores/breaks insert_conflict functionality of sequel. Example:
require "sequel"
DB = Sequel.connect("litedb://test.db")
DB.create_table(:_systems) do
primary_key :id
String :name, unique: true, null: false
end
DB.create_table(:_nodes) do
primary_key :id
foreign_key :_system_id, :_systems, on_delete: :cascade
String :hostname, null: false
String :platform
String :uri, null: false
end
class System < Sequel::Model(DB[:_systems])
one_to_many :nodes, key: :_system_id
one_to_many :tokens, key: :_system_id
end
class Node < Sequel::Model(DB[:_nodes])
many_to_one :system, key: :_system_id
plugin :validation_helpers
plugin :insert_conflict
def validate
super
validates_presence :hostname
validates_presence :uri
end
end
2.times do
node = Node.new(hostname: "host1", uri: "ssh://1.1.1.1:22")
node.id = 1
node.insert_conflict(target: :id, update: { hostname: "host1" }).save
end
p Node.count
It will break with following log (notice, that INSERT statement if without ON CONFLICT clause):
I, [2024-02-06T22:25:07.018779 #30242] INFO -- : (0.002177s) CREATE TABLE `_systems` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `name` varchar(255) NOT NULL UNIQUE)
I, [2024-02-06T22:25:07.019082 #30242] INFO -- : (0.000099s) CREATE TABLE `_nodes` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `_system_id` integer REFERENCES `_systems` ON DELETE CASCADE, `hostname` varchar(255) NOT NULL, `platform` varchar(255), `uri` varchar(255) NOT NULL)
I, [2024-02-06T22:25:07.019315 #30242] INFO -- : (0.000017s) BEGIN IMMEDIATE TRANSACTION
I, [2024-02-06T22:25:07.019449 #30242] INFO -- : (0.000051s) PRAGMA table_info('_systems')
I, [2024-02-06T22:25:07.019514 #30242] INFO -- : (0.000014s) COMMIT
I, [2024-02-06T22:25:07.019995 #30242] INFO -- : (0.000015s) BEGIN IMMEDIATE TRANSACTION
I, [2024-02-06T22:25:07.020103 #30242] INFO -- : (0.000049s) PRAGMA table_info('_nodes')
I, [2024-02-06T22:25:07.020164 #30242] INFO -- : (0.000014s) COMMIT
I, [2024-02-06T22:25:07.021538 #30242] INFO -- : (0.000015s) BEGIN IMMEDIATE TRANSACTION
I, [2024-02-06T22:25:07.021663 #30242] INFO -- : (0.000025s) INSERT INTO `_nodes` (`hostname`, `uri`, `id`) VALUES ('host1', 'ssh://1.1.1.1:22', 1)
I, [2024-02-06T22:25:07.021781 #30242] INFO -- : (0.000038s) SELECT * FROM `_nodes` WHERE `id` = 1
I, [2024-02-06T22:25:07.021868 #30242] INFO -- : (0.000051s) COMMIT
I, [2024-02-06T22:25:07.022026 #30242] INFO -- : (0.000017s) BEGIN IMMEDIATE TRANSACTION
E, [2024-02-06T22:25:07.022114 #30242] ERROR -- : SQLite3::ConstraintException: UNIQUE constraint failed: _nodes.id: INSERT INTO `_nodes` (`hostname`, `uri`, `id`) VALUES ('host1', 'ssh://1.1.1.1:22', 1)
I, [2024-02-06T22:25:07.022191 #30242] INFO -- : (0.000014s) ROLLBACK
/usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:100:in `step': SQLite3::ConstraintException: UNIQUE constraint failed: _nodes.id (Sequel::ConstraintViolation)
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:100:in `next'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:125:in `each'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:98:in `to_a'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:98:in `block in execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:72:in `prepare'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:88:in `execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:257:in `block (2 levels) in _execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/logging.rb:43:in `log_connection_yield'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:257:in `block in _execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:88:in `hold'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:in `synchronize'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:248:in `_execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:186:in `execute_insert'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:1205:in `execute_insert'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:423:in `insert'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1744:in `_insert_raw'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1726:in `_insert'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1794:in `block (2 levels) in _save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1047:in `around_create'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1791:in `block in _save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1047:in `around_save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1786:in `_save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:in `block (2 levels) in save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:264:in `_transaction'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:239:in `block in transaction'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:92:in `hold'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:in `synchronize'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:197:in `transaction'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1954:in `checked_transaction'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:in `block in save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1940:in `checked_save_failure'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:in `save'
from t.rb:44:in `block in <main>'
from <internal:numeric>:237:in `times'
from t.rb:41:in `<main>'
/usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:100:in `step': UNIQUE constraint failed: _nodes.id (SQLite3::ConstraintException)
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:100:in `next'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:125:in `each'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:98:in `to_a'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:98:in `block in execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:72:in `prepare'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:88:in `execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:257:in `block (2 levels) in _execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/logging.rb:43:in `log_connection_yield'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:257:in `block in _execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:88:in `hold'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:in `synchronize'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:248:in `_execute'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:186:in `execute_insert'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:1205:in `execute_insert'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:423:in `insert'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1744:in `_insert_raw'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1726:in `_insert'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1794:in `block (2 levels) in _save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1047:in `around_create'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1791:in `block in _save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1047:in `around_save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1786:in `_save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:in `block (2 levels) in save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:264:in `_transaction'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:239:in `block in transaction'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:92:in `hold'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:in `synchronize'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:197:in `transaction'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1954:in `checked_transaction'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:in `block in save'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1940:in `checked_save_failure'
from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:in `save'
from t.rb:44:in `block in <main>'
from <internal:numeric>:237:in `times'
from t.rb:41:in `<main>'
When you switch litedb:// to sqlite:// you get following output:
I, [2024-02-06T22:26:32.192244 #30512] INFO -- : (0.004684s) CREATE TABLE `_systems` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `name` varchar(255) NOT NULL UNIQUE)
I, [2024-02-06T22:26:32.196462 #30512] INFO -- : (0.004014s) CREATE TABLE `_nodes` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `_system_id` integer REFERENCES `_systems` ON DELETE CASCADE, `hostname` varchar(255) NOT NULL, `platform` varchar(255), `uri` varchar(255) NOT NULL)
I, [2024-02-06T22:26:32.196744 #30512] INFO -- : (0.000038s) SELECT sqlite_version()
I, [2024-02-06T22:26:32.196883 #30512] INFO -- : (0.000051s) PRAGMA table_xinfo('_systems')
I, [2024-02-06T22:26:32.197471 #30512] INFO -- : (0.000078s) PRAGMA table_xinfo('_nodes')
I, [2024-02-06T22:26:32.198855 #30512] INFO -- : (0.000012s) BEGIN
I, [2024-02-06T22:26:32.199113 #30512] INFO -- : (0.000140s) INSERT INTO `_nodes` (`hostname`, `uri`, `id`) VALUES ('host1', 'ssh://1.1.1.1:22', 1) ON CONFLICT (`id`) DO UPDATE SET `hostname` = 'host1' RETURNING *
I, [2024-02-06T22:26:32.202219 #30512] INFO -- : (0.003058s) COMMIT
I, [2024-02-06T22:26:32.202435 #30512] INFO -- : (0.000014s) BEGIN
I, [2024-02-06T22:26:32.202606 #30512] INFO -- : (0.000075s) INSERT INTO `_nodes` (`hostname`, `uri`, `id`) VALUES ('host1', 'ssh://1.1.1.1:22', 1) ON CONFLICT (`id`) DO UPDATE SET `hostname` = 'host1' RETURNING *
I, [2024-02-06T22:26:32.202668 #30512] INFO -- : (0.000016s) COMMIT
I, [2024-02-06T22:26:32.202912 #30512] INFO -- : (0.000038s) SELECT count(*) AS 'count' FROM `_nodes` LIMIT 1
1
No error, INSERT had ON CONFLICT, and there are only 1 Node in db.
I'm using ruby 3.3.0, litestack 0.4.2, sqlite3 1.7.1 with sqlite in version 3.45.0.
Hi,
it seems that litedb sequel driver ignores/breaks insert_conflict functionality of sequel. Example:
It will break with following log (notice, that INSERT statement if without ON CONFLICT clause):
When you switch litedb:// to sqlite:// you get following output:
No error, INSERT had ON CONFLICT, and there are only 1 Node in db.
I'm using ruby 3.3.0, litestack 0.4.2, sqlite3 1.7.1 with sqlite in version 3.45.0.