Lazarus

Programming => General => Topic started by: BosseB on November 21, 2019, 11:51:27 am

Title: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 21, 2019, 11:51:27 am
I am building a control system for external equipment using an RPi4 box.
I have done a similar system 13-14 years ago on Windows using Delphi7 and ADO connection to a MSSQLServer database.
But that was not interfaced through Apache, I created a dedicated Delphi Windows service program instead.

Today I have moved to FPC/Lazarus on RPi4 and Raspbian and I want to use a web interface for user interaction.
Therefore I planned on using text files (ini format) for handling the user config.

But for other reasons it would be better using a database, and I think that SQLite would be OK because there are not so much data to handle and it is not a massively multiuser thing either. Probably zero external users most of the time...

But it needs to be possible to interface from FPC and PHP (on webserver) and run on RPi4B with Raspbian Buster.

I have found this info: SQLite with Lazarus (https://wiki.lazarus.freepascal.org/SQLite)
But it deals with a GUI program and my program is not, it is a command line program run from cron.
And then there is the web interface through PHP7....

One of the first lines in the doc page I linked to states
Quote
SQLite is an embedded (non-server) single-user database
Does this disqualify SQLite as database if I also want it to be reachable from Apache?
If so what could I use instead?

And is there some example of non-GUI use of SQLite in an FPC command line program somewhere?


Title: Re: Using SQLite database in command line program on RPi4B?
Post by: MarkMLl on November 21, 2019, 12:02:45 pm
As I understand it, SQLite is a library rather than a daemon so it exists only in the context of a single program.

If you want something more comprehensive then consider PostgreSQL or Firebird, which are fairly compatible as far as the SQL queries are concerned. I favour the former since the documentation is better: I got into fairly deep water when writing something that as well as doing normal queries also set up new users etc. as required.

While you can put operational parameters into a database, somewhere you also need to store the information telling the program how to connect to the database. A .ini file is good for that, with dues consideration of password storage etc.

MarkMLl
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: wittbo on November 21, 2019, 12:12:24 pm
First:  I don't believe, that SQLite ist only applicable within GUI applications. You can define and use all components directly in your program at runtime, so no GUI ist needed.

Second:  By default, SQLITE is not multiuser cabable. The concept behind it is an embedded database, the backend is a file in the filesystem, no dedicated server process(es), nothing more. There are some threads on this platform discussing the multiuser capabilities; when you have more than one program instance using this one database file, collision probability depends on the frequency and duration of write accesses. And this is not predictable in a webserver environment. In this case I would decide for a server based database like MySql.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: devEric69 on November 21, 2019, 12:36:17 pm
With what I understand: *if* you decide that your Lazarus application will be the only one which will access your single-user embedded SQLite database, then it will have to behave like an "application server".
So, your Php 7.x interface will have to send requests (SOAP, ...?) to your Lazarus  application, which will act as a proxy between Php 7.x HTML-UI and SQLite, due to the fact that your Lazarus application has exclusive CRUD access to SQLite.

After writing this, I realize that you'd better go with mySQL or mariaDB: Php 7.x and your Lazarus console application will be able to access your database together (nb: Zeos components works well with these databases)...
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 21, 2019, 03:07:07 pm
OK, so my use case is real simple, it could be implemented using a bunch of text files.
Through a web interface the user should be able to configure the jobs by creating new job definitions or editing existing definitions.

And the job sequencer will check every minute if there is a job to run and then start it using the data in the files or database. This happens unsynchronized to any user interaction via the web.

I have earlier tried to install mysql on an RPi but when I tried to also install phpmyadmin in order to be able to navigate the database the whole RPi went unstable and I had to scrap it. Could not repair so started over.
So I have bad vibes for MySql.

What else could one sensibly use in a light-weight application like this?
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: MarkMLl on November 21, 2019, 03:21:05 pm
If you have to have a web interface, one possibility would be to embed the server into the program actually doing the measurement so that they can use the same instance of SQLite. You can always have a server listen on a non-standard port number to make sure that it doesn't clash with e.g. a pre-existing copy of Apache, /but/ you would have to take into account that a malicious user would probably find holes in a lightweight server that probably wouldn't exist in Apache etc.

MySQL has a bit of an odd reputation of needing drivers tailored to the exact version of server. For various reasons it's not my favourite choice.

MarkMLl
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: devEric69 on November 21, 2019, 03:40:45 pm
Okay, that's clearer.

One solution - among others - could be:
1°) to manage the description and configuration text files of the CRONs to launch: write an UI interface with Php, but like @MarkLi said: only "If you have to have a web interface". But *if* nobody need access from the "World Wild Web", write an application with Lazarus would be easier and faster, of course.
2°) write a Lazarus application (console or not), with a thread-worker which will be the observer of the appearance of each CRON's zipped results file (this new zipped files being the subjects to be observed). And the worker will write their time-stamped appearance, etc., in the SQLite database.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 21, 2019, 03:45:58 pm
Maybe a stupid question, but...
whoever said, that the DB-server has to run on the Pi?
[SERVERMACHINE] --> Running/providing MySQL (or whatever RDBMS)
[MACHINEWITHWEBINTERFACE] --> connect to MySQL on [SERVERMACHINE] --> Login to MySQL --> Do jobInstructions
[RPi4b] --> Your App connecting to MySQL on [SERVERMACHINE]

Or is my brain malfunctioning?
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 21, 2019, 04:19:21 pm
Maybe a stupid question, but...
whoever said, that the DB-server has to run on the Pi?
[SERVERMACHINE] --> Running/providing MySQL (or whatever RDBMS)
[MACHINEWITHWEBINTERFACE] --> connect to MySQL on [SERVERMACHINE] --> Login to MySQL --> Do jobInstructions
[RPi4b] --> Your App connecting to MySQL on [SERVERMACHINE]

