Menu
homeConnection

Sqlite editor in Russian. Free program SQLiteStudio for editing SQLite databases. Why does everyone complain that SQLite is slow?

I finally decided to write an article about SQLite, in which I want to summarize my 3-year experience of using this database under Windows. I see that the topic is popular, but there is little information.

A little introductory note.

This article is not for beginner programmers.
It is not a SQL tutorial.
She doesn't advocate using SQLite.
She's not advocating not to use SQLite.
The article is written in the form of questions from a hypothetical newbie to SQLite and answers to them (since there is a lot of information and it’s at least a little easier to structure it this way).

What is SQLite?
SQLite is an embedded cross-platform database that supports a fairly comprehensive set of SQL commands and is available in source code (in C).

The SQLite source codes are in the public domain, which means there are no restrictions on use at all.

Website (with excellent documentation in English): http://sqlite.org

Current version: 3.7.13

You can compile SQLite yourself, but I download it already compiled as a Windows DLL.

For your own assembly, you usually download the so-called. "amalgamation"
those. SQLite sources in the form of a single file in C + sqlite3.h.

To reduce the size of SQlite code by eliminating unnecessary stuff, all sorts of DEFINEs are used.

How popular is SQLite?
Briefly: she is everywhere. At least on any smartphone.
How reliable is it?
Very. When a version is released, it goes through a series of serious automated tests (~ 2 million tests are carried out), code coverage by tests is 100% (since August 2009).
What other tools do the developers provide?
A console utility for working with databases is available (sqlite3.exe, “a command-line shell for accessing and modifying SQLite databases”).
That's all?
Yes, everything from the main developers. However, other people write all sorts of managers, etc.
Personally, I have never found the ideal one and use the console.
What does "sufficiently complete set of SQL" mean?
As you know, SQL has moved in different directions in its development. Large manufacturers began to push in all sorts of extensions. And although all sorts of standards are accepted (SQL 92), in real life all large databases do not fully support the standards + they have something of their own. So, SQLite tries to live by the principle of “minimal but complete set”. It doesn't support complicated stuff, but it's pretty much the same as SQL 92.
And it introduces some of its own features, which are very convenient, but not standard.
What specifically is it about SQL support that might cause confusion?
You cannot delete or change a column in a table (ALTER TABLE DROP COLUMN..., ALTER TABLE ALTER COLUMN...).
There are triggers, but not as powerful as those of large RDBMSs.
There is foreign key support, but by default it is DISABLED.
There is no built-in support for UNICODE (but it is generally not difficult to achieve).
No stored procedures.
What's good or unusual?
a) Each record contains a virtual rowid column, which is equal to a 64-bit number (unique for the table).
You can declare your column INTEGER PRIMARY KEY and then this column will become rowid (with its own name, the name rowid still works).
When inserting a record, you can specify a rowid, or you can not specify it (and the system will then insert a unique one).
Details: www.sqlite.org/autoinc.html
b) you can easily organize the database in memory (this is very convenient and I’ll tell you more about it a little later);
c) easy to transport: by default, the database is one file (in a cross-platform format);
d) the column type does not determine the type of value stored in this record field, that is, any value can be entered in any column;
e) many built-in functions (which can be used in SQL): www.sqlite.org/lang_corefunc.html;
I don’t understand - what’s wrong with the type? Why do we need a column type then at all?
The column type determines how the values ​​are compared (they need to be converted to a single type when comparing, say, inside an index).
But it does not oblige you to enter values ​​of this particular type into the column. Something like weak typing.

Let's say we declared a column as "A INTEGER".
SQlite allows you to enter values ​​of any type into this column (999, “abc”, “123”, 678.525).
If the value being inserted is not an integer, then SQlite attempts to cast it to an integer.
Those. the string “123” will turn into the integer 123, and the remaining values ​​will be written “as is”.

So is it possible not to specify the column type at all?
This is very often done: CREATE TABLE foo (a,b,c,d).
What about architecture? Is there no server?
There is no server, the application itself is a server. Access to the database occurs through “connections” to the database (something like an OS file handle), which we open through a call to the corresponding DLL function. When opening, the name of the database file is indicated. If there is no such thing, it is automatically created.
It is acceptable to open multiple connections to the same database (via a file name) in the same or different applications.
The system uses file access blocking mechanisms at the OS level to make it all work
(these mechanisms usually don't work well on network drives, so it's not recommended to use SQlite with a file on a network).
Initially, SQlite worked on the principle of “many read, one writes.”
That is, only one connection writes to the database at a given time. If other connections try to write too, they will get the SQLITE_BUSY error.
You can, however, enter an operation timeout. Then the connection, faced with a busy database, will wait N seconds before failing with the error SQLITE_BUSY.
So what should we do?
Either one connection and all requests through it, or proceed from a possible timeout and provide for repeating the SQL execution.
There is another possibility: not long ago a new type of SQlite log appeared: Write Ahead Log, WAL.
If you enable this particular log mode for the database, then several connections will be able to simultaneously modify the database.
But in this mode the database already occupies several files.
Well, now it’s clear why SQLite is terrible, because it doesn’t have a GLOBAL CACHE?
Indeed, all modern RDBMSs are unthinkable without a global shared cache, which can store all sorts of goodies like compiled parameterized queries. This is done by a server that is not here. However, within the same application, SQlite can share the cache between several connections (read here: www.sqlite.org/sharedcache.html) and save some memory.
Why does everyone complain that SQLite is slow?
Two reasons. The first is the default settings. They work for reliability, not performance.
The second is a misunderstanding of the transaction recording mechanism. By default, after any command, SQlite will commit the transaction (that is, wait until the database is in a consistent state before turning off the power). Depending on the paranoia mode, SQLite will spend from 50 to 300 ms on this (waiting for the end of writing data to disk).
What should I do? I need to insert 100 thousand records and quickly!
Delete indexes, turn on the synchronization mode OFF (or NORMAL), insert in portions of N thousand (N - select, take 5000 to start with). Before inserting a portion, do BEGIN TRANSACTION, after - COMMIT.
But I found a mistake! How to report?
No way.

