pbl-api/mysql-pbl.js

// ROUTES FOR OUR API（直接执行 SQL 语句）
// =============================================================================
var express = require("express");
var request = require("request");
var bcrypt = require("bcryptjs");
var router = express.Router(); // get an instance of the express Router
const querystring = require("querystring");
var mysql = require("./mysql");

//=====================================
// 本地

// const _mysqlLabor = ["183.36.26.8", "pbl"]; //edu數據庫信息
// const _mysqluser = ["183.36.26.8", "cocorobouser"]; //用户数据库信息
// const _getmysqlLabor2 = ["183.36.26.8", "pbl"]; //edu數據庫信息
// const _getmysqlLabor = ["183.36.26.8", "pbl"]; //edu數據庫信息


//线上
const _mysqlLabor = ["172.16.12.150", "pbl"]; //edu數據庫信息
const _mysqluser = ["172.16.12.150", "cocorobouser"]; //用户数据库信息
// const _getmysqlLabor = ["172.16.12.150", "pbl"]; //edu數據庫信息
// const _getmysqluser = ["172.16.12.150", "cocorobouser"]; //用户数据库信息
//======================================

//解析 url 查询参数
function queryString(url) {
    var a = url.split("?");
    var json = querystring.parse(a[1]);
    return json;
}

//直接执行 SQL（pbl 库）
function runPbl(res, sql, params) {
    var cfg = _mysqlLabor;
    var p = [cfg[0], cfg[1], sql].concat(params || []);
    mysql.usquery(p, function (ret) {
        res.end(JSON.stringify(ret));
    });
}

//直接执行 SQL（cocorobouser 库）
function runUser(res, sql, params) {
    var cfg = _mysqluser;
    var p = [cfg[0], cfg[1], sql].concat(params || []);
    mysql.usquery(p, function (ret) {
        res.end(JSON.stringify(ret));
    });
}

/* 查询用户（按 userid） */
router.route("/selectUser").all((req, res, next) => {
    var json = queryString(req.url);
    var sql = "SELECT * FROM User WHERE userid = ?";
    runPbl(res, sql, [json["uid"]]);
});

/* 查询全部用户 */
router.route("/selectUserAll").all((req, res, next) => {
    var json = queryString(req.url);
    var page = parseInt(json["page"]) || 1;
    var pageSize = parseInt(json["pageSize"]) || 10;
    var offset = (page - 1) * pageSize;
    var sql = "SELECT * FROM User LIMIT ? OFFSET ?";
    runPbl(res, sql, [pageSize, offset]);
});

/* 修改用户 */
router.route("/updateUser").post((req, res, next) => {
    var json = req.body;
    var sql = "UPDATE User SET username = ? WHERE userid = ?";
    runPbl(res, sql, [json["username"], json["userid"]]);
});

/* sql */
router.route("/sql").post((req, res, next) => {
    var json = req.body;
    var sql = json["sql"];
    runPbl(res, sql, []);
});

module.exports = router;