Why Database? (1)

So far, we have been augmenting the stateless HTTP with either HTML5 localStorage and/or PHP superglobals variable $_SESSION to make a dynamic web application that remembers user's interactions with the application

But how to permanently store the data that we have due to interaction with the users across sessions in our server?

Why Database? (2)

One clear solution: Database

We do not use simple file as our web application will likely be accessed by many users, possibly at similar time, with each of them either Creating a new data or Reading/Updating/Deleting an existing data (CRUD)...

We need the database system's Atomicity, Consistency, Isolation, Durability (ACID)* properties to handle concurrent access and yet still maintain data integrity

Note: Part of this lecture is a CS2102 quick revision, although we will use Laravel framework this time

Relational Database

• First published by Edgar Frank Codd in 1970
• A relational database consists of a collection of tables
• A table consists of rows and columns
• Each row represents a record
• Each column represents an attribute of the records contained in the table

Example: Simple Scoreboard

Imagine we have a simple database with three tables:
USERS, ACCESS, and SCORES

1. USERS has attributes: USERID, EMAIL, PASSWORD
   (FYI: Laravel has this table by default)
2. ACCESS has attributes: USERID and ROLE
3. SCORES has attributes: USERID and SCORE

Some content

Table USERS
USERID   | EMAIL                    | PASSWORD
---------+--------------------------+-------------
steven   | stevenha@comp.nus.edu.sg | hashed1
student1 | axxxxxx@comp.nus.edu.sg  | hashed2
student2 | bxxxxxx@comp.nus.edu.sg  | hashed3
student3 | cxxxxxx@comp.nus.edu.sg  | hashed4
...
Table ACCESS                  Table SCORES
USERID   | ROLE               USERID   | SCORE
---------+--------            ---------+--------
steven   | 1                  student2 | 90
student1 | 2                  student3 | 70
student2 | 3                  ...
student3 | 3
...

Unstructured Data

Compare the previous tables with this unstructured one:

Steven has one student as his TA (student1) and a few other students. He has listed the score of his students in a table and he wants to let his TA to update the scores weekly. His students can only see the scores without ability to do update....

Same information, but it is very hard to perform meaningful queries on this data

Entities and Relations

• An entity represents something real
• A relation represents a connection between entities
• The tables USERS and SCORES may be regarded as entities
• Table ACCESS may be regarded as a relationship between a particular user in table USERS and what he/she can do with the content of table SCORES

SQL (Brief Overview)

Structured Query Language (SQL)

Pronounciation: Either S Q L (one character at a time) or 'sequel', your choice

SQL is used on a Relational Database Management System (RDBMS) to create, search, and modify tables

MySQL

In CS3226, we use MySQL as the default RDBMS
(but wrapped in Laravel Eloquent ORM... discussed soon)

We will concentrate on important MySQL Create Read Update Delete (CRUD) operations

Accessing MySQL at (DO) Server

1. Login to your own (DO) server
2. At command prompt (terminal), type in
   'mysql -u your_username -h localhost -p'
3. Type in [THE_PASSWORD]
   This is given to you when you spin a new DO droplet
4. At MySQL prompt, type in 'CREATE DATABASE testDB;'
   for first usage, or type in 'USE testDB; otherwise
   PS: testDB is our database name for this lecture
5. Then you can perform queries (CS2102++ stuffs)

But we will not frequently do this directly as we will use Laravel built-in database features

Database Migration

In Laravel, we create new Tables by using Database Migration, i.e. we write code to create the required table, specify columns and their attributes, and write code to (possibly) reverse this creation action

First, we use Laravel Artisan to create a starting point:
For example: php artisan make:migration Create_Score_Table --create=scores

Write Code to Create Table

Using the same example (table scores),
now open file database/migrations/
[TIMESTAMP]_Create_Score_Table.php

Then, we update the up() method (executed when we run the migration --- to create the table, its columns, and attributes) and the down() method (executed when we run rollback function --- usually to drop the created table)

public function up() {
  Schema::create('scores', function(Blueprint $table) {
    $table->increments('id'); // Laravel default, auto increment
    $table->string('userid', 10)->unique(); // custom column
    $table->integer('score'); // another custom column
    $table->timestamps(); // another default: created_at, updated_at
  });
}

