SQLite is a lightweight database. Its design target is embedded, and it has been used in many embedded products. It occupies very low resources. In embedded devices, it may only require a few hundred K. Memory is enough. It can support mainstream operating systems such as Windows/Linux/Unix, and can be combined with many programming languages, such as Tcl, PHP, Java, etc., as well as ODBC interfaces. It is also compared to Mysql and PostgreSQL, two world-famous open source software. In terms of database management systems, its processing speed is faster than them all.
Although SQLite is very small, the SQL statements it supports are not inferior to other open source databases. The SQL it supports 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
At the same time, it also supports transaction processing functions and so on. Some people also say that it is like Microsoft's Access. Sometimes it feels a bit similar, but in fact they are very different. For example, SQLite supports cross-platform, is simple to operate, and can directly create databases in many languages, unlike Access, which does not require the support of Office. If you have a very small application, or you want to do embedded development and do not have a suitable database system, then you can consider using SQLite now. At present, its latest version is 3.2.2, and its official website is: http://www.sqlite.org , where the source code and documentation can be obtained. At the same time, because the database structure is simple and there is not much system source code, it is also suitable for professionals who want to study database system development.
Now let's start with a brief introduction. Mainly I want to clarify a few issues, one is how to install and use, and the other is how to develop it in conjunction with PHP.
1. Installation
1. Windows platform
downloads files under windows, which is actually a command line program (download address: sqlite>
# SQLite prompt. If you want to view command help, enter .help. In sqlite, all system commands begin with .:
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 of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html 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 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>
www.knowsky.com
# We create a database catlog
sqlite> create table catalog(
...> id integer primarykey,
...> pid integer,
...> name varchar(10) UNIQUE
...> );
sqlite>
# If the table exists, it will prompt:
SQL error: table catalog already exists
# We create index information
create index catalog_idx on catalog (id asc);
# We check the table information to see how many tables there are
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);
#Insert a record into the data table
sqlite> insert into catalog (ppid,name) values ('001','heiyeluren');
#No prompts if successful. If the expression is wrong, an error message will be prompted:
SQL error: near "set": syntax error
# Retrieve how many records there are
sqlite> select count(*) from catalog;
1
# Retrieve search records
sqlite> select * from catalog;
1|1|heiyeluren
Anyway, there is no problem if you use standard SQL to operate. If you are not sure, you can check the help information on the official website. In addition, it should be noted that SQLite does not support modifying the table structure. If you want to modify the table structure, you can only delete the table and re-create it, so you must consider scalability when creating the table. It is estimated that this function will be strengthened in the future.
2. Linux/Unix platform
error: I haven't installed it yet, haha, but it is probably similar to Windows. I will make up this part another day.
2. PHP development of SQLite.
PHP 5 no longer supports Mysql by default, but supports SQLite by default. This shows how influential it is. So if you want to develop PHP for SQLite, it is recommended that you use PHP 5.0.0 or above. I am currently using PHP version 5.0.4, which directly supports SQLite extensions. I will not explain how to install PHP extensions in detail here. If you are not sure, you can check the PHP related documents.
Here I am mainly talking about the development of SQLite. Currently, all mainstream DB classes in PHP support SQLite drivers, including the PEAR::DB class and the ADOdb class, so using DB for development is also a good choice.
the
sake of simplicity,the
following operations are all performed on the Windows
It operates. There are many processing functions for SQLite in PHP. You can check the PHP manual for detailed information.
We use the sqlite_open() function to open a sqlite database. It returns an operation resource successfully, and returns false if it fails. Then all subsequent operations are performed on this resource. To execute a sql query, use the sqlite_query function.
Below I assume that you have a sqlite database file abc.db in the current PHP program directory. We operate this file:
<?php
//Open 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 obtained results
print_r(sqlite_fetch_array($result));
?>
The output we see is:
Array
(
[0] => fnord
[bar] => fnord
)
proves that our code is executed successfully. If there is no input, please check the program or whether your database file exists.
Then with this basic operation, you can consider using more complex operations and SQL to operate it and let it help you manage information. You can make a guestbook or a CMS system. I think there will be no problem.
2. Use PHP to create and operate the database
. If you do not have any tools such as sqlite.exe, you can also create a sqlite database through PHP and manage it.
In fact, the content of the database created through the sqlite.exe program is empty. In fact, the database file only exists after the table is created and data is added. So can we manually add a file, such as an empty test.db file? And operate on it. This is completely possible. Below we will use the PHP program to create a database and perform simple functions of creating data tables, inserting data and retrieving data.
First let’s look at the code: (the code is longer, but easier to understand)
<?php
/**
* File: sqlite.php
* Function: Processing of 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, the file content is empty
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));
}
//Generate 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 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));
}
//Get the retrieved data and display it
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 operate correctly, the program will finally output:
ID: 1
Name: heiyeluren
Our above program includes relatively complete functions, including debugging, exception handling, database access, etc. It is considered a simple application. You can also expand it if you are interested.
* End:
Our basic operations are here. I will complete the content when I have time in the future. If you are interested, you can research it. Maybe your personal homepage needs such a small database to help you.
* Reference documents:
http://www.donews.net/limodou/archive/2004/03/21/7997.aspx
http://www.linuxsir.org/bbs/showthread.php?p=1213668#post1213668
* SQLite resource 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