Or is my brain malfunctioning?
I might not have directly mentioned it, but my project is such:

So it was obvious to me that the database server should reside on the RPi itself.
There may also be another database on the server in the cloud, and in such case database replication is probably a wanted function too.

I really do not want to create a Lazarus GUI application running on the RPi just for setup and configuration.
It would make it necessary to install a full GUI version of Raspbian onto the RPi when I really was looking for a Lite command line only version.
I had in mind running through Apache on RPi to handle config and maintenance stuff.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 21, 2019, 04:23:01 pm
If you have to have a web interface, one possibility would be to embed the server into the program actually doing the measurement so that they can use the same instance of SQLite. You can always have a server listen on a non-standard port number to make sure that it doesn't clash with e.g. a pre-existing copy of Apache, /but/ you would have to take into account that a malicious user would probably find holes in a lightweight server that probably wouldn't exist in Apache etc.

MySQL has a bit of an odd reputation of needing drivers tailored to the exact version of server. For various reasons it's not my favourite choice.

MarkMLl
I want to avoid  having specialized client software to maintain. Done it before and it was no fun.
Instead using a web browser and writing a website for config (using PHP unfortunately) is easier on my nerves...
And having a server side software communication over http is no fun either, I have done a similar thing before using both a server and client in Delphi. By I don't want a repeat of that...

(Are you the M Mo..an L..d I have seen on the mail list?)
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: MarkMLl on November 21, 2019, 05:20:50 pm
I want to avoid  having specialized client software to maintain. Done it before and it was no fun.
Instead using a web browser and writing a website for config (using PHP unfortunately) is easier on my nerves...
And having a server side software communication over http is no fun either, I have done a similar thing before using both a server and client in Delphi. By I don't want a repeat of that...

(Are you the M Mo..an L..d I have seen on the mail list?)

I sympathise with your misgivings about specialised client software, but at the same time a web server can be quite vulnerable and require careful optimisation. And yes, but I'm not using the old email account due to a legal dispute.

MarkMLl
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 21, 2019, 05:23:20 pm
As for SQLite-Concurrent Access:
https://stackoverflow.com/questions/4060772/sqlite-concurrent-access
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: devEric69 on November 21, 2019, 05:32:59 pm
Quote
Instead using a web browser and writing a website for config (using PHP unfortunately) is easier on my nerves...

Indeed, considering the specifications, as a first reaction, I will probably also write the interface in Php (there are many classes and 7.x is typed, so easier) + Apache.
So, there is already a HTTP server to install: Apache.

Quote
I really do not want to create a Lazarus GUI application running on the RPi just for setup and configuration.

I was thinking about XFCE + Midnight Commander just to help during development.

Quote
It shall upload results to a server somewhere when measurements are done.

