The purpose of this assignment is to learn about relational databases and how to use a relational database called PostqreSQL.
This assignment builds on the gift assignment. We will replace the CouchDB database in the gift assignment with a PostgreSQL database.
This assignment requires that you have access to a PostgreSQL database. Install PostgreSQL and start it. How you do this depends on your operating system and maybe other factors.
Under OS X ...
Download the Postgres.app and copy it into your Applications folder. Add the following export to ~/.bash_profile; this will let you easily launch the command line tools inside a terminal window.
export PATH="/Applications/Postgres93.app/Contents/MacOS/bin:$PATH"Double click the Postgres.app to start it. It will run in the background from that point.
Under Windows ...
Use the standard installer for Windows. During installation and setup, select defaults when available. Leave the username as postgres but you will need to pick a password. The password is not critical, unless you plan to open up your database to the outside, so set something easy to remember. Do not run StackBuilder; exit when you reach this point.
To run the PostgreSQL server, run the following from the command line. Adjust the path to match the location of runpsql.bat in your system.
"C:\Program Files\PostgreSQL\9.3\scripts\runpsql.bat"Run the following script in terminal window to set up the environmental variables to run PostgreSQL commands at the command line.
"C:\Program Files\PostgreSQL\9.3\pg_env.bat"
In JB 359 ...
# Download source distribution of postgresql. cd git clone https://github.com/postgres/postgres.git postgres-src # Build and install postgresql. cd postgres-src ./configure --prefix=$HOME/postgres/ make make install # Create the server's data folder. ~/postgres/bin/initdb -D ~/postgres/data/ # Create the gift database. ~/postgres/bin/createdb gift # Start the postgres server. ~/postgres/bin/postgres -D ~/postgres/data/ # Run the command line client . ~/postgres/bin/psql -h localhost -d gift # Shutdown the postres server. ~/postgres/bin/pg_ctl -D ~/postgres/data/ stop
After starting the server, run the following in a terminal window to create a database named gift.
createdb gift
To become familiar with the SQL language, read the PostgreSQL documentation. The following are the SQL operations used in this assignment.
Find and do a tutorial to learn the basics of SQL using the PostgreSQL database. The tutorial in the PostgreSQL documentation may be sufficient.
The starting point for this assignment is the code you developed for the gift assignment. Make a copy of your gift folder and name it psql.
We will completely replace the contents of the scripts folder. For this reason, start by deleting the files in ~/405/psql/scripts.
Inside the psql/scripts folder, create file gift.sql with the following contents. This script creates a table named users and inserts two rows into the table.
DROP TABLE users;
CREATE TABLE users (
_id varchar(80) primary key,
_rev varchar(80),
pw varchar(80),
balance real,
gems int,
score int
);
INSERT INTO users VALUES ('a', '0', 'a', 5, 0, 0);
INSERT INTO users VALUES ('b', '0', 'b', 0, 0, 0);
Create local.sh with the following contents. (Name the file local.bat if you are on Windows.)
psql -h localhost -d gift -f gift.sql
Run local.sh to create the gift database with the local PostgreSQL server.
With CouchDB we could easily interact directly with the database because the interaction is based on HTTP protocol and JSON. This is not the case with PostgreSQL. Rather than implementing the communication protocol needed, we will use a Node.js library called pg to simplify the process of issuing commands to the database and receiving their reults.
Install a PostgreSQL driver.
cd ~/405 npm install pg
The above command creates a folder named node_modules
in the root level of the repository.
When we run Node.js in the psql subfolder,
Node.js searches upward through the parent folders of the current directory
for the presence of node_module folders.
It will search these folders for modules that are loaded through the require function.
For this reason, we can place library dependencies in ~/405.
Also, we do this in prepartion for the next assignment,
where we need to have the pg located at this level.
In the gift application server code we separated database interaction from
the resource request handlers by placing all database code in a module
named db.
This organization is an example of the principle of
separation of concerns.
This gives us a big benefit at this point;
we only need to modify a single module
in order to replace the underlying database.
All other code can remain unchanged.
Replace db.js with the following.
On Windows, replace
'postgres://localhost/gift'with'postgres://postgres@localhost/gift'in the following.
var pg = require('pg');
var url = 'postgres://localhost/gift'; // or 'postgres://postgres@localhost/gift'
exports.init = function(cb) {
pg.connect(url, function(err, client, done) {
if (err) throw err;
client.query("select * from users where _id = 'a'", function (err, result) {
done();
if (err) throw err;
cb();
})
});
};
exports.getDoc = function(_id, cb) {
pg.connect(url, function(err, client, done) {
if (err) return cb(err);
client.query('select * from users where _id = $1', [_id], function (err, result) {
done();
if (err) return cb(err);
if (result.rows.length === 0) {
return cb(null, null);
}
cb(null, result.rows[0]);
});
});
};
exports.updateDoc = function(doc, cb) {
pg.connect(url, function(err, client, done) {
if (err) return cb(err);
var nextRev = (parseInt(doc._rev) + 1).toString();
client.query("update users set balance = $1, gems = $2, score = $3, _rev = $4 where _id = $5 and _rev = $6",
[doc.balance, doc.gems, doc.score, nextRev, doc._id, doc._rev],
function (err, result) {
done();
if (err) {
cb(err);
} else if (result.rowCount === 0) {
// Assume the doc is there. If it's not, I think the code still works fine.
cb(null, { old: true });
} else {
cb(null, { rev: nextRev });
}
});
});
};
Use a browser to test that the app works.