Basic Introduction to SQLite

Basic Introduction to SQLite
1. What is SQLite? -> SQLite Introduction

SQLite is a lightweight, self-sufficient, serverless, non-configuration, and transactional SQL database engine. like other databases, the SQLite engine is not an independent process and can be statically or dynamically connected according to application requirements. SQLite can directly access its stored files.

SQLite is an open-source embedded relational database. It is highly portable, easy to use, compact, efficient, and reliable. Unlike other database management systems, SQLite is easy to install and run. In most cases, you can create, connect to, and use a database by ensuring that the binary file of SQLite exists. If you are looking for an embedded database project or solution, SQLite is definitely worth considering.

2. Why SQLite:

The main reasons are as follows:

1: a single server is not required or an operating system (because it does not need a server );

2: No configuration is required (no installation or management is required );

3: A complete SQLite database is stored in a single cross-platform disk file.

4: SQLite is lightweight and very small (the configuration is smaller than kb and the optional function configuration is smaller than KB );

5: No external dependency (self-sufficiency );

6: Transaction compatibility, allowing access from multiple threads or thread security perspectives

7: The SQLite query language supports the SQL92 (SQL2) function (as long as you know the commonly used database query statements, SQLite will produce the message statement and it will be OK ---> however, some features are not supported. but it is enough for use );

8: Provides simple and easy-to-use APIs.

9: cross-platform (UNIX, MAC Android, IOS, Window, WinCE) running.

3. SQLite installation:

If the operating system is Windows, you can download SQLite from this address.

SetSqlite-shell-win32-*. zipAndSqlite-dll-win32-*. zipDownload the compressed file.

Decompress the preceding two compressed files to obtain the sqlite3.def, sqlite3.dll, and sqlite3.exe files.

1: here we can open the SQLite execution file through the Window command line.

Enter CMD in the command line and run it as an administrator.

:

2: You can also directly open the SQLite3.exe file.

:

 

You can use. help to get help instructions.

4. add, delete, modify, and query SQLite:

First, create a database:

Create a table as follows:

The following are some SQLite websites:

Http://www.sqlite.org/SQLite official website.

Http://www.php.net/manual/en/book.sqlite3.php SQLite provides support for PHP websites.

Https://bitbucket.org/xerial/sqlite-jdbc is a library for accessing and creating SQLite database files in Java

Https://docs.python.org/2/library/sqlite3.html sqlite3 python module, which provides SQL interfaces compatible with DB-API 2.0 specifications.

Http://search.cpan.org /~ The msergeant/DBD-SQLite-0.31/SQLite Perl driver is used with the Perl DBI module.

Http://search.cpan.org /~ The timb/DBI/Perl DBI module provides common interfaces for any database, including SQLite.

Introduction to SQLite and Chinese

SQLite Chinese community-www.sqlite.com.cn/
Let's take a look at this. The actual English is also very simple. You can search for some interfaces encapsulated by others in Google source code.

How to Use SQLite

Here are some of Kevin's experiences on his use. The original Article roughly means that the Android example covers some basic usage of Sqlite, however, they did not provide a reasonable method of use in depth, and more importantly, they did not provide a reasonable method of use. Most examples and documents only involve the most basic database queries, or teach you how to create a ContentProvider. What is never mentioned here is: · where can I create and save a SQLiteOpenHelper instance? · How many instances can be created? · Is there anything to worry about accessing the database with multiple threads? The basic content is that you can connect to the Sqlite database any number of times, and the Android system also supports you. Sqlite has a file-Level Lock for Synchronous access and error prevention. If you only know this, it will bring you great pain. One advantage of open source is that you can go deep into the code. From code and some tests, I learned the fact that Sqlite has a file-Level Lock. Many threads can read data at the same time, but only one thread can write data. The lock prevents multiple concurrent writes. · Android implements some java locks in SQLiteDatabase to ensure that the action is synchronized. · If you use multiple threads to access the database, your database will not (or should not) Crash. It is not mentioned that if you write a database through multiple different real connections, one of them will fail, and it will not continue writing after the previous one is completed. In a simple way, it won't write your changes. Worse, you won't get an exception, but it just outputs some messages in LogCat. That's all. SQLiteOpenHelper class has done some interesting things. Although it can obtain a read-only connection and a read-write connection, they are essentially the same connection. If there is no file write error, the read-only connection is essentially a read/write connection. Interesting. Therefore, if your app uses a helper, you will never use multiple connections Even if you use it in multiple threads. Similarly, there is only one SQLiteDatabase instance in a helper instance, and some java locks are implemented in this instance. Therefore, when you are performing database operations, other db operations will be locked. Even if you use multiple threads to do these tasks to optimize database performance, bad messages are useless. According to my understanding, the way SQLite works is basically impossible to destroy your database, unless there are bugs in the code or hardware problems. Therefore, we recommend that you create a static SQLiteOpenHelper object. When should I close it? No. When the app is closed, it automatically releases the file reference. However, will there be an exception in "close () was never explicitly called on database? If you note that when the connection is hung there, you do not get the exception. You only encounter exceptions when the connection has been established and you try to open another one. Therefore, you only need to open the connection once. Use this method as follows: public class DatabaseHelper extends OrmLiteSqliteOpenHelper {private static DatabaseHelper instance; public static synchronized DatabaseHelper getHelper (Context context) {if (instance = null) instance = new DatabaseHelper (context); return instance;} // Other stuff ...} that's all...