server.js

#!/bin/env node


//*******************************************
// Server side code
// Here are the POSTS and GETS that connect to
// Oracle DB
//
//
//
//
//
//
//
//*********************************************



var express = require('express'),
    app = express(),
    http = require('http'),
    oracledb = require('oracledb'),
    // dbConfig = require('./dbconfig.js'),
    bodyParser = require('body-parser'),
    cookieParser = require("cookie-parser"),
    ejs = require('ejs'),
    util = require('util'),
    path = require("path"),
    url = require("url"),
    atob = require("atob"),
    stream = require("stream"),
    Q = require("q");

var dbConfig = {user: "", password: "", connectString: "localhost:1525/CRS"};
//for lab machines, change connectString to "CRS"



app.set('view engine', ejs);
app.use(cookieParser());
app.use(bodyParser.json({limit: '50mb'}));
app.use(bodyParser.urlencoded({limit: '50mb', extended:true}));
app.use(function (req, res, next) {
    // Website you wish to allow to connect
    res.setHeader('Access-Control-Allow-Origin', 'http://localhost:8080');

    // Request methods you wish to allow
    res.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PUT, PATCH, DELETE');

    // Request headers you wish to allow
    res.setHeader('Access-Control-Allow-Headers', '*');

    // Set to true if you need the website to include cookies in the requests sent
    // to the API (e.g. in case you use sessions)
    res.setHeader('Access-Control-Allow-Credentials', true);

    // Pass to next layer of middleware
    next();
});

//Set port number.
app.set('port', 8080);
app.set('ip', "127.0.0.1");

//Add public folder for static files
app.use(express.static(__dirname));

app.get("*", function (req, res) {
    var resource = url.parse(req.url).pathname;
    if(~resource.indexOf("node_modules") || ~resource.indexOf("bower_components")) {
        res.sendFile(path.join(__dirname));
    } else {
        if(resource.slice(-1) !== "/") {
            res.sendFile(path.join(__dirname + "/app" + resource));
        } else {
            res.sendFile(path.join(__dirname + "/app" + resource, "index.html"));
        }
    }
});

//Server
http.createServer(app).listen(app.get('port') ,app.get('ip'), function () {
    console.log("✔ Express server listening at %s:%d ", app.get('ip'),app.get('port'));
});

process.on('SIGINT', function() {
  process.exit();
});


// Post for login info. Checks to see if a user exists. If they have a valid username/password
// Return success
app.post("/login", function (req, res) {
    oracledb.getConnection(dbConfig, function (err, connection) {
            if (err) {
                connectionError(err, res);
                return;
            }
            connection.execute(
                "SELECT * " +
                "FROM users " +
                "WHERE user_name = :user_name AND password = :password",
                {user_name: req.body.userName, password: req.body.password},
                function (err, result) {
                    if (err) {
                        executeError(err, res);
                    } else {
                        // console.log(util.inspect(result, {showHidden: false, depth: null}));
                        if(result.rows.length > 0) {
                            res.send({success: true});
                        } else {
                            res.status(422);
                            res.send(
                                {
                                    error: true,
                                    errorCode: 2,
                                    message: "Username or password is incorrect. Please try again.",
                                    success: false
                                }
                            );
                        }
                    }
                    doRelease(connection);
                }
            );
        }
    );
});


// Get the last photo ID in order to increment
// to make a new ID
app.post("/photoid", function(req, res){
  var DBQueryString;

      DBQueryString =
            "SELECT photo_id " +
            "FROM images " +
            "WHERE images.photo_id = " +
            "(SELECT MAX(photo_id) FROM images)";

        oracledb.getConnection(dbConfig, function (err, connection) {
            if (err) {
                connectionError(err, res);
                return;
            }
            connection.execute(DBQueryString,
                function (err, result) {
                    // console.log(util.inspect(result, {showHidden: false, depth: null}));
                    if (err) {
                        executeError(err, res);
                    } else {
                        // Should return the max id currently in the table
                        res.send({result: result.rows, success:true});
                    }
                    doRelease(connection);
                }
            );
        });
});

// Get the last group ID in order to increment
// to make a new ID
app.post("/groupid", function(req, res){
    oracledb.getConnection(dbConfig, function (err, connection) {
            if (err) {
                connectionError(err, res);
                return;
            }
            connection.execute(
                "SELECT GROUP_ID " +
                "FROM groups " +
                "ORDER BY GROUP_ID desc",
                function (err, result) {
                    if (err) {
                        executeError(err, res);
                    } else {
                        // console.log(util.inspect(result, {showHidden: false, depth: null}));
                        if(result.rows.length > 0) {
                            res.send({lastID: result.rows[0][0], success: true});
                        } else {
                            res.status(500);
                            res.send(
                                {
                                    error: true,
                                    errorCode: 2,
                                    message: "Could not retrieve data",
                                    success: false
                                }
                            );
                        }
                    }
                    doRelease(connection);
                }
            );
        }
    );
});



