Jan
19
2009
2

Converting MySQL Database to UTF-8

When we were building a WordPress website for a Chinese community we found even the configuration in WordPress defined default charset as 'UTF-8', becase the datbase tables/fields are by default not in 'utf8_unicode_ci', it won't allow users to post in Chinese the characters all come up as ? marks.

So we decide to alter all the tables and fields for WordPress to UTF-8, which would fix the problem. However it's not easy to change all the fields one by one from one charset and collation to another. Then we have written the following php script to help converting the tables and all text fields to UTF-8.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php
//mysql database name
$db_name = "my_test";
 
//db connection
$dc = mysql_connect("localhost", "root", "");
mysql_select_db($db_name, $dc);
 
//set database charset and collation
$sql = "ALTER DATABASE `".$db_name."` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
mysql_query($sql);
 
//set tables charset and collation
$sql = "SHOW TABLES";
$rs = mysql_query($sql);
$tables = array();
while($r = mysql_fetch_array($rs)){
	$sql = "ALTER TABLE `".$r[0]."`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
	mysql_query($sql);
	$tables[] = $r[0];
}
 
//set table rows charset and collation
foreach($tables as $tab){
	$sql = "SHOW FULL COLUMNS FROM `".$tab."`";
	$rs = mysql_query($sql);
	while($r = mysql_fetch_array($rs)){
		if(!empty($r['Collation']) && $r['Collation'] != 'utf8_unicode_ci'){
			$nu = ($r['Null'] == "YES")? "NULL" : "NOT NULL";
			$df = empty($r['Default'])? "" : "DEFAULT '".addslashes($r['Default'])."'";
			$sql = "ALTER TABLE `".$tab."` CHANGE `".$r['Field']."` `".$r['Field']."` ".$r['Type']." CHARACTER SET utf8 COLLATE utf8_unicode_ci ".$nu." ".$df;
			mysql_query($sql);
		}
	}
}

To set the MySQL default charset and collation to UTF-8, so the tables created later without any charset or collation specification would be default to UTF-8, please add these two lines to your my.cnf file under [mysqld],

character-set-server=utf8
default-collation=utf8_unicode_ci
Written by in: Web Development | Tags: ,
Jan
17
2009
10

PHP Frameworks Comparison Test

We have done enough reading and digging on the net just to see how others test and compare PHP frameworks, however there are hardly anything recent and convincing. Most of the test are on throughput of a simple 'Hello World' page, which we don't think reflect the real performance of a framework, simply because no Database interaction was involved, hence Model as in MVC is not tested at all. So we decide to conduct our own test with a more real world case, which would require MVC work together intensively to get up the result.

The Test

We have each programmer picked a popular PHP framework, and the task is to setup the framework and create MVC for the benchmark, which involves,
  • 1000 database insertion
  • 1000 database update
  • query 1000 records (all fields), and display in a list
  • each record size is about 2kb
We then put all the coded project onto one work station which has the following configuration,
  • Pentium 4, 4.3GHz
  • 2GB RAM
  • Windows XP, SP3
  • XAMPP 1.7.0 (patched to work with PDO)
  • APC enabled (Zend Optimizer disabled)
  • All programs turned off, including Anti-Virus and Firewall
We use a PHP batch script under CMD to make queries to each framework 10 times, and reports on the average response time and memory usage.

The Result

Framework Database Engine Avg. Response Time Avg. Memory Usage
Pure PHP mysqli 5.28s
 
0.14MB
 
oModel* adodb mysql 7.13s
 
6.88MB
 
Yii Framework
(yiilite)
pdo_mysql 7.41s
 
8.38MB
 
Yii Framework pdo_mysql 7.7s
 
9.44MB
 
Kohana mysqli 7.68s
 
11.22MB#
 
Zend Framework pdo_mysql 8.37s
 
7.99MB
 
Zend Framework mysqli 11.28s
 
7.88MB
 
Akelos mysqli 12.98s
 
10.93MB
 
* The Orite in-house lightweight MVC framework
# With <benchmark> parameter turned off in the database config file, it only uses 1.5MB memory, and takes about 7.8s. Thanks Jeremy Bush for the advice. (updated 24/07/2009)

The Verdicts

Framework Pro. Con.
oModel Fastest framework, light file structure, highly flexible, real short learning curve Not as comprehensive, not well documented, no community support, need high skill set to work on for large-scale project
Yii Framework Fast, comprehensive, simple and secure file structure, strictly php5 OO, well documented, code generation Farely new, still building community awareness
Kohana Flexibility, Easy start Big memory footprint, DB feature incomplete
Zend Framework Great library set, flexible, best community support Long learning curve, code generation problem*
Akelos Great RoR port, database migration Slow, php4
* We have try to run Zend-Tool on two workstations, all failed to run

