On Saturday, 21 January, 2017 13:45 James K. Lowden <jklow...@schemamania.org> wrote: > 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)
In this case changing the overloaded relative-record-number indexNo would not work at all, even if the update were atomic. > or > FOREIGN KEY bar references desktops(indexNo) ON UPDATE CASCADE would fix this, of course. > 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. My suggestion would be to forgo the artificial relative position being computed by the application and replace it with the actual data used to determine the ordering, and add an appropriate ORDER BY when retrieving the data. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