//Update a photo's information based on form data
app.post("/updatePhoto", function(req, res){
  var DBQueryString =
      "UPDATE IMAGES " +
      "SET PERMITTED = :permitted1, SUBJECT = :subject1, PLACE = :place1, TIMING = TO_DATE (:timing1, 'yyyy/mm/dd'), DESCRIPTION = :desc1 " +
      "WHERE PHOTO_ID = :photoid1",
      DBQueryParam = {permitted1: req.body.permit,
                      subject1: req.body.subject,
                      place1: req.body.place,
                      timing1: req.body.timing,
                      desc1: req.body.desc,
                      photoid1: req.body.photoid
                    };

      oracledb.getConnection(dbConfig, function (err, connection) {
          if (err) {
              connectionError(err, res);
              return;
          }
          connection.execute(DBQueryString, DBQueryParam,
              {autoCommit: true},
              function (err, result) {
                  if (err) {
                      executeError(err, res);
                  } else {
                      res.send({success:true});
                  }
                  doRelease(connection);
              }
          );
      });

});


// Based on code found here: https://github.com/oracle/node-oracledb/blob/master/doc/api.md#lobhandling
// Translate photo data to base 64 and write to empty blob
// See project report for more details
app.post("/upload", function(req, res){
    var DBQueryString =
        "INSERT INTO images (photo_id, owner_name, permitted, subject, place, timing, description, thumbnail, photo) VALUES (:photo_id, :owner_name, :permitted, :subject, :place, TO_DATE (:timing, 'yyyy/mm/dd'), :description, EMPTY_BLOB(), EMPTY_BLOB()) RETURNING photo, thumbnail INTO :lobbv, :lobtn",

        DBQueryParam = {photo_id: req.body.photo_id,
                        owner_name: req.body.owner_name,
                        permitted: req.body.permitted,
                        subject: req.body.subject,
                        place: req.body.place,
                        timing: req.body.timing,
                        description: req.body.descr,
                        lobbv: {type: oracledb.BLOB, dir: oracledb.BIND_OUT},
                        lobtn: {type: oracledb.BLOB, dir: oracledb.BIND_OUT}
                      };

    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(
           DBQueryString,
           DBQueryParam,
           {autoCommit: false},

           function (err, result) {
                var byteCharacters, buffer, i, lob,
                    bufferStream, view, arrayBuffer, bytes;
                var imageDeferred = Q.defer();
                var thumbnailDeferred = Q.defer();

                if (err || result.rowsAffected != 1 || result.outBinds.lobbv.length != 1) {
                    executeError(err, res);
                } else {
                    // Create an ArrayBuffer from the base64 image
                    byteCharacters = atob(req.body.photo);
                    bytes = new Uint8Array(byteCharacters.length);
                    for (i = 0; i < byteCharacters.length; i++) {
                        bytes[i] = byteCharacters.charCodeAt(i);
                    }
                    arrayBuffer = bytes.buffer;
                    // Create an ArrayBuffer from the base64 thumbnail
                    thumbnailCharacters = atob(req.body.thumbnail);
                    thumbnailBytes = new Uint8Array(thumbnailCharacters.length);
                    for (i = 0; i < thumbnailCharacters.length; i++) {
                        thumbnailBytes[i] = thumbnailCharacters.charCodeAt(i);
                    }
                    thumbnailArrayBuffer = thumbnailBytes.buffer;

                    // Create a Node.js Buffers from the ArrayBuffers
                    buffer = new Buffer(arrayBuffer.byteLength);
                    view = new Uint8Array(arrayBuffer);
                    for (i = 0; i < buffer.length; i++) {
                        buffer[i] = view[i];
                    }
                    thumbnailBuffer = new Buffer(thumbnailArrayBuffer.byteLength);
                    view = new Uint8Array(thumbnailArrayBuffer);
                    for (i = 0; i < thumbnailBuffer.length; i++) {
                        thumbnailBuffer[i] = view[i];
                    }

                    // Put the buffer into a stream so that it can be piped to oracle blob
                    bufferStream = new stream.PassThrough();
                    bufferStream.end(buffer);
                    thumbnailStream = new stream.PassThrough();
                    thumbnailStream.end(thumbnailBuffer);

                    // When the buffer finishes writing to the lob, resolve the deferred
                    bufferStream.on("end", function () {
                        imageDeferred.resolve();
                    });
                    thumbnailStream.on("end", function () {
                        thumbnailDeferred.resolve();
                    });
                    // If there is an error then handle it
                    bufferStream.on("error", function (err) {
                        executeError(err, res);
                        imageDeferred.reject();
                    });
                    thumbnailStream.on("error", function (err) {
                        executeError(err, res);
                        thumbnailDeferred.reject();
                    });

                    Q.allSettled([imageDeferred.promise, thumbnailDeferred.promise]).then(function () {
                    // Q.allSettled([imageDeferred.promise]).then(function () {

                        connection.commit(function (err) {
                            if (err) {
                                executeError(err, res);
                            } else {
                                res.send({success: true});
                            }
                        });

                    });

                    lob = result.outBinds.lobbv[0];
                    thumbnaillob = result.outBinds.lobtn[0];

                    // If there is an error then handle it
                    lob.on("error", function (err) {
                        executeError(err, res);
                        imageDeferred.reject();
                    });
                    thumbnaillob.on("error", function (err) {
                        executeError(err, res);
                        thumbnailDeferred.reject();
                    });

                    lob.on('finish', function() {
                        // console.log("lob.on 'finish' event");
                        connection.commit( function(err) {
                            if (err)
                              console.error(err.message);
                            else
                              console.log("Image uploaded successfully.");
                                connection.release(function(err) {
                                  if (err) console.error(err.message);
                                });
                          });
                      });

                    // Put the images into the blob
                    bufferStream.pipe(lob);
                    thumbnailStream.pipe(thumbnaillob);
                }
            }
        );

   });
});