The Conclusion

We understand this benchmark comparison is still bit off the real world, as it has too much emphasis on the database operation, where as in real web scenario the result can vary on different server deployment. Also, we didn't test cache mechanisms under each framework, which would play a real important role in the production environment, when server gets lot of hits on dynamic content, the result can be really different depend on project nature. Hopefully Orite will further test  real web project taken most the core features of frameworks in consideration. If we were after speed, we would stick with oModel which had been the backbone of lots of recent Orite projects. After testing the frameworks, our team had picked both Zend Framework which has the most supportive community and feature set, and Yii Framework the good combination of comprehensive feature, ease of use and performance. We will start few project based on Yii Framework and continuously keep eyes and hands on Zend. Interesting findings regarding this topic will be posted after we have dived into these frameworks a bit more.
Jan
16
2009
5

Australian Postcodes with Geocoding (updated 12 Jan 2009)

Orite has been involving in quite few projects that requires the Australian postcode database, and some even requires Geocoding (GPS coordinates) for mapping purpose. So we have written code to read data from the official AusPost Postcode Datafile and use GoogleMap API to find the coordinates and save to a csv file with 5 fields consists the suburb name, state, postcode, latitude, longtitude. We have recently updated this database accroding to the 12 Jan 2009 datafile released by AusPost, which are available for download in 7Zip compressed file, Australian Postcodes with Geocoding (183KB) If you found anything incorrect in this datafile, please post to let us know.
Jan
16
2009
4

XAMPP 1.7.0 for Windows Problem

We recently updated our XAMPP on developers work stations to 1.7.0, since it incorporates the latest MySQL 5.1 and PHP 5.2.8 releases. However we dicovered an issue with the mysql libraries which prevents PDO from running properly. Come with the package there are two versions of mysql libraries, if you check xampp/php and xampp/apache/bin you will find one is named libmysql_5.0.51a on the files, the other is just libmysql.dll, all we did to fix the problem is replacing the library files with the 5.051a files in the directories mentioned before. Also in xampp/php/ext there're php_mysqli.dll and php_mysql.dll needs replacing too. Please make sure you stop Apache before replacing file under xampp/apache/bin This should fix the problem with PDO.
Written by in: Web Development | Tags: , , , ,
Jan
16
2009
0

Searching for The PHP Framework

Orite dev team have been working on our own MVC PHP dev framework for quite few years, which we utilize Adodb for database abstraction and TemplatePower for View template, and we have the handy oModel (Orite Model) to create all database tables as class objects, so all the database operations are nicely layed out in 3 layers. However we write our own Control, which consists of lots of inclusions (of classes and libraries) and dirty (not very readable) procedure logic etc. It's not too much of a hassle to develop on the Orite MVC framework, since the simplicity of it's structure and logic. However in order to do tricks like Ajax, URL rewriting, it requires all the knowledge about PHP MySQL HTML Javascript and Apache, and sometime because the different deployment environment one has to learn different platforms, we have been deploy on Linux (various distro), Windows (different versions) and Solaris. Although the Orite MVC framework performed for years, we decided to go something else. Since for the new year we are proposing few large-scale community portal sites, which would require more collabrative dev and constant maintenance. Here we set out the feature request for the framework we looking for which must have,
  • MVC pattern, since its what we familiar and happy with
  • No non-sense folder structure, not too flexible or too restricted
  • Solid and performance database abstraction layer, support transactions
  • Good error handling and reporting
  • URL management, routing and filtering
  • Layered theme, layout, view
  • Code generation
  • Strict PHP5 implementation
  • Good community support and active development
  • Easy learning and start
  • Performance and security
Would be bonus to have,
  • Templating
  • Ajax
  • Caching
  • Built-in useful libraries (validation, emailing, etc)
  • Shell support for cron jobs
  • I18n
  • Database migration
We know it's not an easy task to find THE right PHP framework to use and stick with, since there are quite few available on the market and all have quite good reputation. Also because the evolving of the technology and vary of our project scope. So we decide to firstly pick a few popular ones and do our own testing and benchmarking, that's the only way to choose properly. We'll follow up with the results... If you are using a PHP framework and think that's the best, please feel free to post a comment below.
Written by in: Web Development | Tags: , ,

Copyright Orite Group Pty Ltd | Powered by WordPress | Theme: Aeros 2.0