The Core of Mimer SQL Mobile 9.3
With Mimer SQL Mobile, enterprise class database functionality like full SQL, non-locking multi user access, and multilingual support is introduced to resource constrained mobile devices. Now Mimer SQL is extended with several new features, some very useful for developers of mobile applications.
Mimer SQL 9.3 brings the mobile database concept to a higher level. With features like automatic upgrades, dynamic memory utilization, controlled file limits, and transparent handling of data on removable flash memory cards, this version of Mimer SQL meets the needs identified in mobile device development.
Earlier when databases should be updated on remote mobile devices, a quite complex upgrade application was needed, and developed, by the application system supplier. This application should not only be aware of the complete system structure, but also be able to handle interruptions and partial upgrades. All these difficulties are now solved by the new Mimer SQL automatic upgrade feature.
When automatic upgrade is enabled, the Mimer SQL database system keeps track of changes to database objects. This allows an upgraded version of the system databank to be distributed to existing installations, where the upgrade will be applied automatically. The upgrade works transparently over a series of revisions, for example, a customer can go to the latest version independent of the current upgrade level.
As mentioned, this is especially useful when upgrading applications and data located on mobile devices - one single file is replaced on the device. When the database is restarted, all changes to the database, including new tables, views, indexes, SQL statements, procedures and triggers, are automatically applied. Upgrades may, for example, add new columns to a table without loosing any data.
When a table is found missing in a database with the AUTOUPGRADE attribute set, the table will automatically be created. The table will initially be empty. In addition, if a databank is missing the databank file is automatically created.
The following commands are used to control AUTOUPGRADE:
ALTER DATABASE SET AUTOUPGRADE;
ALTER DATABASE DROP AUTOUPGRADE;
Please note that dropping the AUTOUPGRADE option means that the upgrade chain is broken and that all older modifications made to the database will be dropped.
The process when using automatic upgrade is as follows:
- When the first version of the database is ready to launch, the AUTOUPGRADE option is set for the database. This option must be enabled continuously from now on for the upgrade functionality to work properly. The database (i.e. the system databank and associated databank files) is copied from the development environment to the production system.
- Continued development and maintenance must be carried out on the development database system that was previously launched, using the original system databank. As the AUTOUPGRADE attribute has been set, the modifications made will be recorded and saved by Mimer SQL internally.
- When the set of changes are complete, the updated system databank is copied to the production system. (Note that only the system databank, SYSDB, is copied.) The upgrade is now performed in the production system automatically when tables having an old definition are accessed.
The auto-upgrade functionality can handle interruptions of the upgrade process, and it can also deal with a situation where old structures, for example from an external flash, are used at a later stage.
Dynamic database cache
With the new dynamic database cache, the Mimer SQL database manager dynamically allocates main memory when intensive database operations are made.
The expansion is limited to a level decided by a startup parameter for the database server. The actual size of the database cache is decided dynamically at run-time dependent on the workload and available memory.
When the memory is needed by other applications, Mimer SQL automatically releases the memory.
Controlled databank file size
In order to keep resource usage controlled within predefined bounds, the possibility to set limits on databank files has been introduced. In addition, with this functionality you can guarantee that a certain amount of data can be stored even if there is no disk space available when the INSERT is made.
When creating a databank it is possible to specify that the size of the physical file should be within certain limits. This can be achieved by using MINSIZE, GOALSIZE and MAXSIZE like in the following example:
CREATE DATABANK mimer_store SET MINSIZE 10 M, GOALSIZE 20 M, MAXSIZE 30 M;
This means that the file size will be between 10 and 30 mega bytes and that the system will try to keep the size as close to 20 mega bytes as possible. The size can be specified using the suffixes K, M or G, which stands for Kilo bytes, Mega bytes and Giga bytes, respectively.
These limits can also be specified in an alter databank statement:
ALTER DATABANK mimer_store SET MAXSIZE 2 G;
In addition, there is an option for dropping size limitations for a databank:
ALTER DATABANK mimer_store DROP MAXSIZE;
Another new file handling feature is the possibility to set the actual size for a file, using the SET FILESIZE command:
ALTER DATABANK mimer_store SET FILESIZE 20 K;
If the given file size is less than the currently allocated size, the system will release unused file blocks in order to shrink the file and if the file size is larger than the current size, new blocks will be allocated.
By dropping the FILESIZE for a databank, as shown below, the databank is truncated. This means that the file will be made as small as possible but not smaller than MINSIZE, if specified.
ALTER DATABANK mimer_store DROP FILESIZE;
Removable databank files
A new feature is the transparent handling of databank files on removable devices. This is, for example, useful when databanks are stored on flash memory cards.
It is also possible to automatically create new tables on the flash memory card when it is inserted in the mobile phone.
A useful construction using the removable databank feature is to create a union view spanning two tables, one on the internal and one on the external, removable, flash. The application then retrieves data transparently, and when the external flash is present all data is seen, as shown in the following figure.
This means, for example, that music tunes stored in a databank on a removable flash card can be appended to the existing music tracks on the phone without any need for changes to the music player application. No special coding is needed in the application. It simply executes the SELECT statement and all other preparations are handled automatically by the database server.
Read only databanks
In Mimer SQL 9.3 it is possible to specify that a databank should be read-only. This means that the databank can be placed on read-only media, such as a CD or on a read-only memory (ROM) card. In addition, performance is gained. For a read-only databank, transactions can be performed more efficiently than for a read-write databank.
For data that is updated rarely, it is possible to store it read-only in between the updates. For example, if a price list is updated once a month, the prices could be held read-only in between the revisions of the price list.
A databank is set READ ONLY with the ALTER DATABANK statement:
ALTER DATABANK mimer_store SET OPTION READ ONLY;
Customized footprint and functionality
Functionality and footprint of Mimer SQL Mobile can be customized for manufacturers of mobile devices and embedded appliances that use resource constrained hardware platforms. Mimer works closely with developers of mobile solutions, which has resulted in a mobile aware architecture.
Below are some real-life examples on components, configurations and platforms that Mimer SQL Mobile has been adapted to.
Executable <300 KB
Stack ≈5 KB
Heap ≈60 KB
Cache (min) ≈90 KB
C/C++ Micro API
ADO.NET for Compact Framework
ISO SQL Collations
Updated Database Access and Query Tool
DbVisualizer is the database access and query tool bundled with Mimer SQL. With Mimer SQL 9.3 the new version DbVisualizer 5.1 is provided. In this version the Mimer driver is preloaded which means that new users do not have to locate the Mimer JDBC jar files manually.
The following screen shot shows the new version displaying the code for one of the functions defined in the example database:
The new 5.1 version contains numerous enhancements for constructing SQL statements for all different Mimer SQL commands. For example, to create a new sequence, right-click on the "Sequence" object type which opens a sub menu where a new sequence easily can be created:
When selecting "Create Sequence" the following dialog will appear where the values for the new sequence can be entered directly:
Pressing the Execute button will then create the sequence.
For further details, please see the Mimer SQL 9 home page.
You can download Mimer SQL 9 from the download page.
Documentation is provided with the downloaded products, but can also be found at the documentation page.
Last updated: 2007-06-21