From: 011netservice@gmail.com Date: 2022-04-24 Subject: SQLite-KeyWord.txt ---------- 2020-07-23 SQLite 下載 官方: https://www.sqlite.org/ https://www.sqlite.org/download.html 20200722-www.sqlite.org.download.pdf System.Data.SQLite is an ADO.NET provider for SQLite: http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki --> 盡量用這裡 http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki 20200720-system.data.sqlite.org.pdf Document: SQLite.NET.chm 若不能用, 可改用 sqlite-netFx-full-source-1.0.113.0 內的 維護工具 官方: -a---- 2019/10/11 下午 07:27 504320 sqldiff.exe -a---- 2019/10/11 下午 07:28 953856 sqlite3.exe -a---- 2019/10/11 下午 07:28 2019328 sqlite3_analyzer.exe DB Browser for SQLite: Open source, freeware, 部分中文 https://sqlitebrowser.org/ DB.Browser.for.SQLite-3.11.2-win64.zip ---------- 2020-07-23 有效率的資料庫新增資料方法: Tips on Optimizing Your Queries The next few paragraphs will attempt to give you a few rudimentary rules for speeding up your queries in general, and especially how SQLite is adversely affected by the kinds of SQL behaviors you may have taken for granted in other providers. It is by no means a complete optimization guide. For even more details on optimizing your queries, visit sqlite.org. The Importance of Transactions If you are inserting data in SQLite without first starting a transaction: DO NOT PASS GO! Call BeginTransaction() right now, and finish with Commit()! If you think I'm kidding, think again. SQLite's A.C.I.D. design means that every single time you insert any data outside a transaction, an implicit transaction is constructed, the insert made, and the transaction destructed. EVERY TIME. If you're wondering why in the world your inserts are taking 100x longer than you think they should, look no further. Prepared Statements Lets have a quick look at the following code and evaluate its performance: using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) { int n; for (n = 0; n < 100000; n ++) { mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1); mycommand.ExecuteNonQuery(); } } This code seems pretty tight, but if you think it performs well, you're dead wrong. Here's what's wrong with it: 以上沒效率的地方有4點: 1. I didn't start a transaction first! This insert is dog slow! 沒有先使用一個 transaction. 2. The CLR is calling "new" implicitly 100,000 times because I am formatting a string in the loop for every insert 程式會在迴圈中, 每一次重新解析 SQL 指令的字串. 3. Since SQLite precompiles SQL statements, the engine is constructing and deconstructing 100,000 SQL statements and allocating/deallocating their memory 資料庫會在迴圈中, 每一次重新解析 SQL 指令語法. 4. All this construction and destruction is involving about 300,000 more native to managed interop calls than an optimized insert 迴圈中, 每一次(會自動隱含)重複呼叫 SQLiteTransaction 建構與解構式. So lets rewrite that code slightly: 因此需改成如下: // 只用一個 SQLiteTransaction using (SQLiteTransaction mytransaction = myconnection.BeginTransaction()) { using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) { SQLiteParameter myparam = new SQLiteParameter(); int n; mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)"; mycommand.Parameters.Add(myparam); // 迴圈中單純新增作業, 不要包含 SQLiteCommand, SQLiteParameter. for (n = 0; n < 100000; n ++) { myparam.Value = n + 1; mycommand.ExecuteNonQuery(); } } // 只需 Commit 一個 SQLiteTransaction mytransaction.Commit(); } Now this is a blazing fast insert for any database engine, not just SQLite. The SQL statement is prepared one time -- on the first call to ExecuteNonQuery(). Once prepared, it never needs re-evaluating. Furthermore, we're allocating no memory in the loop and doing a very minimal number of interop transitions. Surround the entire thing with a transaction, and the performance of this insert is so far and away faster than the original that it merits a hands-on-the-hips pirate-like laugh. Every database engine worth its salt utilizes prepared statements. If you're not coding for this, you're not writing optimized SQL, and that's the bottom line. ---------- 2020-07-23 Connection String ref: https://www.connectionstrings.com/sqlite/ Connection string 欄位說明: SQLite-Connection-Fields.pdf 常用: (2020-07-23 測試 OK) Data Source=App_Data\temp1.db;Version=3;Version=3; Data Source=App_Data\temp1.db;Version=3;Version=3;Synchronous=Full; 若檔案存在, 則開啟, 否則自動新增. Data Source=App_Data/DBTemplate.db;Version=3;FailIfMissing=True; Data Source=App_Data/DBTemplate.db;Version=3;FailIfMissing=True;Synchronous=Full; 若檔案存在, 則開啟, 否則拋出例外錯誤. Data Source=c:\mydb.db;Version=3;New=True; 建立新資料庫. Data Source=:memory: 記憶體資料庫, 連線結束就消失. 若需暫存 Table 可用 "CREATE TEMP TABLE" SQL 指令就可以, 不需要用到記憶體資料庫. Data Source=c:\mydb.db;Version=3;Read Only=True; 唯讀資料庫 Data Source=c:\mydb.db;Version=3;Journal Mode=Off; 加速處理, 不要紀錄交易. 結果會跟 in-memory database 一樣容易受損. Data Source=c:\mydb.db;Version=3;Pooling=True;Max Pool Size=100; 預設不會啟動 Connection pooling, 必須指定啟動. 建議不要啟動. 因為 SQLite 是 File-Based 的檔案資料庫. 不適用於多工平行處理的需求. 最好結束時 Data Source=c:\mydb.db;Version=3;Synchronous=Full; 預設為 Full. Normal - Normal file flushing behavior 正常(這是怎樣 ?) Full - Full flushing after all writes 寫入後立即清空 Off - Underlying OS flushes I/O's 由 OS 決定 Full specifies a full flush to take action after each write. Normal is the default value. Off means that the underlying OS flushes I/O's. Basic Data Source=c:\mydb.db;Version=3; Version 2 is not supported by this class library. In-Memory Database 記憶體資料庫, 連線結束就消失. An SQLite database is normally stored on disk but the database can also be stored in memory. Read more about SQLite in-memory databases here. Data Source=:memory: Using UTF16 Data Source=c:\mydb.db;Version=3;UseUTF16Encoding=True; Dim cn As SQLiteConnection = New SQLiteConnection("FullUri=file:") 暫存資料庫檔案, 無法指定檔案名稱與存放位置. ? 不知道存到哪裡 ? 存放位置為 “C:\Users\[user]\AppData\Local\Temp\etilqs_d4Fsx23dc” Temporary File Storage Locations The directory or folder in which temporary files are created is determined by the OS-specific VFS. On unix-like systems, directories are searched in the following order: 1. The directory set by PRAGMA temp_store_directory or by the sqlite3_temp_directory global variable 2. The SQLITE_TMPDIR environment variable 3. The TMPDIR environment variable 4. /var/tmp 5. /usr/tmp 6. /tmp 7. The current working directory (".") The first of the above that is found to exist and have the write and execute bits set is used. The final "." fallback is important for some applications that use SQLite inside of chroot jails that do not have the standard temporary file locations available. On Windows systems, folders are searched in the following order: 1. The folder set by PRAGMA temp_store_directory or by the sqlite3_temp_directory global variable 2. The folder returned by the GetTempPath() system interface. With password 密碼, 聊勝於無. 但是 System.Data.SQLite 不支援! Data Source=c:\mydb.db;Version=3;Password=myPassword; 不支援! Using the pre 3.3x database format Data Source=c:\mydb.db;Version=3;Legacy Format=True; With connection pooling Connection pooling is not enabled by default. Use the following parameters to control the connection pooling mechanism. 預設不會啟動 Connection pooling, 必須指定啟動. Data Source=c:\mydb.db;Version=3;Pooling=True;Max Pool Size=100; Read only connection 唯讀資料庫 Data Source=c:\mydb.db;Version=3;Read Only=True; Using DateTime.Ticks as datetime format Data Source=c:\mydb.db;Version=3;DateTimeFormat=Ticks; The default value is ISO8601 which activates the use of the ISO8601 datetime format Store GUID as text Normally, GUIDs are stored in a binary format. Use this connection string to store GUIDs as text. Data Source=c:\mydb.db;Version=3;BinaryGUID=False; Note that storing GUIDs as text uses more space in the database. Specify cache size Data Source=c:\mydb.db;Version=3;Cache Size=2000; The Cache Size value measured in bytes Specify page size Data Source=c:\mydb.db;Version=3;Page Size=1024; The Page Size value measured in bytes Disable enlistment in distributed transactions Data Source=c:\mydb.db;Version=3;Enlist=N; Disable create database behaviour If the database file doesn't exist, the default behaviour is to create a new file. Use the following parameter to raise an error instead of creating a new database file. 找不到檔案應錯誤, 不要自動新增 Data Source=c:\mydb.db;Version=3;FailIfMissing=True; Limit the size of database 限制資料庫大小. 從 version 3.12.0, 2016-03 起, 預設 1 Page = 4096 bytes. 1 Page 可為 512 到 65536 bytes. The Max Page Count is measured in pages. This parameter limits the maximum number of pages of the database. Data Source=c:\mydb.db;Version=3;Max Page Count=5000; Disable the Journal File This one disables the rollback journal entirely. To speed up writes to disk (but make them as unreliable as with an in-memory database) 加速處理, 不要紀錄交易. 結果會跟 in-memory database 一樣容易受損. Data Source=c:\mydb.db;Version=3;Journal Mode=Off; Persist the Journal File This one blanks and leaves the journal file on disk after a commit. Default behaviour is to delete the Journal File after each commit. Data Source=c:\mydb.db;Version=3;Journal Mode=Persist; Controling file flushing Data Source=c:\mydb.db;Version=3;Synchronous=Full; Full specifies a full flush to take action after each write. Normal is the default value. Off means that the underlying OS flushes I/O's. Standard Data Source=c:\mydb.db;Version=3; The "Version" key can take value "2" for SQLite 2.x (default) or value "3" for SQLite 3.x SQLite Version 2.X Data Source=c:\mydb.db;Version=2; Create a new database 建立新資料庫 Data Source=c:\mydb.db;Version=3;New=True; Using compression Data Source=c:\mydb.db;Version=3;Compress=True; Specifying Cache Size The Cache Size value represents the amount of data pages that are held in memory. Try increase this value for speed improvements but don't forget to keep track of the applications memory usage. Data Source=c:\mydb.db;Version=3;Cache Size=3000; UTF 8 建立 UTF8 內碼的資料庫, 預設就是 UTF8, 可以不用指定. Data Source=c:\mydb.db;Version=3;UTF8Encoding=True; UTF 16 Data Source=c:\mydb.db;Version=3;UTF16Encoding=True; SQLite3 ODBC Driver Standard DRIVER=SQLite3 ODBC Driver;Database=c:\mydb.db;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0; .NET Framework Data Provider for ODBC Use an ODBC driver from .NET Driver={any odbc driver's name};OdbcKey1=someValue;OdbcKey2=someValue; See the respective ODBC driver's connection strings options. The .net OdbcConnection will just pass on the connection string to the specified ODBC driver. Read more here. ---------- 2020-07-23 DB password 這篇完全無效. 由於 system.data.sqlite.org 的版本, 不支援: https://stackoverflow.com/questions/1381264/password-protect-a-sqlite-db-is-it-possible/24349415 Community Support Policies, Effective May 1, 2017, Updated May 22, 2020: Legacy CryptoAPI Codec 1. It is unsupported. 2. It is disabled in all default build configurations. 3. It may not be possible to build binaries with it enabled. SQLite-Password.pdf You can password protect a SQLite3 DB. Before doing any operations, set the password as follows. 還沒使用前, 可先如下設定資料庫密碼: SQLiteConnection conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); conn.SetPassword("password"); conn.Open(); then next time you can access it like 下次連線時, 就可以在 ConnectionString 中, 啟用資料庫密碼 conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;Password=password;"); conn.Open(); This wont allow any GUI editor to view your data. Some editors can decrypt the DB if you provide the password. The algorithm used is RSA. 啟用密碼的資料庫, 不允許檢視資料. 有些工具仍可解碼. 解碼的演算法是 RSA. Later if you wish to change the password, use 變更密碼: conn.ChangePassword("new_password"); To reset or remove password, use 清除密碼: conn.ChangePassword(String.Empty); ---------- 2020-07-20 Library Reference Library: Readme-SQLiteDownload-20200216.pdf SQLite 最難搞的是執行環境所需要的程式庫. 正常情況下, 透過 NuGet 安裝即可. 但是在不同時代時, 會碰到不同的問題. 問題主要來源為: C++ Library, 跟 Visual studio 的版本. ZLib46 reference 的是 System.Data.SQLite v 1.0.111, 原本可透過 NuGet 安裝正常可用, 但是已經不提供 NuGet 安裝方式. v 1.0.112版本起, 只支援 visual studio 2017 以上, 可從 NuGet 安裝, 因此無法從 Visual Studio 2015 專案檔案格式. 在 Visual Studio 2015 透過 NuGet 安裝會觸發錯誤可能如下: Severity Code Description Project File Line Suppression State Error The default XML namespace of the project must be the MSBuild XML namespace. If the project is authored in the MSBuild 2003 format, please add xmlns="http://schemas.microsoft.com/developer/msbuild/2003" to the element. If the project has been authored in the old 1.0 or 1.2 format, please convert it to MSBuild 2003 format. W:\CodeHelper\cs\vs2015\Practice\CRUDSamples\packages\EntityFramework.6.3.0\build\EntityFramework.props 0 必須改用 Visual Studio 2017 以上的版本才能透過 NuGet 自動安裝. 若要從 Visual Studio 2015 中安裝, 或是自行不透過 NuGet 安裝: 可到 http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki 各版本的原文下載說明: SQLiteV101130-Downlod-Notes.pdf 下載必要的程式庫(選擇合適的一個就好) 搭配 Visual 2015 安裝: Bundle 版本 會包含 SQLite.Interop.dll (適用 x86 編譯) (非 Bundle 版本)不包含 SQLite.Interop.dll (適用 AnyCPU 編譯, 參考以下安裝架構, 並且在上層目錄放入對應的32/64位元版本) ("static"字眼的版本)必須搭配 Microsoft Visual C++ Runtime Library (2005, 2008, 2010, 2012, 2013, 2015, 2017) 才能使用. ("setup"字眼的版本) 則包含 Microsoft Visual C++ Runtime Library (2005, 2008, 2010, 2012, 2013, 2015, 2017) 一起安裝. All the "bundle" packages contain the "System.Data.SQLite.dll" mixed-mode assembly. These packages should only be used in cases where the assembly binary must be deployed to the Global Assembly Cache for some reason (e.g. to support some legacy application on customer machines). ("bundle"字眼的版本) 包含 "System.Data.SQLite.dll". 適用於安裝在電腦的(共用區 Global Assembly Cache ) All the "static" packages contain either native or mixed-mode assembly binaries linked statically to the appropriate version of the Visual C++ runtime. Typically, these packages are used in cases where customer machines may not have the necessary version of the Visual C++ runtime installed and it cannot be installed due to limited privileges. ("static"字眼的版本) 包含 native or mixed-mode assembly, 會連結到對應的 C++ Runtime Library. 適用於權限受限制的電腦上安裝執行. 以下為下載的版本, 直接 Reference 就可用: (20200720 以前) Precompiled Binaries for 32-bit Windows (.NET Framework 4.6) sqlite-netFx46-binary-bundle-Win32-2015-1.0.112.0.zip (3.24 MiB) <---- CRUDSQLiteWinForm 使用這個版本, reference 所有的 .dll 即可 sqlite-netFx46-binary-Win32-2015-1.0.112.0.zip (3.45 MiB) sqlite-netFx46-binary-bundle-x64-2015-1.0.112.0.zip (3.30 MiB) sqlite-netFx46-binary-x64-2015-1.0.112.0.zip (3.61 MiB) 以下為下載的版本, 直接 Reference 就可用: 20200720 Setups for 32-bit Windows (.NET Framework 4.6) 內含安裝 Visual C++ 2015 Update 3 runtime for x86 sqlite-netFx46-setup-bundle-x86-2015-1.0.113.0.exe (17.33 MiB), 這是唯一可安裝在 Visual Studio 2015 的版本. 其餘版本不用安裝, 直接 Reference 即可. sqlite-netFx46-setup-x86-2015-1.0.113.0.exe (17.50 MiB) Setups for 64-bit Windows (.NET Framework 4.6) 內含安裝 Visual C++ 2015 Update 3 runtime for x64 sqlite-netFx46-setup-bundle-x64-2015-1.0.113.0.exe (18.18 MiB) sqlite-netFx46-setup-x64-2015-1.0.113.0.exe (18.42 MiB) Precompiled Binaries for 32-bit Windows (.NET Framework 4.6) 需搭配 Visual C++ 2015 Update 3 runtime for x86 sqlite-netFx46-binary-bundle-Win32-2015-1.0.113.0.zip (3.26 MiB) ----> 20200720, ZLibSQLite 使用這個版本, Reference System.Data.SQLite.dll 就可以 sqlite-netFx46-binary-Win32-2015-1.0.113.0.zip (3.48 MiB) Precompiled Binaries for 64-bit Windows (.NET Framework 4.6) 需搭配 Visual C++ 2015 Update 3 runtime for x64 sqlite-netFx46-binary-bundle-x64-2015-1.0.113.0.zip (3.31 MiB) sqlite-netFx46-binary-x64-2015-1.0.113.0.zip (3.61 MiB) Precompiled Statically-Linked Binaries for 32-bit Windows (.NET Framework 4.6) ("static"字眼的版本)必須搭配 Microsoft Visual C++ Runtime Library sqlite-netFx46-static-binary-bundle-Win32-2015-1.0.113.0.zip (3.78 MiB) sqlite-netFx46-static-binary-Win32-2015-1.0.113.0.zip (4.00 MiB) Precompiled Statically-Linked Binaries for 64-bit Windows (.NET Framework 4.6) ("static"字眼的版本)必須搭配 Microsoft Visual C++ Runtime Library sqlite-netFx46-static-binary-bundle-x64-2015-1.0.113.0.zip (3.80 MiB) sqlite-netFx46-static-binary-x64-2015-1.0.113.0.zip (4.10 MiB) ---------- 2020-07-20 安裝架構如下: ref: https://blog.yslifes.com/archives/1543 含不含”-bundle“差異只是在編譯時是否有把SQLite.Interop.dll編譯進System.Data.SQLite.dll裡而，所以在使用Any CPU時需要把SQLite.Interop.dll另外放，下載32及64位元的Precompiled Binaries版本，解壓縮檔案，把SQLite.Interop.dll檔案，32元位就放到x86目錄，而64位元則放x64目錄，然後選一個System.Data.SQLite.dll放到根目錄，然後專案新增參考System.Data.SQLite.dll Using Native Library Pre-Loading The final step is to identify the target processor architecture on both the development and customer machines. Generally, x86 binaries will work on an x64 machine running Windows (using WoW64). However, there is a problem when using managed code. If the executable that starts the process consists entirely of managed code, it will run with the native processor architecture of the machine, which will be x64 on an x64 machine. Later on, this will cause assemblies containing any native code compiled for x86 (e.g. the "System.Data.SQLite.dll" mixed-mode assembly, the "SQLite.Interop.dll" native interop assembly, or the "sqlite3.dll" native library) to fail to load, typically resulting in a BadImageFormatException being thrown. If the development and customer machines may have different processor architectures, more than one binary package may be required. For this situation, using the native library pre-loading feature is highly recommended. It is available as of version 1.0.80.0 and enabled by default. In order to take advantage of this feature, the separate managed and interop assemblies must be used with XCOPY deployment (i.e. this feature is not supported by the mixed-mode assembly, nor when the assembly is deployed to the global assembly cache), resulting in an application deployment that looks something like this: 安裝架構如下: \App.exe (optional, managed-only application executable assembly) \App.dll (optional, managed-only application library assembly) \System.Data.SQLite.dll (required, managed-only core assembly) \System.Data.SQLite.Linq.dll (optional, managed-only LINQ assembly) \System.Data.SQLite.EF6.dll (optional, managed-only EF6 assembly) \x86\SQLite.Interop.dll (required, x86 native interop assembly) \x64\SQLite.Interop.dll (required, x64 native interop assembly) The string "" above represents the directory where the application binaries are to be deployed on the target machine. With the native library pre-loading feature enabled and the application deployment shown above, the System.Data.SQLite managed-only assembly will attempt to automatically detect the processor architecture of the current process and pre-load the appropriate native library. Description: The official SQLite database engine for both x86 and x64 along with the ADO.NET provider. This package includes support for LINQ and Entity Framework 6. author: SQLite Development Team Date published: Sunday, October 27, 2019 (10/27/2019) https://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki All downloadable packages on this web page that do not include the word "static" in their file name require the appropriate version (e.g. 2005, 2008, 2010, 2012, 2013, 2015, 2017) of the Microsoft Visual C++ Runtime Library, to be successfully installed on the target machine, prior to making use of the executables contained therein. It should also be noted that the downloadable packages on this web page that include the word "setup" (i.e. the setup packages) already include and will attempt to automatically install the required version of the Microsoft Visual C++ Runtime Library. Setups for 32-bit Windows (.NET Framework 4.6) sqlite-netFx46-setup-bundle-x86-2015-1.0.113.0.exe (17.33 MiB) This setup package features the mixed-mode assembly and will install all the necessary runtime components and dependencies for the x86 version of the System.Data.SQLite 1.0.113.0 (3.32.1) package. The Visual C++ 2015 Update 3 runtime for x86 is included. The .NET Framework 4.6 is required. This is the only setup package that is capable of installing the design-time components for Visual Studio 2015. However, if you do not require the design-time components, please consider downloading the associated "Precompiled Binaries" instead. (sha1: 238ceb40a6630ad1ff5b4716e635958a293dc9c6) sqlite-netFx46-setup-x86-2015-1.0.113.0.exe (17.50 MiB) This setup package will install all the necessary runtime components and dependencies for the x86 version of the System.Data.SQLite 1.0.113.0 (3.32.1) package. The Visual C++ 2015 Update 3 runtime for x86 is included. The .NET Framework 4.6 is required. (sha1: 91fe7db15ace9b94d9763ff85a9a30fc541f89a2) Setups for 64-bit Windows (.NET Framework 4.6) sqlite-netFx46-setup-bundle-x64-2015-1.0.113.0.exe (18.18 MiB) This setup package features the mixed-mode assembly and will install all the necessary runtime components and dependencies for the x64 version of the System.Data.SQLite 1.0.113.0 (3.32.1) package. The Visual C++ 2015 Update 3 runtime for x64 is included. The .NET Framework 4.6 is required. (sha1: 6819c64f480775ee2f112898254379b1964f3dbb) sqlite-netFx46-setup-x64-2015-1.0.113.0.exe (18.42 MiB) This setup package will install all the necessary runtime components and dependencies for the x64 version of the System.Data.SQLite 1.0.113.0 (3.32.1) package. The Visual C++ 2015 Update 3 runtime for x64 is included. The .NET Framework 4.6 is required. (sha1: e166d737361e7e0e579299f2b373d5f2a78472aa) ---------- 2020-05-29 在記憶體中資料庫 In-Memory Databases . An SQLite database is normally stored in a single ordinary disk file. However, in certain circumstances, the database might be stored in memory. The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename ":memory:". In other words, instead of passing the name of a real disk file into one of the sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2() functions, pass in the string ":memory:". For example: 例如以下開啟方式 rc = sqlite3_open(":memory:", &db); When this is done, no disk file is opened. Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases. The special filename ":memory:" can be used anywhere that a database filename is permitted. For example, it can be used as the filename in an ATTACH command: ATTACH DATABASE ':memory:' AS aux1; Note that in order for the special ":memory:" name to apply and to create a pure in-memory database, there must be no additional text in the filename. Thus, a disk-based database can be created in a file by prepending a pathname, like this: "./:memory:". The special ":memory:" filename also works when using URI filenames. For example: rc = sqlite3_open("file::memory:", &db); Or, ATTACH DATABASE 'file::memory:' AS aux1; 執行 rc = sqlite3_open(":memory:", &db); 後, 不會開啟磁碟檔案, 而是在記憶體中建立資料庫. 資料庫一旦關閉, 就不再存在. 每個記憶體資料庫都是獨立的, 因此執行兩次 rc = sqlite3_open(":memory:", &db); 將會建立兩個獨立的記憶體資料庫. 注意 ":memory:" 不可有多餘的字, 例如: "./:memory:" 將會產生實體的檔案, 而不是存放在記憶體中. 相同功能, 也可以改用 URI 的方式開啟記憶體資料庫: rc = sqlite3_open("file::memory:", &db); Or, ATTACH DATABASE 'file::memory:' AS aux1; 在記憶體中資料庫且共享快取 In-memory Databases And Shared Cache In-memory databases are allowed to use shared cache if they are opened using a URI filename. If the unadorned ":memory:" name is used to specify the in-memory database, then that database always has a private cache and is this only visible to the database connection that originally opened it. However, the same in-memory database can be opened by two or more database connections as follows: rc = sqlite3_open("file::memory:?cache=shared", &db); Or, ATTACH DATABASE 'file::memory:?cache=shared' AS aux1; This allows separate database connections to share the same in-memory database. Of course, all database connections sharing the in-memory database need to be in the same process. The database is automatically deleted and memory is reclaimed when the last connection to the database closes. If two or more distinct but shareable in-memory databases are needed in a single process, then the mode=memory query parameter can be used with a URI filename to create a named in-memory database: rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db); Or, ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1; When an in-memory database is named in this way, it will only share its cache with another connection that uses exactly the same name. 如果以 rc = sqlite3_open("file::memory:?cache=shared", &db); Or, ATTACH DATABASE 'file::memory:?cache=shared' AS aux1; 開啟共用記憶體資料庫, 則可以開啟共用的記憶體資料庫. 當然, 還是限制在同一個 Process 中這樣開啟記憶體資料庫, 才能共用. 這樣開啟共用的記憶體資料庫, 會在最後一個連線開啟後, 關閉且清除資料庫記憶體. 如果以 rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db); Or, ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1; 開啟記憶體資料庫, 則可以開啟指定名稱的共用的記憶體資料庫. 在同一個 Process 中, 可以區分不同的記憶體資料庫(如上例中的名稱為 memdb1) Temporary Databases 暫存資料庫 When the name of the database file handed to sqlite3_open() or to ATTACH is an empty string, then a new temporary file is created to hold the database. rc = sqlite3_open("", &db); ATTACH DATABASE '' AS aux2; A different temporary file is created each time, so that just like as with the special ":memory:" string, two database connections to temporary databases each have their own private database. Temporary databases are automatically deleted when the connection that created them closes. Even though a disk file is allocated for each temporary database, in practice the temporary database usually resides in the in-memory pager cache and hence is very little difference between a pure in-memory database created by ":memory:" and a temporary database created by an empty filename. The sole difference is that a ":memory:" database must remain in memory at all times whereas parts of a temporary database might be flushed to disk if database becomes large or if SQLite comes under memory pressure. The previous paragraphs describe the behavior of temporary databases under the default SQLite configuration. An application can use the temp_store pragma and the SQLITE_TEMP_STORE compile-time parameter to force temporary databases to behave as pure in-memory databases, if desired. 如果以 空白字串 如下 rc = sqlite3_open("", &db); 或 ATTACH DATABASE '' AS aux2; 開啟資料庫, 則可以開啟暫存資料庫. 每一次開啟暫存資料庫都會產生一個新的暫存資料庫檔案, 並於關閉連線後, 自動刪除資料庫檔案. 雖然暫存資料庫會存放到檔案中, 但是大部份的情況下, 仍會存放在記憶體中. 因此跟記憶體資料庫其實差異不大. 最大的差別在, 只有當資料庫檔案太大或是記憶體不足時, 暫存資料庫是會把記憶體中的資料寫到檔案中. 透過 SQLITE_TEMP_STORE 指示詞, 可將 暫存資料庫 工作模式 設定為跟記憶體資料庫一模一樣. 結論: 1. 若不需要存放檔案的小量暫存資料, 則開啟為 記憶體資料庫. 2. 若資料庫檔案較大時, 則開啟為 暫存資料庫. 注意檔案存取權限與安全. ---------- 2020-03-01 AUTO INCREMENT AUTOINCREMENT clear: delete from sqlite_sequence where name = 'your_table_name' reset to new no.: update sqlite_sequence set seq = [起始值為0] where name = 'your_table_name' ; 注意: 1. 新號碼應為 table 欄位中的最大值. 2. 若沒有任何 table 存在 AUTOINCREMENT 且有資料時, 則不會有 sqlite_sequence 這個 table, 也不會有對應 'your_table_name' 的紀錄. ---------- 2020-02-16 string functions: SELECT col1 |||| '-' || substr('00'||col2, -2, 2) || '-' || substr('0000'||col3, -4, 4) New Line: SQL has no backslash escapes. You can generate a newline in a string by writing it directly in the query: SELECT [Field1] || ' ' || [Field2] FROM MyTable or use a blob literal: SELECT [Field1] || x'0a' || [Field2] FROM MyTable or use the char function: SELECT [Field1] || char(10) || [Field2] FROM MyTable ---------- 2020-02-16 vacuum ref: https://www.itread01.com/content/1546587928.html 1、sqlite刪除資料或者表後，檔案大小不變 原因分析： sqlite採用的是變長紀錄儲存，當你從Sqlite刪除資料後，未使用的磁碟空間被新增到一個內在的”空閒列表”中用於儲存你下次插入的資料，用於提高效率，磁碟空間並沒有丟失，但也不向作業系統返回磁碟空間，這就導致刪除資料乃至清空整個資料庫後，資料檔案大小還是沒有任何變化，還是很大 解決辦法： sqlite3中執行vacuum命令即可。 # ls -shal sqldata.s3db 83M -rw-r--r-- 1 kt kt 83M 4月 1 16:11 sqldata.s3db //從Sqlite刪除資料 # sqlite3 sqldata.s3db SQLite version 3.8.2 2013-12-06 14:53:30 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> delete from records; sqlite> .quit //資料庫大小沒有發生變化 # ls -shal sqldata.s3db 83M -rw-r--r-- 1 kt kt 83M 4月 5 14:40 sqldata.s3db # sqlite3 sqldata.s3db sqlite> vacuum; sqlite> .quit //資料庫大小發生變化 # ls -shal sqldata.s3db 28K -rw-r--r-- 1 kt kt 25K 4月 5 14:48 sqldata.s3db ref: http://blog.csdn.net/liukang325/article/details/23422335 ---------- 2020-02-16 Transaction Make your SQLite bulk inserts very fast in C# .NET SQLite 一秒最快能完成 50,000 筆以上的 INSERT，但一秒只能完成幾十筆 Transaction ref: https://blog.darkthread.net/blog/sqlite-insert-slow/ https://www.jokecamp.com/blog/make-your-sqlite-bulk-inserts-very-fast-in-c/ using (var cnSqlite = new SQLiteConnection(csSqlite)) { cnSqlite.Open(); Stopwatch sw = new Stopwatch(); sw.Start(); var totalCount = list.Count; var index = 0; foreach (var voc in list) { Console.WriteLine($"{index++}/{totalCount}({index * 100.0 / totalCount:n1}%) {voc.Word}"); cnSqlite.Execute("INSERT INTO Dictionary VALUES(@Word, @KKSymbol, @Explanation)", (object)voc); } sw.Stop(); Console.Write($"Duration={sw.ElapsedMilliseconds:n0}ms"); } 事實上我沒耐心等到 sw.Stop() 觀察總耗時，花了 30 分鐘只 INSERT 完 10% 我就放棄了。換句話說，全部跑完要 5 個小時啊啊啊啊啊～ 這個年代這種速度？我有正在操作古董火砲對抗航母戰鬥群的無力感… 爬文找到文章（Make your SQLite bulk inserts very fast in C# .NET），才知這是 SQLite 的 FAQ。 SQLite 一秒最快能完成 50,000 筆以上的 INSERT，但一秒只能完成幾十筆 Transaction，依原本寫法，由於每筆 INSERT 動作預設自成一個 Transaction，速度要快也難。 解決方法很簡單，只需加個兩行，將整個迴圈包成一個 Transaction 就搞定。猜看看速度改進多少？ using (var cnSqlite = new SQLiteConnection(csSqlite)) { cnSqlite.Open(); Stopwatch sw = new Stopwatch(); sw.Start(); using (SQLiteTransaction tran = cnSqlite.BeginTransaction()) { var totalCount = list.Count; var index = 0; foreach (var voc in list) { Console.WriteLine($"{index++}/{totalCount}({index * 100.0 / totalCount:n1}%) {voc.Word}"); cnSqlite.Execute("INSERT INTO Dictionary VALUES(@Word, @KKSymbol, @Explanation)", (object)voc); } tran.Commit(); } sw.Stop(); Console.Write($"Duration={sw.ElapsedMilliseconds:n0}ms"); } 實測結果，132,319 筆 14.887 秒塞完，平均 8,888 筆/秒（這數字巧合也太神奇惹）！跟原本 7 筆/秒相比，速度提升 1200 倍！ ---------- 2020-02-16 DateTime 摘要: select datetime('now'), datetime('now', 'localtime'), datetime('2013-10-07 08:23:19.120'); 2020-02-16 14:26:15 2020-02-16 22:26:15 2013-10-07 08:23:19 select strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime'), strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime') 2020-02-16 22:32:54 2020-02-16 22:32:54.415 date(...) = strftime('%Y-%m-%d', ...) time(...) = strftime('%H:%M:%s', ...) datetime(...) = strftime('%Y-%m-%d %H:%M:%s', ...) julianday(...) = strftime('%J', ...) 很明顯的，strftime()需要輸入較多的參數，因此在使用上比較不方便。 但要將timestring轉為unix timestamp只能靠strftime('%s', ...) 注意:這樣是 utc time CREATE TABLE IF NOT EXISTS "TConfig" ( "FID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "FParentID" INTEGER NULL, "FSeqNo" INTEGER NULL, "FKey" TEXT NOT NULL DEFAULT "", "FValue" TEXT NULL, "FValueB" TEXT NULL, "FReadonly" INTEGER NULL, "FNote" TEXT NULL, "FCreateTime" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, <---- 這樣是 utc time "FUpdateTime" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP <---- 這樣是 utc time ); 下面這樣才會是 local time: CREATE TABLE IF NOT EXISTS "TConfig" ( "FID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "FParentID" INTEGER NULL, "FSeqNo" INTEGER NULL, "FKey" TEXT NOT NULL DEFAULT "", "FValue" TEXT NULL, "FValueB" TEXT NULL, "FReadonly" INTEGER NULL, "FNote" TEXT NULL, "FCreateTime" DATETIME NOT NULL DEFAULT (datetime('now', 'localtime')), "FUpdateTime" DATETIME NOT NULL DEFAULT (datetime('now', 'localtime')) ); 當前的日期： sqlite> SELECT date('now'); 2013-05-07 當前月份的最後一天： sqlite> SELECT date('now','start of month','+1 month','-1 day'); 2013-05-31 計算給定的日期和時間的UNIX時間戳1092941466： sqlite> SELECT datetime(1092941466, 'unixepoch'); 2004-08-19 18:51:06 以下計算UNIX時間戳1092941466抵消本地時區的日期和時間： sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime'); 2004-08-19 11:51:06 以下計算當前的UNIX時間戳： sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime'); 1367926057 以下計算的美國“獨立宣言”簽署以來的天數： sqlite> SELECT julianday('now') - julianday('1776-07-04'); 86504.4775830326 以下一個特彆的時刻在2004年以來的秒數計算： sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56'); 295001572 以下計算日期為當年10月的第一個星期二： sqlite> SELECT date('now','start of year','+9 months','weekday 2'); 2013-10-01 以下計算時間自UNIX紀元秒（類似strftime('%s','now') ，除了包括小數部分）： sqlite> SELECT (julianday('now') - 2440587.5)*86400.0; 1367926077.12598 UTC與本地時間值之間進行轉換，格式化日期時間，使用UTC或localtime修改如下： sqlite> SELECT time('12:00', 'localtime'); 05:00:00 sqlite> SELECT time('12:00', 'utc'); 19:00:00 在SQLite中，date()和time()函數用於檢索當前日期和時間。日期和時間函數使用IS0-8601日期和時間格式的子集。 在SQLite中有6種不同的日期和時間函數返回，並以不同格式進行日期和時間的計算： 編號 函數 描述 ---- --------------- ------------------------------------------------------------------------------ 1 date()函數 SQLite date()函數用於獲取日期並以「YYYY-MM-DD」格式返回。 2 datetime()函數 SQLite datetime()函數用於計算日期/時間值，並以「YYYY-MM-DD HH:MM:SS」格式返回。 3 julianday()函數 SQLite julianday()函數根據儒略日返回日期。是公元前4714年11月24日以後的天數(在格林尼治時間)。它將日期作爲浮點數返回。 4 now()函數 嚴格來說它不是一個函數。只是一個作爲時間字符串參數用於在各種SQLite函數來檢索當前的日期和時間。一些典型用法：date('now'),time('now') 5 strftime()函數 SQLite strftime()函數用於以格式化的方式返回日期，並且還有助於您在日期上進行計算。 6 time()函數 SQLite time()函數用於獲取時間並以「HH-MM-SS」格式返回。 ---- --------------- ------------------------------------------------------------------------------ ref: http://coding-warehouse.logdown.com/posts/7329105-sqlite-date-and-time-functions [SQLITE]日期與時間函數 SQLITE沒有日期型態或時間型態 與其他資料庫軟體不同，SQLITE不支援DATE或DATETIME的資料型態。 雖然在CREATE TABLE tableName (myDate DATE, myTime DATETIME);時，已經定義myDate欄位與myTime欄位分別為日期與時間資料型態，但實際上是以字串在操作的，就算錯誤的日期或錯誤的時間SQLITE也吃得下去。 因此只能依靠內建的日期與時間函數。 五種日期與時間函數 1. date(timestring, modifier, modifier, ...)傳回字串YYYY-MM-DD。 2. time(timestring, modifier, modifier, ...)傳回字串HH:MM:SS。 3. datetime(timestring, modifier, modifier, ...)傳回字串YYYY-MM-DD HH:MM:SS。 4. julianday(timestring, modifier, modifier, ...)傳回從西元前4714年11月24日中午起算的天數(含小數)。也就是儒略日的天數。 5. strftime(format, timestring, modifier, modifier, ...)這是C語言的標準函數。利用format來指定輸出的格式。 參數：timestring timestring可是以下12種字串之一 1. YYYY-MM-DD 2. YYYY-MM-DD HH:MM 3. YYYY-MM-DD HH:MM:SS 4. YYYY-MM-DD HH:MM:SS.SSS 5. YYYY-MM-DDTHH:MM，用T分隔日期與時間 6. YYYY-MM-DDTHH:MM:SS，用T分隔日期與時間 7. YYYY-MM-DDTHH:MM:SS.SSS，用T分隔日期與時間 8. HH:MM，暗指2001-01-01 9. HH:MM:SS，暗指2001-01-01 10. HH:MM:SS.SSS，暗指2001-01-01 11. now，目前的UTC時間 12. DDDDDDDDDD，用數字來表示儒略日或是UNIX Timestamp 另外，第2種至第10種還可以加上[+-]HH:MM的來表示時區，或者接Z來表示Zulu Time(即UTC)。 因此，下列4種表示法均為同一時間 datetime('2013-10-07 08:23:19.120') datetime('2013-10-07T08:23:19.120Z') datetime('2013-10-07 04:23:19.120-04:00') datetime(2456572.84952685) 參數：format format 為 strftime 函數專屬的參數。 format description %d day of month: 00 %f fractional seconds: SS.SSS %H hour: 00-24 %j day of year: 001-366 %J Julian day number %m month: 01-12 %M minute: 00-59 %s seconds since 1970-01-01 i.e. UNIX Timestamp %S seconds: 00-59 %w day of week 0-6 with Sunday==0 %W week of year: 00-53 %Y year: 0000-9999 %% % strftime()可以用來代替其他的日期與時間函數。 date(...) = strftime('%Y-%m-%d', ...) time(...) = strftime('%H:%M:%s', ...) datetime(...) = strftime('%Y-%m-%d %H:%M:%s', ...) julianday(...) = strftime('%J', ...) 很明顯的，strftime()需要輸入較多的參數，因此在使用上比較不方便。 但要將timestring轉為unix timestamp只能靠strftime('%s', ...) 參數：modifier modifier有下列13種 1. NNN days 2. NNN hours 3. NNN minutes 4. NNN.NNNN seconds 5. NNN months 6. NNN years 7. start of month，回到指定日期的月初 8. start of year，回到指定日期的年初 9. start of day，回到指定日期的開始 10. weekday N，前往下一個指定的weekday，Sunday = 0, Monday = 1,... 11. unixepoch，轉為Unix Timestamp 12. localtime，把UTC換算為當地時間 13. utc，把當地時間換算為UTC 其中，unixepoch前面的timestring參數只能是數字型態。例如： date('1970-01-01', 'unixepoch')會傳回Null。 date(0, 'unixeopch')會傳回1970-01-01。 Example Item Function -------------------- ---------------------------------------------------- 今天日期 date('now') 本月的最後一天 date('now', 'start of month', '+1 month', '-1 day') UNIX timestamp strftime('%s', 'now') 美國獨立迄今多少日 julianday('now') - julianday('1774-07-04') 今年10月的第一個週二 date('now', 'start of year', '+9 months', 'weekday 2') -------------------- ---------------------------------------------------- ---------- 2020-02-16 Default Values Default Values for Datatime ref: https://stackoverflow.com/questions/200309/sqlite-database-default-time-value-now 這樣最簡單, 維護方便, 可讀性高, 但占空間: CREATE TABLE test ( id INTEGER PRIMARY KEY AUTOINCREMENT, t TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 若為 REAL: CREATE TABLE test ( id INTEGER PRIMARY KEY AUTOINCREMENT, t REAL DEFAULT (datetime('now', 'localtime')) ); 新增可以這樣 INSERT INTO "test" DEFAULT VALUES; 以下可省空間, 可讀性低, 寫入跟讀取都需要特殊指令, 適合程式運作. Create Table Demo ( idDemo Integer Not Null Primary Key AutoIncrement ,DemoValue Text Not Null Unique ,DatTimIns Integer(4) Not Null Default (strftime('%s', DateTime('Now', 'localtime'))) -- get Now/UTC, convert to local, convert to string/Unix Time, store as Integer(4) ,DatTimUpd Integer(4) Null ); Create Trigger trgDemoUpd After Update On Demo Begin Update Demo Set DatTimUpd = strftime('%s', DateTime('Now', 'localtime')) -- same as DatTimIns Where idDemo = new.idDemo; End; Create View If Not Exists vewDemo As Select -- convert Unix-Times to DateTimes so not every single query needs to do so idDemo ,DemoValue ,DateTime(DatTimIns, 'unixepoch') As DatTimIns -- convert Integer(4) (treating it as Unix-Time) ,DateTime(DatTimUpd, 'unixepoch') As DatTimUpd -- to YYYY-MM-DD HH:MM:SS From Demo; Insert Into Demo (DemoValue) Values ('One'); -- activate the field Default -- WAIT a few seconds -- Insert Into Demo (DemoValue) Values ('Two'); -- same thing but with Insert Into Demo (DemoValue) Values ('Thr'); -- later time values Update Demo Set DemoValue = DemoValue || ' Upd' Where idDemo = 1; -- activate the Update-trigger Select * From Demo; -- display raw audit values idDemo DemoValue DatTimIns DatTimUpd ------ --------- ---------- ---------- 1 One Upd 1560024902 1560024944 2 Two 1560024944 3 Thr 1560024944 Select * From vewDemo; -- display automatic audit values idDemo DemoValue DatTimIns DatTimUpd ------ --------- ------------------- ------------------- 1 One Upd 2019-06-08 20:15:02 2019-06-08 20:15:44 2 Two 2019-06-08 20:15:44 3 Thr 2019-06-08 20:15:44 ---------- 2020-02-16 ref: http://garyliutw.blogspot.com/2013/07/sqlite_22.html 一、Virtual Table Virtual Table 是 SQLite 其中一項功能強大的特色，開發者可透過此機制來擴充 SQLite 的資料來源（Data Source）。 例如，開發者可先以 C 語言，遵循 SQLite 的規格文件，實作一個模組（module），將外部檔案載入進 SQLite （如 CSV 檔或 Web Log 檔） ，成為一個虛擬的 table、然後使用 SQL 對該 table 進行操作。 實作 Virtual Table 模組的步驟： 實作所有必要的 methods，如：xCreat()、xConnect()、xDisconnect()、xDestroy()、xBestIndex()、xOpen()、xClose()、xFilter()、xNext()、xEof()、xRowid()、xColumn()…等等。 創建一個 sqlite3_module 結構的實體（包含所有步驟 1 函式指標 ）。 使用 sqlite3_create_module() 或 sqlite3_create_module_v2() 來註冊上述的 sqlite3_module 結構。 使用以下指令來載入 Virtual Table： CREATE VIRTUAL TABLE tablename USING modulename; CREATE VIRTUAL TABLE tablename USING modulename(arg1, arg2, ...); 自 SQLite 3.6.17 以後，Virtual Table 也可以使用「shared cache mode 」了。 二、In-Memory DB SQLite 通常是將資料庫儲存在一個檔案中。但是在某些狀況下，您可能希望將其儲存在記憶體中。 要讓 SQLite 將 DB 完全存在記憶體中，可在開啟資料庫時，使用特殊檔名「:memory:」來做為檔案名稱。在 shell 進入 In-Memory DB 的方式有以下兩種： ATTACH DATABASE ':memory:' AS aux1; ATTACH DATABASE 'file::memory:' AS aux1; 此時，SQLite 不會在磁碟上開啟任何檔案，而是在記憶體中建立一個新的 DB。而當資料庫連線關閉後，該 DB 就會消失。每一個 :memory: 資料庫都是唯一、各自獨立的。所以，如果用檔案名稱「:memory:」開啟兩個 DB 連線，則會建立兩個新的 in-memory DB（並各自擁有自己的 private cache）。 如果希望在同一個 process、兩個以上的 DB 連線開啟同一個 in-memory DB，則可以使用下列的方式： ATTACH DATABASE 'file::memory:?cache=shared' AS aux1; 同樣地，當最後一個 DB 連線關閉時，此 DB 會被刪除、記憶體會被回收。 如果希望在同一個 process、兩個以上的 DB 連線、各自開啟不同的 in-memory DB ，可以使用下列方式： ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1; ATTACH DATABASE 'file:memdb2?mode=memory&cache=shared' AS aux2; 如果 in-memory DB 這樣命名，那各 DB 連線之間就可以共享其 cache。 In-Memory DB 可使用的 journal mode 只有「MEMORY」和「OFF」兩種。 三、Temporary DB 如果檔案名稱「空字串」，那 SQLite 就會使用一個臨時檔來儲存該 DB： ATTACH DATABASE '' AS aux3; 就像 in-memory DB 一樣，每次使用這個指令，SQLite 就會建立不同的臨時檔案，連到不同臨時 DB 的兩個資料庫連線，各自擁有自己的 DB。當原來建立 DB 的連線關閉後，臨時 DB 就會自動被刪除。 即使 SQLite 會為每個臨時 DB 配置一個檔案，但實務上，臨時 DB 還是會先被儲存在記憶體的分頁 cache 之中，因此與 in-memory DB 稍有不同。差別在於：「in-memory DB」從頭到尾都一直存在記憶體之中，但「臨時 DB」在資料庫變大或記憶體不夠用時就會被寫入到磁碟。 四、其他重要特色 全文索引（Full Text Search），請參考 SQLite 官網文件「SQLite FTS3 and FTS4 Extensions」。 R-Trees，請參考 SQLite 官網文件「R-Trees」。 ---------- 2020-02-16 Column Data Types: ------- -------------------------------------------------------------------------------------- NULL 無值. INTEGER 存放 1, 2, 3, 4, 6 或 8 bytes 的正負號整數. C# CAST 須以 (long)column, 而非(int)v1 轉型. REAL 存放 8 bytes 浮點數. TEXT 存放 字串. 以(UTF-8, UTF-16BE or UTF-16LE)格式存放. BLOB 存放 原始資料. ------- -------------------------------------------------------------------------------------- 注意 SQLite 並非存放靜態的資料型別, 而是以讀取欄位時轉換的型別, 決定存放的值. 例如: INTEGER 欄位, 可存放 1, 2, 3, 4, 6, or 8 bytes 的整數, 完全看讀取欄位時使用的轉換函數, 決定最後的型別. SQLite Storage Classes Each value stored in an SQLite database has one of the following storage classes − Sr.No. Storage Class & Description ------ --------------------------------------------------------------------------------------------------------------------- 1 NULL. The value is a NULL value. 2 INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. 3 REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number. 4 TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE) 5 BLOB. The value is a blob of data, stored exactly as it was input. ------ --------------------------------------------------------------------------------------------------------------------- SQLite storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. SQLite Affinity Type (相似型別) SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities − Sr.No. Affinity & Description 1 TEXT. This column stores all data using storage classes NULL, TEXT or BLOB. 2 NUMERIC. This column may contain values using all five storage classes. 3 INTEGER. Behaves the same as a column with NUMERIC affinity, with an exception in a CAST expression. 4 REAL. Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. 5 NONE. A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another. SQLite Affinity and Type Names Following table lists down various data type names which can be used while creating SQLite3 tables with the corresponding applied affinity. Data Type Affinity ------------------- -------------------------- INT INTEGER INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 ------------------- -------------------------- CHARACTER(20) TEXT VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB ------------------- -------------------------- BLOB NONE no datatype specified ------------------- -------------------------- REAL REAL DOUBLE DOUBLE PRECISION FLOAT ------------------- -------------------------- NUMERIC NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME ------------------- -------------------------- Boolean Datatype SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). 沒有 Boolean 型別, 可用整數 0 或 1 替代. Date and Time Datatype DateTime SQLite does not have a separate storage class for storing dates and/or times, but SQLite is capable of storing dates and times as TEXT, REAL or INTEGER values. 沒有 Dates/Times 型別, 可用以下型別替代: Sr.No. Storage Class & Date Formate ------- ------------------------------------------------------------------ 1 TEXT. A date in a format like "YYYY-MM-DD HH:MM:SS.SSS" (23 CHARS) 建議改為 "YYYYMMDDHHMMSSSSS" (17位數, 若為數字只要 8 Bytes) 或 "YYYYMMDDHHMMSS" (14位數, 若為數字只要 8 Bytes). 若使用數字, 則較難使用 SQL Like 語法的好處, 但是可以再減少存放空間. 2 REAL. The number of days since noon in Greenwich on November 24, 4714 B.C. 3 INTEGER. The number of seconds since 1970-01-01 00:00:00 UTC ------- ------------------------------------------------------------------ You can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions. ---------- 2019-10-22 https://mybeauty.pixnet.net/blog/post/26492636 介紹 資料庫在處理大量的資料上是很必要的，但如果資料固定且量不多，或是資料庫只會在單機上使用，且沒有讓多人使用的需求的話，就不一定需要特別架一個資料庫系統；因此，可以使用類似 SQLite 這樣的嵌入式資料庫（embedded SQL database）。 SQLite 與其他一般資料庫差異不大，一般的 SQL-92 語法都能夠使用，而且不需要建立一個資料庫系統，要使用的時候，只要在編譯程式的時候將 SQLite 程式庫一起編入就可以使用。另外，SQLite 的資料庫（database）都是以單一檔案的形式存於磁碟中，不需要再安裝資料庫伺服器軟體，所以要把資料庫複製或建立在你的電腦上是相單簡單快速。 SQLite 是一個很小的 C 語言程式庫，且本身就完全包含資料庫引擎的功能，而且可以嵌入至其他程式中，完全不用額外的設定。其特性如下： 支援ACID (Atomic, Consistent, Isolated, Durable) transaction。 Zero-configuration：無須管理者的設定及管理。 支援大部分SQL92的語法。 資料庫存在於一個單一的檔案中。 資料庫系統所在機器的位元組順序(Byte order)無關。 支援大小至2 terabytes (2^41 bytes)。 記憶體需求小：小於3萬行的C語言程式碼。小於250KB的程式空間。 大部分的資料庫操作皆快於一般資料庫系統。 簡單易用的API。 支援TCL。也有其他語言的支援可用。 註解詳細的程式碼，以及超過90%的測試。 程式庫自己包含完整的功能，無須其他額外的程式或程式庫。 為public domain，可免費使用。 serverless cross-platform － 使用unicode 不會進行data type檢查 ---------- 2019-10-18 https://sqlitebrowser.org/about/ ---------- 2019-09-12 http://lhzyaminabe.blogspot.com/2018/01/net-coresqlite.html ---------- 2019-06-13 https://www.sqlite.org/index.html ---------- 2018-12-30 https://blog.darkthread.net/blog/dapper-sqlite/ ---------- 2018-03-06 http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki