Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"
Hi Dan! On 07.01.19 16:58, Dan Kennedy wrote: > I think it was a bug. Thanks for reporting it. Now fixed here: > > https://www.sqlite.org/src/info/6281ef974c0ac7a7 Cool, thanks for confirming it was indeed a bug (and not my stupidity), and the quick fix! Yours, Daniel -- https://www.domob.eu/ OpenPGP: 1142 850E 6DFF 65BA 63D6 88A8 B249 2AC4 A733 0737 Namecoin: id/domob -> https://nameid.org/?name=domob -- 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz To go: Arc-Cav-Hea-Kni-Mon-Tou signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"
On 01/06/2019 08:08 PM, Daniel Kraft wrote: Hi! It seems to me that the session extension is broken in a situation that involves a "WITHOUT ROWID" table and "INSERT OR REPLACE" statements (but not if only one of those is used). Note that I'm using SQLite version 3.26.0 (> 3.17.0), so the session extension should work also for my WITHOUT ROWID table. My test code is attached (it uses the Google logging library in addition to SQLite3 with session extension). This code creates a WITHOUT ROWID table with an INTEGER PRIMARY KEY and puts in some initial data. Then it uses an "INSERT OR REPLACE" statement to update the initial row and insert a new one, recording this change into a changeset. Finally, it inverts and applies the changeset, so that we should get back to the initial data. However, when I build and run the code as it is, then the table will be *empty* in the end (instead of containing the initial data pair (100, 1)). It works as expected if I make one of the following two changes: a) Commenting out the "WITHOUT ROWID" line, or b) using "UPDATE" and "INSERT" statements (as in the comment in the code) instead of one "INSERT OR REPLACE" statement. Am I doing something wrong here, or is this a bug? I think it was a bug. Thanks for reporting it. Now fixed here: https://www.sqlite.org/src/info/6281ef974c0ac7a7 Dan. Is there something I can do to make this work as expected? Thanks! Yours, Daniel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"
On 6-1-2019 14:11, Daniel Kraft wrote:
Since it seems that the mailing list swallowed my attached example code,
I've put it on Github as well:
https://gist.github.com/domob1812/7842edade949b5169edaf9de79f1b6d1
The example code (from github.com), so no-one has to go-to github:
#include
#include
#include
#include
int
printResults (void*, int numColumns, char** values, char** columns)
{
for (int i = 0; i < numColumns; ++i)
std::cout << " " << columns[i] << " = " << values[i];
std::cout << std::endl;
return 0;
}
int
abortOnConflict (void* ctx, int conflict, sqlite3_changeset_iter* it)
{
LOG (ERROR) << "Changeset application has a conflict of type " << conflict;
return SQLITE_CHANGESET_ABORT;
}
int
main (int argc, char** argv)
{
LOG (INFO)
<< "Using SQLite version " << SQLITE_VERSION
<< " (library version: " << sqlite3_libversion () << ")";
CHECK_EQ (SQLITE_VERSION_NUMBER, sqlite3_libversion_number ())
<< "Mismatch between header and library SQLite versions";
sqlite3* db;
CHECK_EQ (sqlite3_open (":memory:", &db), SQLITE_OK);
/* Create schema (table WITHOUT ROWID) and insert some initial data. */
CHECK_EQ (sqlite3_exec (db, R"(
CREATE TABLE `test` (
`id` INTEGER PRIMARY KEY,
`value` INTEGER
)
WITHOUT ROWID
;
INSERT INTO `test` (`id`, `value`) VALUES (100, 1);
)", nullptr, nullptr, nullptr), SQLITE_OK);
/* Make a modification and record a changeset for it. */
sqlite3_session* session;
CHECK_EQ (sqlite3session_create (db, "main", &session), SQLITE_OK);
CHECK_EQ (sqlite3session_attach (session, nullptr), SQLITE_OK);
CHECK_EQ (sqlite3_exec (db, R"(
-- UPDATE `test` SET `value` = 2 WHERE `id` = 100;
-- INSERT INTO `test` (`id`, `value`) VALUES (200, 3);
INSERT OR REPLACE INTO `test`
(`id`, `value`) VALUES (100, 2), (200, 3)
)", nullptr, nullptr, nullptr), SQLITE_OK);
int changeSize;
void* changeBytes;
CHECK_EQ (sqlite3session_changeset (session, &changeSize, &changeBytes),
SQLITE_OK);
sqlite3session_delete (session);
/* Apply the inverted changeset to revert the change. */
int invertedSize;
void* invertedBytes;
CHECK_EQ (sqlite3changeset_invert (changeSize, changeBytes,
&invertedSize, &invertedBytes),
SQLITE_OK);
CHECK_EQ (sqlite3changeset_apply (db, invertedSize, invertedBytes, nullptr,
&abortOnConflict, nullptr),
SQLITE_OK);
sqlite3_free (changeBytes);
sqlite3_free (invertedBytes);
/* Print contents of table for debugging. */
std::cout << "Final values of table:" << std::endl;
CHECK_EQ (sqlite3_exec (db, R"(
SELECT * FROM `test`
)", &printResults, nullptr, nullptr), SQLITE_OK);
sqlite3_close (db);
return EXIT_SUCCESS;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"
On 06 Jan 2019, at 13:11, Daniel Kraft wrote: > Since it seems that the mailing list swallowed my attached example code, > I've put it on Github as well: Attachments are explicitly disallowed on this list. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"
Since it seems that the mailing list swallowed my attached example code, I've put it on Github as well: https://gist.github.com/domob1812/7842edade949b5169edaf9de79f1b6d1 On 06.01.19 14:08, Daniel Kraft wrote: > Hi! > > It seems to me that the session extension is broken in a situation that > involves a "WITHOUT ROWID" table and "INSERT OR REPLACE" statements (but > not if only one of those is used). Note that I'm using SQLite version > 3.26.0 (> 3.17.0), so the session extension should work also for my > WITHOUT ROWID table. > > My test code is attached (it uses the Google logging library in addition > to SQLite3 with session extension). This code creates a WITHOUT ROWID > table with an INTEGER PRIMARY KEY and puts in some initial data. Then > it uses an "INSERT OR REPLACE" statement to update the initial row and > insert a new one, recording this change into a changeset. Finally, it > inverts and applies the changeset, so that we should get back to the > initial data. > > However, when I build and run the code as it is, then the table will be > *empty* in the end (instead of containing the initial data pair (100, > 1)). It works as expected if I make one of the following two changes: > > a) Commenting out the "WITHOUT ROWID" line, or > b) using "UPDATE" and "INSERT" statements (as in the comment in the > code) instead of one "INSERT OR REPLACE" statement. > > Am I doing something wrong here, or is this a bug? Is there something I > can do to make this work as expected? > > Thanks! Yours, > Daniel > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- https://www.domob.eu/ OpenPGP: 1142 850E 6DFF 65BA 63D6 88A8 B249 2AC4 A733 0737 Namecoin: id/domob -> https://nameid.org/?name=domob -- 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz To go: Arc-Cav-Hea-Kni-Mon-Tou signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"
Hi! It seems to me that the session extension is broken in a situation that involves a "WITHOUT ROWID" table and "INSERT OR REPLACE" statements (but not if only one of those is used). Note that I'm using SQLite version 3.26.0 (> 3.17.0), so the session extension should work also for my WITHOUT ROWID table. My test code is attached (it uses the Google logging library in addition to SQLite3 with session extension). This code creates a WITHOUT ROWID table with an INTEGER PRIMARY KEY and puts in some initial data. Then it uses an "INSERT OR REPLACE" statement to update the initial row and insert a new one, recording this change into a changeset. Finally, it inverts and applies the changeset, so that we should get back to the initial data. However, when I build and run the code as it is, then the table will be *empty* in the end (instead of containing the initial data pair (100, 1)). It works as expected if I make one of the following two changes: a) Commenting out the "WITHOUT ROWID" line, or b) using "UPDATE" and "INSERT" statements (as in the comment in the code) instead of one "INSERT OR REPLACE" statement. Am I doing something wrong here, or is this a bug? Is there something I can do to make this work as expected? Thanks! Yours, Daniel -- https://www.domob.eu/ OpenPGP: 1142 850E 6DFF 65BA 63D6 88A8 B249 2AC4 A733 0737 Namecoin: id/domob -> https://nameid.org/?name=domob -- 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz To go: Arc-Cav-Hea-Kni-Mon-Tou signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