public function down() {
  Schema::dropIfExists('scores');
}

Details of Laravel Schema builder can be found here, compare it with pure (My)SQL command:

CREATE TABLE SCORES (USERID VARCHAR(20) PRIMARY KEY, SCORE INTEGER);

PS: users table is created by default in a Laravel project,
and we don't show the role table in this example
(it will be repeated in Security Lecture)

Technicalities

Assuming a database named 'testDB' has been created in our (DO) server, we need to update a hidden one-stop configuration file: '.env' with our database credentials so that our migration can be performed

DB_HOST=[IP_ADDRESS_OF_YOUR_DO_DROPLET]
DB_DATABASE=testdb
DB_USERNAME=root
DB_PASSWORD=[THEPASSWORD]

A security minded person will do extra steps to secure these settings more, i.e. by setting up config/database.php instead, etc (verbal explanation); we can then run:

php artisan migrate

Database Seeding

Sometime we need our table(s) to be pre-populated with some dummy (Faked) content for preliminary testing

php artisan make:seeder ScoreSeeder

<?php
use Illuminate\Database\Seeder;
class ScoreSeeder extends Seeder {
  public function run() {
    // you can create a few manual entry
    // or, if needed, use $faker = Faker\Factory::create();
    // here, we show random entries
    for ($i = 0; $i < 7; $i++) { // create 7 dummy
      DB::table('scores')->insert([
        'userid' => str_random(10),
        'score' => rand(70, 100),
      ]);
} } }

php artisan db:seed --class=ScoreSeeder

Laravel Eloquent ORM

ORM stands for Object Relational Mapper

Basically, each database table in our Laravel web application has a corresponding "Model" (the M in the MVC) which is used to interact with that database table,
without directly writing (My)SQL* queries

php artisan make:model Score

<?php // the file app/Score.php contains:
namespace App;
use Illuminate\Database\Eloquent\Model;
class Score extends Model {
  // not needed as the table name is the plural form of this model name
  // protected $table = 'scores'; // set it if the table name is different
  protected $fillable = ['score']; // only column 'score' is update-able
  // PS: created_at and updated_at are auto-managed by Laravel Eloquent
}

Read Example

Assuming that we have seeded our database table 'scores', we can update the routes/web.php with this direct Controller+View code (bad practice, only for illustration)
PS: I am trying to use REST architectural style

use App\Score; // we include the Model
Route::get('score', function() { // trying to follow REST convention
  return Score::all(); // just display all table content as text
});

Create Example

We can add a new row into our database table 'scores'

Route::get('score/create', function() { // just for illustration
  // usually this route should just open a blank form for creating
  // a new (row) entry in a REST architecture (verbal explanation)
  // here we use it to illustrate row addition in Laravel Eloquent
  $s = new Score;
  $s->userid = 'steven'; // second call will be a duplicate/error as we
  $s->score = 77; // are entering the same userid (that has to be unique)
  $s->save(); // just like that
  return Score::all(); // show the updated data immediately
});

Update Example

We can update the content of existing row of our database table 'scores'

Route::get('score/steven/edit', function() { // just for illustration
  // usually this route should just open a blank form for editing
  // an existing (row) entry in a REST architecture (verbal explanation)
  // here we use it to illustrate an update operation in Laravel Eloquent

  // first, we need to find the correct row; orFail is important
  $s = Score::whereRaw('userid = "steven"')->firstOrFail();
  $s->score = 99; // update the update-able field (we can't update userid)
  $s->save(); // that's all
  return Score::all(); // show the updated data immediately

  // PS: we can also do mass update, taking all form input
  // something like $s->update($request->all()); (verbal explanation)
});

Delete Example

We can delete an existng row of our database table 'scores'

Route::get('score/steven/delete', function() { // just for illustration
  // usually this route should use delete http request instead of get/post
  // here we use it to illustrate deletion in Laravel Eloquent

  // as usual, we need to find the correct row first
  $s = Score::whereRaw('userid = "steven"')->firstOrFail();
  $s->delete(); // that's all, PS: second call will be an error
  // as we hardcode the userid instead of parameterizing it

  return Score::all();
});

Collections

