Database topic map
If memory stored topic map turns out to be insufficient for your topic map, due to topic map size for example, you can use Wandora's database topic map implementation. Database topic map stores all data into an SQL database enabling essentially unlimited topic map sizes. However, using database storage option has implicit consequences
- Database topic map exists usually between Wandora use sessions and Wandora only connects to the database using provided username and password. Depending on the provided use rights the user may have only read rights to the database topic map.
- Usually database topic map initialization requires some external actions to be taken in order to configure the database for Wandora.
- Database topic map can be managed with database's own tools.
- Database may locate in a network computer. This means that Wandora uses a network connection (JDBC) to connect the database.
- Multiple users may use same database topic map simultaneusly. Wandora's database topic map implementation is not thread safe. You will encounter problems sooner or later if you allow multiple users to edit database topic map simultaneusly. However, database topic map can be successfully read by multiple users without problems.
- Database topic map is substantially slower than memory topic map. If database is not local but somewhere on the net, expect the database topic map to be even slower.
Creating Database Layer
To create new database topic map layer select Layers > New layer and select layer type Database. You also need to name and configure the database layer. By default Wandora contains configuration settings for example databases. Example databases are
- MS SQL Server test is an example database topic map configuration using Microsoft SQL Server database in domain 192.168.1.48 with database name, username, and password wandora. In order to use this configuration setting you need to initialize equivalent database.
- MySQL test is an example dabase topic map configuration using MySQL database in domain 127.0.0.1 with database name, username, and password wandora. In order to use this configuration setting you need to initialize equivalent database.
- HSQL Memory is a working example of database topic map configuration using HSQL database engine's memory database. This configuration works without extra actions. As the topic map database is stored into HSQL's memory, the database is lost when Wandora exits.
- HSQL testdb is an example database topic map configuration using HSQL database engine and file database in C:\wandora\testdb. In order to use this configuration setting you need to initialize the database.
If you have not configured the layer before you need to create a configuration for the layer. Conguration is created clicking New button in database layer selection window. This opens configuration window shown below
To define database configuration you have to select database type first. Current version of Wandora supports two specific and one generic database types. Specific types are for MySQL and Microsoft SQL Server. Generic database type allows you to create database layer with any JDBC capable SQL database.
Setting up MySQL or Microsoft SQL Server database configuration you need to enter
- Database configuration name.
- IP or domain name for the database server.
- Database's name.
- Name of valid database user.
- Password for the database user. Although Wandora suggests that you can enter database password later without remembering it, current Wandora version supports only remembered passwords. Wandora does not request password separately but layer creation fails if you have decided not to remember the password.
Wandora's software package does not contain JDBC library for Microsoft SQL Server. You need to download libraries separately from producer's own WWW site. See Download for specific download addresses.
Setting up generic database configuration you need to enter
- Database configuration name.
- Database JDBC driver class name.
- Connection string.
- Database user.
- Database user password.
- SQL clauses used to initialize the database.
After the initial database configuration the configuration settings are stored to Wandora's preferences and you can recall the configuration whenever you want to reconnect to an existing database topic map.
Initializing database tables for the topic map
Database connection can be established to an existing or a new database. If you are configuring fresh database you probably want to create tables for the database. Wandora does not contain tool to create fresh database. You have to use database's native tools. SQL files to create database are located in resources/conf/database folder. There are separate creation files for MySQL, Microsoft SQL Server, HSQL, and generic SQL database. Detailed database initialization steps are beyond the scope of this chapter but in case of MySQL the steps are
Log in the MySQL server with
mysql --user=<user_name> --password=<password>
Create empty database for the topic map layer
create database mytopicmap;
Import database creation clauses from db_mysql.sql
Set database user name and password
grant all on mytopicmap.* to "username"@"%" identified by "password";
And the database is ready to be used as a layer in Wandora. Wandora database contains 8 different database tables. Below is an illustration of database structure. Image is also available in EPS and DIA formats within the Wandora application package in resources/conf/database folder.
Exporting topic map as SQL
Wandora has a special export feature used to convert topic maps directly to SQL clauses suitable for Wandora's database topic map model. This feature can speed up the database topic map creation time significantly. See SQL Dump Export for more details about exporting any topic map to SQL clauses.
Restoring database topic map
It may be more convenient to handle SQL dumps instead of XTM exports when using database topic maps. Wandora features a SQL topic map import... tool used to restore existing SQL database dump. Of cource you can import SQL dump directly to database but sometimes it is difficult to access the database directly and optional method is required. To restore existing database dump you need empty database and wandora connected to the database. Select the database topic map layer and choose File > Import > SQL topic map import.... Tool requests the database dump file. Note that SQL topic map import... requires a valid database topic map dump. Invalid SQL dump makes Wandora easily unstable.
Note also that Wandora features Database extractor tool used to transform any SQL database into a topic map. Do not mix up SQL topic map import... and Database extractor. First is used to restore Wandora topic map database dumps as the latter is used to transform SQL databases to topic maps.