Work with SQLite

MOSAIC stores the output of an analysis in a SQLite database as described in the Database Structure and Query Syntax section. Interacting with the data through the Structured Query Language (SQL) is a flexible approach to further analyze or plot the output. Here we provide a few detailed examples of the common ways in which the output of MOSAIC can be queried for further processing. While this section is not a comprehensive SQL tutorial, it provides common use cases to allow you to get started.

One way to retrieve data from a SQLite_ database is to use the select command. In its simplest form, a select query can return the entire contents of a table using the syntax below. The statement below selects all columns (select *) from the table specified by <tablename>.

select * from <tablename>

The power of SQL lies in its ability to restrict results to match specific criteria. This is accomplished with the where clause described next. SQL queries can be very fast event for large databases. It is often desirable to only include events that were successfully fit in a plot or other analysis. All eventprocess-page algorithms implemented in MOSAIC store a ProcessingStatus column in the output database. This enables one to easily query events that were successfully processed. This is easily accomplished with the query below, which returns all columns for events that were successfully processed (ProcessingStatus=normal).

select * from metadata where ProcessingStatus='normal'

It is not always necessary to retrieve every column for events that fit a certain criteria. For example, gui-blockdepth-sec in the GUI displays a histogram of the blockade depths that match a user specified criteria. This is accomplished within the GUI by a query similar to the one shown below. There are two important differences between the query below and previous examples: i) by replacing * with BlockDepth, we only retrieve the BlockDepth column for events that meet the criteria specified after the where clause, and ii) selection criteria specified after where can be compound statements or even nested as seen in the examples below.

select BlockDepth from metadata where ProcessingStatus='normal' and ResTime > 0.2

select BlockDepth from metadata where ProcessingStatus='normal' and ResTime > 0.2
and BlockDepth between 0.1 and 0.5

Multiple columns can be retrieved from a table by providing a comma separated list of column names after the select clause. As in previous cases, only events that meet a specified criteria are returned. The results can be ordered using order. In this example we sort the results in ascending order by the AbsEventStart column.

select BlockDepth, ResTime, AbsEventStart from metadata where ProcessingStatus='normal'
order by AbsEventStart ASC

Finally, SQL allows the number of results returned to be limited using the limit clause. In this example, we limit the query results to the first 500 rows that meet our criteria.

select AbsEventStart from metadata where ProcessingStatus='normal'
order by AbsEventStart ASC limit 500