37 lines
1.2 KiB
SQL
37 lines
1.2 KiB
SQL
-- migrate:up
|
|
|
|
create table "emailVerifications" (
|
|
"id" serial primary key,
|
|
"secret" varchar(255) unique not null,
|
|
"expires" timestamp not null
|
|
);
|
|
|
|
alter table "emails" add "verification" integer unique references "emailVerifications" on delete cascade;
|
|
|
|
insert into "emailVerifications" ("secret", "expires")
|
|
select "verificationSecret", (localtimestamp + '7 days') as "expires" from "emails"
|
|
where "verificationSecret" is not null;
|
|
|
|
update "emails" set
|
|
"verification" = (select "id" from "emailVerifications" where "secret" = "verificationSecret");
|
|
|
|
alter table "emails" drop "verificationSecret" cascade;
|
|
|
|
create unique index "emails_uid_verified" on "emails" ("uid", ("verification" is not null));
|
|
-- at most one verified and one pending email per user
|
|
|
|
-- migrate:down
|
|
|
|
alter table "emails" add "verificationSecret" varchar(255) unique;
|
|
|
|
update "emails" set
|
|
"verificationSecret" = (select "secret" from "emailVerifications"
|
|
where "emailVerifications"."id" = "emails"."verification");
|
|
|
|
alter table "emails" drop "verification";
|
|
|
|
drop table "emailVerifications";
|
|
|
|
create unique index "emails_uid_verified" on "emails" ("uid", ("verificationSecret" is not null));
|
|
-- at most one verified and one pending email per user
|