Overview

Picoforms's database storing forms and answers, as well as managing user access to data. It is a root component of the multi-program and multi-user system.

A scheme of components

Above is a diagram of a complete Picoforms installation and its participants. Every of its components is:

RDBMS (Relational Database Management System)

This repository.

Form web server

Internet-facing application serving forms in a nice HTML & CSS UI to clients. Accepts client answers and transfers them to the database.

User

An answer viewer or form editor.

Client

A form visitor and responder.

Installation

DBMS in use is PostgreSQL 18¹ without any extra dependencies or extensions.

1. Clone source code repository via Git into current directory.

   sourcedir="picoforms-database"
   git clone https://codeberg.org/picoforms/database "$sourcedir" && cd "$sourcedir"
   

2. Install, set up and start PostgreSQL.

   Concrete example for Alpine Linux:

   apk add postgresql
   rc-service postgresql start
   

3. Connect as superuser. Create the database.

   dbname="picoforms"
   createdb "$dbname"
   

4. Connect as superuser to the database. Enter SQL commands from roles.sql, then schema.sql.

   cat roles.sql schema.sql | psql "$dbname"
   

User management

To add a user with read-only access (viewer):

createuser --member-of=picoforms_ro --pwprompt "$your_username"

To add a user with read-write access (editor):

createuser --member-of=picoforms_rw --pwprompt "$your_username"

To add a new web server:

1. Create a role:

   createuser --member-of=picoforms_www --pwprompt "$your_username"
   

2. Add a row to www_form_server table, with column rolename set to "$your_username" defined in previous step, and column title to human-readable name of a server.
3. Continue with the web server installation as described in its repository.

Development

While it is possible to edit SQL dumps manually, it's painful and discouraged. It is much better to follow this practice:

1. Install the database.
2. Make some edits to database schema via SQL client of your choice (e.g. CLI psql, DBeaver, Adminer).
3. Dump the schema back to repository with this command.

   pg_dump picoforms --no-owner --restrict-key=picoforms --create --schema-only > schema.sql
   

   This command produces exactly same dumps across different hosts, users and time.

File roles.sql is edited manually. It contains all roles used in Picoforms database.