Unihan SQL Browser

Created by Uriah Eisenstein , 2010

This is a small Java™ Web Start application intended to provide easy SQL-based access to the information in the Unicode Han Database, version 6.0.0 (compatibility with Unihan 5.2.0 is still maintained). The application loads Unihan data from the original ZIP file into a database, and provides a simple SQL client to the database. Other SQL clients can of course be used if the database is persistent (rather than in-memory); the application does have the advantage of support for inserting and displaying characters by Unicode code point values.

The application can be useful for obtaining statistical information about Unihan contents (such as how many characters are listed for each radical-stroke count), checking the data for consistency, or programmatic access if a persistent database is created. For comprehensive information about single characters the Unihan Database Lookup is probably better.

The application was developed on Windows XP using JDK 1.6.0.16 and the Apache Derby 10.6.1.0 database.
Note: Java DB (Apache Derby) versions prior to 10.6.1.0 do not support NATURAL JOIN, which is used in the definitions of several views, so they should be upgraded.
SQLite has also been used for testing; the JDBC driver for it can be found here.
Icons are from the FatCow collection.

Running the Application

The application itself contains neither the Unihan.zip file nor a database driver. The former is available from the Unicode website here (version 6.0), and is not to be hosted elsewhere. A database driver may be optionally bundled with the application in later versions.

The application requires a JDBC-compliant driver to be locally installed and accessible to it. This usually means the relevant JAR file must reside in the lib\ext subfolder under the JRE (Java Runtime Environment) installation location; For Apache Derby, the file is derby.jar. Please refer to your database documentation for further information.

The application can be launched using the following link:

Launch JNLP file

Fill in an appropriate database URL and browse for the Unihan.zip file, then press Connect to start loading Unihan data into the database. If you have loaded the data into a persistent database, you can instead mark the Don't load Unihan checkbox to directly access the existing data. Either way, allow the connection to complete and press Continue to be able to send SQL queries. Note that you may also create additional tables, indices etc. as well as change loaded Unihan data (though this of course will not be reflected in the Unihan source files).

The database driver is expected to be inferred from the database URL. For instance, the application defaults to jdbc:derby:memory:unihan;create=true, which locates the Apache Derby database and instructs it to create an in-memory database. This takes about a minute, depending on the database in use and computer speed.

If the application doesn't run properly, please refer to the Troubleshooting notes below.

Database Schema

The exact definition of the tables and views created by the application is available in its menu through Info → DDL Statements. The following are general guidelines.

Each Unihan field has either a table or a view of the same name containing its data.
Views are used when the field data is split into several tables in order to normalize it. For instance, the kZVariant field contains a source indication only for some Z-variants, so sources are indicated in a separate table rather than insert multiple NULL values in a single table.
Some additional views, e.g. Numeric_any, unify data from multiple tables for easier access.
All tables providing data on individual characters contain the characters in a CODE_POINT column encoded in UTF-16, that is - a single character or a surrogate pair.
The columns CODE_POINT, RADICAL_INDEX, RADICAL_SIMPLIFIED and STROKES have the same name in many tables, in order to facilitate NATURAL JOIN/JOIN USING clauses. Other columns have the field name as their name or prefix.
Simple data fields such as kDefinition (String) are contained in columns with the same name as the field, as well as the table.
This is probably not considered good database design in general, but here it seems less confusing since field names are well known.
Compound data fields, such as radical-stroke indices, are split into several columns.
Columns with an _ord suffix indicate the ordinal position of the given record, which is sometimes significant; for instance, kMandarin pronunciations are ordered by frequency.
Numeric data is contained in numeric fields, whether the source is decimal or hexadecimal. Displaying the data in hexadecimal format depends on the database in use; for instance, in Derby the following external function can be defined:
```
CREATE FUNCTION HEX(I INT) RETURNS VARCHAR(8)
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.Integer.toHexString'
DETERMINISTIC NO SQL RETURNS NULL ON NULL INPUT
```
Ku/ten forms are stored as decimal integers as well. The following example shows how they can be converted into hexadecimal codes (based on this explanation):
```
SELECT CODE_POINT, 
HEX((kGB0/100 + 160)*256 + (MOD(kGB0, 100) + 160))
FROM kGB0
```
Some kIRG_USource entries map code points to themselves rather than to UTC indices. This is expected to change in Unicode 6.0, so in order to avoid complicating the table structure, such entries are ignored.
The application includes a copy of the CJKRadicals.txt file from the Unicode Character Database, which is loaded into the CJKRadicals table.
The HAN_BLOCK view lists Unicode blocks related to CJK characters (see below).
A UNIHAN_OBJECTS table is created listing all tables, views etc. created by the current version of the application, along with their type and creation order. This can be useful for generating SQL statements to manipulate all such database objects - see example below.
The SQL_STATEMENTS table from the previous version has been removed.
All tables are automatically set to table-locking mode during startup, which slightly improves performance when filling them with all Unihan data. The UNIHAN_OBJECTS table can be used to configure all tables back into row-locking mode.

