vely:Example: write report

Example: write report

Builds a report of employees from a database:

Creates a "reports" directory and a file name with a time stamp, then
writes the report to a file and also,
displays the report to a web page.

In a nutshell: MariaDB; command line; web browser; Nginx; Unix sockets; 2 source files, 69 lines of code.

Screenshots of application

Database report is saved into a file (the path to which is displayed) and the report itself is shown in the web page:

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 Nginx as a web server and MariaDB 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/write_report.tar.gz
cd write_report

Setup application

The very first step is to create an application. The application will be named "write_report", 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) write_report

This will create a new application home (which is "/var/lib/vv/write_report") 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, you will create MariaDB database "db_write_report" owned by user "vely" with password "your_password". You can change any of these names, but remember to change them everywhere here. And then, you will create database objects in the database.

Execute the following logged in as root in mysql utility:

--Create velydb database hosting application data (if it doesn't exist):
create database if not exists db_write_report;
create user if not exists vely identified by 'your_password';
grant create,alter,drop,select,insert,delete,update on db_write_report.* to vely;
-- Create database objects needed for the application (eg. tables, indexes):
use db_write_report;
source setup.sql;
exit

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_write_report". This name doesn't have to be "db_write_report", 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 '[client]
user=vely
password=your_password
database=db_write_report
protocol=TCP
host=127.0.0.1
port=3306' > db_write_report

The above is a standard mariadb client options file. Vely uses native MariaDB database connectivity, so you can specify any options that a given database lets you.

Build application

Use vv utility to make the application:

vv -q --db=mariadb:db_write_report

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

Start your application server

To start the application server for your web application use vf FastCGI process manager. The application server will use a Unix socket to communicate with the web server (i.e. a reverse-proxy):

vf -w 3 write_report

This will start 3 daemon processes to serve the incoming requests. You can also start an adaptive server that will increase the number of processes to serve more requests, and gradually reduce the number of processes when they're not needed:

vf write_report

See vf for more options to help you achieve best performance.

If you want to stop your application server:

vf -m quit write_report

Setup web server

This shows how to connect your application listening on a Unix socket (started with vf) to Nginx web server.

- Step 1:
You will need to edit the Nginx configuration file. For Ubuntu and similar:

sudo vi /etc/nginx/sites-enabled/default

while on Fedora and other systems it might be at:

sudo vi /etc/nginx/nginx.conf

Add the following in the "server {}" section ("/write_report" is the application path (see request_URL) and "write_report" is your application name):

location /write_report { include /etc/nginx/fastcgi_params; fastcgi_pass  unix:///var/lib/vv/write_report/sock/sock; }

- Step 2:
Finally, restart Nginx:

sudo systemctl restart nginx

Note: you must not have any other URL resource that starts with "/write_report" (such as for example "/write_report.html" or "/write_report_something" etc.) as the web server will attempt to pass them as a reverse proxy request, and they will likely not work. If you need to, you can change the application path to be different from "/write_report", see request_URL.

Access application server from the browser

Use the following URL(s) to access your application server from a client like browser (see request_URL). Use actual IP or web address instead of 127.0.0.1 if different.

#Run report 
http://127.0.0.1/write_report/write_report

Note: if your server is on the Internet and it has a firewall, you may need to allow HTTP traffic - see ufw, firewall-cmd etc.

Access application server from command line

To access your application server from command line (instead through web browser/web server), use "cgi-fcgi" to see the application response:

#Run report 
export REQUEST_METHOD=GET
export SCRIPT_NAME='/write_report'
export PATH_INFO='/write_report'
export QUERY_STRING=''
cgi-fcgi -connect /var/lib/vv/write_report/sock/sock /

Note: to suppress output of HTTP headers, add this before running "cgi-fcgi":

export VV_SILENT_HEADER=yes

If you need to, you can also run your application as a CGI program.

Run program from command line

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

#Run report 
export REQUEST_METHOD=GET
export SCRIPT_NAME='/write_report'
export PATH_INFO='/write_report'
export QUERY_STRING=''
/var/lib/vv/bld/write_report/write_report

Note: to suppress output of HTTP headers, add this before running /var/lib/vv/bld/write_report/write_report program:

export VV_SILENT_HEADER=yes

Note: if running your program as a command-line utility is all you want, you don't need to run an application server.

Files

You are now done with the example! What follows are the source files in this project so you can examine how it works:

Database setup (setup.sql)

For this example, table "employees" is created if it doesn't exist (all its data deleted if it does), and it is populated with some data:

create table if not exists employees (name varchar(50), salary int);
delete from employees;
insert into employees (name, salary) values ('Mike', 50000), ('Joan', 60000), ('Kelly', 70000);

Database report (write_report.vely)

A result set of a query is stored in a string using write-string with run-query within in. write-string simply redirects any output (that would normally go to the client) into a string. This output is then written to a file using write-file and also sent back to the client.

A few other things are demonstrated here, such as get-req to obtain the database vendor (i.e. which database is used, as any number of databases can be, such as MariaDB/MySQL, PostgreSQL, SQLite). Also getting current time (get-time) is shown to produce a timestamp for the file name, and match-regex to replace spaces with underscores.

// SPDX-License-Identifier: EPL-2.0
// Copyright 2018 DaSoftver LLC.

#include "vely.h"

void write_report()
{

    out-header default

    get-app db-vendor db_write_report to define dbv
    @Database vendor used is <<p-out dbv>><br/>

    // Build report for a query and store it into variable outmsg

    write-string define outmsg
    @Employee report<hr/>

    // Get data from the database
    run-query @db_write_report="select name, salary from employees order by name" output name, salary
       @  -----<br/>
       @  Name: <<query-result name>>
       @  Salary: <<query-result salary>><br/>
    end-query
    @-----<br/>

    @End report.

    end-write-string


    // build time stamp for report file
    get-time to define curr_time format "%A %B %d %Y %l %M %p %Z"
    get-req process-id to define pid
    match-regex " " in curr_time replace-with "_" result define tstamp

    // get application home directory and build the path for reports directory
    get-app directory to define home_dir
    write-string define report_dir
    @<<p-out home_dir>>/reports
    end-write-string

    // make directory, ignore error if it exists
    exec-program "mkdir" args "-p", report_dir status define st
    if (st != VV_OKAY  && st != VV_ERR_EXIST) {
        @Error in creating reports directory
        exit-request
    }

    // build the name of report file
    write-string define report_file
    @<<p-out home_dir>>/reports/report_<<pf-out "%lld", pid>><<p-out tstamp>>
    end-write-string

    // write report file

    write-file report_file from outmsg status st
    if (st<0) {
        @Error in writing report file <<p-out report_file>> (<<pf-out "%lld", st>>)
        exit-request
    } else {
        // if okay, display report file to the web
        @Report file [<<p-out report_file>>] written.<br/>
        @Report file content:<br/>
        p-out outmsg
    }

}