Query Editor Execution Order

In SQLite Navigator, when a query is executed a number of operations occur. First, the editor will run the provided SQL and return a dataset to the IDE. Second, if a Lua script was provided that will be run. The Lua is given access to the Dataset and gives the IDE the ability to post process a returned dataset in memory. Third, the if fluid templates are provided they will be run against the dataset to transform it into a transformed output.

Lua Example

In this example, we will loop over the returned records from a query and print the "name" column to the Lua Output tab.

-- Lua: Loop over results from query
for i = 0, results.Rows.Count - 1, 1
do
   print(results.Rows[i]["name"] .. "\r\n")
end

In this example, we will loop over the returned results from a query and change the value in the name column.

-- Lua: Loop over results from query
for i = 0, results.Rows.Count - 1, 1
do
   results.Rows[i]["name"] = "Redacted"
end

The Lua runs before the templates are generated so any changes to the record set by the Lua will be reflected in anything the fluid template engine outputs. Tip: You can use the Ctrl+, hot key to invoke autocomplete for Lua while in the Lua editor. This will allow you to see the top level namespaces providing you a convenient starting point.

Fluid Templates

As part of the query editor you can provide two different fluid templates. The first runs against the Dataset and allows you to transform the output row by row. This might assist you in creating a CSV file, a markdown file or any custom output. The second fluid template runs against the schema of the query that was returned. This is useful for when you want to create a model against the returned record set (that might be a class in C# or Java).

Fluid Examples

In this example we will assume you have a query that has two fields, name and age. It uses a for loop to loop over the rows and then create an HTML table with one HTML table row for every record set row.

{% comment %} Export the area's to an HTML table {% endcomment %}
<table>	
{%- for row in Rows %}
    <tr>
        <td>{{ row["name"] }}</td>
        <td>{{ row["age"] }}</td>
    </tr>
{%- endfor%}
</table>

In this example we will create a fluid template that generates a C# class from the fields returned in the query. The fluid template loops over the columns in and generates it's output based off of the meta data returned in the column object.

namespace ApexGate
{
    public class TestClass
    {
        {%- for column in columns %}
        /// <summary>
        /// {{ column.dbtype }}
        /// <summary>
        {%-if column.primaryKey %}
        [Key]
        {%- endif %}
        public {{ column.type }}{% if column.nullable %}?{% endif %} {{ column.name }} { get; set; }
        {% endfor %}
    }
}