 |  |  |
 |
 |
 |
 |
SQLite and PHP
For whom this clause{article}
Given clause{article} intends PHP to the programmers who have shown interest to expansion SQLite. Clause{article} will present the reader the functionality offered{suggested} by expansion PHP SQLite, and also will consider advantages SQLite in comparison with other DB. It is supposed, that the reader is familiar with bases PHP and SQL. Some operational experience with MySQL or PostgreSQL, will promote the best understanding of about what there will be a speech.
Introduction
Recently, you, probably, heard about new expansion for PHP: SQLite. There are many reasons on which SQLite can seem the best achievement since have learned to cut bread. SQLite offers sound SQL the interface to not relativistic database and creates elegant alternative to bulky interfaces of other databases without loss of functionality or speed as it would be possible to expect. We shall consider this surprising expansion in clause{article} and, we shall hope, we will manage to confirm those advantages, about which, probably, you naslyshany.
What is SQLite?
SQLite is a built in library in which much of standard SQL 92 is realized. Its{her} claim on popularity is both actually @ cursor of base, and its{her} interface (his cursor is more exact) within the limits of one library, and also an opportunity to store{keep} all data in one file. I carry a position of functionality SQLite somewhere in between MySQL and PostgreSQL. However, in practice, SQLite not seldom it appears in 2-3 times (and even more) is faster. Such probably thanking vysokouporjadochennoj to internal architecture and elimination of necessity for connections such as "server - client" and "client - server".
All this collected in one package, only the little is more on the size of a client part of library MySQL, is impressing achievement for a high-grade database. Using highly an effective infrastructure, SQLite can work in tiny volume of memory selected for it{her}, much smaller, than in any other systems of a DB. It does{makes} SQLite by very convenient tool with an opportunity of use practically in any problems{tasks} assigned on a database.
What to me to use from SQLite?
Besides speed and efficiency at SQLite there is a number{line} of other advantages which do{make} by its{her} ideal decision for many problems{tasks}. As database SQLite inherently - usual files, no what necessity for additional means of administration demanding a lot of time for creation of complex structure of access rights for protection of the user databases. All this is already automatically supported by the organization of access rights in the most file system, it also means (in the field of restriction of space), that is not required what special rules for tracking filling disk space by the user. Advantage to users that there is an opportunity to create such quantity{amount} of databases what they will wish themselves plus the absolute control over all these databases.
The fact, that a database is a unique file, does{makes} its{her} easily transferable{tolerable}. SQLite besides, eliminates necessity for start of additional service processes (daemons) which could "eat off" a significant amount of memory and other resources, even in case of moderate use of a database.
Expansion SQLite
As the newest expansion of a DB, SQLite has had the luck to be free from a code for return compatibility, in contrast more to old expansions, such as MySQL which are obliged to support out-of-date functionality owing to return compatibility. It also allows new expansion to use the most newest development in PHP for achievement of the highest level of execution{performance} and functionality. Developers have facilitated a problem to users, having created expansion easy for transition to it from other systems of a DB, having left the interface similar that has already been realized in PHP.
SQLite also supports the floppy mechanism for transfer of resources of a database to procedural interfaces, doing{making} it is equally easy for transition from MySQL where the resource is passed the last, and PostgreSQL where he is passed the first.
SQLite also distinguishes the powerful object-oriented interface which can be used for effective extraction of the data from base, relieving you from necessity of performance of your own shell for the procedural interface. As shown in an example is lower, the object-oriented interface also allows you to avoid transfer of all resources by a heap.
<? php
// We create new base (OO the interface)
$db = new sqlite_db ("db.sqlite");
// We create the table foo and we insert something for an example
$db-> query (" BEGIN;
CREATE TABLE foo (id INTEGER PRIMARY KEY, name CHAR (255));
INSERT INTO foo (name) VALUES (' Ilia ');
INSERT INTO foo (name) VALUES (' Ilia2 ');
INSERT INTO foo (name) VALUES (' Ilia3 ');
COMMIT; ");
// We carry out search
$result = $db-> query (" SELECT * FROM foo ");
// It is passable in a cycle on cells
while ($result-> hasMore ()) {
// We receive the current cell
$row = $result-> current ();
print_r ($row);
// We pass to the following cell
$result-> next ();
}
// There is no special a necessity as PHP itself will break off connection
unset ($db);
?>
Installation SQLite
In PHP 5.0 installation SQLite has the feature as both expansion and library are connected together, therefore everything, that it is necessary for you to make is to add-with-sqlite in line konfigurirovanija. I still would recommend to establish SQLite, but only in a case if speech about otkompilirovannom a binary file that will allow you to open base and to operate her without use PHP. It is rather useful for debugging and performance of various simultaneous commands, and also useful for testing a code of searches. In the future, enough often you can find out, that connected library SQLite "is obsolete" a little, therefore the sheaf of yours PHP with external library will allow you to benefit by last corrections and innovations SQLite. It also will allow you to update in the future yours SQLite without recompilation PHP.
To collect expansion SQLite as external library, simply use-with-sqlite =/path/to/lib/.
I also should mention, that expansion SQLite passes an exhaustive series of tests, both for object-oriented, and for procedural interfaces. Each separate function, and each method supported SQLite are tested. It is a magnificent source of examples not only how each method and each function SQLite works, but also the expected conclusion allowing you to see an end result of each operation.
Use SQLite
The procedural interface to SQLite almost same, as at MySQL and other expansions of a DB. Generally transition to SQLite will demand only to change mysql/pq/etc … a prefix of function on sqlite.
<? php
// We create new base (the procedural interface)
$db = sqlite_open ("db.sqlite");
// We create the table foo
sqlite_query ($db, " CREATE TABLE foo (id INTEGER PRIMARY KEY, name CHAR (255)) ");
// We add something for an example
sqlite_query ($db, " INSERT INTO foo (name) VALUES (' Ilia ') ");
sqlite_query ($db, " INSERT INTO foo (name) VALUES (' Ilia2 ') ");
sqlite_query ($db, " INSERT INTO foo (name) VALUES (' Ilia3 ') ");
// We carry out search
$result = sqlite_query ($db, " SELECT * FROM foo ");
// It is passable in a cycle sample on cells
while ($row = sqlite_fetch_array ($result)) {
print_r ($row);
/* Each result will look approximately so
Array
(
[0] => 1
[id] => 1
[1] => Ilia
[name] => Ilia
)
*/
}
// We close connection with base
sqlite_close ($db);
?>
Actually significant differences between SQLite and other databases are in the cursor. As against other DB in SQLite there is no binding to types; all data are saved as a line terminating by symbol NULL that is better, than binary data presentation in stolbcakh special type. Owing to compatibility SQLite while supports type of the specification in designs CREATE TABLE, for example, such as INT, CHAR, FLOAT, TEXT and to that similar, but it is real them does not use. Inside base, SQLite only does{makes} distinction between the line and integer data during sorting. Therefore, if you are not going to to sort the data, you can do without the instruction{indication} of special type of fields at creation of tables in SQLite.
" Beztipovaja the nature " SQLite does{makes} sorting and comparison of the data to a certain extent more slowly as each time SQLite will be compelled to define{determine} type of the data and to apply or the line mechanism of sorting / comparison or numerical. SQL tables often demand automatically appropriated{given} key for fast access to cells, meaning return of the link to last added cell. For SQLite this syntax, to put it mildly, is useless. To create such table, you need to declare a field as INTEGER PRIMARY KEY, that is more convenient than instruction{indication} of the specialized type or giving of additional properties which specify that the field is autoincremental.
The connected searches
As you can expect, SQLite bears{carries} in yourself many new features improving execution{performance} and expanding functionality. One of such features - an opportunity to carry out the connected searches which mean performance of plural searches through function of performance of one search. It reduces quantity{amount} of involved functions PHP and thus increases speed of job of a script. And also allows you to group easily blocks of searches inside transactions, improving performance further. Such opportunity can be the significant factor in execution time of plural references{manipulations} on recording in base. However there are some juicy features about which it is not necessary to forget.
If any search in SQLite uses something entered by the user directly, you should take additional safety measures for check of such input to stop undesirable search. In contrast MySQL where such can cause only misunderstanding at search to a DB and give out a mistake, in SQLite it will allow attacking to execute any search about your server with possible{probable} pitiable consequences. If you add recordings through the block of searches and would like to return the identifier sqlite_last_insert_rowid () will well cope with such problem , but will return the identifier only to last recording. On the other hand, at attempt to define{determine} what quantity{amount} of cells it has been changed, using sqlite_changes (), we shall receive the result containing the general{common} number of cells changed all executed searches. If your block of searches contains SELECT, be convinced what is it the very first search, otherwise your final sample will not contain the cells returned by this search.
<? php
// We create new base only in memory
$db = new sqlite_db (":memory: ");
// We create the table of two stolbcov bar and it is added in it{her} 2 cells
/* To improve performance, all block of search is grouped inside transaction. */
$db-> query (" BEGIN;
CREATE TABLE bar (id INTEGER PRIMARY KEY, id2);
INSERT INTO bar (id2) VALUES (1);
INSERT INTO bar (id2) VALUES (2);
COMMIT; ");
// Will print " 2 insert queries "
echo $db-> changes (). " insert queriesn ";
// Will print: " last inserted row id: 2 "
echo " last inserted row id: ". $db-> last_insert_rowid ();
?>
New functions
Besides the new internal features, SQLite offers a number{line} of new functions which simplify and accelerate extraction of the data from base.
<? php
$db = new sqlite_db ("db.sqlite");
/* We carry out search with sample of all cells in an associative file */
$result_array = $db-> array_query (" SELECT * FROM foo ", SQLITE_ASSOC);
print_r ($result_array);
?>
It allows both to carry out search and to take the functions given by one call, bringing to nothing all superfluous job PHP. In itself PHP the script becomes simpler, at the expense of use of only one function there where differently you had to use some functions for extraction of the data working in a cycle. When the result only one column comes back, it is possible to use sqlite_single_query () which instantly returns a line or a file of lines depending on number of cells from which the information is taken.
<? php
$db = sqlite_open ("db.sqlite");
// We receive the identifier of a column (as a line)
$id = sqlite_single_query ($db, " SELECT id FROM foo WHERE name ='Ilia ' ");
var_dump ($id); // string (1)
// In case concurrences it is more than one, result will be a file
$ids = sqlite_single_query ($db, " SELECT id FROM foo WHERE name LIKE ' I % ' ");
var_dump ($ids); // array (3)
?>
As well as in a case with other features, you can use it, but you should not abuse it. In case of sample of all data, search in one pass, you should remember, that all results will be saved in memory. If the result of sample contains a plenty of the data, the price of expenses for memory will certainly bring to nothing all advantages the numbers of calls of functions received by decrease{reduction}. Hence, you need to reserve use of these functions for cases of sample of a small amount of the data.
Iteratory in SQLite
In PHP 5.0 there is other way of sample given by search with use iteratorov.
<? php
$db = new sqlite_db ("db.sqlite");
// We reduce use of memory, carrying out search without caching
$res = $db-> unbuffered_query (" SELECT * FROM foo ");
foreach ($res as $row) {// we receive result in a cycle
// A code of a conclusion
print_r ($row);
}
?>
Reception of results in a cycle works the same way, as well as pass on a file in a cycle through foreach () except that at this time you do not have access to keys and value represents a file containing the data in special cells. As iteratory it not functions, and internal indexes in the cursor, they demand much less resources in comparison with functions sqlite_fetch_ * (), and do not demand, that results kehshirovalis` in memory. The end result will be extremely fast, it is more simple and accessible method of extraction of the data. No what by-effects in use iteratorov objects in SQLite and when you needed to receive result as set of lines, you need to remember such opportunity.
Auxiliary functions
SQLite also distinguishes a number{line} of auxiliary functions which can it is useful in an operating time with a database. One of such functions, sqlite_num_fields (), can be used for definition of quantity{amount} stolbcov in special results of sample. As a variant if you are going to to take the data, simply use count () with the first result which will return to you the same number. If keys are taken both line, and numerical, you need to separate result as in a resulting file there will be so much ocurrences how much exists fields in the table. This number can be important, if it is necessary for your script to receive names of fields inside any table. In that case you could use sqlite_field_name () in a cycle, for access to this information, as shown in an example is lower.
<? php
$db = new sqlite_db ("db.sqlite");
$res = $db-> unbuffered_query (" SELECT * FROM foo LIMIT 1 ");
// We receive quantity{amount} of fields
$n_fields = $res-> num_fields ();
while ($i <$n_fields) {
// We take separate fields
$field_name = $res-> field_name ($i ++);
echo $field_name. "n";
}
?>
Certainly, it not an ideal way for reception of names stolbcov from the table, is simple to that he appears unsuccessful in a case when the table does not contain any cell, and also on that, what is it will demand from you extraction of the data which you are not going to to use. Much the best decision will be use of function sqlite_fetch_column_types () which receives stolbcy together with their types from the table is not dependent on, whether there are there data whether or not.
Advantage of buffering
In most cases, owing to performance or use of memory, you do not want to use searches without keshirovanija. It can lead to small loss of functionality that can be necessary in the certain cases for which searches without keshirovanija not always the best choice.
For example, suppose, you would like to find out the real quantity{amount} of cells chosen your search. In search without keshirovanija, you should do{make} sample of each separate cell before you can define{determine} her . Keshirovannye searches - more simple way consisting in use of function sqlite_num_rows () which will easily receive this information from result of sample. Searches without keshirovanija also are limited in direct sample of the data, meaning, that you are obliged to choose the data from all cells for one pass. In keshirovannykh searches of such restriction no, you can use sqlite_seek () for displacement on any cell and sample of it{her} of the data, or even for access to cells upside-down if it is necessary.
<? php
$db = new sqlite_db ("db.sqlite");
$res = $db-> query (" SELECT * FROM foo ");
$n_rows = $res-> num_rows (); // we receive number of cells of sample
$res-> seek ($n_rows - 1); // we pass to a position of the following cell
// Extraction of given upside-down
do {
$data = $res-> current (SQLITE_ASSOC); // we obtain the given cells
print_r ($data);
}
while ($res-> hasPrev () ** $res-> prev ()); // and so up to the first cell
?>
The user functions
One of the most interesting opportunities that expansion SQLite gives the table for an opportunity of creation of own functions used in limits SQL. It is possible due to that SQLite contains in one library connected with PHP, both the interface, and a cursor of base. Using sqlite_create_function () you can create functions which further can be applied to result of sample or are used inside any condition WHERE.
<? php
/* We determine a difference between line entered by the user and line from base proceeding from symbols contained in line */
function char_compare ($db_str, $user_str) {
return similar_text ($db_str, $user_str);
}
$db = new sqlite_db ("db.sqlite");
/* We create function char_compare () inside SQLite on the basis of ours PHP functions, char_compare (). 3-rd parameter specifies quantity{amount} of arguments necessary to function */
$db-> create_function (' char_compare ',' char_compare ', 2);
/* We carry out search, where char_compare () it is used for comparison between name and the specified line */
$res = $db-> array_query (" SELECT name, char_compare (name, ' Il2 ') AS sim_index FROM foo ", SQLITE_ASSOC);
print_r ($res);
?>
The opportunity of use PHP in SQL allows you to simplify the given script, doing{making} his accessible to the greater audience of developers. It allows to use PHP as a cursor of patterns which will insert HTML a code into the data from base. In many cases it can simplify a code so strongly, that there will be no necessity for use of an additional cursor of patterns. Besides simplification of a code, it also improves performance of the program and reduces expenses of a script for memory because no what actions with the data occuring in the user space.
Do not overlook, that in the event that the data with which you work, can contain a binary code, you should use function sqlite_udf_decode_binary () for their translation from the internal coding, in which they are stored{kept} in SQLite before to work with them. If you so have acted{arrived}, you need to code the binary data, using sqlite_udf_encode_binary () to be confident, that the data will not be damaged{injured} at the following to them access.
Let's sum up
Now, having seen as works SQLite, and that she can offer, you, probably, pay to it{her} attention for use in the current or future applications. I hope, this brief review has given you some performance about functionality SQLite, and has not dispelled anything that you heard about her good.
As well as any tool, SQLite has strong and weaknesses. Being the ideal decision, for applications small and-or intended mainly for data read-out, she not so well approaches for the large-scale applications which are often carrying out recording in base. This restriction - consequence{investigation} of that architecture SQLite is based on the unique file which does not provide distribution of the data to repeatedly created files, and also uses blocking on recording.
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |