Precautions for using SQLite as a local cache

Today, I saw an article by Lu Minji of yuanyou <C # SQLite of the local data storage solution>, which describes many advantages of SQLite, especially for local data caching and applications.

From brother Lu's content, I boast SQLite:

SQLite Official Website: http://www.sqlite. org/at first glance saw the characteristics of SQLite.

1. Acid transactions

2. Zero Configuration-no installation or configuration management required

3. A complete database stored in a single disk file

4. database files can be freely shared among machines with different byte sequences.

5. Support database size to 2 TB

6. Small enough, roughly 30 thousand lines of C code, 250 K

7. faster operations than some popular databases in most common databases

8. Simple and Easy APIs

9. Includes TCL binding and supports binding in other languages through wrapper.

10. Source Code with good comments and more than 90% test coverage

11. Independence: no additional dependencies

12. Source is completely open. You can use it for any purpose, including selling it.

13. Support for multiple development languages: C, PHP, Perl, Java, ASP. NET, and Python

I did this application just a while ago. In this article, I will talk about my experience in locally caching business data in SQLite and learn from it. I am in a hurry during development. Please give me more comments in many places.

Solved problems

First, what are the actual problems I solved with SQLite?

Problem 1: The data of a function needs to be connected to a remote database for query at a very slow speed. It is not easy to query the data once. We hope to reuse the previously queried data set.

Question 2: a very large amount of data, such as tens of millions or even hundreds of millions of data records, is read to the able at a time, and memory overflow occurs. Therefore, the first analysis is conducted through reader, after the analysis is completed, the data is not saved in the memory, but the user's second analysis and third analysis are the data to be used for the first analysis. If we query the remote server again, efficiency can be imagined.

In combination with the above two problems, in order to solve the efficiency problem and data reuse degree, reduce the pressure on the database server, I used SQLite to cache data (of course this is not the only and not the best solution ).

Optimize sqlitehelper

I have added several useful methods to brother Lu's sqlitehelper class:

The first method is getschema to get the table structure of a table.

/// <Summary> /// query all data types in the database /// </Summary> /// <returns> </returns> Public datatable getschema () {using (sqliteconnection connection = new sqliteconnection (connectionstring) {connection. open (); datatable DATA = connection. getschema ("tables"); connection. close (); // foreach (datacolumn column in data. columns) // {// console. writeline (column. columnname); //} return data ;}}

The second method is istableexist to determine whether a duplicate table exists in the SQLite database.

/// <Summary> /// determine whether the SQLite database table exists // </Summary> /// <Param name = "dbpath"> path of the SQLite database file to be created </param> Public bool istableexist (string tablename) {using (sqliteconnection connection = new sqliteconnection (connectionstring) {connection. open (); Using (sqlitecommand command = new sqlitecommand (connection) {command. commandtext = "select count (*) from sqlite_master where type = 'table' and name = '" + tablename + "'"; int iaaa = convert. toint32 (command. executescalar (); If (convert. toint32 (command. executescalar () = 0) {return false;} else {return true ;}}}}

The third method is query. Execute the query statement and return dataset.

/// <Summary> /// execute the query statement, return dataset /// </Summary> /// <Param name = "sqlstring"> query statement </param> /// <returns> dataset </returns> Public dataset Query (string sqlstring) {using (sqliteconnection connection = new sqliteconnection (connectionstring) {dataset DS = new dataset (); try {connection. open (); sqlitedataadapter command = new sqlitedataadapter (sqlstring, connection); command. fill (DS, "ds");} catch (system. data. SQLite. sqliteexception ex) {Throw new exception (ex. message) ;}return DS ;}}

Build cache object model and cache Controller

Each cache object generates a table in the database, and the table name is automatically generated by the cache controller. All access to the cache is done by the cache controller, access through the cache item ID and modulekey.

In SQLite, you also need a system table to maintain the correspondence between each cache item and the actual cache storage table. We call it a configuration table, which will be created when the cache controller creates the SQLite cache database file.

The configuration table has the following fields mapped to the cache object model cdlcacheitem class:

Column name	Description
ID	Unique number cached
Modulekey	Cache Module name. A module can contain multiple cached data, which can be distinguished by IDs. In actual applications, data is often cached for a function. Therefore, you can use modulekey to obtain all the cache lists for this function and then select some of them for use.
Comments	Cache description
Tablename	Name of the data table stored in the cache
Adddate	Cache Timestamp

The method for creating a database is as follows:

