A Web Developer's Diary

Lightweight self-contained database: SQLite vs. H2 database vs. MySQL embedded

7/8/2012

1 Comment

 
SQLite is a popular choice for PHP programmers to kick-start their development. It's easy, self-contained, requires no configuration, no server and all the data is kept neatly in just one file. Then later on, the application is commonly migrated to a production DBMS (e.g. MySQL). There have not been any alternatives for SQLite when developing in PHP with MySQL as a target platform. But then again, why would you want an alternative for SQLite? Well, to name a few reasons: MySQL compatibility and support for hash indices. And that's not all! Keep reading to see why.

When looking for alternatives to SQLite, I came across two suitable candidates: H2 database engine and a MySQL embedded version. My criteria were that the alternative should be as easy to use as SQLite and compatible with MySQL, based on two reasons. First, I wanted it to be compatible with MySQL so that I can keep using that easy self-contained database for development even after the application has gone into production. Second, an easy to setup database is great for bringing new developers up to speed with development as fast as possible.

Let's start with the good stuff. Below is a comparison of SQLite, H2 database engine and MySQL embedded version.
Comparison of self-contained PHP development databases.
Compare SQLite H2 database engine MySQL Embedded
Footprint 350KiB ~1MB <2MB
License Public domain Dual: Modified MPL 1.1 / EPL 1.0 (commercial friendly) GPL 2.0 (only commercial friendly if not redistributed)
Self-contained ✔ ✔ ✔
Single file ✔ ✔ ✖
Serverless ✔ ✔ ✖
Server-mode ✖ ✔ ✔
Zero-configuration ✔ ✔ ✔
Transactions ✔ ✔ ✔
Indices ✔ (B-tree, R-tree, full-text) ✔ (B-tree, tree, hash, full-text) ✔ (B-tree, R-tree, hash, full-text)
MySQL compatibility ✖ ✔ (but not 100%) ✔
Compatibility with other DBMS ✖ ✔ MySQL, PostgreSQL, Oracle, MSSQL, DB2, HSQLDB and Derby ✖
Encryption ✖ ✔ ✖
In-memory databases ✔ ✔ ✔ (MEMORY storage engine)
To me it looks like H2 database is the easiest to manage. It requires no server and everything is put neatly in one file, which makes back-upping and sharing databases among developers easier. It even has a nice extra feature over MySQL: encryption of data files (though I don't see a direct need for that during development for me personally).

I have experimented with PHP and H2 before and ran into some limitations (Quercus' MySQL driver doesn't play well yet with H2's MySQL compatibility mode so I have to use the Quercus' PDO driver instead). I haven't tried embedding MySQL yet, which is what I am going to try next.

The embedded version of MySQL has the big benefit of being 100% MySQL compatible, but it also has an uncertainty. I am not sure if it's okay to redistribute my application (containing embedded MySQL) among developers. Another limitation could be that I would have to buy a commercial license for MySQL should I choose to distribute embedded MySQL to customers (vs. customers setting up their own MySQL server). Though, it is okay to use MySQL embedded version for a website hosted by myself (it's not technically distributed in that case).

For now, I have not decided yet whether I will stick with H2 or switch to the embedded version of MySQL. I am going to give both a try and I will keep you updated via this blog!
1 Comment

Run Code Igniter on H2 database engine using PDO and Quercus

7/6/2012

4 Comments

 
In my previous post I explained how to setup PHP and H2 database engine using a platform-independent Java stack, a.k.a. JAMP. I managed to get the Code Igniter framework running on JAMP, with some tweaking of the Code Igniter PDO driver. Here are the steps to make it work.
If you would like to skip these steps and jump immediately into some coding action, download the demo.
(Unzip and go into directory jamp-ci-demo/, then type `mvn jetty:run` from the command line. You need Maven installed to execute this command.)
jamp-ci-demo.zip
File Size: 2341 kb
File Type: zip
Download File

