sql-request.js

const mysql = require('mysql');

var con = mysql.createConnection({
    host: "127.0.0.1",
    port: 3306,
    user: "hyperdrive",
    password: "hyper",
    database: 'hyperdrive',
    insecureAuth: true
});

/**
 * This function check if a user exist in the DB
 * @param {string} login This is the login of the user
 * @param {string} pass This is the password of the user
 */
function userExist(login, pass, callback){
    const q = `SELECT login, passwd FROM Users WHERE login='${login}' AND passwd='${pass}';`;
    con.query(q, function (err, result) {
        if (err) return false;
        if (result.length > 0) {
            console.log("user exists");
            return callback({
                login: result[0]['login'],
                passwd: result[0]['passwd'].hashCode()
            });
        } else {
            console.log("user don't exists");
            return callback(false);
        }
    });
}

/**
 * This function insert a new user in the DB
 * @param {string} login This is the login of the user
 * @param {string} pass This is the password of the user
 */
function insertUser(login, passwd, callback){
    let q = `INSERT INTO Users VALUES ('${login}', '${passwd}');`;
    userExist(login, passwd, (userExist) => {
        if (!userExist){
            con.query(q, (err, res) => {
                if (err) return callback(false);
                if (!res) {console.log(res);return callback(false);}
                console.log("User", login, "inserted in the db");
                return callback(true);
            });
        }
    });
}

/**
 * This function add a new directory to a user
 * @param {string} path Path to add
 * @param {string} login User's path
 * @param {string} parent Parent of the new folder
 */
function addPath(path, login, parent, callback){
    let q = `INSERT INTO Paths VALUES ('${path}', '${login}', ${parent});`;
    con.query(q, function(err, res) {
        if (err) {
            console.log("Error while adding a new path");
            console.log(err);
            return callback(false);
        }
        console.log("New path", path, "added succesfully !");
        return callback(true);
    });
}

/**
 * This function add a user in the db it it doesn't already exists
 * It also add the default path of the user after inserting the new user.
 * @param {string} login This is the login of the user
 * @param {string} pass This is the password of the user
 */
function addUser(login, passwd) {

    insertUser(login, passwd, (insertOk) => {
        console.log(insertOk);
        if (insertOk) {
            addPath('/'+login, login, null, (res)=>{});
            console.log("Add ok");
        }
    });
}


/**
 * This function return all the element in a directory
 * @param {string} login Login of the user
 * @param {string} path Path to show content
 * @param {string} callback callback function
 */
async function changeDirectory(login, path, callback){
    q = `
        SELECT Paths.paths, login, parent, Files.file_id, Files.file_name
        FROM Paths
        LEFT JOIN Files ON Files.paths = Paths.paths
        WHERE Paths.login='${login}'
        AND Paths.paths='${path}'
        OR Paths.parent='${path}';`;

    con.query(q, (err, res) => {
        if (err) { 
            console.log("Error while loading the path");
            return callback(false, err);
        }

        let content = [];
        for (let i in res){
            content.push({
                paths: res[i].paths,
                login: res[i].login,
                parent: res[i].parent,
                file_id: res[i].file_id,
                file_name: res[i].file_name
            });
        }

        return callback(content, "Change dir success");
    });
}

// verify if a file_id is at a user
function verify_user_file_id(file_id, login){

    return new Promise(resolve => {
        let q = `SELECT (login) FROM Files as F LEFT JOIN Paths as P ON F.paths = P.paths WHERE F.file_id = '${file_id}';`
        con.query(q, function(err, res) {
            if (err) {
                console.log("Error while veryfing file_id for user");
                console.log(err);
                resolve(false);
            }
            console.log(res.length)
            // return new Promise(resolve => resolve("resolved"));
            if (res.length > 0){
                if (res[0].login == login)
                    resolve(true);
                resolve(false);
            }
            resolve(false);
        });
    });
}

async function addSharing(login, to_user, file_id){

    verif = await verify_user_file_id(file_id, login);

    if (verif){
        let q = `INSERT INTO Shares VALUES ('${login}', '${to_user}', '${file_id}');`;
        con.query(q, function(err, res) {
            if (err) {
                console.log("Error while adding a share path");
                console.log(err);
                return "Error sharing - (DB error)";
            }
        });
        // return "New sharing (", login, " --> " + to_user + "( - file_id : " + file_id + ")) added succesfully !";
        return "Sharing ok."
    }
    else{
        return "Unable to share, this is not your file.";
    }
}

async function verifyFileID(login, file_id, callback){
    let q = `SELECT (file_name) FROM Files as F LEFT JOIN Paths as P ON F.paths = P.paths WHERE F.file_id = '${file_id}' AND P.login = '${login}';`

    con.query(q, (err, res) => {
        if (err) { 
            console.log("Error while verifying file_id");
            return callback(false);
        }

        console.log(res)
        if (res.length > 0)
            filename = res[0].file_name;
        else
            filename = false;

        return callback(filename);
    });
}

function createPath(path, user, callback) {
    let parent = path.split("/");
    parent.pop();
    parent = parent.join("/");

    q = `INSERT INTO Paths VALUES ('${path}', '${user}', ${parent})`;
    con.query(q, (err, resp) => {
        if (err) return callback(false, err);
        return callback(true, resp);
    });
}

exports.userExist = userExist;
exports.addUser = addUser;
exports.addPath = addPath;
exports.addSharing = addSharing;
exports.changeDirectory = changeDirectory;
exports.createPath = createPath;
exports.verifyFileID = verifyFileID;