# Database
OI provides the ability to store persistent data by using a built-in database.
# Backend software
OI utilizes SQLite as the backend database system. SQLite stores data in standalone files which can be copied and opened in a variety of programs.
Note
OI Versions prior to April 2016 used Postgresql as the backend database system. Postgresql and SQLite were both supported from April 2016 to September 2017.
While Postgresql provided an extraordinarily powerful database system, the advantages of Postgresql were not well utilized in OI application use cases. SQLite proved to be a faster, simpler solution with few drawbacks.
Legacy OI applications built during Postgresql should be compatible with SQLite. The exception would be any custom SQL queries that may need to be modified for SQLite compatibility.
# Files
The database files are stored in /tmp/db/sqlite. This folder is a symlink to either the hard drive storage location /media/hdd/db/sqlite or a location in ram memory /var/volatile/tmp/db/sqlite. These files can be copied out using FTP.
# DB Connection (fw 1.11)
Database connections are set in the Tools: Database Sources dialog in OI Builder. This tool was created to allow more complex database interactions.
Database Table Components do not use the Database Sources tools and instead use the DB Connection property settings.
# DB Connection (fw 1.10 and older)
Each database component allows setting values for DB Connection. The only value that can effectively be changed is the databaseName value. Other properties are provided for internal system and for backward compatibility.
The databaseName value specifies the file name that the database will be stored in. This allows a program to run multiple databases at the same time and can be useful for recording multiple sets of data while maintaining the same table names and column names.
WARNING
Queries cannot access data from multiple databases. The query will only work with the database specified in the databaseName for the component.
# dataSource
In Firmware 1.11 and newer, the dataSource field refers to one of the sources available in the Database Sources dialog.
For Firmwares 1.10 and older, the dataSource field usually accepts either a table name or a full query. Using a query may limit some of the built-in functionality of components such as the Database Table component's editing ability.
# Database Sources
Starting with OI 1.11, the tools for using the database have been restructured to use the Database Sources utility. Here you can manage database sources which provide read and write capabilities to components. Starting with OI 1.11, you can use CSV files to transfer data with applications.
Creating a Data Sources will also create a tab in the Tag editor where you can create tags for the specific data source.
# Components
The following components utilize Database Sources:
- DBWrite
- DBRead
- DBEdit
- DBTable
- DBImageCapture
- DBImageViewer
DBTable does not utilize the Database Sources utility because it uses a separate thread for it's database communication. A table loading lots of data or running a complex query will not block the main thread.
# Database name
This names the file that will be used for storing the database. Sources cannot access data from multiple databases.
# Data Source: Table
This source is needed for components that will write to the database.
# Persist Data
If true, then OI will try to use the existing table during startup. CSV data will not be loaded if the table already exists.
If false, OI will create a new table and erase any previous data. CSV data will be loaded each time the application is re-loaded (simply going to config screen then back to run will not reload data).
# Data CSV Utilities
Data can be loaded from a CSV file on startup. We provide a tool to recreate this file based on columns defined in the table definition.
# Update Data CSV
Triggering this saves the current table to a CSV file that is associated with the OI program. This will be saved on the Data card and transferred with the program. Use cases are for transferring and backing up data such as recipes.
The status tag is true when the OIB data and the production database match.
# Reset Data CSV
Triggering this drops the table, then recreates the table with data from the CSV file if it exists. This provides an ability to restore some default values or to clear out all entries from a table.
The status tag is true when reset is not running.
# Table Definition
On startup, these columns are created with the table. These columns are also used with the Recreate File feature.
Note
If there is a mismatch between the table definition here and with a DBWrite component's tag list, any missing columns are added to the table.
# Data Source: SQL Select
This source executes the SQL query to provide data for other DB components.
# Data Source: SQL Command
An SQL command allows running arbitrary SQL code such as creating tables, deleting tables, custom updates, creating triggers, and creating views. An SQL command cannot be used as a datasource for a component. The only way for these commands to be executed is either through the "execute on startup" checkbox or through the triggerTag.
# Tag Injection
Tags can be injected into the dataSourceSQL or an SQL Select Data Source by pre-pending a dollar sign to the tag name. The Database Image Viewer component uses this feature to specify which image should be displayed from the results. This can also be used to search. For example, we could have a database that has a column "searchField", and a tag named "searchText".
Then we could perform a search:
select * from db_example where searchField like '%$searchTag%'
Note
OI's SQLite system is configured with case-insensitive LIKE.
# Script Access
Databases can be accessed within scripts by locating the database file and opening using any SQLite adapter. Read more about scripting here.