How to open a .SQLITE file? Review of programs for administering SQLite databases.

The third part is subtleties and features.

This part is a hodgepodge of all sorts of SQLite features. I have collected here (in my opinion) the most important topics, without understanding which it is impossible to comprehend SQLite nirvana.

Since, again, there is a lot of information, the format of the article will be as follows: a short introduction to interesting topic and a link to the home site, where the details are. The site, alas, is in English.

Using SQLite in Multithreaded Applications

SQLite can be built single-threaded (compilation option SQLITE_THREADSAFE = 0).

In this embodiment, it cannot be used simultaneously from multiple threads, since there is no synchronization code at all. For what? For breakneck speed.

You can check if there is multithreading using the call sqlite3_threadsafe(): if it returned 0, then it is single-threaded SQLite.

By default, SQLite is built with thread support (sqlite3.dll).

There are two ways to use multi-threaded SQLite: serialized and multi-thread.

Serialized(you must specify the flag SQLITE_OPEN_FULLMUTEX when opening a connection). In this mode, threads can make SQLite calls in any way they want, no restrictions. But all calls block each other and are processed strictly sequentially.

Multi-thread(SQLITE_OPEN_NOMUTEX). In this mode, you cannot use the same connection simultaneously from multiple threads (but it is allowed simultaneous use different connections different streams). This mode is usually used.

Data Format

Base SQL data ite can store (text) data in UTF-8 or UTF-16.

Kit API calls consists of calls that receive UTF-8 ( sqlite3_XXX) and calls that receive UTF-16 ( sqlite3_XXX16).

If the data type of the interface and connection do not match, then on-the-fly conversion is performed.

Always use UTF-8.

UNICODE support

By default - no support. You need to create your own collation (method of comparison) via sqlite3_create_collation .
And define your built-in functions like(), upper(), lower() through www.sqlite.org/c3ref/create_function.html.

And some people already build SQLite DLLs with it.

Data Types and Value Comparison

As already mentioned, SQLIte allows you to write any value to any column.

A value inside the database can belong to one of following types storage ( storage class):
NULL,
INTEGER(occupies 1,2,3,4,6 or 8 bytes),
REAL(floating point number, 8 bytes in IEEE format),
TEXT(string in database data format, usually UTF-8),
BLOB(binary data, stored “as is”).

The sort order for values ​​of different types is:
- NULL least of all (including other NULL);
- INTEGER And REAL less than any TEXT And BLOB, are compared arithmetically with each other;
- TEXT less than any BLOB, are compared with each other based on their collation;
- BLOB-s are compared with each other through memcmp().

SQLite performs implicit type conversions on the fly in several places:
- when entering a value into a column (the column type specifies the conversion recommendation);
- when comparing values ​​with each other.

Values BLOB And NULL are always entered in any column "as is".

To column TEXT values TEXT values ​​are entered “as is” INTEGER And REAL become strings.
To column NUMERIC, INTEGER numbers are written "as is", and strings become numbers if _they can_ (that is, the reverse "lossless" conversion is allowed).
For column REAL the rules are similar to INTEGER(NUMERIC); the difference is that all numbers are represented in floating point format.
To column NONE values ​​are entered “as is” (this type is used by default unless another is specified).

When comparing values different types Additional type conversion can be performed between each other.

When comparing a number to a string, if the string can be converted to a number "losslessly", it becomes a number.

I will note here that in SQLite there can be as many as you like in a unique index NULL values ​​(Oracle agrees with this and MS SQL disagrees).

Database in memory

If on call sqlite3_open() pass the filename as ":memory:", then SQLite will create a connection to a new (clean) database in memory.

This connection is absolutely indistinguishable from the connection to the database in the file according to the logic of use: the same SQL set commands

Unfortunately, there is no way to open two connections to the same database in memory.

UPD: It turns out that it is already possible to open two connections to one database in memory.

Rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db);

ATTACH DATABASE "file:memdb1?mode=memory&cache=shared" AS aux1;

Joining multiple databases simultaneously

To open a connection to the database, use the call sqlite3_open().

At any time we can open connection join up to 10 more databases via SQL command ATTACH DATABASE.

