PHP has exploded onto the web application scene, gaining high popularity among developers. This certainly comes as no surprise given how easy it is to setup and deploy. Typical applications make use of a MySQL back-end, for reasons of availability and cross-platform support. However, once the application is up and running with a PHP/MySQL setup, development need not stop there. An abundance of features can be made available by running the same web application off of a SQL Anywhere database instead. Full-text search, web services, and support for mobile synchronization are just a few of the added perks available for free. With SQL Anywhere 11’s PHP support, integration into existing environments is nearly painless. This article outlines some of the motivation behind wanting to port existing applications over for use with SQL Anywhere, and then covers the steps necessary to do this. Specifically, phpBB3, a popular forum application, is quickly and easily ported. The application itself is used by many web sites today, both home-grown and commercially developed.
Introduction
It comes as no surprise that PHP has become
a web standard technology. It’s free, in every sense of the word, and easy to
deploy on a wide variety of platforms. It pairs nicely with other free
technologies, and within a few tens of minutes enables a world of possibilities
on the web. This attractive feature set makes it ideal for starting developers,
and those looking to create the "first draft" of a web application.
Typically, developers pair PHP with MySQL for use in the data tier. This is
only natural since MySQL is also free to download and use. More and more, MySQL
is becoming a de facto standard in the industry when creating data-driven PHP
applications.
Released last year as a "Web
Edition" (read free to download and use), SQL Anywhere might just meet the
needs of web applications better. Among the useful set of features included for
web developers: extensive GUI administration and monitoring tools, RSA-security
enablement, web services (SOAP), clustering, and even inter-database
synchronization. But more than just these extras, what makes SQL Anywhere
useful is the advanced query processing that goes on. Underneath the hood,
their "Web Edition" is the exact same product as their full edition
software, just stripped of features like ECC-security, in-memory mode, and
several others. What that means is, for absolutely nothing, developers of web
applications can enjoy the same self-tuning query processing engine as those
working on enterprise-level applications.
At the end of the day, developers of PHP
web applications have another choice for their data tier. SQL Anywhere snaps
into existing development processes without any effort at all, and offers many
more features than the incumbent. That way, once the application is "up
and running", enhancements can actually be made because the technology
allows it.
Knowing that SQL Anywhere might be useful
is one thing, but taking applications that have already been designed for MySQL
and enhancing them with SQL Anywhere is quite another. Well, actually, the
process isn’t so difficult. In the end, the benefits outweigh the cost. That
is, code changes might take a bit of time, but the features afforded by them
make it worth it. As a preview of how simple it is to make the changes, one
can look at the PHPAPI reference of the SQL Anywhere.
It’s important to notice that these are nearly the exact same functions as
provided by the default MySQL PHP driver just renamed from "mysql_*"
to "sasql_*". Meaning, it is possible to achieve nearly the same
functionality from either driver with a simple find & replace.
To demonstrate the process of porting over
an application, phpBB3 is chosen. This application is commonly used as forum
software for many websites. It comes as no surprise that it is free and open
source; definitely the way to go, just not the place stop at. As will be
discussed later, there are several improvements that can be made to improve the
database usage in this application. Their site boasts millions of users
worldwide, "making it the most widely used open source bulletin board
system in the world".
The Existing Application
The application setup itself is quite
common. The application is a bunch of PHP scripts hosted on a PHP-enabled web
server, with a database management system installed on the system as well. The
scripts depend on the PHP setup to have the appropriate means of connecting to
the database software. Installing it from scratch on a Windows machine would
entail getting IIS running, installing PHP (along with the MySQL driver) and
configuring IIS to use it, installing MySQL, and then dropping the phpBB3 code
on to the web server.
The code for phpBB3, which can be
downloaded from their site, follows a very sensible design pattern. That is, it
abstracts away the database from the rest of the code. Essentially, the
application maintains what it calls a "Database Abstraction Layer"
(DBAL), which provides a uniform set of data tier interfaces to the rest of the
application. This allows the application to be flexible, providing specific
implementations (or drivers) for use with different databases. Indeed, phpBB is
one of the only forum software packages to provide support for a wide variety
of databases. When an application needs to access or modify data, it calls one
or more functions from the DBAL, which, in turn, calls functions from the
specific driver.
The key differences between RDBMSs are not
just in the features and function support they provide, but also in the syntax
for SQL that they use. Almost every database product uses its own dialect of
SQL. For that reason, there are no specific queries in the application code
itself. Instead, the application asks the DBAL to build the query strings and
then execute them against the database. In this manner, different drivers can
take care of the appropriate syntax for their respective database.
Specific drivers are provided already for
the MySQL, Oracle, SQLite, Microsoft SQL Server, and others. Porting the
application over merely consists of creating a similar driver for SQL Anywhere,
and then copying the data structure and contents.
The Changes
Setting up SQL Anywhere
The following shows the required steps for
setting up PHP with SQL Anywhere. First, copy the appropriate extension from
the Bin32 directory of SQL Anywhere. For example, if using PHP version 5.2.6,
copy php-5.2.6_sqlanywhere.dll to the ext folder of PHP.
Next, edit the php.ini configuration file
to use the extension.
Now that PHP is set up for use with SQL
Anywhere, a database can be created. This is done quickly and painlessly from
Sybase Central, by selecting Tools > SQL Anywhere > Create Database…
This launches the database creation wizard, which will create, tune, and mount
the database.
Upon successful creation and mounting of
the database, it will be ready to receive incoming connections.
