SQLite Database Management System-database engine I know

SQLite is a lightweight associated database management system designed for embedded systems. SQLite is a database engine that implements self-dependency, pure client, zero configuration, and supports transactions. It was first developed by D. Richard Hipp and is currently the world's most widely deployed open source database engine.

In this article, we will introduce the following content:

Create an SQLite Database

Copy codeThe Code is as follows: SQLiteConnection conn = new SQLiteConnection ("Data Source = mytest. s3db ");
Conn. Open ();

SQLite data insertion
Copy codeThe Code is as follows: // <summary>
/// Allows the programmer to easily insert into the DB
/// </Summary>
/// <Param name = "tableName"> The table into which we insert the data. </param>
/// <Param name = "data"> A dictionary containing the column names and data for the insert. </param>
/// <Returns> A boolean true or false to signify success or failure. </returns>
Public bool Insert (string tableName, Dictionary <string, string> data)
{
Boolean returnCode = true;
StringBuilder columnBuilder = new StringBuilder ();
StringBuilder valueBuilder = new StringBuilder ();
Foreach (KeyValuePair <string, string> val in data)
{
ColumnBuilder. AppendFormat ("{0},", val. Key );
ValueBuilder. AppendFormat ("'{0}',", val. Value );
}
ColumnBuilder. Remove (columnBuilder. Length-1, 1 );
ValueBuilder. Remove (valueBuilder. Length-1, 1 );
Try
{
This. ExecuteNonQuery (string. Format ("insert into {0} ({1}) VALUES ({2 });",
TableName, columnBuilder, valueBuilder ));
}
Catch (Exception ex)
{
MLog. Warn (ex. ToString ());
ReturnCode = false;
}
Return returnCode;
}

Copy codeThe Code is as follows: DateTime entryTime;
String name = string. Empty, title = string. Empty;
GetSampleData (out name, out title, out entryTime );
Int id = random. Next ();
InsertParameterDic. Add ("Id", id. ToString ());
InsertParameterDic. Add ("Name", name );
InsertParameterDic. Add ("Title", title );
InsertParameterDic. Add ("EntryTime ",
EntryTime. ToString ("yyyy-MM-dd HH: mm: ss "));

Db. Insert ("Person", insertParameterDic );

SQLite Transaction Processing Method

Begin Transaction:

Commit Transaction:

Rollback Transaction:

Copy codeThe Code is as follows: try
{
Db. OpenTransaction ();
Insert4Native ();
Db. CommiteTransaction ();
}
Catch (System. Exception ex)
{
MLog. Error (ex. ToString ());
Db. RollbackTransaction ();
}

SQLite Index

Indexes are a feature used to optimize queries. Data is divided into clustered indexes and non-clustered indexes. The former is determined by the data organization in the database, for example, when we insert a piece of data into the database, the clustered index can ensure that the data is inserted in order, and the position and structure of the inserted data remain unchanged. Non-clustered indexes are manually and explicitly created indexes that can create indexes for each column in the database. They are similar to the indexes in the dictionary, the principle is to create an index for columns with scattered and Composite Columns to improve query efficiency in the case of big data and complex queries.

Copy codeThe Code is as follows: // <summary>
/// Create index
/// </Summary>
/// <Param name = "tableName"> table name </param>
/// <Param name = "columnName"> column name </param>
/// <Param name = "indexName"> index name </param>
Public void CreateIndex (string tableName, string columnName, string indexName)
{
String createIndexText = string. Format ("create index {0} ON {1} ({2 });",
IndexName, tableName, columnName );
ExecuteNonQuery (createIndexText );
}

Simple Query,Unrelated database sizeThe test results for query efficiency are as follows (700,000 data records ):Copy codeThe Code is as follows: string SQL = "SELECT LeafName FROM File WHERE Length> 5000 ";

Complex QueryThe query efficiency test results are as follows (~ 40,000 data records ):

