SQLite (Notes)

From Minor Miracle Software
Revision as of 13:45, 8 January 2020 by WikiSysop (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

SQLite (Notes)

  • Functions

SQLite has no function property as in SQL Server or Oracle.

  • Query results.

The sqlite3_get_table function returns 0 even if no records were selected. A query can end successfully but still produce nothing. If rows and columns are both zero then nothing was returned.

  • Carriage Return/Line Feed in a statement

Use || CHAR(13 ) || CHAR(10 ) ||.

  • TRUNCATE TABLE
The statement DELETE TABLE ; is used which does delete all data but does not reset the PK. SQLite v3.30.1 Uses PostgreSQL syntax SQLite and multi-threaded read/writes. "What would PostgreSQL do?" when things get tricky. Does not enforce type checking. ALTER TABLE can't delete or modify columns. INTEGER PRIMARY KEY No BOOLEAN datatype. Use 0 or 1. No DATETIME datatype. Use INTEGER to store seconds since 1970. Foreign Key Enforcement Is Off By Default. Turn it on. PRIMARY KEYs Can Sometimes Contain NULLs. INTEGER PRIMARY KEYS do not. Double-quoted String Literals Are Accepted. AUTOINCREMENT Does Not Work The Same As MySQL. FAQ[1] Good example to use SQLite[2].
  • SQLiteStudio[3] decent gui.
Each database is a separate file. Makes saving session settings easy.
  • Need to wrap it in a c++ class.

Compiling SQLite[4]

Foreign Keys[5] are disabled by default. Thread safe time features are also off by default. Enabling these requires a recompile and using the "PRAGMA foreign_keys = ON" statement when opening a session with the database.
1 Download[6] the Windows sqlite-dll-win32-x86-<version>.zip and sqlite-amalgamation-<version>.zip.
2 Extract the Amalgamation files into a working folder.
3 Create the following config.h file and put it in the folder.

// Foreign Keys
#undef SQLITE_OMIT_FOREIGN_KEY
#undef SQLITE_OMIT_TRIGGER
#define SQLITE_DEFAULT_FOREIGN_KEYS 1

// Thread Safe 
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1

4 Open a Visual Studio Developer Command Prompt and move to the folder.
4.1 There are two Developer Command Prompts, one for x86 and one for x64. Pick the one for the desired architecture. 5 Run this command:

cl sqlite3.c -D_HAVE_SQLITE_CONFIG_H -DSQLITE_API=__declspec(dllexport) -link -dll -out:sqlite3.dll

Github[7] has a more detailed description.
6 Copy the sqlite3.dll, sqlite3.h, and sqlite3.lib to the target folder.

Internal Links

Parent Article:Databases