Ceci est une ancienne révision du document !
In this series of articles, I will be building a text-based application with Free Pascal, using its text-based interface for user interaction. This will be combined with other, more modern, technologies such as database access using SQL and Web access with HTTP. The final aim of the project is to demonstrate how Pascal can be used to build a modern application, while avoiding the overhead associated with a graphical interface that uses a widget set such as GTK or Qt. In the previous part of the series, we covered personalizing the Free Vision menu bar, responding to commands, and using default message boxes and dialogs. This third part will describe connecting our Free Pascal / Free Vision program with the Sqlite3 database.
Dans cette série d'articles, je construirai une application en mode texte avec FreePascal, en utilisant son interface en mode texte pour l'interaction avec l'utilisateur. Ceci sera combiné avec d'autres technologies, plus modernes, telles que l'accès à une base de données en utilisant SQL et l'accès au Web avec HTTP. Le but final du projet est de démontrer comment Pascal peut être utilisé pour construire une application moderne, tout en évitant le surpoids associé à une interface graphique qui utile un jeu de gadgets comme GTK ou Qt.
Dans la partie précédente de la série, nous avons couvert la personnalisation de la barre de menu Free Vision, la réponse aux commandes, et l'utilisation des boites et dialogues de message par défaut. Cette troisième partie décrira la connexion de note programme FreePascal / Free Vision à la base de données SQLite3.
Installation Although SQlite achieved notoriety only relatively recently, the project began as far back as 2000. It certainly filled in an empty space in the universe of databases, since its big feature is that client programs link to a simple library. There is no need for installing a large database frontend on the user’s machine, nor to connect to an external database server over a network. Data is stored locally, in a simple file format. This makes it quite quick and suitable for small to medium-sized databases that are accessed basically by a single user, while at the same time making data easily transportable between computer systems (simply copy a file over). SQLite will probably already be installed on an Ubuntu system, but let us just make sure and install both the command-line client and the developer package. We will need this second bit to compile database access into our programs. The packages required are: sudo apt-get install sqlite3 libsqlite3-dev Now, as a normal user, we can create a new database using the command-line tool. For example, we can create a file of issues for our favorite magazine. Let us call the actual file “fullcircle.db”. So: $ sqlite3 fullcircle.db SQLite version 3.11.0 2016-02-15 17:29:24 Enter “.help” for usage hints. sqlite> create table issues (id integer primary key asc, title varchar, description varchar, download varchar, tags varchar); sqlite>
Installation
Bien que SQLite n'a atteint la notoriété que relativement récemment, ce projet a commencé depuis 2000. Il a certainement rempli un vide dans l'univers de bases de données, car sa fonctionnalité importante est que les programmes des clients sont liés à une simple bibliothèque. Il n'y a pas besoin d'installer une grande « face avant » de la base de données sur la machine de client, ni de se connecter à un serveur externe de base de données à travers un réseau. Les données sont stockées localement, dans un format de fichier simple. Ceci la rend très rapide et pratique pour les bases de données de taille petite à moyenne qui sont accédées en général par un unique utilisateur, tout en rendant les données facilement transportables d'un ordinateur à un autre (par simple copie d'un fichier).
SQLite sera probablement déjà installé sur un système Ubuntu, mais assurons-nous en et installons en ligne de commande à la fois le client et le paquet pour développeur. Nous aurons besoin de cette seconde partie pour compiler l'accès à la base de données dans nos programmes. Les paquets nécessaires sont :
sudo apt-get install sqlite3 libsqlite3-dev
Maintenant, en tant qu'utilisateur ordinaire, nous pouvons créer une nouvelle base de données en utilisant l'outil en ligne de commande. Par exemple, nous pouvons créer un fichier des erreurs de notre magazine favori. Commençons par appeler le vrai fichier «fullcircle.db ». Ainsi :
$ sqlite3 fullcircle.db SQLite version 3.11.0 2016-02-15 17:29:24 Enter “.help” for usage hints. sqlite> create table issues (id integer primary key asc, title varchar, description varchar, download varchar, tags varchar); sqlite>
The CREATE TABLE commands creates a new table - named “issues” - in which several fields are defined for each entry. “id” will simply be a numerical identification code that will be used by the system to index our entries in ascending mode. For each entry, information recorded will include the title, description, download link and tags – in separate strings. This may become more clear when we populate the database with some actual information: sqlite> insert into issues values (110, 'issue 110', '- description goes here -', 'http://dl.fullcirclemagazine.org/issue110_en.pdf', 'python clonezilla inkscape, chromebook'); sqlite> insert into issues values (109, 'issue 109', '- description goes here -', 'http://dl.fullcirclemagazine.org/issue109_en.pdf', 'python libreoffice inkscape netbook ltsp'); sqlite> insert into issues values (108, 'issue 108', '- description goes here -', 'http://dl.fullcirclemagazine.org/issue108_en.pdf', 'python inkscape chrome devices arduino');
Now, with some data inside the database, we can perform some queries: sqlite> select * from issues where title='issue 109'; 109|issue 109|- description goes here -|http://dl.fullcirclemagazine.org/issue109_en.pdf|python libreoffice inkscape netbook ltsp sqlite> select download from issues where id=110; http://dl.fullcirclemagazine.org/issue110_en.pdf sqlite> select id from issues where tags like '%inkscape%'; 108 109 110
In the first, we request all information for entries with title “issue 109”. In the second, we need just the download link for the entry with ID 110. In the last, we are searching for the IDs of all numbers that have been tagged with “inkscape” - note the use of “%” as wildcards to match any string that contains “inkscape”, but may have other text bits found before or after the word that interests us. To exit the command line client, we issue command: sqlite> .quit
Interfacing with Free Pascal We will begin by writing a short program, just to test connectivity between our program environment and the Sqlite library. In it, we will limit ourselves to performing a quick search and reporting results on-screen with the Writeln command. Free Pascal project members have prepared a suitable unit. There are actually several alternatives, but the one presented here seems to be the best supported, at least for the time being. We will need the Crt unit for Writeln, but also Strings and Classes; this latter unit contains the TStringList type that will be used to access each line of the query result. Finally, Sqlite3 and Sqlite3db handle the actual connection to the library: uses Crt, Classes, Strings, Sqlite3, Sqlite3db;
We will need several variables to handle the connection. sql is the connection itself, while i and n will be used to iterate and to count the number of results returned by our query. res will format each line of results in a parseable manner. Though not really necessary, id and downloadURL will be used to hold the values of individual fields. var sql : TSQLite; i, n : Integer; res : TStringList; id, downloadURL : String; We begin by creating a connection, executing our query and counting the number of lines in the result: sql := TSQLite.Create(dbfile); sql.Query(dbquery, nil); n := sql.List_Field.count;
We can now iterate over the result lines: for i := 1 to n do begin res := TStringList(sql.List_Field.items[i-1]); id := res[0]; downloadURL := res[1]; Writeln (id, ' ', downloadURL); end; Finally, even though Sqlite is rather robust, it is always best to close the connection gracefully: sql.Free; The code for the complete program is available at this link: http://pastebin.com/k4JB6fZr .
Connecting Sqlite to Free Vision In the previous part of this series, we designed a basic Free Vision application, with a menu that contained a File > Open item. We programed this item to issue the cmOpen command, which was then captured in the HandleEvent method to create a TFileDialog prompting the user to choose a file. However, the choice made by the user has not yet been properly exploited. Let us do so now. In the first place, we will modify the code creating the TFileDialog so it filters for files with the .db extension: pOpen := New(PFileDialog, Init('*.db', 'Open', 'File ~N~ame', fdOpenButton, hcNoContext)); result := ExecuteDialog (pOpen, @FileName);
After execution, result is an integer that holds either command cmOpen indicating the user closed the dialog using the “Open” button, or cmCancel if the “Cancel” button was used. FileName is a string with the file name chosen. We can now use this input to set up a Dialog window that creates a Sqlite connection, performs the query, and displays results: if not (result = cmCancel) then begin GetExtent(R); R.A.X := R.A.X + 2; R.A.Y := R.A.Y + 2; R.B.X := R.B.X - 2; R.B.Y := R.B.Y - 2; pDisplay := New(PDisplaySQLDialog, Init(FileName)); ExecuteDialog (pDisplay, nil); end;
R is a variable of type TRect that Free Vision uses to indicate a rectangular region on screen. The two fields A and B are the top-left and bottom-right positions occupied that define the rectangle, which is in this case X and Y the column and row numbers. On the other hand, pDisplay is a pointer to a TDisplaySQLDialog - an object that inherits from TDialog and is not part of the standard Vision libraries. So let us define it, overwriting only the constructor Init method to pass along the database file name to be opened and displayed: TDisplaySQLDialog = object(TDialog) constructor Init (FileName : String); end; Having a tailored Init will serve two purposes: it is here that we will build up the interface, with a PListBox to hold the data to be displayed, a PScrollBar to allow us to move up and down in the list, and a PButton to close the dialog window. Let us start by setting up the window size and title using the inherited TDialog constructor method: R.Assign(0, 0, 78, 17); inherited Init (R, 'xxx');
Now, let us fit in the Sqlite routine to populate a PStringCollection with the results of our query. Unfortunately, there is a slight hiccup with the TStringList used in the previous example, already present in the days of Turbo Vision. One of the FreeVision classes also defines a TStringList type of object, which is incompatible with that used to parse Sqlite’s output. So we need to specify we wish to use the version defined in the Classes unit. The code (top right) should be self-explanatory. With this done, we have the dialog window set up and Items populated with the data retrieved from the database. It is now a simple matter of setting up the widgets and inserting each into the Dialog window (code is bottom right). That’s about it. The complete code for our program, so far, is to be found at link: http://pastebin.com/sLFWm6pR . In this part, we set up a small Sqlite database, then built a command-line Free Pascal program to access it. Finally, we integrated the database code into our Free Vision application through a new Dialog type to connect to the database and display retrieved data. In the next part of the series, we will connect to the Internet in order to refresh the information in our database directly from the Full Circle Magazine website.