从零开始学习Node.js系列教程之SQLite3和MongoDB用法分析

var util = require('util');var async = require('async');  //npm install asyncvar notesdb = require('./nodesdb-sqlite3');// var notesdb = require('./notesdb-mongoose');notesdb.connect(function(error){  if (error) throw error;});notesdb.setup(function(error){  if (error){    util.log('ERROR ' + error);    throw error;  }  async.series([  //async.series函数可以控制函数按顺序执行，从而保证最后的函数在所有其他函数完成之后执行    function(cb){      notesdb.add("test", "testtest",      function(error){        if (error) util.log('ERROR ' + error);        cb(error);      });    }  ],    function(error, results){      if (error) util.log('ERROR ' + error);      notesdb.disconnect(function(err){});    }  );});

//数据库接口库var util = require('util');var sqlite3 = require('sqlite3');sqlite3.verbose();var db = undefined;/* 数据库名是直接硬编码的，所以当调用connect和setup函数时，当前目录中就会生成chap06.sqlite3文件 */exports.connect = function(callback){  db = new sqlite3.Database("chap06.sqlite3", sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE,    function(err){      if (err){        util.log('FAIL on creating database ' + err);        callback(err);      } else {        callback(null);      }    });}//此处的disconnect函数是空的exports.disconnect = function(callback){  callback(null);}exports.setup = function(callback){  db.run("CREATE TABLE IF NOT EXISTS notes " +    "(ts DATETIME, author VARCHAR(255), note TEXT)",    function(err){      if (err){        util.log('FAIL on creating table ' + err);        callback(err);      } else {        callback(null);      }    });}exports.emptyNote = {"ts": "", author: "", note: ""};exports.add = function(author, note, callback){  db.run("INSERT INTO notes (ts, author, note) " +    "VALUES (?, ?, ?);",    [new Date(), author, note],    function(error){      if (error){        util.log('FAIL on add ' + error);        callback(error);      } else {        callback(null);      }    });}/*run函数接受一个字符串参数，其中?表示占位符，占位符的值必须通过一个数组传递进来调用者提供了一个回调函数，然后通过这个回调函数来声明错误 */exports.delete = function(ts, callback){  db.run("DELETE FROM notes WHERE ts = ?;",    [ts],    function(err){      if (err){        util.log('FAIL to delete ' + err);        callback(err);      } else {        callback(null);      }    });}exports.edit = function(ts, author, note, callback){  db.run("UPDATE notes " +    "SET ts = ?, author = ?, note = ? " +    "WHERE ts = ?",    [ts, author, note, ts],    function(err){      if (err){        util.log('FAIL on updating table ' + err);        callback(err);      } else {        callback(null);      }    });}exports.allNotes = function(callback){  util.log(' in allnote');  db.all("SELECT * FROM notes", callback);}exports.forAll = function(doEach, done){  db.each("SELECT * FROM notes", function(err, row){    if (err){      util.log('FAIL to retrieve row ' + err);      done(err, null);    } else {      doEach(null, row);    }  }, done);}/*allNotes和forAll函数是操作所有数据的两种方法，allNotes把数据库中所有的数据行收集到一个数组里，而forAll方法可以接受两个回调函数，每当从数据集中拿一行数据，回调函数doEach都会执行一遍，当读完所有数据时，回调函数done就会执行 */exports.findNoteById = function(ts, callback){  var didOne = false;  db.each("SELECT * FROM notes WHERE ts = ?",    [ts],    function(err, row){      if (err){        util.log('FAIL to retrieve row ' + err);        callback(err, null);      } else {        if (!didOne){          callback(null, row);          didOne = true;  //保证回调函数只被执行一次        }      }    });}

npm install mongoose

var util = require('util');var mongoose = require('mongoose');var Schema = mongoose.Schema;var dburl = 'mongodb://localhost/chap06'; //dburl用于连接已运行的MongoDBexports.connect = function(callback){  mongoose.connect(dburl);}exports.disconnect = function(callback){  mongoose.disconnect(callback);}exports.setup = function(callback){callback(null);}//定义模式var NoteSchema = new Schema({  ts: {type: Date, default: Date.now}, //默认值  author: String,  note: String});//将NoteSchema作为Mongoose的模型注册进去mongoose.model('Note', NoteSchema);var Note = mongoose.model('Note');exports.emptyNote = {"_id": "", author: "", note: ""};exports.add = function(author, note, callback){  var newNote = new Note();  newNote.author = author;  newNote.note = note;  newNote.save(function(err){    if (err){      util.log('FATAL ' + err);      callback(err);    } else {      callback(null);    }  });}exports.delete = function(id, callback){  exports.findNoteById(id, function(err, doc){    if (err){      callback(err);    } else {      util.log(util.inspect(doc));      doc.remove();      callback(null);    }  });}exports.edit = function(id, author, note, callback){  exports.findNoteById(id, function(err, doc){    if (err){      callback(err);    } else {      doc.ts = new Date();      doc.author = author;      doc.note = note;      doc.save(function(err){        if (err){          util.log('FATAL ' + err);          callback(err);        } else {          callback(null);        }      });    }  });}exports.allNotes = function(callback){  Note.find({}, callback);}exports.forAll = function(doEach, done){  Note.find({}, function(err, docs){    if (err){      util.log('FATAL ' + err);      done(err, null);    }    docs.forEach(function(doc){      doEach(null, doc);    });    done(null);  });}/*_id字段是MongoDB提供的全局唯一的ID，用于标识存储的文档 */var findNoteById = exports.findNoteById = function(id, callback){  Note.findOne({_id: id}, function(err, doc){    if (err){      util.log('FATAL ' + err);      callback(err, null);    }    callback(null, doc);  });}

