--[[----------------------------------------------------------------------------

WFSQLiteReadOnlyDatabaseImpTest.lua

--------------------------------------------------------------------------------

ADOBE CONFIDENTIAL
------------------
Copyright 2020 Adobe
All Rights Reserved.

NOTICE: All information contained herein is, and remains
the property of Adobe and its suppliers, if any. The intellectual
and technical concepts contained herein are proprietary to Adobe
and its suppliers and are protected by all applicable intellectual
property laws, including trade secret and copyright laws.
Dissemination of this information or reproduction of this material
is strictly forbidden unless prior written permission is obtained
from Adobe.

------------------------------------------------------------------------------]]

	-- WF.core.lua
local WFTimer = require 'WFTimer'

	-- WF.core.os
local KSPathUtils = require 'KSPathUtils'
local WFFileUtils = require 'WFFileUtils'

	-- WF.core.self_test
local WFRxTestCase = require 'WFRxTestCase'

	-- WF.core.utils
local KSIdGenerator = require 'KSIdGenerator'

	-- WF.sqlite.database
local WFSQLiteDatabaseImp = require 'WFSQLiteDatabaseImp' -- (native)
local WFSQLiteDatabaseTestUtils = require 'WFSQLiteDatabaseTestUtils'


--============================================================================--

-- @mark WFSQLiteReadOnlyDatabaseImpTest

local WFSQLiteReadOnlyDatabaseImpTest = {}

--------------------------------------------------------------------------------

local db -- writeable
local roDb -- read-only

local tempDbPath

--------------------------------------------------------------------------------

local function temporaryPathForDb()

	local tempFolder = KSPathUtils.getStandardFilePath 'temp'
	local dbFolder = KSPathUtils.child( tempFolder, 'WFSQLiteDatabaseTest' )
	WFFileUtils.mkdir( dbFolder )

	local dbName = string.format( "testDB-%s", KSIdGenerator.uuid() )

	local dbPath = KSPathUtils.child( dbFolder, dbName )

	dbPath = WFFileUtils.ensureFileNameIsUnique( dbPath )

	tempDbPath = dbPath

	return dbPath

end

--------------------------------------------------------------------------------

local function uniqueInMemoryDbName()
	
	-- Using ':memory:' as the database name doesn't allow multiple connections to 
	-- be created to the same db so use a named in memory db with `mode=memory` so 
	-- that we can create both a writeable db connection and a read only connection
	return 'file:memdb' .. KSIdGenerator.localCounter() .. '?mode=memory&cache=shared'

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.tearDown()
	
	if roDb then
		
		roDb:close()
		
		roDb = nil
		
		collectgarbage()
		collectgarbage()
		
	end

	if db then

		db:close()

		db = nil

		collectgarbage()
		collectgarbage()

	end

	if tempDbPath then

		-- [scouten 2015-02-24]: There is a sporadic failure to delete this
		-- file on Windows. Retry after a delay if it fails the first time.
		-- It's not critical that this get deleted and not worth signaling
		-- a test failure if the deletion fails.

		local thisTempDbPath = tempDbPath

		if not pcall( WFFileUtils.delete, tempDbPath ) then

			WFTimer.afterDelayDo( .25, function()
				WFFileUtils.delete( thisTempDbPath )
			end )

		end

		tempDbPath = nil

	end

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestCantCreateDb( rxTest )
	
	local _, err = pcall( function()
		roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( temporaryPathForDb() )
	end )
	
	local errorCode14 = err:find( "error code 14" )
	rxTest:expectEqual( errorCode14 ~= nil, true )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestNoWriteableSQLAllowed( rxTest )
	local dbPath = uniqueInMemoryDbName()
	
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )
	db:exec( "CREATE TABLE t1( a, b NOT NULL )" )
	db:exec( "INSERT INTO t1 VALUES(1, 'foo')" )
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	local _, err = pcall( function()
		roDb:exec( "CREATE TABLE t2( a, b NOT NULL )" )
	end )
	
	local errorCode8 = err:find( "error code 8" )
	rxTest:expectEqual( errorCode8 ~= nil, true )
	
	_, err = pcall( function()
		roDb:exec( "INSERT INTO t1 VALUES(1, 'foo')" )
	end )
	
	errorCode8 = err:find( "error code 8" )
	rxTest:expectEqual( errorCode8 ~= nil, true )
	
end


--============================================================================--

-- @mark GC and :close

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestGCWithoutClose()

	-- Hard to tell much from this test, other than it doesn't blow up.

	db = WFSQLiteDatabaseImp.makeReadOnlyDatabase( uniqueInMemoryDbName() )
	db = nil

	collectgarbage()
	collectgarbage()
	collectgarbage()

