Testing

Prepare Mergin to production

config/config.js

@@ -2,37 +2,69 @@ module.exports = {
   development: {
     app: {
       name: 'User Account Management',
-      port: process.env.PORT || 9989
+      port: process.env.PORT || 9989,
+      host: 'http://localhost:9989'
     },
     passport: {
       strategy: 'saml',
       saml: {
         path: process.env.SAML_PATH || '/saml/SSO',
-        entryPoint: process.env.SAML_ENTRY_POINT || 'https://m4lab.hft-stuttgart.de/idp/saml2/idp/SSOService.php',
-        //issuer: 'sp-account.m4lab.hft-stuttgart.de', //local metadata
-        issuer: 'sp-account-testing.m4lab.hft-stuttgart.de', //testing metadata
-        //issuer: 'sp-account-prod.m4lab.hft-stuttgart.de', //production metadata
-        logoutUrl: 'https://m4lab.hft-stuttgart.de/idp/saml2/idp/SingleLogoutService.php'
+        entryPoint: process.env.SAML_ENTRY_POINT || 'Saml Entry Point',
+        issuer: 'SAML issuer', //local metadata
+        logoutUrl: 'SAML logout URL'
       }
     },
     database: {
-      host: 'transfer.hft-stuttgart.de',      	  // DB host
-      user: 'DBManager',          // DB username
-      password: 'Stuttgart2019',  // DB password
+      host: 'localhost',      	  // DB host
+      user: 'usernamedb',          // DB username
+      password: 'passworddb',  // DB password
       port: 3306,                 // MySQL port
       dbUser: 'userdb',           // User DB
-      host_project: 'm4lab.hft-stuttgart.de', // DB host project db
-      //host_project: 'localhost', // local
-      dbProject: 'projectDB'     // Project DB
+      host_project: 'localhost', // DB host project db
+      dbProject: 'projectdb'     // Project DB
     },
     mailer: {
-      host: 'mail.hft-stuttgart.de',  // hostname
+      host: 'mailhost',  // hostname
       secureConnection: false,        // TLS requires secureConnection to be false
       port: 587,                      // port for secure SMTP
-      authUser: 'ad\\support-transfer',
-      authPass: '6laumri2',
+      authUser: 'mailuser',
+      authPass: 'mailpass',
       tlsCiphers: 'SSLv3',
-      from: 'support-transfer@hft-stuttgart.de',
+      from: 'mailfrom',
+    }
+  },
+  testing: {
+    app: {
+      name: 'User Account Management',
+      port: process.env.PORT || 9989,
+      host: 'https://m4lab.hft-stuttgart.de/account'
+    },
+    passport: {
+      strategy: 'saml',
+      saml: {
+        path: process.env.SAML_PATH || '/saml/SSO',
+        entryPoint: process.env.SAML_ENTRY_POINT || 'saml entry point',
+        issuer: 'SAML issuer', //testing metadata
+        logoutUrl: 'SAML logout URL'
+      }
+    },
+    database: {
+      host: 'dbhost',      	  // DB host
+      user: 'dbuser',          // DB username
+      password: 'dbpass',  // DB password
+      port: 3306,                 // MySQL port
+      dbUser: 'userdb',           // User DB
+      host_project: 'dbhost', // DB host project db
+      dbProject: 'projectdb'     // Project DB
+    },
+    mailer: {
+      host: 'mailhost',  // hostname
+      secureConnection: false,        // TLS requires secureConnection to be false
+      port: 587,                      // port for secure SMTP
+      authUser: 'mailuser',
+      authPass: 'mailpass',
+      tlsCiphers: 'SSLv3',
+      from: 'mailfrom',
     }
   }
-}
+}
\ No newline at end of file

routes/dbconn.js

 const mysql = require('mysql')
 
-var env = process.env.NODE_ENV || 'development';
+var env = process.env.NODE_ENV || 'testing';
 const config = require('../config/config')[env]
 
 // ==== USER ACOOUNT DB CONNECTION ====

routes/mailer.js

 const nodemailer = require('nodemailer')
 
-var env = process.env.NODE_ENV || 'development';
+var env = process.env.NODE_ENV || 'testing';
 const config = require('../config/config')[env]
 
 var smtpTransport = nodemailer.createTransport({

routes/methods.js

@@ -42,14 +42,26 @@ var methods = {
                                 throw err
                             });
                         }
-                        // COMMIT
-                        dbconn.user.commit(function(err) {
+                        // MLAB-129: INSERT verification token
+                        let verificationData = {
+                            user_id: newUserId,
+                            token: data.verificationToken
+                        }
+                        dbconn.user.query('INSERT INTO verification SET ?', verificationData, function (err, results, fields) {
                             if (err) {
                                 return dbconn.user.rollback(function() {
                                     throw err
                                 });
                             }
-                        });
+                            // COMMIT
+                            dbconn.user.commit(function(err) {
+                                if (err) {
+                                    return dbconn.user.rollback(function() {
+                                        throw err
+                                    })
+                                }
+                            })
+                        })
                     })
                 });
             });
