Introduction to Open Source Embedded Database SQLite

Introduction to Open Source Embedded Database SQLite

Nils-Erik frantzell, Department of Computer Science, University of California, Santa Cruz

August 22, 2005

Since the emergence of commercial applications decades ago, databases have become a major component of software applications. Just as database management systems are critical, they also become very large and occupy a considerable amount of system resources, increasing management complexity. As software applications gradually modularize modules, a new type of database is more suitable than a large and complex traditional database management system. The embedded database runs directly in the application process and provides the zero-configuration running mode, which consumes a very small amount of resources. This article introduces the popular SQLite database engine and describes how to use it in application development.

SQLite is an open source embedded database engine written by D. Richard hipp in C language. It is completely independent and does not have external dependencies. It is introduced as an option in PHP v4.3 and is built in PHP V5. SQLite supports most sql92 standards, runs on all major operating systems, and supports most computer languages. SQLite is also very robust. The Creator conservatively estimates that SQLite can handle websites with or 00 clicks per day, and sometimes SQLite can handle 10 times the load of the preceding numbers.

Function

SQLite's support for the sql92 standard includes indexing, restrictions, triggering, and viewing. SQLite does not support foreign key restrictions, but supports atomic, consistent, independent, and persistent (acid) Transactions (more information about acid will be provided later ).

Why is embedding? The name of an embedded database comes from its unique operating mode. This type of database is embedded into the application process, eliminating the overhead related to the client server configuration. Embedded databases are actually lightweight and require less memory during runtime. They are written in simplified code, which is faster and more effective for embedded devices. The embedded running mode allows an embedded database to easily manage application data through SQL without relying on original text files. The embedded database also provides a zero-configuration running mode to enable one of them and run a snapshot. You know, SQLite database permissions only depend on the file system, and there is no concept of user accounts. SQLite has database-level locking, no network server, and can implement most sql92 standards (but not all ). Other major functions of the sql92 standard are foreign keys and check restrictions. Learn which sql92 functions are not implemented.

This means that the transaction isAtomicBecause they are either completely executed or not executed at all. Transaction is alsoConsistentBecause the database is never retained in an inconsistent state. Transaction orIndependentTherefore, if two transactions execute operations on the same database at the same time, the two transactions do not interfere with each other. And the transaction isPersistentTherefore, the database can survive the crash and power failure without data loss or damage.

SQLite achieves independent transaction processing through database-level isolation and shared locking. This means that when multiple processes and threads can read data from the same database at the same time, but only one can write data. An exclusive lock must be obtained before a process or thread writes data to the database. After an exclusive lock is triggered, other read or write operations will not occur again.

The SQLite website records the complete SQLite locking semantics.

Back to Top

Internal Structure

