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?

 

[Diceman]

Hello @MichalK73
Today I needed some information about MariaDB solutions (nothing to do with connector J), but after getting the answer to my original questions, I then decided to give Juan Sanz from the MariaDB support team a scenario via one of those annoying chat windows through the browser.

The conclusion is that @Erel is 100% correct and you can in fact bundle MariaDB Connector J with any software created by yourself for clients even is you are financially benefiting from it.

I gave Juan Sanz the scenario and here is his answer to me via the chat window.

Spoiler: Juan Sanz from MariaDB support chatting to me via a support chat window - Last part of out chat...

Juan Sanz 09:51
Do you know our Knowledge Base?

Peter 09:51
Your knowledge is confusing, I just need a simple answer to the simple question above? Thank you...

Juan Sanz 09:51
Ok give me a second please

Peter 09:52
ok

Juan Sanz 09:53
Our connector is GPL

so you can use it freely

even if it is a bespoke solution you are benefiting commercially for

it's yours to enjoy and exploit

just like the rest of MariaDB Comunity

Peter 09:54
Oh okay, thank you very much. You have a nice day, and keep up the great work...
Read

Juan Sanz 09:55
you're very welcome, Peter

you too, have a nice one

Juan Sanz has closed the chat.

So you can indeed bundle MariaDB Connector J with your bespoke solutions for clients and there will be absolutely no comeback from doing that, and that is exactly what I've been doing for the last couple of years anyway with my closed source applications.


Enjoy...

A no fee client is good news. But are there any fees for running a MariaDb server? If so, how are the fees assessed? Per connection? Per thread? Per user?
TIA

 

[MichalK73]

A no fee client is good news. But are there any fees for running a MariaDb server? If so, how are the fees assessed? Per connection? Per thread? Per user?
TIA

It has already been said that MariaDB is completely free even for commercial purposes.

 

[Peter Simpson]

A no fee client is good news. But are there any fees for running a MariaDb server?

Probably, but I always run MySQL server (Free) with MariaDB Connector J (Free) which is fine to do

Btw I've donated to the MariaDB foundation...

 

[rgarnett1955]

Hi,

SQLite is probably not the best choice as mySQL is free and provides a growth path if more than one concurrent users. mySQL is used in a lot of point of sale apps, medical practice apps (Open Dental) and has a lot more features than sqLite and as for sqLite there are a lot of free tools to assist in database management. SqLite is better suited as a database for standalone apps where small size, speed and simplicity are required. Firefox uses sqlite to store its operational data. SQLite can also be used in medium scale embedded systems with 500 k or more of ram.

 

[tchart]

No one seems to have mentioned it but for me personally I would opt for sqlite over mysql mainly for simplicity and to reduce support.

Not many customers are IT savvy. Imagine trying to tell them over the phone to stop/start a Windows service. Or tell them how to set up a new database login. Or open firewall. Or make an exception in antivirus etc. Or what happens when their PC crashes and the database is corrupt. All of these things are non issues with sqlite.

I use sqlite until it's no longer scalable.

I've had sqlite databases with gigabytes worth of data and they work fine (this was for parsing log files).

As long as your app is the one that's made the connection to the database I don't see why it shouldn't be a workable solution.

Just my 2 cents

 

[jroriz]

No one seems to have mentioned it but for me personally I would opt for sqlite over mysql mainly for simplicity and to reduce support.

Not many customers are IT savvy. Imagine trying to tell them over the phone to stop/start a Windows service. Or tell them how to set up a new database login. Or open firewall. Or make an exception in antivirus etc. Or what happens when their PC crashes and the database is corrupt. All of these things are non issues with sqlite.

I use sqlite until it's no longer scalable.

I've had sqlite databases with gigabytes worth of data and they work fine (this was for parsing log files).

As long as your app is the one that's made the connection to the database I don't see why it shouldn't be a workable solution.

Just my 2 cents

In your experience, would sqlite support simultaneous access by, say, 10 users?

 

[tchart]

In your experience, would sqlite support simultaneous access by, say, 10 users?

The answer always is "it depends".

As @Erel already stated there is no problem with multiple readers accessing the database.

In your case if your POS system is running on multiple clients (PC, Android etc) then the answer is no, SQLite is not appropriate if all of these clients are trying to connect and write over the network.

If you have multiple clients making their own connection to any database (SQLite, MySQL, whatever) and maintaining this connection then to me is bad practise and I would never implement something like this. Im sure many people will disagree with me on this but in reality having clients connect to a database this way is the old way of doing things. Will it work, yes of course it will but there are better ways to do it.

If I was doing this project I would have one master device (a PC?) that was the receiver of data and then have the clients connect to the master over https using jServer. If the POS is only sending data they can just call a REST end point on the master device to send the data via JSON. The master device can then write this data as it arrives. This way the master is the only one that has a database connection and this does suit SQLite just fine.

Same goes for reading data, why not just read the data over https using REST? Much easier.

In fact I would even go one further an make this a web application for the POS. Then there is even less to worry about in terms of set up or support on POS devices. If a device goes down? Then what? Just get a new machine and fire up the browser. Have a big event, buy a bunch of Android tablets.

Again just my 2 cents as there are always multiple ways to do things. You just need to do what you are comfortable with and use the richt techology for the chosen path.

 

[Diceman]

In your experience, would sqlite support simultaneous access by, say, 10 users?

Sqlite works well with jRDC2 server which allows access using HTTP over the local network or Internet. It is extremely fast and secure. Check it out.

 

[tchart]

Sqlite works well with jRDC2 server which allows access using HTTP over the local network or Internet. It is extremely fast and secure. Check it out.

Awesome. There you go database over http! I did not know about that @Diceman very cool.

 

[Diceman]

Awesome. There you go database over http! I did not know about that @Diceman very cool.

See my posts #31 and #34 of this thread. I've used jRDC2 for a couple of years and its great. Server CPU times for queries is around 1ms to 5ms when I execute 6 queries (1 record at a time). And of course jRDC2 is multi-threaded so it should be able to handle 100 concurrent users easily. And because no one has a direct connection to the server, they can't monopolize it (unless your server code itself tries to write or update millions of rows at a time and the database lock locks everyone else out until it finishes). The requests are in and out in just milliseconds. It is the perfect solution. It is a little more work setting it up, but it is a robust, fast and secure solution.