If a Query results in multiple rows of data, that query result is automatically converted into an instance of Laravel Collection class

Then you can perform various methods on Collection, e.g. all, sortBy, sortByDesc, first, last, pluck, etc...

Some of these Collection methods are much more easier to use than using the direct (My)SQL queries...

Form Model Binding

When we want to create a new row and/or update an existing row, we will likely use an HTML form

Especially for the edit/update action, it will be nice if we use model binding so that our edit/update form are pre-populated with existing values of the corresponding Model (database table) and our changes afterwards are automatically updated in the Model (database table)

We can do that easily using LaravelCollective; Instead of using Form::open, we use Form::model($model_variable, ['action' => 'SomeController@update']) as long as the HTML form field names match with the ones in Model

Eloquent Relationships

Not discussed in details but we can do: One to One,
One to Many, or
Many to One relationships

As usual, for more details, read Laravel Eloquent ORM documentation

PHP + MySQL

All slides in this section are OUTDATED (replaced with Laravel ORM) but left behind as an illustration of the complexity of doing database access using vanilla PHP

Create Table

The basic syntax is 'CREATE TABLE TABLE_NAME (list of columns and their definitions);' (details)

Usually we use UPPERCASE for most SQL commands and table/attribute names

Example: Executing the following SQL commands causes the creation of the USER, ROLE, and SCOREBOARD database tables shown earlier (all initially empty)

CREATE TABLE USER (USERID VARCHAR(20) PRIMARY KEY, EMAIL VARCHAR(30), PASSWORD VARCHAR(20));
CREATE TABLE ACCESS (USERID VARCHAR(20) PRIMARY KEY, ROLE INTEGER);
CREATE TABLE SCOREBOARD (USERID VARCHAR(20) PRIMARY KEY, SCORE INTEGER);

SQL Data Types

Some basic SQL data types that you may need in your database tables (details):

• INTEGER: signed fullword binary integer
• FLOAT: signed doubleword floating point number
• VARCHAR(n): varying length character string of maximum length n
• DATE: A date attribute in a DBMS-specific format

For basic web applications, these basic data types are usually enough

A note about NULL

SQL allows the NULL value to appear in tuples (table rows) and it indicates a non-initialized attribute in a row

This can be disallowed by adding a NOT NULL constraint during table creation

CREATE TABLE USER (
  USERID VARCHAR(20) PRIMARY KEY,
  EMAIL VARCHAR(30) NOT NULL,
  PASSWORD VARCHAR(20) NOT NULL
);

If the USER table is declared this way, then when we add a row to the USER table, then EMAIL and PASSWORD must be specified (on top of USERID)

Insert into Table

To do some work with our tables, we need to first populate it (using the INSERT command)

The syntax is 'INSERT INTO TABLE_NAME VALUES(list of values)' (details)

Example:

INSERT INTO USER VALUES('steven', 'stevenha@comp.nus.edu.sg', 'hashed1');
INSERT INTO USER VALUES('student1', 'axxxxxx@comp.nus.edu.sg', 'hashed2');
INSERT INTO USER VALUES('student2', 'bxxxxxx@comp.nus.edu.sg', 'hashed3');
INSERT INTO USER VALUES('student3', 'cxxxxxx@comp.nus.edu.sg', 'hashed4');
INSERT INTO USER VALUES('student4', null, 'hashed5'); -- will not work

Executing an SQL Source Script

The SQL commands used so far have been recorded as populate.sql script

To ensure that we have the same database table contents as with the one shown in this slide, type in 'source populate.sql;' at MySQL prompt

The SQL commands that will be used in the next few slides until before the PHP+MySQL part are recorded as examples.sql script

SELECT - Basic

The result of a SQL search is a table (soon, we will learn how to format the result as an HTML table)

The most basic syntax is 'SELECT EXPRESSION FROM TABLE_NAME OPTIONAL_COMMANDS;' (details)

SELECT * FROM ACCESS;

The result is the whole ACCESS table

+----------+------+
| USERID   | ROLE |
+----------+------+
| steven   |    1 |
| student1 |    2 |
| student2 |    3 |
| student3 |    3 |
+----------+------+

SELECT - WHERE (1)

Example on using the "WHERE" clause

SELECT * FROM ACCESS WHERE ROLE < 3;

