Zpotify.sql - master - vessel/junkyard

Zpotify.sql
124 строки4 KB
vessel
upload files
24 янв 2025, 09:59
24 янв 2025, 09:59115918a
CREATE TABLE IF NOT EXISTS "Users" (
	"id" serial NOT NULL UNIQUE,
	"username" varchar(255) NOT NULL UNIQUE,
	"realname" varchar(255) NOT NULL,
	"email" varchar(255) NOT NULL UNIQUE,
	"passwordhash" varchar(255) NOT NULL,
	"premium" boolean NOT NULL DEFAULT false,
	"birthdate" date NOT NULL,
	"regdate" timestamp NOT NULL,
	"profilepictureurl" varchar(255) NOT NULL,
	PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "Playlists" (
	"id" serial NOT NULL UNIQUE,
	"author" bigint NOT NULL,
	"title" varchar(255) NOT NULL,
	"coverurl" varchar(255) NOT NULL,
	PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "Friends" (
	"user" bigint NOT NULL UNIQUE,
	"friend" bigint NOT NULL UNIQUE,
	PRIMARY KEY ("user", "friend")
);

CREATE TABLE IF NOT EXISTS "Added_to_playlist" (
	"id" serial NOT NULL UNIQUE,
	"playlist" bigint NOT NULL,
	"song" bigint NOT NULL,
	"date" timestamp NOT NULL,
	PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "Songs" (
	"id" serial NOT NULL UNIQUE,
	"title" bigint NOT NULL,
	"songurl" varchar(255) NOT NULL UNIQUE,
	PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "Bands" (
	"id" serial NOT NULL UNIQUE,
	"title" bigint NOT NULL,
	"description" varchar(255) NOT NULL,
	"coverurl" varchar(255) NOT NULL,
	PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "Subscriptions" (
	"user" bigint NOT NULL,
	"sub" bigint NOT NULL,
	PRIMARY KEY ("user", "sub")
);

CREATE TABLE IF NOT EXISTS "Follow" (
	"follower" bigint NOT NULL,
	"user" bigint NOT NULL,
	PRIMARY KEY ("follower", "user")
);

CREATE TABLE IF NOT EXISTS "Albums" (
	"id" serial NOT NULL UNIQUE,
	"title" varchar(255) NOT NULL,
	"coverurl" varchar(255) NOT NULL,
	"songs" bigint NOT NULL,
	"releasedate" date NOT NULL,
	PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "Playlist_followers" (
	"playlist" bigint NOT NULL,
	"user" bigint NOT NULL,
	PRIMARY KEY ("playlist", "user")
);

CREATE TABLE IF NOT EXISTS "Album_likes" (
	"album" bigint NOT NULL,
	"user" bigint NOT NULL,
	PRIMARY KEY ("album", "user")
);

CREATE TABLE IF NOT EXISTS "album_bands" (
	"album" bigint NOT NULL,
	"band" bigint NOT NULL,
	PRIMARY KEY ("album", "band")
);

CREATE TABLE IF NOT EXISTS "bands_members" (
	"band" bigint NOT NULL,
	"member" bigint NOT NULL,
	PRIMARY KEY ("band", "member")
);


ALTER TABLE "Playlists" ADD CONSTRAINT "Playlists_fk1" FOREIGN KEY ("author") REFERENCES "Users"("id");
ALTER TABLE "Friends" ADD CONSTRAINT "Friends_fk0" FOREIGN KEY ("user") REFERENCES "Users"("id");

ALTER TABLE "Friends" ADD CONSTRAINT "Friends_fk1" FOREIGN KEY ("friend") REFERENCES "Users"("id");
ALTER TABLE "Added_to_playlist" ADD CONSTRAINT "Added_to_playlist_fk1" FOREIGN KEY ("playlist") REFERENCES "Playlists"("id");

ALTER TABLE "Added_to_playlist" ADD CONSTRAINT "Added_to_playlist_fk2" FOREIGN KEY ("song") REFERENCES "Songs"("id");


ALTER TABLE "Subscriptions" ADD CONSTRAINT "Subscriptions_fk0" FOREIGN KEY ("user") REFERENCES "Users"("id");

ALTER TABLE "Subscriptions" ADD CONSTRAINT "Subscriptions_fk1" FOREIGN KEY ("sub") REFERENCES "Bands"("id");
ALTER TABLE "Follow" ADD CONSTRAINT "Follow_fk0" FOREIGN KEY ("follower") REFERENCES "Users"("id");

ALTER TABLE "Follow" ADD CONSTRAINT "Follow_fk1" FOREIGN KEY ("user") REFERENCES "Users"("id");
ALTER TABLE "Albums" ADD CONSTRAINT "Albums_fk3" FOREIGN KEY ("songs") REFERENCES "Songs"("id");
ALTER TABLE "Playlist_followers" ADD CONSTRAINT "Playlist_followers_fk0" FOREIGN KEY ("playlist") REFERENCES "Playlists"("id");

ALTER TABLE "Playlist_followers" ADD CONSTRAINT "Playlist_followers_fk1" FOREIGN KEY ("user") REFERENCES "Users"("id");
ALTER TABLE "Album_likes" ADD CONSTRAINT "Album_likes_fk0" FOREIGN KEY ("album") REFERENCES "Albums"("id");

ALTER TABLE "Album_likes" ADD CONSTRAINT "Album_likes_fk1" FOREIGN KEY ("user") REFERENCES "Users"("id");
ALTER TABLE "album_bands" ADD CONSTRAINT "album_bands_fk0" FOREIGN KEY ("album") REFERENCES "Albums"("id");

ALTER TABLE "album_bands" ADD CONSTRAINT "album_bands_fk1" FOREIGN KEY ("band") REFERENCES "Bands"("id");
ALTER TABLE "bands_members" ADD CONSTRAINT "bands_members_fk0" FOREIGN KEY ("band") REFERENCES "Bands"("id");

ALTER TABLE "bands_members" ADD CONSTRAINT "bands_members_fk1" FOREIGN KEY ("member") REFERENCES "Users"("id");