A Modest Proposal

8 posts / 0 new
Last post
KW2P
A Modest Proposal

It's been 40 years since coding anything in Pascal. I was wondering if there are wrappers or bindings that enable calling C library functions from the Pascal that CQR is written in. I assume there are. I really like CQR and I'm considering creating a version of my own with the code refactored to use SQLite instead of MySQL.

SQLite eliminates dependence on unreliable external packages like MySQL. It's small, fast, and linked directly into the program. I switched to using it for all my database needs years ago.

Among the things SQLite has going for it is it's the most widely deployed and extensively tested database in the world. There are billions of instances running right now on every Android device in the world. Everything stored on Android is in an SQLite database. US government agencies have been switching over to it to eliminate dependence on flaky outside vendors and problems exactly like the problems we're having with CQR. The US Navy has switched onboard ship systems to SQLite. Maintenance systems, damage control, fire suppression, weapons and fire control, and navigation on most US ships now use SQLite. Soon, all will. What's more, the SQLite file format (along with CSV and XML) is one of the few approved by the US Library of Congress for long term archival storage of data. This is because it's stable.

The one thing SQLite does not do is support concurrency. CQR obviously has no need for that. Most applications don't.

What do you think?

/phil

oh1kh
A Modest Proposal

HI !
Then it is a good time to recall Pascal :-)
I was using it at school with "big mainframe computer" that was weaker than simplest smartphone today and had only Teletypewriter terminals and paper stripe puncher to save and load programs (that exeeded storage quota).
After that used Borland Turbo Pascal 5.5 a bit, but then came a long cap. Tried first versions of Delphi, and Kylix when moved to Linux, but that did not feel good.

Free pascal / Lazarus finally recovered programming and cqrlog has been a good training environment.

The concurrency is out of my sql knowledge. If you mean same time users for same database cqrlog does use parallel threads that do their own tasks with database.
Also using several cqrlogs with their own local settings, but common log database, is possible.

There is also cqrweblog that uses same database via web server. You can do you portable logging via smartphone, even at same time when Ham shack computer uses standard cqrlog (with same external mysql server) https://github.com/dl8bh/cqrweblog

Actually the problem with cqrlog is that it is running mysql_safe thread that hold its data in ~/.config/cqrlog/database for easy backups. With some linuxes security settings prevents this, some not.
If external mysql server is used instead there are no problems but backups are more difficult and also sql user must be created for external sql server.

In addition Lazarus sql unit is a bit out of date and does not work properly any more with mysql 8.0, but does with MariaDB (sql 5.7 needed)

But nothing prevents you to make things in your way. Pascal source has around 2.5 million characters with nearly same amount in graphical definitions (lfm files).

--
Saku
OH1KH

OK1MLG
A Modest Proposal

Hi Phil, Saku,

first of all, thank Petr, Saku and other contributors for great job named CQRLog.

I have been using the log for several years and, like others, I see that it is harder and harder to keep the CQRLog running after upgrading an operation system. Each distribution is specific and it is difficult to prepare one package for all.

Based on my experiences, SQLite is good for single-writer/multiple-reader applications. A concurrency access for multiple-writer is possible but to be honest, the support is not so good as in case of MySQL or Postgresql. You can used some kind of SQL serialization for SQLite but an application has to be designed for it. Therefore I think CQRLog with SQLite is a totally new project - maybe CQRLog V2 written in scripting language for rapid development with wide base of contributors.

Excuse my curiosity, maybe it has already been answered, but why CQRLog starts MySQL server from application?
Is it not easier to have a well-tuned external database and the application that connects to it than application what is starting database server from application? Why does application use HOME folder to store database files instead of default path?

I wish you Merry Christmas, Happy New Year and many DXs.

Lada

KW2P
Hello Lada,

Hello Lada,

I haven't studied the code but I doubt that CQRlog requires any concurrency from the database. This is not a Walmart store that might have 50 asynchronous transactions, or more, happening simultaneously. CQRlog doesn't need any of the immense power of a database like MySQL. It needs something fast, rock-solid, and portable, like SQLite or BerkeleyDB. CQRlog is using only a tiny fraction of the power of MySQL, but must carry the burden of a complex package that is normally maintained by professonal IT personnel. Not to deprecate SQLite, which can handle databases over 200 terabytes in size.

/p

ok2cqr
ok2cqr's picture
Re: A Modest Proposal

Hi Lada,

first versions of CQRLOG used Firebird as a database server, user's data were stored in the $HOME because of easier backup. Also after a system reinstall, it was easier to not forget to backup the database when it was in your home directory. Also installing the application was much easier.

