Lightweight database SQLite combined with PHP Development

SQLite is a lightweight database. It is designed to be embedded and has been used in many embedded products. It occupies very low resources. In embedded devices, it may only take several hundred KB of memory. It supports mainstream operating systems such as Windows, Linux, and UNIX, and can be combined with many programming languages, such as TCL, PHP, Java, and ODBC interfaces, similar to MySQL and PostgreSQL, the two world-renowned open-source database management systems, the processing speed is faster than that of them.

 

Lightweight database SQLite combined with PHP Development

SQLite is a lightweight database. It is designed to be embedded and has been used in many embedded products. It occupies very low resources. In embedded devices, it may only take several hundred KB of memory. It supports mainstream operating systems such as Windows, Linux, and UNIX, and can be combined with many programming languages, such as TCL, PHP, Java, and ODBC interfaces, similar to MySQL and PostgreSQL, the two world-renowned open-source database management systems, the processing speed is faster than that of them.

Although SQLite is small, the supported SQL statements are not inferior to those of other open-source databases. The supported SQL statements include:

Attach Database
Begin transaction
Comment
Commit transaction
Copy
Create Index
Create Table
Create trigger
Create View
Delete
Detach Database
Drop Index
Drop table
Drop trigger
Drop View
End transaction
Explain
Expression
Insert
On conflict clause
Pragma
Replace
Rollback transaction
Select
Update

It also supports transaction processing. Some people also say that it is like Microsoft's access. Sometimes it is a bit similar, but in fact they are quite different. For example, SQLite supports cross-platform, simple operations, and can directly create databases in many languages. Unlike access, SQLite requires office support. If you are a very small application or want to do embedded development without a suitable database system, you can now consider using SQLite. Its latest version is 3.2.2, and its official website is: http://www.sqlite.org, which can get the source code and documentation above. At the same time, because the database structure is simple and the system source code is not much, it is also suitable for professionals who want to study database system development.

Now let's start with a brief introduction. I want to clarify a few questions: first, how to install and use, and second, how to develop with PHP.

I. Installation

1. Windows Platform

