My First SQLite Project

You can share your experience with HMG. Share with some screenshots/project details so that others will also be benefited.

Moderator: Rathinagiri

User avatar
esgici
Posts: 4543
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Contact:

Re: My First SQLite Project

Post by esgici »

gfilatov wrote: I hope that give you an idea :idea:
Yes, no "an" but many ideas :idea:

But all of them are promotions and nothing about limits :(

This means that SQLite doesn't have any limit :?:

Regards

--

Esgici
Viva INTERNATIONAL HMG :D
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My First SQLite Project

Post by sudip »

Hello Esgici, Filatov,

As a humble student of both of you, I am trying to find out some limits of SQLite (sorry for long quote):
Situations Where Another RDBMS May Work Better

*

Client/Server Applications

If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, the file locking logic of many network filesystems implementation contains bugs (on both Unix and Windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.
*

High-volume Websites

SQLite will normally work fine as the database backend to a website. But if you website is so busy that you are thinking of splitting the database component off onto a separate machine, then you should definitely consider using an enterprise-class client/server database engine instead of SQLite.
*

Very large datasets

With the default page size of 1024 bytes, an SQLite database is limited in size to 2 tebibytes (241 bytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.
*

High Concurrency

SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database. For many situations, this is not a problem. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.
Hope following link will be helpful (from where I got above information) http://www.sqlite.org/whentouse.html

So, can we replace DBF with SQLite :?:

Please correct me if I have any wrong ideas about Sqlite. :)

With best regards.

Sudip
Last edited by sudip on Thu Sep 17, 2009 1:34 pm, edited 1 time in total.
With best regards,
Sudip
User avatar
gfilatov
Posts: 1057
Joined: Fri Aug 01, 2008 5:42 am
Location: Ukraine
Contact:

Re: My First SQLite Project

Post by gfilatov »

esgici wrote:...
This means that SQLite doesn't have any limit :?:
Esgici,

You are right :!:
There are the following limits as described in the documentation:
Limits In SQLite

"Limits" in the context of this article means sizes or quantities that can not be exceeded. We are concerned with things like the maximum number of bytes in a BLOB or the maximum number of columns in a table.

SQLite was originally designed with a policy of avoiding arbitrary limits. Of course, every program that runs on a machine with finite memory and disk space has limits of some kind. But in SQLite, those limits were not well defined. The policy was that if it would fit in memory and you could count it with a 32-bit integer, then it should work.

Unfortunately, the no-limits policy has been shown to create problems. Because the upper bounds were not well defined, they were not tested, and bugs (including possible security exploits) were often found when pushing SQLite to extremes. For this reason, newer versions of SQLite have well-defined limits and those limits are tested as part of the test suite.

This article defines what the limits of SQLite are and how they can be customized for specific applications. The default settings for limits are normally quite large and adequate for almost every application. Some applications may want to increase a limit here or there, but we expect such needs to be rare. More commonly, an application might want to recompile SQLite with much lower limits to avoid excess resource utilization in the event of bug in higher-level SQL statement generators or to help thwart attackers who inject malicious SQL statements.

Some limits can be changed at run-time on a per-connection basis using the sqlite3_limit() interface with one of the limit categories defined for that interface. Run-time limits are designed for applications that have multiple databases, some of which are for internal use only and others which can be influenced or controlled by potentially hostile external agents. For example, a web browser application might use an internal database to track historical page views but have one or more separate databases that are created and controlled by javascript applications that are downloaded from the internet. The sqlite3_limit() interface allows internal databases managed by trusted code to be unconstrained while simultaneously placing tight limitations on databases created or controlled by untrusted external code in order to help prevent a denial of service attack.

Maximum length of a string or BLOB

The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). You can raise or lower this value at compile-time using a command-line option like this:

-DSQLITE_MAX_LENGTH=123456789
The current implementation will only support a string or BLOB length up to 231-1 or 2147483647. And some built-in functions such as hex() might fail well before that point. In security-sensitive applications it is best not to try to increase the maximum string and blob length. In fact, you might do well to lower the maximum string and blob length to something more in the range of a few million if that is possible.

During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB. So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row.

The maximum string or BLOB length can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_LENGTH,size) interface.

Maximum Number Of Columns

The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper bound on:

The number of columns in a table
The number of columns in an index
The number of columns in a view
The number of terms in the SET clause of an UPDATE statement
The number of columns in the result set of a SELECT statement
The number of terms in a GROUP BY or ORDER BY clause
The number of values in an INSERT statement
The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767. On the other hand, many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table.

In most applications, the number of columns is small - a few dozen. There are places in the SQLite code generator that use algorithms that are O(N?) where N is the number of columns. So if you redefine SQLITE_MAX_COLUMN to be a really huge number and you generate SQL that uses a large number of columns, you may find that sqlite3_prepare_v2() runs slowly.

The maximum number of columns can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_COLUMN,size) interface.

Maximum Length Of An SQL Statement

The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000. You can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH and 1073741824.

If an SQL statement is limited to be a million bytes in length, then obviously you will not be able to insert multi-million byte strings by embedding them as literals inside of INSERT statements. But you should not do that anyway. Use host parameters for your data. Prepare short SQL statements like this:

INSERT INTO tab1 VALUES(?,?,?);
Then use the sqlite3_bind_XXXX() functions to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It is also runs faster since the large string does not need to be parsed or copied as much.

The maximum length of an SQL statement can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_SQL_LENGTH,size) interface.

Maximum Number Of Tables In A Join

SQLite does not support joins containing more than 64 tables. This limit arises from the fact that the SQLite code generator uses bitmaps with one bit per join-table in the query optimizer.

SQLite uses a very efficient O(N?) greedy algorithm for determining the order of tables in a join and so a large join can be prepared quickly. Hence, there is no mechanism to raise or lower the limit on the number of tables in a join.

Maximum Depth Of An Expression Tree

SQLite parses expressions into a tree for processing. During code generation, SQLite walks this tree recursively. The depth of expression trees is therefore limited in order to avoid using too much stack space.

The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression tree depth. If the value is 0, then no limit is enforced. The current implementation has a default value of 1000.

The maximum depth of an expression tree can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_EXPR_DEPTH,size) interface if the SQLITE_MAX_EXPR_DEPTH is initially positive. In other words, the maximum expression depth can be lowered at run-time if there is already a compile-time limit on the expression depth. If SQLITE_MAX_EXPR_DEPTH is set to 0 at compile time (if the depth of expressions is unlimited) then the sqlite3_limit(db,SQLITE_LIMIT_EXPR_DEPTH,size) is a no-op.

Maximum Number Of Arguments On A Function

The SQLITE_MAX_FUNCTION_ARG parameter determines the maximum number of parameters that can be passed to an SQL function. The default value of this limit is 100. SQLite should work with functions that have thousands of parameters. However, we suspect that anybody who tries to invoke a function with more than a few parameters is really trying to find security exploits in systems that use SQLite, not do useful work, and so for that reason we have set this parameter relatively low.

The number of arguments to a function is sometimes stored in a signed character. So there is a hard upper bound on SQLITE_MAX_FUNCTION_ARG of 127.

The maximum number of arguments in a function can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_FUNCTION_ARG,size) interface.

Maximum Number Of Terms In A Compound SELECT Statement

A compound SELECT statement is two or more SELECT statements connected by operators UNION, UNION ALL, EXCEPT, or INTERSECT. We call each individual SELECT statement within a compound SELECT a "term".

The code generator in SQLite processes compound SELECT statements using a recursive algorithm. In order to limit the size of the stack, we therefore limit the number of terms in a compound SELECT. The maximum number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500. We think this is a generous allotment since in practice we almost never see the number of terms in a compound select exceed single digits.

The maximum number of compound SELECT terms can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_COMPOUND_SELECT,size) interface.

Maximum Length Of A LIKE Or GLOB Pattern

The pattern matching algorithm used in the default LIKE and GLOB implementation of SQLite can exhibit O(N?) performance (where N is the number of characters in the pattern) for certain pathological cases. To avoid denial-of-service attacks from miscreants who are able to specify their own LIKE or GLOB patterns, the length of the LIKE or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes. The default value of this limit is 50000. A modern workstation can evaluate even a pathological LIKE or GLOB pattern of 50000 bytes relatively quickly. The denial of service problem only comes into play when the pattern length gets into millions of bytes. Nevertheless, since most useful LIKE or GLOB patterns are at most a few dozen bytes in length, paranoid application developers may want to reduce this parameter to something in the range of a few hundred if they know that external users are able to generate arbitrary patterns.

The maximum length of a LIKE or GLOB pattern can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_LIKE_PATTERN_LENGTH,size) interface.

Maximum Number Of Host Parameters In A Single SQL Statement

A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123".

Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the "?123" form is used, the host parameter number is the number that follows the question mark.

SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.

The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.

Maximum Depth Of Trigger Recursion

SQLite limits the depth of recursion of triggers in order to prevent a statement involving recursive triggers from using an unbounded amount of memory.

Prior to SQLite version 3.6.18, triggers were not recursive and so this limit was meaningless. Beginning with version 3.6.18, recursive triggers were supported but had to be explicitly enabled using the PRAGMA recursive_triggers statement. Beginning with version 3.7.0, recursive triggers are enabled by default but can be manually disabled using PRAGMA recursive_triggers. The SQLITE_MAX_TRIGGER_DEPTH is only meaningful if recursive triggers are enabled.

The default maximum trigger recursion depth is 1000.

Maximum Number Of Attached Databases

The ATTACH statement is an SQLite extension that allows two or more databases to be associated to the same database connection and to operate as if they were a single database. The number of simulataneously attached databases is limited to SQLITE_MAX_ATTACHED which is set to 10 by default. The code generator in SQLite uses bitmaps to keep track of attached databases. That means that the number of attached databases cannot be increased above 30 on a machines with a 32-bit integer.

The maximum number of attached databases can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size) interface.

