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

WFSQLiteDatabaseImpTest.lua

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

ADOBE CONFIDENTIAL
------------------
Copyright 2011 Adobe Systems Incorporated
All Rights Reserved.

NOTICE: All information contained herein is, and remain the property of
Adobe Systems Incorporated and its suppliers, if any. The intellectual and
technical concepts contained herein are proprietary to Adobe Systems
Incorporated and its suppliers and may be covered by U.S. and Foreign
Patents, patents in process, and are protected by trade secret or copyright
law.  Dissemination of this information or reproduction of this material is
strictly forbidden unless prior written permission is obtained from
Adobe Systems Incorporated.

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

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

	-- 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'
local WFPlatformInfo = require 'WFPlatformInfo'

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


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

-- @mark WFSQLiteDatabaseImpTest

local WFSQLiteDatabaseImpTest = {}

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

local function temporaryPathForDb( rxTest )

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

	local dbName = string.format( "%s%s", rxTest:testName(), KSIdGenerator.uuid() )

	local dbPath = KSPathUtils.child( dbFolder, dbName )

	dbPath = WFFileUtils.ensureFileNameIsUnique( dbPath )

	rxTest:finally( function()
		pcall( WFFileUtils.delete, dbPath )
		pcall( WFFileUtils.delete, dbFolder )
	end )

	return dbPath

end

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

