Tools Chive Lead image: © vicspacewalker, 123RF.com

Chive: Web 2.0 front end for SQL database management

Changing of the Guard

Generations of web admins have used phpMyAdmin or SQL Buddy to communicate with their databases. Newcomer Chive has the potential to send the legacy tools into early retirement thanks to its state-of-the-art Ajax interface and impressive feature scope. By Thomas Drilling

Even bona fide database administrators, who aren't afraid to dabble in MySQL command mode or formulate SQL statements and queries, are likely to appreciate the state-of-the-art Web 2.0 environment for developing and managing SQL statements and databases. A more grateful audience, however, are administrators of CMS, CRM, and groupware systems whose SQL skills are minimal.

Most web applications take care of setting up the required database tables themselves when it comes to their MySQL underpinnings, but creating the initial database and setting up users frequently is a task assigned to the web administrator, and if you have ever backed up or migrated a complete web application from an SQL dump, you will definitely appreciate a tool like Chive in your daily work.

Features

Although phpMyAdmin has proved its value over the years and SQL Buddy has many fans, the more modern Chive [1], with its contemporary Ajax interface, offers a number of additional features, such as an SQL editor with syntax highlighting. Additionally, Chive can create code for reuse as bookmarks as well as triggers, statements and views.

Chive also is more secure than phpMyAdmin, with is continual vulnerabilities [2]. For example, Chive supports direct authentication against the database. This makes it obligatory to secure access to Chive on the web server with SSL/HTTPS.

Not all features existed at the outset, but since the introduction of version 1.0 in December 2011, Chive has nothing to be ashamed of when compared with its competitors [3]. Table 1 provides a comparison with phpMyAdmin and SQL Buddy.

Tabelle 1: Feature Comparison

Create and Modify
Feature	Chive	phpMyAdmin	SQL Buddy
License	GPLv3	GPL	MIT
Editor with syntax highlighting	Yes	No	No
User management	Yes	Yes	Yes
Import	SQL	SQL	SQL
Export	SQL, CSV	SQL, CSV, others	SQL
Foreign keys	Yes	Yes	Yes
Indices	Yes	Yes	Yes
Direct database authentication	Yes	No	Yes
Database	Yes	Yes	Yes
Tables	Yes	Yes	Yes
Routines	Yes	Rudimentary	Rudimentary
Triggers	Yes	Rudimentary	Rudimentary
Views	Yes	Rudimentary	Rudimentary
Inline editing of values	Yes	No	No
Inline editing of rows	Yes	Rudimentary	No
Browsing
Database	Yes	Yes	Yes
Tables	Yes	Yes	Yes
Routines	Yes	Rudimentary	Yes
Triggers	Yes	Rudimentary	Yes
Views	Yes	Yes	No

Installing Chive

Chive, a free tool licensed under GPLv3, has been under development since September 2009 by web agency Fusonic [4] with the help of the Yii [5] PHP framework. Chive relies on PHP 5.1.0 (or newer) with the php_pdo and php_pdo_mysql extensions (see the "PDO Search" box), as well as MySQL 5.

PDO Search

On older operating systems, one requirement imposed by Chive can potentially cause trouble: PHP Data Objects (PDOs). Although this requirement should be fulfilled for a local installation based on a contemporary distribution with at least PHP 5.1, the chance of stumbling over an older version exists if you rely on a web hoster.

The PHP PDO extension is an interface for consistent and simple database access. PHP developers then can use the same functions to read data or create queries independently of the database, for example. Additionally, you need a database-specific PDO driver. As of PHP 5.1, the driver is integrated, but for older versions you will need to fire up your compiler.

For PHP 5.0, the PDO is available as a PECL extension. Because PDOs need the new object-oriented features in the PHP 5 kernel, PDP will definitely not work with PHP versions older than 5.0. That said, PDPO is included in PHP 5.1 and is automatically enabled when you run configure:

./configure --with-zlib -enable-pdo=shared