The result is part of the ACCESS table

+----------+------+
| USERID   | ROLE |
+----------+------+
| steven   |    1 |
| student1 |    2 |
+----------+------+

SELECT - WHERE (2)

Example on using the more complex "WHERE" clause

SELECT * FROM ACCESS WHERE ROLE = 1 OR ROLE >= 3;

The result is part of the ACCESS table

+----------+------+
| USERID   | ROLE |
+----------+------+
| steven   |    1 |
| student2 |    3 |
| student3 |    3 |
+----------+------+

SELECT - Get Column(s)

Selecting particular columns

SELECT USERID FROM ACCESS WHERE ROLE < 3;

The result is part of the ACCESS table, column USERID

+----------+
| USERID   |
+----------+
| steven   |
| student1 |
+----------+

SELECT - Order By

We can sort the output

SELECT * FROM ACCESS ORDER BY ROLE DESC;

The result is the ACCESS table, but in descending order of ROLE values

+----------+------+
| USERID   | ROLE |
+----------+------+
| student2 |    3 |
| student3 |    3 |
| student1 |    2 |
| steven   |    1 |
+----------+------+

SELECT - Cartesian Product

We can combine information from two tables

SELECT * FROM USER, ACCESS;

The result is the table USER x ACCESS with rows of the form (u, a) where u is a row in USER and a is a row in ACCESS; In total, we have 4*4 = 16 rows

+----------+--------------------------+----------+----------+------+
| USERID   | EMAIL                    | PASSWORD | USERID   | ROLE |
+----------+--------------------------+----------+----------+------+
| steven   | stevenha@comp.nus.edu.sg | hashed1  | steven   |    1 |
| student1 | axxxxxx@comp.nus.edu.sg  | hashed2  | steven   |    1 |
| student2 | bxxxxxx@comp.nus.edu.sg  | hashed3  | steven   |    1 |
| student3 | cxxxxxx@comp.nus.edu.sg  | hashed4  | steven   |    1 |
| steven   | stevenha@comp.nus.edu.sg | hashed1  | student1 |    2 |
... 10 other rows ...
| student3 | cxxxxxx@comp.nus.edu.sg  | hashed4  | student3 |    3 |
+----------+--------------------------+----------+----------+------+

SELECT - Joins

We can match up the rows in multiple tables based on the same (primary key) value for columns, e.g. compare the previous cartesian product of USER x ACCESS with this one

SELECT U.EMAIL, A.ROLE FROM USER U, ACCESS A WHERE U.USERID=A.USERID;

Note: The tables have been aliased in the above query: USER as U, ACCESS as A with this result:

+--------------------------+------+
| EMAIL                    | ROLE |
+--------------------------+------+
| stevenha@comp.nus.edu.sg |    1 |
| axxxxxx@comp.nus.edu.sg  |    2 |
| bxxxxxx@comp.nus.edu.sg  |    3 |
| cxxxxxx@comp.nus.edu.sg  |    3 |
+--------------------------+------+

Other Common SQL Queries

Update row(s) in a table USER

UPDATE USER SET email='stevenhalim@gmail.com' WHERE USERID='steven';
SELECT * FROM USER; -- see that steven's email has been updated

Delete row(s) from a table SUPPLIER

DELETE FROM ACCESS WHERE USERID = 'student3';
SELECT * FROM ACCESS; -- see that 'student3' is no longer listed

Drop the entire table ACCESS

DROP TABLE ACCESS;
SELECT * FROM ACCESS; -- error, that table no longer exist

Notes

A relational database is a powerful tool and we have just scratched the surface as we do not cover:

• Transaction processing
• Concurrent access
• Aggregate queries
• Stored procedures (PL/SQL, embedded Java)
• Integrity constraints
• Design
• Indexes
• Fault tolerence
• Online backups
• Database distribution, etc...

Take CS2102 (if you have not done so) to learn more about these...

Linking the two (1)

In the first half of this lecture, we digressed to database (CS2102) and used direct mysql command line interface

Now, we will write PHP scripts to access our MySQL database so that we can Create/Read/Update/Delete our database from our web application

Linking the two (2)

Basically, the PHP commands that you will need to know to access MySQL database are:

