SQLite

unlocked -> read

   1.  If the journal file exists
          A.  Open the journal file for exclusive access
                 i) return SQLITE_BUSY
          B.  Get a write lock on the database
                 i) close the journal file
                ii) return SQLITE_PROTOCOL_ERROR
          C.  playback the journal
          D.  close and delete the journal file
          E.  drop the write lock on the database
   2.  Get a read lock on the database file.
          A.  return SQLITE_BUSY
   3.  return SQLITE_OK

read -> unlocked

   1.  Drop the read lock on the database file
   2.  Invalidate all pages in the cache
   3.  return SQLITE_OK

read -> write

   1.  Create the journal file and open for exclusive access
          A.  return SQLITE_BUSY
   2.  Drop the read lock on the database
   3.  Get a write lock on the database 
          A.  Get a read lock on the database
                 i) return SQLITE_PROTOCOL_ERROR
          B.  Delete the journal
          C.  return SQLITE_BUSY
   4.  return SQLITE_OK

write -> read  (commit)

   1.  Sync the journal
   2.  Write all dirty pages
          A.  playback journal
          B.  Reload or invalidate all pages in cache
   3.  Sync the database
   4.  Drop the write lock on the database
   5.  Get a read lock on the database
          A.  return SQLITE_PROTOCOL_ERROR
   6.  Delete the journal
   7.  return SQLITE_OK

write -> read  (rollback)

   1.  Playback the journal
   2.  Drop the write lock on the database
   3.  Get a read lock on the database
          A.  return SQLITE_PROTOCOL_ERROR
   4.  Delete the journal
   5.  Reload or invalidate all pages in cache
   6.  SQLITE_FULL

How to do a B*Tree insert:

add_to_page(cursor, data, ptr){
  if( data_fits_on_page ){ add data to page; return; }
  if( page==root ){
     newpage1 = lowerpart( page+(data+ptr) );
     newpage2 = upperpart( page+(data+ptr) );
     page = newpage1 + center + newpage2;
     return;
  }
  if( move_some_data_left || move_some_data_right ){
    add data to page
    return
  }
  newpage = upperhalf( page+(data+ptr) );
  pop cursor one level
  add_to_page(cursor, center, newpage);
}

The Proposed New SQLite 2.0 Interface design.  (April 16, 2001)

Primary access routines:

  sqlite *sqlite_open(const char *zFilename, int mode);
  int sqlite_compile(sqlite*, const char *zSql);
  int sqlite_row(sqlite*, int *argc, const char ***argv);
  int sqlite_finish(sqlite*);
  int sqlite_close(sqlite*);

Secondary access routines:

  const char sqlite_version[];
  const char sqlite_encoding[];
  int sqlite_complete(const char *);
  sqlite *sqlite_dup(sqlite*);
  int sqlite_abort(sqlite*);
  void sqlite_interrupt(sqlite*);
  char *sqlite_errmsg(sqlite*);
  const char **sqlite_columns(sqlite*);
  int sqlite_argc(sqlite*);
  const char **sqlite_argv(sqlite*);
  char *sqlite_vmprintf(const char *zFormat, va_list);
  int sqlite_table(sqlite*, int *nrow, int *ncolumn, const char ***argv);
  void sqlite_busy_handler(sqlite*, int(*)(void*,const char*,int), void*);

Access routines that are implement derived from primary and sec
  
  char *sqlite_mprintf(const char *zFormat, ...);
  int sqlite_compile_vprintf(sqlite*, const char *zFormat, va_list);
  int sqlite_compile_printf(sqlite*, const char *zFormat, ...);
  int sqlite_busy_timeout(sqlite*, int ms);

The C++ interface is obvious...

Deprecated legacy interfaces:

  int sqlite_exec(
    sqlite*,
    char *sql,
    int (*)(void*,int,char**,char**),
    void*,
    char **errmsg
  );
  int sqlite_exec_printf(...);
  int sqlite_exec_vprintf(...);
  int sqlite_get_table(
    sqlite*,
    char *sql,
    const char ***result,
    int *nrow,
    int *ncolumn,
    char **errmsg
  );
  void sqlite_free_table(char**);
  int sqlite_get_table_printf(...);
  int sqlite_get_table_vprintf(...);

TCL Interface

  sqlite DB FILENAME ?MODE?
  DB compile SQL
  DB row VAR
  DB argc
  DB argv ?N?
  DB table ?VAR?
  DB columns ?N?
  DB finish
  DB abort
  DB eval SQL ?VAR SCRIPT?
  DB close
  DB complete
  DB timeout MS
  DB busy SCRIPT
  DB dup NEWDB

Primary access pattern:

  sqlite *db = sqlite_open("testdb", 0644);
  sqlite_compile(db, "SELECT * FROM sqlite_master");
  while( sqlite_row(db, &argc, &argv)==SQLITE_OK ){
    /* Do something with the row data in argc, argv */
  }
  sqlite_finish(db);
  sqlite_close(db);

Alternative access pattern 1:

  sqlite *db = sqlite_open("testdb", 0644);
  sqlite_compile(db, "SELECT * FROM sqlite_master");
  sqlite_table(db, &nrow, &ncolumn, &argv);
  /* Do something with the matrix data in argv */
  sqlite_finish(db);
  sqlite_close(db);

Issues:

  *  If one query is in progress and we do sqlite_compile(), does
     that abort the current query or return an error code?

  *  What happens here:
        sqlite_compile(db, "SELECT a FROM t1; SELECT b,c FROM t2;");
        sqlite_table(db, &nrow, &ncolumn, &argv);
     What value is returned for nrow and ncolumn?  Or is this an error?
     Or maybe only the first table is returned?