function WFSQLiteDatabaseImpTest.rxTestToString( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	rxTest:expectEqual( [[WFSQLiteDatabaseImp( ":memory:" )]],
						tostring( db ) )

	db:close()

	rxTest:expectEqual( [[WFSQLiteDatabaseImp( ":memory:" CLOSED )]],
						tostring( db ) )

	db = nil

end


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

-- @mark GC and :close

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

function WFSQLiteDatabaseImpTest.rxTestGCWithoutClose( rxTest )

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

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'
	rxTest:expectEqual( type( db ), 'userdata' )

	db = nil

	collectgarbage()
	collectgarbage()
	collectgarbage()

end

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

-- @mark extended error codes

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

function WFSQLiteDatabaseImpTest.rxTestExtendedErrorCodes_constraint_notnull( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec( "CREATE TABLE t1( a, b NOT NULL )" )
	db:exec( "INSERT INTO t1 VALUES(1, 'foo')" )

	-- if extended error codes are on,
	-- this should throw extended error code 1299: NOT NULL constraint failed: t1.b
	-- otherwise if will throw error code 19: NOT NULL constraint failed: t1.b
	local _, err = pcall( function()
		db:exec( "INSERT INTO t1 VALUES(2, NULL)" )
	end )

	local errorCode1299 = err:find( "error code 1299" )

	rxTest:expectEqual( errorCode1299 ~= nil, true )


end

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

-- @mark prepared statements

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

function WFSQLiteDatabaseImpTest.rxTestPrepare_errorMultipleStatements( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	rxTest:expectError( function()
		db:prepare( "CREATE TABLE t1( a ); CREATE TABLE t2( b )" )
	end )

end

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

function WFSQLiteDatabaseImpTest.rxTestPrepare_errorSyntaxError( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

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

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_tostring( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

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

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

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

	stmt:finalize()

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

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_finalize_errorFinalizeTwice( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

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

	local stmt = db: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 )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_bind( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( seq, value )"

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

	local function insert( seq, value )
		insertStmt:bind( 1, seq )
		insertStmt:bind( 2, value )
		insertStmt:runToCompletion()
	end

	insert( 1, 42.44 )
	insert( 2, false )	-- booleans fold to int, so this will be 0
	insert( 3, true )	-- will be 1
	insert( 4, "aString" )
	insert( 5, nil )
	insert( 6, KSData.make "aBlob" )

	rxTest:expectError( function()
		insert( 7, function() end ) -- not allowed
	end )

	rxTest:expectError( function()
		insert( 8, insertStmt ) -- any user data other than KSData is not allowed
	end )

	insertStmt:finalize()

	local queryStmt = db:prepare "SELECT seq, value FROM foo ORDER BY seq"

	rxTest:expectEqual(
		{ 1, 42.44 },
		{ queryStmt:stepValues() } )

	rxTest:expectEqual(
		{ 2, 0 }, -- boolean folding to 0
		{ queryStmt:stepValues() } )

	rxTest:expectEqual(
		{ 3, 1 }, -- boolean folding to 1
		{ queryStmt:stepValues() } )

	rxTest:expectEqual(
		{ 4, "aString" },
		{ queryStmt:stepValues() } )

	rxTest:expectEqual(
		{ 5, nil },
		{ queryStmt:stepValues() } )

	local seq, value = queryStmt:stepValues()

	rxTest:expectEqual( 6, seq )
	rxTest:expectEqual( true, KSData.isData( value ) )
	rxTest:expectEqual( "aBlob", tostring( value ) )

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

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_bind_namedPlaceholder( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( seq, value )"

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

	local function insert( seq, value )
		insertStmt:bind( ":s", seq )
		insertStmt:bind( ":val", value )
		insertStmt:runToCompletion()
	end

	insert( 1, 42.44 )
	insert( 2, false )	-- booleans fold to int, so this will be 0
	insert( 3, true )	-- will be 1
	insert( 4, "aString" )
	insert( 5, nil )
	insert( 6, KSData.make "aBlob" )

	rxTest:expectError( function()
		insert( 7, function() end ) -- not allowed
	end )

	rxTest:expectError( function()
		insert( 8, insertStmt ) -- any user data other than KSData is not allowed
	end )

	insertStmt:finalize()

	local queryStmt = db:prepare "SELECT seq, value FROM foo ORDER BY seq"

	rxTest:expectEqual(
		{ 1, 42.44 },
		{ queryStmt:stepValues() } )

	rxTest:expectEqual(
		{ 2, 0 }, -- boolean folding to 0
		{ queryStmt:stepValues() } )

	rxTest:expectEqual(
		{ 3, 1 }, -- boolean folding to 1
		{ queryStmt:stepValues() } )

	rxTest:expectEqual(
		{ 4, "aString" },
		{ queryStmt:stepValues() } )

	rxTest:expectEqual(
		{ 5, nil },
		{ queryStmt:stepValues() } )

	local seq, value = queryStmt:stepValues()

	rxTest:expectEqual( 6, seq )
	rxTest:expectEqual( true, KSData.isData( value ) )
	rxTest:expectEqual( "aBlob", tostring( value ) )

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

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_bind_errorNoSuchPlaceholder( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( seq, value )"

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

	rxTest:expectError( function()
		insertStmt:bind( ":bogus", 42 )
	end )

	insertStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_bind_errorFinalized( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( seq, value )"

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

	insertStmt:finalize()

	rxTest:expectError( function()
		insertStmt:bind( 1, 42 )
	end )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_bind_errorTooManyArgs( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( seq, value )"

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

	rxTest:expectError( function()
		insertStmt:bind( 1, 2, 3 )
	end )

	insertStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_bindByPosition( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

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

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

	insertStmt:bindByPosition( 1, false, true, "aString" )
	insertStmt:runToCompletion()

	insertStmt:bindByPosition( 2, nil, 42, "some more data" )
	insertStmt:runToCompletion()

	rxTest:expectError( function()
		insertStmt:bindByPosition( 3, function() end, "blah", "bork" )
		insertStmt:runToCompletion()
	end )

	insertStmt:finalize()

	local queryStmt = db:prepare "SELECT a, b, c, d FROM foo ORDER BY a"

	rxTest:expectEqual(
		{ 1, 0, 1, "aString" },
		{ queryStmt:stepValues() } )

	rxTest:expectEqual(
		{ 2, nil, 42, "some more data" },
		{ queryStmt:stepValues() } )

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

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_step( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()

	insertStmt:finalize()

	local queryStmt = db: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() )

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_step_errorFinalized( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()

	insertStmt:finalize()

	local queryStmt = db: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 )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_reset( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()

	insertStmt:finalize()

	local queryStmt = db: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() )

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_runToCompletion_errorTooManyRows( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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

	insertStmt:finalize()

	local queryStmt = db:prepare "SELECT a FROM foo"

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

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_gatherArrayOfTables( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()

	insertStmt:finalize()

	local queryStmt = db: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() )

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_gatherArray( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()

	local queryStmt = db: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()

	insertStmt:finalize()

	queryStmt_bindByPosition( queryStmt, 1 )

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

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_gatherSet( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()

	insertStmt:finalize()

	local queryStmt = db:prepare "SELECT b, c FROM foo"

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

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_gatherMap( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()

	insertStmt:finalize()

	local queryStmt = db:prepare "SELECT b, c FROM foo ORDER BY a"

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

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_gatherArrayOfArray( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()

	local queryStmt = db: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 WFSQLiteDatabaseImpTest.rxTestStatement_getMethod( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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 )

	local queryStmt = db:prepare "SELECT a, b, c FROM foo WHERE a > ? ORDER BY a"

	local queryStmt_gatherArrayOfArray = insertStmt: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 WFSQLiteDatabaseImpTest.rxTestStatement_nativeEncoder( rxTest )

	local sampleEncoder = WFSQLiteDatabaseTestUtils.sampleEncoder

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()

	local stmt = db:prepare "SELECT * FROM foo"

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

	rxTest:expectEqual( 76, result )

end

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

-- @mark WFSQLiteDatabase carray extension

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

function WFSQLiteDatabaseImpTest.rxTestStatement_carray_emptyCarray( rxTest )
	
	local db = WFSQLiteDatabaseImp.makeDatabase( ':memory:' )

	db:exec( "CREATE TABLE foo( a, b )" )
	
	local array = {}
	
	local queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, ?, 'int32' ) ORDER BY b" )

	queryStmt:bindByPosition( array, #array )
	
	rxTest:expectEqual(
		{
		},
		queryStmt:gatherArrayOfArray() )
		
end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_carray_notIn_emptyCarray( rxTest )
	
	local db = WFSQLiteDatabaseImp.makeDatabase( ':memory:' )

	db:exec( "CREATE TABLE foo( a, b )" )
	
	db:exec( "INSERT INTO foo VALUES ( 10, 100 )" )
	db:exec( "INSERT INTO foo VALUES ( 20, 200 )" )
	db:exec( "INSERT INTO foo VALUES ( 30, 300 )" )
	
	local array = {}
	
	local queryStmt = db:prepare( "SELECT b FROM foo WHERE a not in CARRAY( ?, ?, 'int32' ) ORDER BY b" )

	queryStmt:bindByPosition( array, #array )
	
	rxTest:expectEqual(
		{
			{
				100,
			},
			{
				200,
			},
			{
				300,
			},
		},
		queryStmt:gatherArrayOfArray() )
		
end

----------------------------------------------------------------------
function WFSQLiteDatabaseImpTest.rxTestStatement_carray_int( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase( ':memory:' )

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

	db:exec( "INSERT INTO foo VALUES ( 10, 100 )" )
	db:exec( "INSERT INTO foo VALUES ( 20, 200 )" )
	db:exec( "INSERT INTO foo VALUES ( 30, 300 )" )

	local array = { 10, 20, 30, 40 }

    -- specify arraySize directly
	local queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, 4, 'int32' ) ORDER BY b" )

	queryStmt:bindByPosition( array )

	rxTest:expectEqual(
		{
			{
				100,
			},
			{
				200,
			},
			{
				300,
			},
		},
		queryStmt:gatherArrayOfArray() )

    -- specify arraySize per parameter
	queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, ?, 'int32' ) ORDER BY b" )

	queryStmt:bindByPosition( array, #array )

	rxTest:expectEqual(
		{
			{
				100,
			},
			{
				200,
			},
			{
				300,
			},
		},
		queryStmt:gatherArrayOfArray() )


    -- use int64
	queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, ?, 'int64' ) ORDER BY b" )

	queryStmt:bindByPosition( array, #array )

	rxTest:expectEqual(
		{
			{
				100,
			},
			{
				200,
			},
			{
				300,
			},
		},
		queryStmt:gatherArrayOfArray() )


	   -- use int64
	queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, ?, 'double' ) ORDER BY b" )

	queryStmt:bindByPosition( array, #array )

	rxTest:expectEqual(
		{
			{
				100,
			},
			{
				200,
			},
			{
				300,
			},
		},
		queryStmt:gatherArrayOfArray() )
end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_carray_double( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase( ':memory:' )

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


	db:exec( "INSERT INTO foo VALUES ( 3.1415 )" )
	db:exec( "INSERT INTO foo VALUES ( 8.15 )" )
	db:exec( "INSERT INTO foo VALUES ( -1.2345 )" )

	local array = { 3.1415, -1.2345 }

	local queryStmt = db:prepare( "SELECT a FROM foo WHERE a in CARRAY( ?, ?, 'double' ) ORDER BY a" )

	queryStmt:bindByPosition( array, #array )

	rxTest:expectEqual(
		{
			{
				-1.2345,
			},
			{
				3.1415,
			},
		},
		queryStmt:gatherArrayOfArray() )
end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_carray_string( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase( ':memory:' )

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

	db:exec( "INSERT INTO foo VALUES ( 'foo', 30 )" )
	db:exec( "INSERT INTO foo VALUES ( 'bar', 40 )" )
	db:exec( "INSERT INTO foo VALUES ( 'baz', 50 )" )

	local array = { "foo", "bar" }

	local queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, 2, 'char*' ) ORDER BY b" )

	queryStmt:bindByPosition( array )

	rxTest:expectEqual(
		{
			{
				30,
			},
			{
				40,
			},
		},
		queryStmt:gatherArrayOfArray() )

	queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, ?, 'char*' ) ORDER BY b DESC" )

	queryStmt:bindByPosition( array, #array )

	rxTest:expectEqual(
		{
			{
				40,
			},
			{
				30,
			},
		},
		queryStmt:gatherArrayOfArray() )


end

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

function WFSQLiteDatabaseImpTest.rxTestStatement_carray_errors( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase( ':memory:' )

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

	db:exec( "INSERT INTO foo VALUES ( 'foo', 30 )" )
	db:exec( "INSERT INTO foo VALUES ( 'bar', 40 )" )
	db:exec( "INSERT INTO foo VALUES ( 'baz', 50 )" )

	local array = { "foo", "bar" }

	-- carray default is 'int32' not 'strings'
	local queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, 2 )" )

	rxTest:expectError( function()
			queryStmt:bindByPosition( array )
		end )


	-- mix data types
	array = { 10, "bar" }

	queryStmt = db:prepare( "SELECT b FROM foo WHERE b in CARRAY( ?, 2, 'int32' )" )

	rxTest:expectError( function()
			queryStmt:bindByPosition( array )
		end )

	-- specify arraySize in sql string and get it wrong
	array = { 10, 20 }

	queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, 5, 'int32' )" )

	rxTest:expectError( function()
			queryStmt:bindByPosition( array )
		end )

	-- specify arraySize via bind parameter and get it wrong
	array = { 10, 20, 30, 40, 50 }

	queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, ?, 'int32' )" )

	rxTest:expectError( function()
			queryStmt:bindByPosition( array, #array + 2 )
		end )

	-- try to specify array type via parameter
	queryStmt = db:prepare( "SELECT b FROM foo WHERE a in CARRAY( ?, ?, ? )" )

	rxTest:expectError( function()
			queryStmt:bindByPosition( array, #array, "int32" )
		end )

end


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

-- @mark WFSQLiteDatabaseImp methods

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

function WFSQLiteDatabaseImpTest.rxTestExec_errorBadSQL( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	rxTest:expectError( function()
		db:exec "CREATE BOGUS THING t1( a )"
	end )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestChangeCounter( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( cc, blah )"

	rxTest:expectEqual( 0, db:getChangeCounter() )

	local insertStmt = db:prepare [[INSERT INTO foo VALUES( incrementChangeCounter(), 'blah' ) ]]

	rxTest:expectError( function()
		insertStmt:step()
		insertStmt:finalize()
	end )

	db:setChangeCounter( 17 )

	rxTest:expectEqual( 17, db:getChangeCounter() )

	db:exec( [[INSERT INTO foo VALUES( incrementChangeCounter(), 'blah' ) ]] )

	rxTest:expectEqual( 18, db:getChangeCounter() )

	db:exec( [[INSERT INTO foo VALUES( incrementChangeCounter(), 'blah2' ) ]] )

	rxTest:expectEqual( 19, db:getChangeCounter() )

	local queryStmt = db:prepare "SELECT cc, blah FROM foo ORDER BY cc"

	rxTest:expectEqual(
		{
			{ 17, "blah" },
			{ 18, "blah2" },
		},
		queryStmt:gatherArrayOfArray() )

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestGetAffectedRowCount( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( a )"

	db:exec "INSERT INTO foo VALUES( 1 )"
	db:exec "INSERT INTO foo VALUES( 2 )"
	db:exec "INSERT INTO foo VALUES( 3 )"
	db:exec "INSERT INTO foo VALUES( 4 )"
	db:exec "INSERT INTO foo VALUES( 5 )"
	db:exec "INSERT INTO foo VALUES( 6 )"

	db:exec "DELETE FROM foo WHERE a > 3"

	rxTest:expectEqual( 3, db:getAffectedRowCount() )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestGetLastInsertRowID( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( a INTEGER PRIMARY KEY, b )"

	db:exec "INSERT INTO foo VALUES( 42, 'blah' )"
	db:exec "INSERT INTO foo VALUES( 44, 'boof' )"

	rxTest:expectEqual( 44, db:getLastInsertRowID() )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestGetAutoCommit( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( a INTEGER PRIMARY KEY, b )"

	rxTest:expectEqual( true, db:getAutoCommit() )

	db:exec "BEGIN TRANSACTION"

	rxTest:expectEqual( false, db:getAutoCommit() )

	db:exec "ROLLBACK TRANSACTION"

	rxTest:expectEqual( true, db:getAutoCommit() )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestSetChunkSize( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase( temporaryPathForDb( rxTest ) )

	rxTest:expectError( function()
		db:setChunkSize( "notANumber" )
	end )

	rxTest:expectError( function()
		db:setChunkSize( 1.23 ) -- floats not allowed
	end )

	rxTest:expectError( function()
		db:setChunkSize( 0 ) -- too small
	end )

	rxTest:expectError( function()
		db:setChunkSize( 6 * 1024 * 1024 ) -- too big
	end )

	db:setChunkSize( 1024 * 1024 ) -- just right

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestSetProgressCallbackThreadLifetime()

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( a )"
	local f = coroutine.wrap( function( db )
		db:setProgressCallback( function()end, 10 )
	end )
	f(db)
	f = nil
	collectgarbage 'collect'
	collectgarbage 'collect'
	collectgarbage 'collect'
	db:setProgressCallback( nil )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestSetProgressCallback( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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

	db:setProgressCallback( progressCallbackSpy, 10 )

	-- Test the cancel case.

	local queryStmt = db: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

	db:setProgressCallback( nil ) -- clears the hook

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

	rxTest:expectEqual( 0, progressCallbackCalledCount )

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestSetCommitHook( rxTest )

	local tempDbPath = temporaryPathForDb( rxTest )
	local db = WFSQLiteDatabaseImp.makeDatabase( tempDbPath )

	local commitHookCalledCount = 0
	local commitHookCalledArgs -- will fill in with most recent args

	local function commitHookSpy( ... )
		commitHookCalledCount = commitHookCalledCount + 1
		commitHookCalledArgs = { ..., n = select( '#', ... ) }
	end

	db:exec "CREATE TABLE foo( a )"

	db:setCommitHook( commitHookSpy )

	db:exec "BEGIN TRANSACTION"
	db:exec "INSERT INTO foo VALUES( 42 )"

	rxTest:expectEqual( 0, commitHookCalledCount )

	db:exec "COMMIT"

	rxTest:expectEqual( 1, commitHookCalledCount )
	rxTest:expectEqual( { tempDbPath, n = 1 }, commitHookCalledArgs )

	db:setCommitHook( nil )

	db:exec "BEGIN TRANSACTION"
	db:exec "INSERT INTO foo VALUES( 44 )"

	rxTest:expectEqual( 1, commitHookCalledCount )

	db:exec "COMMIT"

	rxTest:expectEqual( 1, commitHookCalledCount )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestSetCommitHook_cancel( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	local commitHookCalledCount = 0

	local function commitHook( ... )
		commitHookCalledCount = commitHookCalledCount + 1
		return false
	end

	db:exec "CREATE TABLE foo( a )"

	db:setCommitHook( commitHook )

	db:exec "BEGIN TRANSACTION"
	db:exec "INSERT INTO foo VALUES( 42 )"

	rxTest:expectEqual( 0, commitHookCalledCount )

	rxTest:expectError( function()
		db:exec "COMMIT"
	end )

	rxTest:expectEqual( 1, commitHookCalledCount )

	local countStmt = db:prepare "SELECT count(*) FROM foo"

	rxTest:expectEqual(
		{
			["count(*)"] = 0,
		},
		countStmt:runToCompletion() )

	countStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestSetCommitHook_proceed( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	local commitHookCalledCount = 0

	local function commitHook( ... )
		commitHookCalledCount = commitHookCalledCount + 1
		return true
	end

	db:exec "CREATE TABLE foo( a )"

	db:setCommitHook( commitHook )

	db:exec "BEGIN TRANSACTION"
	db:exec "INSERT INTO foo VALUES( 42 )"

	rxTest:expectEqual( 0, commitHookCalledCount )

	db:exec "COMMIT"

	rxTest:expectEqual( 1, commitHookCalledCount )

	local countStmt = db:prepare "SELECT count(*) FROM foo"

	rxTest:expectEqual(
		{
			["count(*)"] = 1,
		},
		countStmt:runToCompletion() )

	countStmt:finalize()

	db:close()

end

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

-- FIXME [jandrew 4/29/2015]: This test fails on Android
if not WFPlatformInfo.isAndroid then

function WFSQLiteDatabaseImpTest.rxTestCustomStringOrder( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( a )"

	db:registerCustomStringOrder { 'red', 'yellow', 'green', 'blue', 'purple' }

	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' )"

	local queryStmt = db: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() )

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

	queryStmt:reset()

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

	queryStmt:finalize()

	db:close()

end

end

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

function WFSQLiteDatabaseImpTest.rxTestSetFatalErrorHook( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

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

	rxTest:expectError( function()

		db:setFatalErrorHook()

	end )

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

	rxTest:expectError( function()

		db:setFatalErrorHook( "not allowed" )

	end )

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

	rxTest:expectError( function()

		db:setFatalErrorHook( {} )

	end )

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

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

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

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

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestCorruptDbErrorV1AndV2( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':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.
	db:installErrorHandlers( {
		
		raiseFatalCorruptionError = raiseFatalCorruptionError,

	} )

	db:setFatalErrorHook( fatalErrorHook )

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

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

	rxTest:expectEqual( sawFatalErrorHook, nil )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestFatalErrorHookCorrupt( rxTest )

	local myDb = WFSQLiteDatabaseImp.makeDatabase ':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.
	myDb:setFatalErrorHook( fatalErrorHook )

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

	-- verify the v2 was called

	rxTest:expectEqual( sawFatalErrorHook, true )

	myDb:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestFatalErrorHookIOError( rxTest )

	local myDb = WFSQLiteDatabaseImp.makeDatabase ':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

	myDb:setFatalErrorHook( fatalErrorHook )

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

	-- verify the v2 was called

	rxTest:expectEqual( sawFatalErrorHook, true )

	myDb:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestTransientIoError( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':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()
		db:exec "SELECT test_transient_io_error()"
	end )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestErrorHandlers( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':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'

	db:installErrorHandlers( errorHandlers )

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

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

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestCorruptionHandlerV2( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':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'

	db:installErrorHandlers( errorHandlers )

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

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

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestErrorHandlers_fallbackNoHandler( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:installErrorHandlers( {} )

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

	db:close()
end

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

function WFSQLiteDatabaseImpTest.rxTestOnlineBackup( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( a )"
	db:exec "INSERT INTO foo VALUES( 42 )"
	db:exec "INSERT INTO foo VALUES( 44 )"

	-- Create more than 50 DB pages so we can see progress func run.

	for i = 1, 60 do
		db:exec( "CREATE TABLE page" .. tostring( i ) .. "( a )" )
		db:exec( "INSERT INTO page" .. tostring( i ) .. " VALUES( 1 )" )
	end

	local targetDb = WFSQLiteDatabaseImp.makeDatabase( temporaryPathForDb( rxTest ) )

	local progressFuncCalled = false
	local progressFuncCalledWithInvalidNumber = 0
	local progressFuncCalledWithExtraArgs = false

	local function progressFunc( percentDone, ... )

		progressFuncCalled = true

		if type( percentDone ) ~= 'number' or percentDone < 0 or percentDone > 100 then
			progressFuncCalledWithInvalidNumber = percentDone
		end

		if select( '#', ... ) > 0 then
			progressFuncCalledWithExtraArgs = { ..., n = select( '#', ... ) }
		end

		return true -- continue

	end

	db:onlineBackup( targetDb, progressFunc )

	rxTest:expectEqual( true, progressFuncCalled )
	rxTest:expectEqual( 0, progressFuncCalledWithInvalidNumber )
	rxTest:expectEqual( false, progressFuncCalledWithExtraArgs )

	local queryStmt = targetDb:prepare "SELECT * FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			{ 42 },
			{ 44 },
		},
		queryStmt:gatherArrayOfArray() )

	queryStmt:finalize()

	targetDb:close()

	-- Make sure source DB is unaffected.

	queryStmt = db:prepare "SELECT * FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			{ 42 },
			{ 44 },
		},
		queryStmt:gatherArrayOfArray() )

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestOnlineBackup_noProgressFunc( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( a )"
	db:exec "INSERT INTO foo VALUES( 42 )"
	db:exec "INSERT INTO foo VALUES( 44 )"

	-- Create more than 50 DB pages so we can see progress func run.

	for i = 1, 60 do
		db:exec( "CREATE TABLE page" .. tostring( i ) .. "( a )" )
		db:exec( "INSERT INTO page" .. tostring( i ) .. " VALUES( 1 )" )
	end

	local targetDb = WFSQLiteDatabaseImp.makeDatabase( temporaryPathForDb( rxTest ) )

	db:onlineBackup( targetDb )

	local queryStmt = targetDb:prepare "SELECT * FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			{ 42 },
			{ 44 },
		},
		queryStmt:gatherArrayOfArray() )

	queryStmt:finalize()

	targetDb:close()

	-- Make sure source DB is unaffected.

	queryStmt = db:prepare "SELECT * FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			{ 42 },
			{ 44 },
		},
		queryStmt:gatherArrayOfArray() )

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestOnlineBackup_cancel( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( a )"
	db:exec "INSERT INTO foo VALUES( 42 )"
	db:exec "INSERT INTO foo VALUES( 44 )"

	-- Create more than 50 DB pages so we can see progress func run.

	for i = 1, 100 do
		db:exec( "CREATE TABLE page" .. tostring( i ) .. "( a )" )
		db:exec( "INSERT INTO page" .. tostring( i ) .. " VALUES( 1 )" )
	end

	local targetDb = WFSQLiteDatabaseImp.makeDatabase( temporaryPathForDb( rxTest ) )

	local progressFuncCalled = false

	local function progressFunc( percentDone, ... )

		progressFuncCalled = true
		return false -- stop

	end

	db:onlineBackup( targetDb, progressFunc )

	rxTest:expectEqual( true, progressFuncCalled )

	-- Query should fail: No such table.

	rxTest:expectError( function()
		targetDb:prepare "SELECT * FROM foo ORDER BY a"
	end )

	targetDb:close()

	-- Make sure source DB is unaffected.

	local queryStmt = db:prepare "SELECT * FROM foo ORDER BY a"

	rxTest:expectEqual(
		{
			{ 42 },
			{ 44 },
		},
		queryStmt:gatherArrayOfArray() )

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestOnlineBackup_errorTargetClosed( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	db:exec "CREATE TABLE foo( a )"
	db:exec "INSERT INTO foo VALUES( 42 )"
	db:exec "INSERT INTO foo VALUES( 44 )"

	-- Create more than 50 DB pages so we can see progress func run.

	for i = 1, 60 do
		db:exec( "CREATE TABLE page" .. tostring( i ) .. "( a )" )
		db:exec( "INSERT INTO page" .. tostring( i ) .. " VALUES( 1 )" )
	end

	local targetDb = WFSQLiteDatabaseImp.makeDatabase( temporaryPathForDb( rxTest ) )
	targetDb:close()

	rxTest:expectError( function()
		db:onlineBackup( targetDb )
	end )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestReleaseMemory( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':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 ) )

	db:close()

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()

	stmt:finalize()

	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 WFSQLiteDatabaseImpTest.rxTestContainsWord( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	for i, testData in ipairs( happyPathTestData ) do

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

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

	end

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestContainsWord_errorInvalidData( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':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" )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestContainsWord_usingTable( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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() )

	queryStmt:finalize()

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestContainsWord_bug2742430( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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 function doQuery( operand, str )

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

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

		local result = stmt:gatherArray()

		stmt:finalize()

		return result
	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" ) )

	db:close()

end

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

function WFSQLiteDatabaseImpTest.rxTestContainsWord_bug2898598( rxTest )

	local db = WFSQLiteDatabaseImp.makeDatabase ':memory:'

	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()
		insertStmt:finalize()

		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 = db:prepare( sql )

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

		local matches = multiSelectStmt:gatherArray()

		multiSelectStmt:finalize()

		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" ) )

	db:close()

end

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

return WFRxTestCase.wrapSuite( 'WFSQLiteDatabaseImpTest', WFSQLiteDatabaseImpTest )