//GET : return the group members based on group id
//POST : insert a new row into the groups table from form data
app.route("/displayGroup")
    .get(function(req, res){
        var DBQueryString =
               "SELECT friend_id, date_added " +
               "FROM GROUP_LISTS " +
               "WHERE GROUP_ID = :groupID" ,
               DBQueryParam = {groupID: req.query.groupID};

           oracledb.getConnection(dbConfig, function (err, connection) {
               if (err) {
                   connectionError(err, res);
                   return;
               }
              connection.execute(DBQueryString, DBQueryParam,
                  {autoCommit: true},
                  function (err, result) {
                      if (err) {
                          executeError(err, res);
                      } else {
                          res.send({success: true, results: result.rows});
                       }
                       doRelease(connection);
                   }
               );
          });
    })
    .post(function(req, res){
        // console.log(util.inspect(req.body, {showHidden: false, depth: null}));
        var DBQueryString =
               "INSERT INTO GROUP_LISTS (GROUP_ID, FRIEND_ID, DATE_ADDED, NOTICE)" +
               "VALUES (:group_id, :user_name, CURRENT_DATE, :notice)" ,
               DBQueryParam = {group_id: req.body.group_id, user_name: req.body.user_name, notice: null};

           oracledb.getConnection(dbConfig, function (err, connection) {
               if (err) {
                   connectionError(err, res);
                   return;
               }
              connection.execute(DBQueryString, DBQueryParam,
                  {autoCommit: true},
                  function (err, result) {
                      if (err) {
                          executeError(err, res);
                      } else {
                          res.send({success: true, results: result.rows});
                       }
                       doRelease(connection);
                   }
               );
          });
    });

//Delete a group member based on username
app.post("/deleteMember" , function(req, res){
    var DBQueryString =
              "DELETE from GROUP_LISTS WHERE group_id = :group_id AND FRIEND_ID = :user_name" ,
              DBQueryParam = {group_id: req.body.group_id, user_name: req.body.user_name};

          oracledb.getConnection(dbConfig, function (err, connection) {
              if (err) {
                  connectionError(err, res);
                  return;
              }
             connection.execute(DBQueryString, DBQueryParam,
                 {autoCommit: true},
                 function (err, result) {
                    //  console.log(util.inspect(result, {showHidden: false, depth: null}));
                     if (err) {
                         executeError(err, res);
                     } else {
                         res.send({success: true});
                      }
                      doRelease(connection);
                  }
              );
         });
});

//Delete a group. See project report for more details
app.post("/deleteGroup" , function(req, res){
    var DBQueryUpdate =
        "UPDATE images " +
        "SET permitted = 2" +
        "WHERE permitted = :group_id; "

    var DBQueryStringList =
        "DELETE from GROUP_LISTS "+
        "WHERE group_id = :group_id; ";

    var DBQueryStringGroup =
        "DELETE from GROUPS " +
        "WHERE group_id = :group_id; ";

    var DBQueryParam = {group_id: req.body.group_id};

    DBQueryTotal = "BEGIN\n" + DBQueryUpdate + DBQueryStringList + DBQueryStringGroup + "END;";

    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryTotal, DBQueryParam,
           {autoCommit: true},
           function (err, result) {
               if (err) {
                   executeError(err, res);
               } else {
                   res.send({success: true});
               }
               doRelease(connection);
            }
        );
    });
});

//Return a list of groups a user is a member of
app.post("/memberships", function(req, res){
    var DBQueryString =
        "SELECT l.group_id, g.group_name " +
        "FROM GROUP_LISTS l, GROUPS g " +
        "WHERE l.FRIEND_ID = :userName AND l.GROUP_ID = g.GROUP_ID" ,
        DBQueryParam = {userName: req.query.userName};
    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           {autoCommit: true},
           function (err, result) {
               if (err) {
                   executeError(err, res);
               } else {
                   res.send({success: true, results: result.rows});
               }
               doRelease(connection);
            }
        );
    });
});