//在数据库需要放置在一台计算机上时，应该考虑使用SQLite3//控制器，在nodesdb-sqlite3.js和notesdb-mongoose.js模块之间切换var util = require('util');var url = require('url');var express = require('express');var nmDbEngine = 'sqlite3'; //用于命名数据库引擎、选择合适的notesdb实现和选择合适的views目录//var nmDbEngine = 'mongoose';var notesdb = require('./nodesdb-' + nmDbEngine);var app = express();app.use(express.logger());app.use(express.cookieParser()); //添加cookieParser中间件app.use(express.bodyParser());app.engine('.html', require('ejs').__express);  //3.X//app.register('.html', require('ejs'));  //2.Xapp.set('views', __dirname + '/views-' + nmDbEngine);app.set('view engine', 'ejs');//是一个路由中间件函数，用于在一些路由器函数中解析URL查询参数var parseUrlParams = function(req, res, next){  req.urlP = url.parse(req.url, true);  next();}//检查用户是否被允许访问，这里只检查cookie是否等于AOK，这个单词通常意味着一切都没问题/* 很多应用都需要用户登录，然后用户才能进行一些特权操作。由于HTTP是一个无状态的协议， 验证用户的唯一方式就是发送一个cookie到浏览器上，然后验证标识符。cookie包含了应用中用于验证用户的数据。 cookieParser中间件在这里做了很多工作，查找cookie，解析cookie，然后将解析出来的值让到req对象中。 当存在cookie时，它的值会被放入req.cookies中。 */var checkAccess = function(req, res, next){  if (!req.cookies || !req.cookies.notesaccess || req.cookies.notesaccess !== "AOK"){    res.redirect('/login');  } else {    next();  }}notesdb.connect(function(error){  if (error) throw error;})app.on('close', function(error){  notesdb.disconnect(function(err){});});app.get('/', function(req, res) {res.redirect('/view');});app.get('/view', checkAccess, function(req, res){ //可以在每个路由上加checkAccess检查  notesdb.allNotes(function(err, notes){    if (err){      util.log('ERROR ' + err);      throw err;    } else {      res.render('viewnotes.html', {title: "Notes ("+ nmDbEngine +")", notes: notes});    }  });});/*当用户点击ADD按钮时app.get('/add', ...)内的函数就会被调用，浏览器会发出一个发往/add的HTTP GET请求。这个函数使用addedit.html模板来创建一个表单，让用于通过这个表单输入标签，然后通过单击SUBMIT按钮提交，当用户提交表单，浏览器就会发出一个HTTP POST请求，app.post('/add', ...)内的函数就会被调用，用户输入的数据会被存放在请求主体中，而请求主体会被bodyParser(app.use(express.bodyParser()))中间件处理并存放在req.body中 */app.get('/add', function(req, res){  res.render('addedit.html', {title: "Notes ("+ nmDbEngine +")", postpath: '/add', note: notesdb.emptyNote});});app.post('/add', function(req, res){  notesdb.add(req.body.author, req.body.note,    function(error){      if (error) throw error;      res.redirect('/view');    });});app.get('/del', parseUrlParams, function(req, res){  notesdb.delete(req.urlP.query.id,    function(error){      if (error) throw error;      res.redirect('/view');    });});app.get('/edit', parseUrlParams, function(req, res){  notesdb.findNoteById(req.urlP.query.id,    function(error, note){      if (error) throw error;      res.render('addedit.html',        {title: "Notes ("+ nmDbEngine +")", postpath: '/edit', note: note});    });});app.post('/edit', function(req, res){  notesdb.edit(req.body.id, req.body.author, req.body.note,    function(error){      if (error) throw error;      res.redirect('/view');    });});app.get('/login', function(req, res){  res.render('login.html', {title: "Notes LOGIN ("+ nmDbEngine +")"});});app.post('/login', function(req, res){  //此处可以添加检查用户信息的逻辑  //...  res.cookie('notesaccess', 'AOK');  res.redirect('/view');});app.listen(3000);

//控制台显示var util = require('util');var notesdb = require('./notesdb-sqlite3');// var notesdb = require('./notesdb-mongoose');notesdb.connect(function(error){  if (error) throw error;});notesdb.forAll(function(error, row){  util.log('ROW: ' + util.inspect(row));}, function(error){  if (error) throw error;  util.log('ALL DONE');  notesdb.disconnect(function(err){});});

<!DOCTYPE html><html><head>  <title><%= title%></title></head><body>  <h1><%= title%></h1>  <p><a href='/view'>View</a> | <a href='/add'>Add</a></p></body></html>

<% include layout.html %><table><% notes.forEach(function(note){ %>  <tr>    <td>      <p><%=new Date(note.ts).toString()%>: by <b><%=note.author%></b></p>      <p><%=note.note%></p>    </td>    <td>      <form method="get" action="/del">        <input type="submit" value="Delete" />        <input type="hidden" name="id" value="<%=note.ts%>" />      </form>      <br/>      <form method="get" action="/edit">        <input type="submit" value="Edit" />        <input type="hidden" name="id" value="<%=note.ts%>" />      </form>    </td>  </tr>  <% }); %></table>

<% include layout.html %><form method="post" action="<%=postpath%>">  <% if (note){ %>  <input type="hidden" name="id" value="<%=note.ts%>" />  <% } %>  <input type="text" name="author" value="<%=note.author%>" />  <br/>  <textarea rows="5" cols="40" name="note">    <%=note.note%>  </textarea>  <br/>  <input type="submit" value="Submit" /></form>

<% include layout.html %><form method="POST" action="/login">  <p>Click the <i>Login</i> to log in.</p>  <input type="submit" value="Login" /></form>