$db = new mysqli($db_host, $db_uid, $db_pwd, $db_name); // open connection
$res = $db->query($query); // THAT SQL query that we covered so far
$sanitizedinput = $db->escape_string($inputfromuser); // increase security
$db->close(); // close connection

More details are given in the next few slides

Making Database Connection (1)

Create a PHP file: config.php and save it outside the public_html folder, e.g. ../public_html

<?php // config.php basically contains these 4 constants
define("db_host", "localhost"); // a constant doesn't need a $
define("db_uid", "your_cs3226_username"); // change this to yours
define("db_pwd", "your_cs3226_password"); // change this to yours
define("db_name", "your_cs3226_username"); // default for this class
?>

Making Database Connection (2)

To connect with our database, we use connect.php:

<?php // connect.php basically contains these commands
require_once '../config.php'; // your PHP script(s) can access this file
$db = new mysqli(db_host, db_uid, db_pwd, db_name); // it is built-in
if ($db->connect_errno) // are we connected properly?
  exit("Failed to connect to MySQL, exiting this script");
?>

Making SQL Queries

After we are connected, we can perform MySQL queries from our web application :), e.g. displaying the table in our database as HTML table, try db.php

<?php require_once 'connect.php';
function DisplayTableInHTML($table_name) {  
  global $db; // refer to the global variable 'db'
  $query = "SELECT * FROM " . $table_name;
  $res = $db->query($query); // yes, just like this
  if (!$res) exit("There is a MySQL error, exiting this script");
  echo "<p>Table " . $table_name . "<br></p>"; // dynamic HTML table
  echo "<table border=\"1px\" style=\"border-collapse: collapse\">";
  while ($r = mysqli_fetch_row($res)) { // important command
    echo "<tr><td>" . $r[0] . "</td>"; // echo first column
    for ($i = 1; $i < count($r); $i++) echo "<td>" . $r[$i] . "</td>";
    echo "</tr>";
  }
  echo "</table>";
}
DisplayTableInHTML("USER"); $db->close(); ?>

Updating Our Database

Obviously, we can update our database using our PHP script

However, to prevent security issues like SQL injection, I will limit what you can do to changing email of table USER only

<?php require_once 'connect.php';
$uid = $_REQUEST['userid']; // get userid (dangerous)
$eml = $_REQUEST['email']; // get the new email (dangerous)
echo "userid = " . $uid . "
\n";
$q = "UPDATE USER SET EMAIL='" . $eml . "' WHERE USERID='" . $uid . "'";
echo "SQL query = " . $q . "
\n";
$res = $db->query($query); // you can form SQL query from URL parameters
if (!$res) exit("MySQL reports " . $db->error);  
else echo $db->affected_rows . " rows are updated, db.php.
";
?>

Always Sanitize the Input! (1)

We can still inject something update_db.php?userid=steven&email=a@b.com', PASSWORD='somethingelse
Check db.php again

+----------+--------------------------------+-----------------+
| USERID   | EMAIL                          | PASSWORD        |
+----------+--------------------------------+-----------------+
| steven   | a@b.com                        | somethingelse   |
| student1 | axxxxxx@comp.nus.edu.sg        | hashed2         |
| student2 | bxxxxxx@comp.nus.edu.sg        | hashed3         |
| student3 | cxxxxxx@comp.nus.edu.sg        | hashed4         |
+----------+--------------------------------+-----------------+

Always Sanitize the Input! (2)

But if we sanitize the inputs like this:

<?php require_once 'connect.php';
$uid = $db->escape_string($_REQUEST['userid']); // better
$eml = $db->escape_string($_REQUEST['email']); // better
// same as before
?>

Now you should not be able to do that SQL injection anymore (details in security lecture)

Notes (1)

This entire outdated section is just one simple working example on how to use database (in this case, MySQL) and manipulate it via web-based interface (in this case, via PHP script called from a web browser)

The process of updating/showing/modifying database in a real web application is much more sophisticated and fancier than this example

Notes (2)

There are many more SQL commands (take CS2102/other database module in SoC if you have not done so or self-study) and PHP commands involving MySQL (self-study)

But let's learn Laravel ORM way instead of using vanilla PHP to access MySQL database...
So let's get our hands dirty with Lab5