//GET : return a list of groups that the user owns
//POST : insert a new group into the groups table
app.route("/groups")
    .get(function(req, res){
        var DBQueryString =
            "SELECT * " +
            "FROM GROUPS " +
            "WHERE GROUPS.USER_NAME = :userName" ,
            DBQueryParam = {userName: req.query.userName};

        oracledb.getConnection(dbConfig, function (err, connection) {
            if (err) {
                connectionError(err, res);
                return;
            }
           connection.execute(DBQueryString, DBQueryParam,
               {autoCommit: true},
               function (err, result) {
                   if (err) {
                       executeError(err, res);
                   } else {
                    //    console.log(util.inspect(result, {showHidden: false, depth: null}));
                       res.send({success: true, results: result.rows});
                    }
                    doRelease(connection);
                }
            );
        });

    })
    .post(function(req, res){
        var DBQueryString =
            "BEGIN\n " +
            "INSERT INTO GROUPS (GROUP_ID, USER_NAME, GROUP_NAME, DATE_CREATED) " +
            "VALUES (:groupID, :userName, :groupName, CURRENT_DATE); " +

            "INSERT INTO GROUP_LISTS (GROUP_ID, FRIEND_ID, DATE_ADDED, NOTICE) " +
            "VALUES (:groupID, :userName, CURRENT_DATE, :notice); " +

            "END;",
            DBQueryParam = {groupID: req.body.groupID,
                            userName: req.body.userName,
                            groupName: req.body.groupName,
                            notice: null
                            };

        // console.log(util.inspect(req, {showHidden: false, depth: null}));

        oracledb.getConnection(dbConfig, function (err, connection) {
            if (err) {
                connectionError(err, res);
                return;
            }
           connection.execute(DBQueryString, DBQueryParam,
               {autoCommit: true},
               function (err, result) {
                   if (err) {
                       executeError(err, res);
                   } else {
                    //    console.log(util.inspect(result, {showHidden: false, depth: null}));
                       res.send({success: true});
                    }
                    doRelease(connection);
                }
            );
        });

    });


//Delete an image based on photo id
app.post("/deleteImage", function(req, res){
  var DBQueryString_tracking = "DELETE from IMAGE_TRACKING WHERE PHOTO_ID = :photo_id; ",
      DBQueryString_images = "DELETE from IMAGES WHERE PHOTO_ID = :photo_id; " ,
      DBQueryString_total = "BEGIN\n" + DBQueryString_tracking + DBQueryString_images + "END;",
      DBQueryParam = {photo_id: req.body.photo_id};

        oracledb.getConnection(dbConfig, function (err, connection) {
            if (err) {
                connectionError(err, res);
                return;
            }
           connection.execute(DBQueryString_total, DBQueryParam,
               {autoCommit: true},
               function (err, result) {
                  //  console.log(util.inspect(result, {showHidden: false, depth: null}));
                   if (err) {
                       executeError(err, res);
                   } else {
                       res.send({success: true});
                    }
                    doRelease(connection);
                }
            );
       });

});

//first check if the username/email combination exists
//if it is a unique entry then register the user
app.route("/register")
    .get(function (req, res) {
        /** Use to check if a user name or email is already taken.
         *  All parameters should be part of the query string.
         *
         *  Possible parameters:
         *       userName
         *       email
         */

        var DBQueryString,
            DBQueryParam,
            username = req.query.userName,
            email = req.query.email;

        DBQueryString =
            "SELECT * " +
            "FROM persons " +
            "WHERE persons.user_name = :user_name OR persons.email = :email";

        DBQueryParam = {user_name: username, email: email};

        oracledb.getConnection(dbConfig, function (err, connection) {
            if (err) {
                connectionError(err, res);
                return;
            }
            connection.execute(DBQueryString, DBQueryParam,
                function (err, result) {
                    // console.log(util.inspect(result, {showHidden: false, depth: null}));
                    if (err) {
                        executeError(err, res);
                    } else if (result.rows.length) {
                        //there was a tuple returned with the username and email
                        //so not unique and must use another combination
                        res.status(409);
                        res.send(
                            {
                                error: true,
                                errorCode: 4,
                                message: "Element already exists",
                                success: false
                            }
                        );
                    } else {
                        //username and email are unique so able to register
                        res.send({success: true});
                    }
                    doRelease(connection);
                }
            );
        });
    })
    .post (function (req, res){
        var DBQueryString,
            DBQueryParam,
            DBQueryTotal,
            username = req.body.userName;
            password = req.body.password;
            fname = req.body.fname;
            lname = req.body.lname;
            addr = req.body.addr;
            email = req.body.email;
            phone = req.body.phone;

            // Need to find how to calculate the current datestamp
            DBQueryStringUsers =
            "INSERT INTO users " +
            "(USER_NAME, PASSWORD, DATE_REGISTERED) " +
            "VALUES (:username, :password, CURRENT_DATE); ";

            DBQueryStringPersons =
            "INSERT INTO persons " +
            "(USER_NAME, FIRST_NAME, LAST_NAME, ADDRESS, EMAIL, PHONE) " +
            "VALUES (:username, :first_name, :last_name, :address, :email, :phone); ";

            DBQueryTotal = "BEGIN\n" +DBQueryStringUsers + DBQueryStringPersons + "END;";

            // Need to put the parameters in here
            DBQueryParam = {username: username,
                                  password: password,
                                  first_name: fname,
                                  last_name: lname,
                                  address: addr,
                                  email: email,
                                  phone: phone
                                  };

            // console.log(util.inspect(DBQueryParam, {showHidden: false, depth: null}));


        oracledb.getConnection(dbConfig, function (err, connection) {
            if (err) {
                connectionError(err, res);
                return;
            }
           //Execute both SQL statements
           connection.execute(DBQueryTotal, DBQueryParam,
               {autoCommit: true},
               function (err, result) {
                   if (err) {
                       executeError(err, res);
                   } else {
                       // console.log(util.inspect(result, {showHidden: false, depth: null}));
                       res.send({success: true});
                    }
                    doRelease(connection);
                }
            );
        });
    });


