9.1 准备工作
安装MySQL:
从npm添加mysql模块:
9.2 创建数据库模式
$ mysql -u root -p < schema.sql
DROP DATABASE IF EXISTS PhotoAlbums;
CREATE DATABASE PhotoAlbums
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
USE PhotoAlbums;
CREATE TABLE Albums
(
name VARCHAR(50) UNIQUE PRIMARY KEY,
title VARCHAR(100),
date DATETIME,
description VARCHAR(500),
-- allow for sorting on date.
INDEX(date)
)
ENGINE = InnoDB;
CREATE TABLE Photos
(
album_name VARCHAR(50),
filename VARCHAR(50),
description VARCHAR(500),
date DATETIME,
FOREIGN KEY (album_name) REFERENCES Albums (name),
INDEX (album_name, date)
)
ENGINE = InnoDB;
CREATE TABLE Users
(
user_uuid VARCHAR(50) UNIQUE PRIMARY KEY,
email_address VARCHAR(150) UNIQUE,
display_name VARCHAR(100) NOT NULL,
password VARCHAR(100),
first_seen_date BIGINT,
last_modified_date BIGINT,
deleted BOOL DEFAULT false,
INDEX(email_address),
INDEX(user_uuid)
)
ENGINE = InnoDB;
9.3 基本数据库操作
连接数据库:
添加查询:
var mysql = require('mysql'),
async = require('async');
var host = "localhost";
var database = "PhotoAlbums";
var user = "root";
var password = "secret";
/**
* Don't forget that for waterfall, it will stop and call the final
* "cleanup" function whenever it sees an error has been passed to
* one of the callback functions.
*
* Also, if a parameter is given to the callback, it will include
* those in the next function called in the waterfall.
*/
var dbclient;
async.waterfall([
// 1. create database connection
function (cb) {
console.log("\n** 1. create connection.");
dbclient = mysql.createConnection({
host: host,
user: user,
password: password,
database: database,
});
dbclient.connect(cb);
},
// 2. let's add a couple of albums. we will run them as separate
// queries.
function (results, cb) {
console.log("\n** 2. create albums.");
dbclient.query(
"INSERT INTO Albums VALUES (?, ?, ?, ?)",
[ "italy2012", "Spring Festival in Italy", "2012-02-15",
"I went to Italy for Spring Festival" ],
cb);
},
function (results, fields, cb) {
console.log(arguments);
console.log(fields);
console.log("\n** 2b. create albums.");
dbclient.query(
"INSERT INTO Albums VALUES (?, ?, ?, ?)",
[ "australia2010", "Vacation Down Under", "2010-10-20",
"Spent some time in Australia visiting Friends" ],
cb);
},
function (results, fields, cb) {
console.log(fields);
console.log("\n** 2c. create albums.");
dbclient.query(
"INSERT INTO Albums VALUES (?, ?, ?, ?)",
[ "japan2010", "Programming in Tokyo", "2010/06/10",
"I worked in Tokyo for a while." ],
cb);
},
// 3. let's add some photos to albums
function (results, fields, cb) {
console.log(fields);
// mysql is cool with this date format.
var pix = [
{ filename: "picture_01.jpg",
albumid: "italy2012",
description: "rome!",
date: "2012/02/15 16:20:40" },
{ filename: "picture_04.jpg",
albumid: "italy2012",
description: "fontana di trevi",
date: "2012/02/19 16:20:40" },
{ filename: "picture_02.jpg",
albumid: "italy2012",
description: "it's the vatican!",
date: "2012/02/17 16:35:04" },
{ filename: "picture_05.jpg",
albumid: "italy2012",
description: "rome!",
date: "2012/02/19 16:20:40" },
{ filename: "picture_03.jpg",
albumid: "italy2012",
description: "spanish steps",
date: "2012/02/18 16:20:40" },
{ filename: "photo_05.jpg",
albumid: "japan2010",
description: "something nice",
date: "2010/06/14 12:21:40" },
{ filename: "photo_01.jpg",
albumid: "japan2010",
description: "tokyo tower!",
date: "2010/06/11 12:20:40" },
{ filename: "photo_06.jpg",
albumid: "japan2010",
description: "kitty cats",
date: "2010/06/14 12:23:40" },
{ filename: "photo_03.jpg",
albumid: "japan2010",
description: "shinjuku is nice",
date: "2010/06/12 08:40:40" },
{ filename: "photo_04.jpg",
albumid: "japan2010",
description: "eating sushi",
date: "2010/06/12 08:34:40" },
{ filename: "photo_02.jpg",
albumid: "japan2010",
description: "roppongi!",
date: "2010/06/12 07:44:40" },
{ filename: "photo_07.jpg",
albumid: "japan2010",
description: "moo cow oink pig woo!!",
date: "2010/06/15 12:55:40" },
{ filename: "photo_001.jpg",
albumid: "australia2010",
description: "sydney!",
date: "2010/10/20 07:44:40" },
{ filename: "photo_002.jpg",
albumid: "australia2010",
description: "asdfasdf!",
date: "2010/10/20 08:24:40" },
{ filename: "photo_003.jpg",
albumid: "australia2010",
description: "qwerqwr!",
date: "2010/10/20 08:55:40" },
{ filename: "photo_004.jpg",
albumid: "australia2010",
description: "zzzxcv zxcv",
date: "2010/10/21 14:29:40" },
{ filename: "photo_005.jpg",
albumid: "australia2010",
description: "ipuoip",
date: "2010/10/22 19:08:40" },
{ filename: "photo_006.jpg",
albumid: "australia2010",
description: "asdufio",
date: "2010/10/22 22:15:40" }
];
var q = "\
INSERT INTO Photos (filename, album_name, description, date) \
VALUES (?, ?, ?, ?)";
console.log("\n** 3. Add pictures.");
async.forEachSeries(
pix,
// run the query and call clbk to do next in array
// we do in serial because connection only does
// one thing at a time.
function (item, clbk) {
dbclient.query(
q,
[ item.filename, item.albumid,
item.description, item.date ],
clbk);
},
cb);
},
function (cb) {
console.log(arguments);
// 4. list all albums
console.log("\n** 4. list albums");
dbclient.query("SELECT * FROM Albums ORDER BY date DESC", cb);
},
function (rows, fields, cb) {
console.log(fields);
console.log(" -> dumping albums:");
for (var i = 0; i < rows.length; i++) {
console.log(" -> Album: " + rows[i].name
+ " (" + rows[i].date + ")");
}
// 5. find italy2012 album.
console.log("\n** 5. Find album.");
dbclient.query(
"SELECT * FROM Albums WHERE name = ?",
[ "italy2012" ],
cb);
},
function (rows, fields, cb) {
console.log(fields);
console.log(" -> dumping italy2012:");
for (var i = 0; i < rows.length; i++) {
console.log(" -> Album: " + rows[i].name
+ " (" + rows[i].date + ")");
}
// 6. find all photos in italy2012 album. sort by date,
// and return subset
console.log("\n** 6. Photos for albums.");
var q = "\
SELECT * FROM Photos WHERE album_name = ?\
ORDER BY date DESC LIMIT ?, ?";
dbclient.query(q, ["italy2012", 2, 5 ], cb);
},
function (rows, fields, cb) {
console.log(fields);
console.log(" -> dumping italy2012 photos:");
for (var i = 0; i < rows.length; i++) {
console.log("Photo: " + rows[i].filename
+ " (" + rows[i].date + ")");
}
// 7. replace the description in a photo
console.log("\n** 7. update photo.");
dbclient.query(
"UPDATE Photos SET description = ? \
WHERE album_name = ? AND filename = ?",
[ "NO SHINJUKU! BAD!", "italy2012", "picture_03.jpg" ],
cb);
},
function (results, fields, cb) {
console.log(fields);
console.log(results);
console.log(" -> updated rows: " + results.affectedRows);
if (results.affectedRows != 1) {
cb(new Error("CRAP TEST 7 didn't affect 1 row!"));
return;
}
// 8. delete a photo
console.log("\n** 8. delete photo.");
dbclient.query(
"DELETE FROM Photos WHERE filename = ? AND album_name = ?",
[ "photo_04.jpg", "japan2010" ],
cb);
},
function (results, fields, cb) {
console.log(fields);
console.log(results);
console.log(" -> deleted rows: " + results.affectedRows);
if (results.affectedRows != 1) {
cb(new Error("CRAP TEST 8 didn't affect 1 row!"));
return;
}
// 9. delete an entire album and its photos.
// a. delete photos
console.log("\n** 9. delete entire album and photos");
dbclient.query(
"DELETE FROM Photos WHERE album_name = ?",
[ "australia2012" ],
cb);
},
function (results, fields, cb) {
console.log(fields);
console.log(" -> delete photos rows: " + results.affectedRows);
console.log(results);
// b. delete the album
dbclient.query(
"DELETE FROM Albums WHERE name = ?",
[ "australia2012" ],
cb);
},
function (results, fields, cb) {
console.log(fields);
console.log(" -> delete album rows: " + results.affectedRows);
console.log(results);
// 10. ask for an album that doesn't exist.
console.log("\n** 10. Search for non-existant album.");
dbclient.query(
"SELECT * FROM Albums WHERE name = ?",
[ "asdfasdf" ],
cb);
},
function (rows, fields, cb) {
console.log(fields);
console.log(" -> asked for bogus, got " + rows.length + " rows");
cb(null);
}
],
// waterfall cleanup function
function (err, results) {
if (err) {
console.log("Aw, there was an error: ");
console.log(err);
} else {
console.log("All operations completed without error.");
}
dbclient.end();
});
9.4 添加应用身份验证
9.5 资源池
9.6 验证API