end

--============================================================================--

-- @mark extended error codes

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestExtendedErrorCodesEnabled( rxTest )
	
	local dbPath = uniqueInMemoryDbName()
	
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec( "CREATE TABLE t1( a, b NOT NULL )" )
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	-- if extended error codes are on,
	-- this should throw extended error code 257: missing collseq 
	local _, err = pcall( function()
		db:exec( "SELECT b FROM t1 ORDER BY b COLLATE fake_collate" )
	end )

	local errorCode257 = err:find( "error code 257" )

	rxTest:expectEqual( errorCode257 ~= nil, true )


end

--============================================================================--

-- @mark prepared statements

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestPrepare_errorMultipleStatements( rxTest )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	rxTest:expectError( function()
		-- it doesn't matter here that these are create statements and we're using a read 
		-- only connection as we are only preparing the statements
		roDb:prepare( "CREATE TABLE t1( a ); CREATE TABLE t2( b )" )
	end )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestPrepare_errorSyntaxError( rxTest )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	rxTest:expectError( function()
		roDb:prepare( "CREATE BOGUS THING t1" )
	end )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_toString( rxTest )
	local dbPath = uniqueInMemoryDbName()
	
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )
	db:exec( "CREATE TABLE foo( a, b, c )" )
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )

	local stmt = roDb:prepare "SELECT a, b, c FROM foo ORDER BY a"

	rxTest:expectEqual(
		[[WFSQLiteStatement( "SELECT a, b, c FROM foo ORDER BY a" on database "]]..dbPath..[[" )]],
		tostring( stmt ) )

	stmt:finalize()

	rxTest:expectEqual(
		[[WFSQLiteStatement( "(finalized)" on database "(finalized)" FINALIZED )]],
		tostring( stmt ) )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_finalize_errorFinalizeTwice( rxTest )

	local dbPath = uniqueInMemoryDbName()
	
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )
	db:exec( "CREATE TABLE foo( a, b, c )" )
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )

	local stmt = roDb:prepare "SELECT a, b, c FROM foo ORDER BY a"

	stmt:finalize()

	rxTest:expectEqual(
		[[WFSQLiteStatement( "(finalized)" on database "(finalized)" FINALIZED )]],
		tostring( stmt ) )

	rxTest:expectError( function()
		stmt:finalize()
	end )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_step( rxTest )
	
	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a, b, c )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ?, ?, ? )"

	insertStmt:bindByPosition( 1, "b1", "c1" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 2, "b2", "c2" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 3, "b3", "c3" )
	insertStmt:runToCompletion()
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	local queryStmt = roDb:prepare "SELECT a, b, c FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			a = 1,
			b = "b1",
			c = "c1",
		},
		queryStmt:step() )

	rxTest:expectEqual(
		{
			a = 2,
			b = "b2",
			c = "c2",
		},
		queryStmt:step() )

	rxTest:expectEqual(
		{
			a = 3,
			b = "b3",
			c = "c3",
		},
		queryStmt:step() )

	rxTest:expectEqual( nil, queryStmt:step() )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_step_errorFinalized( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a, b, c )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ?, ?, ? )"

	insertStmt:bindByPosition( 1, "b1", "c1" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 2, "b2", "c2" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 3, "b3", "c3" )
	insertStmt:runToCompletion()
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	local queryStmt = roDb:prepare "SELECT a, b, c FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			a = 1,
			b = "b1",
			c = "c1",
		},
		queryStmt:step() )

	rxTest:expectEqual(
		{
			a = 2,
			b = "b2",
			c = "c2",
		},
		queryStmt:step() )

	queryStmt:reset()
	queryStmt:finalize()

	rxTest:expectError( function()
		queryStmt:step()
	end )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_reset( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a, b, c )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ?, ?, ? )"

	insertStmt:bindByPosition( 1, "b1", "c1" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 2, "b2", "c2" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 3, "b3", "c3" )
	insertStmt:runToCompletion()
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	local queryStmt = roDb:prepare "SELECT a, b, c FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			a = 1,
			b = "b1",
			c = "c1",
		},
		queryStmt:step() )

	rxTest:expectEqual(
		{
			a = 2,
			b = "b2",
			c = "c2",
		},
		queryStmt:step() )

	queryStmt:reset()

	rxTest:expectEqual(
		{
			a = 1,
			b = "b1",
			c = "c1",
		},
		queryStmt:step() )

	rxTest:expectEqual(
		{
			a = 2,
			b = "b2",
			c = "c2",
		},
		queryStmt:step() )

	rxTest:expectEqual(
		{
			a = 3,
			b = "b3",
			c = "c3",
		},
		queryStmt:step() )

	rxTest:expectEqual( nil, queryStmt:step() )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_runToCompletion_errorTooManyRows( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ? )"

	for i = 1, 21 do
		insertStmt:bind( 1, i )
		insertStmt:runToCompletion()
	end
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	local queryStmt = roDb:prepare "SELECT a FROM foo"

	rxTest:expectError( function()
		queryStmt:runToCompletion() -- should fail: >20 rows
	end )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_gatherArrayOfTables( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a, b, c )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ?, ?, ? )"

	insertStmt:bindByPosition( 1, "b1", "c1" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 2, "b2", "c2" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 3, "b3", "c3" )
	insertStmt:runToCompletion()
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	local queryStmt = roDb:prepare "SELECT a, b, c FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			{
				a = 1,
				b = "b1",
				c = "c1",
			},
			{
				a = 2,
				b = "b2",
				c = "c2",
			},
			{
				a = 3,
				b = "b3",
				c = "c3",
			},
		},
		queryStmt:gatherArrayOfTables() )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_gatherArray( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a, b, c )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ?, ?, ? )"

	insertStmt:bindByPosition( 1, "b1", "c1" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 2, "b2", "c2" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 3, "b3", "c3" )
	insertStmt:runToCompletion()
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	local queryStmt = roDb:prepare "SELECT a, b, c FROM foo WHERE a > ? ORDER BY a"

	local queryStmt_gatherArray = queryStmt:getMethod 'gatherArray'

	rxTest:expectEqual( 'function', type( queryStmt_gatherArray ) )

	local queryStmt_bindByPosition = queryStmt:getMethod 'bindByPosition'

	queryStmt_bindByPosition( queryStmt, 0 )

	rxTest:expectEqual( { 1, 2, 3 }, queryStmt_gatherArray( queryStmt ) )

	-- calling this again with new bindings should be kosher, since gatherArray
	-- resets statement on completion

	queryStmt_bindByPosition( queryStmt, 1 )

	rxTest:expectEqual( { 2, 3 }, queryStmt_gatherArray( queryStmt ) )

	insertStmt:bindByPosition( 4, "b4", "c4" )
	insertStmt:runToCompletion()

	queryStmt_bindByPosition( queryStmt, 1 )

	-- calling this again should pick up the latest results
	rxTest:expectEqual( { 2, 3, 4 }, queryStmt_gatherArray( queryStmt ) )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_gatherSet( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a, b, c )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ?, ?, ? )"

	insertStmt:bindByPosition( 1, "b1", "c1" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 2, "b2", "c2" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 3, "b3", "c3" )
	insertStmt:runToCompletion()
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	local queryStmt = roDb:prepare "SELECT b, c FROM foo"

	rxTest:expectEqual(
		{
			b1 = true,
			b2 = true,
			b3 = true,
		},
		queryStmt:gatherSet() )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_gatherMap( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a, b, c )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ?, ?, ? )"

	insertStmt:bindByPosition( 1, "b1", "c1" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 2, "b2", "c2" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 3, "b3", "c3" )
	insertStmt:runToCompletion()

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	local queryStmt = roDb:prepare "SELECT b, c FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			b1 = "c1",
			b2 = "c2",
			b3 = "c3",
		},
		queryStmt:gatherMap() )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_gatherArrayOfArray( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a, b, c )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ?, ?, ? )"

	insertStmt:bindByPosition( 1, "b1", "c1" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 2, "b2", "c2" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 3, "b3", "c3" )
	insertStmt:runToCompletion()

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	local queryStmt = roDb:prepare "SELECT a, b, c FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			{
				1,
				"b1",
				"c1",
			},
			{
				2,
				"b2",
				"c2",
			},
			{
				3,
				"b3",
				"c3",
			},
		},
		queryStmt:gatherArrayOfArray() )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_getMethod( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a, b, c )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ?, ?, ? )"

	local insertStmt_bindByPosition = insertStmt:getMethod 'bindByPosition'
	rxTest:expectEqual( 'function', type( insertStmt_bindByPosition ) )

	local insertStmt_runToCompletion = insertStmt:getMethod 'runToCompletion'
	rxTest:expectEqual( 'function', type( insertStmt_runToCompletion ) )

	insertStmt_bindByPosition( insertStmt, 1, "b1", "c1" )
	insertStmt_runToCompletion( insertStmt )

	insertStmt_bindByPosition( insertStmt, 2, "b2", "c2" )
	insertStmt_runToCompletion( insertStmt )

	insertStmt_bindByPosition( insertStmt, 3, "b3", "c3" )
	insertStmt_runToCompletion( insertStmt )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	local queryStmt = roDb:prepare "SELECT a, b, c FROM foo WHERE a > ? ORDER BY a"

	local queryStmt_gatherArrayOfArray = queryStmt:getMethod 'gatherArrayOfArray'

	rxTest:expectEqual( 'function', type( queryStmt_gatherArrayOfArray ) )

	local queryStmt_bindByPosition = insertStmt_bindByPosition
	
	queryStmt_bindByPosition( queryStmt, 0 )

	rxTest:expectEqual(
		{
			{
				1,
				"b1",
				"c1",
			},
			{
				2,
				"b2",
				"c2",
			},
			{
				3,
				"b3",
				"c3",
			},
		},
		queryStmt_gatherArrayOfArray( queryStmt ) )

	-- calling this method with new bindings should be legal without reset
	-- because gatherArrayOfArray resets on completion
	
	queryStmt_bindByPosition( queryStmt, 1 )

	rxTest:expectEqual(
		{
			{
				2,
				"b2",
				"c2",
			},
			{
				3,
				"b3",
				"c3",
			},
		},
		queryStmt_gatherArrayOfArray( queryStmt ) )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestStatement_nativeEncoder( rxTest )

	local sampleEncoder = WFSQLiteDatabaseTestUtils.sampleEncoder

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec "CREATE TABLE foo( a )"

	local insertStmt = db:prepare "INSERT INTO foo VALUES( ? )"

	insertStmt:bindByPosition( 14 )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 21 )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 41 )
	insertStmt:runToCompletion()

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	local stmt = roDb:prepare "SELECT * FROM foo"

	local result = sampleEncoder( stmt )
	stmt:reset()

	rxTest:expectEqual( 76, result )

