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:
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:
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
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.
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 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;
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.
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):
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:
See vf for more options to help you achieve best performance.
If you want to stop your application 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:
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):
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.
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/>
write-string define outmsg
@Employee report<hr/>
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
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-app directory to define home_dir
write-string define report_dir
@<<p-out home_dir>>/reports
end-write-string
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
}
write-string define report_file
@<<p-out home_dir>>/reports/report_<<pf-out "%lld", pid>><<p-out tstamp>>
end-write-string
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 {
@Report file [<<p-out report_file>>] written.<br/>
@Report file content:<br/>
p-out outmsg
}
%%
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.