web-monitor/server/model.js

const sqlite3 = require('sqlite3').verbose();
const {config} = require('./config');

//success->resolve(row)
//fail-> reject(err)
function authenticate(email,password){
	return new Promise((resolve,reject) =>{
		let db = new sqlite3.Database(config.database, (err) => {
			if (err) {
				reject(err.message);
			}
		});
		var sql = 'SELECT email, password FROM users WHERE email=(?) AND password=(?)';
		var params = [email,password];
		db.get(sql, params, (err,row)=>{
			if(err){
				//console.error(err.message);
				reject(err.message);
			}
			if(row){
				resolve(row);
			}
			else{
				reject('email not found');
			}
		});
		db.close((err)=>{
			if(err){
				reject(err.message);
			}
		});
		
	});
}

//success->resolve(row)
//fail-> reject(err)
function createUser(email,password){
	return new Promise((resolve, reject) => {
		let db = new sqlite3.Database(config.database, (err) => {
			if (err) {
				reject (err.message);
			}
		});
		 db.serialize( () => {
			db.run('CREATE TABLE IF NOT EXISTS users(email TEXT NOT NULL PRIMARY KEY, password TEXT NOT NULL)', (err) => {
				if (err) {
					reject (err.message);
				}
			});
			
			var sql = 'INSERT INTO users (email, password) VALUES (?,?)';
			var params = [email, password];
			
			 db.run(sql, params,  (err) => {
				if (err) {
					reject (err.message);
				}
				else{
					 resolve(email); //TODO resolve with entire ROW
				}
			});
		});
		
		db.close((err) => {
			if (err) {
				reject (err.message);
			} 
		});
		
  })
}

//success->resolve(row)
//fail-> reject(err)
function updateLastModified(rowid, last_change){
	return new Promise((resolve,reject) =>{
		//console.log("data in model is "+data);
		let db = new sqlite3.Database(config.database, (err) => {
			if (err) {
				reject(err.message);
			}
		});
		var sql = 'UPDATE monitors SET last_change = (?) WHERE rowid=(?)';
		var params = [last_change, rowid];
		db.run(sql, params,  (err) => {
			if (err) {
				reject(err.message);
			}
			else{
				resolve(); //TODO resolve entire row
			}
		});
		db.close((err) => {
			if (err) {
				reject(err.message);
			} 
		});
	});
} 
//success->resolve(row.last_change)
//fail-> reject(err.message)
function getLastModified(rowid){
	return new Promise((resolve,reject)=>{
		let db = new sqlite3.Database(config.database, (err) => {
			if (err) {
				resolve({accepted:false, message:err.message});
			}
		});
		var sql = 'SELECT last_change FROM monitors WHERE rowid=(?)';
		var params = [rowid];
		db.get(sql,params, (err,row) =>{
			if(err){
				reject(err.mesasage);
			}
			else if(row){
				resolve(row.last_change);
			}
			else{
				reject("row not found")
			}
		});
	});
}

//success->resolve(row)
//fail-> reject(err.message)
function createMonitor(name, url, email, last_modified){
	return new Promise((resolve, reject) =>{
		let db = new sqlite3.Database(config.database, (err) => {
			if (err) {
				reject(err.message);
			}
		});
		db.serialize( () => {
		

			var sql = 'INSERT INTO monitors (email, name, url, notify, last_change) VALUES (?,?,?,?,?)';

			var params = [email, name, url,1,last_modified];
			 db.run(sql, params,  (err) => {
				if (err) {
					reject(err.message);
				}
				else{
					resolve(); //TODO update query to return row inserted
				}
			});
		});
		
		db.close((err) => {
			if (err) {
				resolve({accepted:false, message:err.message});
			}
		});
	});
}
//success->resolve(row)
//fail-> reject(err.message)
function removeMonitors(rowids){
	return new Promise((resolve,reject) =>{
		let db = new sqlite3.Database(config.database, (err) => {
			if (err) {
				reject(err.message);
			}
		});

		var sql = 'DELETE FROM monitors WHERE rowid in (?';
			for(var i = 1; i < rowids.length; ++i)
				sql+=',?';
			sql+=')';
		//console.log("sql is "+sql);
		var params = rowids;
		db.run(sql,params, function(err){
			if(err){
				reject(err.message)
			}
			else{
				resolve(); //TODO resolve with reference to removed rows
			}
		});
	});
}

//success->resolve(rows)
//fail-> reject(err.message)
function getAllMonitors(email){
	return new Promise((resolve, reject) =>{
		let db = new sqlite3.Database(config.database, (err) => {
			if (err) {
				reject(err.message);
			}
		});

		db.serialize( () => {
			db.run('CREATE TABLE IF NOT EXISTS monitors('
			+'email TEXT NOT NULL'
			+',name TEXT NOT NULL'
			+',url TEXT NOT NULL'
			+',notify INTEGER NOT NULL'
			+',last_change INTEGER'
			+',last_check INTEGER'
			+',check_frequency INTEGER'
			+',page_data BLOB'
			+',FOREIGN KEY(email) REFERENCES users(email)'
			+')', (err) => {
				if (err) {
					reject(err.message);
				}
			});
			var sql = 'SELECT rowid,email,name,url,notify,last_change,last_check FROM monitors WHERE email=(?)';
			var params = [email];
			db.all(sql, params, (err,rows)=>{
				if(err){
					reject(err.message);
				}
				if(rows){
					resolve(rows);
				}
				else{
					resolve();
				}
			});
		});
		
		db.close((err)=>{
			if(err){
				reject(err.message);
			}
		});
	});
}

//success->resolve(rows)
//fail-> reject(err.message)
function getAllMonitorsGlobal(){
	return new Promise((resolve, reject) =>{
	
		let db = new sqlite3.Database(config.database, (err) => {
			if (err) {
				reject(err.message);
			}
		});
		var sql = 'SELECT rowid,email,name,url,notify,last_change,last_check FROM monitors';
		db.all(sql, [], (err,rows)=>{
			if(err){
				reject(err.message);
			}
			if(rows){
				resolve(rows);
			}
			else{
				reject(err.message);
			}
		});
		db.close((err)=>{
			if(err){
				reject(err.message);
			}
		});
	});
}



module.exports={
	createUser,
	authenticate,
	getAllMonitors,
	createMonitor,
	removeMonitors,
	getLastModified,
	updateLastModified,
	getAllMonitorsGlobal

}