Shooty-Arena/web/database/database.js

// Strict Mode
"use strict";

// Imports
import mysql from "mysql";

// Connection
const connection = mysql.createConnection({
    host: "127.0.0.1",
    user: process.env.DATABASE_USER,
    password: process.env.DATABASE_PASSWORD,
    database: "shootyarena"
});

// Connect
connection.connect(( error ) => {
    console.log(error)
    if (error) throw error;
});

// Create Tables
async function createTables () {
    connection.query("CREATE table users ( username VARCHAR(10) NOT NULL UNIQUE PRIMARY KEY, email VARCHAR(331) NOT NULL UNIQUE, passphrase VARCHAR(60) NOT NULL, active VARCHAR(60) NOT NULL, lastLoginTime INT NOT NULL );", ( error, _ ) => {
        if (error) throw error;
    });
    connection.query("CREATE TABLE stats ( username VARCHAR(10) NOT NULL UNIQUE PRIMARY KEY, FOREIGN KEY(username) REFERENCES users(username), gameRank VARCHAR(13) NOT NULL, elo INT NOT NULL, kills INT NOT NULL, deaths INT NOT NULL );", ( error, _ ) => {
        if (error) throw error;
    });
    connection.query("CREATE TABLE servers ( address VARCHAR(45) UNIQUE, playerLimit INT NOT NULL, playerCount INT NOT NULL );", ( error, _ ) => {
        if (error) throw error;
    });
    connection.query("CREATE TABLE codes ( username VARCHAR(10) NOT NULL UNIQUE PRIMARY KEY, FOREIGN KEY(username) REFERENCES users(username), code INT NOT NULL );", ( error, _ ) => {
        if (error) throw error;
    })
};

// Drop Tables
async function dropTables () {
    connection.query("DROP TABLE servers;", ( error, _ ) => {
        if (error) throw error;
    });
    connection.query("DROP TABLE stats;", ( error, _ ) => {
        if (error) throw error;
    });
    connection.query("DROP TABLE codes;", ( error, _ ) => {
        if (error) throw error;
    });
    connection.query("DROP TABLE users;", ( error, _ ) => {
        if (error) throw error;
    });
};

// Create User
async function createUser ( username, email, passphrase, active ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("INSERT INTO users ( username, email, passphrase, active, lastLoginTime ) VALUES ?;", [[[ username, email, passphrase, active, 0 ]]], ( error, _ ) => {
            if (error) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Create Stat
async function createStat ( username ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("INSERT INTO stats ( username, gameRank, elo, kills, deaths ) VALUES ?;", [[[ username, "Shitty Shooty", 800, 0, 0 ]]], ( error, _ ) => {
            if (error) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Create Server
async function createServer ( address, playerLimit ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("INSERT INTO servers ( address, playerLimit, playerCount ) VALUES ?;", [[[ address, playerLimit, 0 ]]], ( error, _ ) => {
            if (error) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Create Code
async function createCode ( username, code ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("INSERT INTO codes ( username, code ) VALUES ?;", [[[ username, code ]]], ( error, _ ) => {
            if (error) {
                resolve(false);
            } else {
                resolve(true);
            }
        })
    })
}

// Delete Server
async function deleteServer ( address ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("DELETE FROM servers WHERE address = ?;", [[ address ]], ( error, _ ) => {
            if (error) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Username Exists
async function usernameExists ( username ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT username FROM users WHERE username = ?;", [[ username ]], ( error, result ) => {
            console.log(error, result)
            if (error || result[0] !== undefined) {
                resolve(true);
            } else {
                resolve(false);
            };
        });
    });
};

// Email Exists
async function emailExists ( email ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT email FROM users WHERE email = ?;", [[ email ]], ( error, result ) => {
            if (error || result[0] !== undefined) {
                resolve(true);
            } else {
                resolve(false);
            };
        });
    });
};

// Active Exists
async function activeExists ( active ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT active FROM users WHERE active = ?;", [[ active ]], ( error, result ) => {
            if (error || result[0] !== undefined) {
                resolve(true);
            } else {
                resolve(false);
            };
        });
    });
};

