Php WebLibraryDB

The WebLibraryDB Application

Download the source archive WebLibraryDB.zip. This project can be configured to work with either MySQL or SQLite. Install it as a NetBeans Php Application with Existing Sources or Php Application from Remote Server.

Change MySQL Database?

By default the database used is the one for the Java LibraryDB and LibraryFX applications, namely, the test database with guest user and empty password. Fortunately, the tables here are the same:
user
book
borrow
The difference is that the user table is expanded with 2 more fields. However these new fields would simply be ignored by the Java Library applications.

Nevertheless, you might want to consider using a new database for this application, call it:
library
You can reuse the same guest user, but you'll have to create it explicitly. Here is what you would do:
mysql -u root -p
Password: (whatever it is, probably empty)
mysql> create database library;
mysql> create user guest@localhost;      (may already exist)
mysql> grant all on library.* to guest@localhost;
mysql> quit
Then verify successful entry:
mysql -u guest library
Finally you have to edit the following file:

include/db.php
...
class DBProps {
  const which = "mysql"; // "mysql" or "sqlite"
 
  public static function getProps() {
    switch (self::which) {
 
      case "mysql":
        return [
            'url' => "mysql:host=127.0.0.1;dbname=test",
            //'url' => "mysql:host=localhost;dbname=test",
            'username' => 'guest',
            'password' => '',
        ];
    ...
Change the dbname setting to the new database:

include/db.php
...
        return [
            'url' => "mysql:host=127.0.0.1;dbname=library",
            'username' => 'guest',
            'password' => '',
        ];
...

Php Database APIs and ORMs

Supposing that you want to build a web/database application using Php and MySQL. One of the most basic decisions is what API (Application Programming Interface) to use within Php to access the database. Php provides several MySQL-based APIs, for example:
  1. MySQL API (deprecated), the first API written. Operations are function calls with the mysql_ prefix.
  2. MySQLi (MySQL improved) API. It can operate either as function calls with the mysqli_ prefix, or by an object-oriented style.
  3. PDO (Php Data Objects) API. This is a database-independent and can be used to access multiple database systems such as MySQL, PostgreSQL, SQLite, etc.
The MySQLi API is often favored by textbooks on Php/MySQL. The main drawback is that it "marries" your application to MySQL which is OK unless at some point you want a divorce so that you can use another database system. You can "re-program" your application if you want to change to a different database such as SQLite, namely, there is an SQLite API, where one roughly replaces occurrences of "mysql_" by "sqlite_". Nevertheless, your application would simply be "re-married", not independent. The PDO API emerges as the best choice to make your application database-independent.

ORMs

One of the disadvantages in all the above APIs is the reliance on SQL operations to achieve the desired effects. When using SQL directly, we are still working on the level of tables, records, and SQL statements, whereas it is usually better programming practice to instead think of records as objects, tables as classes and use dedicated methods to manipulate the database.

An ORM (Object Relational Mapping) is a higher level API, built on top of one of the above "lower level" APIs. The selling feature of an ORM is that it provides methods for treating database records as objects. Although it is impossible to eliminate all direct SQL accesses, it is possible to do so for most common database accesses. Summarizing, the advantages of ORMs:

RedBean Php ORM

RedBeanPhp is an ORM based on the PDO API. The version currently used in the applications is 4.3.4. RedBeanPhp requires using a Php version at least 5.3.4 with relevant PDO drivers installed. The home page is:
www.redbeanphp.com
Installing RedBean Php is extremely simple. The download archive, RedBeanPHP4_3_4.tar.gz, extracts as two files, one of which is the main file which is all that is necessary to include:
rb.php
RedBean Php presents its operations in groups which it calls façades. For our purposes, we rely mostly on what RedBean calls the R-façade in which database operations are static functions of the "R" class, e.g.:
R::operation
Each usage of RedBean begins with the command R::setup to establish the database connection in a manner exactly like that used in the PDO API.

Support for MySQL and SQLite

RedBean support for MySQL and SQLite is available by installing the PDO drivers. For most UNIX-based systems, the MySQL PDO API will be installed automatically when Php/MySQL support is installed. PDO SQLite support is also common in UNIX systems, but may need to be installed separately. For example, in Ubuntu Linux, you need to install the package:
php-sqlite
For the Windows Native Php installation, you will need these extensions uncommented in the Php init file:
extension=php_pdo_mysql.dll
extension=php_pdo_sqlite.dll

The Database

The MySQL tables used for this demo project are kept in the setup/tables directory:

book-mysql.sql
create table `book`(
  id int auto_increment primary key not null,
  title varchar(255) unique not null, 
  binding enum ('paper','cloth') not null,
  quantity int not null
)

user-mysql.sql
create table `user` (
  id integer auto_increment primary key not null,
  name varchar(255) unique not null,
  email varchar(255) not null,
  password char(64) not null,
  is_admin bool not null
)

borrow-mysql.sql
create table `borrow` (
  id integer auto_increment primary key not null,
  book_id integer not null,
  user_id integer not null,
  borrowed_at date,
  foreign key(book_id) references book(id),
  foreign key(user_id) references user(id),
  unique(book_id,user_id)
)
In contrast to the tables used in our Java "Library" projects (LibraryDB, LibraryFX), the user table contains the new password and is_admin fields to provide user authentication.

Something worthy of noting is how SQLite handles the boolean type. We use the table declaration:

user-sqlite.sql
create table `user` (
  id integer primary key not null,
  name text unique not null collate nocase,
  email text not null collate nocase,
  password char(64) not null,
  is_admin bool not null
)
In both MySQL and SQLite, "bool" is handled as an integer type, but SQLite does not interpret a default "false" value as 0, so it's better to have Php set it to false.

Install and Run

As indicated at the beginning of the document, download the source archive WebLibraryDB.zip and install it as a NetBeans Php Application with Existing Sources (or possibly Php Application from Remote Server). The scripts within this project are intended to be executed through a shell.

Using MySQL

As describe above, you can use the previous MySQL database used before (which we'll assume), or create a new one, if you'd like, also described above. Set the which constant in the database settings file:

include/db.php
...
class DBProps {
  const which = "mysql"; // "mysql" or "sqlite"
  ...
Open a terminal shell, cd to the WebLibraryDB directory and run:

MAC, Linux:
$ php setup/makeTables.php
Windows:
> php setup\makeTables.php

To understand the behavior of RedBean operations, run the following test scripts. They can be run repeatedly, each time producing slightly different results based on randomized values set within the scripts.
php showTest.php

Using SQLite

Set the which constant:

include/db.php
...
class DBProps {
  const which = "sqlite"; // "mysql" or "sqlite"
  ...
Then run:
php showTest.php
The SQLite database is the file include/database/db.sqlite. For MAC OS X and Linux systems the permissions on this file and its enclosing directory must be set correctly. The initialization code should have the same effect as these two UNIX-like shell-based commands :
chmod 777 include/database/
chmod 666 include/database/db.sqlite

Creating the tables

The makeTables.php file does nothing more than call two scripts in order:

setup/makeTables.php
<?php
chdir(__DIR__);
echo "\n============= createTables\n";
require_once "createTables.php"; 
 
echo "\n============= populateTables\n";
require_once "populateTables.php";
The initial open pre tag simply ensures that the output of the browser execution will look like that of the shell execution. The createTables.php file drops and then creates the tables from scratch. Because of the foreign key constraints, the borrow table must be dropped before user and book and created after these two tables.

setup/createTables.php
<?php
chdir(__DIR__);
require_once '../include/db.php';
 
echo "\n---- database = ", DBProps::which, "\n\n";
 
$create_order = ["user", "book", "borrow"];
$drop_order = array_reverse($create_order);
 
foreach ($drop_order as $table) {
  $sql = "drop table if exists `$table`";
  echo "$sql\n";
  R::exec($sql);
}
 
foreach ($create_order as $table) {
  $filename = sprintf("%s-%s.sql", $table, DBProps::which);
  $sql = file_get_contents("tables/$filename");
  echo "$sql\n";
  R::exec($sql);
}
select
RedBean provides a mechanism to run SQL commands directly. In some cases, the complexity of an SQL query cannot be matched fully by the ORM operations. The R::exec function is used to execute non-SELECT SQL queries. This is useful for low level SQL operations such as creating and dropping tables. Our application uses it in just these ways:

Redbean Database Initialization

At the heart of the code used is the DBProps class defined in include/db.php. All scripts depend upon the database properties, either for MySQL or SQLite. Using the given database settings, these turn out to be:
[
  'url' => "mysql:host=127.0.0.1;dbname=test",
  //'url' => "mysql:host=localhost;dbname=test",
  'username' => 'guest',
  'password' => '',
]
//or, after defining $database to be the full path to the desired file:
[
  'url' => "sqlite:$database",
  'username' => null,
  'password' => null
)
These settings are what is needed by RedBean PHP (via PDO) to initialize the connection with the DB::init() call. The code sets $props to one array map of one of these two choices and then executes these statements:
R::setup($props['url'], $props['username'], $props['password']);
R::freeze(true);
The setup call establishes the database connection. The freeze call tells RedBean not to operate in fluid mode in which database tables are created and altered on the fly as needed. The implication is that we have pre-created the database tables externally and do no want RedBean to assist, making the remaining RedBean operations much more efficient.

localhost vs. 127.0.0.1

The MySQL url uses the IP address "127.0.0.1" and comments out the usage with "localhost." They are the same thing, but doing so makes the access more general for MAC and Linux system. For MAC and Linux, using "localhost" will attempt to access a dedicated file socket, whereas with the IP address, it will attempt to access the MySQL network socket at port 3306.

For our purposes, it means that all 4 possible combinations of Php and MySQL will work:

Redbean Operations

Adding records

The populateTables.php script uses the R-functions: R::dispense and R::store. For example, these operations are representative:
$book = R::dispense('book');
$book->title = "Java in a Nutshell";
$book->binding = rand(0,1) == 0 ? "paper" : "cloth"; 
$book->quantity = rand(1,5);
$book_id = R::store($book);
 
$user = R::dispense('user');
$user->name = "bill";
$user->email = "digger@gmail.com";
$user->password = hash("sha256", "bill");
$user_id = R::store($user);
In our script, only the book titles are "real data," the other two fields are randomized. The titles are loaded from an external file, setup/titles.txt and Php loads these into an array using the file function:
$titles = file("setup/titles.txt");

Retrieve by id primary key

Retrieving by the id primary key is the preferred way to obtain an object, for example, the book with id #4:
$book = R::load('book',4);
After a successful retrieval, we can access the fields as object members:
$book->id, $book->title, $book->binding, $book->quantity
The $book object is called a bean, of type RedBean_OODBBean. You are discouraged to try to dump the entire object with print_r($book) because you'll get much more than you bargained for. Instead, do:
print_r($book->getProperties());
getting the information you really want, like this:
Array (
    [id] => 4
    [title] => Data Structures in Java
    [binding] => paper
    [quantity] => 1
)
If you look for a non-existent book, you will still get something, for example:
$book = R::load('book', 400);
print_r($book->getProperties());
gives
Array (
    [id] => 0
)
Therefore, you can see how to test if book_id is one of a valid book or not:
$book = R::load('book', $book_id);
if ($book->id == 0) {
  // no such book
} 
else {
  // the book exists
}

Modify an existing book

The R::store operation serves to do an UPDATE as well as an INSERT. A typical operation is this:
$book = R::load('book', 4);
$book->quantity += 1;
R::store($book);
Any way to obtain an existing book (such as R::findOne) can be used as the starting point of an update operation. The key difference between an update and an add is that the $book exists, as detected $book->id != 0. Of course when a book is added we usually must set all the fields, whereas an update may set some fields and not others.

Select multiple records with R::findAll

The R::findAll (or, R::find) operation is used for table SELECT operations based on queries other than by id. For example, when used with one parameter, the table, we get all records:
$all = R::findAll('book');
What it returned is an array of database objects. The array is actually a map, corresponding the object's id to the object:
$all = [
  id => database object for id
  ...
]
You can iterate through it either ignoring the id key or not:
foreach($all as $book) {
  ...
}
or
foreach($all as $book_id => $book) {
  // $book_id is $book->id
}
If no records were obtained, the map is empty, and so you can use the test:
$records = R::findAll('book', ...);
if (count($records) == 0) {
  // none found
}
You can obtain information about the total records more simply using the R::count operation:
$num_books = R::count('book');
Ordinarily, a second parameter is used to specify other SELECT qualifications such as WHERE, ORDER, LIMIT, etc. For example:
$some_1 = R::findAll('book', "where quantity > 3");
$some_2 = R::findAll('book', "quantity > 3 order by title");
$some_3 = R::findAll('book', "binding='cloth' order by quantity desc");
Note that the SQL keyword "where" is optional. We can omit where specifications if they are not used:
$all_2 = R::findAll('book', "order by title");
$some_4 = R::findAll('book', "order by quantity desc limit 10");

Value bindings

It is always a better idea to employ a third parameter to insert values. For example:
$some_5 = R::findAll( 'book', "quantity>? and binding=?", [3, 'cloth'] );
The reason this is so important is that you want the inputs to be sanitized before insertion into the WHERE clause, especially when the input values are obtained from variables:
$q = /* a quantity */;
$b = /* a binding */;
$some_6 = R::findAll( 'book', "quantity>? and binding=?", [$q, $b]);

Fetch a single record with R::findOne

The R::findOne operation returns a single record object (the first of many if applicable), not an array of objects. This is commonly used for retrieval based on a unique field, e.g.:
$username = "alice";
$alice = R::findOne('user', "name=?", [$username]);
 
$title = "Multimedia Systems";
$book = R::findOne('book', "title=?", [$title] );
Please observe the critical nature of value bindings, because, for example, this is a mistake:
$title = "Carla's Code Secrets";
$book = R::findOne('book', "title='$title'");
If no such record exists, findOne returns null. This feature can be tested before proceeding:
$title = /* some title */
$book = R::findOne('book', "title=?", [$title] );
if (is_null($book)) {
  // no such book
}
Recall that testing for null can be done in several equivalent ways:
is_null($book)
$book === null
!isset($book)

Deleting a record with R::trash

The operation R::trash does an individual deletion. Commonly the operation is preceded by retrieval by id:
$book = R::load( 'book', $book_id );
R::trash($book);
A valid $book found in any way can be deleted with this operation. Deleting a non-existent book from a load operation does not generate an error, but a null from findOne does. For example:
$book = R::load( 'book', 400 );  // book does not exist
R::trash($book);                 // no problem
 
$book = R::findOne( 'book', "title=?", ["NO SUCH BOOK"] );
R::trash($book);                 // ERROR!!

Operations through R::$adapter

Other RedBean operations are available through helper objects. For example, the static R::$adapter object allows us to compute the number of affected rows after a database operation:
$num = R::$adapter->getAffectedRows();
This can be useful, say, in the delete operation to obtain the delete status:
$book = R::load('book', $book_id);  // $book_id may not be valid
R::trash($book);
$successful_delete = R::$adapter->getAffectedRows() > 0;

Many-to-many Operations

For many-to-many relationships which employ a join table, RedBean provides tools by which you can effectively "pass through" the join table transparently. Suppose for definiteness we have:
$alice = R::findOne( 'user', 'name=?', ["alice"] );
$book7 = R::load( 'book', 7 );

The shared mechanism

For tables book and user, if the join table used the "default name"
book_user
then we could use these operations:
$alice->sharedBook
$book7->sharedUser
to get a list of all books borrowed by alice, and respectively, all users which have a copy of the book. The disadvantage of using the default join table name is that RedBean does not allow creating instances of join records directly:
$join = R::dispense('book_user');
If the join table has a simple name (borrow in our case), we must present the name within the via operation as:
$alice->via('borrow')->sharedBook
$book7->via('borrow')->sharedUser
These listings are actually array maps associating the book id to the book object, and so we can list all books of alice by:
$aliceBooks = $alice->via('borrow');
foreach($aliceBooks as $book_id => $book) {
  // do something with the $book
}
You can use the with operator to impose ordering/limiting features, for example:
// alice's books, ordered by title
$alice->via('borrow')->with("order by title")->sharedBook
 
// alice's books, at most 5, ordered by quantity descending:
$alice->via('borrow')->with("order by quantity desc limit 5")->sharedBook

Creating joins with shared

To lend book #7 to alice we can do either of these two:
$alice->via('borrow')->sharedBook[] = $book7;
R::store($alice);
 
// or
$book7->via('borrow')->sharedUser[] = $alice;  
R::store($book7);
Even if the borrow record already exists, using this operation has no ill effect.

The defect with this method is that fields other than the joined id fields (like borrowed_at in our case) are not effected. This means one must be able to initialize the join record without initializing these extra fields. The requirement is that an extra field In our case the borrowed_at field is allowed to be NULL:
create table borrow (
  ...
  borrowed_at date,
  ...
)
With the following table definition, creation using the "shared" mechanism would fail:
create table borrow (
  ...
  borrowed_at date not null,
  ...
)

Joining with the link operation

Alternatively, we can set the other fields in the join record when the join is created with the RedBean link operation:
$alice->link( 'borrow', ['borrowed_at' => SOME_DATE_VALUE ] )->book = $book7;
R::store($alice);
 
// or
$book7->link( 'borrow', ['borrowed_at' => SOME_DATE_VALUE ] )->user = $alice;
R::store($book7);
This statement is meant for the insertion of a link, not the updating of join information and it throws an error if the join already exists.

Three ways to create a join

Here are three ways to create a join record in the borrow table, setting the borrowed_at field to some specific value. Assume we already have the $user and $book objects.
  1. Create the join record explicitly.
    RedBean would not allow you to create a join record in the following way with the "natural" table name, book_user.
    $borrow = R::dispense('borrow');
    $borrow->user_id = $user->id;
    $borrow->book_id = $book->id;
    $borrow->borrowed_at = SOME_VALUE;
    R::store($borrow);
  2. Use the link operation.
    This is the slickest way to achieve the desired outcome:
    $user->link('borrow', ['borrowed_at' => SOME_VALUE])->book = $book;
    R::store($user);
    The user and book usages can be switched.
  3. Use the shared mechanism.
    The issue here is that the extra fields in the join record can cause problems. These extra fields must allow NULL or have default values.
    $user->via('borrow')->sharedBook[] = $book;
    R::store($user);
    $borrow = R::findOne('borrow', 'book_id=? and user_id=?', [$book_id,$user_id]);
    $borrow->borrowed_at = SOME_VALUE;
    R::store($borrow);
    The user and book usages can be switched.

Testing joined entities

The most obvious way to test if a book and user are joined is to see if the borrow record for the pair exists. We have provided a Helper class with one support function used to obtain the borrow record for a given book and user id:

include/helper.php
<?php
require_once "rb.php";
 
class Helper {
  public static function getBorrowBookUser($book_id, $user_id) {
    return R::findOne(
      "borrow", "book_id=? and user_id=?", [$book_id, $user_id]
    );
  }
}
Using this, we can do:
$book_id = /* ... */;
$user_id = /* ... */;
$borrow = Helper::getBorrowBookUser($book_id, $user_id);

$are_joined = ! is_null($borrow); // or, isset($borrow)

Removing join records

The most obvious way to do removals is to find the join record from the two foreign keys and delete it. Using the helper class defined above, we can delete a borrow record by:
$book_id = /* ... */;
$user_id = /* ... */;
$borrow = Helper::getBorrowBookUser($book_id,$user_id);
R::trash($borrow);
The Php unset operation offers an alternative slick way to effect deletion of a join record:
unset($book->via('borrow')->sharedUser[$user->id]); 
R::store($book);
 
// or
unset($user->via('borrow')->sharedBook[$book->id]); 
R::store($user);

One-to-many Operations

Although less significant for this application, RedBean provides several useful mechanisms for one-to-many relations. A book has many borrow records, but a borrow record corresponds to only one book, so this relationship is one-to-many. Likewise the relationship between borrow record and a user is one-to-many.

If, say, we have a database object $join corresponding to a join record with certain user_id and book_id values, then we can go directly to the user database object from the join via the syntax:
$user = $join->user;
$book = $join->book;
The former is equivalent to doing:
$user = R::load('user', $join->user_id);
In particular, we can rewrite the code to create a borrow for a $user and $book like this:
$borrow = R::dispense('borrow');
$borrow->user = $user;
$borrow->book = $book;
$borrow->borrowed_at = SOME_VALUE;
R::store($borrow);

The "own" mechanism

The many-to-one relation can be expressed as "ownership", the one (say a user) is said to own the join records which have his/her id within. RedBeans tacks on the "own" operator prefix as a way of getting from one record (like a user) to the many borrow records which it owns. We can use these expressions to get associated join records from user or book:
$user->ownBorrow
$book->ownBorrow
Either represents the array of links from in which we can iterate through to find all join entries. For example,
foreach ($book->ownBorrow as $borrow) {
  echo "$borrow->book_id, $borrow->user_id, $borrow->borrowed_at\n";
}
This gives a convenient way of finding, for a given user, the list of all borrow information. For example:
$alice = R::findOne('name=?', ['alice']);
foreach( $alice->ownBorrow as $borrow ) {
  $book = $borrow->book;        // a book she borrowed
  $date = $borrow->borrowed_at; // when she borrowed it
}
RedBean provides a withCondition operator which allows you filter the links according to SQL specifications, e.g.,
$joins = $book->withCondition('order by borrowed_at')->ownBorrow


© Robert M. Kline