SQLite

Example c prog

• gcc -o simplesqlite3 simplesqlite3.c -Wall -W -O2 -Wl,-R/usr/local/lib -lsqlite3
  • could only get source to compile with given compiler flags on linux.

command line usage

• sqlite3 mydata.db
  • or use SQL command attach interactively
• -line = execute one-off SQL statement
  • sqlite3 -line mydata.db ’select * from memos where priority > 20;’
• -html = Query results will be output as simple HTML tables.
• -init file = Read and execute commands from file , which can contain a mix of SQL statements and meta-commands.

interactive usage

• .quit = .exit = GTFO
• .database = show list of databases
• .table = show list of tables
• .dump = show a dump of the whole db
• .schema <table_name> = show the table create statement

Trigger Behavior

     -- ********************************************************************
     --   Creating a trigger for timeEnter
     --     Run as follows:
     --            $ sqlite3 test.db < trigger1
     -- ********************************************************************
     CREATE TRIGGER functionname AFTER  INSERT ON table_name
     BEGIN
      UPDATE table_name SET certain_row = DATETIME('NOW')  WHERE rowid = new.rowid;
     END;
     -- ********************************************************************

C++ API Calls

• sqlite_open_v2

 int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module (OS Interface Object) to use */
);

• prepare statement - To execute an SQL query, it must first be compiled into a byte-code program
  • only use the _v2 call; the non v2 is retained for backward compatibility.

int sqlite3_prepare_v2(
  sqlite3 *db,      /* Database handle */
  const char *zSql, /* SQL statement, UTF-8 encoded */
  int nByte,        /* Maximum length of zSql in bytes. Just use -1 */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle - what gets passed to sqlite3_step()*/
  const char **pzTail     /* OUT: Pointer to unused portion of zSql, Just use NULL*/
);

• sqlite_busy_handler

 int sqlite3_busy_handler(
  sqlite3*,           // Pointer to db connect
  int(*)(void*,int),  // Function pointer to callback function
  void*               // Some kind of string
);

• • This routine sets a callback function that might be invoked whenever an attempt is made to open a database table that another thread or process has locked.
  • This function is good for defining what happens when a busy connection is returned, like outputting some busy message. If you don't care, and all you want to do is just have the connection wait, better to use sqlite3_busy_timeout(..)
• sqlite3_mprintf - enhanced sprintf function with added SQL formatting options.
• sqlite_exec

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

Sequence of Events

1. The first argument, "db", is a database connection obtained from a prior successful call to sqlite3_open(), sqlite3_open_v2(), etc.
2. Tell SQLITE what to do if a connection is busy using sqlite_busy_handler(), or more simply sqlite_busy_timeout()
3. Create the object using sqlite3_prepare_v2() or a related function. Here you set up the syntax of the SQL statement, and put tokens into the string that will substitute or "bind" data into that string
4. Bind values to host parameters using the sqlite3_bind_*() interfaces.
5. Run the SQL by calling sqlite3_step() one or more times.
   • Here's where the result codes are returned, like SQLITE_BUSY, SQLITE_DONE
6. Use the column access functions to the values returned by statement executed.
7. Reset the statement using sqlite3_reset() then go back to before the bind step. Do this zero or more times.
8. Destroy the object using sqlite3_finalize().

File Locking and Concurrency

• Handled by the "pager" module, which handles multiple threads, processes, or both.
• Wrap the SQL statements with BEGIN TRANSACTION; ... END TRANSACTION;