With version 1.0 CQRLOG moved to MySQL. As far as I remember the main reason was creating DEB packages. As far as I remember there were not DEB packages for Firebird in Debian repository. After a lot of testing if SQLite I decided to use MySQL. I think there is still an SQLite related branch in the git repository when I tried to use it.

I think that MySQL was a good choice but I did one big mistake - saving the database in the user's home directory. I should use the installed MySQL server and develop user-friendly backup for CQRLOG data. Right now I don't know how to fix the mistake and help users to move to MySQL server in the system. Any ideas are very appreciated.

Personally, I use CQRLOG on several computers with one central database running on our home server that provides also other services like DLNA, backup, running OwnCloud etc. Using SQLite instead of MySQL wouldn't allow this. CQRLOG could probably use MySQL embedded but I have no idea how to get it to work on numerous Linux distributions.

Right now CQRLOG has these main issues:

- MySQL data in the user's HOME directory
- GTK2 is getting obsolete and will be removed from distributions, we have QT5 port but it's still unstable because of bugs in Lazarus
- DEB, RPM packaging. It's real pain to release a new version, we need snap and flatpack packages, any help is really welcome

Another problem is with me. I work like a dog and barely have the energy to be on the air. CQRLOG source code is not in good condition and needs a lot of refactoring, starting again doesn't have sense because creating so big app like CQRLOG would create years. Step by step refactoring is better and I already started but only with small changes. Right now solving the main issues would help a lot because I could release new version monthly and it would be more fun. Releasing a new version took me several hours and that is horrible :(.

Merry Christmas and Happy New Year!

73 Petr, OK2CQR

KW2P
Hello Petr!

Hello Petr!

Thank you for the detailed reply. I figured there was some history behind everything. There always is.

And, I don't mean to give the impression that I'm throwing rocks. It's easy for me to sit here and point at problems. Now that I have an upgraded machine and the right software installed properly, version 2.4.0 is working great. It was the difficulty of getting to this point that's the problem. And, the problem is not with CQRlog itself but external stuff that's all too easily misconfigured on a given system. The fact that I'm here discussing it shows that I like CQRlog and care about its future. :-)

I have no experience with Snap or Flatpack and until now have avoided both and any software that requires it. I imagine I can't keep that up forever. Hah.

/phil

OK1MLG
A Modest Proposal

Thank Peter for your answer. It makes sense what you wrote above. I do not think that it was a BIG mistake to place MySQL to user's home directory. It was a one way how to easily keep everything together.

I have implemented few  projects where MySQL has been placed in chroot (non-standard folder). But I can confirm that it is not the best choice in case when you want to upgrade Linux - It's a nightmare because everything has to be reconfigured manually.

How to migrate to external MySQL? Maybe I have a naive view, but there are several ways how to do it
1) create a shell script which will convert CQRLog local DB to external MySQL DB. You can just enter connection parameters and schema name of a new external database and database objects can be migrated via SQL export.

2) it can be also done directly via CQRLog GUI new menu item - something like File->Export->Export SQLs or Migrate to External DB.

Mentioned migration option(s) will be available in next release (e.g. 2.5)  with a note that a next release (e.g. 3.0) will use only an external database. As I wrote it is a naive approach but sometime a naive approach can help in the future.

I am not a snap or flatpack specialist but when you will use an external database then snap or flatpack has not to be used. You can easily used DEB package as today - BTW a snap is Canonical's packaging system which is used widely only in Ubuntu.

GTK2 is a more serious problem but this is a longer discussion.

It is long long time when I wrote an application in Pascal (especially in Turbo Pascal) - I have not experiences with Lazarus. Therefore, I cannot help you with CQRLog but I can help you with migration script if you find it useful. Just like with you, there is not much free time but the migration script is possible to implement.

@Phil: As I briefly look at the source code, I can see that CQRLog has many threads and every thread can potentially use database. I cannot confirm how many threads use the database but CQRLog is designed as multithread application. Therefore, it is not easy to switch to SQLite without rewriting or redesigning CQRLog.

I also found an interesting project - CloudLog by 2M0SQL where Log is designed as a service (web service). This approach is enterprise-orientated but it has several benefits like separating the database from the data view (view can be designed for many different devices). It also solves many problems with upgrades. On the other hand it does not contain so many features as CQRLog.

As Phile wrote, the fact that we are discussing it shows that we like CQRlog and care about its future.

Regards
Lada

KW2P
Ah, well, if it's

Ah, well, if it's multithreaded and more than one database operation can be in progress at the same time, then SQLite is not going to work. One could put an interface layer on SQLite that blocked, but that defeats the point of multithreading. Hahaha.