SQL编程语言讲解、辅导database程序设计
- 首页 >> Matlab编程 In this assignment you will write a Node.js & Express RESTful E-Mail API backed by a PostgreSQL database and
associated tests to demonstrate it works as required.
Installation
See instructions in Assignment 2 and ensure you are running the current LTS version of Node.js.
You will also need to download and install Docker Desktop:
Setup
Download the starter code archive from Canvas and expand into an empty folder. I recommend, if you have not
already done so, creating a folder for the class and individual folders beneath that for each assignment.
The starter code archive contains three files that you will modify:
api/openapi.yaml
src/app.js
test/mail.test.js
And some files that you should not modify:
.env
.eslintrc.js
docker-compose.yml
package.json
sql/database.sql
sql/mail.sql
sql/schema.sql
src/server.js
To setup the development environment, navigate to the folder where you extracted the starter code and run
the following command:
$ npm install
This will take some time to execute as npm downloads and installs all the packages required to build the
assignment.
Now start the development database:
$ docker-compose up -d
To stop the development database later: (do not do this now, do it when you’re done with the assignment)
$ docker-compose down
Summer 2021 - Assignment 7
https://www.docker.com/products/docker-desktop
To start the dev server, run the following command:
$ npm start
The first time this runs it will take a while to download and install the Docker PostgreSQL image and start the
database service for your server to run against.
To execute tests run the following command:
$ npm test
To run the linter against your code, run the following command:
$ npm run lint
Background
E-Mails in this system have the following properties:
• id Universally Unique Identifier https://www.uuidtools.com/what-is-uuid
• from See below
• to See below
• subject String
• sent ISO Format Date String
• received ISO Format Date String
• content String
Where form and to have the following properties:
• name String
• email E-Mail Address
TAKE CARE: THIS IS NOT THE SAME AS ASSIGNMENT 6
Note that your API must be constrained by an OpenAPI version 3.0.3 schema. You should endeavor to make this
schema as restrictive as possible.
For example, your schema should specify the acceptable format of the e-mail id property. If an id in any other form
is presented, your system should reject it simply by performing the validation provided in the starter code.
When you start the server, use a browser to visit http://localhost:3010/v0/api-docs/ where you can manually test
your API as demonstrated in class for the books example.
Initially, the only operation available will be:
Which will not work until you implement the necessary Express route.
Once that is working, you will need to add additional endpoints in the OpenAPI schema and additional Express
routes to service them.
If you have no idea what any of those terms represent, view the recording of lecture 12 “Node.js & Express”, and
study the handouts from the same lecture before consulting with the TAs and/or Instructor.
Resetting Docker
If you run into problems with your dev database, or simply want to re-set it to its initial state, issue the following
commands to shut docker down:
$ docker stop $(docker ps -aq)
$ docker rm $(docker ps -aq)
Then restart the development database:
$ docker-compose up -d
Database Schema
The supplied database schema can be found in sql/schema.sql. It defines a single table ‘mail’ with the
following columns:
id UUID Unique e-mail identifier
mailbox String Name of the mailbox the email is currently in
mail JSON The email itself
Requirements
Basic:
• Write an OpenAPI compliant E-Mail RESTful API, backed by the PostgreSQL database, with the
following informally described endpoints:
o GET /v0/mail
Return all the emails in the system as an array of named mailboxes containing arrays of e-mails, one
per mailbox. For example:
[
{"name": "inbox", "mail": []},
{"name": "sent", "mail": []},
{"name": "trash", "mail": []},
...
]
Note that all returned e-mails should have had their ‘content’ property removed.
o GET /v0/mail?mailbox={mailbox}
Return all the emails in a specified mailbox as an array containing one named mailbox containing an
array of e-mails in the mailbox. Throw a 404 error if the mailbox is unknown. For example, if the query
parameter “mailbox” was equal to ‘trash”, return:
[ {"name": "trash", "mail": []} ]
Note that the returned e-mails should have had their ‘content’ property removed.
o GET /v0/mail/{id}
Return the e-mail identified by id, including its ‘content’ property. Throw a 404 error if id does not
identify a known e-mail.
o POST /v0/mail
Save a new email sent in the HTTP request body into the sent mailbox and return the newly created email.
The id, and from properties should be missing on submission and set on return; id should be
set to a valid UUID, from.email and from.name should be as for all existing emails in the sent
mailbox. The sent and received properties should also be missing from the submitted email with
sent being set appropriately on return with received holding the same value. Throw a 400 error if
the submitted e-mail has any unexpected properties.
o PUT /v0/mail/{id}?mailbox={mailbox}
Move the e-mail identified by id into the named mailbox. If the mailbox does not exist, create it and
move the identified e-mail into it. Return an HTTP 204 on success and throw a 404 error if id does not
identify a known e-mail. Throw a 409 error if the named mailbox is ‘sent’ and the mail identified by id
is not already in the sent mailbox.
Advanced:
• Write tests to demonstrate your implementation satisfies the Basic Requirements.
• Show your tests achieve 100% line, statement, branch, and function code coverage.
• Demonstrate zero lint errors or warnings in your code.
Stretch:
• Modify your OpenAPI schema to include the following informally described endpoint:
o GET /v0/mail?from={from}
Returns an array of named mailboxes containing arrays of e-mails sent by the individual identified by
from where from can be a full or partial name like ‘jOhn sMiTh’ or ‘JoN’ or a correctly formatted email
address like johnny@bigco.com. In both cases, from is case insensitive.
Note that the returned e-mails should have had their ‘content’ property removed.
• Write tests to demonstrate this new end point works.
• Continue to show zero lint errors and 100% code coverage.
What steps should I take to tackle this?
You should base your implementation on the Books Database Example from class. Start by implementing the
simple GET endpoints then move on to the more complex POST and PUT operations.
There is a huge amount of information available on OpenAPI 3.0.3 at https://swagger.io/specification/ and
http://spec.openapis.org/oas/v3.0.3. Also consult the Petstore example at https://petstore3.swagger.io/ and makes
use of the on-line schema validator at https://editor.swagger.io/.
A good resource for querying JSON stored in PostgreSQL: https://www.postgresqltutorial.com/postgresql-json/
How much code will I need to write?
Much of this implementation will be in the OpenAPI schema in api/openapi.yaml. if you find yourself writing
“defensively” in your JavaScript to check for incorrect data sent by the caller, you should instead make your
OpenAPI schema more robust such that it can be used to reject invalid submissions.
Assuming you OpenAPI schema is sufficiently robust, your solution is likely to contain somewhere in the region of
200 lines of code.
Grading scheme
The following aspects will be assessed:
1. (100%) Does it work?
a. Basic ( 50% )
b. Advanced ( 30% )
c. Stretch ( 20% )
2. (-100%) Did you give credit where credit is due?
a. Your submission is found to contain code segments copied from on-line resources and you failed to give
clear and unambiguous credit to the original author(s) in your source code (-100%). You will also be
subject to the university academic misconduct procedure as stated in the class academic integrity policy.
b. Your submission is determined to be a copy of a past or present student’s submission (-100%)
c. Your submission is found to contain code segments copied from on-line resources that you did give a
clear an unambiguous credit to in your source code, but the copied code constitutes too significant a
percentage of your submission:
o < 25% copied code No deduction
o 25% to 50% copied code (-50%)
o > 50% copied code (-100%)
What to submit
Run the following command to create the submission archive:
$ npm run zip
** UPLOAD CSE183.Assignment7.Submission.zip TO THE CANVAS ASSIGNMENT AND SUBMIT **
associated tests to demonstrate it works as required.
Installation
See instructions in Assignment 2 and ensure you are running the current LTS version of Node.js.
You will also need to download and install Docker Desktop:
Setup
Download the starter code archive from Canvas and expand into an empty folder. I recommend, if you have not
already done so, creating a folder for the class and individual folders beneath that for each assignment.
The starter code archive contains three files that you will modify:
api/openapi.yaml
src/app.js
test/mail.test.js
And some files that you should not modify:
.env
.eslintrc.js
docker-compose.yml
package.json
sql/database.sql
sql/mail.sql
sql/schema.sql
src/server.js
To setup the development environment, navigate to the folder where you extracted the starter code and run
the following command:
$ npm install
This will take some time to execute as npm downloads and installs all the packages required to build the
assignment.
Now start the development database:
$ docker-compose up -d
To stop the development database later: (do not do this now, do it when you’re done with the assignment)
$ docker-compose down
Summer 2021 - Assignment 7
https://www.docker.com/products/docker-desktop
To start the dev server, run the following command:
$ npm start
The first time this runs it will take a while to download and install the Docker PostgreSQL image and start the
database service for your server to run against.
To execute tests run the following command:
$ npm test
To run the linter against your code, run the following command:
$ npm run lint
Background
E-Mails in this system have the following properties:
• id Universally Unique Identifier https://www.uuidtools.com/what-is-uuid
• from See below
• to See below
• subject String
• sent ISO Format Date String
• received ISO Format Date String
• content String
Where form and to have the following properties:
• name String
• email E-Mail Address
TAKE CARE: THIS IS NOT THE SAME AS ASSIGNMENT 6
Note that your API must be constrained by an OpenAPI version 3.0.3 schema. You should endeavor to make this
schema as restrictive as possible.
For example, your schema should specify the acceptable format of the e-mail id property. If an id in any other form
is presented, your system should reject it simply by performing the validation provided in the starter code.
When you start the server, use a browser to visit http://localhost:3010/v0/api-docs/ where you can manually test
your API as demonstrated in class for the books example.
Initially, the only operation available will be:
Which will not work until you implement the necessary Express route.
Once that is working, you will need to add additional endpoints in the OpenAPI schema and additional Express
routes to service them.
If you have no idea what any of those terms represent, view the recording of lecture 12 “Node.js & Express”, and
study the handouts from the same lecture before consulting with the TAs and/or Instructor.
Resetting Docker
If you run into problems with your dev database, or simply want to re-set it to its initial state, issue the following
commands to shut docker down:
$ docker stop $(docker ps -aq)
$ docker rm $(docker ps -aq)
Then restart the development database:
$ docker-compose up -d
Database Schema
The supplied database schema can be found in sql/schema.sql. It defines a single table ‘mail’ with the
following columns:
id UUID Unique e-mail identifier
mailbox String Name of the mailbox the email is currently in
mail JSON The email itself
Requirements
Basic:
• Write an OpenAPI compliant E-Mail RESTful API, backed by the PostgreSQL database, with the
following informally described endpoints:
o GET /v0/mail
Return all the emails in the system as an array of named mailboxes containing arrays of e-mails, one
per mailbox. For example:
[
{"name": "inbox", "mail": [
{"name": "sent", "mail": [
{"name": "trash", "mail": [
...
]
Note that all returned e-mails should have had their ‘content’ property removed.
o GET /v0/mail?mailbox={mailbox}
Return all the emails in a specified mailbox as an array containing one named mailbox containing an
array of e-mails in the mailbox. Throw a 404 error if the mailbox is unknown. For example, if the query
parameter “mailbox” was equal to ‘trash”, return:
[ {"name": "trash", "mail": [
Note that the returned e-mails should have had their ‘content’ property removed.
o GET /v0/mail/{id}
Return the e-mail identified by id, including its ‘content’ property. Throw a 404 error if id does not
identify a known e-mail.
o POST /v0/mail
Save a new email sent in the HTTP request body into the sent mailbox and return the newly created email.
The id, and from properties should be missing on submission and set on return; id should be
set to a valid UUID, from.email and from.name should be as for all existing emails in the sent
mailbox. The sent and received properties should also be missing from the submitted email with
sent being set appropriately on return with received holding the same value. Throw a 400 error if
the submitted e-mail has any unexpected properties.
o PUT /v0/mail/{id}?mailbox={mailbox}
Move the e-mail identified by id into the named mailbox. If the mailbox does not exist, create it and
move the identified e-mail into it. Return an HTTP 204 on success and throw a 404 error if id does not
identify a known e-mail. Throw a 409 error if the named mailbox is ‘sent’ and the mail identified by id
is not already in the sent mailbox.
Advanced:
• Write tests to demonstrate your implementation satisfies the Basic Requirements.
• Show your tests achieve 100% line, statement, branch, and function code coverage.
• Demonstrate zero lint errors or warnings in your code.
Stretch:
• Modify your OpenAPI schema to include the following informally described endpoint:
o GET /v0/mail?from={from}
Returns an array of named mailboxes containing arrays of e-mails sent by the individual identified by
from where from can be a full or partial name like ‘jOhn sMiTh’ or ‘JoN’ or a correctly formatted email
address like johnny@bigco.com. In both cases, from is case insensitive.
Note that the returned e-mails should have had their ‘content’ property removed.
• Write tests to demonstrate this new end point works.
• Continue to show zero lint errors and 100% code coverage.
What steps should I take to tackle this?
You should base your implementation on the Books Database Example from class. Start by implementing the
simple GET endpoints then move on to the more complex POST and PUT operations.
There is a huge amount of information available on OpenAPI 3.0.3 at https://swagger.io/specification/ and
http://spec.openapis.org/oas/v3.0.3. Also consult the Petstore example at https://petstore3.swagger.io/ and makes
use of the on-line schema validator at https://editor.swagger.io/.
A good resource for querying JSON stored in PostgreSQL: https://www.postgresqltutorial.com/postgresql-json/
How much code will I need to write?
Much of this implementation will be in the OpenAPI schema in api/openapi.yaml. if you find yourself writing
“defensively” in your JavaScript to check for incorrect data sent by the caller, you should instead make your
OpenAPI schema more robust such that it can be used to reject invalid submissions.
Assuming you OpenAPI schema is sufficiently robust, your solution is likely to contain somewhere in the region of
200 lines of code.
Grading scheme
The following aspects will be assessed:
1. (100%) Does it work?
a. Basic ( 50% )
b. Advanced ( 30% )
c. Stretch ( 20% )
2. (-100%) Did you give credit where credit is due?
a. Your submission is found to contain code segments copied from on-line resources and you failed to give
clear and unambiguous credit to the original author(s) in your source code (-100%). You will also be
subject to the university academic misconduct procedure as stated in the class academic integrity policy.
b. Your submission is determined to be a copy of a past or present student’s submission (-100%)
c. Your submission is found to contain code segments copied from on-line resources that you did give a
clear an unambiguous credit to in your source code, but the copied code constitutes too significant a
percentage of your submission:
o < 25% copied code No deduction
o 25% to 50% copied code (-50%)
o > 50% copied code (-100%)
What to submit
Run the following command to create the submission archive:
$ npm run zip
** UPLOAD CSE183.Assignment7.Submission.zip TO THE CANVAS ASSIGNMENT AND SUBMIT **