SQLite

# possible *-shm file (i.e. the greater of 32KB and the system page-size).
#
do_execsql_test 0.0 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(x);
} {wal}
set MINSHMSZ [file size test.db-shm]
set dfltpgsz [db one {PRAGMA page_size}]

foreach bZeroShm {0 1} {
  for {set pgsz 512} {$pgsz<=65536} {set pgsz [expr {$pgsz*2}]} {
set TN [expr $bZeroShm+1]
do_multiclient_test tn {
  
  # Close all connections and delete the database.
  #
  code1 { db close  }
  code2 { db2 close }
  code3 { db3 close }
  forcedelete test.db
  
  # Do not run tests with the connections in the same process.
  #
  if {$tn==2} continue

  foreach c {code1 code2 code3} {
    $c {
      sqlite3_shutdown
      sqlite3_config_uri 1
    }
  }

  do_test $TN.1.1 {
    code2 { sqlite3 db2 test.db }
    sql2 { 
      CREATE TABLE t1(x, y);
      PRAGMA journal_mode = WAL;
      INSERT INTO t1 VALUES('a', 'b');
      INSERT INTO t1 VALUES('c', 'd');
    }
    file exists test.db-shm
  } {1}

  do_test $TN.1.2.1 {
    copy_to_test2 $bZeroShm
    code1 {
      sqlite3 db file:test.db2?readonly_shm=1
    }

    sql1 { SELECT * FROM t1 }
  } {a b c d}
  do_test $TN.1.2.2 {
    sql1 { SELECT * FROM t1 }
  } {a b c d}

  do_test $TN.1.3.1 {
    code3 { sqlite3 db3 test.db2 }
    sql3 { SELECT * FROM t1 }
  } {a b c d}

  do_test $TN.1.3.2 {
    sql1 { SELECT * FROM t1 }
  } {a b c d}

  code1 { db close  }
  code2 { db2 close }
  code3 { db3 close }

  do_test $TN.2.1 {
    code2 { sqlite3 db2 test.db }
    sql2 { 
      INSERT INTO t1 VALUES('e', 'f');
      INSERT INTO t1 VALUES('g', 'h');
    }
    file exists test.db-shm
  } {1}

  do_test $TN.2.2 {
    copy_to_test2 $bZeroShm
    code1 {
      sqlite3 db file:test.db2?readonly_shm=1
    }
    sql1 { 
      BEGIN;
      SELECT * FROM t1;
    }
  } {a b c d e f g h}

  do_test $TN.2.3.1 {
    code3 { sqlite3 db3 test.db2 }
    sql3 { SELECT * FROM t1 }
  } {a b c d e f g h}
  do_test $TN.2.3.2 {
    sql3 { INSERT INTO t1 VALUES('i', 'j') }
    code3 { db3 close }
    sql1 { COMMIT } 
  } {}
  do_test $TN.2.3.3 {
    sql1 { SELECT * FROM t1 }
  } {a b c d e f g h i j}


  #-----------------------------------------------------------------------
  # 3.1.*: That a readonly_shm connection can read a database file if both
  #        the *-wal and *-shm files are zero bytes in size.
  #
  # 3.2.*: That it flushes the cache if, between transactions on a db with a
  #        zero byte *-wal file, some other connection modifies the db, then
  #        does "PRAGMA wal_checkpoint=truncate" to truncate the wal file
  #        back to zero bytes in size.
  #
  # 3.3.*: That, if between transactions some other process wraps the wal
  #        file, the readonly_shm client reruns recovery.
  #
  catch { code1 { db close } }
  catch { code2 { db2 close } }
  catch { code3 { db3 close } }
  do_test $TN.3.1.0 {
    list [file exists test.db-wal] [file exists test.db-shm]
  } {0 0}
  do_test $TN.3.1.1 {
    close [open test.db-wal w]
    close [open test.db-shm w]
    code1 {
      sqlite3 db file:test.db?readonly_shm=1
    }
    sql1 { SELECT * FROM t1 }
  } {a b c d e f g h}

  do_test $TN.3.2.0 {
    list [file size test.db-wal] [file size test.db-shm]
  } {0 0}
  do_test $TN.3.2.1 {
    code2 { sqlite3 db2 test.db }
    sql2 { INSERT INTO t1 VALUES(1, 2) ; PRAGMA wal_checkpoint=truncate }
    code2 { db2 close }
    sql1 { SELECT * FROM t1 }
  } {a b c d e f g h 1 2}
  do_test $TN.3.2.2 {
    list [file size test.db-wal] [file size test.db-shm]
  } [list 0 $MINSHMSZ]
    set TN [expr $bZeroShm+1]-$pgsz
    do_multiclient_test tn {
      
      # Close all connections and delete the database.
      #
      code1 { db close  }
      code2 { db2 close }
      code3 { db3 close }
      forcedelete test.db
      
      # Do not run tests with the connections in the same process.
      #
      if {$tn==2} continue
    
      foreach c {code1 code2 code3} {
        $c {
          sqlite3_shutdown
          sqlite3_config_uri 1
        }
      }
    
      do_test $TN.1.1 {
        code2 { sqlite3 db2 test.db }
        sql2 "PRAGMA page_size=$::pgsz"
        sql2 {
          CREATE TABLE t1(x, y);
          PRAGMA journal_mode = WAL;
          INSERT INTO t1 VALUES('a', 'b');
          INSERT INTO t1 VALUES('c', 'd');
        }
        file exists test.db-shm
      } {1}
    
      do_test $TN.1.2.1 {
        copy_to_test2 $bZeroShm
        code1 {
          sqlite3 db file:test.db2?readonly_shm=1
        }
    
        sql1 { SELECT * FROM t1 }
      } {a b c d}
      do_test $TN.1.2.2 {
        sql1 { SELECT * FROM t1 }
      } {a b c d}
    
      do_test $TN.1.3.1 {
        code3 { sqlite3 db3 test.db2 }
        sql3 { SELECT * FROM t1 }
      } {a b c d}
    
      do_test $TN.1.3.2 {
        sql1 { SELECT * FROM t1 }
      } {a b c d}
    
      code1 { db close  }
      code2 { db2 close }
      code3 { db3 close }
    
      do_test $TN.2.1 {
        code2 { sqlite3 db2 test.db }
        sql2 "PRAGMA page_size=$::pgsz;"
        sql2 { 
          INSERT INTO t1 VALUES('e', 'f');
          INSERT INTO t1 VALUES('g', 'h');
        }
        file exists test.db-shm
      } {1}
    
      do_test $TN.2.2 {
        copy_to_test2 $bZeroShm
        code1 {
          sqlite3 db file:test.db2?readonly_shm=1
        }
        sql1 { 
          BEGIN;
          SELECT * FROM t1;
        }
      } {a b c d e f g h}
    
      do_test $TN.2.3.1 {
        code3 { sqlite3 db3 test.db2 }
        sql3 { SELECT * FROM t1 }
      } {a b c d e f g h}
      do_test $TN.2.3.2 {
        sql3 { INSERT INTO t1 VALUES('i', 'j') }
        code3 { db3 close }
        sql1 { COMMIT } 
      } {}
      do_test $TN.2.3.3 {
        sql1 { SELECT * FROM t1 }
      } {a b c d e f g h i j}
    
    
      #-----------------------------------------------------------------------
      # 3.1.*: That a readonly_shm connection can read a database file if both
      #        the *-wal and *-shm files are zero bytes in size.
      #
      # 3.2.*: That it flushes the cache if, between transactions on a db with a
      #        zero byte *-wal file, some other connection modifies the db, then
      #        does "PRAGMA wal_checkpoint=truncate" to truncate the wal file
      #        back to zero bytes in size.
      #
      # 3.3.*: That, if between transactions some other process wraps the wal
      #        file, the readonly_shm client reruns recovery.
      #
      catch { code1 { db close } }
      catch { code2 { db2 close } }
      catch { code3 { db3 close } }
      do_test $TN.3.1.0 {
        list [file exists test.db-wal] [file exists test.db-shm]
      } {0 0}
      do_test $TN.3.1.1 {
        close [open test.db-wal w]
        close [open test.db-shm w]
        code1 {
          sqlite3 db file:test.db?readonly_shm=1
        }
        sql1 { SELECT * FROM t1 }
      } {a b c d e f g h}
    
      do_test $TN.3.2.0 {
        list [file size test.db-wal] [file size test.db-shm]
      } {0 0}
      do_test $TN.3.2.1 {
        code2 { sqlite3 db2 test.db }
        sql2 { INSERT INTO t1 VALUES(1, 2) ; PRAGMA wal_checkpoint=truncate }
        code2 { db2 close }
        sql1 { SELECT * FROM t1 }
      } {a b c d e f g h 1 2}
      if {$pgsz!=$dfltpgsz} continue
      do_test $TN.3.2.2 {
        list [file size test.db-wal] [file size test.db-shm]
      } [list 0 $MINSHMSZ]

  do_test $TN.3.3.0 {
    code2 { sqlite3 db2 test.db }
    sql2 { 
      INSERT INTO t1 VALUES(3, 4);
      INSERT INTO t1 VALUES(5, 6);
      INSERT INTO t1 VALUES(7, 8);
      INSERT INTO t1 VALUES(9, 10);
    }
    code2 { db2 close }
    code1 { db close }
    list [file size test.db-wal] [file size test.db-shm]
  } [list [wal_file_size 4 1024] $MINSHMSZ]
  do_test $TN.3.3.1 {
    code1 { sqlite3 db file:test.db?readonly_shm=1 }
    sql1 { SELECT * FROM t1 }
  } {a b c d e f g h 1 2 3 4 5 6 7 8 9 10}
  do_test $TN.3.3.2 {
    code2 { sqlite3 db2 test.db }
    sql2 { 
      PRAGMA wal_checkpoint; 
      DELETE FROM t1;
      INSERT INTO t1 VALUES('i', 'ii');
    }
    code2 { db2 close }
    list [file size test.db-wal] [file size test.db-shm]
  } [list [wal_file_size 4 1024] $MINSHMSZ]
  do_test $TN.3.3.3 {
    sql1 { SELECT * FROM t1 }
  } {i ii}

  #-----------------------------------------------------------------------
  #
  #
  catch { code1 { db close } }
  catch { code2 { db2 close } }
  catch { code3 { db3 close } }

  do_test $TN.4.0 {
    code1 { forcedelete test.db }
    code1 { sqlite3 db test.db }
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(x);
      INSERT INTO t1 VALUES('hello');
      INSERT INTO t1 VALUES('world');
    }

    copy_to_test2 $bZeroShm

    code1 { db close }
  } {}

  do_test $TN.4.1.1 {
    code2 { sqlite3 db2 file:test.db2?readonly_shm=1 }
    sql2 { SELECT * FROM t1 }
  } {hello world}

  do_test $TN.4.1.2 {
    code3 { sqlite3 db3 test.db2 }
    sql3 {
      INSERT INTO t1 VALUES('!');
      PRAGMA wal_checkpoint = truncate;
    }
    code3 { db3 close }
  } {}
  do_test $TN.4.1.3 {
    sql2 { SELECT * FROM t1 }
  } {hello world !}

  catch { code1 { db close } }
  catch { code2 { db2 close } }
  catch { code3 { db3 close } }

  do_test $TN.4.2.1 {
    code1 { sqlite3 db test.db }
    sql1 {
      INSERT INTO t1 VALUES('!');
      INSERT INTO t1 VALUES('!');

      PRAGMA cache_size = 10;
      CREATE TABLE t2(x);

      BEGIN;
        WITH s(i) AS (
          SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<500
          )
        INSERT INTO t2 SELECT randomblob(500) FROM s;
        SELECT count(*) FROM t2;
    } 
  } {500}
  set sz [file size test.db-wal]
  do_test $TN.4.2.2.(sz=$sz) {
    expr {$sz>400000}
  } {1}
  do_test $TN.4.2.4 {
    file_control_persist_wal db 1; db close

    copy_to_test2 $bZeroShm
    code2 { sqlite3 db2 file:test.db2?readonly_shm=1 }
    sql2 {
      SELECT * FROM t1;
      SELECT count(*) FROM t2;
    }
  } {hello world ! ! 0}

  #-----------------------------------------------------------------------
  #
  #
  catch { code1 { db close } }
  catch { code2 { db2 close } }
  catch { code3 { db3 close } }

  do_test $TN.5.0 {
    code1 { forcedelete test.db }
    code1 { sqlite3 db test.db }
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(x);
      INSERT INTO t1 VALUES('hello');
      INSERT INTO t1 VALUES('world');
      INSERT INTO t1 VALUES('!');
      INSERT INTO t1 VALUES('world');
      INSERT INTO t1 VALUES('hello');
    }

    copy_to_test2 $bZeroShm
    
    code1 { db close }
  } {}

  do_test $TN.5.1 {
    code2 { sqlite3 db2 file:test.db2?readonly_shm=1 }
    sql2 {
      SELECT * FROM t1;
    }
  } {hello world ! world hello}

  do_test $TN.5.2 {
    code1 {
      proc handle_read {op args} {
        if {$op=="xRead" && [file tail [lindex $args 0]]=="test.db2-wal"} {
          set ::res2 [sql2 { SELECT * FROM t1 }]
        }
        puts "$msg xRead $args"
        return "SQLITE_OK"
      }
      testvfs tvfs -fullshm 1

      sqlite3 db file:test.db2?vfs=tvfs
      db eval { SELECT * FROM sqlite_master }

      tvfs filter xRead
      tvfs script handle_read
    }
    sql1 {
      PRAGMA wal_checkpoint = truncate;
    }
    code1 { set ::res2 }
  } {hello world ! world hello}

  do_test $TN.5.3 {
    code1 { db close }
    code1 { tvfs delete }
  } {}

  #-----------------------------------------------------------------------
  #
  #
  catch { code1 { db close } }
  catch { code2 { db2 close } }
  catch { code3 { db3 close } }

  do_test $TN.6.1 {
    code1 { forcedelete test.db }
    code1 { sqlite3 db test.db }
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(x);
      INSERT INTO t1 VALUES('hello');
      INSERT INTO t1 VALUES('world');
      INSERT INTO t1 VALUES('!');
      INSERT INTO t1 VALUES('world');
      INSERT INTO t1 VALUES('hello');
    }

    copy_to_test2 $bZeroShm
    
    code1 { db close }
  } {}

  do_test $TN.6.2 {
    code1 {
      set ::nRem 5
      proc handle_read {op args} {
        if {$op=="xRead" && [file tail [lindex $args 0]]=="test.db2-wal"} {
          incr ::nRem -1
          if {$::nRem==0} {
            code2 { sqlite3 db2 test.db2 }
            sql2  { PRAGMA wal_checkpoint = truncate }
          }
        }
        return "SQLITE_OK"
      }
      testvfs tvfs -fullshm 1

      tvfs filter xRead
      tvfs script handle_read

      sqlite3 db file:test.db2?readonly_shm=1&vfs=tvfs
      db eval { SELECT * FROM t1 }
    }
  } {hello world ! world hello}

  do_test $TN.6.3 {
    code1 { db close }
    code1 { tvfs delete }
  } {}
}
      do_test $TN.3.3.0 {
        code2 { sqlite3 db2 test.db }
        sql2 { 
          INSERT INTO t1 VALUES(3, 4);
          INSERT INTO t1 VALUES(5, 6);
          INSERT INTO t1 VALUES(7, 8);
          INSERT INTO t1 VALUES(9, 10);
        }
        code2 { db2 close }
        code1 { db close }
        list [file size test.db-wal] [file size test.db-shm]
      } [list [wal_file_size 4 1024] $MINSHMSZ]
      do_test $TN.3.3.1 {
        code1 { sqlite3 db file:test.db?readonly_shm=1 }
        sql1 { SELECT * FROM t1 }
      } {a b c d e f g h 1 2 3 4 5 6 7 8 9 10}
      do_test $TN.3.3.2 {
        code2 { sqlite3 db2 test.db }
        sql2 { 
          PRAGMA wal_checkpoint; 
          DELETE FROM t1;
          INSERT INTO t1 VALUES('i', 'ii');
        }
        code2 { db2 close }
        list [file size test.db-wal] [file size test.db-shm]
      } [list [wal_file_size 4 1024] $MINSHMSZ]
      do_test $TN.3.3.3 {
        sql1 { SELECT * FROM t1 }
      } {i ii}
    
      #-----------------------------------------------------------------------
      #
      #
      catch { code1 { db close } }
      catch { code2 { db2 close } }
      catch { code3 { db3 close } }
    
      do_test $TN.4.0 {
        code1 { forcedelete test.db }
        code1 { sqlite3 db test.db }
        sql1 {
          PRAGMA journal_mode = wal;
          CREATE TABLE t1(x);
          INSERT INTO t1 VALUES('hello');
          INSERT INTO t1 VALUES('world');
        }
    
        copy_to_test2 $bZeroShm
    
        code1 { db close }
      } {}
    
      do_test $TN.4.1.1 {
        code2 { sqlite3 db2 file:test.db2?readonly_shm=1 }
        sql2 { SELECT * FROM t1 }
      } {hello world}
    
      do_test $TN.4.1.2 {
        code3 { sqlite3 db3 test.db2 }
        sql3 {
          INSERT INTO t1 VALUES('!');
          PRAGMA wal_checkpoint = truncate;
        }
        code3 { db3 close }
      } {}
      do_test $TN.4.1.3 {
        sql2 { SELECT * FROM t1 }
      } {hello world !}
    
      catch { code1 { db close } }
      catch { code2 { db2 close } }
      catch { code3 { db3 close } }
    
      do_test $TN.4.2.1 {
        code1 { sqlite3 db test.db }
        sql1 {
          INSERT INTO t1 VALUES('!');
          INSERT INTO t1 VALUES('!');
    
          PRAGMA cache_size = 10;
          CREATE TABLE t2(x);
    
          BEGIN;
            WITH s(i) AS (
              SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<500
              )
            INSERT INTO t2 SELECT randomblob(500) FROM s;
            SELECT count(*) FROM t2;
        } 
      } {500}
      set sz [file size test.db-wal]
      do_test $TN.4.2.2.(sz=$sz) {
        expr {$sz>400000}
      } {1}
      do_test $TN.4.2.4 {
        file_control_persist_wal db 1; db close
    
        copy_to_test2 $bZeroShm
        code2 { sqlite3 db2 file:test.db2?readonly_shm=1 }
        sql2 {
          SELECT * FROM t1;
          SELECT count(*) FROM t2;
        }
      } {hello world ! ! 0}
    
      #-----------------------------------------------------------------------
      #
      #
      catch { code1 { db close } }
      catch { code2 { db2 close } }
      catch { code3 { db3 close } }
    
      do_test $TN.5.0 {
        code1 { forcedelete test.db }
        code1 { sqlite3 db test.db }
        sql1 {
          PRAGMA journal_mode = wal;
          CREATE TABLE t1(x);
          INSERT INTO t1 VALUES('hello');
          INSERT INTO t1 VALUES('world');
          INSERT INTO t1 VALUES('!');
          INSERT INTO t1 VALUES('world');
          INSERT INTO t1 VALUES('hello');
        }
    
        copy_to_test2 $bZeroShm
        
        code1 { db close }
      } {}
    
      do_test $TN.5.1 {
        code2 { sqlite3 db2 file:test.db2?readonly_shm=1 }
        sql2 {
          SELECT * FROM t1;
        }
      } {hello world ! world hello}
    
      do_test $TN.5.2 {
        code1 {
          proc handle_read {op args} {
            if {$op=="xRead" && [file tail [lindex $args 0]]=="test.db2-wal"} {
              set ::res2 [sql2 { SELECT * FROM t1 }]
            }
            puts "$msg xRead $args"
            return "SQLITE_OK"
          }
          testvfs tvfs -fullshm 1
    
          sqlite3 db file:test.db2?vfs=tvfs
          db eval { SELECT * FROM sqlite_master }
    
          tvfs filter xRead
          tvfs script handle_read
        }
        sql1 {
          PRAGMA wal_checkpoint = truncate;
        }
        code1 { set ::res2 }
      } {hello world ! world hello}
    
      do_test $TN.5.3 {
        code1 { db close }
        code1 { tvfs delete }
      } {}
    
      #-----------------------------------------------------------------------
      #
      #
      catch { code1 { db close } }
      catch { code2 { db2 close } }
      catch { code3 { db3 close } }
    
      do_test $TN.6.1 {
        code1 { forcedelete test.db }
        code1 { sqlite3 db test.db }
        sql1 {
          PRAGMA journal_mode = wal;
          CREATE TABLE t1(x);
          INSERT INTO t1 VALUES('hello');
          INSERT INTO t1 VALUES('world');
          INSERT INTO t1 VALUES('!');
          INSERT INTO t1 VALUES('world');
          INSERT INTO t1 VALUES('hello');
        }
    
        copy_to_test2 $bZeroShm
        
        code1 { db close }
      } {}
    
      do_test $TN.6.2 {
        code1 {
          set ::nRem 5
          proc handle_read {op args} {
            if {$op=="xRead" && [file tail [lindex $args 0]]=="test.db2-wal"} {
              incr ::nRem -1
              if {$::nRem==0} {
                code2 { sqlite3 db2 test.db2 }
                sql2  { PRAGMA wal_checkpoint = truncate }
              }
            }
            return "SQLITE_OK"
          }
          testvfs tvfs -fullshm 1
    
          tvfs filter xRead
          tvfs script handle_read
    
          sqlite3 db file:test.db2?readonly_shm=1&vfs=tvfs
          db eval { SELECT * FROM t1 }
        }
      } {hello world ! world hello}
    
      do_test $TN.6.3 {
        code1 { db close }
        code1 { tvfs delete }
      } {}
    }
  } ;# for pgsz
} ;# foreach bZeroShm

finish_test