GNOME Bugzilla – Bug 570232
SetOption uses sqlite REPLACE which *doesn't* replace, it INSERTs
Last modified: 2010-05-17 13:34:49 UTC
Please describe the problem: The sqlite stored procedure for SetOption in data/db/sqlite-stored-procs.sql uses REPLACE but that is just a synonym for INSERT. The result is the repeated setting of the same option creates multiple rows in the Option table. SetOption REPLACE INTO Options (OptionKey, OptionValue) VALUES (?,?); Either the OptionKey column needs to be a key, or an alternative stored procedure is required. Steps to reproduce: 1. Change the same option multiple times Actual results: Multiple entries for the same OptionKey in the Options table Expected results: A unique row per OptionKey Does this happen every time? Yes Other information: IRC conversation log (I'm IntuitiveNipple) <IntuitiveNipple> I noticed that the SetOption stored procedure creates a new entry in the table each time it is executed rather than updating the existing value, and was wondering if that is another bug? <IntuitiveNipple> the s.p. uses REPLACE but that is the same as INSERT (unlike mysql where REPLACE is like DELETE then INSERT) <IntuitiveNipple> If multiple entries of IntegrityCheck are in Options, then will GetOption always return the last row? <martyn> yea, REPLACE might be a MySQL command not a SQLite command, thanks for noticing <martyn> juergbi: any comment there? <IntuitiveNipple> REPLACE is valid sqlite, it just doesn't do a DELETE - that would be mysql specific <juergbi> do we miss a proper primary key here? <IntuitiveNipple> REPLACE is a synonym for INSERT: http://www.sqlite.org/lang_replace.html <juergbi> we'd probably want INSERT OR REPLACE <martyn> IntuitiveNipple: can you update the bug with the result of this conversation? <martyn> I have to nip out quickly <martyn> I will fix it when I get back <IntuitiveNipple> I was about to open a new one on this issue, since it could be quite complex! <juergbi> afaict, it should be fixed by just marking the OptionKey as PRIMARY KEY in the db schema <juergbi> REPLACE is not a synonym for INSERT, it's a synonym for INSERT OR REPLACE <juergbi> which is exactly the behavior we want <IntuitiveNipple> OptionKey is a string, and PRIMARY KEY should be an integer, as far as I recall
I've done a test to confirm that creating the table with OptionKey as a PRIMARY KEY solves the issue. sqlite3 test.db sqlite> .schema test CREATE TABLE test (OptionKey text primary key, OptionValue text); sqlite> .quit sqlite3 test.db "select * from test" IntegrityCheck|1 sqlite3 test.db "replace into test (OptionKey,OptionValue) values ('IntegrityCheck',2)" sqlite3 test.db "select * from test" IntegrityCheck|2 The only issue now is how to fix-up existing tables (since ALTER TABLE can't change the column-type) *if* it proves to be a problem (if a SELECT doesn't return the last row when GetOption is used). The easiest way might be to create a new table and move the data over then drop the old table.
Just fixed it in HEAD, looks like we'll have to force reindexing anyway...
Moving "Daemon" component bugs to "General" since "Daemon" refers to the old 0.6 architecture