Creating the Driver
The port is performed easiest by starting
with a copy of the MySQL driver provided (\includes\db\mysql.php), and removing
the implementations under each function declaration. It is important that the
class name be unique and consistent with the filename. Before beginning,
set multi_insert to false, as this is not supported. Most function
implementation can be re-copied over from the MySQL driver, replacing mysql_*
functions with the equivalent sasql_* functions, however there are some
The sql_connect need not be as complicated
as with the other drivers because there is no need to worry about software
versions. Currently, all of the PHP-supporting SQL Anywhere products work the
same, so long as the PHP environment is configured correctly. Therefore, all
that is required is for the connection string to be created, and then
sasql_connect function to be invoked. The sql_server_info does not really need
to be implemented, and the same goes for the _sql_report function, so they can
just return empty values.
The SQL syntax used with MySQL is fairly
different than the one used for SQL Anywhere. SA's dialect is more similar to
that of Microsoft SQL Server syntax. For this reason, the _sql_transaction,
_sql_query_limit, and _sql_like_expression functions should be copied from the
Microsoft SQL Server driver, replacing mssql_* functions with the equivalent
sasql_* functions. An interesting point here is with the _sql_query_limit
function. SQL Anywhere syntax supports "SELECT TOP M START AT N"
syntax. Meaning, it can be given both a limit on the number of rows and a
specific starting index. Thus, there is no need to fetch all rows and seek
through them, which is what the SQL Server driver attempts to do. This is a
prime example of how choosing a better (yet still free) technology can yield a
more scalable product.
Modifying the Settings
In order for the application to make use of
the newly created driver, the configuration must be altered. The configuration
file (config.php) should be edited to modify the values of dbms, dbhost,
dbport, etc... Keep in mind the construction of the connection string specified
in the driver's sql_connect function. It is important to be consistent.
Furthermore, the value of dbms should be set to the name of class of the driver
without the dbal_ prefix (i.e. dbal_sqlanywhere → sqlanywhere).
Migrating the Data
One of the bonuses of having extensive GUI
tools is that they make the job easier. This is especially true with
"Migration Wizard" available under the "Sybase Central"
administration tool. Essentially, it allows for the connection to a MySQL
database and migration all of the contents over to a SQL Anywhere database via
ODBC. Best of all, it is all done automatically. After creating the SQL
Anywhere intend database, simply run the migration wizard to get the data from
the original MySQL database.
It will first be necessary to create an
ODBC data source (DSN) for the existing MySQL database. SQL Anywhere can use this
ODBC data source to create what it refers to as a Remote Server connection. It
will use this remote connection to pull all data. From the Tools menu, select
SQL Anywhere 11 > Migrate Database... to run the Migration Wizard.
The migration wizard will prompt for the
creation of the remote server. Select the MySQL type, and enter the name of the
ODBC DSN created.
It will also be necessary to create an
external login if the MySQL server does not have the same database users as the
target SQL Anywhere database.
Once the remote server is created,
migration is as simple as selecting which tables to import.
Taking Advantage of SQL Anywhere
Following these steps, phpBB3 has been
successfully ported over for use with SQL Anywhere. While the app will run just
as it did before, there are certain modifications that can be made to improve
performance. Thanks to features like clustered foreign keys and indices, and
extensive GUI tools, these modifications are easily realized.
During the migration phase, it may alarm
some developers to notice that the tables contain no foreign key relationships
defined. By placing foreign key constraints on a database, referential
integrity is preserved. Furthermore, SQL Anywhere supports something called
clustered foreign keys, which keep related table rows physically together on the
disk, ensuring quick access. An example of this would be retrieving all the posts
of an associated user. Clustering by user would give very efficient access to
their posts. The GUI tool can be used to add this constraint to the existing
tables seamlessly. While some indices are created by the phpBB3 installation,
they are not sufficient and are not clustered. By using the database monitoring
tool to look at query access plans, it becomes easy to see that certain indices
should be created. Sybase central allows for this as well.
But more than just performance enhancements
are made possible by using SQL Anywhere. One of their signature products is
MobiLink, which allows for mobile synchronization. This can be thought of as
enablement for the next generation of applications: occasionally-connected
mobile. Their technology allows client databases to synchronize changes with
the server when they want. Another feature of benefit to web developers is
exposed SOA services. The database engine itself can host an HTTP server, so
applications can use XML-HTTP readers rather than making direct calls to the
database, allowing uniform access to data.
Though PHP dominates in an era of free
programming for the web, it shouldn't mean sacrificing useful and high-end
features. The database can do more work than it is given credit for. In this
case, coupling PHP with SQL Anywhere can open the door to many performance and
feature benefits for web applications. Existing PHP/MySQL applications can be
easily ported to take advantage of this, as demonstrated through an example
migration of phpBB3. Developers, of both home-grown and corporate-level web
applications, should take care in choosing what free technologies they use with
PHP. What previously constituted "good enough" for the initial
creation of the application might not be the right choice for the future.
Bryan Sachdeva works as a Professional Services Consultant
for a software company in Toronto, Ontario, Canada. Having
completed his undergraduate degree in the field of
Computer Engineer with a minor in Management Science,
his focus has always been on the strategic management of
computing systems in business. On the technical side of
things, Bryan has always been heavily involved in .NET
application and service architecture and development, as
well as data flow architecture. Recently his main interests
have shifted from distributed data and synchronization to
object-relational mapping from class to database. Bryan's
professional experience with different database vendors as
well as work in the domains of Support, Development,
Management, and User Advocacy speak to his ability to
address real needs with real solutions.