//Both getSearchResults and getImages take in an blob
//data from the database and converts them into a base 64
//string representation

getSearchResults = function (row, index) {
  var lob, buffer, bufferLength;
  var lobLoadingThumb = Q.defer();
  var lobLoadingPhoto = Q.defer();
  var deferred =  Q.defer();
  var imageObj;
  var thumbObj;

  lob = row[0];
  thumbnailLob = row[1];
  buffer = new Buffer(0);
  bufferLength = 0;
  thumbnailBuffer = new Buffer(0);
  thumbnailBufferLength = 0;

  // console.log(util.inspect(row, false, null));


  if (lob) {
    // console.log("LOB " + util.inspect(lob, false, null));
        // When data comes in from the stream, add it to the buffer
        lob.on("data", function (chunk) {
            bufferLength = bufferLength + chunk.length;
            buffer = Buffer.concat([buffer, chunk], bufferLength);
            // console.log("DATA " + bufferLength);
        });
        //When the data is finished coming in, change it to base 64 and add to result
        lob.on("end", function () {
            imageObj = buffer.toString("base64");
            // console.log("END " + imageObj);
        });
        // When the stream closes, resolce the promsie
        lob.on("close", function (chunk) {

            // console.log("CLOSE PHOTO + RESOLVE");

            // Fulfill promise
            lobLoadingPhoto.resolve();
        });
        // Make sure we reject the promise when the stream fails so that we don't have a memory leak
        lob.on("error", function (err) {
            // executeError(err, res);
            // Reject promise
            console.log("ERROR " + err);
            lobLoadingPhoto.reject();
        });
  }
  if (thumbnailLob) {
        // When data comes in from the stream, add it to the buffer
        thumbnailLob.on("data", function (chunk) {
            thumbnailBufferLength = thumbnailBufferLength + chunk.length;
            thumbnailBuffer = Buffer.concat([thumbnailBuffer, chunk], thumbnailBufferLength);
        });
        //When the data is finished coming in, change it to base 64 and add to result
        thumbnailLob.on("end", function () {
            thumbObj = thumbnailBuffer.toString("base64");
        });
        // When the stream closes, resolve the promsie
        thumbnailLob.on("close", function (chunk) {
            // Fulfill promise
          lobLoadingThumb.resolve();
        });
        // Make sure we reject the promise when the stream fails so that we don't have a memory leak
        thumbnailLob.on("error", function () {
            // executeError(err, res);
            // Reject promise
            lobLoadingThumb.reject();
        });
  }

  //When all promises complete, return the results
  Q.all([lobLoadingPhoto.promise, lobLoadingThumb.promise]).then(function () {
      deferred.resolve({imageObj: imageObj, thumbObj: thumbObj});
  }).done();

  return deferred.promise;

};

getImages = function (row, index) {
  var lob, buffer, bufferLength;
  var lobLoadingThumb = Q.defer();
  var lobLoadingPhoto = Q.defer();
  var deferred =  Q.defer();
  var imageObj;
  var thumbObj;

  lob = row[row.length-1];
  thumbnailLob = row[row.length-2];
  buffer = new Buffer(0);
  bufferLength = 0;
  thumbnailBuffer = new Buffer(0);
  thumbnailBufferLength = 0;

  if (lob) {
    // console.log("LOB " + util.inspect(lob, false, null));
        // When data comes in from the stream, add it to the buffer
        lob.on("data", function (chunk) {
            bufferLength = bufferLength + chunk.length;
            buffer = Buffer.concat([buffer, chunk], bufferLength);
            // console.log("DATA " + bufferLength);
        });
        //When the data is finished coming in, change it to base 64 and add to result
        lob.on("end", function () {
            imageObj = buffer.toString("base64");
            // console.log("END " + imageObj);
        });
        // When the stream closes, resolce the promsie
        lob.on("close", function (chunk) {

            // console.log("CLOSE PHOTO + RESOLVE");

            // Fulfill promise
            lobLoadingPhoto.resolve();
        });
        // Make sure we reject the promise when the stream fails so that we don't have a memory leak
        lob.on("error", function (err) {
            // executeError(err, res);
            // Reject promise
            console.log("ERROR " + err);
            lobLoadingPhoto.reject();
        });
  }
  if (thumbnailLob) {
        // When data comes in from the stream, add it to the buffer
        thumbnailLob.on("data", function (chunk) {
            thumbnailBufferLength = thumbnailBufferLength + chunk.length;
            thumbnailBuffer = Buffer.concat([thumbnailBuffer, chunk], thumbnailBufferLength);
        });
        //When the data is finished coming in, change it to base 64 and add to result
        thumbnailLob.on("end", function () {
            thumbObj = thumbnailBuffer.toString("base64");
        });
        // When the stream closes, resolve the promsie
        thumbnailLob.on("close", function (chunk) {
            // Fulfill promise
          lobLoadingThumb.resolve();
        });
        // Make sure we reject the promise when the stream fails so that we don't have a memory leak
        thumbnailLob.on("error", function () {
            // executeError(err, res);
            // Reject promise
            lobLoadingThumb.reject();
        });
  }

  //When all promises complete, return the results
  Q.all([lobLoadingPhoto.promise, lobLoadingThumb.promise]).then(function () {
      deferred.resolve({imageObj: imageObj, thumbObj: thumbObj});
  }).done();

  return deferred.promise;

};

