The development of lightweight database SQLite combined with PHP

Data | database

SQLite is a lightweight database, its design goal is embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, may only need hundreds of K of memory is enough. It can support Windows/linux/unix and so on mainstream operating system, simultaneously can with many programming language combination, for instance TCL, PHP, Java and so on, also has the ODBC interface, similarly compared to MySQL, PostgreSQL the two open source world's most famous database management system, it processing faster than them.

SQLite, although small, supports SQL statements that are not inferior to other open source databases, and its supported SQL includes:

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 capabilities and so on. Others say it's like Microsoft Access, and sometimes it feels a bit like it, but in fact they are very different. For example, SQLite supports Cross-platform, simple operations, and the ability to create databases directly in many languages without the need for office support like access. If you are a very small application, or if you want to do embedded development, there is no suitable database system, then you can consider using SQLite now. Currently its latest version is 3.2.2, its official website is: http://www.sqlite.org, can get source code and documentation on it. At the same time, because the database structure is simple, the system source code is not many, also suitable to study the database system development professionals.

Now we start a simple introduction, mainly I want to clarify a few issues, one is how to install the use, and the second is how to develop with PHP.

First, installation

1. Windows platform

Downloading a file under Windows is actually a command-line program (download address: Http://www.sqlite.org/sqlite-3_2_2.zip), which includes the ability to generate database files, execute SQL queries, back up databases, and so on.
After downloading such as we unzipped to D:\Downloads\sqlite\sqlite-3_2_2 this directory, then we enter CMD, and enter this 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, then a database file is generated, and if it exists, the database file is used directly, which is equivalent to the use in MySQL
SQLite version 3.2.2
Enter '. Help ' for instructions
Sqlite>
# SQLite prompt, if you want to see the command to help enter. Assist, all system commands in SQLite are. To begin with:
Sqlite>. Help
. databases List names and files of attached databases
. Dump? TABLE?      ... Dump the database in a SQL text format
. Echo on| Off Turn command echo on or off
. Exit Exit this program
. Explain on| Off Turn output mode suitable to 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 the all indices on TABLE
. Mode mode?     TABLE? Set output mode where mode is one of:
CSV comma-separated values
Column left-aligned columns. (The. Width)
HTML HTML <table> Code
Insert SQL INSERT statements for TABLE
Line one value/Line
List Values delimited by. Separator string
Tabs tab-separated values
Tcl 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 num ... Set column widths for "column" mode
Sqlite>

# we create a database catlog
sqlite> CREATE TABLE catalog (
   ...> ID Integer PrimaryKey,
   ...> pid integer,
   ...> name varchar (TEN) UNIQUE
   ... >);
Sqlite>
# If the table exists, it prompts:
SQL error:table catalog already exists
# we create INDEX information
CREATE INDEX CATALOG_IDX on CA Talog (id ASC);
# We look at the table for information, see how many Tables
sqlite>. Table
aa       Catalog
# View the structure of the table:
sqlite>. Schema Catalog
CREATE TABLE catalog (
ID integer primary key,
PID integer,
name varchar (10) UNIQUE
);
CREATE INDEX catalog_idx on catalog (ID ASC);
# Inserts a record into the datasheet
sqlite> INSERT INTO catalog (Ppid,name) VALUES (' 001 ', ' Heiyeluren ');
# success without any prompts, if an expression error prompts an error message:
SQL error:near "set": Syntax error
# retrieves how many records
Sqlite> select COUNT (*) from catalog;
1
# retrieves the search record
sqlite> select * from Catalog;
1|1|heiyeluren

There is no problem using standard SQL, and it is not clear that you can go to the official website to view help information. Also want to explain is that SQLite does not support modifying the table structure, if you want to modify the table structure, only delete the table to re-establish, so set up the table must consider extensibility. It is expected that the function will be strengthened in the future.

2. Linux/unix Platform

Error: Has not been installed, hehe, but it is estimated to be similar to Windows, another day to fill this part of the content.

Second, PHP to the development of SQLite

PHP 5 no longer defaults to support MySQL, but the default support SQLite, visible its influence how big, so if you want to do sqlite PHP development, I suggest you use PHP 5.0.0 version, I currently use PHP 5.0.4 version, directly support SQLite extension , I'm not going to tell you how to install PHP extensions, and if you're not sure you can view the PHP documentation.
Here I mainly talk about the development of SQLite. Currently, PHP's mainstream DB classes support SQLite drivers, including pear::D Class B, ADODB classes are supported, so using DB for development is also a good choice.

(This is done for simplicity, all on the Windows XP platform)

1. SQLite Database with PHP operation already established

If you have a database and a table structure that has already been built through Sqlite.exe, then you can manipulate it directly. There are more processing functions for SQLite in PHP, and you can view the PHP manual for more information.

We use the Sqlite_open () function to open a SQLite database, which succeeds in returning an operation resource, failure returns false, then all subsequent operations are performed on this resource, executing an SQL query using the Sqlite_query function.

Let me assume that you have a abc.db SQLite database file in the current PHP directory, and we'll do the following:

<?php
Open the SQLite database
$db = @sqlite_open ("abc.db");
Exception handling
if (! $db) die ("Connection Sqlite failed.\n");
Add a database called 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 results obtained
Print_r (Sqlite_fetch_array ($result));

?>
The output we see is:

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

Prove that our code executed successfully, no input please check the program, or whether your database file exists.

So with this basic operation, you can consider using more complex operations and SQL to manipulate it, let it help you manage the information, you can do a message, or do a CMS system, I think there is no problem.

2. Use PHP to build a database and manipulate

If you don't have any sqlite.exe tools, you can also use PHP to create a SQLite database and manage it.
In fact, through the Sqlite.exe program to create a database, the content is empty, in fact, only later create a table, add data, the database file, then we can manually add a file, such as an empty test.db file, and to operate on it. This is perfectly OK, and we'll use a PHP program to complete the creation of a database and perform a simple function of creating a datasheet, inserting data, and retrieving data.

First we look at the code: (The code is long, but it's easier to understand)

<?php
/**
* File: sqlite.php
* Function: Processing of the SQLite database
* Author: Heiyeluren
* Time: 2005-8-5
*/
Define ("LN", __line__);//Line number
Define ("FL", __file__);/Current File
Define ("Debug", 0);//debug switch

$db _name = "heiyeluren.db";
Create a database file with empty file contents
if (!file_exists ($db _name)) {
if (!) ( $fp = fopen ($db _name, "w+"))) {
Exit (Error_code ( -1, LN));
}
Fclose ($FP);
}
Open database File
if (!) ( $db = Sqlite_open ($db _name))) {
Exit (Error_code ( -2, LN));
}
To produce a 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 () UNIQUE)") {
Exit (Error_code ( -3, LN));
}
Insert a piece of data
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));
}

Gets the retrieved data and displays
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 do the right thing, then the final output of the program:

Id:1
Name:heiyeluren

The above procedures include a more complete function, debugging, exception handling, access to database functions, is a simple application. You can also expand if you are interested.

End

Our basic operation is here, later I will fill in the content. If you are interested in studying, perhaps your personal homepage will need such a small database to help you.

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

* SQLite Resources
Official website: http://www.sqlite.org
SQL syntax: http://www.sqlite.org/lang.html
Development Documentation: Http://www.sqlite.org/docs.html
FAQ: http://www.sqlite.org/faq.html
Download Address: http://www.sqlite.org/download.html

Author:heiyeluren
Date:2005-8-5