Change row sequence of an SQLite table and reset/regenerate id (PK) column accordingly

unholy_sqlite_reorder.sh

#!/bin/sh
set -e

if [ ! -w "$1" ] ; then
  echo "Usage: $0 [sqlite.db]"
  exit 1
fi

sqlite3 -echo -bail $1 << __EOF
BEGIN TRANSACTION;
-- new order
CREATE TEMPORARY TABLE users_copy AS SELECT * FROM users ORDER BY name, locality;
-- no time for nostalgia, but we do want to keep the current schema, indexes, and stuff
DELETE FROM users;
-- if you liked it then you shoulda' put an id INTEGER PRIMARY KEY on it (-not! let it regenerate instead!)
INSERT INTO users ( name, locality, homepage, created_at, updated_at )
 SELECT name, locality, homepage, created_at, updated_at
 FROM users_copy;
-- drop it like it's hot, even though it was a tmp table
DROP TABLE users_copy;
COMMIT;
__EOF