19.0.0 released Nov 08, 2023
Report from SQL database, file writing


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, 68 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:

Vely

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 this to see the application response:
#Run report 
export CONTENT_TYPE=
export CONTENT_LENGTH=
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 the above:
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 
vv -r --app='/write-report' --req='/write-report?' --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.
Note: if running your program as a command-line utility is all you want, you don't need to run an application server.
Source files
The following are the source files in this application:
- 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.

#include "vely.h"

%% /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
    }

%%

See also
Examples
example-client-API  
example-cookies  
example-create-table  
example-develop-web-applications-in-C-programming-language  
example-distributed-servers  
example-docker  
example-encryption  
example-file-manager  
example-form  
example-hash-server  
example-hello-world  
example-how-to-design-application  
example-how-to-use-regex  
example-json  
example-multitenant-SaaS  
example-postgres-transactions  
examples  
example-sendmail  
example-shopping  
example-stock  
example-uploading-files  
example-using-mariadb-mysql  
example-using-trees-for-in-memory-queries  
example-utility  
example-write-report    
See all
documentation


You are free to copy, redistribute and adapt this web page (even commercially), as long as you give credit and provide a dofollow link back to this page - see full license at CC-BY-4.0. Copyright (c) 2019-2023 Dasoftver LLC. Vely and elephant logo are trademarks of Dasoftver LLC. The software and information on this web site are provided "AS IS" and without any warranties or guarantees of any kind. Icons from table-icons.io copyright Paweł Kuna, licensed under MIT license.