SQLite Table to PDF

The video below shows how to generate a PDF table from an SQLite database.

The database in this example consists of a single table with four columns of synthetic data. The last column contains a JSON array of three not real URLs.

This database was created with the following V program.

    // filename: users.v
    import vaker
    import rand
    import json
    import sqlite

    struct User {
        first_name             string    ['vaker:first_name']
        last_name              string    ['vaker:last_name']
        last_login             string    ['vaker:date']
        url_1              string    ['vaker:url']
        url_2              string    ['vaker:url']
        url_3              string    ['vaker:url']
    }

    struct Bookmarks {
        url_1 string
        url_2 string
        url_3 string

    }
    fn main() {
        user := User{}
        mut num :=0
        items := 300
        rand.seed([u32(3223878742), 1732001562])
        mut db := sqlite.connect('users_json.db')!
        mut query := ''
        mut code := 0
        db.create_table('users',['first_name','last_name','last_login','bookmarks'])

    for{
        if num < items {
            vaker.fake_data(&user)
            mut p := json.encode(Bookmarks{user.url_1,user.url_2,user.url_3})
            mut ll := ""
            if user.last_login > "2015-01-01"
                {ll = user.last_login}
            else
                {ll = ""}
            query = "insert into users(first_name,last_name,last_login,bookmarks) values ('${user.first_name}','${user.last_name}','${ll}','${p}')"
            code = db.exec_none(query)
            if code != 101{
            println(code.str()+": "+query)}
        }
        else{
            break
        }
        num++
    }
    db.close() or { false }
    }

The Vaker module, a synthetic data generator library for testing and development, should be installed prior compilation/run.

The database users_json.db binary file will be created in the same directory after the command:

   $ v.exe run users.v

The command-line program sqlite3.exe can execute SQL queries against the generated database and prints the results of a query in markdown format. As shown below, the .mode markdown command switches the output format to Markdown, whilst the json_extract function extracts and returns the first item from the JSON array stored in the last column of the table.

   $ sqlite3.exe users_json.db ".mode markdoqn" "select first_name,last_name,last_login, json_extract(bookmarks,'$.url_1') as URL from users where last_login!='' order by last_login limit 14 " | pandoc.exe -f markdown -o users.pdf --pdf-engine=typst.exe

Pandoc can read the standard input and then prints a PDF table from the result of the SQL-query. The pdf engine option is set to path\typst.exe.