First, checkout a copy of JAMP at github (git clone https://github.com/webdevelopersdiary/jamp.git).

Then download Code Igniter and put in the web root (src/main/webapp/).

Because Code Igniter likes pretty urls (ie. /controller_name/method_name) I added support for mod_rewrite to JAMP. It can parse mod_rewrite rules which are located in .htaccess directly in the web root (src/main/webapp/). Add src/main/webbapp/.htaccess with mod_rewrite rules:
<IfModule mod_rewrite.c>
RewriteEngine On

RewriteCond %{REQUEST_URI} ^(system|application).*
RewriteRule ^(.*)$ /index.php?/$1 [L]

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php?/$1 [L]
</IfModule>

<IfModule !mod_rewrite.c>
ErrorDocument 404 /index.php
</IfModule>
Start up the JAMP web server (make sure .htaccess exists before starting the web server or it will have trouble discovering it, then execute mvn jetty:run). Verify Code Igniter is running correctly by going to http://localhost:8080/welcome (the default controller).

If you see a pesky error '$assign_to_config is an undefined variable', go to src/main/webapp/index.php and add
$assign_to_config = array();
under the section 'CUSTOM CONFIG VALUES' (line 110 of index.php) and that should get rid of the error.

Next we configure Code Igniter to connect to the H2 database using PDO. Edit the configuration located at src/main/webapp/application/config/database.php and set these settings:
$db['default']['hostname'] = 'mysql:';
$db['default']['dbdriver'] = 'pdo';
Next, we have to make a slight modification to Code Igniter's PDO driver. Change the method num_rows() from src/main/webapp/system/database/drivers/pdo/pdo_result.php to:

	/**
* Number of rows in the result set
*
* @access public
* @return integer
*/
function num_rows()
{
$sql = $this->result_id->queryString;
if(NULL === $sql) {
$sql = print_r($this->result_id, TRUE);
$quercus_preg = '/\Aresource\(PDOStatement\[(.*)\]\)\z/is';
$php_preg = '/\APDOStatement Object\s*\(.*=>\s*(.*)\)\s*\z/is';
if(preg_match($quercus_preg, $sql)) {
// We're running in Quercus
$sql = preg_replace($quercus_preg, '\1', $sql);
} else if(preg_match($php_preg, $sql)) {
// We're running in PHP
$sql = trim(preg_replace($php_preg, '\1', $sql));
} else {
// No clue about what is running this script!
$sql = NULL;
}
}
if (is_numeric(stripos($sql, 'SELECT')))
{
$count = count($this->result_id->fetchAll());
$this->result_id->execute();
return $count;
}
else
{
return $this->result_id->rowCount();
}
}
Everything should now be in place to connect to the H2 database using Code Igniter's ActiveRecord library. Let's test it! Create a test controller application/controller/test.php:
<?php

class Test extends CI_Controller {
public function index() {
$this->load->database();
$sql = $this->db->query('CREATE TABLE IF NOT EXISTS test (value DATETIME)');
$sql = $this->db->set('value', 'NOW()', FALSE)->insert('test');
var_dump($this->db->get('test')->result());
}
}
Voila! Go to http://localhost:8080/test and enjoy a working Code Igniter on H2 database using ActiveRecord :-)

Note: I used Code Igniter version 2.1.2, Quercus version 4.0.28 and H2 database version 1.3.167.
4 Comments

JAMP: an ultra portable PHP, web server and database stack in Java

7/5/2012

5 Comments

 
Recently I was experimenting with running PHP and the H2 database engine in a Java web server using Caucho Quercus (or just Quercus for short). In theory this would yield an ultra portable platform-independent PHP, web server and database stack. I eventually got it working, but with some limitations. Before I get into details, here are some quick notes: Quercus is a 100% Java implementation of PHP5, though it doesn't support 100% of PHP5's functionality, unfortunately. Also, Quercus' implementation seems to differ from PHP5's implementation sometimes. That said, I thought I would give it a shot anyway, because some popular CMS systems have had success running with Quercus, such as Drupal and Wordpress. I used Jetty as my Java web server (a.k.a. Java web container) and Maven for dependency management. The source code of my experiment is available at github.

This tutorial describes how to set up the PHP and database stack in the Jetty web server. First we set up a web application (webapp for short) in Java which can interpret .php files using Quercus. Then we setup the H2 database engine. Last, we setup the part where PHP can connect to H2 while actually thinking it is MySQL that it's connecting to (using H2's MySQL compatibility mode, because PHP does not have support for H2). Here we go!

Ultra short walk-through
  1. Make sure you have Maven installed.
  2. Download latest version of Quercus (scroll down, get the WAR file).
  3. Go to the directory where you downloaded Quercus and install it in your local maven repository by running the Maven command (e.g. for Quercus version 4.0.25):
    $ mvn install:install-file -Dfile=quercus-4.0.25.war -DgroupId=com.caucho -DartifactId=quercus -Dversion=4.0.25 -Dpackaging=war
  4. Checkout git repository:
    $ git clone https://github.com/webdevelopersdiary/jamp.git
    $ cd jamp
  5. Run web server:
    $ mvn jetty:run
  6. Wait until you see "[INFO] Started Jetty Server" in the console output and point your browser to http://localhost:8080/
  7. The document root is located in src/main/webapp/
  8. Connect to the database in your code using "$pdo = new PDO('mysql:');", mysql_connect() doesn't work due to a driver incompatibility, for more information on this keep on reading.

The long version
Setting up Quercus is quite straight-forward. Download the WAR file of the latest version of Quercus (in my case 4.0.25) and install it in your local Maven repository by running this command from the same location as where you downloaded quercus-4.0.25.war:
 mvn install:install-file -Dfile=quercus-4.0.25.war -DgroupId=com.caucho -DartifactId=quercus -Dversion=4.0.25 -Dpackaging=war 
Then include the following dependency to your Maven project's pom.xml and Quercus should be integrated in your webapp:
<project>
...
<dependencies>
...
<dependency>
<groupId>com.caucho</groupId>
<artifactId>quercus</artifactId>
<version>4.0.25</version>
<type>war</type>
</dependency>
...
</dependencies>
...
</project>
The next step is to be able to run the webapp locally, so that's what we use Jetty for. Add this to your pom.xml:
<project>
...
<build>
<plugins>
<plugin>
<groupId>org.mortbay.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
</plugin>
</plugins>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.mortbay.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
<version>8.1.4.v20120524</version>
<configuration>
<scanIntervalSeconds>1</scanIntervalSeconds>
<stopKey>foo</stopKey>
<stopPort>9999</stopPort>
</configuration>
</plugin>
</plugins>
</pluginManagement>
</build>
...
</project>
Now Jetty and Quercus should be okay to go. You can verify this by putting a phpinfo.php file with the famous code snippet "<?php phpinfo(); ?>" at src/main/webapp/phpinfo.php (relative to pom.xml's location, create directories if they don't exist) and start up the Jetty webserver:
 echo '<?php phpinfo();' > src/main/webapp/phpinfo.php
mvn jetty:run
Wait for Jetty to be completely started (console output should say something like "[INFO] Started Jetty Server") and point your browser to http://localhost:8080/phpinfo.php and verify that Jetty is running and Quercus is installed. (You can stop Jetty by pressing CTRL-C in the console.)