The thing is, the popularity of SQLite is scary - it's everywhere. I'm not kidding.
And developers were faced with a flood of error messages that were either caused by misunderstanding or were hidden feature requests. They actually closed the direct acceptance of reports with errors.
So you should sign up for the mailing list and post your problem there and hope for the best.

Personally, I had a situation that I interpreted as a defect in SQLIte. I described this in the newsletter. The behavior of SQLite was corrected in the next version.

A handy utility for playing around with SQLite.

To be continued.

Tags: Add tags

SQLite Editor is designed for editing databases in various applications. The program can be integrated directly into RooTExplorer for greater convenience.

Characteristic

Developers who work with SQL will be able to view and edit data in databases directly on mobile platforms. You can instantly save the result without interrupting the subsequent operation of a third-party application.
The SQLite Editor app works great with RootExplorer. The file manager opens up new possibilities for using the application, allowing you to open databases directly from the file manager. In addition, in the manager you can add databases to favorites, sort them, and store them conveniently. There is also a tab with the history of recently opened programs. These features make it much easier to navigate and search for edited databases so you can make changes later.

Peculiarities

The application can work with or without root rights. In the latter case, access is provided only to the databases of those applications that are installed on the SD card. In addition, as noted above, the application is closely combined with RootExplorer, and this file manager requires super-user rights to operate. The only thing that can complicate the use of the application is the lack of Russian localization. But this inconvenience is fully compensated by the stability and speed of operation, clear navigation and familiar functions for developers working with SQL.

SQLite is a compact, embedded relational database. The library source code has been released into the public domain. In 2005, the project received the Google-O'Reilly Open Source Awards.

The word “embedded” means that SQLite does not use the client-server paradigm, that is, the SQLite engine is not a separate running process with which the program interacts, but provides a library with which the program is linked and the engine becomes an integral part of the program. Thus, function calls (API) of the SQLite library are used as the exchange protocol. This approach reduces overhead, response time, and simplifies the program. SQLite stores the entire database (including definitions, tables, indexes, and data) in a single standard file on the computer on which the program is executed. Simplicity of implementation is achieved due to the fact that before the start of a write transaction, the entire file storing the database is locked; ACID functions are achieved, among other things, by creating a log file.

Several processes or threads can simultaneously read data from the same database without any problems. Writing to the database can only be done if no other requests are currently being served; otherwise, the write attempt fails and an error code is returned to the program. Another option is to automatically repeat recording attempts within a specified time interval.

The package also includes a functional client part in the form of an executable file sqlite3, which demonstrates the implementation of the functions of the main library. The client part works from the command line and allows you to access the database file based on standard operating system functions.

Thanks to the engine architecture, it is possible to use SQLite both on embedded systems and on dedicated machines with gigabyte data arrays.

SQLite supports dynamic data typing. Possible field types: INTEGER, REAL, TEXT, BLOB.

Using SQLite

The SQLite library itself is written in C; there are a large number of bindings to other programming languages, including Delphi, C++, Java, C#, Visual Basic .NET, Perl, PureBasic, Tcl (Tcl tools included with SQLite), Haskell, Scheme, Smalltalk, and Parser, as well as many others. A complete list of existing tools is available on the project page.

The simplicity and ease of embedding SQLite has led to the library being used in browsers, music players and many other programs.

In particular, SQLite is used:

  • - environment for running applications (partially);
  • Autoit;
  • Framework ;
  • Yii framework; (In stock);
  • XUL platform powered by Gecko 1.9+, 1.9+ and potentially all applications based on this platform, including:
  • Some models of Garmin GPS navigators;
  • Android API;
  • Minetest.

Many programs support SQLite as a data storage format (especially Mac OS and iOS, Android), including:

  • 1C:Enterprise 7.7 (using an external component);
  • 1C:Enterprise 8.3 (for storing log entries);
  • Adobe Photoshop Lightroom;
  • Eserv;
  • F-Spot;
  • (since version 3.0);
  • Daminion;

Do you work with SQL databases? Then this application will definitely come in handy - with its help you can view, edit and save databases in any application.

Peculiarities

SQLite Editor does not require superuser rights by default. But without them, it will allow you to edit and save modified databases only of those applications that are installed on the MicroSD.

With root rights, in turn, you will be able to adjust the databases of any applications - including those located on the internal memory.

The program interacts well with the functionality of the RootExplorer file manager. With its help, you can conveniently open databases in the application, viewing them in the catalog.

In addition, the file directory allows you to add SQL files to your favorites, making them easier to find. There is also a history (it is also in the application itself), where information about recent paths is located.

Decor

The program carries only a functional load. Therefore, you can forget about the beauty in the interface. The main menu is presented in strict dark blue and black colors.

A simple font and pseudo-3D icons are some of the other features of the interface.

In addition, there is no Russian localization. However, this does not prevent knowledgeable users from navigating the utility’s tools.

This handy program will become a reliable assistant for geeks and advanced users who would like to make changes to applications directly on their mobile device.