azures04
dbcb436c9f
Removed the 'unique_active_skin' database trigger from setupDatabase and updated setSkin in userRepository to handle skin selection logic in application code. This change centralizes the logic for ensuring only one active skin per user, improving maintainability and error handling.
387 lines
17 KiB
JavaScript
387 lines
17 KiB
JavaScript
const path = require("node:path")
|
|
const mariadb = require("mariadb")
|
|
const logger = require("./logger")
|
|
const crypto = require("node:crypto")
|
|
|
|
const rootConfig = {
|
|
host: process.env.DATABASE_HOST,
|
|
user: process.env.DATABASE_USER,
|
|
password: process.env.DATABASE_PASSWORD,
|
|
connectionLimit: 1,
|
|
connectTimeout: 20000
|
|
}
|
|
|
|
async function setupDatabase() {
|
|
let conn
|
|
|
|
try {
|
|
conn = await mariadb.createConnection(rootConfig)
|
|
await conn.query(`CREATE DATABASE IF NOT EXISTS \`${process.env.DATABASE_NAME}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`)
|
|
logger.log(`Base de données '${process.env.DB_NAME}' vérifiée.`, ["MariaDB", "yellow"])
|
|
await conn.end()
|
|
|
|
conn = await mariadb.createConnection({
|
|
...rootConfig,
|
|
database: process.env.DB_NAME,
|
|
multipleStatements: true
|
|
})
|
|
logger.log("Checking and synchronising the schema...", ["MariaDB", "yellow"])
|
|
await conn.query(`USE \`${process.env.DATABASE_NAME}\``)
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS players (
|
|
email VARCHAR(255) NULL UNIQUE,
|
|
username VARCHAR(16) NOT NULL UNIQUE,
|
|
password TEXT NOT NULL,
|
|
uuid VARCHAR(36) NOT NULL UNIQUE PRIMARY KEY,
|
|
nameChangeAllowed TINYINT(1) DEFAULT 1,
|
|
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`)
|
|
logger.log(`${"players".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS playersProperties (
|
|
name VARCHAR(256) NOT NULL,
|
|
value VARCHAR(512) NOT NULL,
|
|
uuid VARCHAR(36) NOT NULL,
|
|
UNIQUE KEY uniqueProperty (uuid, name),
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"playersProperties".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS clientSessions (
|
|
accessToken TEXT NOT NULL,
|
|
clientToken VARCHAR(36) NOT NULL,
|
|
uuid VARCHAR(36) NOT NULL,
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid)
|
|
)
|
|
`)
|
|
logger.log(`${"clientSessions".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS legacyClientSessions (
|
|
sessionId VARCHAR(36) NOT NULL,
|
|
uuid VARCHAR(36) NOT NULL,
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"legacyClientSessions".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS uuidToNameHistory (
|
|
uuid VARCHAR(36) NOT NULL,
|
|
username VARCHAR(255) NOT NULL,
|
|
changedAt DATETIME NULL,
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"uuidToNameHistory".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`CREATE INDEX IF NOT EXISTS idx_uuidToNameHistory_uuid ON uuidToNameHistory (uuid)`)
|
|
logger.log(`${"idx_uuidToNameHistory_uuid".bold} index ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`DROP TRIGGER IF EXISTS log_new_user_name`)
|
|
await conn.query(`
|
|
CREATE TRIGGER log_new_user_name
|
|
AFTER INSERT ON players
|
|
FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO uuidToNameHistory (uuid, username, changedAt)
|
|
VALUES (NEW.uuid, NEW.username, NULL);
|
|
END;
|
|
`)
|
|
logger.log(`${"log_new_user_name".bold} trigger ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`DROP TRIGGER IF EXISTS log_user_name_change`)
|
|
await conn.query(`
|
|
CREATE TRIGGER log_user_name_change
|
|
AFTER UPDATE ON players
|
|
FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.username != NEW.username THEN
|
|
INSERT INTO uuidToNameHistory (uuid, username, changedAt)
|
|
VALUES (NEW.uuid, NEW.username, CURRENT_TIMESTAMP);
|
|
END IF;
|
|
END;
|
|
`)
|
|
logger.log(`${"log_user_name_change".bold} trigger ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS blockedServers (
|
|
hashedIp VARCHAR(40) NOT NULL PRIMARY KEY,
|
|
banner VARCHAR(256) DEFAULT 'CONSOLE',
|
|
reason VARCHAR(512) NULL
|
|
)
|
|
`)
|
|
logger.log(`${"blockedServers".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS playersPrivileges (
|
|
uuid VARCHAR(36) PRIMARY KEY,
|
|
onlineChat TINYINT(1) DEFAULT 1,
|
|
multiplayerServer TINYINT(1) DEFAULT 1,
|
|
multiplayerRealms TINYINT(1) DEFAULT 1,
|
|
telemetry TINYINT(1) DEFAULT 1,
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"playersPrivileges".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS playersPreferences (
|
|
uuid VARCHAR(36) PRIMARY KEY,
|
|
profanityFilter TINYINT(1) DEFAULT 0,
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"playersPreferences".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS banReasons (
|
|
id INTEGER PRIMARY KEY AUTO_INCREMENT,
|
|
reasonKey VARCHAR(512) UNIQUE NOT NULL
|
|
)
|
|
`)
|
|
logger.log(`${"banReasons".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS bans (
|
|
banId VARCHAR(512) PRIMARY KEY NOT NULL,
|
|
uuid VARCHAR(36) NOT NULL,
|
|
expires BIGINT DEFAULT NULL,
|
|
reason INTEGER NOT NULL,
|
|
reasonMessage VARCHAR(1024) DEFAULT NULL,
|
|
FOREIGN KEY(reason) REFERENCES banReasons(id),
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"bans".bold} table ready`, ["MariaDB", "yellow"])
|
|
await conn.query(`CREATE INDEX IF NOT EXISTS idx_bans_uuid ON bans (uuid)`)
|
|
logger.log(`${"idx_bans_uuid".bold} index ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`DROP TRIGGER IF EXISTS auto_init_player_settings`)
|
|
await conn.query(`
|
|
CREATE TRIGGER auto_init_player_settings
|
|
AFTER INSERT ON players
|
|
FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO playersPrivileges (uuid) VALUES (NEW.uuid);
|
|
INSERT INTO playersPreferences (uuid) VALUES (NEW.uuid);
|
|
END;
|
|
`)
|
|
logger.log(`${"auto_init_player_settings".bold} trigger ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS playersBlockslist (
|
|
blockerUuid VARCHAR(36) NOT NULL,
|
|
blockedUuid VARCHAR(36) NOT NULL,
|
|
PRIMARY KEY (blockerUuid, blockedUuid),
|
|
FOREIGN KEY (blockerUuid) REFERENCES players(uuid) ON DELETE CASCADE,
|
|
FOREIGN KEY (blockedUuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"playersBlockslist".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS usernameRules (
|
|
id INTEGER PRIMARY KEY AUTO_INCREMENT,
|
|
rule VARCHAR(255) NOT NULL,
|
|
type INTEGER DEFAULT 0
|
|
)
|
|
`)
|
|
logger.log(`${"usernameRules".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS textures (
|
|
uuid VARCHAR(36) NOT NULL UNIQUE,
|
|
hash VARCHAR(64) PRIMARY KEY NOT NULL,
|
|
type VARCHAR(10) NOT NULL,
|
|
url VARCHAR(2048) NOT NULL,
|
|
alias VARCHAR(64) NULL,
|
|
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`)
|
|
logger.log(`${"textures".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS playersSkins (
|
|
playerUuid VARCHAR(36) NOT NULL,
|
|
assetHash VARCHAR(64) NOT NULL,
|
|
variant VARCHAR(10) DEFAULT "CLASSIC",
|
|
isSelected TINYINT(1) DEFAULT 0,
|
|
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (playerUuid, assetHash),
|
|
FOREIGN KEY (playerUuid) REFERENCES players(uuid) ON DELETE CASCADE,
|
|
FOREIGN KEY (assetHash) REFERENCES textures(hash)
|
|
)
|
|
`)
|
|
logger.log(`${"playersSkins".bold} table ready`, ["MariaDB", "yellow"])
|
|
await conn.query(`CREATE INDEX IF NOT EXISTS idx_active_skin ON playersSkins (playerUuid, isSelected)`)
|
|
logger.log(`${"idx_active_skin".bold} index ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS playersCapes (
|
|
playerUuid VARCHAR(36) NOT NULL,
|
|
assetHash VARCHAR(64) NOT NULL,
|
|
isSelected TINYINT(1) DEFAULT 0,
|
|
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (playerUuid, assetHash),
|
|
FOREIGN KEY (playerUuid) REFERENCES players(uuid) ON DELETE CASCADE,
|
|
FOREIGN KEY (assetHash) REFERENCES textures(hash) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"playersCapes".bold} table ready`, ["MariaDB", "yellow"])
|
|
await conn.query(`CREATE INDEX IF NOT EXISTS idx_active_cape ON playersCapes (playerUuid, assetHash)`)
|
|
logger.log(`${"idx_active_cape".bold} index ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`INSERT IGNORE INTO textures (hash, type, url, uuid) VALUES ('df8ed96c557d441a63e7b6a4a911ab84fa453b42fc2ae6b01c3e1b02e138168c', 'SKIN', '/texture/alex.png', '${crypto.randomUUID()}')`)
|
|
await conn.query(`INSERT IGNORE INTO textures (hash, type, url, uuid) VALUES ('4c7b0468044bfecacc43d00a3a69335a834b73937688292c20d3988cae58248d', 'SKIN', '/texture/steve.png', '${crypto.randomUUID()}')`)
|
|
logger.log(`defaults skins (steve, alex) ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`DROP TRIGGER IF EXISTS unique_active_skin`)
|
|
|
|
await conn.query(`DROP TRIGGER IF EXISTS unique_active_cape`)
|
|
|
|
await conn.query(`DROP TRIGGER IF EXISTS auto_assign_random_default_skin`)
|
|
await conn.query(`
|
|
CREATE TRIGGER auto_assign_random_default_skin
|
|
AFTER INSERT ON players
|
|
FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO playersSkins (playerUuid, assetHash, variant, isSelected)
|
|
SELECT
|
|
NEW.uuid,
|
|
hash,
|
|
CASE
|
|
WHEN hash = '4c7b0468044bfecacc43d00a3a69335a834b73937688292c20d3988cae58248d' THEN 'CLASSIC'
|
|
ELSE 'SLIM'
|
|
END,
|
|
1
|
|
FROM textures
|
|
WHERE hash IN (
|
|
'4c7b0468044bfecacc43d00a3a69335a834b73937688292c20d3988cae58248d',
|
|
'df8ed96c557d441a63e7b6a4a911ab84fa453b42fc2ae6b01c3e1b02e138168c'
|
|
)
|
|
ORDER BY RAND()
|
|
LIMIT 1;
|
|
END;
|
|
`)
|
|
logger.log(`${"auto_assign_random_default_skin".bold} trigger ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS playerCertificates (
|
|
uuid VARCHAR(36) PRIMARY KEY,
|
|
privateKey TEXT NOT NULL,
|
|
publicKey TEXT NOT NULL,
|
|
publicKeySignatureV2 TEXT NOT NULL,
|
|
expiresAt DATETIME NOT NULL,
|
|
refreshedAfter DATETIME NOT NULL,
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"playerCertificates".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS playerProfileActions (
|
|
uuid VARCHAR(36) NOT NULL,
|
|
action VARCHAR(64) NOT NULL,
|
|
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (uuid, action),
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"playerProfileActions".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS serverSessions (
|
|
uuid VARCHAR(36) PRIMARY KEY,
|
|
accessToken TEXT NOT NULL,
|
|
serverId VARCHAR(255) NOT NULL,
|
|
ip VARCHAR(45) NULL,
|
|
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (uuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"serverSessions".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
try {
|
|
await conn.query(`SET GLOBAL event_scheduler = ON;`)
|
|
logger.log("MySQL Event Scheduler enabled.", ["MySQL", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE EVENT IF NOT EXISTS clean_expired_certificates
|
|
ON SCHEDULE EVERY 1 HOUR
|
|
DO
|
|
DELETE FROM playerCertificates WHERE expiresAt < NOW();
|
|
`)
|
|
logger.log(`${"clean_expired_certificates".bold} event ready`, ["MySQL", "yellow"])
|
|
} catch (e) {
|
|
logger.log("Warning: Could not enable Event Scheduler (permission issue?). Skipping event creation.", ["MySQL", "red"])
|
|
}
|
|
|
|
await conn.query(`
|
|
CREATE EVENT IF NOT EXISTS clean_expired_certificates
|
|
ON SCHEDULE EVERY 1 HOUR
|
|
DO
|
|
DELETE FROM playerCertificates WHERE expiresAt < NOW();
|
|
`)
|
|
logger.log(`${"clean_expired_certificates".bold} event ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS apiAdministrators (
|
|
id INTEGER PRIMARY KEY AUTO_INCREMENT,
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
|
password TEXT NOT NULL,
|
|
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`)
|
|
logger.log(`${"apiAdministrators".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS apiAdministratorsPermissionsList (
|
|
permissionKey VARCHAR(64) PRIMARY KEY
|
|
)
|
|
`)
|
|
logger.log(`${"apiAdministratorsPermissionsList".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`INSERT IGNORE INTO apiAdministratorsPermissionsList(permissionKey) VALUES ("RESET_PLAYER_SKIN"), ("GRANT_PLAYER_CAPE"), ("REMOVE_PLAYER_CAPE"), ("CHANGE_PLAYER_USERNAME"), ("CHANGE_PLAYER_PASSWORD"), ("UPLOAD_CAPE"), ("DELETE_CAPES"), ("PLAYER_BAN"), ("PLAYER_BAN_HISTORY"), ("PLAYER_BAN_STATUS"), ("PLAYER_ACTIONS_LIST"), ("PLAYER_UNBAN"), ("REGISTER_USER")`)
|
|
logger.log(`${"apiAdministratorsPermissionsList".bold} permissions ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS apiAdministratorsPermissions (
|
|
administratorId INTEGER NOT NULL,
|
|
permissionKey VARCHAR(64) NOT NULL,
|
|
PRIMARY KEY (administratorId, permissionKey),
|
|
FOREIGN KEY (administratorId) REFERENCES apiAdministrators(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (permissionkey) REFERENCES apiAdministratorsPermissionsList(permissionKey) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"apiAdministratorsPermissions".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS oaauth2LinkAttempts (
|
|
OAuth2LinkId VARCHAR(255) NOT NULL,
|
|
playerUuid VARCHAR(255) NOT NULL,
|
|
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (playerUuid) REFERENCES players(uuid) ON DELETE CASCADE
|
|
)
|
|
`)
|
|
logger.log(`${"oaauth2LinkAttempts".bold} table ready`, ["MariaDB", "yellow"])
|
|
|
|
logger.log("MariaDB database successfully initialised!", ["MariaDB", "yellow"])
|
|
|
|
} catch (err) {
|
|
logger.error("Critical error during DB initialisation: " + err.message, ["MariaDB", "yellow"])
|
|
console.error(err)
|
|
process.exit(1)
|
|
} finally {
|
|
if (conn) conn.end()
|
|
}
|
|
}
|
|
|
|
module.exports = {
|
|
setupDatabase
|
|
} |