B4J Question Would you recommend SQLite?

[jroriz]

Well, I intend to rewrite a small POS system for B4J.
It will be used by small restaurants, so I don't expect a lot of competing readings and recordings, or a lot of data, and it will be used on a local network.
My intention is to use SQLITE, to facilitate the installation, but I never used this database on a daily basis.
Does anyone here have experience with SQLITE?
Can I trust this database in the medium / long term, when it starts to grow?
Is there anything I should be concerned about?

 

[Peter Simpson]

Well, I intend to rewrite a small POS system for B4J.
It will be used by small restaurants, so I don't expect a lot of competing readings and recordings, or a lot of data, and it will be used on a local network.
My intention is to use SQLITE, to facilitate the installation, but I never used this database on a daily basis.
Does anyone here have experience with SQLITE?
Can I trust this database in the medium / long term, when it starts to grow?
Is there anything I should be concerned about?

No don't use SQLite,
I recommend that you use a relational database management system like MySQL or MariaDB, they are free and can be setup in just a few minutes. There are plenty of users on here that can help you do set them up.

Over the years I've developed multiple POS, Invoice and stock control systems for clients, if the hardware is in a building then the choices are clear as far as I'm concerned.

Even though SQLite will do just fine for a single device, I would never use SQLite when it comes to that type of application. Being able to expend in the future is extremely important, so I personally always go the MySQL route. Yes of course you can go the SQLite route, especially if it's only for one connected piece of hardware like a single till. But even then I would still go the MySQL route, especially as it will make connecting other devices to the same database in the future a lot easier, but that's for the future.

BTW when I say MySQL or MariaDB databases I mean in house and not on the internet.

✅ JDBCSQL Connector/J - Connect directly to MySQL, MSSQL, PostgreSQL, MariaDB and Oracle databases

Hello fellow B4X'ers, Here is some example code for connecting your B4J projects to 5 different types of database engines, this tutorial is for learning purposes only and nothing else. Disclaimer: This code with some minor changes may also work in B4A. MySQL and MSSQL connectors definitely do...

www.b4x.com

Question: How exactly is the hardware setup going to be configured?

 

[Harris]

IMHO (again) - SQLite is great for mobile devices where one needs a DB. It comes pre-installed and is designed for them - NOT the backend server.
MySQL is perfect for the backend server - on small or medium (to large) projects.

Do you know what WAL is? Neither do I.... (or actually want to from what I have read here on this forum...)

@udg has a POS system that he has built in B4J. Check it out on the B4J "Share My Creation"...

POS management

Hi all, since last December I deployed a B4J-based solution in order to manage 16 POS cash registers at the Cornaredo Stadium in Lugano, home of the FC Lugano, a football club partecipating SFL's Super League and UEFA Champions League. The solution is data-driven, so almost anything is set up...

www.b4x.com

 

[Erel]

SQLite in wal mode can be used in server solutions. However I also recommend using MySQL, even just for its useful tool such as PHPMyAdmin.
You can install it on any OS.

 

[MichalK73]

I used SQLite for 2-3 ABMaterial servers with client support (shop). The longest one works over 1.5 years and nothing happens. Another supports 500 users but works for 3 months. Nothing bad is happening either. But these are services with relatively small database polling by system / users etc. Where I know that polling is frequent, MariaDB from HicarioCP uses it (very efficient even on the cheapest equipment). I am talking about queries up to 1-100 and more database queries for a second. Here the base is growing quite quickly for me.

I tested HicarioCP for SQLite and it also works which speeds up SQLite but I didn't have a need for now. Generally, when the client reports that the system is clogging, the database transfer is a matter of transferring the structure and data to MariaDB and setting up a new connection to the database in the server's program. I don't have to change anything else using the DBM library. That's all. The transition from SQLite to MariaDB is even only half a business day, so it's not a problem either.

 

[jroriz]

No don't use SQLite,
I recommend that you use a relational database management system like MySQL or MariaDB, they are free and can be setup in just a few minutes. There are plenty of users on here that can help you do set them up.

Over the years I've developed multiple POS, Invoice and stock control systems for clients, if the hardware is in a building then the choices are clear as far as I'm concerned.

Even though SQLite will do just fine for a single device, I would never use SQLite when it comes to that type of application. Being able to expend in the future is extremely important, so I personally always go the MySQL route. Yes of course you can go the SQLite route, especially if it's only for one connected piece of hardware like a single till. But even then I would still go the MySQL route, especially as it will make connecting other devices to the same database in the future a lot easier, but that's for the future.

BTW when I say MySQL or MariaDB databases I mean in house and not on the internet.

✅ JDBCSQL Connector/J - Connect directly to MySQL, MSSQL, PostgreSQL, MariaDB and Oracle databases

