On Sat, 21 Jan 2017 19:33:06 +0200
R Smith <rsm...@rsweb.co.za> wrote:
> UPDATE desktops SET indexNo = -indexNo - 1 WHERE indexNo >= X;
> INSERT INTO desktops ... new row for indexNo X ... ;
> UPDATE desktops SET indexNo = -indexNo WHERE indexNo < 0;
unless
FOREIGN KEY indexNo references foo(bar)
or
FOREIGN KEY bar references desktops(indexNo)
I don't think enough functionality is exposed to create a generalized
function that would just do the right thing. One can imagine a
C function sqlite3_exec_update that
1. determines the affected columns
2. finds any applicable constraints
3. drops the constraints
4. begins a transaction
5. executes the update
6. re-adds the contraints
7. commits
But just for starters ALTER TABLE does not support constraints,
and SQLITE_MASTER doesn't reflect constraint definitions.
DRH suggests renaming the table or using an index instead. I'm not
sure renaming the table works in the presence of foreign key
enforcement (so that would have to be touched, too). Even if
indexes are used, the index definitions are not exposed in a way that
the could be dropped and re-created under programatic control without
parsing the SQL. Both approaches are inefficient if only a small
proportion of rows are affected. Both impose unnecessary complexity on
the user.
The only place all the above information is readily available is inside
the SQLite engine. There the SQL is parsed and all applicable
constraints are exposed in binary form. A simplistic decision was made
early on to enforce constraints on a row-by-row basis. That decision
was defensible at the time. As SQLite has grown in sophistication --
WAL, foreign keys, CTE, recursion -- lack of atomic update looms
more and more as an important defect.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