end


--============================================================================--

-- @mark WFSQLiteReadOnlyDatabaseImp methods

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestExec_errorBadSQL( rxTest )

	db = WFSQLiteDatabaseImp.makeReadOnlyDatabase ':memory:'

	local _, err = pcall( function()
		db:exec "CREATE BOGUS THING t1( a )"
	end )
	
	rxTest:expectEqual( true, err:find( 'syntax error' ) ~= nil )
end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestSetProgressCallback( rxTest )

	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec( "CREATE TABLE foo( a )" )

	local progressCallbackCalledCount = 0
	local progressCallbackCalledArgs -- will fill in with most recent args
	local progressCallbackNextResult

	local function progressCallbackSpy( ... )
		progressCallbackCalledCount = progressCallbackCalledCount + 1
		progressCallbackCalledArgs = { ..., n = select( '#', ... ) }
		return progressCallbackNextResult
	end

	for i = 1, 100 do
		db:exec( "INSERT INTO foo VALUES( " .. tostring( i ) .. " )" )
			-- Kids, don't try this at home.
	end
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	roDb:setProgressCallback( progressCallbackSpy, 10 )

	-- Test the cancel case.
	
	local queryStmt = roDb:prepare "SELECT a FROM foo ORDER BY a"

	rxTest:expectSpecificError( "<<<SQLite operation canceled by user>>>",
		function()
			return queryStmt:gatherArrayOfArray()
		end )

	rxTest:expectEqual( 1, progressCallbackCalledCount )
	rxTest:expectEqual( { n = 0 }, progressCallbackCalledArgs )

	-- Test the success case.

	progressCallbackNextResult = true
		-- inverse of SQLite definition: true means continue

	local queryResults = queryStmt:gatherArrayOfArray()
	rxTest:expectEqual( 100, #queryResults )

	rxTest:expectEqual( true, progressCallbackCalledCount >= 1 )
	rxTest:expectEqual( { n = 0 }, progressCallbackCalledArgs )

	-- Clear the progress hook.

	queryStmt:reset()

	progressCallbackCalledCount = 0

	roDb:setProgressCallback( nil ) -- clears the hook

	queryResults = queryStmt:gatherArrayOfArray()
	rxTest:expectEqual( 100, #queryResults )

	rxTest:expectEqual( 0, progressCallbackCalledCount )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestCustomStringOrder( rxTest )
	
	local dbPath = uniqueInMemoryDbName()
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec( "CREATE TABLE foo( a )" )
	
	db:exec( "INSERT INTO foo VALUES( 0 )" )
	db:exec( "INSERT INTO foo VALUES( 'blue' )" )
	db:exec( "INSERT INTO foo VALUES( 'red' )" )
	db:exec( "INSERT INTO foo VALUES( 'purple' )" )
	db:exec( "INSERT INTO foo VALUES( 'green' )" )
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	roDb:registerCustomStringOrder { 'red', 'yellow', 'green', 'blue', 'purple' }
	
	local queryStmt = roDb:prepare( "SELECT a FROM foo ORDER BY a COLLATE custom_string_order" )

	rxTest:expectEqual( { 0, 'red', 'green', 'blue', 'purple' }, queryStmt:gatherArray() )

	db:exec "INSERT INTO foo VALUES( 'blue' )"

	queryStmt:reset()

	rxTest:expectEqual( { 0, 'red', 'green', 'blue', 'blue', 'purple' }, queryStmt:gatherArray() )

	db:exec( "INSERT INTO foo VALUES( 'sheep' )" )

	queryStmt:reset()

	rxTest:expectEqual( { 0, 'red', 'green', 'blue', 'blue', 'purple', 'sheep' },
						queryStmt:gatherArray() )

	db:exec( "INSERT INTO foo VALUES( 'donkey' )" )

	queryStmt:reset()

	rxTest:expectEqual( { 0, 'red', 'green', 'blue', 'blue', 'purple', 'donkey', 'sheep' },
						queryStmt:gatherArray() )

	db:exec( "INSERT INTO foo VALUES( 'red' )" )

	queryStmt:reset()

	rxTest:expectEqual( { 0, 'red', 'red', 'green', 'blue', 'blue', 'purple', 'donkey', 'sheep' },
						queryStmt:gatherArray() )

	roDb:registerCustomStringOrder { 'sheep', 'donkey', 'blue' }

	queryStmt:reset()

	rxTest:expectEqual( { 0, 'sheep', 'donkey', 'blue', 'blue', 'green', 'purple', 'red', 'red', },
						queryStmt:gatherArray() )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestSetFatalErrorHook( rxTest )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	rxTest:log( "Test bogus handler: nil" )

	rxTest:expectError( function()

		roDb:setFatalErrorHook()

	end )

	rxTest:log( "Test bogus handler: string" )

	rxTest:expectError( function()

		roDb:setFatalErrorHook( "not allowed" )

	end )

	rxTest:log( "Test bogus handler: table" )

	rxTest:expectError( function()

		roDb:setFatalErrorHook( {} )

	end )

	rxTest:log( "Test valid initial install return value" )

	rxTest:expectEqual( roDb:setFatalErrorHook( function() end ), false )

	rxTest:log( "Test valid subsequent install return value" )

	rxTest:expectEqual( roDb:setFatalErrorHook( function() end ), true )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestCorruptDbErrorV1AndV2( rxTest )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	local sawRaiseFatalCorruptionError

	local function raiseFatalCorruptionError( path, msg, sql )

		sawRaiseFatalCorruptionError = true

		error( msg or "corrupt!" )

	end

	local sawFatalErrorHook

	local function fatalErrorHook( errorType, path, msg, sql )

		rxTest:logf( "Got fatalErrorHook with %s, %s, %s, %s",
						errorType, path, msg, sql )

		sawFatalErrorHook = errorType == "corrupt"

		error( msg or "corrupt!" )

	end

	-- Try with 2 error handlers, a v1 ( raiseFatalCorruptionError ) and
	-- a v2 ( fatalErrorHook ). Only the v1, should be called.
	roDb:installErrorHandlers( {
		
		raiseFatalCorruptionError = raiseFatalCorruptionError,

	} )

	roDb:setFatalErrorHook( fatalErrorHook )

	rxTest:expectError( function()
		roDb:exec( "SELECT test_corrupt_db_error()" )
	end )

	-- verify the v1 was called, but not the v2
	rxTest:expectEqual( sawRaiseFatalCorruptionError, true )

	rxTest:expectEqual( sawFatalErrorHook, nil )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestFatalErrorHookCorrupt( rxTest )

	local roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	local sawFatalErrorHook

	local function fatalErrorHook( errorType, path, msg, sql )

		rxTest:logf( "Got fatalErrorHook with %s, %s, %s, %s",
						errorType, path, msg, sql )

		sawFatalErrorHook = errorType == "corrupt"

	end

	-- Try with just a v2 error handler and verify that it was called.
	roDb:setFatalErrorHook( fatalErrorHook )

	rxTest:expectError( function()
		roDb:exec( "SELECT test_corrupt_db_error()" )
	end )

	-- verify the v2 was called

	rxTest:expectEqual( sawFatalErrorHook, true )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestFatalErrorHookIOError( rxTest )

	local roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	local sawFatalErrorHook

	local function fatalErrorHook( errorType, path, msg, sql )

		rxTest:logf( "Got fatalErrorHook with %s, %s, %s, %s",
						errorType, path, msg, sql )

		sawFatalErrorHook = errorType == "IO"

	end

	roDb:setFatalErrorHook( fatalErrorHook )

	rxTest:expectError( function()
		roDb:exec( "SELECT test_transient_io_error()" )
	end )

	-- verify the v2 was called

	rxTest:expectEqual( sawFatalErrorHook, true )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestTransientIoError( rxTest )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	-- Even though this gets covered in .rxTestErrorHandlers, I had some
	-- trouble with it not working properly in the bigger test, so I split
	-- this out to have an isolated test for the error function.

	rxTest:expectError( function()
		roDb:exec( "SELECT test_transient_io_error()" )
	end )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestErrorHandlers( rxTest )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	local callParams
	local rethrow

	local function errorHandlerSpy( fnName, ... )
		callParams = { name = fnName, ..., n = select( '#', ... ) }
		if rethrow then
			error "error relayed by errorHandlerSpy"
		end
	end

	local errorHandlers = {}

	local function addErrorHandlerNamed( fnName )
		errorHandlers[ fnName ] = function( ... )
			errorHandlerSpy( fnName, ... )
		end
	end

	addErrorHandlerNamed( 'raiseFatalCorruptionError' )
	addErrorHandlerNamed( 'isFatalCorruptionError' )
	addErrorHandlerNamed( 'raiseFatalButNotCorruptError' )
	addErrorHandlerNamed( 'isFatalButNotCorruptError' )
	addErrorHandlerNamed( 'raiseUserInterruptError' )
	addErrorHandlerNamed( 'isUserInterruptError' )
	addErrorHandlerNamed( 'isMalformedDatabaseSchemaError' )
	addErrorHandlerNamed( 'handleCloseDBError' )

	roDb:installErrorHandlers( errorHandlers )

	rxTest:expectError( function()
		rethrow = true
		roDb:exec( "SELECT test_transient_io_error()" )
	end )

	rxTest:expectEqual(
		{
			":memory:",
			n = 3,
			name = "raiseFatalButNotCorruptError",
		},
		callParams )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestCorruptionHandlerV2( rxTest )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	local callParams
	local rethrow

	local function errorHandlerSpy( fnName, ... )
		callParams = { name = fnName, ..., n = select( '#', ... ) }
		if rethrow then
			error "error relayed by errorHandlerSpy"
		end
	end

	local errorHandlers = {}

	local function addErrorHandlerNamed( fnName )
		errorHandlers[ fnName ] = function( ... )
			errorHandlerSpy( fnName, ... )
		end
	end

	addErrorHandlerNamed( 'raiseFatalCorruptionError' )
	addErrorHandlerNamed( 'isFatalCorruptionError' )
	addErrorHandlerNamed( 'raiseFatalButNotCorruptError' )
	addErrorHandlerNamed( 'isFatalButNotCorruptError' )
	addErrorHandlerNamed( 'raiseUserInterruptError' )
	addErrorHandlerNamed( 'isUserInterruptError' )
	addErrorHandlerNamed( 'isMalformedDatabaseSchemaError' )
	addErrorHandlerNamed( 'handleCloseDBError' )

	roDb:installErrorHandlers( errorHandlers )

	rxTest:expectError( function()
		rethrow = true
		roDb:exec( "SELECT test_transient_io_error()" )
	end )

	rxTest:expectEqual(
	{
		":memory:",
		n = 3,
		name = "raiseFatalButNotCorruptError",
	},
	callParams )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestErrorHandlers_fallbackNoHandler( rxTest )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	roDb:installErrorHandlers( {} )

	rxTest:expectError( function()
		roDb:exec( "SELECT test_transient_io_error()" )
	end )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestReleaseMemory( rxTest )

	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( ':memory:' )

	-- Can't really verify that SQLite does anything, but we can verify
	-- the API behavior.

	local result = WFSQLiteDatabaseImp.releaseMemory( 10000 ) -- arbitrary number

	rxTest:expectEqual( 'number', type( result ) )

end


--============================================================================--

-- @mark contains_word operators

--------------------------------------------------------------------------------

local happyPathTestData = {
	-- test[ 1 ] = src string
	-- test[ 2 ] = word
	-- test[ 3 ] = contains_word_starting_with expected result
	-- test[ 4 ] = contains_word_ending_with expected result
	-- test[ 5 ] = contains_word expected result
	{ "NULL", " anything ", 0, 0, 0 },
	{ "anything", "NULL", 0, 0, 0 },
	{ "NULL", "NULL", 0, 0, 0 },
	{ "", "", 0, 0, 0 },
	{ "", "anything", 0, 0, 0 },
	{ "anything", "", 1, 1, 1 },
	{ "abc", "a", 1, 0, 0 },
	{ " abc", "a", 1, 0, 0 },
	{ " abc ", "a", 1, 0, 0 },
	{ "babc ", "a", 0, 0, 0 },
	{ "abc", "c", 0, 1, 0 },
	{ " abc", "c", 0, 1, 0 },
	{ " abc ", "c", 0, 1, 0 },
	{ "abc", "abc", 1, 1, 1 },
	{ " abc", "abc", 1, 1, 1 },
	{ " abc abc ", "abc", 1, 1, 1 },
	{ " abc ", "abc", 1, 1, 1 },
	{ " babcd ", "abc", 0, 0, 0 },
	{ " abcd ", "abc", 1, 0, 0 },
	{ " babc ", "abc", 0, 1, 0 },
	{ "abcabc", "abc", 1, 1, 0 },
	{ "abcabcabcd", "abcd", 0, 1, 0 },
	{ "abcdefg deadbeefabcdef", "abcdef", 1, 1, 0 },
	{ "abc-123", "123", 0, 1, 0 },
	{ "abc-123", "-123", 0, 1, 0 },
	{ "abc-123", "abc-123", 1, 1, 1 },
	{ "abc-123", "abc", 1, 0, 0 },
	{ "abc-123", "abc-1", 1, 0, 0 },
}

--------------------------------------------------------------------------------

local function mapNullToNil( s )

	return s ~= "NULL" and s or nil

end

--------------------------------------------------------------------------------

local function runContainsTest( db, sqlWithFormatStr, operator,
								srcString, wordToFind )

	local sql = string.format( sqlWithFormatStr, operator )

	local stmt = db:prepare( sql )
	stmt:bindByPosition( srcString, wordToFind )

	local rawResult = stmt:gatherArray()
	return rawResult[ 1 ] or 0

end

--------------------------------------------------------------------------------

local containsSql1 = "SELECT %s( ?, ? )"
local containsSql2 = "SELECT 1 WHERE %s( ?, ? )"

--------------------------------------------------------------------------------

local function runOneTestInstance( db, testData, sqlWithFormatStr )

	local srcString = mapNullToNil( testData[ 1 ] )
	local wordToFind = mapNullToNil( testData[ 2 ] )

	return {
		testData[ 1 ],
		testData[ 2 ],
		runContainsTest( db, sqlWithFormatStr, 'contains_word_starting_with',
						 srcString, wordToFind ),
		runContainsTest( db, sqlWithFormatStr, 'contains_word_ending_with',
						 srcString, wordToFind ),
		runContainsTest( db, sqlWithFormatStr, 'contains_word',
						 srcString, wordToFind ),
	}

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestContainsWord( rxTest )

	db = WFSQLiteDatabaseImp.makeReadOnlyDatabase ':memory:'

	for i, testData in ipairs( happyPathTestData ) do

		rxTest:expectEqual( testData,
							runOneTestInstance( db, testData, containsSql1 ) )

		rxTest:expectEqual( testData,
							runOneTestInstance( db, testData, containsSql2 ) )

	end

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestContainsWord_errorInvalidData( rxTest )

	db = WFSQLiteDatabaseImp.makeReadOnlyDatabase ':memory:'

	local function runBadSql( template, operator )
		local sql = string.format( "SELECT " .. template, operator )
		db:exec( sql )
	end

	rxTest:expectError( runBadSql, "%s()", "contains_word" )
	rxTest:expectError( runBadSql, "%s( 'a' )", "contains_word" )
	rxTest:expectError( runBadSql, "%s( 'a', 1 )", "contains_word" )
	rxTest:expectError( runBadSql, "%s( 1, 'a' )", "contains_word" )
	rxTest:expectError( runBadSql, "%s( 'a', 'a', 'a' )", "contains_word" )

	rxTest:expectError( runBadSql, "%s()", "contains_word_starting_with" )
	rxTest:expectError( runBadSql, "%s( 'a' )", "contains_word_starting_with" )
	rxTest:expectError( runBadSql, "%s( 'a', 1 )", "contains_word_starting_with" )
	rxTest:expectError( runBadSql, "%s( 1, 'a' )", "contains_word_starting_with" )
	rxTest:expectError( runBadSql, "%s( 'a', 'a', 'a' )",
								   "contains_word_starting_with" )

	rxTest:expectError( runBadSql, "%s()", "contains_word_ending_with" )
	rxTest:expectError( runBadSql, "%s( 'a' )", "contains_word_ending_with" )
	rxTest:expectError( runBadSql, "%s( 'a', 1 )", "contains_word_ending_with" )
	rxTest:expectError( runBadSql, "%s( 1, 'a' )", "contains_word_ending_with" )
	rxTest:expectError( runBadSql, "%s( 'a', 'a', 'a' )",
								   "contains_word_ending_with" )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestContainsWord_usingTable( rxTest )
	
	local dbPath = 'file:memdb' .. KSIdGenerator.localCounter() .. '?mode=memory&cache=shared'
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )

	db:exec "CREATE TABLE t1( c1, c2, c3 )"
	db:exec "INSERT INTO t1 VALUES( 1, ' abc ', ' def ' )"
	db:exec "INSERT INTO t1 VALUES( 2, ' def ', ' abc ' )"
	db:exec "INSERT INTO t1 VALUES( 3, NULL, NULL )"

	local queryStmt = db:prepare
		[[SELECT c1 FROM t1 WHERE contains_word( c2, ? )]]

	queryStmt:bindByPosition( "abc" )

	rxTest:expectEqual( { 1 }, queryStmt:gatherArray() )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestContainsWord_bug2742430( rxTest )
	
	local dbPath = 'file:memdb' .. KSIdGenerator.localCounter() .. '?mode=memory&cache=shared'
	
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )

	db:exec( "CREATE TABLE t1( c1, c2, c3 )" )
	db:exec( "INSERT INTO t1 VALUES( 1, ' abc/', 'def ' )" )
	db:exec( "INSERT INTO t1 VALUES( 2, ' def/', 'abc ' )" )
	db:exec( "INSERT INTO t1 VALUES( 3, NULL, NULL )" )
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	local function doQuery( operand, str )

		local sql = string.format(
				"SELECT c1 FROM t1 WHERE %s( replace( c2 || c3, '/', ' ' ), ? )",
				operand )

		local stmt = roDb:prepare( sql )
		stmt:bindByPosition( str )

		return stmt:gatherArray()

	end

	rxTest:expectEqual( { 1, 2 }, doQuery( "contains_word", "abc" ) )
	rxTest:expectEqual( { 1, 2 }, doQuery( "contains_word_starting_with", "ab" ) )
	rxTest:expectEqual( { 1, 2 }, doQuery( "contains_word_starting_with", "de" ) )
	rxTest:expectEqual( { 1, 2 }, doQuery( "contains_word_ending_with", "bc" ) )
	rxTest:expectEqual( { 1, 2 }, doQuery( "contains_word_ending_with", "ef" ) )

