Programming Worksheet

REST• REST is an acronym for Representational State Transfer
• REST is often referred to as a RESTful API (application programming interface)
• RESTful API is a method of communication using HTTP
• A REST application typically resides on a server and responds to requests
• The RESTful API represents the required syntax, or interface, for interaction to use the REST services
• HTTP is the primary protocol used in web-based communication
• Web-based communication typically refers to web browsers, but is not limited to these types of
applications
• A REST application typically resides on a server and responds to requests using HTTP
• REST services work well with cloud servers and services
2
HTTP VERBS
• Internet browsers communicate using the HTTP protocol and use HTTP verbs for server requests
• Typical web page requests use the GET verb to retrieve data (SELECT)
• Form submissions use the POST verb to add new data (INSERT)
• Other verbs exist, such as PUT (UPDATE) and DELETE
• Together this actions use the acronym CRUD
HTTP
CRUD
Controller Actions
Behavior
POST
Create
create
Create new object and return ID
GET
Read
show
Return object with given ID
PUT
Update
update
Update object with given ID
DELETE
Delete
destroy
Delete object with given ID
3
CIT 281
• We will be using NodeJS (Node) to develop database REST applications
• You may use your CIT 281 Node environments, or setup a new environment, for this class
• You are always welcome to use GIT
• Although we may setup a client-side (browser) components, the focus this term is on the
server-side components
• We will focus on client-side components next term in CIT 382
4
VISUAL STUDIO CODE
• Visual Studio Code is available for Windows, Mac and Linux
• Tips for using Code
• Always create a separate folder for your Node.js apps, unless you want to create separate launch
configurations
• Name your main Node.js file app.js, or setup the launch program as part of launch configurations
• Always open the folder where your code is located using File | Open folder menu option if you plan
to debug your code
• Code has a built-in Node.js terminal window for directly running your code without debugging,
although you can use the debugger option to enable break points
5
NODE INITIALIZATION
• Node uses a file called package.json in each folder for information about the Node application
• This file is created using the init option of npm
npm init -y
• When adding modules to a Node application using npm using the install option, adding –save
updates package.json to indicate the package dependency
6
ADDING MYSQL SUPPORT
• A MySQL Node.js driver/module is available for Node.js called mysql2
• Use npm (Node Package Manager) to add the mysql2 package into your projects
• npm install –save mysql2
• In lab this week you will find instructions to help you to add the mysql support
• You can refer to the Github mysql2 website for more information
7
EXPRESS
• You may have experience using the express Node module
• You are welcome to use express with your Node.js programming
• The examples presented in class will use Express
• Additionally, you’ll notice the use of body-parser package to assist with parsing of message
bodies
8
CLIENT-SERVER
• For this class we will be focusing on the server, creating a REST service using Node
• You will see a client-side solution in lab this week, but the focus is on the REST functionality, and
how to interact with the database
• JSON will be used for REST calls and results
• A typical browser-based solution as a web application forms a client-server relationship, but
REST calls are also client-server
9
STATIC WEB PAGES
• To prevent cross-origin errors, we must run our client HTML page from the same URL as the
Node server
• If not, you’ll see a CORS (Cross-Origin Resource Error) error
• This error alerts you to attempts to pull content from a different domain
10
CREATING THE CONNECTION
• Once you’ve added mysql support, you need to include the module, and create a connection in
your code
• The following code prepares a connection object, but does not actually connect to the database
let mysql = require(‘mysql2’);
let connection = mysql.createConnection({
host : ‘localhost’,
port: ‘port #’,
user: ‘me’,
password: ‘secret’,
database: ‘my_db’
});
11
KEEPING SETTINGS PRIVATE
• You can create a separate JavaScript file to hold information you do not
want to make public, such as your database password
exports.dbHost = “localhost”;
exports.dbPort = “3306”;
exports.dbUser = “username”;
exports.dbPassword = “password”;
exports.dbDatabase = “dbName”;
• Include this information in your program using a require statement
require(‘./dbInfo.js’)
12
CONNECTION REVISITED
• With the database settings in a separate file such as dbInfo.js, you can use require to include
that information
let mysql = require(‘mysql2’);
let dbInfo = require(‘./dbInfo.js’);
let connection = mysql.createConnection({
host : dbInfo.dbHost,
port: dbInfo.dbPort,
user: dbInfo.dbUser,
password: dbInfo.dbPassword,
database: dbInfo.dbDatabase
});
13
CONNECTION REVISITED
• With the database settings in a separate file such as dbInfo.js, you can use require to include
that information
let mysql = require(‘mysql2’);
let dbInfo = require(‘./dbInfo.js’);
let connection = mysql.createConnection(dbInfo);
14
REQUIRE
• When adding in the require statement for the private data JavaScript file dbInfo.js, note that we
had to use “./” to reference the current folder
• Unlike the mysql2 module that was added in as a Node.js module, dbInfo.js is simply being
included as a source JavaScript file
15
CREATING/ENDING THE CONNECTION
// … Include connection information and object …
connection.connect(function(err) {
if (err) {
console.log(err);
} else {
console.log(‘Connected to database, closing connection in 5 seconds…’);
setTimeout(function() {
connection.end();
console.log(“Database connection closed”)
}, 5000);
}
});
16
CALLBACK FUNCTIONS
• When calling the connect method of a connection object, note that we used an anonymous
function as the parameter
• Anonymous functions are used as callbacks
• You should have seen callback functions when creating event handlers in JavaScript
• The callback function is non-blocking, and is called asynchronously when the calling method is
ready for the callback to be called
17
CALLBACK FUNCTIONS
• You can also use the ES6 fat arrow syntax for callbacks
connection.connect((err) => {
if (err) {
console.log(err);
} else {
console.log(‘Connected to database, closing connection in 5 seconds…’);
setTimeout( => {
connection.end();
console.log(“Database connection closed”)
}, 5000);
}
});
18
EXECUTING QUERIES
• Once you have a valid connection, you may execute queries
connection.query(‘SELECT * FROM LG_PRODUCT’, function (err, rows) {
if (err) {
// handle error perhaps using throw err
} else {
console.log(‘Data received:\n’);
console.log(rows);
}
}
19
FILTERING
• You can use values in your SQL WHERE clause, such as
var sql = “SELECT * FROM LG_PRODUCT WHERE PROD_BASE = ‘Water’”;
• You must be careful inserting user input values directly into your SQL
var baseType = ;
var sql = “SELECT * FROM LG_PRODUCT WHERE PROD_BASE = ‘”
+ baseType + “’”;
• Users could attempt a SQL injection attack
20
PARAMETERIZED QUERIES
• In the previous example we saw how we might apply a filter, but if any user-entered data might
be used as part of a query, you are strongly recommended to use parameterized queries
• A parameterized query places a ? within a SQL statement, and an array is used as a parameter
to indicate the values
• Note: You can also use an object-based parameterized solution using an object where the
property names represent the database column, and the value represents the value of the
query
21
PARAMETERIZED QUERY
• Let’s look at that query again as a parameterized query using a SQL filter
connection.query(‘SELECT * FROM LG_PRODUCT WHERE PROD_BASE = ?’,
[‘Water’],
function (err, rows) {
if (err) {
// handle error perhaps using throw err
} else {
console.log(‘Data received:\n’);
console.log(rows);
}
}
22
PARAMETERIZED QUERY
• The parameterized query works by passing an array of values
• Each value in the array corresponds to a ? In the query, and the ? is replaced by the
corresponding value
• This technique allows the query software to appropriately wrap the array values to prevent SQL
injection
• This technique also works well to remove the need to worry about embedded quotations or
surrounding string data with quotations
23
PARAMETERIZED OBJECT QUERIES
• Rather than an array of values, you can also use an object methodology for parameterized queries
• Rather than an array of values, you would pass an object where each property represents a column
name and the corresponding property value represents the value for the query
connection.query(‘SELECT * FROM LG_PRODUCT WHERE ?’,
{PROD_BASE: ‘Water’},
function (err, rows) {
if (err) {
// handle error perhaps using throw err
} else {
console.log(‘Data received:\n’);
console.log(rows);
}
});
24
OTHER ACTIONS
• You can perform all the typical SQL actions using Node (and corresponding CRUD name)
• Add/insert new data (Create)
• View data (Read)
• Update data (Update
• Delete data (Delete)
• Query results are typically returned as JSON data
25
URL
• As already mentioned, we will be using Express to assist with handling REST connections using
URL routes
• Below is a description of the typical components that make up a URL
https://doepud.co.uk/blog/anatomy-of-a-url
26
ROUTES
• The routes component of a URL consist of everything after the domain, which would include the
pathway and any parameters
• URL parameters are indicated by the ? character (question mark) and are typically query
parameter name and value pairs (e.g. docid=-7246…)
• Many parameters may be specified and are separated by the & character (ampersand)
• URLs may not include certain characters that would conflict with the URL interpretation
• The URLs are URL encoding, also known as percent encoding
• Example: W3School’s HTML URL Encoding
• URLs also need to be decoded
• Most HTML parsing libraries handle the encoding and decoding
27
EXPRESS
• As mentioned, we will be using Express to simply the managing of URL routes
• To use express, we need only create an instance of Express
• We will use this express instance to simplify route and HTTP verb handling
• The req parameter is the request object and contains data sent to the server
• The req object contains a params object that contains URL parameters
• The req object contains a body object that contains values send via POST and PUT
• The res parameter is the response object and is used to respond to the request
• We will be typically respond with JSON results
let app = express();
….
app.get(‘/alien’, function (req, res) {
…
res.JSON(….)
} );
28
JSON
• JSON is a format widely used to transfer information between client and server computers
• Remember the JSON.parse() and JSON.stringify() methods
• When using express, you simply have to use the .json() method to send data, and all data is
automatically converted to the correct JSON format
• Remember that JSON, or JavaScript Object Notation, makes sending information in arrays and
objects easier, but you will need to work at parsing the JSON data
• You may opt to find an application or website that helps with parsing JSON data
29
BODY-PARSER PACKAGE
• We will be using the body-parser Node package to assist with handling routes and
decoding/encoding HTTP requests
• The body-parser package is known as middleware and works with Express to make the process
of dealing with HTTP interaction easier
• You are welcome to explore the body-parser capabilities, but we will use typical settings
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
30
HANDLING PAGE NOT FOUND
• A basic technique for handling routes is to list out all of the routes you wish to support
• To handle a user attempting to access a route that is not available, you will use the http status
code 404
• Wikipedia includes a list of HTTP Status Codes
• Your last route must be the unhandled route support
app.get(‘*’, function(req, res) {
res.status(404).send(‘Sorry that page does not exist’);
});
31
MAPPED AND UNMAPPED ROUTES
• A mapped route correctly displays, and an unmapped route is handled correctly
32
AJAX AND JQUERY
• In lab you will be working with a COLOR REST Node server
• We have also supplied a web page that uses AJAX and jQuery to demonstrate working with a
REST service
• What is Ajax?
• Ajax is the ability to make asynchronous page requests using JavaScript without reloading the web
page
• jQuery simplifies using Ajax
33
CLIENT-SIDE WEB PAGE
• We will need to do a few setup tasks before we can use Ajax and jQuery
• Setup a public folder on Cloud9
• Create an express static route for static web pages
• Add jQuery support
• Remote: Reference the latest jQuery code at http://code.jquery.com/jquery-latest.min.js
• Download the latest code and reference directly from your local computer
• Upload your files to your Cloud9 public folder
34
SQL VIEW
• The output of a relational operation such as a SELECT statement is another relation, or table
• We can save SQL statements using Workbench, but SQL provides a more permanent technique
called a relational, or SQL, view
• A view is a virtual table based on a SELECT query
• Views may contain columns, computed columns, aliases, and aggregate functions from one or
more tables
• Tables involved in a view are referred to as the view base tables
• Views are created using CREATE VIEW
2
VIEW EXAMPLE
• Consider a view for the singer-catalog-song tables
• Consider SQL to return the results of these three tables to list works by artist (your data may
differ)
3
VIEW EXAMPLE
• Below is the SQL from the previous query
4
VIEW EXAMPLE
• Here is the code to create a view for the
previous query, along with a drop
statement to ensure we can easily recreate the view
• Use the view just like a table using the
SELECT statement, or any other SQL
expression (e.g. joins, sorting,
aggregation)
5
WORKBENCH VIEWS
• Views are listed in MySQL Workbench Schemas Explorer window under the Views section (may
require refresh after creating the view)
6
VIEW HIGHLIGHTS
• A view can be used anywhere a table name is expected in a SQL statement, which means you
can limit the columns displayed from the view itself, as well as perform any other SQL
manipulation possible with a table
• Views are dynamically updated each time the view is invoked, so any changes to a view’s base
tables are always represented in the view results
• Views provide a vehicle to add security to information by allowing limiting access to users to
specific views with data subsets
• Views are frequently used as a basis for reports
7
VIEW HIGHLIGHTS
• Views can be used to update base table data, although certain requirements must be met
• Views can be made read-only to prevent changing base table data
• Normal tables should not include computed columns, but views are a great solution to create
and maintain permanent computed output
8
BASE TABLE STRUCTURE CHANGES
• Views are dependent on the schema and relationships of its underlying base tables
• Changes to base tables require revisiting views to ensure the view still functions as expected
9
PERFORMANCE
• MySQL views are updated when invoked, so views in general are slower than a direct query
• Some database systems support “materialized” view tables, maintaining a view like a table, but
MySQL does not currently support this feature
• If a view references other views, then the performance suffers even more
10
EVALUATING PERFORMANCE
• MySQL includes the ability to “explain” how a query will execute using the EXPLAIN verb
• To use EXPLAIN, prefix any SQL statement with EXPLAIN and MySQL will return a series of rows
explaining the steps necessary to execute the query
• EXPLAIN results are also know as execution plans
• Workbench includes a visual version of explain accessible from the toolbar
11
EVALUATION COMPARISON
• Below are the results of using Workbench to visually “explain” our view, and the original SQL
statement used to create our view
View
Execution
Plan
SQL
Execution
Plan
The view included an additional step to create the “materialized”
virtual table before executing the SQL
12
VIEWS WITH EXTERNAL TABLES
• We can create a view that references tables from another database
13
STORED PROCEDURES
• Relational databases support the ability to create your own database API (application
programming interface)
• Stored procedures
• Stored SQL with optional parameters (input) and results (output)
• Invoked with call statement (stand-alone)
• Functions
• Stored SQL with optional parameters (input) but required results (output)
• Invoked by calling as part of a SQL expression (inline)
• Major Benefits
• Reusable API
• Avoid duplicating code
• Encapsulated functionality isolates changes due to schema modifications
• Easier to tune performance of queries
14
STORED PROCEDURE EXAMPLE
• Let’s create a stored procedure using the
SQL we earlier used for a SQL view
• Here is the code to create a stored
procedure, with no input parameters, for
the previous query, along with a drop
statement to ensure we can easily recreate the stored procedure
• The standard end of SQL statement
delimiter is changed to // to enable the
entire definition to be executed as a single
statement, then restored
• The procedure is called using the call
statement
15
PARAMETERS
• Both functions and stored procedures
may include parameters
• Let’s add a new stored procedure to
accept the ID of the singer as input,
and return the results
16
FUNCTIONS
• Functions are created using the CREATE FUNCTION statement
17
RELATIONSHIPS
• We’ve discussed three basic relationships
• 1:1, one to one
• 1:M, one to many
• M:N, many to many
• We have two types of relationships
• Identifying
• Non-identifying
• Primary keys may be composite or single
• Indexes may be composite or single, and may enforce uniqueness
• Foreign keys may not be mandatory
2
REFERENTIAL INTEGRITY
• Referential integrity enforces our relationships and maintains valid foreign key to primary key
entries
• Tables with foreign keys also contain the constraints to maintain referential integrity (see the
table DDL)
• We can easily delete rows with foreign keys, but how do we remove a row referenced as a
foreign key, and what behavior should we expect?
• The relationship “rules” indicate expected behavior, but we need to manually update or
relationship constraints to automate the behavior
3
EXAMPLE: SONG CATALOG
Consider the song catalog we’ve seen before:
We can easily delete any entry in catalog, but we cannot
delete a singer or a song if either are referenced in the
catalog (referential integrity)
4
REFERENTIAL INTEGRITY ENFORCEMENT
• The enforcement of referential integrity is independent of relationship type (identifying or nonidentifying)
• Enforcement is also independent of cardinality, and mandatory versus optional
• Enforcement behavior may be modified, depending on the type of change
• Update: A row is updated
• Delete: A row is removed
5
REFERENTIAL ACTIONS
• Per the MySQL Manual MySQL supports four referential actions for InnoDB tables for both
updates and deletes
• CASCADE
• Delete or update the row from the parent table, and automatically delete or update the matching rows in
the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do
not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the
child table.
• SET NULL
• Delete or update the row from the parent table, and set the foreign key column or columns in the child table
to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.
• If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT
NULL.
6
REFERENTIAL ACTIONS
• RESTRICT
• Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same
as omitting the ON DELETE or ON UPDATE clause.
• NO ACTION
• A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or
update operation for the parent table if there is a related foreign key value in the referenced table. Some
database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key
constraints are checked immediately, so NO ACTION is the same as RESTRICT.
• By default, Workbench selects Restrict
7
CHANGING REFERENTIAL ACTIONS
• In our song catalog model, what if we do want to delete a singer (or a song)?
• Based on the mandatory identifying relationship, if we opted to delete either a singer or a song,
we should delete the entire row in catalog
• We can use Workbench Model or Workbench Alter Table to modify foreign key referential
actions
8
FOREIGN KEY OPTIONS
• The image below shows the Foreign Key Options available under the Foreign Keys tab in Workbench
Model
9
REFERENTIAL ACTION: CASCADE
• With identifying, mandatory foreign key relationships like between catalog and singer or song,
we would use the CASCADE option
• If we delete an entry in either singer or song, any rows in catalog using the key would also be
deleted
10
REFERENTIAL ACTION: SET NULL
• If a foreign key relationship is non-identifying, and optional, then the best choice for referential
action is SET NULL
11
EXAMPLE: ADD BAND
• Consider adding a band entity
▪ The relationship between band and singer is nonidentifying, and the foreign key is optional, so SET NULL
would set the BAND_BAND_ID foreign key to NULL if a
corresponding band was deleted from the band table
12
REFERENTIAL ACTION
CHOICE SUMMARY
• If a relationship is identifying, or non-identifying and mandatory, CASCADE is almost always the
correct referential action unless you want to force removing the referenced foreign key first
• If a relationship is non-identifying, and optional, the best choice is for referential action is SET
NULL
13