// Activate User
async function activateUser ( active ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("UPDATE users SET active = '' WHERE active = ?;", [[ active ]], ( error, result ) => {
            if (error || result.affectedRows === 0) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Login User
async function loginUser ( email ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT passphrase FROM users WHERE email = ?;", [[ email ]], ( error, result ) => {
            if (error || result[0] === undefined) {
                resolve(false);
            } else {
                resolve(result[0]);
            };
        });
    });
};

// Get All Stats
async function getAllStats () {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT * FROM stats ORDER BY elo DESC;", ( error, result ) => {
            if (error) {
                resolve(false);
            } else {
                resolve(result);
            };
        });
    });
};

// Get Stats
async function getStats ( username ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT gameRank, elo, kills, deaths FROM stats WHERE username = ?;", [[ username ]], ( error, result ) => {
            if (error || result[0] === undefined) {
                resolve(false);
            } else {
                resolve(result);
            };
        });
    });
};

// Update Game Rank
async function updateGameRank ( username, gameRank ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("UPDATE stats SET gameRank = ? WHERE username = ?;", [[ gameRank ], [ username ]], ( error, result ) => {
            if (error || result.affectedRows === 0) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Update Elo
async function updateElo ( username, elo ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("UPDATE stats SET elo = ? WHERE username = ?;", [[ elo ], [ username ]], ( error, result ) => {
            if (error || result.affectedRows === 0) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Update Kills
async function updateKills ( username, kills ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("UPDATE stats SET kills = ? WHERE username = ?;", [[ kills ], [ username ]], ( error, result ) => {
            if (error || result.affectedRows === 0) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Update Deaths
async function updateDeaths ( username, deaths ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("UPDATE stats SET deaths = ? WHERE username = ?;", [[ deaths ], [ username ]], ( error, result ) => {
            if (error || result.affectedRows === 0) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Get Free Server
async function getFreeServer () {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT address FROM servers WHERE playerCount < playerLimit ORDER BY  playerCount DESC;", ( error, result ) => {
            if (error || result[0] === undefined) {
                resolve(false);
            } else {
                resolve(result[0]);
            };
        });
    });
};

// Set Player Count
async function setPlayerCount ( address, playerCount ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("UPDATE servers SET playerCount = ? WHERE address = ?;", [[ playerCount ], [ address ]], ( error, _ ) => {
            if (error || result.affectedRows === 0) {
                resolve(false);
            } else {
                resolve(true);
            };
        });
    });
};

// Get Active
async function getActive ( email ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT active FROM users WHERE email = ?;", [[email]], ( error, result ) => {
            if (error == null && result[0].active == "") {
                resolve(true);
            } else {
                resolve(result[0].active);
            }
        });
    });
};

// Get Username
async function getUsername ( email ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT username FROM users WHERE email = ?;", [[email]], ( error, result ) => {
            if (error || result[0] === undefined) {
                resolve(false);
            } else {
                resolve(result[0].username)
            }
        })
    })
}

// Get Code
async function getCode ( username ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("SELECT code FROM codes WHERE username = ?;", [[ username ]], ( error, result ) => {
            if (error || result[0] === undefined) {
                resolve(false);
            } else {
                resolve(result[0].code);
            }
        })
    })
}

// Set Code
async function setCode ( username, code ) {
    return new Promise (async ( resolve, _ ) => {
        connection.query("UPDATE codes SET code = ? WHERE username = ?", [[code], [username]], ( error, result ) => {
            if (error) {
                resolve(false);
            } else {
                resolve(true);
            }
        })
    })
}


// Exports
module.exports = {
    createTables,
    dropTables,
    createUser,
    createStat,
    createServer,
    deleteServer,
    usernameExists,
    emailExists,
    activeExists,
    activateUser,
    loginUser,
    getAllStats,
    getStats,
    updateGameRank,
    updateElo,
    updateKills,
    updateDeaths,
    getFreeServer,
    setPlayerCount,
    getActive,
    getUsername,
    createCode,
    getCode,
    setCode,

};