41 lines
1.4 KiB
SQL
41 lines
1.4 KiB
SQL
-- migrate:up
|
|
|
|
create table "emails" (
|
|
"id" serial primary key,
|
|
"uid" integer not null references "users" on delete cascade,
|
|
"email" varchar(320) unique not null, -- local 64 + domain 255 + '@' 1 per RFC5321
|
|
"verificationSecret" varchar(255) unique
|
|
);
|
|
|
|
create unique index "emails_uid_verified" on "emails" ("uid", ("verificationSecret" is not null));
|
|
-- at most one verified and one pending email per user
|
|
|
|
insert into "emails" ("uid", "email", "verificationSecret")
|
|
select "id", "email", null as "verificationSecret" from "users" where "email" is not null;
|
|
|
|
insert into "emails" ("uid", "email", "verificationSecret")
|
|
select "id", "pendingEmail", "emailVerificationSecret" from "users" where "pendingEmail" is not null;
|
|
|
|
alter table "users"
|
|
drop "email" cascade,
|
|
drop "pendingEmail" cascade,
|
|
drop "emailVerificationSecret" cascade;
|
|
|
|
-- migrate:down
|
|
|
|
alter table "users"
|
|
add "email" varchar(320) unique,
|
|
add "pendingEmail" varchar(320) unique,
|
|
add "emailVerificationSecret" varchar(255) unique;
|
|
|
|
update "users" set "email" = "emails"."email"
|
|
from "emails"
|
|
where "users"."id" = "emails"."uid" and "emails"."verificationSecret" is null;
|
|
|
|
update "users" set "pendingEmail" = "emails"."email",
|
|
"emailVerificationSecret" = "emails"."verificationSecret"
|
|
from "emails"
|
|
where "users"."id" = "emails"."uid" and "emails"."verificationSecret" is not null;
|
|
|
|
drop table "emails";
|