I have a table representing a hierarchy using the nested set model, declared as
CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
label TEXT NOT NULL UNIQUE,
lft INTEGER NOT NULL,
rgt INTEGER NOT NULL
);
I have implemented functionality for adding, removing and renaming
entries in C. When I got to implementing the function for changing the
parent of an entry the SQL UPDATE-statements just aren't being
performed.
The part of the code that does the updating looks like this:
/* Earlier declarations. */
const char* updates[] = {
"UPDATE tags SET lft = CASE WHEN lft BETWEEN ? AND ?"
" THEN lft + ? ELSE lft + ?"
"END WHERE lft BETWEEN ? AND ?",
"UPDATE tags SET rgt = CASE WHEN rgt BETWEEN ? AND ?"
" THEN rgt + ? ELSE rgt + ?"
"END WHERE rgt BETWEEN ? AND ?"
};
sqlite3_stmt* stmt;
int src_lft, src_rgt, dest_rgt;
int affected_lft, affected_rgt;
int move, displacement;
int i;
/* The update statements being run at the end of the function. */
for (i = 0; i < sizeof(updates)/sizeof(*updates); i++) {
sqlite3_prepare_v2(db, updates[i], -1, &stmt, NULL);
sqlite3_bind_int(stmt, 1, src_lft);
sqlite3_bind_int(stmt, 2, src_rgt);
sqlite3_bind_int(stmt, 3, move);
sqlite3_bind_int(stmt, 4, displacement);
sqlite3_bind_int(stmt, 5, affected_lft);
sqlite3_bind_int(stmt, 6, affected_rgt);
sqlite3_step(stmt);
sqlite3_finalize(stmt);
}
The values put into the prepared statements are correct, and all the
sqlite3 function calls return SQLITE_OK, except step which returns
SQLITE_DONE, so everything seems to be in order. But when inspecting
the database afterwards no actual change have been made.
I have test program construct a test database which looks like this.
$ sqlite3 test.db "SELECT * FROM tags"
1|colors|1|14
2|green|2|7
3|light green|3|4
4|dark green|5|6
5|red|8|13
6|pink|9|10
7|magenta|11|12
In the test program I want to set red's parent to green, which has
proved to be difficult. If I create a new database, where I add the
elements so that this is the case (the add function can add a new
entry with a specified parent), I get the following database.
$ sqlite3 test.db "SELECT * FROM tags"
1|colors|1|14
2|green|2|13
3|light green|3|4
4|dark green|5|6
5|red|7|12
6|pink|8|9
7|magenta|10|11
Now, take the original database and do the UPDATE statements by hand
with the sqlite3 program and you get...
$ sqlite3 test.db "UPDATE tags SET lft = CASE WHEN lft BETWEEN 8 AND
13 THEN lft + (-1) ELSE lft + 6 END WHERE lft BETWEEN 7 AND 13"
$ sqlite3 test.db "UPDATE tags SET rgt = CASE WHEN rgt BETWEEN 8 AND
13 THEN rgt + (-1) ELSE rgt + 6 END WHERE rgt BETWEEN 7 AND 13"
$ sqlite3 test.db "SELECT * FROM tags"
1|colors|1|14
2|green|2|13
3|light green|3|4
4|dark green|5|6
5|red|7|12
6|pink|8|9
7|magenta|10|11
The exact same database as the previously constructed one. The
constants for the statements were taken as they were printed out from
the function which is supposed to change parent, so it should be the
same semantics. Bear in mind that UPDATE statements work fine in all
the other functions I use them in.
I really have no clue what could be wrong here. I'm using MinGW and
generated the sqlite3 library using dlltool.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
