API Reference | developer.brewmp.com API Reference | developer.brewmp.com

Developer

API Reference

ISQL

Brew Release
Brew MP 1.0.2
Description
The ISQL interface is deprecated. Refer to the dbc_IConnection interface instead.
Note: Thinking of the complete set of dbc interfaces as a replacement for the ISQL and IDatabase interfaces is more accurate than directly comparing the individual interfaces, whose functionality may not match exactly.
The ISQL interface functions allow you to execute SQL statements on an open database. The simplest approach is to call ISQL_Exec passing in the text of the SQL statements to execute and a pointer to a callback function that will receive any query results. Results are returned a row at a time with the value of each column in text format.
An alternative approach is to use ISQL_Prepare to compile the SQL text. This returns the compiled statements in the form of an ISQLStmt interface from which you can step through the SQL statements and get the results in various formats. This allows you to asynchronously process SQL and receive results. This approach also allows you to easily add or update records with non-text data such integers, floating point numbers, and binary blobs by binding data to specified columns.

Performance and Resource Usage

ISQL's internal SQL implementation (SQLite) is designed to provide the most durable embedded database system possible. As such, it uses a journal file to maintain database integrity across unexpected device powerdowns and application crashes. The journal file is used to restore the database file to its previous state after an unexpected exit.
While the journal file provides database durability, it introduces several caveats. The journal file is used to store pages from the database before an operation is committed. This requires both the existing data in the database to be written to the file system as well as the new data being comitted. These extra file system calls decrease ISQL's overal performace. Database designers should keep this in mind and store only what's necessay with their databases. For example, a content management database ought to be used to store DRM information, but not the actual protected content itself.
The use of the journal file also increases the file system resources ISQL requires to perform transactions. If the file system space is not available for the journal file, the SQL transaction will fail. For example, a transaction to delete an item from a database requires enough file system space for a journal file containing the original item. If the file system is full, ISQL will be unable to create this journal file and the operation will file. ISQL will be unable to remove items from its database file. See the section on configuring ISQL below to tell ISQL to recycle its journal file and essentially "reserve" file system space to avoid this potential problem.

Configuring ISQL

ISQL may be configured through it's BREW APIs or by executing "pragma" commands through ISQL_Exec(). The underlying SQL implementation recognizes this command and can be configured at runtime in various ways. The configuration established by the use of the pragma command is typically valid only for the lifetime of the ISQL object. Next time you open the database, the same pragma commands should be executed.
For example, to tell ISQL to reserve a particular amount of file system space (by reusing its journal file as described in the previous section), an application might execute the following SQL query:
      PRAGMA BREW_reserved_fs_bytes=5000

ISQL will interpret this query as a configuration command, and perform the necessary internal tweaks to recycle its journal file, keeping it at 5k to reserve file system space for the next SQL operation.
A subset of the pragma commands ISQL understands follows:

File System Space Reservation

The BREW_reserved_fs_bytes pragma tells ISQL to reserve the specified numnber of bytes in the file system by resizing the journal file to the requested size and saving it for a future operation. If there's a chance your application might fill up EFS space, it is recommended to use this pragma and reserve enough space to delete a single item from your database.
To reserve 5k in EFS for ISQL operations, execute the following pragma command:
      PRAGMA BREW_reserved_fs_bytes=5000;
Note that once this pragma has been set, the journal file will be recycled and reused for each and every transaction. The performance impact of this command is negligible.

Connection Cache Size

The cache size pragmas are used to control the number of database pages a session can hold in memory. This value affects how many operations can be cached befome the SQL implementation requires an exclusive lock on the database to commit them. If your application accesses a database that is simultaneously shared by other ISQL instances, increasing the cache size may reduce the occurences of SQL_BUSY errors at the expense of extra memory usage.
To set the cache size for a particular session, excecute the following pragma command:
      PRAGMA cache_size=10000;
To permanently set the cache size for all sessions, the following pragma command should be executed:
      PRAGMA default_cache_size=10000;
The default cache_size value used by ISQL is 2000 pages.

Getting Database Information

The following pragmas can be used to obtain information about the current database:
The database_list pragma provides a list of all attached databases.
      PRAGMA database_list;
The index_info pragma lists information about columns with an index in a particular table. This pragma takes an index name as an argument.
     CREATE INDEX my_index ON mytable(name, type_id);
     PRAGMA index_info(my_index);
The index_list pragma provides information about all indexes in a table. This pragma takes a table name as an argument.
      PRAGMA index_list(mytable);
The table_info pragma provides information about all columns in a table. This pragma takes a table name as an argument.
   PRAGMA table_info(mytable);

Debugging

The integrity_check pragma can be used to verify the integrity of a database file. When executed, ISQL looks for out-of-order records, missing pages and other corruption. A single string is returned listing the problems if any are found.
      PRAGMA integrity_check;

For a complete list of pragma commands ISQL understands, visit
      http://sqlite.org/pragma.html
Note that some commands listed there may not be applicable to the SQLite implementation in this version of ISQL.

Notes on Triggers


ISQL supports the use of triggers. Triggers are database operations that are automatically performed when a specified database event occurs. They are identified by name and specify a particular operation that is to be performed on a specific table in a database when a certain event occurs.
Triggers are set using the CREATE TRIGGER command and removed using the DROP TRIGGER command. There is no special BREW API support for them as none is needed.
Triggers in ISQL behave according to the following rules:
- A trigger created with the CREATE TRIGGER command is written to the
database file. It remains active until it is dropped using DROP TRIGGER. Triggers are automatically loaded when the database file is opened and thus outlive the ISQL instances used to create them.
- When an application creates a trigger through ISQL_Exec(), other instances
of ISQL in the same or other applications become aware of the trigger the next time they perform a read or write of the database. That is, when one instance of ISQL creates a trigger, other instances of ISQL recognize the database schema change when they next access it and automtically "learn" about the new trigger. This all happens under the hood.
- Triggers fire only once, and only in the context of the ISQL instance that
performed the trigger action. If, for example, application A adds a trigger, then application B causes that trigger to fire, the trigger action is performed in the context of application B only.
- Temporary triggers created with CREATE TEMP TRIGGER behave just like regular
triggers, except that they are dropped when the ISQL instance that created them is released. They are not written to the database and are dropped when the database connection is closed.
- As soon as a temporary trigger has been created from an ISQL instance, other
ISQL instances accessing the same database file automagically "learn" about the temporary trigger and can cause it to fire. Once the ISQL instance that created the temporary trigger is released, the trigger is immediately dropped from other ISQL instances as well. Note that triggers (both temporary and not) fire as a result of an UPDATE, INSERT or DELETE command. These commands can only be exectued when the ISQL instance has write access to the database. If there's another instance of ISQL reading the database at the time, the command fails and the trigger doesn't fire. So there's no need to worry about readers when exectuing a command that causes a trigger to fire.

Required header files

AEESQL.h