Download files under Windows, is actually a command line Program, (: http://www.sqlite.org/sqlite-3_2_2.zip), this command line program is used to include the generation of database files, execution of SQL queries, backup database and other functions.
After the download, for example, we extract to the D:/downloads/SQLite/sqlite-3_2_2 directory, then we Enter cmd and enter the directory:
Cd d:/downloads/SQLite/sqlite-3_2_2.
D:/downloads/SQLite/sqlite-3_2_2> sqlite3 test. DB
# If test. DB does not exist, a database file is generated. If yes, the database file is directly used, which is equivalent to the use in MySQL.
SQLite version 3.2.2
Enter ". Help" for instructions
SQLite>
# SQLite prompt. If you want to view the Command help input. Help, all system commands in SQLite start:
SQLite>. Help
. Databases list names and files of attached Databases
. Dump? Table? ... Dump the database in an SQL text format
. Echo on | off turn command echo on or off
. Exit exit this program
. Explain on | off turn output mode suitable for explain on or off.
. Header (s) on | off turn display of headers on or off
. Help show this message
. Import file table import data from file into table
. Indices table show names of all indices on table
. Mode mode? Table? Set output mode where mode is one:
CSV comma-separated values
Column left-aligned columns. (See. width)
HTML <Table> code
Insert SQL insert statements for table
Line one value per line
List values delimited by. Separator string
Tabs tab-separated values
TCL list elements
. Nullvalue string print string in place of null values
. Output Filename send output to filename
. Output stdout send output to the screen
. Prompt main continue Replace the standard prompts
. Quit exit this program
. Read filename Execute SQL in Filename
. Schema? Table? Show the create statements
. Separator string change Separator Used by output mode and. Import
. Show show the current values for various settings
. Tables? Pattern? List names of tables matching a like pattern
. Timeout MS try opening locked tables for MS milliseconds
. Width num... Set column widths for "column" Mode
SQLite>

# Create a database catlog
SQLite> Create Table catalog (
...> ID integer primarykey,
...> PID integer,
...> Name varchar (10) unique
...> );
SQLite>
# If the table exists, the following message is displayed:
SQL error: Table catalog already exists
# Create index information
Create index catalog_idx on catalog (id asc );
# Check the table information to see how many tables are there.
SQLite>. Table
AA catalog
# View the table structure:
SQLite>. schema catalog
Create Table catalog (
Id integer primary key,
PID integer,
Name varchar (10) unique
);
Create index catalog_idx on catalog (id asc );
# Insert a record to the data table
SQLite> insert into catalog (ppid, name) values ('001', 'heiyeluren ');
# If the expression is incorrect, the following error message is displayed:
SQL error: near "set": syntax error
# How many records are retrieved
SQLite> select count (*) from Catalog;
1
# Search records
SQLite> select * From Catalog;
1 | 1 | heiyeluren

There is no problem if you use standard SQL statements. You can go to the official website to view help information. In addition, SQLite does not support modifying the table structure. If you want to modify the table structure, you must delete the table and create it again. Therefore, you must consider scalability when creating the table. It is estimated that this function will be enhanced in the future.

2. Linux/Unix Platforms

Error: I have not installed it yet, but it is estimated that it is similar to windows. I will add this part in another day.

 

Ii. Development of SQLite in PHP

In PHP 5, MySQL is no longer supported by default, but SQLite is supported by default, which shows how influential it is. If you want to develop PHP for SQLite, we recommend that you use PHP 5.0.0 or later, I am currently using PHP 5.0.4, which directly supports SQLite extension. Here I will not elaborate on how to install PHP extension. If you are not clear about it, you can view the PHP documentation.
Here I mainly talk about the development of SQLite. Currently, mainstream PHP dB classes support SQLite drivers, including pear: DB and ADODB classes. Therefore, using DB for development is also a good choice.

(The following operations are performed on Windows XP for ease)

1. Use PHP to operate the SQLite database that has been established

If you have a database and table structure that has been established through sqlite.exe, you can directly operate on it. PHP has many processing functions for SQLite. You can view the PHP manual for details.

We use the sqlite_open () function to open an SQLite database. If it returns a successful resource operation and false if it fails, all subsequent operations will be performed on this resource, execute an SQL query using the sqlite_query function.

Below I assume that you have an SQLite database file ABC. DB in the current PHP program directory. We will operate on this file:

<? PHP
// Open the SQLite Database
$ Db = @ sqlite_open ("ABC. DB ");
// Exception Handling
If (! $ DB) Die ("connection SQLite failed./N ");
// Add a database named foo
@ Sqlite_query ($ db, "create table Foo (Bar varchar (10 ))");
// Insert a record
@ Sqlite_query ($ db, "insert into Foo values ('fnord ')");
// Retrieve all records
$ Result = @ sqlite_query ($ db, 'select bar from foo ');
// Print the obtained result
Print_r (sqlite_fetch_array ($ result ));

?>
The output result is:

Array
(
[0] => fnord
[Bar] => fnord
)

This proves that the code is successfully executed. If no input is provided, check the program or whether your database file exists.

With this basic operation, you can consider using more complex operations and SQL statements to operate on it, so that it can help you manage information. You can create a message book or a CMS system, I think there is no problem.

2. Use PHP to create and operate databases

If you do not have any tools such as sqlite.exe, you can also use PHP to create and manage an SQLite database.
In fact, the content of the database created by the sqlite.exe program is empty. In fact, only after the table is created and the data is added can the database file be used. Can we manually add a file, for example, an empty test. DB file and operate on it. This is all right. Next we will use the PHP program to create a database, and execute the simple function of creating a data table, inserting data, and retrieving data.

First, let's look at the Code: (the code is long, but easy to understand)

<? PHP
/**
* File: SQLite. php
* Function: Processing SQLite Databases
* Author: heiyeluren
* Time: 2005-8-5
*/
Define ("ln", _ line _); // row number
Define ("FL", _ file _); // The current file
Define ("debug", 0); // debug Switch

$ Db_name = "heiyeluren. DB ";
// Create a database file with the file content Blank
If (! File_exists ($ db_name )){
If (! ($ Fp = fopen ($ db_name, "W + "))){
Exit (error_code (-1, LN ));
}
Fclose ($ FP );
}
// Open the database file
If (! ($ Db = sqlite_open ($ db_name ))){
Exit (error_code (-2, LN ));
}
// Generate the data table structure
If (! Sqlite_query ($ db, "Drop table test ")){
Exit (error_code (-3, LN ));
}
If (! Sqlite_query ($ db, "create table test (ID integer primary key, PID integer, name varchar (10) Unique )")){
Exit (error_code (-3, LN ));
}
// Insert a data entry
If (! Sqlite_query ($ db, "insert into test (name) values ('heiyeluren ')")){
Exit (error_code (-4, LN ));
}
// Retrieve the data
If (! ($ Result = sqlite_query ($ db, "select * from test "))){
Exit (error_code (-5, LN ));
}

// Retrieve and display the Retrieved Data
While ($ array = sqlite_fetch_array ($ result )){
Echo "ID:". $ array [ID]. "<br> name:". $ array [name];
}

/* Error message code function */
Function error_code ($ code, $ line_num, $ DEBUG = Debug)
{
If ($ Code <-6 | $ code>-1 ){
Return false;
}
Switch ($ code ){
Case-1: $ errmsg = "create database file error .";
Break;
Case-2: $ errmsg = "Open SQLite database file failed .";
Break;
Case-3: $ errmsg = "create table failed, table already exist .";
Break;
Case-4: $ errmsg = "insert data failed .";
Break;
Case-5: $ errmsg = "query database data failed .";
Break;
Case-6: $ errmsg = "fetch data failed .";
Break;
Case-7: $ errmsg = "";
Break;
Default: $ errmsg = "Unknown error .";
}

$ M = "<B> [Error] </B> <br> file :". basename (FL ). "<br> line :". ln. "<br> mesg :". $ errmsg. "";
If (! $ Debug ){
($ M = $ errmsg );
}
Return $ m;
}

?>

If you are correct, the program outputs the following:

ID: 1
Name: heiyeluren

Our above programs include relatively complete functions, including debugging, exception handling, and database access, which is a simple application. If you are interested, you can also perform expansion.

 

* End:

The basic operations are described here. I will complete the content in the future. If you are interested, you can study it. Maybe your personal homepage needs such a small database to help you.

* Reference:
Http://www.donews.net/limodou/archive/2004/03/21/7997.aspx
Http://www.linuxsir.org/bbs/showthread.php? P = 1213668 # post1213668

* SQLite Resources
Http://www.sqlite.org
SQL Syntax: http://www.sqlite.org/lang.html
Development Documentation: http://www.sqlite.org/docs.html
Problem: http://www.sqlite.org/faq.html
: Http://www.sqlite.org/download.html

Author: heiyeluren
Date: 2005-8-5