Recent

Author Topic: Using SQLite database in command line program on RPi4B?  (Read 2231 times)

BosseB

  • Jr. Member
  • **
  • Posts: 93
Using SQLite database in command line program on RPi4B?
« 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
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?


--
Bo Berglund
Sweden

MarkMLl

  • Sr. Member
  • ****
  • Posts: 307
Re: Using SQLite database in command line program on RPi4B?
« Reply #1 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
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

wittbo

  • Jr. Member
  • **
  • Posts: 79
Re: Using SQLite database in command line program on RPi4B?
« Reply #2 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.
-wittbo-
Lazarus 2.0.4  with FPC 3.0.4a
MBAir with MacOS 10.14.5
iMac with MacOS 10.13.6

devEric69

  • Full Member
  • ***
  • Posts: 164
Re: Using SQLite database in command line program on RPi4B?
« Reply #3 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)...
« Last Edit: November 21, 2019, 12:56:02 pm by devEric69 »
use: Ubuntu 18.04 + Laz. 1.8.5 + FPC 3.0.5 (64 bits).

BosseB

  • Jr. Member
  • **
  • Posts: 93
Re: Using SQLite database in command line program on RPi4B?
« Reply #4 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.
  • One config for the main application - this will probably stay as a file.
  • One description for each job that the system is set up to do, since there are an unknown number of jobs probably better in a DB
  • Several support files used by the jobs, content might be stored in a DB or stay on the file system
  • The result data from the jobs, typically saved as zip files on the file system, a list of these could be in the database
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?
--
Bo Berglund
Sweden

MarkMLl

  • Sr. Member
  • ****
  • Posts: 307
Re: Using SQLite database in command line program on RPi4B?
« Reply #5 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
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

devEric69

  • Full Member
  • ***
  • Posts: 164
Re: Using SQLite database in command line program on RPi4B?
« Reply #6 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.
« Last Edit: November 21, 2019, 03:42:56 pm by devEric69 »
use: Ubuntu 18.04 + Laz. 1.8.5 + FPC 3.0.5 (64 bits).

Zvoni

  • Sr. Member
  • ****
  • Posts: 302
Re: Using SQLite database in command line program on RPi4B?
« Reply #7 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?
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

BosseB

  • Jr. Member
  • **
  • Posts: 93
Re: Using SQLite database in command line program on RPi4B?
« Reply #8 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:
  • Design a remote measuring system using a RaspberryPi as controller.
  • This controller may operate by itself out in the wild powered by solar panels and batteries and no Internet connection at all.
  • It will implement a WiFi AP such that an operator getting close enough can connect his tablet to it and open the web interface for configuring jobs, watch progress and download job results.
  • Or it may also operate with internet connection and close to civilization.
  • When it is connected to the Internet it shall upload results to a server somewhere when measurements are done.

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.
--
Bo Berglund
Sweden

BosseB

  • Jr. Member
  • **
  • Posts: 93
Re: Using SQLite database in command line program on RPi4B?
« Reply #9 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?)
« Last Edit: November 21, 2019, 04:27:04 pm by BosseB »
--
Bo Berglund
Sweden

MarkMLl

  • Sr. Member
  • ****
  • Posts: 307
Re: Using SQLite database in command line program on RPi4B?
« Reply #10 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
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

Zvoni

  • Sr. Member
  • ****
  • Posts: 302
Re: Using SQLite database in command line program on RPi4B?
« Reply #11 on: November 21, 2019, 05:23:20 pm »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

devEric69

  • Full Member
  • ***
  • Posts: 164
Re: Using SQLite database in command line program on RPi4B?
« Reply #12 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) 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 :( .
« Last Edit: November 21, 2019, 05:44:30 pm by devEric69 »
use: Ubuntu 18.04 + Laz. 1.8.5 + FPC 3.0.5 (64 bits).

BosseB

  • Jr. Member
  • **
  • Posts: 93
Re: Using SQLite database in command line program on RPi4B?
« Reply #13 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.
--
Bo Berglund
Sweden

Zvoni

  • Sr. Member
  • ****
  • Posts: 302
Re: Using SQLite database in command line program on RPi4B?
« Reply #14 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:~$
« Last Edit: November 21, 2019, 06:23:24 pm by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts