Recent

Author Topic: How to deploy SQLite programs?  (Read 8590 times)

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
How to deploy SQLite programs?
« on: January 09, 2017, 09:02:34 am »
Hello database experts.

I'm a self-taught programmer and my knowledge in database programming is weak. I understand how to use MS Access, TParadoxDataSet and TDbf. I rarely build programs using database and I do not build commercial programs. That's why I usually use TDbf which seems enough for my needs, although I know it has many limitations.

But I'm thinking to learn and use the 'better' options in the future. After some research, it seems SQLite is the best for me. But there are somethings I'm not really understand.

1. How to deploy programs that using SQLite?

In this page, it said SQLite needs client lib:
http://wiki.freepascal.org/Databases

But in these pages, they said SQLite is zero configuration and no need to deploy any additional library:
http://wiki.freepascal.org/SQLite
https://sqlite.org/serverless.html

So, I'm confused. Which one is correct, or can you please explain how deploy programs that build using SQLite?

2. Is it free for commercial projects?

I know SQLite is free, but is it also free for commercial usage? Do we need to pay license if we use it in commercial projects?

3. Is SQLite too overkill?

Programs I built are simply, single user without network connectivity. Sometimes I think SQLite is too overkill. For example, now I planning my game builder program which user can load all needed files (audios, images, etc) from different sources and save them in a single location or database. What do you think? Should I use SQLite or TDbf or write my own module using TZipFile?

4. How is MySQL compare to SQLite?

