Migrating Web Applications for use with SQL Anywhere Web Edition
July 1, 2009
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.
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.
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.