Vely logo install | documentation | examples | changelog
16.10.0 released May 10, 2023
Example: create table

Example of manipulating tables via SQL. Table is

In a nutshell: PostgreSQL; command line; web browser; Nginx; Unix sockets; 2 source files, 44 lines of code.
Screenshots of application
The web output from creating a table, inserting data, selecting and dropping it:


The same output, only when the program runs from the command line. Set VV_SILENT_HEADER to yes to skip the header output:


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 PostgreSQL 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/create_table.tar.gz
cd create_table

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

This will create a new application home (which is "/var/lib/vv/create_table") 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 PostgreSQL database "db_create_table". You can change the database name, but remember to change it everywhere here. And then, you will create database objects in the database.

Note the example here uses peer-authentication, which is the default on all modern PostgreSQL installations - this means the database user name is the same as the Operating System user name.

Execute the following in PostgreSQL database as root (using psql utility):
echo "create user $(whoami);
create database db_create_table with owner=$(whoami);
grant all on database db_create_table to $(whoami);
" | sudo -u postgres psql

Next, login to database db_create_table and create the database objects for the application:
psql -d db_create_table -f setup.sql

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_create_table". This name doesn't have to be "db_create_table", 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 "user=$(whoami) dbname=db_create_table"  > db_create_table

The above is a standard postgres connection string that describes the login to the database you created. Since Vely uses native PostgreSQL connectivity, you can specify any connection string that your database lets you.
Build application
Use vv utility to make the application:
vv -q --db=postgres:db_create_table

Note usage of --db option to specify PostgreSQL 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 create_table

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 create_table

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

If you want to stop your application server:
vf -m quit create_table

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 ("/create_table" is the application path (see request_URL) and "create_table" is your application name):
location /create_table { include /etc/nginx/fastcgi_params; fastcgi_pass  unix:///var/lib/vv/create_table/sock/sock; }

- Step 2:
Finally, restart Nginx:
sudo systemctl restart nginx

Note: you must not have any other URL resource that starts with "/create_table" (such as for example "/create_table.html" or "/create_table_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 "/create_table", 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 if different.
#Create, use and drop table, show output

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:
#Create, use and drop table, show output 
export SCRIPT_NAME='/create_table'
export PATH_INFO='/create_table'
export QUERY_STRING=''
cgi-fcgi -connect /var/lib/vv/create_table/sock/sock /

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

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):
#Create, use and drop table, show output 
export SCRIPT_NAME='/create_table'
export PATH_INFO='/create_table'
export QUERY_STRING=''

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

Note: if running your program as a command-line utility is all you want, you don't need to run an application server.
You are now done with the example! What follows are the source files in this project so you can examine how it works:
Setup database objects (setup.sql)
Create a table used in the example:
create table if not exists my_table (col1 bigint);

Creating, using and dropping a table (create_table.vely)
DDL (Data Definition Language), DML (Data Definition Language) and SELECT SQL is used in this example. A table is dropped (if it exists), created, data inserted, queried, and then table is dropped again:
// SPDX-License-Identifier: EPL-2.0
// Copyright 2018 DaSoftver LLC.

#include "vely.h"

void create_table()

    out-header default

    // Drop existing table
    run-query @db_create_table = "drop table if exists my_table"  error define err error-text define err_text no-loop
    if (strcmp (err, "0")) {
        report-error "Trouble dropping table, error [%s], error text [%s]", err, err_text
    @Table dropped!<br/>

    // Create table
    run-query @db_create_table =  "create table if not exists my_table (my_number bigint)"  error err error-text err_text no-loop
    if (strcmp (err, "0")) {
        report-error "Trouble creating table, error [%s], error text [%s]", err, err_text
    @Table created!<br/>

    // Insert data into table
    run-query @db_create_table="insert into my_table (my_number) values ('%s'), ('%s'), ('%s')" : "100", "200", "400" affected-rows define nrows
    @Added <<pf-out "%lld", nrows>> rows!<br/>

    // Select data we just inserted
    @Getting data we inserted:<br/>
    run-query @db_create_table="select my_number from my_table" output my_number
        @I got number <<query-result my_number>>!<br/>

    // Drop the table again
    run-query @db_create_table =  "drop table if exists my_table" error err error-text err_text no-loop
    if (strcmp (err, "0")) {
        report-error "Trouble creating table, error [%s], error text [%s]", err, err_text
    @Table dropped!<br/>


See also
Examples ( example_cookies   example_create_table   example_docker   example_file_manager   example_form   example_hash   example_hello_world   example_json   example_multitenant_SaaS   examples   example_sendmail   example_shopping   example_stock   example_utility   example_write_report  )  SEE ALL (documentation)

Copyright (c) 2017-2023 Dasoftver LLC