|
How to Work with a Local Database |
  
|
How to Work with a Local Database
|
How to Work with a Local Database |
|
Overview
The local database is SQLite-based and located in the device. It includes one or more tables which provide a storage space for application data but, also, allow for complex data structures like joined tables, etc. An MCL-Designer V4 project can ONLY include one local database with multiple tables and it is managed in the "Local Database"module. See Navigating the Local Database Module.
The data stored in the local database are managed and manipulated via SQL transactions.
If required, the local database can be encrypted.
MCL-Designer V4 provides you with processes ("Insert Record", "Delete Record", "Free SQL Statement",etc.) that handle the table(s) included in the local database.
See Working with the Local Database Processes Group.
The data within the local database can be indexed (see Working with Indexes) and its display can be structured with the help of views (see Working with Views).
The data included in the table(s) of a local database are displayed in the device's screen with the use of certain controls (Combo Box, Input List, Advanced List Box and Grid).
The maximum number of displayed records is 1000.
It is possible to import an external database into the "Local Database" module and use it as a local database or create a local database using an SQL script(see To Import a Database). Either operation will replace any existing table, view and/or index that was previously created - it will not be possible to recover them.
Each MCL-Designer V4 project includes one default local database with certain properties you can set up according to your needs such as name, location within the device, etc.
The setup options are available in the local database's properties window which can be accessed in the "Local Database" module:
Step-by-step
1. Open the "Local Database" module.
2. Right-click the alias of the local database.
3. Select "Local Database Options".
4. Fill in the available options.
Local Database |
|
Alias |
The local database's alias. Maintain the default OR enter the alias you want to use. This alias will be displayed in the "Local Database" module. |
DB Name |
The local database's default name is "Project.db" but you can change it. Enter the new name in the corresponding box.
Do NOT use special characters and make sure the extension is still ".db" after the edition. |
Notes |
If necessary, add relevant information about the local database. |
Encryption |
|
Check this option to ensure the local database is encrypted. |
|
Method |
ONLY active if the "Encryption" option is checked.
Define the type of key required for the local database's encryption: <Device Key> - This key is generated and provided by the MCL-Client installed in the target device. As a result, the local database can only be read/used within that target device. If you download the file that represents the database from the device to an external environment such as a PC, you will not be able to read/use that data because you will NOT have access to the device's encryption key required for the data decryption. <Text Key> - This key consists of a string with multiple characters (ex: a passphrase). <Key> - This key consists of a binary value in a 32 bytes hexadecimal representation.
Be aware that the key file that is generated can ONLY be applied to the current project's resulting application - it can be used regardless of the application's version.
SQLite recommends the use of Text Keys for SQL database encryption.
|
Key Name |
ONLY active if the selected method is "<Text Key>" or "<Key>".
Enter the name that will represent the encryption key ("<Text Key>" or "<Key>") to be applied to the MCL application. |
Options |
|
Location |
Define the location for the local database in the target device:
Default - Refers to the default storage disc space for files. The local database will ONLY be accessible to the MCL application (and any background procedures). Temp (Non-Persistent) - The application data that are stored in the local database tables are lost every time the RAM is cleaned up. Shared - The internal storage disc space for files/local database tables that can also be shared with other MCL applications. External Storage - External storage disc space for files/local database tables that can ONLY be accessed by the MCL application. External Shared - External storage disc space for files/local database tables to be shared across MCL applications.
•The target device. For instance, if the target device does not have a RAM partition (or an equivalent partition such as "cache disk"), the location “Temp (Non Persistent)” is NOT an option (the device will end up storing the local database files into the default folder for data files (<DATA> alias).
•The Local Database's objective. If the tables' content is to be managed solely by the MCL application (and its background procedures), we recommend the “Default” location. But, if you intend the data to be shared with other MCL applications, select the “Shared” location for your local database.
Be aware that the described storage behavior may not apply to older Android OS versions.
|
Include in PDK |
This option is NOT active if the local database is encrypted (= checked "Encryption" option).
Check this option to ensure that the local database's structure is included in the application's PDK. |
Create Database Schema at Startup if necessary |
Checking this option means that a file with the local database's structure is generated and provided to the MCL application when it is launched. This does NOT occur with every MCL application startup - the local database's schema is ONLY generated/provided at startup if it was not created in a previous application launch.
|
Attach Database at Startup |
Check this option to ensure that the Local Database is connected to the MCL application when it is launched.
|
Local Database Encryption Recommendations:
•If selecting the "<Device Key>" method (encryption key generated within the device by MCL-Client), check the "Create Database Schema at Startup if necessary" + "Attach Database at Startup" options to ensure the MCL application retrieves the database's structure and is connected to it when it is launched.
•If selecting "<Text Key>" or "<Key>" (encryption key is provided via a key file by MCL Support) and checking the "Create Database Schema at Startup if necessary" option, make sure the key file is downloaded onto the target device before launching the MCL app.
•If the MCL application is meant to use an external database, we recommend NOT checking the "Create Database Schema at Startup if necessary" OR the "Attach Database at Startup" options:
▪Since the database is managed externally, MCL-Designer will not be able to create the corresponding schema.
▪The external database file may not necessarily be present before the MCL application is launched.
If using an external database (= created outside MCL-Designer) the encryption algorithm MUST be "AE256-OFB".
Be aware that if a table of your local database includes fields with "Null" value, those will be considered/displayed by the controls that have the local database as its data source as being empty fields (= "" value).
An MCL-Designer project allows you to use a Local Database. The project includes a default local database (a "*.db" file called "project.db") that you can edit as needed (rename the database, create/ delete/edit tables, views, indexes).
However, you can also import an external database ("*.db") OR generate a database using an imported SQL Script ("*.sql" or "*.dump" files).
Be aware that the import of an external database into the project will result in the deleting of any existing tables, views and/or indexes - these pre-existing elements will be replaced with the tables, views and indexes of the imported database.
This operation CANNOT be reverted.
Encrypted databases CANNOT be imported into the MCL-Designer project.
Step-by-step
1. Open the "Local Database" module.
2. Right-click the name of the local database (default name - "project.db").
3. Select "Import Database".
4. In the "Mode" drop-down, define the source type for the database import.
Database File (.db) - Refers to a "*.db" file as the source of the database to import.
Database Script File (.sql, .dump) - Refers to an "*.sql" or "*.dump" file as the source of the database to import.
5. In the "File" option, click 
to continue. In the ensuing OS browse window, browse for/select the required database file.
After the import, the "Local Database" module will display the tables, views and indexes of the imported database.
The default name is kept - "project.db" - but you can change it. See Local Database Setup.
Topics
This chapter covers the following topics:
