[fpc-pascal] sql server embedded: sqlite the only solution?

[Michael Van Canneyt]

On Tue, 22 Dec 2009, Luiz Americo Pereira Camara wrote:

> Michael Van Canneyt escreveu:
>> 
>> 
>> On Tue, 22 Dec 2009, JoshyFun wrote:
>> 
>>> 
>>> SQLite is faster than Firebird, but take care that SQLite does not
>>> enforce foreign keys, so data integrity must be handled by the
>>> programmer "manually".
>> 
>> About the "sqlite is faster":
>> 
>> This depends highly on the kind of queries you are using.
>> 
>> In my tests, sqlite was significantly slower than firebird. I compared
>> speeds of 4 different embedded engines, and sqlite was definitely not the 
>> fastest one.
>
> Can you provide such tests?

Yes.

> Sqlite, due to it's nature (small), is know to have a query optimizer less 
> powerful than other (bigger) RDMS. Most of the time those sql can be manually 
> optimized. Anyway recent versions have improved performance.

That is good to hear!

>> Secondly, I would never recommend sqlite for use with Pascal applications, 
>> since sqlite does not even enforce the data to be of the correct type, 
>> which
>> creates problems if a field of type INT contains e.g. textual data, causing
>> a pascal application to crash.
>
> It's true if trying to load a data file created outside of the pascal 
> application. Databases created and handled by TDataset descendants (Zeos, 
> TSqlite3Dataset, Sqldb) has no such problems.

That doesn't matter.
You cannot be sure about the data you get, that is the point.
You don't know what the user will do to the database file.

I can tell you strange stories of databases which were accessible to 
users. A helpdesk nightmare :-)

>> (last time I checked, they called this "a feature")
>> 
>
> One point not cited in thread was the availability of Firebird embedded that 
> AFAIK is only for Windows.

This is not correct. It works perfectly on linux as well, and mac also. 
I use it on Linux.

> Not to say that sqlite is probably the most used embedded database: 
> http://www.sqlite.org/mostdeployed.html

It doesn't take away the fact that I do not consider it suitable for use with 
pascal. PHP, Java: there it probably doesn't matter, since they use
dynamically typed data. But pascal is strongly typed at compile time, 
and if the engine doesn't guarantee that an INT field contains really 
an INT, then it's out in my book.

But each should weigh the pros and cons; In my opinion, sqlite is off,
but every developer should consider what is important for him.

Michael.