// Sends edits of an image into the database
app.post("/editImage", function(req, res){
    var DBQueryString =
        "UPDATE images " +
        "SET permitted = :permitted, subject = :subject, place = :place, timing = TO_DATE (:timing, 'yyyy/mm/dd'), description = :desc " +
        "WHERE photo_id = :photo_id" ,
        DBQueryParam = {photo_id: req.query.photo_id,
                        permitted: req.query.permitted,
                        subject: req.query.subject,
                        place: req.query.loc,
                        timing: req.query.timing,
                        desc: req.query.desc};

    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           {autoCommit: true},
           function (err, result) {
               if (err) {
                   executeError(err, res);
               } else {
                   res.send({success: true, results: result.rows});
               }
               doRelease(connection);
            }
        );
    });
});

// Retrieves pictures that the user uploaded onto the database
app.post("/getMyPictures", function(req, res){
    var DBQueryString =
        "SELECT * " +
        "FROM images " +
        "WHERE images.owner_name = :userName" ,
        DBQueryParam = {userName: req.query.userName};
    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           function (err, result) {
               var imageArr = [];
               var thumbArr = [];

               if (err) {
                   executeError(err, res);
               } else {

                    result.rows.forEach(function(row, index, array){
                          getImages(row).then(function(photoData){
                              imageArr.push(photoData.imageObj);
                              thumbArr.push(photoData.thumbObj);

                              if(imageArr.length == result.rows.length){
                                  // console.log("seding back");
                                  doRelease(connection);
                                  res.send({
                                    rows:result.rows,
                                    images: imageArr,
                                    thumbs: thumbArr
                                  });
                              }
                          });
                    });

                }
            }
        );
    });
});


//Return the most popular images based on the number
//of distinct users that clicked on the image. See
//project report for more details.
app.post("/getPopularPictures", function(req, res){
  var DBQueryString =
      "with order1 as (SELECT * from ( select tr.photo_id, COUNT(tr.photo_id) as photo_count from images i, image_tracking tr where i.photo_id = tr.photo_id AND i.permitted != 2 group by tr.photo_id ORDER BY photo_count DESC ) where photo_count IN (select distinct COUNT(tr.photo_id) as photo_count from images i, image_tracking tr where i.photo_id = tr.photo_id AND i.permitted != 2 group by tr.photo_id order by photo_count DESC FETCH FIRST 5 ROWS ONLY)) select images.photo_id, images.owner_name, images.permitted, images.subject, images.place, images.timing, images.description, images.thumbnail, images.photo from images, order1 where images.photo_id = order1.photo_id order by order1.photo_count desc",
      DBQueryParam = {};
  oracledb.getConnection(dbConfig, function (err, connection) {
      if (err) {
          connectionError(err, res);
          return;
      }
     connection.execute(DBQueryString, DBQueryParam,
         function (err, result) {
             var imageArr = [];
             var thumbArr = [];
             if (err) {
                 executeError(err, res);
             } else {
                     if(result.rows.length !== 0){
                           result.rows.forEach(function(row, index, array){
                            // console.log("ROW " + util.inspect(row, false, null));

                               getImages(row).then(function(photoData){
                                   imageArr.push(photoData.imageObj);
                                   thumbArr.push(photoData.thumbObj);

                                   if(imageArr.length == result.rows.length){
                                      //  console.log("seding back");
                                       doRelease(connection);
                                       res.send({
                                         rows:result.rows,
                                         images: imageArr,
                                         thumbs: thumbArr
                                       });
                                   }
                               });
                          });
                     }else{
                       res.send({
                         rows:result.rows,
                         images: imageArr,
                         thumbs: thumbArr
                       });
                   }
              }
          }
      );
  });


});


//Select all images from the database. only an
//admin can use this function
app.post("/getAdminPictures", function(req, res){
  var DBQueryString =
      "SELECT * " +
      "FROM images ",
      DBQueryParam = {};
  oracledb.getConnection(dbConfig, function (err, connection) {
      if (err) {
          connectionError(err, res);
          return;
      }
     connection.execute(DBQueryString, DBQueryParam,
         function (err, result) {
             var imageArr = [];
             var thumbArr = [];
             if (err) {
                 executeError(err, res);
             } else {
                       if(result.rows.length !== 0){
                             result.rows.forEach(function(row, index, array){
                              // console.log("ROW " + util.inspect(row, false, null));

                                 getImages(row).then(function(photoData){
                                     imageArr.push(photoData.imageObj);
                                     thumbArr.push(photoData.thumbObj);

                                     if(imageArr.length == result.rows.length){
                                        //  console.log("seding back");
                                         doRelease(connection);
                                         res.send({
                                           rows:result.rows,
                                           images: imageArr,
                                           thumbs: thumbArr
                                         });
                                     }
                                 });
                            });
                       }else{
                         res.send({
                           rows:result.rows,
                           images: imageArr,
                           thumbs: thumbArr
                         });
                     }
              }
          }
      );
  });


});