Congratulations! If you made it this far, you have successfully setup a Java web server that can interpret PHP files! But, we're not there yet, we still have to add database support.
We will run the H2 database engine in embedded mode. To do this, we need to include the H2 dependency in the pom.xml. For the integration of H2 and PHP another dependency is required as well: database connection pooling (DBCP), so add both to your pom.xml:
<project>
...
<dependencies>
...
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.3.167</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
...
</dependencies>
...
</project>
To actually integrate H2 and PHP we need to tell Jetty some additional configuration. Get these two files (web.xml and jetty-env.xml) and drop them in your src/main/webapp/WEB-INF/ directory (create this directory if it doesn't exist).
At this point, your PHP webapp is entirely configured to connect an in-memory embedded H2 database. But, there is a catch. At the point of writing this article, H2's MySQL compatibility mode is not compatible with Quercus' implementation of the PHP MySQL driver (any attempt to connect to H2 using PHP's mysql_connect() will result in an error complaining about a syntax error in query "SET NAMES ..."). To work around this, we connect to H2 using Quercus' implementation of the PHP PDO driver, which does seem to be compatible with H2's MySQL compatibility mode. So, create a file, e.g. test-database.php and put it in src/main/webapp/test-database.php:
<?php

$pdo = new PDO('mysql:');
$sth = $pdo->prepare('SELECT NOW()');
if($sth) {
$sth->execute();
if($now = $sth->fetchColumn()) {
printf('Successfully executed query on H2 at %s.', $now);
} else {
echo 'Could not execute query.';
}
} else {
echo 'Could not prepare query.';
}
Go to http://localhost:8080/test-database.php to see if it's working (which if everything went okay, it should!).

So now you should have your ultra-portable platform-independent PHP, web server and database stack up and running. Happy experimenting! :-)
What's next?
I would like to see a working version of Code Igniter using the ActiveRecord class running on this stack. Also, it would be nice to have H2's MySQL compatibility mode compatible with Quercus' MySQL driver, so we can run applications that use mysql_connect() on the stack (e.g. phpMyAdmin).
Trouble shooting
If you want to call PDOStatement::fetchObject(), you should call PDOStatement::fetchObject('stdClass') instead. This is a bug in Quercus (remember I said in the beginning that sometimes Quercus' implementation differs from PHP's). Acccording to the PHP Manual, the default first argument for PDOStatement::fetchObject should be 'stdClass', it seems the folks at Caucho forgot to implement this.

If you want to use a persistent database file instead of the temporary in-memory database, go to src/main/webapp/WEB-INF/jetty-env.xml and change line
<Set name="url">jdbc:h2:mem:;MODE=MYSQL</Set>
to
<Set name="url">jdbc:h2:filename;MODE=MYSQL</Set>
Where you replace 'filename' with a relative path to a file (relative to pom.xml) or an absolute path to a file. Note that "mem:" has an extra ":" after it and "filename" does not. For more information about the H2 JDBC URL see H2's feature list.
5 Comments

Pro-tip: when to avoid addslashes() in PHP

6/29/2012

2 Comments

 
When peer reviewing PHP code, I often find dangerous uses of addslashes(). It is often believed this is a safe way of escaping user input before passing it to e.g. a SQL query, but in fact it's unsafe. If you find yourself using addslashes(), think twice if you are using it safely:
  • In a MySQL context, use mysql_real_escape_string() instead.
  • MySQLi has an identical mysqli_real_escape_string().
  • PDO provides it's own escape method PDO::quote().
  • PostgreSQL has a wide variety of escape functions: pg_escape_literal() for values, pg_escape_bytea() for columns of type bytea, pg_escape_identifier() is used for escaping identifiers (e.g. table, field names).
  • When trying to pass user input to the command line, use escapeshellarg() and escapeshellcmd() to escape the input.
  • When displaying non-HTML user input anywhere on a webpage, always use htmlentities() or htmlspecialchars().
  • This one is a little awkward, but I've seen it before so I thought it's worth mentioning: when including user input in URLs, use urlencode() instead of addslashes()!

If you have more suggestions for safe escaping, please leave them in the comments below. Happy safe coding!
2 Comments

Minifying, Combining and Caching CSS and JS with Code Ingiter

6/29/2012

0 Comments

 
So you realised all these HTTP requests are taking up a lot of page load time? No worries! You'll be up to speed in no-time.

First download the improved version of the CI plugin Carabiner from github (or download zip), originally written by Tony Dewan and now maintained by Mike Funk. Copy the config and libraries folder into your CI installation by putting them in: application/third_party/carabiner/ and add the package to application/config/autoload.php by adjusting the auto loaded packages:
 $autoload['packages'] = array(APPPATH.'third_party/carabiner'); 
Make sure the application/third_party/carabiner/config/carabiner.php configuration file is set properly (e.g. make sure the cache path exists and is writeable) and you can test your first Minify/Combine/Cache experience using this example controller:
  
class TestCarabiner extends CI_Controller {
public function index() {
$this->load->library('carabiner');
$this->carabiner->css('file1.css');
$this->carabiner->css('file2.css');
$this->carabiner->js('file1.js');
$this->carabiner->js('file2.js');
$this->carabiner->display(); // output html which loads cached files
}
}
Besides using $this->carabiner->display() you can also use $this->carabiner->display_string() to get the HTML as a string instead of outputting it directly to the browser (e.g. so you can use it in a template). You can also call carabiner directly from a Code Igniter view using $this->carabiner->display(). Another useful function call is $this->carabiner->empty_cache('both', 'yesterday') which cleans up old cache files. Full documentation is available at this page.
0 Comments

    Author

    Blog about random challenges of a web developer.

    Archives

    April 2015
    May 2013
    January 2013
    July 2012
    June 2012

    Categories

    All
    Aws
    Cdn
    Code Igniter
    Css
    H2database
    Jamp
    Java
    Javascript
    Magento
    Maven
    Mysql
    Opencl
    Php
    Play-framework
    Quercus
    Scala
    Ubuntu

    RSS Feed

Powered by Create your own unique website with customizable templates.