Chive

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, 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. 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. Table 1 provides a comparison with phpMyAdmin and SQL Buddy.

Installing Chive

Chive, a free tool licensed under GPLv3, has been under development since September 2009 by web agency Fusonic with the help of the Yii 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.

For this article, I installed the Chive version current in January 2012. A newer version 1.0.3 was released March 18, 2012. 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 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).

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

Assuming the preconditions stated earlier are fulfilled (PHP 5.1 with the PDO extension), this setup will work perfectly.

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

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

The buttons at the top of the screen let you conveniently toggle between the Browse , Structure , and SQL views (Figure 3).

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.

Info

[1] PDO drivers