mirror of
https://github.com/Sendouc/sendou.ink.git
synced 2026-03-21 18:04:39 -05:00
fd48bced91
* Initial * CSS lint * Test CI * Add 1v1, 2v2, and 3v3 Tags (#1771) * Initial * CSS lint * Test CI * Rename step --------- Co-authored-by: xi <104683822+ximk@users.noreply.github.com>
78 lines
2.0 KiB
JavaScript
78 lines
2.0 KiB
JavaScript
export function up(db) {
|
|
db.prepare(
|
|
/*sql*/ `
|
|
create table "UnvalidatedUserSubmittedImage" (
|
|
"id" integer primary key,
|
|
"validatedAt" integer,
|
|
"url" text not null unique
|
|
) strict
|
|
`,
|
|
).run();
|
|
|
|
db.prepare(
|
|
/*sql*/ `
|
|
create view "UserSubmittedImage"
|
|
as
|
|
select * from "UnvalidatedUserSubmittedImage" where "validatedAt" is not null
|
|
`,
|
|
).run();
|
|
|
|
db.prepare(
|
|
/*sql*/ `
|
|
create table "AllTeam" (
|
|
"id" integer primary key,
|
|
"name" text not null,
|
|
"customUrl" text not null,
|
|
"inviteCode" text not null,
|
|
"bio" text,
|
|
"twitter" text,
|
|
"avatarImgId" integer,
|
|
"bannerImgId" integer,
|
|
"createdAt" integer default (strftime('%s', 'now')) not null,
|
|
"deletedAt" integer,
|
|
foreign key ("avatarImgId") references "UnvalidatedUserSubmittedImage"("id") on delete set null,
|
|
foreign key ("bannerImgId") references "UnvalidatedUserSubmittedImage"("id") on delete set null
|
|
) strict
|
|
`,
|
|
).run();
|
|
db.prepare(`create index team_custom_url on "AllTeam"("customUrl")`).run();
|
|
|
|
db.prepare(
|
|
/*sql*/ `
|
|
create view "Team"
|
|
as
|
|
select * from "AllTeam" where "deletedAt" is null
|
|
`,
|
|
).run();
|
|
|
|
db.prepare(
|
|
/*sql*/ `
|
|
create table "AllTeamMember" (
|
|
"teamId" integer not null,
|
|
"userId" integer not null,
|
|
"role" text,
|
|
"isOwner" integer not null default 0,
|
|
"createdAt" integer default (strftime('%s', 'now')) not null,
|
|
"leftAt" integer,
|
|
foreign key ("teamId") references "AllTeam"("id") on delete cascade,
|
|
foreign key ("userId") references "User"("id") on delete cascade,
|
|
unique("teamId", "userId") on conflict rollback
|
|
) strict
|
|
`,
|
|
).run();
|
|
|
|
db.prepare(
|
|
/*sql*/ `
|
|
create view "TeamMember"
|
|
as
|
|
select "AllTeamMember".*
|
|
from "AllTeamMember"
|
|
left join "Team" on "Team"."id" = "AllTeamMember"."teamId"
|
|
where "AllTeamMember"."leftAt" is null
|
|
and
|
|
-- if team id is null the team is deleted
|
|
"Team"."id" is not null
|
|
`,
|
|
).run();
|
|
}
|