odecif/bcns-gameDistributionSystem
1
0
Fork 0
Code Issues 7 Pull Requests Packages Projects Releases Wiki Activity

Set up database backend #33

New Issue
Open
opened 2024-04-09 22:46:35 +02:00 by odecif · 1 comment
odecif commented 2024-04-09 22:46:35 +02:00
Owner
Copy Link

After some consideration we should utilize the current MariaDB-server and create a database there for the content-API for storing entries.

The following should be created:

Database name: bcnsgds_[prod,test,dev]

CREATE DATABASE IF NOT EXISTS bcnsgds_dev COMMENT = "bcns-gameDistributionSystem";

USE bcnsgds_dev;

CREATE TABLE IF NOT EXISTS game (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid UUID UNIQUE,
	title NVARCHAR(200) NOT NULL,
	originalTitle NVARCHAR(200) NULL,
	plot MEDIUMTEXT CHARACTER SET utf8 NULL,
	linuxInstruction MEDIUMTEXT CHARACTER SET utf8 NULL,
	systemRequirements MEDIUMTEXT NULL,
	recommendedSpecs MEDIUMTEXT NULL,
	year TINYINT UNSIGNED NULL) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS cdkey (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	cdkey NVARCHAR(50) NOT NULL,
	description NVARCHAR(150) NULL,
	CONSTRAINT `fk_game_cdkey`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS collaborator (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(50) UNIQUE NOT NULL,
	description NVARCHAR(150) NULL) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS gameCollaborator (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	collaboratorId TINYINT UNSIGNED NOT NULL,
	gameId SMALLINT UNSIGNED NOT NULL,
	datetime DATETIME NULL,
	CONSTRAINT `fk_game_gameCollaborator`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_collaborator_gameCollaborator`
		FOREIGN KEY (collaboratorId) REFERENCES collaborator (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS company (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(150) NOT NULL,
	country NVARCHAR(100) NULL) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS gameCompanyRelation (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(50) UNIQUE NOT NULL) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS gameCompany (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	companyId SMALLINT UNSIGNED NOT NULL,
	relationId TINYINT UNSIGNED NOT NULL,
	CONSTRAINT `fk_game_gameCompany`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_company_gameCompany`
		FOREIGN KEY (companyId) REFERENCES company (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_gameCompanyRelation_gameCompany`
		FOREIGN KEY (relationId) REFERENCES gameCompanyRelation (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS ratingOrgan (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(100) NOT NULL,
	region NVARCHAR(100) NULL,
	description TEXT NULL ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS rating (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	ratingOrganId TINYINT UNSIGNED NOT NULL,
	rating NVARCHAR(20) NOT NULL,
	CONSTRAINT `fk_game_rating`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_rating_ratingOrgan`
		FOREIGN KEY (ratingOrganId) REFERENCES ratingOrgan (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS medium (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	name NVARCHAR(100) NULL,
	type TINYINT UNSIGNED NOT NULL,
	location NVARCHAR(250) NOT NULL,
	filename NVARCHAR(100) NOT NULL,
	CONSTRAINT `fk_game_medium`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS additionalFileType (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(100) UNIQUE NOT NULL ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS additionalFile (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	name NVARCHAR(100) NULL,
	typeId TINYINT UNSIGNED NOT NULL,
	location NVARCHAR(250) NOT NULL,
	filename NVARCHAR(100) NOT NULL,
	language NVARCHAR(20) NULL,
	CONSTRAINT `fk_game_additionalFile`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_additionalFile_additionalFileType`
		FOREIGN KEY (typeId) REFERENCES additionalFileType (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS artwork (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	name NVARCHAR(50) NULL,
	type NVARCHAR(50) NOT NULL,
	region NVARCHAR(100) NULL,
	location NVARCHAR(250) NOT NULL,
	filename NVARCHAR(100) NOT NULL,
	CONSTRAINT `fk_game_artwork`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS genre (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(50) UNIQUE NOT NULL ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS gameGenre (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	genreId TINYINT UNSIGNED NOT NULL,
	CONSTRAINT `fk_game_gameGenre`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_gameGenre_genre`
		FOREIGN KEY (genreId) REFERENCES genre (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS playerMode (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(50) UNIQUE NOT NULL,
	description NVARCHAR(250) NULL ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS gamePlayerMode (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	playerModeId TINYINT UNSIGNED NOT NULL,
	CONSTRAINT `fk_game_gamePlayerMode`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_gamePlayerMode_playerMode`
		FOREIGN KEY (playerModeId) REFERENCES playerMode (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;
After some consideration we should utilize the current MariaDB-server and create a database there for the content-API for storing entries. The following should be created: Database name: bcnsgds_[prod,test,dev] ``` CREATE DATABASE IF NOT EXISTS bcnsgds_dev COMMENT = "bcns-gameDistributionSystem"; USE bcnsgds_dev; CREATE TABLE IF NOT EXISTS game ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid UUID UNIQUE, title NVARCHAR(200) NOT NULL, originalTitle NVARCHAR(200) NULL, plot MEDIUMTEXT CHARACTER SET utf8 NULL, linuxInstruction MEDIUMTEXT CHARACTER SET utf8 NULL, systemRequirements MEDIUMTEXT NULL, recommendedSpecs MEDIUMTEXT NULL, year TINYINT UNSIGNED NULL) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS cdkey ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gameId SMALLINT UNSIGNED NOT NULL, cdkey NVARCHAR(50) NOT NULL, description NVARCHAR(150) NULL, CONSTRAINT `fk_game_cdkey` FOREIGN KEY (gameId) REFERENCES game (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS collaborator ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name NVARCHAR(50) UNIQUE NOT NULL, description NVARCHAR(150) NULL) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS gameCollaborator ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, collaboratorId TINYINT UNSIGNED NOT NULL, gameId SMALLINT UNSIGNED NOT NULL, datetime DATETIME NULL, CONSTRAINT `fk_game_gameCollaborator` FOREIGN KEY (gameId) REFERENCES game (id) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_collaborator_gameCollaborator` FOREIGN KEY (collaboratorId) REFERENCES collaborator (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS company ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name NVARCHAR(150) NOT NULL, country NVARCHAR(100) NULL) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS gameCompanyRelation ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name NVARCHAR(50) UNIQUE NOT NULL) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS gameCompany ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gameId SMALLINT UNSIGNED NOT NULL, companyId SMALLINT UNSIGNED NOT NULL, relationId TINYINT UNSIGNED NOT NULL, CONSTRAINT `fk_game_gameCompany` FOREIGN KEY (gameId) REFERENCES game (id) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_company_gameCompany` FOREIGN KEY (companyId) REFERENCES company (id) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_gameCompanyRelation_gameCompany` FOREIGN KEY (relationId) REFERENCES gameCompanyRelation (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS ratingOrgan ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name NVARCHAR(100) NOT NULL, region NVARCHAR(100) NULL, description TEXT NULL ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS rating ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gameId SMALLINT UNSIGNED NOT NULL, ratingOrganId TINYINT UNSIGNED NOT NULL, rating NVARCHAR(20) NOT NULL, CONSTRAINT `fk_game_rating` FOREIGN KEY (gameId) REFERENCES game (id) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_rating_ratingOrgan` FOREIGN KEY (ratingOrganId) REFERENCES ratingOrgan (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS medium ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gameId SMALLINT UNSIGNED NOT NULL, name NVARCHAR(100) NULL, type TINYINT UNSIGNED NOT NULL, location NVARCHAR(250) NOT NULL, filename NVARCHAR(100) NOT NULL, CONSTRAINT `fk_game_medium` FOREIGN KEY (gameId) REFERENCES game (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS additionalFileType ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name NVARCHAR(100) UNIQUE NOT NULL ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS additionalFile ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gameId SMALLINT UNSIGNED NOT NULL, name NVARCHAR(100) NULL, typeId TINYINT UNSIGNED NOT NULL, location NVARCHAR(250) NOT NULL, filename NVARCHAR(100) NOT NULL, language NVARCHAR(20) NULL, CONSTRAINT `fk_game_additionalFile` FOREIGN KEY (gameId) REFERENCES game (id) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_additionalFile_additionalFileType` FOREIGN KEY (typeId) REFERENCES additionalFileType (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS artwork ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gameId SMALLINT UNSIGNED NOT NULL, name NVARCHAR(50) NULL, type NVARCHAR(50) NOT NULL, region NVARCHAR(100) NULL, location NVARCHAR(250) NOT NULL, filename NVARCHAR(100) NOT NULL, CONSTRAINT `fk_game_artwork` FOREIGN KEY (gameId) REFERENCES game (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS genre ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name NVARCHAR(50) UNIQUE NOT NULL ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS gameGenre ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gameId SMALLINT UNSIGNED NOT NULL, genreId TINYINT UNSIGNED NOT NULL, CONSTRAINT `fk_game_gameGenre` FOREIGN KEY (gameId) REFERENCES game (id) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_gameGenre_genre` FOREIGN KEY (genreId) REFERENCES genre (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS playerMode ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name NVARCHAR(50) UNIQUE NOT NULL, description NVARCHAR(250) NULL ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS gamePlayerMode ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gameId SMALLINT UNSIGNED NOT NULL, playerModeId TINYINT UNSIGNED NOT NULL, CONSTRAINT `fk_game_gamePlayerMode` FOREIGN KEY (gameId) REFERENCES game (id) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_gamePlayerMode_playerMode` FOREIGN KEY (playerModeId) REFERENCES playerMode (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB; ```
odecif commented 2024-04-10 01:36:56 +02:00
Author
Owner
Copy Link

Currently not certain if game should use int or uuid as primary key (pk). Most likely uuid will only be used when communicating outwards and a SMALLINT will be used for pk for performance reasons.

Currently not certain if `game` should use int or uuid as primary key (pk). Most likely uuid will only be used when communicating outwards and a SMALLINT will be used for pk for performance reasons.
odecif referenced this issue from a commit 2024-04-10 01:41:25 +02:00
Created SQL-scripts
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
No items
No Label
Milestone
Clear milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: odecif/bcns-gameDistributionSystem#33
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?