MySQL is very commonly used in web hosting (I'm a web designer). Is it easier or better?

5. Any other suggestion?

My requirement is simple:
- Easy deploy, preferable no install and no lib file
- Free, ok if not free for commercial but it should not be expensive

JanRoza

  • Hero Member
  • *****
  • Posts: 672
    • http://www.silentwings.nl
Re: How to deploy SQLite programs?
« Reply #1 on: January 09, 2017, 10:02:13 am »
All you need to supply is sqlite3.dll together with your program (and the database file of course).
At least for Windows, I have no experience with Linux.
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
       Lazarus 3.2 FPC 3.2.2
       CodeTyphon 8.40 FPC 3.3.1

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: How to deploy SQLite programs?
« Reply #2 on: January 09, 2017, 11:20:29 am »
SQLite is public domain, so no limitation what project can freely use it. Many big companies does, like Microsoft, and they probably also support the code somewhat. SQLite is solid software and easy to deploy. If you use Lazarus standard components (sqlDB) for SQLite you will need to install the appropriate driver for the operating system. The driver is one file (i.e sqlite3.dll for windows) put in any directory available to your program. Similar for Linux. Every copy of your program will need this file. There are also commercial SQLite components that doesn't need any external driver: https://www.devart.com/litedac/

Overkill? No, SQLite is light and fast and good even for just storing the application settings in a table.

MySQL is made for use by many simultaneous clients and makes sense for web and multiuser. SQLite is not really that, it locks the database when ONE user is working with it. There are ways around that, like using the (free) ZEOS components in read-only mode and only use read/write when necessary. Or you can perhaps code something that uses threads, events or critical sections that controls the access to SQLite. But if you need multitasking with many users you may be better off with for example MySQL and tools like MySQL Workbench (http://www.mysql.com/products/workbench/). For a real DBMS (database management system) the smoothest and most productive choice for you could certainly be MySQL. Tools, support, litterature and examples are plenty. If you move on to MySQL there will be slightly different syntax for some things and you have to learn that. Moving to PostGres, again there is slightly other syntax. PostGres is just as good, but less litterature and smaller market share. It has a pretty good tool (https://www.pgadmin.org/)
« Last Edit: January 09, 2017, 11:25:14 am by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

DonAlfredo

  • Hero Member
  • *****
  • Posts: 1739
Re: How to deploy SQLite programs?
« Reply #3 on: January 09, 2017, 11:21:02 am »
As a well-known advocate of the mORMot, I would like to point you towards the mORMot ... ;-)

https://synopse.info/fossil/wiki?name=SQLite3+Framework

Especially due to your remarks about the "no lib file".
With FPC, the mORMot offers you a static SQLite3 on various systems (windows/linux).
So, a single executable without the need for a lib.
The whole SQLite3 is included in this single exe.

And the ORM-part gives you easy data persistence without a single line of SQL.
See the samples. And the forum.

Thaddy

  • Hero Member
  • *****
  • Posts: 14204
  • Probably until I exterminate Putin.
Re: How to deploy SQLite programs?
« Reply #4 on: January 09, 2017, 01:08:46 pm »
1. How to deploy programs that using SQLite?
It is simply the difference between statically linking and dynamically linking. The latter needs a shared library (dll, so)
Quote
2. Is it free for commercial projects?
Yes. The Sqlite license is super permissive.
Quote
3. Is SQLite too overkill?
If you NEED sql it is the most lightweight solution. You may look at TBufDataSet and TMemDataSet for even more lightweight w/o sql.
Quote
4. How is MySQL compare to SQLite?
There's no real comparison. MySql is designed as a fully fledged sql server, Sqlite is designed as a single point storage that support sql querying.
mOrMoT uses Sqlite in a server-like fashion. With very, very good results, although I have noticed scaling problems over 500-1000 real connections. This is in practice not a real issue unless you really want to run over 1000 connections at the same time. In that case you need a solution that can cluster, like MySql or better PostGress or MariaDb.
In that case you need to deploy a separate server! There are no lightweight solutions for that many connections.... (even if some people tell you that, don't believe them)
Quote
5. Any other suggestion?
DonAlfredo's answer: link Sqlite statically. Or if you do not need Sql, I would suggest TBufDataSet. It works with all data controls and has still good support for searching and filtering. But no sql.
« Last Edit: January 09, 2017, 01:23:22 pm by Thaddy »
Specialize a type, not a var.

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Re: How to deploy SQLite programs?
« Reply #5 on: January 12, 2017, 11:57:43 am »
All you need to supply is sqlite3.dll together with your program (and the database file of course).
At least for Windows, I have no experience with Linux.

Just need to supply a lib file, it's very easy.

Overkill? No, SQLite is light and fast and good even for just storing the application settings in a table.

MySQL is made for use by many simultaneous clients and makes sense for web and multiuser. SQLite is not really that, it locks the database when ONE user is working with it. There are ways around that, like using the (free) ZEOS components in read-only mode and only use read/write when necessary. Or you can perhaps code something that uses threads, events or critical sections that controls the access to SQLite. But if you need multitasking with many users you may be better off with for example MySQL and tools like MySQL Workbench (http://www.mysql.com/products/workbench/). For a real DBMS (database management system) the smoothest and most productive choice for you could certainly be MySQL. Tools, support, litterature and examples are plenty. If you move on to MySQL there will be slightly different syntax for some things and you have to learn that. Moving to PostGres, again there is slightly other syntax. PostGres is just as good, but less litterature and smaller market share. It has a pretty good tool (https://www.pgadmin.org/)

Thanks for the information.

Especially due to your remarks about the "no lib file".
With FPC, the mORMot offers you a static SQLite3 on various systems (windows/linux).
So, a single executable without the need for a lib.
The whole SQLite3 is included in this single exe.

Interesting. I will try it after I learn some basic of SQLite.

It is simply the difference between statically linking and dynamically linking. The latter needs a shared library (dll, so)

Yes. The Sqlite license is super permissive.

If you NEED sql it is the most lightweight solution. You may look at TBufDataSet and TMemDataSet for even more lightweight w/o sql.

There's no real comparison. MySql is designed as a fully fledged sql server, Sqlite is designed as a single point storage that support sql querying.
mOrMoT uses Sqlite in a server-like fashion.

DonAlfredo's answer: link Sqlite statically. Or if you do not need Sql, I would suggest TBufDataSet. It works with all data controls and has still good support for searching and filtering. But no sql.

Very insightful.

Thanks all for the very helpful information.

turrican

  • Full Member
  • ***
  • Posts: 133
  • Pascal is my life.
    • Homepage
Re: How to deploy SQLite programs?
« Reply #6 on: January 12, 2017, 09:15:01 pm »
I suggest you to use mORMot! It's the best Delphi/Object Pascal SQLite3 and other Databases ORM/Rest Server framework.

Zath

  • Sr. Member
  • ****
  • Posts: 391
Re: How to deploy SQLite programs?
« Reply #7 on: January 12, 2017, 11:21:16 pm »
What a useful set of replies  :)
Some very handy pieces of information there.

 

TinyPortal © 2005-2018