If the console application in Lazarus accesses the SQLite database to log the arrival of results, it apparently should also be an HTTP server to respond to remote requests that queries the database before responding: (I also come from Delphi) I would look for something comparable to TWebBroker, TWebRequest, TWebAction, etc, to listen to an HTTP request and to emulate a distribution loop creating an HTTP response .
I think mORMot (https://wiki.freepascal.org/mORMot (https://wiki.freepascal.org/mORMot)) could do that (I've never used it): "it is an Open Source Client-Server ORM SOA ...") components to do that.   

But, mORMot also knows how to be an HTTP server, so that makes 2 HTTP servers... which brings back some confusion ==> do everything with Lazrus, do everything with Php, or a combination of both? sorry :( .
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 21, 2019, 05:55:50 pm
As for SQLite-Concurrent Access:
https://stackoverflow.com/questions/4060772/sqlite-concurrent-access
Interesting! Seems like SQLite can be used after all.
My case is one with one hit per minute from the cron job plus one per week or so from the locally connected web user...
Question:
Where does one find the sqlite.so file on Raspbian? Or whatever the library is called.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 21, 2019, 06:19:33 pm
the "locate sqlite"-command?
Quote
zvoni@ZvoniLinux:~$ locate libsqlite3.so
/snap/core/7917/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
/snap/core/7917/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
/snap/core18/1223/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
/snap/core18/1223/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
/usr/lib/mysql-workbench/libsqlite3.so
/usr/lib/x86_64-linux-gnu/libsqlite3.so
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
zvoni@ZvoniLinux:~$
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: devEric69 on November 21, 2019, 07:43:54 pm
Just for information and brainstorming, I saw that some use " ssh -Y " ( from a remote Linux machine having X-ming ) or PuTTy with the " X11 option " (from a remote Windows machine having cygwin ).

Documentation says " ssh -Y " would only allow you to launch one remote UI application at a time, so as not to abuse graphic resources on a { Raspberry } server.

If this would allow the use of an ultra light remote desktop with a Pi, then it could be possible to do a Lazarus UI application (TForm + TFrames alClient with  TSchrollBox alClient and controls inside) to, from time to time, query \ see data stored in the database, transfer files, etc.

2 cents.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 21, 2019, 08:38:15 pm
the "locate sqlite"-command?
What I get is:
Code: [Select]
$ locate libsqlite3.so
-bash: locate: command not found
Same if I try sqlite3 on command line:
Code: [Select]
$ sqlite3
-bash: sqlite3: command not found
So I did:
Code: [Select]
$ sudo apt install sqlite3
$ which sqlite3
/usr/bin/sqlite3
sudo find / -name libsqlite3.so
find: ‘/tmp/.vnc-1000/run/gvfs’: Permission denied

AFAICU from the Lazarus SQLitewiki (https://wiki.lazarus.freepascal.org/SQLite) I have to specify the location of the libsqlite3.so in my Lazarus/Fpc code when I open the database:
Code: Pascal  [Select]
  1. SQLiteLibraryName:='./sqlite3.so';
This is when the so file has been placed in the current dir.
But it is hard when there is no such thing on the system...
I must have missed something important here.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 21, 2019, 09:34:38 pm
I found a component on the Lazarus tab SQLdb named TSQLite3Connection, if I use that maybe it will be able itself to dig up where to call sqlite3?
I am trying to find working examples (a hello world program for sqlite) to analyze after first checking that it actually works in my RPi4.

LATER:
I tried to create a new GUI program on the RPi4 and added the TSQLite3Connection component to the form.
Then I set its databasename to /pathto/monitor.sqlite and then checked the Connected checkbox in object inspector. This error message is what turned up as a pop-up error (cannot load libsqlite3.so).
So apparently it still needs this library file, which does not exist on the disk of my RPi4 even after installing sqlite3 itself...


Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 21, 2019, 10:08:47 pm
sudo apt-get install mlocate

Then "locate libsqlite3.so"

Sorry
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 21, 2019, 10:15:22 pm
Didn't work:

Code: [Select]
$ sudo apt install mlocate
-- stuff happens ---
$ locate libsqlite3.so
locate: can not stat () `/var/lib/mlocate/mlocate.db': No such file or directory

What exactly does locate do that find is not doing?
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: winni on November 21, 2019, 10:23:12 pm
Hi!

See
Code: Bash  [Select]
  1. man locate

Meanwhile start a

Code: Bash  [Select]
  1. find / -name libsqlite3.so

That will take some time.

Winni
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: fmc on November 21, 2019, 10:48:59 pm
After installing mlocate, issue this command at the prompt: updatedb
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 21, 2019, 11:37:55 pm
After installing mlocate, issue this command at the prompt: updatedb

So I did that and here is the sequence I used:
Code: [Select]
$ sudo apt install mlocate
$ locate libsqlite3.so
locate: can not stat () `/var/lib/mlocate/mlocate.db': No such file or director
$ sudo updatedb
$ locate libsqlite3.so
/usr/lib/arm-linux-gnueabihf/libsqlite3.so.0
/usr/lib/arm-linux-gnueabihf/libsqlite3.so.0.8.6

So now I have 2 files found but with the wrong extension...
Further investigation shows that the one ending in 0.8.6 is the actual file and it is symlinked to the other.
Am I supposed to symlink it to my own project directry as libsqlite3.so?
I tried to symlink like that to my project dir but I get the exact same error message when I try to set the connection object to Connected=true....
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 22, 2019, 12:39:09 am
FINALLY!
After reading dozens of threads and how-tos I finally found one place where I got into a solution:
Getting started with SQLite under Linux (https://www.tweaking4all.com/software-development/lazarus-development/lazarus-pascal-getting-started-with-sqlite/#GettingstartedwithSQLiteunderLinuxUbuntuRaspbian)
The previously missing link for me is the following:
Code: [Select]
sudo apt install sqlite3 libsqlite3-devI did not know of the last part before...
So I checked what it did using the locate command (after updatedeb) and it found a so file at the same place where the others were. Strangely what this file was is a symlink from the exact same file I symlinked over to the project dir earlier.
So it does not help having it next to the executable, but apparently if it is /usr/lib/arm-linux-gnueabihf/libsqlite3.so

Go figure.... Or maybe this install sets up something else as well, but what?

In any case now that I check the connected property inside Lazarus IDE there is no longer an error and the empty database file gets created.

Now it is way too late, I have to get some sleep....
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: winni on November 22, 2019, 01:28:15 am
Hi!

Keep that in mind for the future. It is often (but not always) that you need the development package for some reasons. So look for the packages with  dev or devel - helps you in some strange situations, because the developers don't mention it - and perhaps they don't know that the devel-package is needed!

Winni
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: MarkMLl on November 22, 2019, 09:39:22 am
That's not the first time I've seen a Debian/Raspbian -dev package set up a symlink that common sense would suggest would always be needed.

I believe that was why https://wiki.freepascal.org/TSQLDBLibraryLoader was added to the LCL.

MarkMLl
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 22, 2019, 09:55:35 am
Thanks Mark!
I did not know about the Library Loader, but found it on the palette now.
Since I am basically working on a command line program I have made a test app (no functionality) just to see what works or not in the GUI.
However, it has a default LibraryName selectable for SQLite (libsqlite3.so) with no path, so it seems like it does not find it by itself.
One has to provide the full path in the configuration, I guess this means I have to make this a configuration item, right?
So it needs to go into the conf file and loaded on application initialization, right?
And do I have to "connect" it to the SQLiteConnection component somehow?
Or is it enough to use the LoadLibrary method after setting the ConnectionType and LibraryName properties?

Then I have to translate this into non-GUI code too.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: MarkMLl on November 22, 2019, 10:38:01 am
In fairness, it's reasonable to expect a developer's machine to have the relevant -dev packages on it. Unfortunately, in the case of Debian/Raspbian the lack of the symlink set up by installation of the -dev package impacts usability of end-user systems which normally wouldn't (and possibly shouldn't) have developer packages installed.

My recollection is that the underlying SQL connection components are thin wrappers around classes in the FCL, but I can't remember whether I've used the FCL directly other than for minimal bug report code.

I tend to be oriented towards PostgreSQL here, when I was shopping around for a suitable database back in the early 00s it was the only accessible one that had comprehensive transaction support which was something I needed badly. Having all libraries in the right place was not an issue with Slackware and Windows ODBC/BDE which were what I was using at the time, I suspect that Debian initially set things up properly and when the missing symlink became an issue I simply started creating a symlink and (later) installing the -dev package as routine... something which I'm not entirely happy doing on a non-development machine.

My recollection is that the FCL component gained a search path as an alternative way of fixing the same problem. Now you /could/ look for the .so library in a sequence of "usual suspect" directories, but that implies that application-level code is making the decision that xxx-1.2.3.so is a suitable implementation of the API defined for xxx.so, which is something that should really be done by the package maintainer at the distro level. So in short, this is an distro-level problem which for some reason Debian et al. are refusing to fix, and FPC/Lazarus allow the path to be set at the application level as a not-entirely-satisfactory way around the problem.

I agree that the library path is probably something you want in your configuration file, with the obvious caveat that it will make the configuration version-specific. What I've got here is a .ini file defining how apps are to connect to the database, after which they get everything else from scripts and scheduler tables stored in the database itself: and that .ini file has not needed maintaining for a long time.

I'd suggest that the best solution would be a hybrid sequence:

* Look for an explicit path in the .ini file. If there isn't one then:

* Attempt to make the connection without an explicit path.

* Look for well-known (tested) library names that the distro /should/ have symlinked.

* If this fails, e.g. because your "well known" sequence goes up to xxx-1.2.3.so but the library is now at xxx-1.2.4.so then put it as an explicit parameter in the .ini file until the next software release, then remove it.

I've got a bit of example code here but I don't think it would tell you anything you don't already know, the only bit which might be useful is that the comments suggest that the FCL gained the path parameter > 2.6.0 and the LCL gained a wrapper component at some point after 1.0.

MarkMLl
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 22, 2019, 02:22:35 pm
Thanks again! Very helpful.
Today I have been looking for a SQLite database browser and what I found (https://sqlitebrowser.org/) seemed to be OK, except after installation on my Windows 7 x64 Pro laptop it complains about missing libraries (they look like Microsoft ones).
So I uninstalled it and will now try to get it onto the RPi4 via apt.
I like to be able to inspect the database directly...
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: MarkMLl on November 22, 2019, 02:33:38 pm
I like to be able to inspect the database directly...

I agree. I believe it can be done and that it's fairly common for e.g. Firefox developers to do that during debugging.

Many thanks to whoever it was that pointed out that multiple applications could potentially access (or at least read) the files representing an SQLite database. With the usual caveat about the extent to which file and record locking was grafted onto unix as an afterthought.

MarkMLl
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 22, 2019, 03:28:33 pm
I can report back that on Raspbian this comnmand gave me a functioning SQLite database browser:
Code: [Select]
sudo apt install sqlitebrowser :D
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: MarkMLl on November 22, 2019, 03:41:41 pm
Well done that man and thanks for keeping people updated with a useful success story.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 22, 2019, 05:16:09 pm
*snipp*
And is there some example of non-GUI use of SQLite in an FPC command line program somewhere?
A very simple example for using SQLite with no GUI
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 22, 2019, 10:06:09 pm
@Zvoni Thanks, helpful example!

Another question now that I am getting into coding SQLite:

I noted that there is a storage class BLOB and that it can be used to store "anything".
I have never touched on using this kind of data but its existence intrigues me since I am moving away from creating and handling files of data into using the SQLite database. I know it exists in other databases too but I want specifically to know how it works in SQLite...

So: Is there a freepascal sample of storing/retrieving binary data using SQLite BLOB?

Say I have  a number of files I want to store as BLOB objects in a database, how do the SQL statements look like for INSERT and SELECT of them?
The files may be text files or zip files in my case.

I have tried to search for example code but got nowhere really.

For text files I realize I can use a text column for the body and another for the file name, but what about zipfiles, they will need blob, right?

I am quite new to database work in Lazarus but I have done a fair deal of code in Delphi towards MSSQLServer databases.
Never ever used BLOB columns, though.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: dsiders on November 22, 2019, 10:27:51 pm
@Zvoni Thanks, helpful example!

Another question now that I am getting into coding SQLite:

I noted that there is a storage class BLOB and that it can be used to store "anything".
I have never touched on using this kind of data but its existence intrigues me since I am moving away from creating and handling files of data into using the SQLite database. I know it exists in other databases too but I want specifically to know how it works in SQLite...

So: Is there a freepascal sample of storing/retrieving binary data using SQLite BLOB?

There is an example using Sqlite (Firebird embedded too) to store images. See: examples/database/image_mushrooms
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 22, 2019, 11:26:53 pm
There is an example using Sqlite (Firebird embedded too) to store images. See: examples/database/image_mushrooms
Looked at it and was no wiser...
Can't find any SQL code in any file that is dealing with reading or writing a BLOB field.
All I see is SQL for creating the database itself.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: winni on November 23, 2019, 12:04:28 am
Hi!

Example and discussion how to save images in blob field in sqlite:

https://forum.lazarus.freepascal.org/index.php?topic=39430.0 (https://forum.lazarus.freepascal.org/index.php?topic=39430.0)

Winni
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: bee on November 23, 2019, 01:58:59 am
I've been using fpc with sqlite for CLI and web app without any GUI component whatsoever. I even made an article about it. Well, it's written in Bahasa Indonesia, but Google Translate should be able to help read it in any languages you want. It's here: https://paklebah.github.io/fpc-sqldb-dan-sqlite.html

Hope it helps.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 25, 2019, 05:21:13 pm
I've been using fpc with sqlite for CLI and web app without any GUI component whatsoever.
I even made an article about it. Well, it's written in Bahasa Indonesia, but Google Translate should be able to help read it in any languages you want.
It's here:
https://paklebah.github.io/fpc-sqldb-dan-sqlite.html (https://paklebah.github.io/fpc-sqldb-dan-sqlite.html)

Hope it helps.
Yes it does!
I really can manage fine with all the examples you have added into the article, quite clear what you are doing.
I can go on from there.

THANKS A LOT!
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 25, 2019, 09:46:21 pm
So now I have come a bit further in my creating a DbHandler class for my project.
I am encapsulating all of the database operations in a class where I have this code when I instantiate it:

Code: Pascal  [Select]
  1. constructor TMonitorDb.Create(dbfile: string);
  2. begin
  3.   FConn := TSQLite3Connection.Create(NIL);
  4.   FTrans := TSQLTransaction.Create(NIL);
  5.   FQuery := TSQLQuery.Create(NIL);
  6.   InitDb(dbfile);  //How to deal with failure?
  7. end;
  8.  
  9. function TMonitorDb.InitDb(DbFile: string): boolean;
  10. var
  11.   NewDatabase: boolean;
  12. begin
  13.   Result := false;
  14.   try
  15.     FConn.Transaction := FTrans;
  16.     FTrans.DataBase := FConn;
  17.     FQuery.Transaction := FTRans;
  18.     FQuery.DataBase := FConn;
  19.     FConn.DatabaseName := DbFile;
  20.     FConn.CharSet := 'UTF8';
  21.     NewDatabase := not FileExists(DbFile);
  22.     FConn.Open;
  23.     Result := FConn.Connected;
  24.     if Result and NewDatabase then //Database not existing, so create tables
  25.     begin
  26.       Log.LogStd('Populating new database');
  27.       FQuery.SQL.Clear;
  28.       //Use queries to build database
  29.       FQuery.SQL.Text := SQL_CREATE_TASKS;
  30.       FQuery.ExecSQL;
  31.       FQuery.SQL.Text := SQL_CREATE_MEASSETTINGS;
  32.       FQuery.ExecSQL;
  33.       FQuery.SQL.Text := SQL_CREATE_CMDFILES;
  34.       FQuery.ExecSQL;
  35.       FQuery.SQL.Text := SQL_CREATE_TASKLOG;
  36.       FQuery.ExecSQL;
  37.     end;
  38.     Result := true;
  39.   except
  40.     FLastError := 'Exception! Could not initialize database ' + DbFile;
  41.   end;
  42. end;

In the InitDb method if there is no database it creates it (handled by SqLite itself when it opens a non-existing database file. If that was done I want to set up the tables as shown above, but for some reason it won't work.
Only the two first of the tables are created...
If I run the query strings directly in the db browser they are all created, but I am running them one by one.

Questions:
Is there something more to do in between the SQL calls? Delays? (Not fun...)
In MSSQLServer I used to insert a GO command between multiple queries in a stored procedure, is there something like that also in SQLite?
Or is there some query property one can check to verify that the operation is ready?

I am using 4 query strings similar to the one below, one for each table.
At first I loaded them all into the FQuery.SQL stringlist but then only a single table was created.
Then I changed to the code above and now I get two tables....

Typical SQL string:
Code: Pascal  [Select]
  1.   SQL_CREATE_TASKLOG = 'CREATE TABLE `TASKLOG` ( '+
  2.           '`TaskLogID`  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, '+
  3.           '`TaskID`     INTEGER NOT NULL DEFAULT -1, '+
  4.           '`StartTime`  TEXT, '+
  5.           '`EndTime`    TEXT, '+
  6.           '`Readings`   INTEGER NOT NULL DEFAULT 0); ';

Can the TSQLTRansaction be used to verify that the ExecSql has actually finished before I run the next?
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 25, 2019, 11:10:26 pm
RESOLVED!
Issue 1: Why could I not run multiple statements?
Well it turns out that it is a limitation of SQLite3. It can only process one query statement at a time, but otoh it does it very quickly so there is no real loss of efficiency by having multiple calls to ExecSQL.

Issue 2: Why did it stop after 2 tables were done?
In fact it was the string constants used to create the tables that had an error!
What happened was that I had copied the SQL text from "DB Browser for SQLite", which I used to create the sql and test that it worked. Inside that I had a -- comment in one table definition line, which was now probably killing the remainder of the statement since I have no line breaks included in the SQL string constants....

Once I fixed issue 1 by separating the tables into an ExecSql for each and corrected issue 2, the string constant, by removing the comment it worked as expected.

The whole process took 5 ms for creation of 4 tables with logging to a logfile in-between each.
Pretty OK to me.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: bee on November 26, 2019, 02:05:22 am
Yes it does!
I really can manage fine with all the examples you have added into the article, quite clear what you are doing.
I can go on from there.

THANKS A LOT!

You're welcome. Glad to know it helps. :)
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 26, 2019, 08:14:36 am
RESOLVED!
Issue 1: Why could I not run multiple statements?
Well it turns out that it is a limitation of SQLite3. It can only process one query statement at a time, but otoh it does it very quickly so there is no real loss of efficiency by having multiple calls to ExecSQL.

OK, resolved and all, but take a look at Transactions. SQLite supports them.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 26, 2019, 07:46:44 pm
FOLLOW-UP on SQLite usage
The functionalit to populate a new database with my table is now working as described above.

So now I have another question regarding handling of the interface between program and database.

There are a number of methods I need to program and I have trouble dealing with datetime and floating point data.
Integer and string are OK, but what to do about datetimes and floats?
The problem here is that the format of these are locale dependent, I would like to just send the TDateTime variable or the double variable into the database. But when I formulate the insert query I seem to need a string representation of these values and here the translation annoys me.
Do I really have to do something like this all the time (shortened SQL build construct)?

Code: Pascal  [Select]
  1. SQL := 'INSERT INTO TASKS ' +
  2.          '(TaskName, ..., TaskRepeat, .... StartTime, .... ScaleFactor) ' +
  3.          'VALUES ' +
  4.          '("'+
  5.          TS.Name + '", "' +
  6.          ....
  7.          IntToStr(TS.TaskRepeat) + ', ' +
  8.          ....
  9.          FormatTime(TS.StartTime) + ', ' +
  10.          ....
  11.          FormatDouble(TS.ScaleFactor) + ' ' +
  12.          ');';

When reading out data from the database result there is a simpler way:

Code: Pascal  [Select]
  1.   SQL := 'SELECT * FROM TASKS WHERE TaskId = ' + IntToStr(ID);
  2.   FQuery.SQL.Text := SQL;
  3.   FQuery.Open;
  4.   if not FQuery.Eof then
  5.   begin
  6.     TS.TaskID := FQuery.FieldByName('TaskID').AsInteger;
  7.     TS.Name := FQuery.FieldByName('TaskName').AsString;
  8.         ....
  9.     TS.TaskRepeat := FQuery.FieldByName('TaskRepeat').AsInteger;
  10.         ....
  11.     TS.StartTime := FQuery.FieldByName('StartTime').AsDateTime;
  12.         ....
  13.     TS.ScaleFactor := FQuery.FieldByName('ScaleFactor').AsFloat;
  14.  

So is there some way to just put the variables themselves into the operation to add an object's data into the database?

Please note that the data and time formats vary wildly across the globe, I prefer ISO (yyyy-mm-dd hh:mm:ss.sss) myself.
And the decimal point of floats is either period or comma depending on location. I want this system to not be depending on keeping track of such things...
And I don't know where the system will be deployed, can be anywhere in the world.

Any simpler way?
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 27, 2019, 08:41:22 am
FOLLOW-UP on SQLite usage
Any simpler way?

Yes!
Use Parameters. I think i used Parameters in the example i've posted.
With Parameters you don't have to juggle around Formats, since the Parameters are taking care of that.
AIRCODE!
Code: [Select]
SQLQuery.SQL.text:='INSERT INTO MyTable (FloatField, DateTimeField) VALUES (:FloatParam, :DateParam);';
SQLQuery.ParamByName('FloatParam').AsFloat:=myFloat;
SQLQuery.ParamByName('DateParam').AsDateTime:=myDateTime;
SQLQuery.ExecSQL;
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 27, 2019, 09:52:21 am
OK, I did not really see the colons in your example...
Anyway I also read up on SQLite and the sqldb in Lazarus so I have done this:

Code: Pascal  [Select]
  1.   SQL := 'INSERT INTO TASKS ' +
  2.          '(TaskName, CmdFileName, ResultFileName, TaskRepeat, RepeatInterval, MaxRunTime, StartTime, ' +
  3.          'Enabled, NextCmd, ContactRes, ErrorCnt, ScaleFactor, MeasSettingID) ' +
  4.          'VALUES (:TaskName, :CmdFileName, :, :TaskRepeat, :RepeatInterval, :MaxRunTime, :StartTime, ' +
  5.          ':Enabled, :NextCmd, :ContactRes, :ErrorCnt, :ScaleFactor, :MeasSettingID);';
  6.  
  7.   FQuery.SQL.Text := SQL;
  8.   FQuery.Prepare;
  9.   FQuery.Params.ParamByName('TaskName').AsString := TS.Name;
  10.   FQuery.Params.ParamByName('CmdFileName').AsString := TS.CmdFileName;
  11.   FQuery.Params.ParamByName('ResultFileName').AsString := TS.ResultFileName;
  12.   FQuery.Params.ParamByName('TaskRepeat').AsInteger := TS.TaskRepeat;
  13.   FQuery.Params.ParamByName('RepeatInterval').AsInteger := TS.TaskRepeatInterval;
  14.   FQuery.Params.ParamByName('MaxRunTime').AsInteger := TS.MaxRunTime;
  15.   FQuery.Params.ParamByName('StartTime').AsDateTime := TS.StartTime;
  16.   FQuery.Params.ParamByName('Enabled').AsInteger := BoolToInt(TS.Enabled);
  17.   FQuery.Params.ParamByName('NextCmd').AsInteger := TS.NextCmd;
  18.   FQuery.Params.ParamByName('ContactRes').AsInteger := BoolToInt(TS.ContactResTest);
  19.   FQuery.Params.ParamByName('ErrorCnt').AsInteger := TS.ErrorCount;
  20.   FQuery.Params.ParamByName('MeasSettingID').AsInteger := TS.MeasSettingsID;
  21.   FQuery.Params.ParamByName('ScaleFactor').AsFloat := TS.ScaleFactor;
  22.   FQuery.ExecSQL;

Notice the Prepare call after setting SQL of the FQuery component.
Apparently this is required in order to use parameters but it is missing from your example.
Is this because it is really not needed?

I have not yet been able to test the call because of other parts of the program not yet done.
But it compiles without errors at least.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 27, 2019, 12:13:09 pm
I don't think it's necessary to use prepare (at least i never used it).

but something else: You do know, that you have to call the Commit-Method of the Transaction-Object to actually write the changes permanently to the DB?
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 27, 2019, 06:52:53 pm
I don't think it's necessary to use prepare (at least i never used it).

but something else: You do know, that you have to call the Commit-Method of the Transaction-Object to actually write the changes permanently to the DB?
No, that is not something I have thought about, otoh I have actually put it into the destructor of my db object, so it kind of worked for what I was doing initially.
Now I have added a second Commit in the InitDb function.
In my application I initialize and destroy the db stuff like this:

Code: Pascal  [Select]
  1. constructor TMonitorDb.Create(dbfile: string);
  2. begin
  3.   FConn := TSQLite3Connection.Create(NIL);
  4.   FTrans := TSQLTransaction.Create(NIL);
  5.   FQuery := TSQLQuery.Create(NIL);
  6.   FConn.Transaction := FTrans;
  7.   FTrans.DataBase := FConn;
  8.   FQuery.Transaction := FTRans;
  9.   FQuery.DataBase := FConn;
  10.   FConn.DatabaseName := DbFile;
  11.   FConn.CharSet := 'UTF8';
  12.   InitDb(dbfile);  //How to deal with failure?
  13. end;
  14.  
  15. function TMonitorDb.InitDb(DbFile: string): boolean;
  16. var
  17.   NewDatabase: boolean;
  18. begin
  19.   Result := false;
  20.   try
  21.     NewDatabase := not FileExists(DbFile);
  22.     FConn.Open;
  23.     Result := FConn.Connected;
  24.     if Result and NewDatabase then //Database not existing, so create tables
  25.     begin
  26.       Log.LogStd('Populating new database ' + DbFile);
  27.       FQuery.SQL.Clear;
  28.       //Use queries to build database
  29.       FQuery.SQL.Text := SQL_CREATE_TASKS;
  30.       Log.LogDeb('TASKS');
  31.       FQuery.ExecSQL;
  32.       FQuery.SQL.Text := SQL_CREATE_MEASSETTINGS;
  33.       Log.LogDeb('MEASSETTINGS');
  34.       FQuery.ExecSQL;
  35.       FQuery.SQL.Text := SQL_CREATE_CMDFILES;
  36.       Log.LogDeb('CMDFILES');
  37.       FQuery.ExecSQL;
  38.       FQuery.SQL.Text := SQL_CREATE_TASKLOG;
  39.       Log.LogDeb('TASKLOG');
  40.       FQuery.ExecSQL;
  41.       FTrans.Commit;
  42.     end;
  43.     Result := true;
  44.   except
  45.     FLastError := 'Exception! Could not initialize database ' + DbFile;
  46.     Log.LogErr('Exception! Could not create tables!');
  47.   end;
  48. end;
  49.  
  50. destructor TMonitorDb.Destroy;
  51. begin
  52.   if FConn.Connected then
  53.   begin
  54.     FTrans.Commit;
  55.     FQuery.Close;
  56.     FConn.Close;
  57.   end;
  58.   FQuery.Free;
  59.   FTrans.Free;
  60.   FConn.Free;
  61.   inherited Destroy;
  62. end;
  63.  

I guess your advice is to add a FTrans.Commit at the end of all of the specific functions I am writing in order to add modify and delete records in the database?
Or should they be after each execution of an FQuery object method?
Like ExecSQL and Open etc.
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 27, 2019, 09:55:14 pm
Another item I just ran across when coding:
The task object contains a property that is actually a packed record type containing a mix of data totalling 32 bytes.
I would like to store that in the database as a single binary value, is that possible?
For example if I set it as a BLOB data type how exactly can I read and write it into the database?

The record consists of:
8  one-byte size parameters
1 single precision float value
3 2-byte (smallint) parameters
1 14-byte array of byte containing various data.

This packet is going to be transferred as-is to the equipment controlled by the application.

So it is important that the 32 bytes are intact from input into the database and back out again.

How can this be accomplished with SQLite?
A simple example will do, all I can find on the net is related to storing images...
And this attempt failed:
(Here TS.MeasSettings is a record type (packed as described above)..

Code: Pascal  [Select]
  1.     TS.MeasSettings := FQuery.FieldByName('MeasSettings').AsBLOB;
Error message:
Code: [Select]
dbhandler.pas(232,59) Error: identifier idents no member "AsBLOB"
So I tried this instead:
Code: Pascal  [Select]
  1. var
  2.    ARR: TBytes;
  3. begin
  4.     ....
  5.     SetLength(ARR, SizeOf(TSSMeasSettings));
  6.     ARR:= FQuery.FieldByName('MeasSettings').AsBytes;
  7.     Move(ARR[0], TS.MeasSettings, SizeOf(TSSMeasSettings));

This does not generate any compilation errors, but is it really what one should do?
I am not comfortable using Move(), but when nothing else seems to work that is what I have done in the past as well...
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 28, 2019, 08:05:39 am
I guess your advice is to add a FTrans.Commit at the end of all of the specific functions I am writing in order to add modify and delete records in the database?
Or should they be after each execution of an FQuery object method?
Like ExecSQL and Open etc.
IMO, that's a question that shouldn't even be asked.
Think about it: Your App runs with your code for, say, a week and collects data, but never "commits" it.
And suddenly the construction worker outside decides to cut the power supply......
A rule of thumb (at least for me): If you're sure about your data --> commit immediately. This applies especially for INSERTS.
For UPDATE's and DELETE's: --> the "classic" scenario --> Do the UPDATE/DELETE (don't commit!) --> Ask User "Are you sure?" --> Yes=Commit / No=Rollback
If there is no User-Feedback required --> commit immediately
Bottom Line: Commit after each "ExecSQL"
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 28, 2019, 08:24:06 am
Another item I just ran across when coding:
The task object contains a property that is actually a packed record type containing a mix of data totalling 32 bytes.
I would like to store that in the database as a single binary value, is that possible?
For example if I set it as a BLOB data type how exactly can I read and write it into the database?

The record consists of:
8  one-byte size parameters
1 single precision float value
3 2-byte (smallint) parameters
1 14-byte array of byte containing various data.

This packet is going to be transferred as-is to the equipment controlled by the application.

So it is important that the 32 bytes are intact from input into the database and back out again.

How can this be accomplished with SQLite?
A simple example will do, all I can find on the net is related to storing images...
And this attempt failed:
(Here TS.MeasSettings is a record type (packed as described above)..

Code: Pascal  [Select]
  1.     TS.MeasSettings := FQuery.FieldByName('MeasSettings').AsBLOB;
Error message:
Code: [Select]
dbhandler.pas(232,59) Error: identifier idents no member "AsBLOB"
So I tried this instead:
Code: Pascal  [Select]
  1. var
  2.    ARR: TBytes;
  3. begin
  4.     ....
  5.     SetLength(ARR, SizeOf(TSSMeasSettings));
  6.     ARR:= FQuery.FieldByName('MeasSettings').AsBytes;
  7.     Move(ARR[0], TS.MeasSettings, SizeOf(TSSMeasSettings));

This does not generate any compilation errors, but is it really what one should do?
I am not comfortable using Move(), but when nothing else seems to work that is what I have done in the past as well...
I think you could create a TMemoryStream, and use its Write-Method
https://www.freepascal.org/docs-html/current/rtl/objects/tmemorystream.write.html

In the End it really depends what alignement your packed record has.

After that it should be a simple
(note: Aircode! Not tested!)
Code: [Select]
MyStream:=TMemoryStream.Create(nil);
MyStream.Write(MyPackedRecord, 32);
SQLQuery.ParamsByName('MyBlobData').LoadFromStream(MyStream, ftBlob);
SQLQuery.ExecSQL;
And reading it from the db is the other way round (Read_method of MemoryStream)
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 28, 2019, 11:53:47 am
I think you could create a TMemoryStream, and use its Write-Method
https://www.freepascal.org/docs-html/current/rtl/objects/tmemorystream.write.html

In the End it really depends what alignement your packed record has.

After that it should be a simple
(note: Aircode! Not tested!)
Several errors pop up:
1) TMemoryStream.Create takes no arguments.
2) SQLQuery.ParamsByName does not exist, it shall be FieldByName
3) LoadFromStream() does not exist for FQuery
4) (MyStream, ftBlob), ftBlob is not recognized 
Quote
Code: [Select]
MyStream:=TMemoryStream.Create(nil);
MyStream.Write(MyPackedRecord, 32);
SQLQuery.ParamsByName('MyBlobData').LoadFromStream(MyStream, ftBlob);
SQLQuery.ExecSQL;
And reading it from the db is the other way round (Read_method of MemoryStream)
I have not used streams in my programming earlier so I have to consult web pages...
The example given does not work...

And this example is the wrong way, my problem here is to read from the database, not to write to it.
That will be the task for the next method I will write.

I understand how to read bytes from and write to a memory stream, but not how to actually write the stream with data from a Query..
In the previous query calls in my code it is  a simple assignment from the query to a variable specifying the AsSomething.
But In this case, how can it be done???
I tried to google it but find only non-working Delphi examples...
What do I do with the below code to READ data from the query into the TS.MeasSettings record?

Code: Pascal  [Select]
  1.     //Alternate solution using memory stream:
  2.     MStr:=TMemoryStream.Create;
  3.     //How to get the data from FQuery into the memory stream?
  4.     MStr.Write(FQuery.FieldByName('MeasSettings'), SizeOf(TSSMeasSettings)); //Does this really work?
  5.     MStr.Write(FQuery.FieldByName('MeasSettings').AsBytes, SizeOf(TSSMeasSettings)); //Or is this how it must be done?
  6.     //Move data from stream to record:
  7.     MStr.Read(TS.MeasSettings, SizeOf(TSSMeasSettings));
  8.     MStr.Free;

It does not throw compile errors, but since I do not have a write to database method yet I cannot check if the correct data is stuffed into the database and read back...
And it seems like there is a problem to use memory stream for writing to the database blob field..


Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 28, 2019, 12:07:39 pm
Look here at the last answer: https://stackoverflow.com/questions/14709685/how-can-i-do-insert-into-blob-in-sqlite-using-delphi-xe3
Yes, he uses a Picture-file for his example, but the source of the MemoryStream doesn't matter
btw: i had a typo in my example.
it must ParamByName, not ParamsByName
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: Zvoni on November 28, 2019, 02:41:26 pm
OK,
here is a working sample:
Loading and Saving BLOB's to sqlite

No idea if there is a more elegant way
Title: Re: Using SQLite database in command line program on RPi4B?
Post by: BosseB on November 28, 2019, 09:23:45 pm
Quote
here is a working sample:
Loading and Saving BLOB's to sqlite
Thanks, I will have a look!

Later:
Yes it does compile and it seems like a very good way to handle blob fields AFAICT at the moment.
I still do not have a database containing the data to read back so I will have to come back later.

This code compiles successfully using your suggested way (and after adding db to uses):
Code: Pascal  [Select]
  1. //Reading data from database:
  2. var
  3.   MStr: TStream;
  4.   ...
  5. begin
  6.   ...
  7.     MStr := FQuery.CreateBlobStream(FQuery.FieldByName('MeasSettings'), bmRead);
  8.     MStr.Read(TS.MeasSettings, SizeOf(TSSMeasSettings));
  9.     MStr.Free;
  10.    ....
  11. end;
  12.  
  13. //Writing data to database:
  14. var
  15.   MStr: TMemoryStream;
  16.   ...
  17. begin
  18.   ...
  19.   //Alternate solution using stream
  20.   MStr:=TMemoryStream.Create;
  21.   MStr.Write(TS.MeasSettings, SizeOf(TSSMeasSettings));
  22.   FQuery.ParamByName('MeasSettings').LoadFromStream(MStr, ftBlob);
  23.   MStr.Free;
  24.  


<DELETED stupid question, a simple Quick Compile showed it is illegal to do typecast like that...>