Mlab 87

clean up the codes

config/dbconn.ts

-import mysql from 'mysql'
+import mysql from 'mysql2'
 
 var env = process.env.NODE_ENV || 'testing'
 const config = require('./config')[env]

config/dbconn2.ts

-import mysql from 'mysql2'
-
-var env = process.env.NODE_ENV || 'testing'
-const config = require('./config')[env]
-
-// ==== USER ACOOUNT DB CONNECTION ====
-var userConnection = mysql.createConnection({
-    host: config.database.host,
-    user: config.database.user,
-    password: config.database.password,
-    port: config.database.port,
-    database: config.database.dbUser,
-    multipleStatements: true
-})
-
-userConnection.connect(function(err) {
-    if (err) throw err
-})
-userConnection.query('USE '+config.database.dbUser)
-
-// ALTERNATIVE approach: close db connection manually after every query
-/*
-var dbconn = function dbconn(query, values, next) {
-    var connection = mysql.createConnection({
-        host: config.database.host,
-        user: config.database.user,
-        password: config.database.password,
-        port: config.database.port,
-        database: config.database.db
-    })
-    connection.connect(function(err) {
-        if (err) throw err;
-    })
-    connection.query(query, values, function(err) {
-        connection.end(); // close the connection
-        if (err) {
-            throw err;
-        }
-        // Execute the callback
-        next.apply(this, arguments);
-    });
-}
-*/
-
-// ==== PROJECT DB CONNECTION ====
-var projectConnection = mysql.createConnection({
-    host: config.database.host_project,
-    user: config.database.user,
-    password: config.database.password,
-    port: config.database.port,
-    database: config.database.dbProject
-})
-
-projectConnection.connect(function(err) {
-    if (err) throw err
-})
-projectConnection.query('USE '+config.database.dbProject)
-
-var connection = {
-    user: userConnection,
-    project: projectConnection
-}
-
-export = connection
\ No newline at end of file

functions/methods.ts

