SQLite example: temperature utility

18.4.0 released Sep 25, 2023

In this example, a temperature measuring device will periodically insert temperatures into a database table, along with a timestamp. The purpose is to read this history and output the result from time to time, which can then be piped or sent elsewhere.

In a nutshell: SQLite; command line; Unix sockets; 2 source files, 25 lines of code.

Setup prerequisites

Install Vely - you can use standard packaging tools such as apt, dnf, pacman or zypper.

Because they are used in this example, you will need to install Apache as a web server and SQLite as a database.

After installing Vely, turn on syntax highlighting in vim if you're using it:

vv -m

Get the source code

The source code is a part of Vely installation. It is a good idea to create a separate source code directory for each application (and you can name it whatever you like). In this case, unpacking the source code will do that for you:

tar xvf $(vv -o)/examples/utility.tar.gz
cd utility

Setup application

The very first step is to create an application. The application will be named "utility", but you can name it anything (if you do that, change it everywhere). It's simple to do with vf:

sudo vf -i -u $(whoami) utility

This will create a new application home (which is "/var/lib/vv/utility") and do the application setup for you. Mostly that means create various subdirectories in the home folder, and assign them privileges. In this case only current user (or the result of "whoami" Linux command) will own those directories with 0700 privileges; it means a secure setup.

Setup the database

Before any coding, you need some place to store the information used by the application. First, create SQLite database "db_utility". You can change the database name, but remember to change it everywhere here. And then, create database objects in the database.

Execute the following to create database "utility.db" and also setup the database objects needed for the example:

sqlite3 /var/lib/vv/utility/app/utility.db < setup.sql

The SQLite database will be in the application home directory (see how-vely-works):

/var/lib/vv/utility/app

Connect Vely to a database

In order to let Vely know where your database is and how to log into it, you will create database-config-file named "db_utility". This name doesn't have to be "db_utility", rather it can be anything - this is the name used in actual database statements in source code (like run-query), so if you change it, make sure you change it everywhere. Create it:

echo '/var/lib/vv/utility/app/utility.db' > db_utility

The above in general is a location of SQLite database and is all that's needed to connect to it.

Build application

Use vv utility to make the application:

vv -q --db=sqlite:db_utility

Note usage of --db option to specify SQLite database and the database configuration file name.

Run program from command line

Execute the following to run your application from command line (as a command-line utility):

#Record a temperature to 91F
vv -r --app='/utility' --req='/temphist?action=record&temp=91' --method=GET --exec

#List recorded temperatures
vv -r --app='/utility' --req='/temphist?action=list' --method=GET --exec

You can also omit "--exec" option to output the bash code that's executed; you can then copy that code to your own script. Note: to suppress output of HTTP headers, add "--silent-header" option to the above.

Source files

The following are the source files in this application:
- Recording temperature readings in a table (setup.sql)
There is a "temp" column (temperature reading) and "timest" (time stamp of the reading). Table name is "temps" (temperatures).

create table temps (temp integer, timest text primary key);

- Utility for recording temperatures (temphist.vely)
This application will record temperatures, as well as list them. If task parameter "action" is "record", store the temperature into table "temps" and check for any errors. If it is "list", just display all the temperature records in a historical order.

#include "vely.h"

%% /temphist
    out-header default

    task-param action

    if-task "record"
        input-param temp
        run-query @db_utility = "insert into temps (temp, timest) values ('%s', current_timestamp)" : temp \
            affected-rows define rc error-text define er no-loop
        if (rc != 1) {
            @Could not insert temperature reading, error <<p-out er>>.
        } else {
            @Temperature reading stored.
        }
    else-task "list"
        run-query @db_utility = "select temp, timest from temps order by timest" output temp, timest
            @Date: <<query-result  timest>>
            @Temperature: <<query-result  temp>>
        end-query
    end-task
%%