Migrating Web Applications for use with SQL Anywhere Web Edition - Page 2

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 exceptions.

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.

Conclusions

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.