If you are installing PHP 5.1 manually, you will want to compile PDO as a shared module because this also lets you use the updates provided by PECL via pear upgrade pdo without having to rebuild PHP completely. Note that in this case you will need to update the database-specific drivers. While you're doing so, it is it is also advisable to enable zlib support (for the PEAR installer).

You also need to enable the specific PDO drivers for the required database; the documentation for the PDO extension [8] will give you more information on this. To make sure the PDO extension is loaded automatically, you then need to edit the php.ini file and, after loading the extension,

extension=pdo.so

enable the database-specific driver.

For this article, I installed the Chive version current in January 2012. A newer version 1.0.3 was released March 18, 2012 [6]. To get started, just unpack the archive and copy the Chive folder into the document root of your web server. If you run a recent Debian or Ubuntu system with Apache2 and a VHost configuration, this will be /var/www/vhosts/virtual_host/httpdocs. If you are installing Chive via SSH on a remote server, the following command line will take care of downloading and unpacking

wget -O - http://www.chive-project.com/Download/Redirect | tar -xzp

The command always downloads the current version of Chive and unpacks it in the chive subdirectory below the current user's home directory. All you need to do then is copy the chive directory to the document root on the web server. To benefit automatically from future updates, you can install Chive version 1.0 via the PPA on Launchpad [7] then log in at http://<DB-Server>/chive. Chive lets the user log in directly to the desired database, or as the MySQL admin, you don't need a separate login, and the attractive Ajax interface is available immediately for use – in contrast to the time-consuming installation with phpMyAdmin (Figure 1). Assuming the preconditions stated earlier are fulfilled (PHP 5.1 with the PDO extension), this setup will work perfectly.

Figure 1: The web interface looks modern and reacts quickly thanks to Ajax.

The two information windows of the start-up page – server information and project news on the right and a schema area listing the existing databases on the left – don't use frames. If you click on one of the existing databases, you are taken to the table view (Figure 2). The buttons at the top of the screen let you conveniently toggle between the Browse, Structure, and SQL views (Figure 3).

Figure 2: The navigation area allows the selected database table view to expand.

Figure 3: In browse mode, you can edit field content just as conveniently as the database structure in the structure view.

One of the most interesting features in Chive is the ability to store SQL statements as bookmarks for recurring queries. Any bookmarks you create are kept under the Bookmarks link at the bottom left of the navigation area.

Another highlight is the ability to work inline. If you want to edit various records in the table, you can edit the records directly on the list view instead of opening each one in the detailed view and then returning to the list view when you're done. As I mentioned before, the Chive SQL editor supports syntax highlighting for SQL statements, thus making the code far easier to read and syntax errors far easier to identify. Chive also lets you export SQL and CSV files (Figure 4).

Figure 4: Chive can export SQL and CSV, as expected.

On the import side, the only option is SQL, which also includes compressed formats. For example, Chive automatically identifies the compression type, such as bzip or gzip, during the import process. Besides supporting templates, Chive also offers improved support for stored procedures, triggers, or views compared with phpMyAdmin.

Conclusions

The Chive MySQL management tool is more than just another alternative to phpMyAdmin or SQL Buddy and is enormously helpful in the database administrator's daily work. The state-of-the-art frameless web interface with its many Ajax elements is attractive and functional. If you size your window manually, the representation of sub-windows, dialogs, and buttons can be unfortunate at times, because Chive simply puts elements on top of one another if it doesn't have enough space; despite this, Chive has a contemporary foundation that builds on the MVC-based Yii framework under the hood.

Above all, I liked the integrated SQL editor with syntax highlighting, the ability to sort by dragging and dropping columns, and the ability to define my own triggers, views, and functions. The bookmark function also is unique and useful among tools of this category. The Chive interface looks and feels far more modern than the slightly jaded phpMyAdmin, and Chive is faster, more compact, and – thanks to the ability to log in directly to the database – more secure than phpMyAdmin. I have been using Chive parallel to phpMyAdmin for some time with my own VServers and web spaces, although I must say I no longer really need phpMyAdmin.