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

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