-- DROP TABLE IF EXISTS Users, Scores, Skins, HasSkin CASCADE;

CREATE TABLE Users (
  user_id   TEXT  PRIMARY KEY,
  nick_name TEXT  NOT NULL,
  money     INT   NOT NULL DEFAULT 0 CHECK(money >= 0)
);

CREATE TABLE Scores (
  user_id   TEXT      NOT NULL,
  score     INT       NOT NULL,
  date_time TIMESTAMP NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, date_time),
  FOREIGN KEY (user_id) REFERENCES Users(user_id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE Skins (
  skin_name   TEXT  PRIMARY KEY,
  pic_url     TEXT  UNIQUE NOT NULL,
  text_color  TEXT  NOT NULL DEFAULT '000000' -- Black color
);

CREATE TABLE HasSkin (
  user_id     TEXT    NOT NULL,
  skin_name   TEXT    NOT NULL,
  key_letter  CHAR(1) NOT NULL,
  amount      INT     NOT NULL CHECK(amount > 0),
  PRIMARY KEY (user_id, skin_name, key_letter),
  FOREIGN KEY (user_id) REFERENCES Users(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (skin_name) REFERENCES Skins(skin_name) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE OR REPLACE PROCEDURE add_user(
  user_id TEXT
) AS $$
BEGIN
  INSERT INTO Users VALUES (user_id, user_id);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_score(
  user_id TEXT, score INT
) AS $$
BEGIN
  INSERT INTO Scores VALUES (user_id, score);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_skin(
  skin_name TEXT, pic_url TEXT, text_color TEXT
) AS $$
BEGIN
  INSERT INTO Skins VALUES (skin_name, pic_url, text_color);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_has_skin(
  new_user_id TEXT, new_skin_name TEXT, new_key_letter CHAR(1),
  new_amount INT
) AS $$
BEGIN
  IF EXISTS(
    SELECT *
    FROM HasSkin
    WHERE user_id = new_user_id
    AND skin_name = new_skin_name
    AND key_letter = new_key_letter
  ) THEN
    UPDATE HasSkin
    SET amount = amount + new_amount
    WHERE user_id = new_user_id
    AND skin_name = new_skin_name
    AND key_letter = new_key_letter;
  ELSE
    INSERT INTO HasSkin VALUES (new_user_id, new_skin_name, new_key_letter, new_amount);
  END IF;
END;
$$ LANGUAGE plpgsql;

CALL add_user('alpha');
CALL add_skin('skin 1', 'url', 'color');
CALL add_has_skin('alpha', 'skin 1', 'a', 1);
CALL add_has_skin('alpha', 'skin 1', 'a', 1);
CALL add_has_skin('alpha', 'skin 1', 'b', 1);

SELECT * FROM HasSkin;

PostgreSQL online editor

Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

  • Postgres is not only free and open-source but also it is highly extensible.
  • Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
  • ACID(Atomicity, Consistency, Isolation, Durability) compliant.
  • First DBMS which implemented Multi-version concurrency control (MVCC) feature.
  • It's the default database server for MacOS.
  • It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1;

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name;

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;