// Retrieves pictures that others uploaded onto the database
app.post("/getGroupPictures", function(req, res){
    var DBQueryString =
        "SELECT * " +
        "FROM images " +
        "WHERE images.permitted IN " +
        "(SELECT group_id FROM group_lists WHERE group_lists.friend_id = :userName) " +
        "OR images.permitted = 1",
        DBQueryParam = {userName: req.query.userName};
    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           function (err, result) {
               var imageArr = [];
               var thumbArr = [];
               if (err) {
                   executeError(err, res);
               } else {
                         if(result.rows.length !== 0){
                               result.rows.forEach(function(row, index, array){
                                // console.log("ROW " + util.inspect(row, false, null));

                                   getImages(row).then(function(photoData){
                                       imageArr.push(photoData.imageObj);
                                       thumbArr.push(photoData.thumbObj);

                                       if(imageArr.length == result.rows.length){
                                          //  console.log("seding back");
                                           doRelease(connection);
                                           res.send({
                                             rows:result.rows,
                                             images: imageArr,
                                             thumbs: thumbArr
                                           });
                                       }
                                   });
                              });
                         }else{
                           res.send({
                             rows:result.rows,
                             images: imageArr,
                             thumbs: thumbArr
                           });
                       }
                }
            }
        );
    });
});

// Get search results from only keywords
app.post("/getKeyResults", function(req, res){
    var DBQueryString = req.body.queryStr,
        DBQueryParam = {};

    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           {autoCommit: true},
           function (err, result) {
               var imageArr = [];
               var thumbArr = [];
               if (err) {
                   executeError(err, res);
               } else {

                    if(result.rows.length !== 0){
                          result.rows.forEach(function(row, index, array){
                           // console.log("ROW " + util.inspect(row, false, null));

                              getSearchResults(row).then(function(photoData){
                                  imageArr.push(photoData.imageObj);
                                  thumbArr.push(photoData.thumbObj);

                                  if(imageArr.length == result.rows.length){
                                     //  console.log("seding back");
                                      doRelease(connection);
                                      res.send({
                                        rows:result.rows,
                                        images: imageArr,
                                        thumbs: thumbArr
                                      });
                                  }
                              });
                         });
                    }else{
                      res.send({
                        rows:result.rows,
                        images: imageArr,
                        thumbs: thumbArr
                      });
                  }
               }
            }
        );
    });
});

// Get search results from only a timeframe
app.post("/getTimeResults", function(req, res){
    var DBQueryString =
        "SELECT * " +
        "FROM images " +
        "WHERE ",
        DBQueryParam = {userName: req.query.userName, startDate: req.query.timeStart, endDate: req.query.timeEnd};

    // If the user is not an admin, add on the permission restrictions
    if (req.query.userName != "admin"){
      DBQueryString = DBQueryString + "(images.permitted IN " +
                                      "(SELECT group_id FROM group_lists WHERE group_lists.friend_id = :userName) " +
                                      "OR images.permitted = 1 " +
                                      "OR (images.permitted = 2 AND images.owner_name = :userName)) " +
                                      "AND ";
    } else {
      DBQueryParam = {startDate: req.query.timeStart, endDate: req.query.timeEnd};
    };

    DBQueryString = DBQueryString + "(images.timing BETWEEN TO_DATE (:startDate, 'yyyy/mm/dd') AND TO_DATE (:endDate, 'yyyy/mm/dd'))";

    // console.log(DBQueryString);

    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           {autoCommit: true},
           function (err, result) {
               var imageArr = [];
               var thumbArr = [];
               if (err) {
                   executeError(err, result);
               } else {
                     if(result.rows.length !== 0){
                           result.rows.forEach(function(row, index, array){
                            // console.log("ROW " + util.inspect(row, false, null));

                               getImages(row).then(function(photoData){
                                   imageArr.push(photoData.imageObj);
                                   thumbArr.push(photoData.thumbObj);

                                   if(imageArr.length == result.rows.length){
                                      //  console.log("seding back");
                                       doRelease(connection);
                                       res.send({
                                         rows:result.rows,
                                         images: imageArr,
                                         thumbs: thumbArr
                                       });
                                   }
                               });
                          });
                     }else{
                       res.send({
                         rows:result.rows,
                         images: imageArr,
                         thumbs: thumbArr
                       });
                   }
               }

            }
        );
    });
});

// Get search results from both keywords and a timeframe
app.post("/getKeyTimeResults", function(req, res){
    var DBQueryString =req.body.queryStr,
        DBQueryParam = {};

    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           {autoCommit: true},
           function (err, result) {
               var imageArr = [];
               var thumbArr = [];
               if (err) {
                   executeError(err, res);
               } else {
                //  console.log(util.inspect(result, false, null));
                        if(result.rows.length !== 0){
                              result.rows.forEach(function(row, index, array){
                               // console.log("ROW " + util.inspect(row, false, null));

                                  getSearchResults(row).then(function(photoData){
                                      imageArr.push(photoData.imageObj);
                                      thumbArr.push(photoData.thumbObj);

                                      if(imageArr.length == result.rows.length){
                                         //  console.log("seding back");
                                          doRelease(connection);
                                          res.send({
                                            rows:result.rows,
                                            images: imageArr,
                                            thumbs: thumbArr
                                          });
                                      }
                                  });
                             });
                        }else{
                          res.send({
                            rows:result.rows,
                            images: imageArr,
                            thumbs: thumbArr
                          });
                      }
               }
            }
        );
    });
});