Maximum Database Page Size

An SQLite database file is organized as pages. The size of each page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The default value for SQLITE_MAX_PAGE_SIZE is 32768. The current implementation will not support a larger value.

It used to be the case that SQLite would allocate some stack structures whose size was proportional to the maximum page size. For this reason, SQLite would sometimes be compiled with a smaller maximum page size on embedded devices with limited stack memory. But more recent versions of SQLite put these large structures on the heap, not on the stack, so reducing the maximum page size is no longer necessary on embedded devices. There is no longer any real reason to lower the maximum page size.

Maximum Number Of Pages In A Database File

SQLite is able to limit the size of a database file to prevent the database file from growing too large and consuming too much disk space. The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to 1073741823, is the maximum number of pages allowed in a single database file. An attempt to insert new data that would cause the database file to grow larger than this will return SQLITE_FULL.

The max_page_count PRAGMA can be used to raise or lower this limit at run-time.
Kind Regards,
Grigory Filatov

"Everything should be made as simple as possible, but no simpler." Albert Einstein
User avatar
esgici
Posts: 4543
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Contact:

Re: My First SQLite Project

Post by esgici »

sudip wrote: Please correct me if I have any wrong ideas about Sqlite. :)
Hi Sudip

Sorry, I haven't any experience on SQLite, nor any other xxSQL nor SQLxxx :(

Mine is only an anxiety on a sad experience of one of my friend by MS_Sql desktop (or "express" or another else) :(

It's very bad, recognizing the dead end after reaching the end :(

Regards

--

Esgici
Viva INTERNATIONAL HMG :D
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My First SQLite Project

Post by sudip »

Hello Brother Esgici,
esgici wrote: Sorry, I haven't any experience on SQLite, nor any other xxSQL nor SQLxxx :(
Mine is only an anxiety on a sad experience of one of my friend by MS_Sql desktop (or "express" or another else) :(
It's very bad, recognizing the dead end after reaching the end :(
No problem :) I am also not a good admirer of MS databases regarding robustness. Again I must say that there are many good features in MS Databases. :)
Sql is very easy and fast. Moreover transaction facility of Sql databases makes them robust.
Main difference of Sql and Dbf dababases is (IMHO), Sql runs on Set theory, where Dbf runs on record based model. Easily we can say during search Sql works on Group (Set), where Dbf search on record by record.
Again, if I am wrong, please correct me.

One thing more, "SQLite" is not a scaled down free version of a professional database. It is light-weight on resource requirement. Developers of Sqlite eliminated some features to make it lightweight, which are mainly required for big Client/Server database.

Now, I am working on a very small real life project to transform it from DBFCDX to Sqlite :)

With best regards.

Sudip
Last edited by sudip on Fri Sep 18, 2009 9:54 am, edited 1 time in total.
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post by Rathinagiri »

Hi,

Here I had made a small sample to create a database, table, unique index on SQLite with various types of data. Then I had inserted some 100 records. Then, selected some records upon date. It seems work well.

Please verify.
sqlite1.zip
(2 KiB) Downloaded 713 times
Now, I am on a small project to convert any HMG MySQL database ;) (ie., with simple data structure having data types of char, numeric, date, logic) into a SQLite database.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post by Rathinagiri »

Rathi, is there any way to add login facility with Sqlite?
No Sudip. As we can see, SQLite database file is a SQL DB file with multiple tables, indices, triggers, and views all contained in a single disk file. As of now, encryption is not available.

I had encrypted with a password using harbour encryption and used a copy of decrypted database in a temporary folder. At the time of closing the program, the database in the temporary folder is re-encrypted and saved in the old directory. This has a huge risk of losing the current data if the system having the temporary database is in problem.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My First SQLite Project

Post by sudip »

Dear Rathi,

Thanks a lot for your project. Excellent!!! You have made your MySql utility functions compatible with Sqlite! Thanks :)

I have one suggestion. Following code during initial insertion will increase the speed:-

Code: Select all

cQstr := "BEGIN TRANSACTION;"

for i := 1 to 100
   
	cQStr += "insert into new (text,floating,date1,logic,text2) values ("+;
	          c2sql("Giri"+alltrim(str(i)))+","+;
	          c2sql(123.45)+","+;
	          iif(i <= 50,c2sql(date()),c2sql(ctod("18-09-2010")))+","+;
	          c2sql(.t.)+","+;
	          c2sql("India")+;
	          ");"
next i

cQstr += "COMMIT;"
         
if .not. miscsql(dbo,cQstr)
   return nil
endif
This is explained in http://www.sqlite.org/faq.html#q19
INSERT is really slow - I can only do few dozen INSERTs per second

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.
Thank you again.

I am also working on Sqlite.

With best regards.

Sudip
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post by Rathinagiri »

Thanks for the torch light Sudip. :)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My First SQLite Project

Post by sudip »

Rathi, My brain became exhausted during conversion of a DBFCDX project (MyExpense) to Sqlite :roll:
But, I must do it today :)
With best regards,
Sudip
Post Reply