I don’t understand - what’s wrong with the type? Why do we need a column type then at all?
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 wide range of full set SQL commands and is available in source code (in C language).Source codes SQLite are in the public domain, that is, 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 in as 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, in its development SQL rushed to different sides. Large manufacturers They started pushing 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 exactly is in SQL support might it 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 you 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 do not work well on network drives, so it is not recommended to use SQlite with a file on the network).
Initially, SQlite worked on the principle of “many read, one writes.”
That is, only one connection writes to the database in this moment 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 so long ago the new kind SQlite log: 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 lack of understanding 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. IN next version SQLite behavior has been fixed.
A handy utility for playing around with SQLite.
To be continued.
Tags:
- sqlite
- sql
- rdbms
- Database
SQLite is a database somewhat similar to MySQL. Fundamental SQLite difference from other databases is that the entire database is one file. If in MySQL database is stored somewhere in the wilds of the server and is not available for transfer, then in SQLite everything is outrageously simple: one file - one database.
Of course, the server must support the SQLite driver (just like any other database), but as a rule there are no problems with this now.
SQLite allows you to work with the database using SQL as usual, creating tables, fields, etc. In general, we can say that SQLite is in no way inferior to the usual MySQL, with the exception, perhaps, of more slow work with “heavy” SQL queries for updating data (insert and update). But, again, this is for high-load sites.
A huge advantage of SQLite is its ease of portability. Copy a file - what could be easier? You don’t need to worry about backups, like in MySQL, you don’t need to create a user with a password on the server, you don’t need to create the database itself. With SQLite we just take it and use it.
To work with the database in PHP is better use PDO - PHP Data Objects - this is the so-called. abstraction that suggests single interface for working with different databases. In theory, using PDO, you can switch to any database without changing SQL queries, for example from MySQL to SQLite. Only the connection parameters change.
This way SQLite will connect via PDO. Nothing is required for this, since PDO itself is already included in PHP, and the SQLite driver is usually included on servers as well.
But, before you start programming, you need to create the base itself. For example, for MySQL there is phpMyAdmin, through which you can perform various operations. There are also similar developments for SQLite, but I will show how this can be done through Firefox browser. To do this, you only need to install the add-on.
To add this add-on to the FireFox main menu ("hamburger"), click Edit and drag the icon onto the menu.
This is installed and you can use it.
First, let's create new base data. In SQLite this is separate file, which will have the extension .sqlite . SQLite Manager will prompt you to specify the directory where this file will be stored. Select or create a new directory. This doesn't really matter to us yet. As a result, a sqlite file with a new database will be created.
This file can be moved (and renamed) anywhere, and then opened with the menu command Databases - Connect database.
Now you need to create a table (or tables) in the database.
SQLite Manager automatically creates service tables sqlite_XXX. We don't touch them and they don't bother us.
A table in a database is where structured information is stored. The table must have a set of fields with the specified properties. For example, the field can be integer - for integers, or text - for text. The number of fields can be arbitrary and is determined only by the webmaster’s task.
Let, for example, we have a table pages with fields
- id - unique number(autoincrement)
- slug- link
- text- free text
- hits- number of views
After the table is created, pay attention to the “SQL statement that created this object” block. It will contain an SQL query that can be used to create a table. It can be useful if you need to create a table in the database via PHP.
The View and Search tab allows you to edit the table. For example, let’s create two lines where the field slug will be home and contact . These will be two pages: home And website/contact.
Field hits will contain a page view counter. The text can be anything.
That's it, the base is ready, now you can use it.
Let's set the task. Let us have a simple website that will display short link(slug) corresponding text and number of views.
If we do this on local server, then let the site be in the directory sqlite. There is a subdirectory in it db, where we will copy our pages.sqlite.
We can do routing as described in the previous article. File.htaccess
AddDefaultCharset UTF-8 Options -Indexes
IN index.php The routing itself will be described in one line:
$page = ($p = key($_GET)) ? $p: "home";
- connect the base
- we make a selection in it by $page
- display the received data
I intentionally simplify the algorithm so as not to complicate the PHP code.
There are two options for working with the database. The first is native PHP code. It is not very complicated, but the abundance of parameters is a little annoying. Therefore, the second option is to use additional wrapper libraries. With them, the code becomes more concise.
I will give the index.php code in the first version:
setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $sql ="SELECT * FROM pages WHERE slug=:page LIMIT 1"; $sth = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $sth->execute(array(":page" => $page)); $rows = $sth->fetchAll(); print_r($rows); // output data here ) catch(Exception $e) ( echo $e->getMessage(); ) # end of file
For the second option, I used a php library from the site labaka.ru, which I placed in a subdirectory lib.
index.php code:
This example will display different text for different addresses and the views counter will work. If you need to add a new page, then you just need to create it in the database. For convenience, in SQLite Manager you can connect the database directly from the site directory.
There are a couple of important points to note here.
First of all, all work with PDO should be contained in a try..catch block. Error messages are caught in this way.
The data that is sent to the sql query must go through validation. In PDO, when data prepare is used (PDO::prepare), parameter escaping is forced. This allows you to protect against possible SQL injections.
When a SQLite database is connected, if there is no database file, it will be created automatically. This is the basis for creating a database immediately after connecting it and creating the necessary tables with the first sql query (which I wrote about above).
One more note about SQLite. Since the database is a file, it can be downloaded from a URL directly through a browser. Therefore, it is better to protect the directory with SQLite files via .htaccess with the Deny from all line. Or place it higher than the main www directory.
SQLite can be useful for small tasks that require a small database: counters, voting, page metadata, etc.
You can refer to this article.
This is a library written in C language that provides work with SQL. This tool belongs to Relational Database Management Systems. Most SQL databases operate on a client/server basis. Let's take MySQL for example. During operation, data is taken from the MySQL server and sent as a response to the request. In the case of using SQLite, the data will be taken directly from the disk, i.e. there will be no need to contact the server.
Installation
We will interact with the database through the command line interface sqlite3(CLI) on Linux. Work with sqlite3 The CLI in MAC OS and Windows is carried out in the same way, however, I recommend that you spend 5 minutes installing the virtual machine so as not to clutter your computer with unnecessary software.
To install sqlite3 on Linux, run the command:
sudo apt-get install sqlite3 libsqlite3-dev
As a result, your machine will have sqlite3. To install this tool on other OS, follow the instructions. To start sqlite, run the sqlite3 command in the console. The result should be like this:
The second line contains a hint that to get help you need to run the command.help . Let's do that. As a result we will see Meta Commands and their description.
Meta Commands
Meta Commands- designed for generating tables and other administrative operations. They all end dot. Let's go through the list of commands that may be useful:
Standard commands
Now let's go through the list standard commands sqlite3, which are designed to interact with the database. Standard commands can be classified into three groups:
- Data Description Language DDL: Commands to create a table, modify and delete databases, tables and more.
The note: SQLite also supports many other commands, a list of which can be found. Since this lesson is intended for beginners, we will limit ourselves to the listed set of commands.
SQLite database files are cross-platform. They can be located on various types of devices.
- A comment
Of all these fields, only the site address can be empty. We can also introduce a column for numbering comments. Let's call it post_id.
Now let's define the data types for each of the columns:
| Attribute | Data type |
| post_id | INTEGER |
| name | TEXT |
| TEXT | |
| website_url | TEXT |
| comment | TEXT |
You will be able to find all data types supported in SQLite3.
It should also be noted that in SQLite3, the data inserted into a column may differ from the specified type. This will not work in MySQL.
Now let's create the database. If you are still in the sqlite3 interface, then type the command.quit to exit. Now enter:
sqlite3 comment_section.db
As a result, in the current directory we will have a file comment_section.db.
The note: If you do not specify a file name, sqlite3 will create a temporary database.
Creating a table
To store comments we need to create a table. Let's call it comments. We execute the command:
CREATE TABLE comments (post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL, website_url TEXT NULL, comment TEXT NOT NULL);
NOT NULL will ensure that the cell does not contain a null value. PRIMARY KEY and AUTOINCREMENT expand the field's capabilities post_id.
To verify that the table has been created, run the .tables meta command. As a result, we see our comments table.
The note: To get the table structure, type .schema comments
Now we can enter data into the table.
INSERTING ROWS
Suppose we need to make the following entry:
Name: Shivam Mamgain Email: [email protected] Website: shivammg.blogspot.com Comment: Great tutorial for beginners.
To insert we will use the command INSERT.
INSERT INTO comments (name, email, website_url, comment) VALUES ("Shivam Mamgain", " [email protected]", "shivammg.blogspot.com", "Great tutorial for beginners.");
There is no need to specify a value for post_id because it will be generated automatically thanks to the AUTOINCREMENT setting.
To fill your hand, you can insert a few more lines.
SAMPLE
To fetch data we will use the command SELECT.
SELECT post_id, name, email, website_url, comment FROM comments;
The same request might look like this:
SELECT * FROM comments;
As a result, all rows will be extracted from the table. The result may appear without column delimitation and without a header. To fix this we do:
To display the header, enter .headers ON .
To display columns, run the command .mode column .
We carry out SELECT request again.
The note: The display type can be changed using the .mode meta command.
UPDATE
Let's assume that the field email for user 'Shivam Mamgain' needs to be changed to ' [email protected]’. We run the following command:
As a result, the entry will be changed.
The note: Column value name may not be unique, so more than one row may be affected by the command. For all users where the value name= ‘Shivam Mamgain’, field email will be changed to ‘ [email protected]’. To change a specific line, you need to track it using the field post_id. We defined it as PRIMARY KEY, which ensures the uniqueness of the value.
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 you 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 lack of understanding 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
The other day I needed to make a system for quickly generating data and saving it in an SQLite database. And it all had to work very quickly. By the way, it was necessary to add the database to MySQL. As for MySQL, there is a wide selection of tools for adding data:
- simple INSERT
- INSERT and many added values (BULK INSERT)
- adding via CSV files
The fastest, without a doubt, was option No. 3. As a matter of fact, it was already used in the system. By the way, this option allows you to add up to several hundred thousand/million records to the database in a matter of seconds. Of course, the server hardware also plays a big role here, but this will not be considered now.
As we all know very well, and have seen more than once, greater capabilities require greater resources and/or additional software to add interaction with this system and impose additional restrictions.
Unlike MySQL, SQLite is quite lightweight and only needs one library to work with it. Database files can be placed anywhere and transferred. But, adding to the database can only be done through a simple INSERT. This means that adding via "BULK" INSERT is not supported. And, if the table has at least one key (at least it will be a primary key), then adding one record also implies rebuilding the indexes. Now imagine you need to add several thousand records in the shortest possible time. Despite how light and fast SQLite is, it takes quite a lot of time.
An unexpected solution was found (at least for me - I did not study all the possibilities of this database) - the use of transactions. This gave a 10-100 times increase in speed. Saving/successfully completing a transaction is fast - in fact, the data is simply dumped into the database file.
What was described was good, but for the complete set it was necessary that this data be written to different sqlite files. For this, a simple and proven method was used - the use of several descriptors. Fortunately, there were few files. After all these manipulations, we have a script that works in less than 5 minutes and writes data to N files with M lines in each. Let me make a reservation right away - in this case there is one table in each database file.
And now some numbers:
In test databases, a transaction was opened for each file and then closed and saved. Important note: Each SQLite can only allow one handle per file. If you try to interact with the database with the second handle, an error will be thrown.
As you can see, this approach allows you to add a large amount of data in minimal time.
It's worth mentioning that when I said that the only way to add a database to SQLite is a simple INSERT, I didn't indicate that this is the only option using the SQL language.
There is another option - an analogue of “LOAD DATA …” in MySQL - importing data from CSV files. Only this option allows you to add data directly from the SQLite environment. In other words, you need to first open the database - for example, call it from the console.
Import path_to_csv_file table
The default delimiter is the "|" character.
In order to change the separator, you need to call the command:
Separator
As a separator, you need to specify a column separator, for example, “,”.
The speed of addition in this case exceeds the speed of the previously described method by a factor of 100. So why was this method not used? The point is this: a server-side programming language (in this case PHP) can use one version of the sqlite library, and another version may (or may not) be installed on the system. When combining these two methods of sqlite interaction, the database may be unavailable for one of the options - an error will be thrown when trying to read it.