Example Queries

Retrieve all radical-stroke indices from kRSUnicode

SELECT CODE_POINT, Radical, Strokes 
FROM kRSUnicode NATURAL JOIN CJKRadicals

Retrieve all radical-stroke indices from kRSKangXi (no simplified radicals)

SELECT CODE_POINT, Radical, Strokes 
FROM kRSKangXi NATURAL JOIN KangxiRadicals

Usage of HAN_BLOCK: retrieve any definitions given for Extension C characters

SELECT CODE_POINT, kDefinition FROM kDefinition
JOIN HAN_BLOCK ON CODE_POINT BETWEEN BLOCK_FIRST AND BLOCK_LAST
WHERE BLOCK_NAME = 'CJK Unified Ideographs Extension C'

Find all assymetric Z-variant relations

SELECT CODE_POINT, kZVariant 
FROM kZVariant Z1
WHERE NOT EXISTS (SELECT * FROM kZVariant Z2
		WHERE Z1.CODE_POINT = Z2.kZVariant
		AND Z1.kZVariant = Z2.CODE_POINT)

Find all characters which have only a J-Source (and thus are probably only used in Japan)

SELECT CODE_POINT FROM kIRG_JSource J
WHERE 'kIRG_JSource' = ALL(
	SELECT SOURCE FROM IRGSource_any
	WHERE CODE_POINT = J.CODE_POINT)

Generate statements to remove all database objects created by the application in the current database
A newer application version may change the DB schema, so this is sort of a cross-database way of storing the current table set.

SELECT 'DROP ' || TYPE || ' ' || NAME 
FROM UNIHAN_OBJECTS
ORDER BY CREATION_ORDER DESC

Changelog

Oct 16, 2010: version 0.20

Support for Unihan 6.0.0 and for all Unihan fields.
JDBC driver can be explicitly specified; support for SQLite.
Support for connecting to an existing database without loading Unihan data again, and for specifying a schema name.
Progress display for database setup and SQL results retrieval; SQL queries can be canceled.
DLL statements display improved.
Undo/redo functionality improved.

Sep 30, 2010: version 0.10

Troubleshooting

Some JDBC drivers cannot be inferred from the database URL and need to be loaded explicitly, by specifying the JDBC driver class name in the setup view: for instance, org.sqlite.JDBC (case-sensitive) loads the SQLite driver. Consult the database documentation to find the JDBC driver class if necessary.

If the JNLP file is opened as a plain text file rather than launch the application, the browser may need to be configured to open JNLP files using Java (TM) Web Start Launcher.
On Firefox, for instance, this can be done via Tools → Options → Applications tab.
Alternatively, the JNLP extension may need to be associated with the application/x-java-jnlp-file MIME type.

You may also download the JNLP file and run it locally, by typing javaws UnihanBrowser.jnlp on the command line. This will run the application from the web page.

Finally, you may download the JAR file itself and run it locally. The application may require a larger amount of memory than usual, especially when using an in-memory database. Therefore, you may want to execute java -Xmx512M -jar unihan.jar on the command line, to set the maximum Java heap size to 512Mb.
The -X options are non-standard, so you may need to execute java -X or java -help to find the memory setting option for your JRE implementation.