Hello fellow B4X'ers, Here is some example code for connecting your B4J projects to 5 different types of database engines, this tutorial is for learning purposes only and nothing else. Disclaimer: This code with some minor changes may also work in B4A. MySQL and MSSQL connectors definitely do...

www.b4x.com

Question: How exactly is the hardware setup going to be configured?

That was the kind of answer I was looking for.
I myself have been using MySQL for many years (and I am very happy). But since I started using B4J I am very surprised by the results of SQLite.

On your question, if I understand correctly, the system expects to be installed on average computers, as I do not intend to require restaurants to invest in servers.

And thank you very much for your enlightening answer.

 

[jroriz]

IMHO (again) - SQLite is great for mobile devices where one needs a DB. It comes pre-installed and is designed for them - NOT the backend server.
MySQL is perfect for the backend server - on small or medium (to large) projects.

Do you know what WAL is? Neither do I.... (or actually want to from what I have read here on this forum...)

@udg has a POS system that he has built in B4J. Check it out on the B4J "Share My Creation"...

POS management

Hi all, since last December I deployed a B4J-based solution in order to manage 16 POS cash registers at the Cornaredo Stadium in Lugano, home of the FC Lugano, a football club partecipating SFL's Super League and UEFA Champions League. The solution is data-driven, so almost anything is set up...

www.b4x.com

That's what I was looking for!
I would like to thank you for the reply, as much as I thanked @Peter Simpson . Then post #6 is also yours!

And I'm going to take a look at #peter's POS, for sure. Great tip!

 

[Peter Simpson]

And I'm going to take a look at #peter's POS, for sure. Great tip!

That particular POS isn't of my making, that awesome system was created by @udg ?

 

[jroriz]

I used SQLite for 2-3 ABMaterial servers with client support (shop). The longest one works over 1.5 years and nothing happens. Another supports 500 users but works for 3 months. Nothing bad is happening either. But these are services with relatively small database polling by system / users etc. Where I know that polling is frequent, MariaDB from HicarioCP uses it (very efficient even on the cheapest equipment). I am talking about queries up to 1-100 and more database queries for a second. Here the base is growing quite quickly for me.

I tested HicarioCP for SQLite and it also works which speeds up SQLite but I didn't have a need for now. Generally, when the client reports that the system is clogging, the database transfer is a matter of transferring the structure and data to MariaDB and setting up a new connection to the database in the server's program. I don't have to change anything else using the DBM library. That's all. The transition from SQLite to MariaDB is even only half a business day, so it's not a problem either.

So would you recommend SQLite for a small POS system, for up to 10 users with average computers as servers?

 

[MichalK73]

It all depends on many factors.

Perform an expected load analysis. I would always assume the biggest load plus 20% excess. I don't know how many tables, views, how many columns you will have. How will you solve the clients' connection to the base? I think jRDC2 will be the best solution and so flexible that in the future it did not have to be to change clients, only a connection in jRDC2 from SQLite to MySql. I think jRDC2 will be enough for 10 client positions. The size of the data is also probably not great, the matter of making good dictionaries. To reduce the use of dictionaries with SQLite, I use MAP variables as dictionaries. The dictionary changes very rarely but very often is read from it to avoid duplicate data in other tables. When starting the program, it loads dictionaries for map type variables and I use them in the program. Map variables are in the main memory so there is nothing to compare to the download speed from SQLite or MariaDB even locally. Such small nuances increase the speed and greatly relieve the SQLlite server. This is just an example of how optimization can be used. In POS there are probably a lot of tables (dictionaries) that do not change and until you ask for such use.
From experience I know from other programs (not mine) that file bases clog up at large sizes. The client once had a database on the server (rather similar to jRDC) and clients locally (20 workstations) with internet connections (about 10). They had tens of thousands of records and some tables hundreds of thousands and everything worked.

Maybe this will help you in choosing a database.

MySQL vs SQLite

Guide to the Top Difference between MySQL vs SQLite. Here we also discuss the key differences of MySQL vs SQLite with infographics.

www.educba.com

https://stackoverflow.com/questions/4539542/sqlite-vs-sql-server

 

[Peter Simpson]

for a small POS system, for up to 10 users with average computers as servers?

I would personally say 1 basic/average computer acting as a MySQL server with some kind of backup solution for the database.

I personally always use a RAID 1 configurable Dell machine and also an external backup solution. In your case just a normal PC with an external backup solution for the MySQL database would be sufficient, even if it's a cheap external backup drive...

 

[jroriz]

It all depends on many factors.

