SQLite database management System-the database engine I know _sqlite

SQLite is a lightweight, design-related database management system for embedded systems. SQLite is a database engine that implements self reliance, pure client, 0 configuration, and supports transactions. it by D. Richard Hipp was first developed and is now the most widely deployed open source database engine in the world.

In this article, we will introduce the following:

Create a SQLite database

code as follows:

Sqliteconnection conn = new Sqliteconnection ("Data source=mytest.s3db");
Conn. Open ();

SQLite Data Insertion

 code as follows:

<summary>
Allows the programmer to easily inserts 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;
}

 code 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:

code as follows:

Try
{
Db. Opentransaction ();
Insert4native ();
Db.commitetransaction ();
}
catch (System.Exception ex)
{
Mlog.error (ex. ToString ());
Db. RollbackTransaction ();
}

Index of SQLite

An index is an attribute used to optimize a query, the data is divided into clustered index and nonclustered index, the former is determined by the data organization in the database, for example, when we insert data into a database, the clustered index can ensure the insertion in order, and the position and structure of the data after inserting is unchanged. Non-clustered indexes are indexes that we manually and explicitly create, which can be indexed for each column in the database, and similar to the index in the dictionary, followed by the principle of indexing the decentralized and combinatorial columns to improve query efficiency in large data and complex queries.

 code 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、 Irrelevant database sizeThe test results for query efficiency are as follows (700,000 data):

 code as follows:

String sql = "Select Leafname from File WHERE Length > 5000";

The test results for query efficiency under Complex queries are as follows (~40,000 data):

 code as follows:

String sql = "Select folder." Location as FilePath "
+ "from folder folder, left JOIN file file". Parentguid=folder. Guid "
+ "WHERE file." Length > 5000000 GROUP by File.leafname ";

SQLite triggers (Trigger)

A trigger is a database operation that is automatically performed after a particular database event (DELETE, INSERT, or UPDATE) occurs, and we can interpret the trigger as an event in a high-level language.

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:

 code as follows:

CREATE TRIGGER update_folder_deleted Update Deleted on Folder
Begin
UPDATE File SET deleted=new. Deleted WHERE Parentguid=old. Guid;
End;

After creating the trigger, execute the following statement:

 code as follows:

UPDATE Folder SET deleted=1 WHERE guid= ' 13051a74-a09c-4b71-ae6d-42d4b1a4a7ae '

The above statement will cause the following statement to automatically execute:

code as follows:

UPDATE File SET deleted=1 WHERE parentguid= ' 13051a74-a09c-4b71-ae6d-42d4b1a4a7ae '

views of SQLite (view)

A view can be a virtual table in which you can store a collection of data filtered by a certain condition, so that the next time we want to get those particular data sets, we don't have to go through a complex query to get the data you want.

In the next example, we create a simple view:

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

SQLite Command line tools

The SQLite library contains a SQLite3.exe command-line tool that enables SQLite basic operations. Here's how to use it to analyze our query results:

1. Cmd->sqlite3.exe mysqlitedbwithoutindex.s3db

2. Open the Explain function and analyze the specified query results

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

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

5. Note that each statement should be appended with a semicolon ";"

SQLite Some common usage restrictions

1. SQLite does not support the case comparison of Unicode characters, see the following test results:

2. How to handle SQLite escape characters:

code as follows:

INSERT into XYZ VALUES (' 5 O ' clock ');

3. Limit the number of bars in a composite SELECT statement:
A compound query statement means that multiple SELECT statements are joined by Union, union all, EXCEPT, or INTERSECT. The SQLite process's code generator uses recursive algorithms to combine SELECT statements. To reduce the size of the stack, SQLite designers limit the number of entries for a composite SELECT statement. The default value for Sqlite_max_compound_select is 500. This value is not strictly limited, in practice, it is almost impossible to see a compound query statement of more than 500 entries.

The reason for compounding queries here is that we can use it to help us quickly insert large amounts of data:

 code 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);
}
}