Static void createdb () {// string SQL = "CREATE TABLE syscdltables (ID integer not null primary key autoincrement unique, modulekey varchar (200), comments varchar (500), tablename varchar (100), adddate datetime) "; sqlitedbhelper. createdb (cachefilepath, SQL );}

Each cache item (Cache object model) is defined as follows, which corresponds to the configuration table:

/// <Summary> /// cache item object /// </Summary> /// <author> tecky Lee </author> /// <date> </date> public class cdlcacheitem {int m_id; public int ID {get {return m_id;} set {m_id = value;} string m_modulekey; Public String modulekey {get {return m_modulekey;} set {m_modulekey = value ;}} string m_comments; Public String comments {get {return m_comments;} set {m_comments = value ;}} string m_tablename; Public String tablename {get {return m_tablename ;} set {m_tablename = value ;}} datetime m_timestamp; Public datetime timestamp {get {return m_timestamp ;}set {m_timestamp = value ;}}}

The following is the Controller Interface Definition:

public interface ICdlCacheController    {        void BeginLoadRow();        void EndLoadRow();        System.Collections.Generic.IList<CdlCacheItem> GetCdlCacheItems(string moduleKey);        CdlCacheItem GetCdlCacheItems(int id);        void LoadRow(System.Data.DataRow row, string tableName);        void LoadRow(IEnumerable<object> row, string tableName);        string LoadTable(System.Data.DataTable dt, string moduleKey, string comments);        System.Data.Common.DbDataReader QueryCdlTableReader(CdlCacheItem item);        System.Data.DataTable QueryCdlTables(CdlCacheItem item);        System.Data.DataTable QueryCdlTables(string sql);        void RemoveAllTables();        void RemoveCdlTables(string moduleKey);        void RemoveCdlTables(System.Collections.Generic.IList<CdlCacheItem> items);        void RemoveCdlTables(CdlCacheItem item);        void RemoveCdlTables(int id);    }

The above functions are described below:

1. beginloadrow, loadrow, and endloadrow. The three function groups can store data in the cache in one row to read data in reader mode when querying the primary database.

2. removealltables and removecdltables are used to delete cache items.

3. getcdlcacheitems. Multiple cache items are obtained through modulekey. For example, if you want to perform a quick analysis based on the data of a function saved in the past few days, you can use this function to obtain the cache list, and the user selects one from the list to continue.

4. querycdltablereader: Obtain the reader object of a cached data. This allows you to perform a row-by-row analysis and read a large amount of data at a time to the datatable. The memory may overflow.

5. querycdltables: Query and load a cache item to the able.

 

Improves cache data writing Efficiency

When SQLite saves data, such as storing 0.1 billion pieces of data at a time, the insertion efficiency is very low, and some people discuss it online.

The main cause of inefficiency is that I/O operations are too frequent. Therefore, when loadtable or beginloadrow-endloadrow is used, transactions are used to reduce the number of data submissions, the storage efficiency of the results is very high. My test result is 4 million data queries, which only takes dozens of seconds. This time can be ignored compared to re-checking the remote server.

The code for beginloadrow and endloadrow is given below (data is submitted only once when endrow is used ):

        SQLiteConnection m_connection;        SQLiteCommand m_command;        DbTransaction m_transaction;        public void BeginLoadRow()        {            m_connection = new SQLiteConnection("Data Source=" + CACHEFILEPATH);            m_connection.Open();            m_transaction = m_connection.BeginTransaction();            m_command = new SQLiteCommand(m_connection);        }        public void EndLoadRow()        {            try            {                if (m_command != null)                    m_command.Dispose();                if (m_transaction != null)                {                    m_transaction.Commit();                }                if (m_connection != null)                {                    m_connection.Close();                    m_connection.Dispose();                }            }            catch (System.Exception ex)            {                LogHandle.Error(ex);            }        }

The loadtable function also calls the beginloadrow-endloadrow mode.

 

How to create database files:

If the SQLite database file does not exist, the database file is automatically created based on the location specified in connetionstring when the SQL statement is executed. The default empty database is only 4 kb.

Other issues to be discussed:

1. I implemented all the caches into one database file. The actual application can cache data in one file according to different services, which is also well managed and easy to maintain, in resource manager, you can copy, delete, and so on.

2. When we store 0.1 billion pieces of data to SQLite, because SQLite does not compress the data, the results database file may have several GB (this is not necessarily the case, and is suitable for the number of database fields, field type ).

If the file is too large, the disk space is consumed. If the user or program is not cleaned up in time, the disk space may be exhausted.

Here, you must establish a mechanism to check the SQLite cache and clean it up in time, or set the upper limit of the cache application. When the upper limit is reached, the historical cache is automatically cleared Based on the timestamp.