end

--------------------------------------------------------------------------------

function WFSQLiteReadOnlyDatabaseImpTest.rxTestContainsWord_bug2898598( rxTest )
	local dbPath = 'file:memdb' .. KSIdGenerator.localCounter() .. '?mode=memory&cache=shared'
	
	db = WFSQLiteDatabaseImp.makeDatabase( dbPath )
	
	roDb = WFSQLiteDatabaseImp.makeReadOnlyDatabase( dbPath )
	
	
	local function doMultiWordSearch( operator, ... )

		local words = { ... }

		local multiWord = table.concat( words, ' ' )

		db:exec( "CREATE TABLE IF NOT EXISTS t1( id, name )" )

		local insertStmt = db:prepare "INSERT INTO t1 VALUES( 1, ? )"
		insertStmt:bind( 1, multiWord )
		insertStmt:runToCompletion()

		local searchSQL = string.format( [[SELECT id FROM t1 WHERE %s( name, ? )]],
										 operator )

		local searchFragments = {}
		for i = 1, #words do
			searchFragments[ i ] = searchSQL
		end

		local sql = table.concat( searchFragments, " INTERSECT " )

		local multiSelectStmt = roDb:prepare( sql )

		for i = 1, #words do
			multiSelectStmt:bind( i, words[ i ] )
		end

		local matches = multiSelectStmt:gatherArray()

		db:exec "DELETE FROM t1"

		return matches

	end

	rxTest:expectEqual( { 1 },
		doMultiWordSearch( "contains_word", "big", "box" ) )

	rxTest:expectEqual( { 1 },
		doMultiWordSearch( "contains_word", "wood", "duck" ) )

	rxTest:expectEqual( { 1 },
		doMultiWordSearch( "contains_word_starting_with", "big", "box" ) )

	rxTest:expectEqual( { 1 },
		doMultiWordSearch( "contains_word_starting_with", "wood", "duck" ) )

end

--------------------------------------------------------------------------------

return WFRxTestCase.wrapSuite( 'WFSQLiteReadOnlyDatabaseImpTest', WFSQLiteReadOnlyDatabaseImpTest )