@@ -57,7 +69,20 @@ var methods = {
         })
     },
     getUserByEmail: function(email, callback) {
-        dbconn.user.query('SELECT salutation, title, firstname, lastname, industry, organisation, speciality FROM user WHERE email = "' +email+'"', function (err, rows, fields) {
+        dbconn.user.query('SELECT verificationStatus, salutation, title, firstname, lastname, industry, organisation, speciality, m4lab_idp FROM user WHERE email = "' +email+'"', function (err, rows, fields) {
+            if (err) {
+                throw err;
+            }
+            else {
+                if ( rows.length > 0) {
+                    user = rows[0];
+                }
+            }
+            callback(user, err);
+        });
+    },
+    getUserById: function(userId, callback) {
+        dbconn.user.query('SELECT verificationStatus, email, salutation, title, firstname, lastname, industry, organisation, speciality FROM user WHERE id = ' +userId, function (err, rows, fields) {
             if (err) {
                 throw err;
             }
@@ -70,7 +95,7 @@ var methods = {
         });
     },
 	checkUserEmail: function(email, callback) {
-        var user;
+        let user
         dbconn.user.query('SELECT id, email FROM user WHERE email = "' +email+'"', function (err, rows, fields) {
             if (err) {
                 throw err;
@@ -84,7 +109,7 @@ var methods = {
         });
     },
     getUserByToken: function(token, callback) {
-        var user;
+        let user
         dbconn.user.query('SELECT t1.user_id, t2.email FROM userdb.credential AS t1 INNER JOIN userdb.user AS t2 ON t1.user_id = t2.id AND t1.resetPasswordToken = "'
             +token+'" and resetPasswordExpires > '+Date.now(), function (err, rows, fields) {
                 if (err) {
@@ -100,75 +125,92 @@ var methods = {
             }
         );
     },
-	updateUser: function(userData, callback) {
-		dbconn.user.query('UPDATE user SET ? WHERE email = "' +userData.email+'"', userData, function (err, rows, fields) {
-            if (err) throw err;
-            callback(err);
+	updateUserById: function(userData, callback) {
+		dbconn.user.query('UPDATE user SET ? WHERE id = ' +userData.id, userData, function (err, rows, fields) {
+            if (err) throw err
+            callback(err)
         })
     },
     updateCredential: function(data, callback) {
         dbconn.user.query('UPDATE credential SET ? WHERE user_id = ' +data.user_id, data, function (err, rows, fields) {
-            if (err) throw err;
-            callback(err);
+            if (err) throw err
+            callback(err)
         })
     },
     getUserIdByEmail: function(email, callback) {
-        var userId
+        let userId
         dbconn.user.query('SELECT id FROM user WHERE email = "' +email+'"', function (err, rows, fields) {
             if (err) {
-                throw err;
+                throw err
             }
             else {
                 if ( rows.length > 0) {
-                    userId = rows[0].id;
+                    userId = rows[0].id
                 }
             }
-            callback(userId, err);
+            callback(userId, err)
         });
     },
     getUserProjectRole: function(userId, callback) {
         dbconn.user.query('SELECT project_id, role_id FROM user_project_role WHERE user_id = "' +userId+'"', function (err, rows, fields) {
-            if (err) throw err;
-            callback(rows, err);
+            if (err) throw err
+            callback(rows, err)
         });
     },
     addUserProjectRole: function(data, callback) {
         dbconn.user.query('INSERT INTO user_project_role SET ?', data, function (err, results, fields){
-            if (err) throw err;
-            callback(err);
-        })
-    },
-    // ======================= project db =======================
-    getAllProjects: function(callback) {
-        dbconn.project.query('CALL getAllprojects', function (err, rows, fields){
-            if (err) throw err;
-            callback(rows[0], err);
+            if (err) throw err
+            callback(err)
         })
     },
-    getAllMailinglists: function(callback) {
-        dbconn.project.query('CALL getAllLists', function (err, rows, fields){
-            if (err) throw err;
-            callback(rows[0], err);
-	    })
-    },
-    getProjectOverviewById: function(projectId, callback) {
-        dbconn.project.query('CALL GetProjectInformationByProjectID(' + projectId+ ')', function (err, rows, fields){
-            if (err) throw err;
-            callback(rows[0], err);
-    	})
-    },
-    getProjectImagesById: function(projectId, callback) {
-        dbconn.project.query('CALL getImagesByProjectID(' + projectId+ ')', function (err, rows, fields){
-            if (err) throw err;
-            callback(rows[0], err);
+    getVerificationTokenByUserId: function(userId, callback) {
+        let token
+        dbconn.user.query('SELECT token FROM verification WHERE user_id = "' +userId+'"', function (err, rows, fields) {
+            if (err) {
+                throw err
+            }
+            else {
+                if (rows.length > 0) {
+                    token = rows[0].token
+                }
+            }
+            callback(token, err)
         })
     },
-    addProjectOverview: function(data, callback) {
-        dbconn.project.query('INSERT INTO project_overview SET ?', data, function (err, results, fields){
+    getUserIdByVerificationToken: function(token, callback) {
+        let userId
+        dbconn.user.query('SELECT user_id FROM verification WHERE token = "' +token+'"', function (err, rows, fields) {
             if (err) {
-                console.error(err);
+                throw err
             }
-            callback(results, err);
+            else if(rows[0]) {
+                userId = rows[0].user_id
+            }
+            callback(userId, err)
+        })
+    },
+    verifyUserAccount: function(userData, callback) {
+        dbconn.user.beginTransaction(function(err) { // START TRANSACTION
+            if (err) { throw err }
+            // update user status
+            dbconn.user.query('UPDATE user SET ? WHERE id =' +userData.id, userData, function (err, rows, fields) {
+                if (err) {
+                    return dbconn.user.rollback(function() { throw err })
+                }
+                // delete verification token
+                dbconn.user.query('DELETE FROM verification WHERE user_id = '+userData.id, function (err, rows, fields) {
+                    if (err) {
+                        return dbconn.user.rollback(function() { throw err })
+                    }
+                    // COMMIT
+                    dbconn.user.commit(function(err) {
+                        if (err) {
+                            return dbconn.user.rollback(function() { throw err })
+                        }
+                    })
+                })
+            })
+            callback(err)
         })
     }
 };