Perform an expected load analysis. I would always assume the biggest load plus 20% excess. I don't know how many tables, views, how many columns you will have. How will you solve the clients' connection to the base? I think jRDC2 will be the best solution and so flexible that in the future it did not have to be to change clients, only a connection in jRDC2 from SQLite to MySql. I think jRDC2 will be enough for 10 client positions. The size of the data is also probably not great, the matter of making good dictionaries. To reduce the use of dictionaries with SQLite, I use MAP variables as dictionaries. The dictionary changes very rarely but very often is read from it to avoid duplicate data in other tables. When starting the program, it loads dictionaries for map type variables and I use them in the program. Map variables are in the main memory so there is nothing to compare to the download speed from SQLite or MariaDB even locally. Such small nuances increase the speed and greatly relieve the SQLlite server. This is just an example of how optimization can be used. In POS there are probably a lot of tables (dictionaries) that do not change and until you ask for such use.
From experience I know from other programs (not mine) that file bases clog up at large sizes. The client once had a database on the server (rather similar to jRDC) and clients locally (20 workstations) with internet connections (about 10). They had tens of thousands of records and some tables hundreds of thousands and everything worked.

Maybe this will help you in choosing a database.

MySQL vs SQLite

Guide to the Top Difference between MySQL vs SQLite. Here we also discuss the key differences of MySQL vs SQLite with infographics.

www.educba.com

https://stackoverflow.com/questions/4539542/sqlite-vs-sql-server

The system is intended to be a local desktop application, not online client server.

 

[Harris]

The system is intended to be a local desktop application, not online client server.

Hummm, however not online (internet based), with many terminals - will it be a local network solution (client / server)?
Anyways, good luck with your new build.

 

[keirS]

I wouldn't use SQLite for such a solution. I would use the H2 database. You can embed this within your application just like SQLite or you can run it as a server because it is written entirely in Java It has far better support for data types than SQLite so includes a decimal type that is far better suited to storing currency values. From the description of you application MySQL would be over the top for your needs.

 

[jroriz]

Verdict: MySQL

 

[Diceman]

I agree with the others, don't use Sqlite for a multiuser database.
I used MySQL 10 years ago and for transaction support you needed to use the InnoDb engine which is a pain to set up and isn't as fast as the MYISAM engine. MySQL requires a license ($2000) for every commercial server you install which eats away at your profits. Unless you use the MySQL Community edition which is released under GPL license but you may have to open source your software. I think MariaDb also requires some sort of license fee. I left MySQL because when Oracle bought them out their licensing became draconian and I don't want the hassle of Oracle coming after me for unpaid royalties. I would prefer using PostgreSQL which is free to distribute in any form and works well with B4A. It has strong community support and a lot of excellent tools.

 

[Erel]

MySQL requires a license ($2000) for every commercial server you install which eats away at your profits. Unless you use the MySQL Community edition which is released under GPL license but you may have to open source your software

That's not accurate. You don't need to open source your code when you use the community edition unless you distribute a modified MySQL engine together with your project.

 

[Winni]

I'm afraid that Erel's assessment is not fully correct. The problem is not the server per se, the problem are the connectors.

MySQL is either licensed under the GPL or under a commercial license that you must purchase. MariaDB is only licensed under the GPL. The devilish problem with those servers are the so-called connectors that your application needs to access the server: They are also licensed under the GPL, unless you purchase a commercial license. The GPL by definition is a viral license: Using these connectors to access the server from your application either requires your application to also be released under the GPL - read: It MUST be Open Sourced under the GPL - or you contact Oracle and purchase licenses so that you are allowed to use the connectors in a proprietary/closed source application.

Repeat: If you want to deploy your application as closed-source software that uses MySQL through the official connectors, you MUST buy the appropriate licenses from Oracle, for the connectors and for the database server.

Of course, you could use a proxy that accesses the MySQL server. But that's an ugly, unprofessional hack and only adds needless complexity to the setup just to avoid licensing issues.

SQLITE is not only free and open source, it is in the PUBLIC DOMAIN -- you can do with it whatever you like. There's a reason why even Apple uses SQLITE as an embedded database engine (they did so for example in their now discontinued professional photo management software APERTURE). SQLITE --IS-- battle tested. And it's commercially safe for you to use it (which is the whole idea why the author put it in the public domain).

If you want to use a risk-free Open Source database server that can easily compete with MySQL or Mariadb on --ALL-- levels and that does not have ANY licensing risks, then use PostgreSQL. It has a steeper learning curve than MySQL, though. But it comes with a business-friendly BSD license.

 

[Erel]

There is indeed a more delicate point about the jdbc drivers.

MariaDB is only licensed under the GPL

MariaDB connectors are distributed under LGPL which means that you can distribute the jars together with your closed source application.
See their faq: https://mariadb.com/kb/en/licensing-faq/

Based on my understanding you can also use MySQL in a closed source application as long as you don't distribute the driver jar yourself.
I'm not sure whether the OP plans to distribute the server side at all.

 

[MichalK73]

On the other hand, to avoid the Oracle license I used the connector from MariaDB to connect to MySql (because it was the client's one) and it worked.