Table of Contents
22.14 Databases - Excursus alternatives
If you only want to manage a small amount of data and MySQL or PostgreSQL with the necessary database servers are too complex for you, then it is worth taking a look at lightweight alternatives. This also applies to SQLite, whose databases and the DB tables anchored in them are in a proprietary format that you cannot read without special programmes. However, you will then have to do without some features such as multi-user operation. This chapter presents some alternatives.
22.14.1 Zenity
The Zenity program (https://help.gnome.org/users/zenity/stable/index.html.de) enables the creation of graphical dialogues with simple console commands. For example, you can use the dialogue program presented here to easily manage your contacts:
Figure 22.14.1.1: User interface (GUI)
All you need is a bash script that you can change to suit your requirements:
#!/bin/bash # Geändert: 24.04.2021 # Datum-Format: ISO 8601 yyyy-mm-dd function eingabe() { zenity --forms \ --title="EMail-Kontakt hinzufügen" \ --text="Geben Sie die geforderten Daten in das Formular ein!" \ --separator="," \ --add-entry="Vorname:" \ --add-entry="Nachname:" \ --add-entry="EMail-Adresse:" \ --add-calendar="Geburtstag:" \ --forms-date-format="%Y-%m-%d" >> liste.csv } function ausgabe() { awk -F "," '{print $1"\n"$2,"\n"$3,"\n"$4}' liste.csv | zenity \ --width=600 \ --height=300 \ --title="EMail-Kontaktliste" \ --text="Wählen Sie einen Datensatz aus..." \ --list \ --column="Vorname" \ --column="Name" \ --column="EMail-Adresse" \ --column="Geburtsdatum" \ --separator="," \ --print-column=all } while true do menu="$(zenity --width=360 \ --height=200 \ --title="EMail-Kontaktliste" \ --text="Wählen Sie eine Aktion aus..." \ --list \ --radiolist \ --column="Auswahl" \ --column="Aktion" \ FALSE "Eingabe von EMail-Kontakt-Daten" \ TRUE "Ausgabe der EMail-Kontakt-Daten" \ FALSE "Programm-Ende")" if [ "$menu" = "Eingabe von EMail-Kontakt-Daten" ]; then eingabe elif [ "$menu" = "Ausgabe der EMail-Kontakt-Daten" ]; then ausgabe elif [ "$menu" = "Programm-Ende" ]; then exit fi done
Figure 22.14.1.2: Output of the contact data
Figure 22.14.1.3: Input of the contact data
All entered data is saved in a text file (liste.csv). In addition to the liste.csv database, you will also find the liste.sh programme file in the download area.
22.14.2 Database with structure
Chapter ‘7.2.2 Project with structures’ presents a project that saves the data records internally in a structure. You can navigate in the data records, add data, change and display data. The database is a file in which the data is saved in a Gambas-specific serialised format, which you can also read from the file:
Figure 22.14.2.1: Data management interface (GUI)
22.14.3 Database with a text file as database
In the download area you will find the project archive db.textfile.tar.gz for data management.
Figure 22.14.3.1: Data management interface (GUI)
You can also navigate, add, change and display data records in this Gambas programme. However, a simple text file is used as the data basis, in which the data records are saved line by line, which you can also read out again from this file.
22.14.4 Recutils database system
Recutils implements databases that are based on pure text files. You can manage extensive data records in the database with just a few shell commands. You can find a good introduction to Recutils in the following articles on the web or in the help system:
- Console: $ info recutils
The easiest way to install Recutils on your system - if it is not already installed - is via the application management. Alternatively, enter the following lines in a terminal:
$ sudo apt-get update $ sudo apt-get upgrade $ sudo apt-get install recutils
The next section demonstrates how to use
- create a Recutils database,
- define a record set, where a record set corresponds to a DB table with DB fields,
- view the definition of a record set,
- enter DB data in a record set,
- read DB data from a record set,
- Change DB data in a record set,
- Save DB data in a csv file (dump),
- read DB data from a csv file into a record set and
- generate a DB report in a self-defined format (→ template).
For administrative tasks such as creating a Recutils database or defining record sets (DB tables), you only need a text editor of your choice. All other tasks are carried out with special console programmes such as recsel or rec2csv from the Recutils tools.
(1)
Creating a Recutils database with a record set (record set in Recutils = DB table)
Enter the following text in a text editor such as xed under Mint. Save the text in the text file liste.rec. After saving, you already have a Recutils database in the file liste.rec with an (empty) table with the table name Liste!
%rec: Liste ' Tabellenname %doc: Kontakt-Liste (Vorname, Nachname, EMail-Adresse und Geburtsdatum) ' Table description %mandatory: Vorname Nachname ' These fields must not be left blank %key: Id ' Key definition with the key name Id %type: Id int ' The data type of the Id field is Integer - predefined type ‘int’ %auto: Id ' The value of Id is automatically increased %type: Vorname line ' The data type of the First name field is text - predefined type ‘line’ %type: Nachname line ' The data type of the Surname field is Text %typedef: EMail_t email ' Type-Definition %type: EMail-Adresse EMail_t ' The data type of the EMail field is ‘email’ (predefined type) %type: Geburtstag date ' The data type of the birthday field is ‘date’ (predefined type) # ENDE TYP-DEFINITIONEN ' Comments only begin on a separate line with #
(2)
Look at the definition of the table in the database with the console programme recinf:
$ recinf -d liste.rec ' You need -d or --descriptor as a parameter %rec: Liste %doc: Kontakt-Liste (Vorname, Nachname, EMail-Adresse und Geburtsdatum) %mandatory: Vorname Nachname %key: Id %type: Id int %auto: Id %type: Vorname line %type: Nachname line %typedef: EMail_t email %type: EMail EMail_t %type: Geburtstag date
(3)
Check how many data records are stored in the DB table ‘List’ in the database:
$ recinf liste.rec 0 Liste
No data record available - which won't shock you. But that will be changed now!
(4)
Insert a data record into the database table
The syntax is simple to completely insert a new data record with the console programme recins. Attention: The field `Id` as character set counter is entered p.d. automatically! The following applies: -f = field, -v = value and -r = record, alternatively also as a combination of f and v with a colon as assignment operator as used in variant 2 for the date of birth:
$ recins -t Liste -f "Vorname" -v "Anna" -f "Nachname" -v "Geier" -f "EMail" -v "anna.g@gmail.com" -f "GebDatum" -v "1999-01-04" liste.rec
Variant 1:
$ recins -t Liste \
-f "Vorname" -v "Anna" \
-f "Nachname" -v "Geier" \
-f "EMail" -v "anna.g@gmail.com" \
-f "GebDatum" -v "1999-01-04" \
liste.rec
Variant 2:
$ recins -t Liste -f "Vorname" -v "Anna" -f "Nachname" -v "Geier" -f "EMail" -v "anna.g@gmail.com" -r "GebDatum:1999-01-04" liste.rec
(5)
Output data records from the DB table list
$ recsel -t Liste liste.rec Id: 0 Vorname: Anna Nachname: Geier EMail: anna.g@gmail.com GebDatum: 1999-01-04
Further data records are entered and then all data records are output again:
$ recsel -t Liste liste.rec Id: 0 Vorname: Anna Nachname: Geier EMailAdresse: anna.g@gmail.com Geburtstag: 1999-01-04 Id: 1 Vorname: Hans Nachname: Lehmann EMailAdresse: hans@gambas-buch.de Geburtstag: 1949-05-02 ... Id: 11 Vorname: Klaus Nachname: Kramer EMailAdresse: klaus.kramer@arcor.com Geburtstag: 1971-06-02
(5.1)
Data records are only output with selected fields
$ recsel -t Liste -p Vorname,Nachname liste.rec
(5.2)
Data records are output with selected properties - also via ~ ‘RegEx’
$ recsel -t Liste --expression "Vorname ~ 'H'" liste.rec ' Vorname beginnt mit 'H' $ recsel -t Liste -e "Vorname ~ 'Ha'" liste.rec ' Vorname beginnt mit 'Ha' $ recsel -t Liste -e "Nachname ~ 'F|N'" liste.rec ' Nachname beginnt mit 'F' oder mit 'N' $ recsel -t Liste -e "GebDatum >> '1997-01-01'" liste.rec ' Vergleich mit << oder == oder >> $ recsel -t Liste -e "Nachname ~ 'F|N' && GebDatum >> '1950-01-01'" liste.rec ' Logischer Vergleich mit && -> AND
(5.3)
Data records are output sorted - here by surname
$ recsel -t Liste --sort="Nachname" liste.rec
(6) \\Changing the content of a field in a selected data record
$ recset -t Liste -e "Nachname='Geier'" -f "EMail" -s "anna.maria.geier@web.de" liste.rec
(7)
Delete a specific data record completely
$ recdel -t Liste -e "Nachname='Geier'" liste.rec
(8)
Export the DB data of the List table to a csv file liste.csv
$ rec2csv -d "," -t Liste liste.rec > liste.csv ' Der Delimiter "," ist der Standard
(9)
Import data records from a csv file into a database file with a defined header. Attention: All existing data records are overwritten!
$ csv2rec liste.csv > liste.rec
(10)
Two steps are necessary for a DB report. In the first step, define a template as a report format pattern. Enter the following three lines in a text file liste.template. You can freely design the template. The field names are enclosed in double curly brackets and act as placeholders for the field content:
ID: {{Id}}
{{Vorname}} {{Nachname}} , Geburtstag: {{GebDatum}}
----
In the second step, you create a report file liste.report with the specified pattern in the template file in which the selected data is sorted by surname:
$ recsel --sort="Nachname" liste.rec | recfmt -f liste.template > liste.report
This is the shortened content of the report file:
ID: 8 Bernd Bär , Geburtstag: 1996-10-22 ---- ID: 11 Klaus Kramer , Geburtstag: 1971-06-02 ... ID: 9 Werner Wiesel , Geburtstag: 1990-06-02 ---- ID: 3 Ziri Zippel , Geburtstag: 1999-05-13 ----
You may have asked yourself why you should also look at the DBMS Recutils? The DBMS Recutils is a powerful system for which it may well be worth developing and testing a graphical user interface (GUI) with Gambas! It would be even more interesting if you could provide a class recutils.class.
22.14.5 LibreOffice Base®
The (partial) programme LibreOffice Base® also provides you with a DBMS with which you can enter, change, query and print your data in SQL without any programming knowledge. Numerous dialogues and forms support you in data management.