Sqlite3_open("foo.sqlite3", &db); // open a connection to the database in the file "foo.sqlite3" sqlite3_exec(&db, "ATTACH "bar.sqlite3" AS bar", ...); // append "bar.sqlite3"

Now all database tables in the db1.sqlite3 file are transparently available in our connection.

To resolve naming conflicts, you should use the join name (the main base is called "main"):

SELECT * FROM main.my_table UNION SELECT * FROM bar.my_table

Nothing prevents you from attaching to the database new base in memory and use it for caching, etc.

Sqlite3_open("foo.sqlite3", &db); // open a connection to the database in the file "foo.sqlite3" sqlite3_exec(&db, "ATTACH ":memory:" AS mem", ...); // attach a new database in memory

This is very useful opportunity. The attached databases must have the same data format as the main database, otherwise there will be an error.

Temporary database

Pass an empty string instead of the filename to sqlite3_open() and a temporary database will be created in a file on disk. Moreover, after closing the connection to the database, it will be deleted from the disk.

Fine-tuning the database using the PRAGMA command

SQL command PRAGMA serves to set all kinds of settings for the connection or the database itself:

PRAGMA name; // request the current value of the name parameter PRAGMA name = value; // set the name parameter to value

Setting up the connection (obviously) should be done immediately after opening and before using it.

A complete description of all parameters is available.

I will focus on the most important things.

PRAGMA page_size = bytes; // database page size; a database page is a unit of exchange between the disk and the cache, it is reasonable to make it equal to the size of the disk cluster (I have 4096) PRAGMA cache_size = -kibibytes; // set the connection cache size in kilobytes, by default it is 2000 database pages PRAGMA encoding = "UTF-8"; // database data type, always use UTF-8 PRAGMA foreign_keys = 1; // enable support for foreign keys, by default - DISABLED PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF; // set the log type, see below PRAGMA synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL; // type of transaction synchronization, see below

Journal and recording of transactions

So we come to a topic, mastery of which immediately transfers you to the third level of the SQLite master's degree.

SQLite carefully maintains the integrity of data in the database (ACID), implementing a mechanism for changing data through transactions.

Briefly about transactions: a transaction is either completely rolled back or completely rolled back. There can be no intermediate states.

If you don't use transactions explicitly ( BEGIN; ...; COMMIT;), That an implicit transaction is always created. It starts before the command is executed and is committed immediately after.

This, by the way, is where the complaints about the “slowness” of SQLite come from. SQLite can insert up to 50 thousand records per second, but record transactions it can't do more than ~50 per second.

This is why it is not possible to insert records quickly using an implicit transaction.

With default settings, SQLite guarantees database integrity even when the power is turned off during operation.

Such amazing behavior is achieved by leading magazine (special file) and a clever mechanism for synchronizing changes on disk.

Briefly updating data in the database works like this:

Before any database modification, SQLite saves editable pages from DB to separate file(magazine), that is, simply copies them there;
- after making sure that a copy of the pages has been created, SQLite begins to change the database;
- having made sure that all changes in the database “reached the disk” and the database became complete, SQLite erases the log.

The atomicity of the transaction mechanism is described in detail.

If SQLite opens a connection to the database and sees that there is already a log, it realizes that the database is in an incomplete state and automatically rolls back the last transaction.

That is, the database recovery mechanism after failures is, in fact, built into SQLite and works unnoticed by the user.

By default, the log is kept in mode DELETE.
PRAGMA journal_mode = DELETE

This means that the log file is deleted after the transaction completes. The very fact of having a log file in this mode means for SQLite that the transaction was not completed and the database needs to be restored. The log file has the name of the database file appended with "-journal".

In mode TRUNCATE The log file is truncated to zero (on some systems this is faster than deleting the file).

In mode PERSIST the beginning of the log file is filled with zeros (however, its size does not change and it can take up a lot of space).

In mode MEMORY the log file is kept in memory and this works quickly, but does not guarantee database recovery in case of failures (there is no copy of the data on the disk).

Or you can completely disable the journal ( PRAGMA journal_mode = OFF). In this situation, transaction rollback stops working (command ROLLBACK) and the database will most likely become corrupted if the program terminates abnormally.

For an in-memory database, log mode can only be either MEMORY, or OFF.

