docker/storage/getUp.sql - master - BoRonin/spoker

docker/storage/getUp.sql
86 строк3 KB
first_commit
07 авг 2026, 11:29
07 авг 2026, 11:297e34029
CREATE TABLE
    "game" (
        "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
        "title" varchar(50) UNIQUE NOT NULL,
        "created_at" timestamp DEFAULT now(),
        "multiplier" INT,
        "is_finished" BOOL DEFAULT false
    );

CREATE TABLE
    "users" (
        "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
        "name" varchar NOT NULL,
        "login" varchar UNIQUE NOT NULL,
        "password" varchar NOT NULL,
        "email" varchar UNIQUE NOT NULL,
        "created_at" timestamp DEFAULT now()
    );

CREATE TABLE
    "combinations" (
        "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
        "name" varchar(30) UNIQUE NOT NULL
    );

CREATE TABLE
    "wins" (
        "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
        "created_at" timestamp DEFAULT now(),
        "player" int NOT NULL,
        "combination" int
    );

CREATE TABLE
    "players" (
        "id" INT GENERATED BY DEFAULT AS IDENTITY UNIQUE,
        "user_id" INT NOT NULL,
        "game_id" INT NOT NULL,
        "chips" int DEFAULT 100,
        "chips_final" int,
        CONSTRAINT players_pkey PRIMARY KEY (user_id, game_id)
    );

ALTER TABLE "wins"
ADD
    FOREIGN KEY ("player") REFERENCES "players" ("id");

ALTER TABLE "wins"
ADD
    FOREIGN KEY ("combination") REFERENCES "combinations" ("id");

ALTER TABLE "players"
ADD
    FOREIGN KEY ("user_id") REFERENCES "users" ("id");

ALTER TABLE "players"
ADD
    FOREIGN KEY ("game_id") REFERENCES "game" ("id");

INSERT INTO combinations (name) VALUES ('high card');

INSERT INTO combinations (name) VALUES ('pair');

INSERT INTO combinations (name) VALUES ('two pairs');

INSERT INTO combinations (name) VALUES ('set');

INSERT INTO combinations (name) VALUES ('flush');

INSERT INTO combinations (name) VALUES ('full house');

INSERT INTO combinations (name) VALUES ('four of a kind');

INSERT INTO combinations (name) VALUES ('straight');

INSERT INTO combinations (name) VALUES ('straight flush');

INSERT INTO combinations (name) VALUES ('royal flush');

INSERT INTO combinations (name) VALUES ('all pass');
INSERT INTO combinations (name) VALUES ('split');

INSERT INTO users (name, login, password, email) VALUES ('Юра', 'YBoronin', '$2a$09$O2hCV2T0j7nvpk5i5.raueyTNaX7sXeeBnXfYQi7mdSt9oj0U2XD2', 'y@b.ru');
INSERT INTO users (name, login, password, email) VALUES ('Ира', 'IBoronina', '$2a$09$O2hCV2T0j7nvpk5i5.raueyTNaX7sXeeBnXfYQi7mdSt9oj0U2XD2', 'y2@b.ru');
INSERT INTO users (name, login, password, email) VALUES ('Наташа', 'NBadirova', '$2a$09$O2hCV2T0j7nvpk5i5.raueyTNaX7sXeeBnXfYQi7mdSt9oj0U2XD2', 'y3@b.ru');
INSERT INTO users (name, login, password, email) VALUES ('Ян', 'ITeplov', '$2a$09$O2hCV2T0j7nvpk5i5.raueyTNaX7sXeeBnXfYQi7mdSt9oj0U2XD2', 'y4@b.ru');