SQLITE IN PHP
Binzywu @ phpe / text
Keywords: SQLite, MySQL, PHP4, PHP5, PEAR, trigger, view, SQL
(One)
SQLite introduction
SQLite's first Alpha version was born in May 2000. There have been 4 years since this year. In May this year, SQLite also ushered in a new mileage: SQLite 3.
Below is your visit to the SQLite official website: Www.sqlite.org first saw the characteristics of SQLite.
ACID transaction
2. Zero Configuration - No need to install and manage configuration
3. A complete database stored in a single disk file
4. Database files can share freely in different bytes
5. Support database size to 2TB
6. Small enough, approximately 30,000 lines C code, 250K
7. Most ordinary databases are faster than some popular databases
8. Simple, easy API
9. Contains TCL bindings, and supports the binding of other languages through Wrapper
10. Good comment source code, and have more than 90% of test coverage
11. Independence: There is no additional dependence
12. Souce complete open, you can be used for any use, including selling it
From the code architecture map you can easily see, yes, SQLite is very simple. For the design ideas of SQLite, simple:
Simple management
2. Simple operation
3. Simplely use it in the program
4. Simple maintenance and customization
Because it is simple, it is fast, but although it is simple, it is still very reliable. Suitable for SQLite applications, website, embedded devices, and applications, application file formats, instead of special files, internal or temporary databases, command line data set analysis Tools, instead of enterprise database, database teaching, test SQL language extension, etc. in demos or tests, but not all are suitable, for example, when using the Server / Client structure, high-load websites, high merging, etc. SQLITE is recommended.
This paper focuses on the application of SQLite in PHP, PHP as an important force in web applications has been moving and developing. In the fifth version of the PHP of Release, I will no longer use mysql as the default support, and It turns into default support. From a certain extent, the wide application of mysql has a great metrics. Although PHP changes support the reasons for mysql authorization, it is also reasonable, reasons for choosing SQLite. It is the features mentioned above. In fact, Mysql never is completely free, you can't use commercial use. And SQLite is complete Open.
(2) SQLite SQL
SQLite's SQL achieves ANSI SQL92 standards to a large extent. Special SQLite supports view, triggers, transactions, support nested SQL. These will talk during the procedure below, so this is temporarily put down, and mainly Talk about some SQL supported by SQLite.
1. Does not support EXISTS, although INSTS is supported in (in is an exists)
2. Do not support multi-database, such as: CREATE TABLE DB1.TABLE1 AS SELECT * from DB2.TABLE1;
3. Do not support stored procedures
4. Does not support Alter View / Trigger / Table
5. Truncate does not support, DELETE does not take the WHERE word, and the effect of Truncate is the same.
6. Does not support Floor and CEILING functions, there are other quite functions
7. Without Auto Increment field, SQLite is actually supporting Auto Increment, ie when setting this field to "Integer Primary Key".
8. IF EXISTS does not support
......
Detailed SQL support can be accessed: http://www.sqlite.org/lang.htm Detailed not support SQL can be accessed: http://www.sqlite.org/cvstrac/wiki?p=unsupportedsql
(3) SQLite's data type
First you will get in touch with the name: Typelessness (None type). To! SQLite is uncustomized. This means you can save any type of data to any of the columns you want to save, No matter what the data type of this column is (not in one case, it is explained later). For SQLite, it is completely effective for the field does not specify the type. Such as:
Create Table EX1 (A, B, C);
It is true that SQLite allows you to ignore data types, but it is still recommended to specify data types in your CREATE TABLE statement. Because data types communicate with you and other programmers, or you are ready to change your database engine. SQLite supports common data types, Such as:
Create Table EX2 (
a varchar (10),
B NVARCHAR (15),
C Text,
D Integer,
e float,
f Boolean,
g Clob,
H blob,
I TimeStamp,
J Numeric (10, 5)
K Varying Character (24),
l National Varying Character (16)
);
As mentioned earlier, the field of SQLite is not typeless. That is, when the field type is "Integer Primary Key".
(4) SQLite's Wrapper
Since SQLite is different from the TCP / IP or RPC access method of other database engines, it is completely local operation. From a certain angle you can say that SQLite and MS Access is very similar, but smaller and more powerful. So-called Wrapper even Package the interface provided by SQLITE to allow other languages to access and use SQLite.
SQLite itself is an interface to C and TCL. So it can be combined with PHP very easy. In addition to PHP's Wrapper, there are many programmers around the world with SQLite's interface package, such as Python, C . , Java, .Net ... The popular language is basically available.
(5) Using SQLite in PHP
1. Installation under PHP
In PHP5, SQLite has been used as a default support. You need to install in PHP4. First go to http://pecl.php.net/package/sqlite to download to SQLite's extension, pay attention to the version under Windows needs to go to HTTP : //snaps.php.net/win32/pecl_stable/php_sqlite.dll Download, Of course, you can also download the code yourself. In fact, you only need to use the command under Linux: 'pear install sqlite' can complete the installation, while in Win Need to modify php.ini, the same, the PHP4 supports SQLite.
At this point, you have no need to install anything, and you have fully supported SQLITE, a simple, fast, and reliable database.
If you need a management software, you can try to use SQLiteManager (www.sqlitemanager.org), a database management system similar to phpMyAdmin.
The interface of the system is approximately as follows:
2. The first PHP program using SQLite.
We create a database called Binzy, and create a table called binzy, there are 2 fields, named, Title., The ID is Integer Primary Key, ie, three primary keys. And insert 2 data Binzy "," JASMIN ".
Open and display data:
IF ($ db = sqlite_open ('../ binzy.db', 0666, $ sqliteerror))
{ // turn on
SQLite $ Result = SQLite_Query ($ DB, 'SELECT * from binzy'); // Query While ($ ROW = SQLITE_FETCH_ARRAY ($ RESULT)) // Get results {
Print 'ID =>'. $ ROW ['myid']. ', name =>'. $ row ['name']. '
'
}
} else {DIE ($ sqliteerror);
The result is as follows,
Next, INSERT records, where we will use SQLite's transaction.
IF ($ db = sqlite_open ('../ binzy.db', 0666, $ sqliteerror) {
SQLITE_QUERY ($ DB, 'Begin Transaction "; // Starting
IF (@Sqlite_Query ($ DB, 'INSERT INTO BINZY "VALUES (/' binzy & jasmin / ')))
{
Print 'execute successfully';
SQLite_Query ($ DB, 'COMMIT Transaction "; // Submit a transaction
}
Else
{
PRINT SQLITE_ERROR_STRING (SQLITE_LAST_ERROR ($ dB));
SQLite_Query ($ DB, 'ROLLBACK TRANSACTION'); / / Rolling Transaction
}
} else {
DIE ($ SQLITEERROR);
}
If the execution fails, this picture will appear.
This is the case,
Yes, if you are familiar with the use of PHP to operate the database to MySQL, then SQLite is almost the same, and it is more concise.
3. Using PEAR :: DB (PHP4)
In the above example, we use the PHP function to access SQLITE directly. This access method is not recommended. Better way to use some data to access abstract layers, such as PEAR's DB. Below is 2 in Query Override. Use a data access to the abstraction layer will be more convenient and safer, and you can minimize costs as possible when you need to migrate.
Require_once ('db.php');
$ dbh = db :: connect ('sqlite: //@localhost/../binzy.db? mode = 0666'); // Open
$ dbh-> setfetchmode (db_fetchmode_assoc);
IF (! db :: iesrror ($ dbh))
{
$ result = $ dbh-> query ('select * from binzy'); // query
IF (! db :: iesrror ($ result))
{
While ($ row = $ result-> fetchrow ()) // read
{Print 'ID =>'. $ Row ['myid']. ', Name =>'. $ Row ['name']. '
'
}
$ dbh-> disconnect ();
}
Else
{
Print ($ dbh-> message);
$ dbh-> disconnect ();
}
}
Else
{
Print ($ dbh-> message);
$ dbh-> disconnect ();
}
4. Using Creole (in PHP5)
Creole is a PHP5-oriented data-oriented data to PHP5. About Creole: Creole: Emerging Data Abstract Layer "in this period. PPEAR :: DB does not change for PHP5, just because PHP5 is good for PHP4 Compatibility, make Pear :: DB still work well under php5. So it is recommended to use Creole when you use PHP5.
Require_once ('creole / creole.php');
$ Connection = NULL;
Try {
$ Connection = creole :: getConnection ('sqlite: //@localhost/../binzy.db? Mode = 0644'); // Get Connection
$ r = $ connection-> executeQuery ('select * from binzy'); // Get ResultSet While ($ r-> next ())
{
Print 'ID =>'. $ r-> getint ('myid'). ', name =>'. $ r-> getstring ('name'). '
';
}
$ Connection-> close ();
}
Catch (SQLEXCEPTION $ EXCEPTION) // Catch Exception
{
$ Connection-> close ();
Print $ exception-> getMessage ();
}
(6) Summary
With the upcoming PHP5, give us a lot of new language features, making PHP more suitable for building a strong robust system. With the PHP5 walks into the PHP developer's sight, SQLite brings us. Surprises from MySQL. Yes, he is simple but powerful, stable. And in the new version of SQLite3, the new version of SQLite3 has been released, not only brings new file structure, but also brings Many new features.