Let's go back a little. How does SQLite “make sure” that the database will always be consistent?

We know that modern systems use clever caching to improve performance and can delay writes to disk.

Let's say SQLite has finished writing to the database and wants to erase the log file to mark the fact that the transaction was committed.

What if the file is deleted before the database is updated?

If during this period of time the power is turned off, then the log will no longer exist, and the database will not yet be complete - data loss!

In short, a clever change commit mechanism must rely on some guarantees from disk system and OS.

PRAGMA synchronous sets the degree of “paranoia” SQLite has in this regard.

Mode OFF(or 0) means: SQLite considers the data to be fixed on disk immediately after it transferred it to the OS (that is, immediately after calling the corresponding OS API).

This means that integrity is guaranteed when application crashes(since the OS continues to run), but not when OS crashes or power failure.

Sync Mode NORMAL(or 1) guarantees integrity during OS crashes and almost all power outages. There is a non-zero chance that if power is lost at the most inopportune moment the base will deteriorate. This is a kind of average, compromise mode in terms of performance and reliability.

Mode FULL guarantees integrity always and everywhere and in case of any accidents. But it works, of course, more slowly, because in certain places there are waiting pauses. And this is the default mode.

So, only the topic of a magazine like WAL.

WAL log mode

By default, the database log mode always "reverts" to DELETE. Let's say we opened a connection to the database and set the mode PERSIST. Changed data, closed connection.

There is a log file left on the disk (the beginning of which is filled with zeros).

Open the connection to the database again. If you do not set the log mode on this connection, it will again work in DELETE. Once we update the data, the transaction commit mechanism will erase the log file.

Log Mode WAL it works differently - it is “permanent”. Once we have switched the database to WAL mode, it will remain in this mode until it is explicitly changed to another log mode.

So why is it needed?

SQLite was originally designed as an embedded database. The architecture for sharing simultaneous access to data was designed primitively: several connections can simultaneously read the database, but write to at the moment There can only be one connection at a time. This means, at a minimum, that writing connection waits for the database to be “freed” from readers. When trying to write to a “busy” database, the application receives an error SQLITE_BUSY(not to be confused with SQLITE_LOCKED!). This access sharing mechanism is achieved through the file locking API (which does not work well on network drives, so it's not recommended to use SQLite there; )

We hope that we helped you solve the problem with the SQLITE3. If you don't know where you can download an application from our list, click on the link (this is the name of the program) - you will find more detailed information regarding where to download a secure installation version of the required application.

A visit to this page should help you answer these or similar questions specifically:

• How to open a file with the SQLITE3 extension?
• How to convert an SQLITE3 file to another format?
• What is the SQLITE3 file format extension?
• What programs serve the SQLITE3 file?

If, after viewing the materials on this page, you still have not received a satisfactory answer to any of the questions presented above, this means that the information presented here about the SQLITE3 file is incomplete. Contact us using the contact form and write what information you did not find.

What else could cause problems?

There may be more reasons why you cannot open the SQLITE3 file (not only the lack of an appropriate application).
Firstly- the SQLITE3 file may not be linked correctly (incompatible) with installed application for its maintenance. In this case, you need to change this connection yourself. For this purpose, click right button mouse over the SQLITE3 file you want to edit, click the option "Open with" and then select the program you installed from the list. After this action, problems with opening the SQLITE3 file should completely disappear.
Secondly- the file you want to open may simply be damaged. In this case, it would be best to find a new version of it, or download it again from the same source (perhaps for some reason in previous session The download of the SQLITE3 file has not completed and it cannot be opened correctly).

Do you want to help?

If you have additional information about the SQLITE3 file extension, we will be grateful if you share it with users of our site. Use the form below and send us your information about the SQLITE3 file.

- Extension (format) is the characters at the end of the file after the last dot.
- The computer determines the file type by its extension.
- By Windows default does not show file name extensions.
- Some characters cannot be used in the file name and extension.
- Not all formats are related to the same program.
- Below are all the programs that can be used to open the SQLITE file.

SQLite is a library that includes a completely self-contained, transactional, SQL database engine. Does not require installation or system configuration. There is also no need to configure the server, no need to start or stop it, and no need to create a new database. Another plus is that it works in turn, starting and ending each part separately, which is useful if suddenly there is an error or the system fails. Thanks to this, the data will not be lost, but will be saved and will continue to function. SQL is the most widely used database in the world and is very common. Has a fully open...

Database NET is extremely handy tool, allowing for various manipulations with countless databases. After downloading the application, you can easily create, select, delete, or update the desired data tables. The program can successfully export or print tables with data. The program interacts with Access, Excel. Supports working with databases: MySQL, Oracle, SQLite, SQLServer, OLEDB, ODBC, OData, SQLAzure, LocalDB, PostgreSQL and other databases. For proper operation The program requires mandatory installation of Microsoft. NET Framework. Allows you to select any font and minimize it to tray.

Files in the sqlite format are tables of the SQLite database of the same name. One SQLite file stores definitions, tables, indexes and data on the machine where the application runs. As in any database, all data is stored in the form of tables, each containing a certain number of fields, and each of them contains data of any type. In order to get data from sqlite you need to run sql query from any SQLite-enabled application. Source code This extension is open, and it itself is rightfully the property of web developers. sqlite opens with any text editor, but this is not entirely convenient, so let’s look at those applications that provide comfortable viewing.

What free programs can open sqlite file

1. SQLite free application, which allows you to create, view and edit databases in sqlite and other formats. It is worth noting that this application is just a library to which the program itself is added. The utility will not directly interact with the system. The maximum database size allowed for work is 2TB
2. - free text editor, is essentially an improved version of the notepad. It supports the syntax of all popular (and not popular) programming languages, encoding conversion, insertion of the “base” of the document (based on the extension in which the document is saved) and also very convenient function window separation.
3. – free utility for editing text files. The application interface is similar to the notepad interface, but provides where more features. Among them there are such features as searching a document, changing encoding, printing, and various encodings.
4. Notepad is the simplest text editor, included in the standard set Windows applications. Primarily focused on txt format, but you can use it to open any documents with text markup. Formatting, paragraphs, and inserting graphics are not supported.

If you come across files with the extension .sqlite or .sqlite3, and don’t know how to open them in order to delve into their insides, then this would be an excellent choice free program. For those who are wondering what these files are, it is a database in SQLite format, where all table data is stored in one file. Nowadays, many programs use SQLite databases to store their data in an organized form, for example, correspondence and contact numbers in Viber.

SQLite doesn't need to be installed, it's the only one executable file, which you download, is the entire program that we launch as needed. Only during the first launch the application will modestly ask which language you prefer to use; Russian is present. During each launch, a table with hints pops up, which can be disabled by checking the appropriate box.

Before we delve into the details SQLite works, I would like to make a small remark. Developers have no unspoken rules about what extensions a SQLite database should have. Depending on the imagination of the developers, the file extension can be either meaningful .sqlite, .sqlite3, sl2, sl3 or more universal db, db2, db3, sbd3. To make sure that you really have a database in the SQLite format, you need to, and if the first line contains the phrase “SQLite format,” then feel free to pick up SQLiteStudio.

To open the database, click in the main menu “Databases” -> “Add database”, after which a window pops up in which we select the file with the database we are interested in, if necessary, make small settings (what version of the database and whether to remember it ), and click “OK”. You don’t have to worry too much about the settings and leave everything at default; in 99% of cases everything works great.

After connecting the database, it is added to the left table, which, when clicked, reveals the list of tables that are contained there. On the right side you can see the structure of the table on the tab of the same name and the data that is stored there, for which you need to click on the “Data” tab.

There is no point in talking about other features of SQLiteStudio, most ordinary users they won’t be needed, and everyone else understands why they bother editing databases and how it should be done. I just want to give one recommendation, do it backup copy database, before you start to climb into it, much less edit it.

Surprisingly, there are a lot of settings here, however, they are all mostly related to appearance programs (colors, fonts, what to show and what to hide) and keyboard shortcuts.

The 5+ program copes with its tasks, allowing you to access data stored in SQLite format databases, edit data and tables. The most important thing is that everything works quite stably, quickly and does not freeze on some files for unknown reasons, like applications from other developers.

The program works great on 32 and 64 bit operating systems. The interface has been translated into approximately ten languages, including Russian.