Copy codeThe Code is AS follows: string SQL = "SELECT folder. Location AS FilePath"
+ "FROM Folder folder left join File file ON file. ParentGuid = folder. Guid"
+ "WHERE file. Length> 5000000 group by File. LeafName ";

SQLite Trigger)

A trigger is a database operation that is automatically executed after a specific database Event (DELETE, INSERT, or UPDATE) occurs. We can understand the trigger as an Event in advanced languages ).

Suppose I have two tables.:

Folder (Guid VCHAR (255) not null, Deleted boolean default 0)

File (ParentGuid VCHAR (255) not null, Deleted boolean default 0)

Create a trigger Update_Folder_Deleted in the Folder table:

Copy codeThe Code is as follows: create trigger Update_Folder_Deleted UPDATE Deleted ON Folder
Begin
UPDATE File SET Deleted = new. Deleted WHERE ParentGuid = old. Guid;
END;

After the trigger is created, execute the following statement:Copy codeThe Code is as follows: UPDATE Folder SET Deleted = 1 WHERE Guid = '13051a74-a09c-4b71-ae6d-42d4b1a4a7ae'

The preceding statement will automatically execute the following statement:Copy codeThe Code is as follows: UPDATE File SET Deleted = 1 WHERE ParentGuid = '13051a74-a09c-4b71-ae6d-42d4b1a4a7ae'

SQLite View)

A view can be a virtual table that stores data sets filtered out according to certain conditions, so that we don't need to use complex queries to obtain these specific data sets next time, you can simply query a specified view to obtain the desired data.

In the next example, we will create a simple view:

Based on the preceding query results, we create a view:

SQLite command line tool

The SQLite library contains a sqlite3.exe command line tool, which can implement basic SQLite operations. Here we will only describe how to use it to analyze our query results:

1. CMD-> sqlite3.exe MySQLiteDbWithoutIndex. s3db

2. Enable the EXPLAIN function and analyze the specified query results

3. re-use the command line to open an indexed database and execute the first two steps

4. By comparing the analysis results of two different query statements, we can find that if an index is used during the query, SQLite will prompt us in the detail column.

5. Note that each statement must be followed by a plus sign (;).

Some common restrictions of SQLite

1. SQLite does not support case-sensitivity comparison of Unicode characters. Please refer to the following test results:

2. How to Handle SQLite escape characters:

Copy codeThe Code is as follows: insert into xyz VALUES ('5 o' 'clock ');

3. limit on the number of compound SELECT statements:
A compound query statement means that multiple SELECT statements are connected by UNION, union all, except t, or INTERSECT. The code generator of the SQLite process uses a recursive algorithm to combine SELECT statements. To reduce the size of the stack, SQLite designers limit the number of entries in a composite SELECT statement. The default value of SQLITE_MAX_COMPOUND_SELECT is 500. There is no strict limit on this value. In practice, it is almost difficult to see a composite query statement with more than 500 entries.

The reason for the compound query is that we can use it to help us insert a large amount of data quickly:

Copy codeThe Code is as follows: public void Insert4SelectUnion ()
{
Bool newQuery = true;
StringBuilder query = new StringBuilder (4 * ROWS4ACTION );
For (int I = 0; I <ROWS4ACTION; I ++)
{
If (newQuery)
{
Query. Append ("insert into Person ");
NewQuery = false;
}
Else
{
Query. Append ("union all ");
}

DateTime entryTime;
String name = string. Empty, title = string. Empty;
GetSampleData (out name, out title, out entryTime );
Int id = random. Next ();
Query. appendFormat ("SELECT '{0}', '{1}', '{2}', '{3}'", id, name, title, entryTime. toString ("yyyy-MM-dd HH: mm: ss "));
If (I % 499 = 0)
{
Db. ExecuteNonQuery (query. ToString ());
Query. Remove (0, query. Length );
NewQuery = true;
}
}

// Executing remaining lines
If (! NewQuery)
{
Db. ExecuteNonQuery (query. ToString ());
Query. Remove (0, query. Length );
}
}