github.com/icyphox/x@v0.0.355-0.20220311094250-029bd783e8b8/popx/stub/migrations/legacy/20200831110752_identity_verifiable_address_remove_code.sqlite3.down.sql (about)

     1  ALTER TABLE "identity_verifiable_addresses" ADD COLUMN "code" TEXT;
     2  ALTER TABLE "identity_verifiable_addresses" ADD COLUMN "expires_at" DATETIME;
     3  UPDATE identity_verifiable_addresses SET code = substr(hex(randomblob(32)), 0, 32) WHERE code IS NULL;
     4  UPDATE identity_verifiable_addresses SET expires_at = CURRENT_TIMESTAMP WHERE expires_at IS NULL;
     5  DROP INDEX IF EXISTS "identity_verifiable_addresses_status_via_uq_idx";
     6  DROP INDEX IF EXISTS "identity_verifiable_addresses_status_via_idx";
     7  CREATE TABLE "_identity_verifiable_addresses_tmp" (
     8  "id" TEXT PRIMARY KEY,
     9  "status" TEXT NOT NULL,
    10  "via" TEXT NOT NULL,
    11  "verified" bool NOT NULL,
    12  "value" TEXT NOT NULL,
    13  "verified_at" DATETIME,
    14  "identity_id" char(36) NOT NULL,
    15  "created_at" DATETIME NOT NULL,
    16  "updated_at" DATETIME NOT NULL,
    17  "code" TEXT NOT NULL,
    18  "expires_at" DATETIME,
    19  FOREIGN KEY (identity_id) REFERENCES identities (id) ON UPDATE NO ACTION ON DELETE CASCADE
    20  );
    21  CREATE UNIQUE INDEX "identity_verifiable_addresses_status_via_uq_idx" ON "_identity_verifiable_addresses_tmp" (via, value);
    22  CREATE INDEX "identity_verifiable_addresses_status_via_idx" ON "_identity_verifiable_addresses_tmp" (via, value);
    23  INSERT INTO "_identity_verifiable_addresses_tmp" (id, status, via, verified, value, verified_at, identity_id, created_at, updated_at, code, expires_at) SELECT id, status, via, verified, value, verified_at, identity_id, created_at, updated_at, code, expires_at FROM "identity_verifiable_addresses";
    24  DROP TABLE "identity_verifiable_addresses";
    25  ALTER TABLE "_identity_verifiable_addresses_tmp" RENAME TO "identity_verifiable_addresses";
    26  DROP INDEX IF EXISTS "identity_verifiable_addresses_status_via_uq_idx";
    27  DROP INDEX IF EXISTS "identity_verifiable_addresses_status_via_idx";
    28  CREATE TABLE "_identity_verifiable_addresses_tmp" (
    29  "id" TEXT PRIMARY KEY,
    30  "status" TEXT NOT NULL,
    31  "via" TEXT NOT NULL,
    32  "verified" bool NOT NULL,
    33  "value" TEXT NOT NULL,
    34  "verified_at" DATETIME,
    35  "identity_id" char(36) NOT NULL,
    36  "created_at" DATETIME NOT NULL,
    37  "updated_at" DATETIME NOT NULL,
    38  "code" TEXT NOT NULL,
    39  "expires_at" DATETIME,
    40  FOREIGN KEY (identity_id) REFERENCES identities (id) ON UPDATE NO ACTION ON DELETE CASCADE
    41  );
    42  CREATE UNIQUE INDEX "identity_verifiable_addresses_status_via_uq_idx" ON "_identity_verifiable_addresses_tmp" (via, value);
    43  CREATE INDEX "identity_verifiable_addresses_status_via_idx" ON "_identity_verifiable_addresses_tmp" (via, value);
    44  INSERT INTO "_identity_verifiable_addresses_tmp" (id, status, via, verified, value, verified_at, identity_id, created_at, updated_at, code, expires_at) SELECT id, status, via, verified, value, verified_at, identity_id, created_at, updated_at, code, expires_at FROM "identity_verifiable_addresses";
    45  DROP TABLE "identity_verifiable_addresses";
    46  ALTER TABLE "_identity_verifiable_addresses_tmp" RENAME TO "identity_verifiable_addresses";
    47  CREATE UNIQUE INDEX "identity_verifiable_addresses_code_uq_idx" ON "identity_verifiable_addresses" (code);
    48  CREATE INDEX "identity_verifiable_addresses_code_idx" ON "identity_verifiable_addresses" (code);