-import dbconn_OBSOLETE = require('../config/dbconn') // DO NOT USE THIS FOR NEW FUNCTIONS
-import dbconn = require('../config/dbconn2')
+import dbconn = require('../config/dbconn')
 
 var methods = {
     // ===================== user db =====================
     registerNewUser: function(data:any, callback:any) {
-        dbconn_OBSOLETE.user.beginTransaction(function(err:any) { // START TRANSACTION
+        dbconn.user.beginTransaction(function(err:any) { // START TRANSACTION
             if (err) { throw err }
             // insert profile
-            dbconn_OBSOLETE.user.query('INSERT INTO user SET ?', data.profile, function (err:any, results:any, fields:any) {
+            dbconn.user.query('INSERT INTO user SET ?', data.profile, function (err:any, results:any, fields:any) {
                 if (err) {
-                  return dbconn_OBSOLETE.user.rollback(function() {
+                  return dbconn.user.rollback(function() {
                     throw err
                   });
                 }
@@ -19,9 +18,9 @@ var methods = {
                     user_id: newUserId,
                     password: data.password
                 }
-                dbconn_OBSOLETE.user.query('INSERT INTO credential SET ?', credentialData, function (err:any, results:any, fields:any) {
+                dbconn.user.query('INSERT INTO credential SET ?', credentialData, function (err:any, results:any, fields:any) {
                     if (err) {
-                        return dbconn_OBSOLETE.user.rollback(function() {
+                        return dbconn.user.rollback(function() {
                             throw err
                         });
                     }
@@ -31,9 +30,9 @@ var methods = {
                         role_id: 2,     // USER
                         user_id: newUserId
                     }
-                    dbconn_OBSOLETE.user.query('INSERT INTO user_project_role SET ?', projectRoleData, function (err:any, results:any, fields:any) {
+                    dbconn.user.query('INSERT INTO user_project_role SET ?', projectRoleData, function (err:any, results:any, fields:any) {
                         if (err) {
-                            return dbconn_OBSOLETE.user.rollback(function() {
+                            return dbconn.user.rollback(function() {
                                 throw err
                             });
                         }
@@ -42,16 +41,16 @@ var methods = {
                             user_id: newUserId,
                             token: data.verificationToken
                         }
-                        dbconn_OBSOLETE.user.query('INSERT INTO verification SET ?', verificationData, function (err:any, results:any, fields:any) {
+                        dbconn.user.query('INSERT INTO verification SET ?', verificationData, function (err:any, results:any, fields:any) {
                             if (err) {
-                                return dbconn_OBSOLETE.user.rollback(function() {
+                                return dbconn.user.rollback(function() {
                                     throw err
                                 });
                             }
                             // COMMIT
-                            dbconn_OBSOLETE.user.commit(function(err:any) {
+                            dbconn.user.commit(function(err:any) {
                                 if (err) {
-                                    return dbconn_OBSOLETE.user.rollback(function() {
+                                    return dbconn.user.rollback(function() {
                                         throw err
                                     })
                                 }
@@ -131,11 +130,6 @@ var methods = {
         return null
     },
     addUserProjectRole_OBSOLETE: function(data:any, callback:any) {
-        /*
-        dbconn_OBSOLETE.user.query('INSERT INTO user_project_role SET ?', data, function (err:any, results:any, fields:any){
-            if (err) throw err
-            callback(err)
-        }) */
         dbconn.user.query('INSERT INTO user_project_role SET ?', data, function (err:any){
             if (err) throw err
             callback(err)
@@ -168,22 +162,22 @@ var methods = {
         return null
     },
     verifyUserAccount: function(userData:any, callback:any) {
-        dbconn_OBSOLETE.user.beginTransaction(function(err:any) { // START TRANSACTION
+        dbconn.user.beginTransaction(function(err:any) { // START TRANSACTION
             if (err) { throw err }
             // update user status
-            dbconn_OBSOLETE.user.query('UPDATE user SET ? WHERE id =' +userData.id, userData, function (err:any, rows:any, fields:any) {
+            dbconn.user.query('UPDATE user SET ? WHERE id =' +userData.id, userData, function (err:any, rows:any, fields:any) {
                 if (err) {
-                    return dbconn_OBSOLETE.user.rollback(function() { throw err })
+                    return dbconn.user.rollback(function() { throw err })
                 }
                 // delete verification token
-                dbconn_OBSOLETE.user.query('DELETE FROM verification WHERE user_id = '+userData.id, function (err:any, rows:any, fields:any) {
+                dbconn.user.query('DELETE FROM verification WHERE user_id = '+userData.id, function (err:any, rows:any, fields:any) {
                     if (err) {
-                        return dbconn_OBSOLETE.user.rollback(function() { throw err })
+                        return dbconn.user.rollback(function() { throw err })
                     }
                     // COMMIT
-                    dbconn_OBSOLETE.user.commit(function(err:any) {
+                    dbconn.user.commit(function(err:any) {
                         if (err) {
-                            return dbconn_OBSOLETE.user.rollback(function() { throw err })
+                            return dbconn.user.rollback(function() { throw err })
                         }
                     })
                 })

routes/account.ts

@@ -41,7 +41,7 @@ export = function (app:any, config:any, passport:any, lang:string) {
       // Service Provider private key
       decryptionPvk: fs.readFileSync(__dirname + '/cert/key.pem', 'utf8'),
       // Service Provider Certificate
-      privateCert: fs.readFileSync(__dirname + '/cert/key.pem', 'utf8'),
+      privateKey: fs.readFileSync(__dirname + '/cert/key.pem', 'utf8'),
       // Identity Provider's public key
       cert: fs.readFileSync(__dirname + '/cert/cert_idp.pem', 'utf8'),
       
@@ -295,12 +295,12 @@ export = function (app:any, config:any, passport:any, lang:string) {
         let newPwd = req.body.inputNewPwd
         let retypePwd = req.body.inputConfirm
 
-        dbconn.user.query('SELECT password FROM credential WHERE user_id='+loggedInUser.getId(), function (err, rows, fields) {
+        dbconn.user.query('SELECT password FROM credential WHERE user_id='+loggedInUser.getId(), function (err:any, rows:any) {
           if (err) {
             console.error(err)
             res.status(500).render(lang+'/500', { error: err })
           }
-          var userPwd = rows[0].password
+          let userPwd = rows[0].password
 
           // check if the password is correct
           bcrypt.compare(currPwd, userPwd, function(err, isMatch) {

package-lock.json

@@ -26,7 +26,6 @@
         "jest": "^26.5.0",
         "method-override": "^3.0.0",
         "morgan": "^1.9.1",
-        "mysql": "^2.17.1",
         "mysql2": "^2.2.5",
         "nodemailer": "^6.3.1",
         "nodemailer-ntlm-auth": "^1.0.1",
@@ -1603,14 +1602,6 @@
       "resolved": "https://registry.npmjs.org/bcryptjs/-/bcryptjs-2.4.3.tgz",
       "integrity": "sha1-mrVie5PmBiH/fNrF2pczAn3x0Ms="
     },
-    "node_modules/bignumber.js": {
-      "version": "9.0.0",
-      "resolved": "https://registry.npmjs.org/bignumber.js/-/bignumber.js-9.0.0.tgz",
-      "integrity": "sha512-t/OYhhJ2SD+YGBQcjY8GzzDHEk9f3nerxjtfa6tlMXfe7frs/WozhvCNoGvpM0P3bNf3Gq5ZRMlGr5f3r4/N8A==",
-      "engines": {
-        "node": "*"
-      }
-    },
     "node_modules/binary-extensions": {
       "version": "2.2.0",
       "resolved": "https://registry.npmjs.org/binary-extensions/-/binary-extensions-2.2.0.tgz",
@@ -2259,11 +2250,6 @@
         "node": ">=0.10.0"
       }
     },
-    "node_modules/core-util-is": {
-      "version": "1.0.2",
-      "resolved": "https://registry.npmjs.org/core-util-is/-/core-util-is-1.0.2.tgz",
-      "integrity": "sha1-tf1UIgqivFq1eqtxQMlAdUUDwac="
-    },
     "node_modules/create-require": {
       "version": "1.1.1",
       "resolved": "https://registry.npmjs.org/create-require/-/create-require-1.1.1.tgz",
@@ -5169,20 +5155,6 @@
       "resolved": "https://registry.npmjs.org/ms/-/ms-2.0.0.tgz",
       "integrity": "sha1-VgiurfwAvmwpAd9fmGF4jeDVl8g="
     },
-    "node_modules/mysql": {
-      "version": "2.18.1",
-      "resolved": "https://registry.npmjs.org/mysql/-/mysql-2.18.1.tgz",
-      "integrity": "sha512-Bca+gk2YWmqp2Uf6k5NFEurwY/0td0cpebAucFpY/3jhrwrVGuxU2uQFCHjU19SJfje0yQvi+rVWdq78hR5lig==",
-      "dependencies": {
-        "bignumber.js": "9.0.0",
-        "readable-stream": "2.3.7",
-        "safe-buffer": "5.1.2",
-        "sqlstring": "2.3.1"
-      },
-      "engines": {
-        "node": ">= 0.6"
-      }
-    },
     "node_modules/mysql2": {
       "version": "2.2.5",
       "resolved": "https://registry.npmjs.org/mysql2/-/mysql2-2.2.5.tgz",
@@ -5941,11 +5913,6 @@
         "node": ">= 10"
       }
     },
-    "node_modules/process-nextick-args": {
-      "version": "2.0.1",
-      "resolved": "https://registry.npmjs.org/process-nextick-args/-/process-nextick-args-2.0.1.tgz",
-      "integrity": "sha512-3ouUOpQhtgrbOa17J7+uxOTpITYWaGP7/AhoR3+A+/1e9skrzelGi/dXzEYyvbxubEF6Wn2ypscTKiKJFFn1ag=="
-    },
     "node_modules/promise": {
       "version": "7.3.1",
       "resolved": "https://registry.npmjs.org/promise/-/promise-7.3.1.tgz",
@@ -6239,20 +6206,6 @@
         "node": ">=8"
       }
     },
-    "node_modules/readable-stream": {
-      "version": "2.3.7",
-      "resolved": "https://registry.npmjs.org/readable-stream/-/readable-stream-2.3.7.tgz",
-      "integrity": "sha512-Ebho8K4jIbHAxnuxi7o42OrZgF/ZTNcsZj6nRKyUmkhLFq8CHItp/fy6hQZuZmP/n3yZ9VBUbp4zz/mX8hmYPw==",
-      "dependencies": {
-        "core-util-is": "~1.0.0",
-        "inherits": "~2.0.3",
-        "isarray": "~1.0.0",
-        "process-nextick-args": "~2.0.0",
-        "safe-buffer": "~5.1.1",
-        "string_decoder": "~1.1.1",
-        "util-deprecate": "~1.0.1"
-      }
-    },
     "node_modules/readdirp": {
       "version": "3.5.0",
       "resolved": "https://registry.npmjs.org/readdirp/-/readdirp-3.5.0.tgz",
@@ -7114,14 +7067,6 @@
       "resolved": "https://registry.npmjs.org/sprintf-js/-/sprintf-js-1.0.3.tgz",
       "integrity": "sha1-BOaSb2YolTVPPdAVIDYzuFcpfiw="
     },
-    "node_modules/sqlstring": {
-      "version": "2.3.1",
-      "resolved": "https://registry.npmjs.org/sqlstring/-/sqlstring-2.3.1.tgz",
-      "integrity": "sha1-R1OT/56RR5rqYtyvDKPRSYOn+0A=",
-      "engines": {
-        "node": ">= 0.6"
-      }
-    },
     "node_modules/stack-utils": {
       "version": "2.0.3",
       "resolved": "https://registry.npmjs.org/stack-utils/-/stack-utils-2.0.3.tgz",
@@ -7245,14 +7190,6 @@
         "node": ">=0.8.0"
       }
     },
-    "node_modules/string_decoder": {
-      "version": "1.1.1",
-      "resolved": "https://registry.npmjs.org/string_decoder/-/string_decoder-1.1.1.tgz",
-      "integrity": "sha512-n/ShnvDi6FHbbVfviro+WojiFzv+s8MPMHBczVePfUpDJLwoLT0ht1l4YwBCbi8pJAveEEdnkHyPyTP/mzRfwg==",
-      "dependencies": {
-        "safe-buffer": "~5.1.0"
-      }
-    },
     "node_modules/string-length": {
       "version": "4.0.2",
       "resolved": "https://registry.npmjs.org/string-length/-/string-length-4.0.2.tgz",
@@ -7796,11 +7733,6 @@
         "node": ">=0.10.0"
       }
     },
-    "node_modules/util-deprecate": {
-      "version": "1.0.2",
-      "resolved": "https://registry.npmjs.org/util-deprecate/-/util-deprecate-1.0.2.tgz",
-      "integrity": "sha1-RQ1Nyfpw3nMnYvvS1KKJgUGaDM8="
-    },
     "node_modules/utils-merge": {
       "version": "1.0.1",
       "resolved": "https://registry.npmjs.org/utils-merge/-/utils-merge-1.0.1.tgz",
@@ -9486,11 +9418,6 @@
       "resolved": "https://registry.npmjs.org/bcryptjs/-/bcryptjs-2.4.3.tgz",
       "integrity": "sha1-mrVie5PmBiH/fNrF2pczAn3x0Ms="
     },
-    "bignumber.js": {
-      "version": "9.0.0",
-      "resolved": "https://registry.npmjs.org/bignumber.js/-/bignumber.js-9.0.0.tgz",
-      "integrity": "sha512-t/OYhhJ2SD+YGBQcjY8GzzDHEk9f3nerxjtfa6tlMXfe7frs/WozhvCNoGvpM0P3bNf3Gq5ZRMlGr5f3r4/N8A=="
-    },
     "binary-extensions": {
       "version": "2.2.0",
       "resolved": "https://registry.npmjs.org/binary-extensions/-/binary-extensions-2.2.0.tgz",
@@ -9999,11 +9926,6 @@
       "resolved": "https://registry.npmjs.org/copy-descriptor/-/copy-descriptor-0.1.1.tgz",
       "integrity": "sha1-Z29us8OZl8LuGsOpJP1hJHSPV40="
     },
-    "core-util-is": {
-      "version": "1.0.2",
-      "resolved": "https://registry.npmjs.org/core-util-is/-/core-util-is-1.0.2.tgz",
-      "integrity": "sha1-tf1UIgqivFq1eqtxQMlAdUUDwac="
-    },
     "create-require": {
       "version": "1.1.1",
       "resolved": "https://registry.npmjs.org/create-require/-/create-require-1.1.1.tgz",
@@ -12208,17 +12130,6 @@
       "resolved": "https://registry.npmjs.org/ms/-/ms-2.0.0.tgz",
       "integrity": "sha1-VgiurfwAvmwpAd9fmGF4jeDVl8g="
     },
-    "mysql": {
-      "version": "2.18.1",
-      "resolved": "https://registry.npmjs.org/mysql/-/mysql-2.18.1.tgz",
-      "integrity": "sha512-Bca+gk2YWmqp2Uf6k5NFEurwY/0td0cpebAucFpY/3jhrwrVGuxU2uQFCHjU19SJfje0yQvi+rVWdq78hR5lig==",
-      "requires": {
-        "bignumber.js": "9.0.0",
-        "readable-stream": "2.3.7",
-        "safe-buffer": "5.1.2",
-        "sqlstring": "2.3.1"
-      }
-    },
     "mysql2": {
       "version": "2.2.5",
       "resolved": "https://registry.npmjs.org/mysql2/-/mysql2-2.2.5.tgz",
@@ -12791,11 +12702,6 @@
         "react-is": "^17.0.1"
       }
     },
-    "process-nextick-args": {
-      "version": "2.0.1",
-      "resolved": "https://registry.npmjs.org/process-nextick-args/-/process-nextick-args-2.0.1.tgz",
-      "integrity": "sha512-3ouUOpQhtgrbOa17J7+uxOTpITYWaGP7/AhoR3+A+/1e9skrzelGi/dXzEYyvbxubEF6Wn2ypscTKiKJFFn1ag=="
-    },
     "promise": {
       "version": "7.3.1",
       "resolved": "https://registry.npmjs.org/promise/-/promise-7.3.1.tgz",
@@ -13051,20 +12957,6 @@
         }
       }
     },
-    "readable-stream": {
-      "version": "2.3.7",
-      "resolved": "https://registry.npmjs.org/readable-stream/-/readable-stream-2.3.7.tgz",
-      "integrity": "sha512-Ebho8K4jIbHAxnuxi7o42OrZgF/ZTNcsZj6nRKyUmkhLFq8CHItp/fy6hQZuZmP/n3yZ9VBUbp4zz/mX8hmYPw==",
-      "requires": {
-        "core-util-is": "~1.0.0",
-        "inherits": "~2.0.3",
-        "isarray": "~1.0.0",
-        "process-nextick-args": "~2.0.0",
-        "safe-buffer": "~5.1.1",
-        "string_decoder": "~1.1.1",
-        "util-deprecate": "~1.0.1"
-      }
-    },
     "readdirp": {
       "version": "3.5.0",
       "resolved": "https://registry.npmjs.org/readdirp/-/readdirp-3.5.0.tgz",
@@ -13755,11 +13647,6 @@
       "resolved": "https://registry.npmjs.org/sprintf-js/-/sprintf-js-1.0.3.tgz",
       "integrity": "sha1-BOaSb2YolTVPPdAVIDYzuFcpfiw="
     },
-    "sqlstring": {
-      "version": "2.3.1",
-      "resolved": "https://registry.npmjs.org/sqlstring/-/sqlstring-2.3.1.tgz",
-      "integrity": "sha1-R1OT/56RR5rqYtyvDKPRSYOn+0A="
-    },
     "stack-utils": {
       "version": "2.0.3",
       "resolved": "https://registry.npmjs.org/stack-utils/-/stack-utils-2.0.3.tgz",
@@ -13855,14 +13742,6 @@
       "resolved": "https://registry.npmjs.org/streamsearch/-/streamsearch-0.1.2.tgz",
       "integrity": "sha1-gIudDlb8Jz2Am6VzOOkpkZoanxo="
     },
-    "string_decoder": {
-      "version": "1.1.1",
-      "resolved": "https://registry.npmjs.org/string_decoder/-/string_decoder-1.1.1.tgz",
-      "integrity": "sha512-n/ShnvDi6FHbbVfviro+WojiFzv+s8MPMHBczVePfUpDJLwoLT0ht1l4YwBCbi8pJAveEEdnkHyPyTP/mzRfwg==",
-      "requires": {
-        "safe-buffer": "~5.1.0"
-      }
-    },
     "string-length": {
       "version": "4.0.2",
       "resolved": "https://registry.npmjs.org/string-length/-/string-length-4.0.2.tgz",
@@ -14262,11 +14141,6 @@
       "resolved": "https://registry.npmjs.org/use/-/use-3.1.1.tgz",
       "integrity": "sha512-cwESVXlO3url9YWlFW/TA9cshCEhtu7IKJ/p5soJ/gGpj7vbvFrAY/eIioQ6Dw23KjZhYgiIo8HOs1nQ2vr/oQ=="
     },
-    "util-deprecate": {
-      "version": "1.0.2",
-      "resolved": "https://registry.npmjs.org/util-deprecate/-/util-deprecate-1.0.2.tgz",
-      "integrity": "sha1-RQ1Nyfpw3nMnYvvS1KKJgUGaDM8="
-    },
     "utils-merge": {
       "version": "1.0.1",
       "resolved": "https://registry.npmjs.org/utils-merge/-/utils-merge-1.0.1.tgz",