Internally, SQLite consists of the following components: SQL Compiler, kernel, backend, and attachment. SQLite makes debugging, modification, and expansion of SQLite kernel more convenient by using virtual machines and virtual database engine (vdbe. All SQL statements are compiled into readable assembly that can be executed in the SQLite virtual machine.

Figure 1. SQLite Internal Structure

SQLite supports databases up to 2 TB in size, and each database is fully stored in a single disk file. These disk files can be moved between computers in different bytes. The data is stored on the disk in the form of B + tree (B + tree) data structure. SQLite obtains database permissions based on the file system.

Data Type

SQLite does not support static data types, but uses column relationships. This means that its data type does not have the table column attribute, but has the data attribute. When a value is inserted into the database, SQLite checks its type. If this type does not match the associated column, SQLite will try to convert the value to the column type. If the conversion fails, the value is stored as its own type.

SQLite supportNULL,INTEGER,REAL,TEXTAndBLOBData type.

Back to Top

Manage SQLite

SQLite comes with a downloadable command-line interface for database administration. You can call this command line program by database name and create a new database and table as follows:

List 1. Create a new database and table

C:/minblogg>sqlite3 c:/minblogg/www/db/alf.db SQLite version 3.2.1 Enter ".help" for instructions sqlite> create table mytable(name varchar(40), age smallint); sqlite> insert into mytable values('Nils-Erik',23); sqlite> select * from mytable; Nils-Erik|23 sqlite>

Then, you can open the database again, list its tables and architectures, and continue to insert and delete values.

List 2. List tables and Architectures

C:/minblogg>sqlite3 c:/minblogg/www/db/alf.db SQLite version 3.2.1 Enter ".help" for instructions sqlite> .tables mytable sqlite> select * from mytable; Nils-Erik|23 sqlite> .schema CREATE TABLE mytable(name varchar(40), age smallint); sqlite>

SQLite also comes with a command line Database analyzer that allows you to display details about the current status of any SQLite database.

Listing 3. SQLite Analyzer

C:/minblogg>sqlite3_analyzer www/db/alf.db Analyzing table mytable... Analyzing table sqlite_master... /** Disk-Space Utilization Report For www/db/alf.db *** As of 2005-Apr-24 18:56:40 Page size in bytes.................... 1024 Pages in the whole file (measured).... 2 Pages in the whole file (calculated).. 2 Pages that store data................. 2 100.0% Pages on the freelist (per header).... 0 0.0% Pages on the freelist (calculated).... 0 0.0% Pages of auto-vacuum overhead......... 0 0.0% Number of tables in the database...... 2 Number of indices..................... 0 Number of named indices............... 0 Automatically generated indices....... 0 Size of the file in bytes............. 2048 Bytes of user payload stored.......... 13 0.63% *** Page counts for all tables with their indices ******************** MYTABLE............................... 1 50.0% SQLITE_MASTER......................... 1 50.0% *** All tables ******************************************************* Percentage of total database.......... 100.0% Number of entries..................... 2 Bytes of storage consumed............. 2048 Bytes of payload...................... 91 4.4% Average payload per entry............. 45.50 Average unused bytes per entry........ 916.50 Maximum payload per entry............. 78 Entries that use overflow............. 0 0.0% Primary pages used.................... 2 Overflow pages used................... 0 Total pages used...................... 2 Unused bytes on primary pages......... 1833 89.5% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 1833 89.5% *** Table MYTABLE **************************************************** Percentage of total database.......... 50.0% Number of entries..................... 1 Bytes of storage consumed............. 1024 Bytes of payload...................... 13 1.3% Average payload per entry............. 13.00 Average unused bytes per entry........ 999.00 Maximum payload per entry............. 13 Entries that use overflow............. 0 0.0% Primary pages used.................... 1 Overflow pages used................... 0 Total pages used...................... 1 Unused bytes on primary pages......... 999 97.6% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 999 97.6%

Because you can use the command line interface to manage databases, it can bring great convenience to the database administrator. Currently, there are many excellent web-based SQLite database management systems. One of them is the PHP-based sqlitemanager.

Figure 2. Use sqlitemanager to manage databases

Back to Top

Backup

There are two methods to back up the SQLite database. If the database is in use, use. DumpCommand. In this way, you can create a file that contains necessary commands and data to recreate the database..dumpCommand can also be used to back up database tables.

Listing 4. Dump command

sqlite> .dump BEGIN TRANSACTION; CREATE TABLE mytable(name varchar(40), age smallint); INSERT INTO "mytable" VALUES('Nils-Erik', 23); COMMIT; sqlite>

If the database is not in use, you can directly copy the database file to a safe location.

Back to Top

Use SQLite in PHP V5

A good practice is to separate the SQLite database from the PHP code. One easy way to do this is to createWWWDirectory. In this directory, createDBDirectory, a script used to store databases and tablesDbscriptsDirectory andBackupsDirectory.

Listing 5. Using PHP V5 to organize SQLite Databases

2004-12-06 15:43 DIR . 2004-12-06 15:43 DIR .. 2005-04-23 19:55 DIR db 2005-01-02 11:46 DIR dbscripts 2005-01-02 11:46 DIR backups

Creating an SQLite database in PHP v5 is very similar to creating a database in the command line interface. If the database does not exist, an empty database is created.

$db = sqlite_open('../db/ac.db');

Creating a table is also very easy:

Listing 6. Creating a table

$db = sqlite_open('../db/ac.db'); sqlite_query($db, 'DROP TABLE post'); sqlite_query($db, 'CREATE TABLE post (id INTEGER PRIMARY KEY, kategori VARCHAR(20) NOT NULL, titel VARCHAR(75) NOT NULL, referens VARCHAR(75), status VARCHAR(20) not null, date varchar(10)not null, synopsis VARCHAR(120), inlaegg varchar(8192))');

Insert a record:

$sqldb = sqlite_open("../db/ac.db"); sqlite_query($sqldb, "INSERT INTO isvs VALUES ('$isvurl' , '$isvname', '$comment')");

And select data:

Listing 7. Selecting data from the SQLite Database

$sqldb = sqlite_open("www/db/ac.db"); $results = sqlite_query($sqldb, "SELECT * FROM isvs order by isvurl asc "); while (list($isvurl, $isvname) = sqlite_fetch_array($results)) { sqlite_close($sqldb);

Back to Top

Using SQLite and database abstraction layer

Two Advanced open-source database abstraction layers support SQLite: pear: DB, which is included in PHP V5 and considered to be more lightweight ezsql. By using PHP extensions and application libraries (PEAR) or ezsql in advance, SQLite can be used for rapid recovery of applications and can be seamlessly switched to more industrial databases as needed.

Listing 8. Using ezsql and SQLite

$users = $db->get_results("SELECT name, age FROM table1"); foreach ( $users as $user ) { echo $user->name; echo $user->age; }

Another game in the city SQLite is not the only open source embedded database engine. If SQLite does not meet your requirements, use Derby (an Apache incubator project) and cloudscape (a commercial IBM Derby version, including IBM support and services ). Cloudscape was born in 1996 at cloudscape. Three years later, Informix software acquired cloudscape. In 2001, IBM acquired the database assets of Informix software, including cloudscape. Last year, IBM presented the code to the Apache Software Foundation as an incubator project like derby. Derby is a 100% Java programming language relational database. It provides stored procedures and triggers, row-level locking, and can execute transaction commit and rollback operations, and supports encryption. Zend recently released Zend core for IBM, which can be seen as a PHP V5-based solution, including PHP extensions for cloudscape and the bound cloudscape database server.

Back to Top

Precautions for using SQLite

Before determining whether to use SQLite in an application, consider the following situations:

• Currently, no network server is available for SQLite. The only way to run SQLite from an application running on another computer is to run it from a shared network. This will cause some problems, such as file locking problems in UNIX and Windows Network Sharing. There is also the performance degradation caused by latency related to access network sharing.
• SQLite only provides database-level locking. Although there are some techniques to increase concurrency, if the application requires a table-level or row-Level Lock, the DBMS can better meet your needs.
• As mentioned above, SQLite can support web sites with a click rate of about or 00 per day-and in some cases, it can process 10 times the traffic. DBMS should be used for websites that have high traffic or need to support a large number of visitors.
• SQLite does not have a user account concept, but determines the permissions of all databases based on the file system. This makes it difficult to enforce the storage quota, and it is impossible to enforce the user license.
• SQLite supports the majority (but not all) sql92 standard. Unsupported features include full trigger support and writable view. See unimplemented sql92 features.

If you feel any restrictions will affect your application, you should consider using a comprehensive DBMS. If you can remove these restrictions and are interested in the fast and flexible embedded open source database engine, you should focus on using SQLite.

Some areas that truly excel at SQLite performance are Web sites that can use SQLite to manage application data, Fast Application Prototype Manufacturing, and training tools.

Back to Top

Conclusion

Because of the low resource usage, good performance, and zero management costs, embedded databases can be used, it will provide efficient performance for applications that previously could not provide backend databases for persistent data. Currently, there is no need to use text files for persistent storage. The ease of use of embedded databases such as SQLite can accelerate application development and enable small applications to fully support complex SQL statements. This is especially important for applications with small device spaces.

Embedded databases are also important for accelerating application development, especially when used at the database abstraction layer (such as pear: DB or ezsql. Finally, SQLite is under development. New functions will be available in the future and will be more useful to open-source communities.

Back to Top

References

• For more information, see the original article on the developerworks global site.

  
  

• Visit SQLite to download the latest version of SQLite, command line interface, documentation, and latest message.
• Download sqlitemanager to facilitate SQLite database management.
• Download PHP V5 including SQLite from php.net.
• Refer to the article "auditing PHP, Part 1: Understanding register_globals" on developerworks to learn about the basic issues that developers need to keep in mind when creating PHP applications.
• "Php Introduction" is a brief introduction to the PHP script language, and discusses the origins, functions, and platforms of PHP.
• "Php example, Part 1" is the first part of the series, which introduces some basic PHP knowledge. This section describes the author page and the front-end Webzine. On the author page, the content provider can enter the body of the article. The front-end is used to introduce the content to readers all over the world.
• "Learning PHP, Part 1" is part 1 of the three-part article series. by recording the workflow system construction process, we will give a comprehensive introduction to PhP, from the most basic PHP scripts of the file system to the use of databases and streams.
• You can obtain the pear: DB database abstraction layer from the PHP extension and application repository.
• Visit jvmultimedia to download the ezsql database abstraction layer.
• Please purchaseSQLiteThis book mainly introduces open source embedded databases.
• Learn more about the Apache incubator project Derby and IBM cloudscape.
• Download the combined PHP V5 binary distribution that includes cloudscape.
• Refer to the open-source area on developerworks to learn more about how-to information, tools, and project updates, help you develop with open-source technologies, and apply these technologies to IBM products.
• Use IBM trial software to improve your next open-source development project, which can be obtained by downloading or from a DVD.
• Join the developerworks community by joining developerworks blogs.

Back to Top

About the author

	Nils-Erik frantzell is a fourth-grade university student at the University of California in Santa Cruz. His interests include databases (especially internal databases), information management, Web technology, and some computer hardware. His hobby is to raise tropical fish and enjoy electronic music.

From: http://www-128.ibm.com/developerworks/cn/opensource/os-sqlite/