MariaDB_api_CanvasGamer

/** CANVAS GAMER API DB**/

/*

  drop table adds;
  drop table belong;
  drop table user;
  drop table category;
  drop table game;


 */

CREATE TABLE `user`(
    id_user INT PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(20),
    lname VARCHAR(20),
    is_admin BOOLEAN DEFAULT FALSE,
    date_registerd DATE,
    email VARCHAR(150),
    password VARCHAR(150)

)ENGINE = InnoDB;


CREATE TABLE category(
    id_cat INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
)ENGINE = InnoDB;

CREATE TABLE game(
    id_game INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    description VARCHAR(300),
    link VARCHAR(100),
    date_release DATE,
    image VARCHAR(100)

)ENGINE = InnoDB;


CREATE TABLE belong(
    id_game INT,
    id_cat INT,
    PRIMARY KEY(id_game,id_cat),
    CONSTRAINT `fk_category_belong`
        FOREIGN KEY(id_cat) REFERENCES `category`(id_cat)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_game_belong`
        FOREIGN KEY(id_game) REFERENCES `game`(id_game)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)ENGINE = InnoDB;


CREATE TABLE adds(
    id_user INT,
    id_game INT,
    date_share DATE,
    CONSTRAINT `fk_adds_user`
        FOREIGN KEY (id_user) REFERENCES `user`(id_user)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_adds_game`
        FOREIGN KEY (id_game) REFERENCES `game`(id_game)
        ON DELETE CASCADE
        ON UPDATE CASCADE
)ENGINE = InnoDB;

/*  DATASET */

INSERT INTO db_api.`user`
(email, password, fname, lname, is_admin, date_registered)
VALUES('petko@gmail.com', 'petkopetko', 'petko', 'petkovski', 0, '2017-03-03');

INSERT INTO db_api.`user`
(email, password, fname, lname, is_admin, date_registered)
VALUES('mitko@gmail.com', 'mitkomitko', 'mitko', 'mitkovski', 0, '2016-03-03');

/*  SELECT * from `user` */

INSERT INTO db_api.game
(name, image, date_release, link, description)
VALUES('snake', 'snake.png', '2017-05-05', 'www.imagesapp.ml/img.png', 'This is pandan of the game snake in tetris.');

INSERT INTO db_api.game
(name, image, date_release, link, description)
VALUES('slime adventure', 'slime.png', '2016-03-03', 'www.imagesapp.ml/slime.png', 'This is pandan of the game super mario in nintendo.');

INSERT INTO db_api.game
(name, image, date_release, link, description)
VALUES('flappy bird', 'fbird.png', '2017-01-01', 'www.imagesapp.ml/bird.png', 'This is pandan of the game flappybird in android.');

/* SELECT * from game */

INSERT INTO db_api.category
(name)
VALUES('tetris');

INSERT INTO db_api.category
(name)
VALUES('arkade');

INSERT INTO db_api.category
(name)
VALUES('adventure');

INSERT INTO db_api.category
(name)
VALUES('action');

INSERT INTO db_api.category
(name)
VALUES('mobile-game');

INSERT INTO db_api.category
(name)
VALUES('easy');

INSERT INTO db_api.category
(name)
VALUES('real-time');

/*  SELECT * FROM category  */

INSERT INTO db_api.adds
(id_user, id_game, date_share)
VALUES(1, 1, '2016-03-03');

INSERT INTO db_api.adds
(id_user, id_game, date_share)
VALUES(1, 2, '2017-09-11');

INSERT INTO db_api.adds
(id_user, id_game, date_share)
VALUES(1, 3, '2017-07-08');

INSERT INTO db_api.adds
(id_user, id_game, date_share)
VALUES(2, 1, '2017-05-22');

INSERT INTO db_api.adds
(id_user, id_game, date_share)
VALUES(2, 2, '2017-05-10');

INSERT INTO db_api.adds
(id_user, id_game, date_share)
VALUES(2, 3, '2018-01-07');

/*  SELECT * FROM adds  */

INSERT INTO db_api.belong
(id_game, id_cat)
VALUES(1, 5);

INSERT INTO db_api.belong
(id_game, id_cat)
VALUES(1, 1);

INSERT INTO db_api.belong
(id_game, id_cat)
VALUES(1, 3);

INSERT INTO db_api.belong
(id_game, id_cat)
VALUES(2, 5);

INSERT INTO db_api.belong
(id_game, id_cat)
VALUES(2, 1);

INSERT INTO db_api.belong
(id_game, id_cat)
VALUES(3, 2);

INSERT INTO db_api.belong
(id_game, id_cat)
VALUES(3, 6);

INSERT INTO db_api.belong
(id_game, id_cat)
VALUES(3, 7);


/*  SELECT * FROM belong NATURAL JOIN category  */