// Checks if the username already has tracking for the image in the database
app.post("/checkTracking", function(req, res){
    var DBQueryString =
        "SELECT * " +
        "FROM image_tracking " +
        "WHERE photo_id = :photo_id AND user_name = :userName" ,
        DBQueryParam = {userName: req.query.userName, photo_id: req.query.photo_id};

    // console.log(DBQueryString);
    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           {autoCommit: true},
           function (err, result) {
               if (err) {
                   executeError(err, res);
               } else {
                   res.send({success: true, results: result.rows});
               }
               doRelease(connection);
            }
        );
    });
});

// Inserts a new image tracking into the database
app.post("/updateTracking", function(req, res){
    var DBQueryString =
        "INSERT INTO image_tracking " +
        "(PHOTO_ID, USER_NAME) " +
        "VALUES (:photo_id, :userName)" ,
        DBQueryParam = {userName: req.query.userName, photo_id: req.query.photo_id};

    // console.log(DBQueryString);
    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           {autoCommit: true},
           function (err, result) {
               if (err) {
                   executeError(err, res);
               } else {
                   res.send({success: true, results: result.rows});
               }
               doRelease(connection);
            }
        );
    });
});

// Retrieves OLAP data analysis
app.post("/analysis", function(req, res){
    var DBQueryString =
        "SELECT " ,
        DBQueryParam = {username: req.body.username,
                        timeStart: req.body.timeStart,
                        timeEnd: req.body.timeEnd};

    var timeframe = req.body.timeframe,
        username =  req.body.username,
        subject =  req.body.subject,
        timeStart =  req.body.timeStart,
        timeEnd =  req.body.timeEnd;

    if (timeframe == "yearly") {
        DBQueryString += "to_char(trunc(timing,'yy'), 'YYYY') AS " + '"Year", ';
    } else if (timeframe == "monthly") {
        DBQueryString += "to_char(trunc(timing,'MM'), 'YYYY-MM') AS " + '"Month", ';
    } else if (timeframe == "weekly") {
        DBQueryString += "to_char(NEXT_DAY(timing, 'SAT'), 'YYYY-MM-DD') AS " + '"Week", ';
    };

    DBQueryString += "COUNT(photo_id) " +
                     "FROM data_analysis ";

    // Add WHERE clause if restrictions are required
    if (username || subject || timeStart){
        DBQueryString += "WHERE "
    }

    // Remove username from parameters if it is undefined
    if (!username){
        delete DBQueryParam.username;
    } else {
        DBQueryString += "(user_name = :username) "
        if (subject || timeStart){
            DBQueryString += "AND "
        }
    }

    if (subject){
        DBQueryString += "(subject LIKE '%" + subject + "%') "
        if (timeStart){
            DBQueryString += "AND "
        }
    }

    if (!timeStart){
        delete DBQueryParam.timeStart;
        delete DBQueryParam.timeEnd;
    } else {
        DBQueryString += "(timing BETWEEN TO_DATE (:timeStart, 'yyyy/mm/dd') AND TO_DATE (:timeEnd, 'yyyy/mm/dd')) "
    }

    if (timeframe == "yearly") {
        DBQueryString += "GROUP BY CUBE(to_char(trunc(timing,'yy'), 'YYYY'))";
    } else if (timeframe == "monthly") {
        DBQueryString += "GROUP BY CUBE(to_char(trunc(timing,'MM'), 'YYYY-MM'))";
    } else if (timeframe == "weekly") {
        DBQueryString += "GROUP BY CUBE(to_char(NEXT_DAY(timing, 'SAT'), 'YYYY-MM-DD'))";
    };

    // console.log(DBQueryString);
    // console.log(DBQueryParam);
    oracledb.getConnection(dbConfig, function (err, connection) {
        if (err) {
            connectionError(err, res);
            return;
        }
       connection.execute(DBQueryString, DBQueryParam,
           {autoCommit: true},
           function (err, result) {
               if (err) {
                   executeError(err, res);
               } else {
                   res.send({success: true, results: result.rows});
               }
               doRelease(connection);
            }
        );
    });
});

//Disconnect from Oracle
function doRelease(connection)
{
  connection.release(
    function(err) {
      if (err) {
        console.error(err.message);
      }
    });
}


/**
* Connection Errors
*/

function connectionError(err, res) {
    console.error(err.message);
    res.status(500);
    res.send(
        {
            error: true,
            errorCode: 1,
            message: "Unable to connect to database. Please try again"
        }
    );
}

function executeError(err, res) {
    console.log(err.message);
    res.status(500);
    res.send(
        {
            error: true,
            errorCode: 3,
            message: "There was a problem retrieving data. Please try again",
            success: false
        }
    );
}

function notAuthorized(res) {
    res.status(401);
    res.send({
        error: true,
        errorCode: 99,
        message: "You are not authorized to view this data"
    });
}