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

WFSQLiteDatabaseTest.lua

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

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

NOTICE: All information contained herein is, and remains 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 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 Systems Incorporated.

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

	-- RxLua
local Rx = require 'Rx'
local Observable = Rx.Observable

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

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

	-- WF.core.utils
local KSIdGenerator = require 'KSIdGenerator'
local KSTestUtils = require 'KSTestUtils'
local KSTableUtils = require 'KSTableUtils'
local WFFunctionUtils = require 'WFFunctionUtils'

	-- WF.sqlite
local WFSQLiteDatabase = require 'WFSQLiteDatabase'
local WFSQLiteCorruptionLogger = require 'WFSQLiteCorruptionLogger'
local WFSQLiteDatabaseSchema = require 'WFSQLiteDatabaseSchema'
local WFSQLiteDatabaseImp = require 'WFSQLiteDatabaseImp'
local WFSQLiteDatabaseTestUtils = require 'WFSQLiteDatabaseTestUtils'


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

-- @mark spy object on WFSQLiteDatabaseImp

local databaseImpSpy = {

	_nativeDb = false,
		-- (WFSQLiteDatabaseImp)

	_calls = false,
		-- (array of string) calls to WFSQLiteDatabaseImp so far

	_path = false,
		-- (string) DB path

	_closed = false,
		-- (boolean) true after :close called

}

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

function databaseImpSpy:__init( path )

	self._nativeDb = WFSQLiteDatabaseImp.makeDatabase( path )

	self._calls = {}

	self._path = path

end

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

function databaseImpSpy:close()

	self._closed = true

	return self._nativeDb:close()

end

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

function databaseImpSpy:assertClosed()

	if not self._closed then
		error( "Database should have been closed by now, but it wasn't", 3 )
	end

end

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

function databaseImpSpy:logCall( ... )

	self._calls[ #self._calls + 1 ] = { ... }

end

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

function databaseImpSpy:calls()

	return Observable.from( self._calls ):unpack()

end

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

function databaseImpSpy:pragmas()

	return self:calls()
		:filter( function( call, sql )
			return call == 'prepare'
				and type( sql ) == 'string'
				and sql:match( "^PRAGMA " )
		end )
		:map( function( _, sql )
			return sql
		end )

end

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

function databaseImpSpy:prepare( ... )

	self:logCall( 'prepare', ... )

	return self._nativeDb:prepare( ... )

end

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

databaseImpSpy = Rx.Class 'databaseImpSpy'( databaseImpSpy )


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

-- @mark WFSQLiteDatabaseTest suite

local WFSQLiteDatabaseTest = {}

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

local function temporaryPathForDb( rxTest, testName )

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

	WFTestFileUtils.addTestFolder( dbFolder )

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

	local dbPath = KSPathUtils.child( dbFolder, dbName )

	dbPath = WFFileUtils.ensureFileNameIsUnique( dbPath )

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

	debug.print( "temporaryPathForDb generated path ", dbPath )

	return dbPath

end

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

local function tablesFromDb( db )

	return db:gatherArrayOfArray
		[[SELECT sql FROM sqlite_master
			WHERE type == 'table'
				AND tbl_name != 'schemaVersion'
			ORDER BY tbl_name]]

end

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

local function indexesFromDb( db )

	return db:gatherArrayOfArray
		[[SELECT sql FROM sqlite_master
			WHERE type == 'index'
				AND tbl_name != 'schemaVersion'
				AND sql NOT NULL
			ORDER BY name]]

end


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

-- @mark :init and schema migration

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

function WFSQLiteDatabaseTest.rxTestInit_new( rxTest )

	-- Simplest case of :init (new database with one table).

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]

	schema:addIndex
		[[CREATE INDEX MyTest_otherField ON MyTest( otherField )]]

	local spy = databaseImpSpy( ':memory:' )

	local db = WFSQLiteDatabase( spy, schema )

	rxTest:expectEqual(
		{
			{
				"CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )"
			},
		},
		tablesFromDb( db ) )

	rxTest:expectEqual(
		{
			{
				"CREATE INDEX MyTest_otherField ON MyTest( otherField )",
			},
		},
		indexesFromDb( db ) )

	rxTest:expectEqual( "WFSQLiteDatabase( :memory: )", tostring( db ) )

end

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

function WFSQLiteDatabaseTest.rxTestInit_logLevel( rxTest )

	-- Simplest case of :init (new database with one table).

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]

	schema:addIndex
		[[CREATE INDEX MyTest_otherField ON MyTest( otherField )]]

	local spy = databaseImpSpy( ':memory:' )

	local db = WFSQLiteDatabase( spy, schema, { logLevel = 'debug' } )

	rxTest:expectEqual(
		{
			{
				"CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )"
			},
		},
		tablesFromDb( db ) )

	rxTest:expectEqual(
		{
			{
				"CREATE INDEX MyTest_otherField ON MyTest( otherField )",
			},
		},
		indexesFromDb( db ) )

end

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

function WFSQLiteDatabaseTest.rxTestInit_tempPath( rxTest )

	-- Simplest case of :init (new database with one table).

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]

	schema:addIndex
		[[CREATE INDEX MyTest_otherField ON MyTest( otherField )]]

	local db = WFSQLiteDatabase( ':temp:', schema )

	rxTest:expectEqual(
		{
			{
				"CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )"
			},
		},
		tablesFromDb( db ) )

	rxTest:expectEqual(
		{
			{
				"CREATE INDEX MyTest_otherField ON MyTest( otherField )",
			},
		},
		indexesFromDb( db ) )

	rxTest:expectEqual( '', db:path() )

end

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

function WFSQLiteDatabaseTest.rxTestInit_useWAL( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:useWAL()

	schema:addTable
		[[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]

	local spy = databaseImpSpy( temporaryPathForDb( rxTest, 'init_useWAL' ) )

	local db = WFSQLiteDatabase( spy, schema )

	rxTest:expectSingleValue( spy:pragmas(), "PRAGMA journal_mode = WAL" )

	db:close()

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestInit_pageSize( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:setPageSize( 4096 )

	schema:addTable
		[[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]

	local spy = databaseImpSpy( temporaryPathForDb( rxTest, 'init_pageSize' ) )

	local db = WFSQLiteDatabase( spy, schema )

	rxTest:expectSingleValue( spy:pragmas(), "PRAGMA page_size = 4096" )

	db:close()

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestInit_synchronous( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:setSynchronous( 'full' )

	schema:addTable
		[[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]
--	CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )


	local spy = databaseImpSpy( temporaryPathForDb( rxTest,  'init_synchronous' ) )

	local db = WFSQLiteDatabase( spy, schema )

	rxTest:expectSingleValue( spy:pragmas(), "PRAGMA synchronous = full" )

	db:close()

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaDropTable( rxTest )
	local db
	local tempPath = temporaryPathForDb( rxTest, 'schema_dropTable' )

	rxTest:finally( function()
		if db then
			db:close()
		end
	end )
	
	local schema = WFSQLiteDatabaseSchema()
	
	local tableStmt = [[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL)]]
	schema:addTable( tableStmt )
	
	db = WFSQLiteDatabase( tempPath, schema )

	rxTest:expectEqual( { { tableStmt } }, tablesFromDb( db ) )
	
	db:close()
	
	local schema2 = WFSQLiteDatabaseSchema()
	
	schema2:dropTable( 'MyTest' )
	
	db = WFSQLiteDatabase( tempPath, schema2 )
	
	rxTest:expectEqual( {}, tablesFromDb( db ) )
	
	db:close()
	
	-- make sure dropping a table that doesn't exist is okay
	local schema3 = WFSQLiteDatabaseSchema()
	
	schema3:dropTable( 'MyTestNeverExisted' )
	
	db = WFSQLiteDatabase( tempPath, schema3 )
	
	rxTest:expectEqual( {}, tablesFromDb( db ) )
	
	db:close()
	db = nil
	
end

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

function WFSQLiteDatabaseTest.rxTestSchemaDropIndexes( rxTest )
	
	local db
	local tempPath = temporaryPathForDb( rxTest, 'schema_dropIndex' )

	rxTest:finally( function()
		if db then
			db:close()
		end
	end )
	
	local schema = WFSQLiteDatabaseSchema()
	
	schema:addTable
		[[CREATE TABLE t1( blah, blah2 )]]

	schema:addIndex
		[[CREATE INDEX t1_blah ON t1( blah )]]
	
	db = WFSQLiteDatabase( tempPath, schema )
	
	rxTest:expectEqual( { { [[CREATE TABLE t1( blah, blah2 )]] } }, tablesFromDb( db ) )
	rxTest:expectEqual( { { [[CREATE INDEX t1_blah ON t1( blah )]] } }, indexesFromDb( db ) )
	
	db:close()
	
	-- make sure if we add a different index and remove the old one that the indexes are 
	-- updated appropriately
	local schema2 = WFSQLiteDatabaseSchema()
	
	schema2:addTable
		[[CREATE TABLE t1( blah, blah2 )]]
	
	schema2:addIndex
		[[CREATE INDEX t1_blah2 ON t1( blah2 )]]
	
	db = WFSQLiteDatabase( tempPath, schema2 )
	
	rxTest:expectEqual( { { [[CREATE TABLE t1( blah, blah2 )]] } }, tablesFromDb( db ) )
	rxTest:expectEqual( { { [[CREATE INDEX t1_blah2 ON t1( blah2 )]] } }, indexesFromDb( db ) )
	
	db:close()
	
	-- make sure if we don't specify any indexes, they also get removed
	local schema3 = WFSQLiteDatabaseSchema()
	schema3:addTable
		[[CREATE TABLE t1( blah, blah2 )]]
		
	db = WFSQLiteDatabase( tempPath, schema3 )
	
	rxTest:expectEqual( { { [[CREATE TABLE t1( blah, blah2 )]] } }, tablesFromDb( db ) )
	rxTest:expectEqual( { }, indexesFromDb( db ) )
	
	db:close()
	db = nil
	
end

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

function WFSQLiteDatabaseTest.rxTestSchemaDropIndexes_Unowned( rxTest )
	
	-- We should not drop indexes from the current database if the table
	-- that the index belongs to is also not in the schema. These should
	-- not be considered owned by the current WFSQLiteDatabase instance
	
	local db
	local tempPath = temporaryPathForDb( rxTest, 'schema_dropIndex_unowned' )

	rxTest:finally( function()
		if db then
			db:close()
		end
	end )
	
	local schema = WFSQLiteDatabaseSchema()
	
	schema:addTable
		[[CREATE TABLE t1( blah )]]

	schema:addIndex
		[[CREATE INDEX t1_blah ON t1( blah )]]
	
	db = WFSQLiteDatabase( tempPath, schema )
	
	rxTest:expectEqual( { { [[CREATE TABLE t1( blah )]] } }, tablesFromDb( db ) )
	rxTest:expectEqual( { { [[CREATE INDEX t1_blah ON t1( blah )]] } }, indexesFromDb( db ) )
	
	db:close()
	
	-- open the same db with a different schema, nothing should be removed, just added
	local schema2 = WFSQLiteDatabaseSchema()
	
	schema2:addTable
		[[CREATE TABLE t2( bleah )]]
	
	schema2:addIndex
		[[CREATE INDEX t2_bleah ON t2( bleah )]]
	
	db = WFSQLiteDatabase( tempPath, schema2 )
	
	rxTest:expectEqual( { { [[CREATE TABLE t1( blah )]] },
						  { [[CREATE TABLE t2( bleah )]] } }, tablesFromDb( db ) )
						  
	rxTest:expectEqual( { { [[CREATE INDEX t1_blah ON t1( blah )]] },
						  { [[CREATE INDEX t2_bleah ON t2( bleah )]] } }, indexesFromDb( db ) )
	
	db:close()
	
	-- open again with slightly different schema, remove only indexes owned by schema
	local schema3 = WFSQLiteDatabaseSchema()
	schema3:addTable
		[[CREATE TABLE t2( bleah )]]
		
	db = WFSQLiteDatabase( tempPath, schema3 )
	
	rxTest:expectEqual( { { [[CREATE TABLE t1( blah )]] },
						  { [[CREATE TABLE t2( bleah )]] } }, tablesFromDb( db ) )
						  
	rxTest:expectEqual( { { [[CREATE INDEX t1_blah ON t1( blah )]] } }, indexesFromDb( db ) ) 
	
	db:close()
	db = nil
	
end


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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_newTable( rxTest )

	local schema1 = WFSQLiteDatabaseSchema( '1.0' )

	local db1, db2
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_newTable' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
		if db2 then
			db2:close()
		end
	end )

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual( {}, tablesFromDb( db1 ) )

	db1:close()

	local schema2 = WFSQLiteDatabaseSchema( '1.0' )

	schema2:addTable
		[[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]

	local spy = databaseImpSpy( tempPath )

	db2 = WFSQLiteDatabase( spy, schema2 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]],
			},
		},
		tablesFromDb( db2 ) )

	db2:close()

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_tableExists( rxTest )

	local schema1 = WFSQLiteDatabaseSchema()
	
	local db1, db2
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_tableExists' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
		if db2 then
			db2:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				"CREATE TABLE t1( blah )",
			},
		}, tablesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()

	local schema2 = WFSQLiteDatabaseSchema()

	schema2:addTable
		[[CREATE TABLE t1( blah )]]

	local spy = databaseImpSpy( tempPath )

	db2 = WFSQLiteDatabase( spy, schema2 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		},
		tablesFromDb( db2 ) )

	rxTest:expectEqual(
		{
			{ "blah" },
		},
		db2:gatherArrayOfArray [[SELECT blah FROM t1]] )

	db2:close()

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_conflictFail( rxTest )

	local schema1 = WFSQLiteDatabaseSchema()
	
	local db1
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_conflictFail' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		}, tablesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()

	local schema2 = WFSQLiteDatabaseSchema()

	schema2:addTable
		[[CREATE TABLE t1( blah, bork )]]

	local spy = databaseImpSpy( tempPath )

	rxTest:expectError( function()

		return WFSQLiteDatabase( spy, schema2 )

	end )

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_conflictReplace( rxTest )

	local schema1 = WFSQLiteDatabaseSchema()

	local db1, db2
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_conflictReplace' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
		if db2 then
			db2:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		}, tablesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()
	db1 = nil

	local schema2 = WFSQLiteDatabaseSchema()

	schema2:addTable
		[[CREATE TABLE t1( blah, bork )]]
		:onConflict( 'replace' )

	local spy = databaseImpSpy( tempPath )

	db2 = WFSQLiteDatabase( spy, schema2 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah, bork )]],
			},
		},
		tablesFromDb( db2 ) )

	rxTest:expectEqual( {},
		db2:gatherArrayOfArray [[SELECT blah FROM t1]] )

	db2:close()
	db2 = nil

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_conflictAlter( rxTest )

	local schema1 = WFSQLiteDatabaseSchema()

	local db1, db2, db3
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_conflictAlter' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
		if db2 then
			db2:close()
		end
		if db3 then
			db3:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		}, tablesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()
	db1 = nil

	local schema2 = WFSQLiteDatabaseSchema()

	schema2:addTable
		[[CREATE TABLE t1( blah, bork )]]
		:onConflict( 'alter', { [[ ALTER TABLE t1 ADD COLUMN bork ]] } )

	local spy = databaseImpSpy( tempPath )

	db2 = WFSQLiteDatabase( spy, schema2 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah , bork)]],
			},
		},
		tablesFromDb( db2 ) )

	rxTest:expectEqual( { { "blah" } },
		db2:gatherArrayOfArray [[SELECT blah FROM t1]] )

	db2:close()
	db2 = nil

	spy:assertClosed()

	local schema3 = WFSQLiteDatabaseSchema()

	schema3:addTable
		[[CREATE TABLE t1( blah, bork, bark )]]
		:onConflict( 'alter', {
			[[ ALTER TABLE t1 ADD COLUMN bork ]],
			[[ ALTER TABLE t1 ADD COLUMN bark ]]
		} )

	local spy3 = databaseImpSpy( tempPath )

	db3 = WFSQLiteDatabase( spy3, schema3 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah , bork, bark)]],
			},
		},
		tablesFromDb( db3 ) )

	rxTest:expectEqual( { { "blah" } },
		db3:gatherArrayOfArray [[SELECT blah FROM t1]] )

	db3:close()
	db3 = nil

	spy3:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_indexExists( rxTest )

	local schema1 = WFSQLiteDatabaseSchema()
	
	local db1, db2
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_indexExists' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
		if db2 then
			db2:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	schema1:addIndex
		[[CREATE INDEX t1_blah ON t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				"CREATE TABLE t1( blah )",
			},
		}, tablesFromDb( db1 ) )

	rxTest:expectEqual(
		{
			{
				"CREATE INDEX t1_blah ON t1( blah )",
			},
		}, indexesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()

	local schema2 = WFSQLiteDatabaseSchema()

	schema2:addTable
		[[CREATE TABLE t1( blah )]]

	schema2:addIndex
		[[CREATE INDEX t1_blah ON t1( blah )]]

	local spy = databaseImpSpy( tempPath )

	db2 = WFSQLiteDatabase( spy, schema2 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		},
		tablesFromDb( db2 ) )

	rxTest:expectEqual(
		{
			{
				"CREATE INDEX t1_blah ON t1( blah )",
			},
		}, indexesFromDb( db2 ) )

	rxTest:expectEqual(
		{
			{ "blah" },
		},
		db2:gatherArrayOfArray [[SELECT blah FROM t1]] )

	db2:close()

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_indexExistsReplace( rxTest )

	local schema1 = WFSQLiteDatabaseSchema()
	
	local db1, db2
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_indexExistsReplace' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
		if db2 then
			db2:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	schema1:addIndex
		[[CREATE INDEX t1_blah ON t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				"CREATE TABLE t1( blah )",
			},
		}, tablesFromDb( db1 ) )

	rxTest:expectEqual(
		{
			{
				"CREATE INDEX t1_blah ON t1( blah )",
			},
		}, indexesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()

	local schema2 = WFSQLiteDatabaseSchema()

	schema2:addTable
		[[CREATE TABLE t1( blah )]]

	schema2:addIndex
		[[CREATE INDEX t1_blah ON t1( blah DESC )]]

	local spy = databaseImpSpy( tempPath )

	db2 = WFSQLiteDatabase( spy, schema2 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		},
		tablesFromDb( db2 ) )

	rxTest:expectEqual(
		{
			{
				"CREATE INDEX t1_blah ON t1( blah DESC )",
			},
		}, indexesFromDb( db2 ) )

	rxTest:expectEqual(
		{
			{ "blah" },
		},
		db2:gatherArrayOfArray [[SELECT blah FROM t1]] )

	db2:close()

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_dbTooNew( rxTest )

	local schema1 = WFSQLiteDatabaseSchema( '4' )

	local db1
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_dbTooNew' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		}, tablesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()

	local schema2 = WFSQLiteDatabaseSchema( '2' )

	schema2:addTable
		[[CREATE TABLE t1( blah )]]

	local spy = databaseImpSpy( tempPath )

	rxTest:expectSpecificError(
		"WFSQLiteDatabase: database format is newer than current software :: Error Code: STANDARD_ERROR",
		function()

			return WFSQLiteDatabase( spy, schema2 )

		end )

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_dbTooOldCantMigrate( rxTest )

	local schema1 = WFSQLiteDatabaseSchema( '5' )
	
	local db1
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_dbTooOldCantMigrate' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		}, tablesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()

	local schema2 = WFSQLiteDatabaseSchema( '2', '1' )

	schema2:addTable
		[[CREATE TABLE t1( blah )]]

	local spy = databaseImpSpy( tempPath )

	rxTest:expectSpecificError(
		"WFSQLiteDatabase: database format is newer than current software :: Error Code: STANDARD_ERROR",
		function()

			return WFSQLiteDatabase( spy, schema2 )

		end )

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_dbSchemaConflict( rxTest )

	local schema1 = WFSQLiteDatabaseSchema( '1' )
	
	local db1
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_dbSchemaConflict' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		}, tablesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()

	local schema2 = WFSQLiteDatabaseSchema( '1' )

	schema2:addTable
		[[CREATE TABLE t1( blah, foo )]]

	local spy = databaseImpSpy( tempPath )

	rxTest:expectSpecificError(
[[WFSQLiteDatabase: conflicting definition for table t1

CREATETABLEt1(blah) :: Error Code: CONFLICTING_SCHEMA]],
		function()

			return WFSQLiteDatabase( spy, schema2 )

		end )

	spy:assertClosed()

end

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

function WFSQLiteDatabaseTest.rxTestSchemaMigration_dbBackwardCompatible( rxTest )

	local schema1 = WFSQLiteDatabaseSchema( '4', '2' )

	local db1, db2
	local tempPath = temporaryPathForDb( rxTest, 'schemaMigration_dbBackwardCompatible' )

	rxTest:finally( function()
		if db1 then
			db1:close()
		end
		if db2 then
			db2:close()
		end
	end )

	schema1:addTable
		[[CREATE TABLE t1( blah )]]

	db1 = WFSQLiteDatabase( tempPath, schema1 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		}, tablesFromDb( db1 ) )

	db1:execOnce( [[INSERT INTO t1 VALUES( "blah" )]] )

	db1:close()

	local schema2 = WFSQLiteDatabaseSchema( '2' )

	schema2:addTable
		[[CREATE TABLE t1( blah )]]

	local spy = databaseImpSpy( tempPath )

	db2 = WFSQLiteDatabase( spy, schema2 )

	rxTest:expectEqual(
		{
			{
				[[CREATE TABLE t1( blah )]],
			},
		},
		tablesFromDb( db2 ) )

	rxTest:expectEqual(
		{
			{ "blah" },
		},
		db2:gatherArrayOfArray [[SELECT blah FROM t1]] )

	db2:close()

	spy:assertClosed()

end


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

-- @mark query runners

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

function WFSQLiteDatabaseTest.rxTestExecOnce( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( blah, bar )]]

	local spy = databaseImpSpy( ':memory:' )

	local db = WFSQLiteDatabase( spy, schema )

	rxTest:expectEqual(
		{
		},
		{ db:execOnce( [[INSERT INTO foo VALUES( 'blah', 'bar' )]] ) } )

	db:execOnce( [[INSERT INTO foo VALUES( 'blah2', 'bar2' )]] )

	rxTest:expectEqual(
		{
			{
				bar = "bar",
				blah = "blah",
			},
			{
				bar = "bar2",
				blah = "blah2",
			},
		},
		{ db:execOnce( [[SELECT * FROM foo ORDER BY blah]] ) } )

end

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

function WFSQLiteDatabaseTest.rxTestExec( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( blah, bar )]]

	local spy = databaseImpSpy( ':memory:' )

	local db = WFSQLiteDatabase( spy, schema )

	rxTest:expectEqual(
		{
		},
		{ db:exec( [[INSERT INTO foo VALUES( 'blah', 'bar' )]] ) } )

	db:execOnce( [[INSERT INTO foo VALUES( 'blah2', 'bar2' )]] )

	rxTest:expectEqual(
		{
			{
				bar = "bar",
				blah = "blah",
			},
			{
				bar = "bar2",
				blah = "blah2",
			},
		},
		{ db:exec( [[SELECT * FROM foo ORDER BY blah]] ) } )

	-- Running same query second time should reuse prepared statement
	-- and stats entries.

	rxTest:expectEqual(
		{
			{
				bar = "bar",
				blah = "blah",
			},
			{
				bar = "bar2",
				blah = "blah2",
			},
		},
		{ db:exec( [[SELECT * FROM foo ORDER BY blah]] ) } )

end

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

function WFSQLiteDatabaseTest.rxTestGatherArrayOfArray( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( blah, bar )]]

	local spy = databaseImpSpy( ':memory:' )

	local db = WFSQLiteDatabase( spy, schema )

	rxTest:expectEqual(
		{
		},
		{ db:exec( [[INSERT INTO foo VALUES( 'blah', 'bar' )]] ) } )

	db:execOnce( [[INSERT INTO foo VALUES( 'blah2', 'bar2' )]] )

	rxTest:expectEqual(
		{
			{ "blah", "bar" },
			{ "blah2", "bar2" },
		},
		db:gatherArrayOfArray( [[SELECT blah, bar FROM foo ORDER BY blah]] ) )

	-- Running same query second time should reuse prepared statement
	-- and stats entries.

	rxTest:expectEqual(
		{
			{ "blah", "bar" },
			{ "blah2", "bar2" },
		},
		db:gatherArrayOfArray( [[SELECT * FROM foo ORDER BY blah]] ) )

end

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

function WFSQLiteDatabaseTest.rxTestGatherArrayOfArrayWithChecksum( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( blah, bar )]]

	local spy = databaseImpSpy( ':memory:' )

	-- Test all datatypes

	local db = WFSQLiteDatabase( spy, schema )

	db:execOnce( [[INSERT INTO foo VALUES( 1, NULL )]] )

	rxTest:expectEqual(
		{
			{ 1 },
			sha256 = "232bd48476d4e8a8f58e65b200f9c0a05a7b861439ddf8067156f0e6b0ba4863",
		},
		db:gatherArrayOfArrayWithChecksum( [[SELECT blah, bar FROM foo ORDER BY blah]] ) )
	
	db:execOnce( [[INSERT INTO foo VALUES( 'blah', 17.3 )]] )

	rxTest:expectEqual(
		{
			{ 1 },
			{ 'blah', 17.3 },
			sha256 = "cbaaa3fe80421caef319f063b5d3269961c80e2079bba9285917a31260ea4897",
		},
		db:gatherArrayOfArrayWithChecksum( [[SELECT blah, bar FROM foo ORDER BY blah]] ) )

	local data = KSData.make( 'bar' )

	db:execOnce( [[INSERT INTO foo VALUES( "blah2", ? )]], data )

	local result = db:gatherArrayOfArrayWithChecksum( [[SELECT blah, bar FROM foo ORDER BY blah]] )
	assert( result.sha256, 'sha256 of db:gatherArrayOfArrayWithChecksum must not be nil' )
	rxTest:expectEqual( '52c760374a6103f508ab45501f8e7e770cfc2de5af819b5560d7faacdc27450a', result.sha256 )

end


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

function WFSQLiteDatabaseTest.rxTestGatherArray( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE t( c )]]

	local spy = databaseImpSpy( ':memory:' )

	local db = WFSQLiteDatabase( spy, schema )

	-- empty sql table yields empty array
	rxTest:expectEqual(
		{},
		db:gatherArray( [[SELECT c FROM t]] ) )

	rxTest:expectEqual(
		{
		},
		{ db:exec( [[INSERT INTO t VALUES( 'a'  )]] ) } )

	db:execOnce( [[INSERT INTO t VALUES( 'b' )]] )

	rxTest:expectEqual(
		{ 'a', 'b' },
		db:gatherArray( [[SELECT c FROM t ORDER BY c]] ) )

	-- Running same query second time should reuse prepared statement
	-- and stats entries.

	rxTest:expectEqual(
		{ 'a', 'b' },
		db:gatherArray( [[SELECT c FROM t ORDER BY c]] ) )


	-- test args
	rxTest:expectEqual(
		{ 'b' },
		db:gatherArray( [[SELECT c FROM t WHERE c > ? ORDER BY c]], 'a' ) )

	-- test args
	rxTest:expectEqual(
		{},
		db:gatherArray( [[SELECT c FROM t WHERE c > ? ORDER BY c]], 'b' ) )

end

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

function WFSQLiteDatabaseTest.rxTestGatherArrayWithChecksum( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( bar )]]

	local spy = databaseImpSpy( ':memory:' )

	-- Test all datatypes

	local db = WFSQLiteDatabase( spy, schema )

	db:execOnce( [[INSERT INTO foo VALUES( NULL )]] )

	rxTest:expectEqual(
		{
			nil,
			sha256 = "6e340b9cffb37a989ca544e6bb780a2c78901d3fb33738768511a30617afa01d",
		},
		db:gatherArrayWithChecksum( [[SELECT bar FROM foo ORDER BY bar]] ) )
	
	db:execOnce( [[INSERT INTO foo VALUES( 17.3 )]] )

	rxTest:expectEqual(
		{
			nil,
			17.3,
			sha256 = "aba0bc0568855bf001ebfd7435d2c88c05b81b44ce07e34cd72130abbbe6a417",
		},
		db:gatherArrayWithChecksum( [[SELECT bar FROM foo ORDER BY bar]] ) )

	local data = KSData.make( 'bar' )

	db:execOnce( [[INSERT INTO foo VALUES( ? )]], data )

	local result = db:gatherArrayWithChecksum( [[SELECT bar FROM foo ORDER BY bar]] )
	assert( result.sha256, 'sha256 of db:gatherArrayWithChecksum must not be nil' )
	rxTest:expectEqual( 'c8dd4c7f3b69c25cbe9669e74c745fdce18ede94ac0634857ab2b9b45f10a2ef', result.sha256 )

end


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

local function makeDb()

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable [[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]
	local db = WFSQLiteDatabase( ":memory:", schema )

	db:exec( "INSERT INTO MyTest VALUES ( 12, 15 )" )
	db:exec( "INSERT INTO MyTest VALUES ( 9, 18 )" )
	db:exec( "INSERT INTO MyTest VALUES ( 15, 20 )" )

	return db

end

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

function WFSQLiteDatabaseTest.rxTestEncodeResults( rxTest )

	local db = makeDb()

	local function myEncoder( sqlStmt )

		-- Normal use would be to make this a native function
		-- but we can do some rudimentary testing from Lua.

		local a, b

		a, b = sqlStmt:stepValues()
		rxTest:expectEqual( 12, a )
		rxTest:expectEqual( 15, b )

		a, b = sqlStmt:stepValues()
		rxTest:expectEqual( 9, a )
		rxTest:expectEqual( 18, b )

		a, b = sqlStmt:stepValues()
		rxTest:expectEqual( 15, a )
		rxTest:expectEqual( 20, b )

	end

	db:encodeResults( "SELECT * FROM MyTest ORDER BY rowid", myEncoder )

	db:close()

end

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

function WFSQLiteDatabaseTest.rxTestEncodeNative( rxTest )

	local enc = WFSQLiteDatabaseTestUtils.sampleEncoder

	local db = makeDb()

	local sum = db:encodeResults( "SELECT * FROM MyTest ORDER BY rowid", enc )

	rxTest:expectEqual( 89, sum )

	db:close()

end

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

function WFSQLiteDatabaseTest.rxTestNativeSet( rxTest )

	local WFSQLiteSetLogicImp = require 'WFSQLiteSetLogicImp'

	local enc = WFSQLiteSetLogicImp.encodeSet

	local db = makeDb()

	local set = db:encodeResults( "SELECT uniqueField FROM MyTest ORDER BY uniqueField", enc )

	rxTest:expectEqual( 3, set.length )

	local arr = set:toArray()

	rxTest:expectEqual( { 9, 12, 15 }, arr )

	local other = db:encodeResults( "SELECT uniqueField + 3 FROM MyTest ORDER BY uniqueField", enc )

	rxTest:expectEqual( 3, other.length )

	arr = other:toArray()

	rxTest:expectEqual( { 12, 15, 18 }, arr )

	local union = set:setWithUnion( other )

	rxTest:expectEqual( 4, union.length )

	arr = union:toArray()

	rxTest:expectEqual( { 9, 12, 15, 18 }, arr )

	local intersection = set:setWithIntersection( other )

	rxTest:expectEqual( 2, intersection.length )

	arr = intersection:toArray()

	rxTest:expectEqual( { 12, 15 }, arr )

	WFSQLiteSetLogicImp.registerSetModule( db )

	local tableArg = intersection:_prepareForVirtualTable( db );

	db:execOnce( string.format( [[CREATE VIRTUAL TABLE virtualTableTest USING WFSQLite_SetTable( %s )]], tableArg ) )

	local result = db:gatherArrayOfArray [[SELECT * FROM virtualTableTest]]
	rxTest:expectEqual( { { 12 }, { 15 } }, result )

	db:execOnce [[DROP TABLE virtualTableTest]]

	db:close()

end

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

function WFSQLiteDatabaseTest.rxTestGetLastInsertRowId( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( blah, bar )]]

	local db = WFSQLiteDatabase( ':memory:', schema )

	db:execOnce( [[INSERT INTO foo VALUES( 'blah', 'bar' )]] )

	rxTest:expectEqual( 1, db:getLastInsertRowId() )

	db:execOnce( [[INSERT INTO foo VALUES( 'blah2', 'bar2' )]] )

	rxTest:expectEqual( 2, db:getLastInsertRowId() )

end

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

function WFSQLiteDatabaseTest.rxTestGetLastInsertRowId( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( blah, bar )]]

	local db = WFSQLiteDatabase( ':memory:', schema )

	db:execOnce( [[INSERT INTO foo VALUES( 'blah', 'bar' )]] )

	rxTest:expectEqual( 1, db:getLastInsertRowId() )

	db:execOnce( [[INSERT INTO foo VALUES( 'blah2', 'bar2' )]] )

	rxTest:expectEqual( 2, db:getLastInsertRowId() )

end

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

function WFSQLiteDatabaseTest.rxTestGetAffectedRowCount( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( a )]]

	local db = WFSQLiteDatabase( ':memory:', schema )

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

end

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

-- @mark savepoint

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

function WFSQLiteDatabaseTest.rxTestSetAutosaveInterval_failureCases( rxTest )

	local db = makeDb()

	rxTest:expectError( function() db:setAutosaveInterval( "bogus" ) end )
	rxTest:expectError( function() db:setAutosaveInterval( 0.5 ) end )

	db:close()

end

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

function WFSQLiteDatabaseTest.testSavepoint_happyPath( test )

	local db = makeDb()
	db:setAutosaveInterval( 2 )

	WFAsyncTestCase( test, 6 )

		:waitFor( function()

			db:withTransactionDo( function()
				db:exec( "INSERT INTO MyTest VALUES ( 44, 51 )" )
			end )

			db:withTransactionDo( function()
				db:exec( "INSERT INTO MyTest VALUES ( 91, 60 )" )
			end )

			return 2

		end )

		:waitFor( function()

			return db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" )

		end )
		:continueWhenValueMatches( 'data',
			{
				{
					9,
					18,
				},
				{
					12,
					15,
				},
				{
					15,
					20,
				},
				{
					44,
					51,
				},
				{
					91,
					60,
				},
			} )

		:waitFor( function()

			db:close()
			return 'ok'

		end )

end

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

function WFSQLiteDatabaseTest.testSavepoint_explicitSave( test )

	local db = makeDb()
	db:setAutosaveInterval( 2 )

	WFAsyncTestCase( test, 6 )

		:waitFor( function()

			db:withTransactionDo( function()
				db:exec( "INSERT INTO MyTest VALUES ( 44, 51 )" )
			end )

			db:withTransactionDo( function()
				db:exec( "INSERT INTO MyTest VALUES ( 91, 60 )" )
			end )

			db:save()

			return 2

		end )

		:waitFor( function()

			return db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" )

		end )
		:continueWhenValueMatches( 'data',
			{
				{
					9,
					18,
				},
				{
					12,
					15,
				},
				{
					15,
					20,
				},
				{
					44,
					51,
				},
				{
					91,
					60,
				},
			} )

		:waitFor( function()

			db:close()
			return 'ok'

		end )

end

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

function WFSQLiteDatabaseTest.testSavepoint_rollback1( test )

	local db = makeDb()
	db:setAutosaveInterval( 2 )

	WFAsyncTestCase( test, 6 )

		:waitFor( function()

			KSTestUtils.assertFails( function()
				db:withTransactionDo( function()
					error "test failure"
				end )
			end )

			db:withTransactionDo( function()
				db:exec( "INSERT INTO MyTest VALUES ( 91, 60 )" )
			end )

			return 2

		end )

		:waitFor( function()

			return db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" )

		end )
		:continueWhenValueMatches( 'data',
			{
				{
					9,
					18,
				},
				{
					12,
					15,
				},
				{
					15,
					20,
				},
				{
					91,
					60,
				},
			} )

		:waitFor( function()

			db:close()
			return 'ok'

		end )

end

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

function WFSQLiteDatabaseTest.testSavepoint_rollback2( test )

	local db = makeDb()
	db:setAutosaveInterval( 2 )

	WFAsyncTestCase( test, 6 )

		:waitFor( function()

			db:withTransactionDo( function()
				db:exec( "INSERT INTO MyTest VALUES ( 44, 51 )" )
			end )

			KSTestUtils.assertFails( function()
				db:withTransactionDo( function()
					error "test failure"
				end )
			end )

			return 2

		end )

		:waitFor( function()

			return db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" )

		end )
		:continueWhenValueMatches( 'data',
			{
				{
					9,
					18,
				},
				{
					12,
					15,
				},
				{
					15,
					20,
				},
				{
					44,
					51,
				},
			} )

		:waitFor( function()

			db:close()
			return 'ok'

		end )

end

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

function WFSQLiteDatabaseTest.testSavepoint_noopSave( test )

	local db = makeDb()
	db:setAutosaveInterval( 1 )

	WFAsyncTestCase( test, 6 )

		:waitFor( function()

			db:withTransactionDo( function()
				db:exec( "INSERT INTO MyTest VALUES ( 44, 51 )" )
			end )

			db:withTransactionDo( function()
				db:exec( "INSERT INTO MyTest VALUES ( 91, 60 )" )
			end )

			return 2

		end )

		:waitFor( function()

			db:save() -- should be no-op as timer should have fired

			return db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" )

		end )
		:continueWhenValueMatches( 'data',
			{
				{
					9,
					18,
				},
				{
					12,
					15,
				},
				{
					15,
					20,
				},
				{
					44,
					51,
				},
				{
					91,
					60,
				},
			} )

		:waitFor( function()

			db:close()
			return 'ok'

		end )

end


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

-- @mark transaction

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

function WFSQLiteDatabaseTest.rxTestTransaction_happyPath( rxTest )

	local db = makeDb()

	db:withTransactionDo( function()
		db:exec( "INSERT INTO MyTest VALUES ( 44, 51 )" )
	end )

	db:withTransactionDo( function()
		db:exec( "INSERT INTO MyTest VALUES ( 91, 60 )" )
	end )

	rxTest:expectEqual(
		{
			{ 9, 18, },
			{ 12, 15, },
			{ 15, 20, },
			{ 44, 51, },
			{ 91, 60, },
		},
		db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" ) )

	rxTest:expectEqual( 2, db:nTransactions() )

	db:close()

end

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

function WFSQLiteDatabaseTest.rxTestTransaction_rollback1( rxTest )

	local db = makeDb()

	rxTest:expectError( function()
		db:withTransactionDo( function()
			error "expected error"
		end )
	end )

	db:withTransactionDo( function()
		db:exec( "INSERT INTO MyTest VALUES ( 91, 60 )" )
	end )

	rxTest:expectEqual(
		{
			{ 9, 18, },
			{ 12, 15, },
			{ 15, 20, },
			{ 91, 60, },
		},
		db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" ) )

	rxTest:expectEqual( 2, db:nTransactions() )

	db:close()

end

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

function WFSQLiteDatabaseTest.rxTestTransaction_rollback2( rxTest )

	local db = makeDb()

	db:withTransactionDo( function()
		db:exec( "INSERT INTO MyTest VALUES ( 44, 51 )" )
	end )

	rxTest:expectError( function()
		db:withTransactionDo( function()
			error "expected error"
		end )
	end )

	rxTest:expectEqual(
		{
			{ 9, 18, },
			{ 12, 15, },
			{ 15, 20, },
			{ 44, 51, },
		},
		db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" ) )

	rxTest:expectEqual( 2, db:nTransactions() )

	db:close()

end

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

function WFSQLiteDatabaseTest.rxTestTransaction_rollbackPartial( rxTest )

	local db = makeDb()

	rxTest:expectError( function()
		db:withTransactionDo( function()
			db:exec( "INSERT INTO MyTest VALUES ( 91, 60 )" )
			error "expected error"
		end )
	end )

	rxTest:expectEqual(
		{
			{ 9, 18, },
			{ 12, 15, },
			{ 15, 20, },
		},
		db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" ) )

	rxTest:expectEqual( 1, db:nTransactions() )

	db:close()

end

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

function WFSQLiteDatabaseTest.rxTestTransaction_errorNestedNotAllowed( rxTest )

	local db = makeDb()

	rxTest:expectError( function()
		db:withTransactionDo( function()
			db:exec( "INSERT INTO MyTest VALUES ( 91, 60 )" )
			db:withTransactionDo( function() -- not allowed
				db:exec( "INSERT INTO MyTest VALUES ( 91, 60 )" )
			end )
		end )
	end )

	rxTest:expectEqual(
		{
			{ 9, 18, },
			{ 12, 15, },
			{ 15, 20, },
		},
		db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" ) )

	rxTest:expectEqual( 1, db:nTransactions() )

	db:close()

end


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

function WFSQLiteDatabaseTest.rxTest_createTransitableRefAndClose( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable [[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]

	local db = WFSQLiteDatabase( ":memory:", schema )

	db:exec( "INSERT INTO MyTest VALUES ( 12, 15 )" )
	db:exec( "INSERT INTO MyTest VALUES ( 9, 18 )" )

	local origDBContents = db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" )

	local transitableRef1 = db:createTransitableRefAndClose()

	local db2 = WFSQLiteDatabase( transitableRef1, schema )

	rxTest:expectError( function()

		-- we've "closed" this db
		db:exec( "INSERT INTO MyTest VALUES ( 15, 20 )" )

	end )

	local otherDBContents = db2:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" )

	rxTest:expectEqual( KSTableUtils.deepEquals( origDBContents, otherDBContents ), true )

	db2:exec( "INSERT INTO MyTest VALUES ( 15, 20 )" )

	otherDBContents = db2:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" )

	rxTest:expectEqual( KSTableUtils.deepEquals( origDBContents, otherDBContents ), false )

	db2:close()

	rxTest:expectError( function()

		-- we've closed this db, too
		db2:exec( "INSERT INTO MyTest VALUES ( 17, 20 )" )

	end )

	rxTest:expectError( function()

		-- we've already used transitableRef1, it can't be reused and it should
		-- throw if we try to create another db from it
		WFSQLiteDatabase( transitableRef1, schema )

	end )

	local db3 = WFSQLiteDatabase( ":memory:", schema )

	local transitableRef2 = db3:createTransitableRefAndClose()

	rxTest:expectEqual( type( transitableRef2:path() ), "string" )

	transitableRef2:close()

	rxTest:expectError( function()

		-- we've already closed transitableRef2, it can't be reused and it
		-- should throw if we try to create another db from it
		WFSQLiteDatabase( transitableRef2, schema )

	end )

end

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

local function justRunDbFunc( db, funcTuple )

	local funcName = funcTuple[ 1 ]

	db[ funcName ]( db, unpack( funcTuple, 2 ) )

end

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

local function runDbFuncWithOrWithoutError( expectError, funcTuple, db, rxTest )

	local funcName = funcTuple[ 1 ]

	rxTest:logf( "running %s and we %s expecting error", 
					funcName, expectError and "ARE" or "ARE NOT" )

	if expectError then

		rxTest:expectError(
			function()

				justRunDbFunc( db, funcTuple )

			end )

		return
	
	end

	justRunDbFunc( db, funcTuple )

end

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

local function runDbFuncWithoutError( funcTuple, db, rxTest )
	
	runDbFuncWithOrWithoutError( false, funcTuple, db, rxTest )
	
end

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

local function runDbFuncWithError( funcTuple, db, rxTest )
	
	runDbFuncWithOrWithoutError( true, funcTuple, db, rxTest )
	
end

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

function WFSQLiteDatabaseTest.rxTestCorruptErrorHandler( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable [[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]

	local sawHandleFatalError

	-- guard against race conditions by waiting for the corruption logger to
	-- start before causing corruption.
	local corruptLoggerThreadIsReady = Rx.Subject()

	-- retain the subscr until after the test completes to ensure the corruption
	-- logger thread stays alive
	local corruptLoggerThreadIsReadySubscr =
			WFSQLiteCorruptionLogger.private.workerThreadIsActiveStream()
				:subscribe( corruptLoggerThreadIsReady )
	
	rxTest
		:expectSingleValue(
			corruptLoggerThreadIsReady,
			true )

	local relayCorruptionLog = Rx.ReplaySubject( 1 )

	local corruptionEventStreamSubscription =
			WFSQLiteCorruptionLogger.getCorruptionEventStream()
				:filter(
					function( errorType )
		
						sawHandleFatalError = errorType == "corrupt"

						return sawHandleFatalError

					end )
				:take( 1 )
				:subscribe( relayCorruptionLog )

	local db = WFSQLiteDatabase( ":memory:", schema )

	db:exec( "INSERT INTO MyTest VALUES ( 12, 15 )" )
	db:exec( "INSERT INTO MyTest VALUES ( 9, 18 )" )

	local funcsPlusArgsThatShouldNeverThrow = {
		{ "path" },
		{ "prepare", "SELECT * FROM MyTest WHERE uniqueField = ?", 12 },
	}

	local funcsPlusArgsThatShouldThrowWhenError = {
			{ "gatherArrayOfArray", "SELECT * FROM MyTest WHERE uniqueField = ?", 12 },
			{ "gatherArray", "SELECT uniqueField FROM MyTest WHERE uniqueField = ?", 12 },
			{ "execOnce", "UPDATE MyTest SET otherField = otherField + 1 WHERE uniqueField = ?" , 12345 },
			{ "exec", "UPDATE MyTest SET otherField = otherField + 1 WHERE uniqueField = ?" , 12345 },
		}

	WFFunctionUtils.map( 
		runDbFuncWithoutError, 
			funcsPlusArgsThatShouldNeverThrow, db, rxTest )

	WFFunctionUtils.map( 
		runDbFuncWithoutError, 
			funcsPlusArgsThatShouldThrowWhenError, db, rxTest )

	db:_DEBUG_simulateCorruption()

	WFFunctionUtils.map( 
		runDbFuncWithoutError, 
			funcsPlusArgsThatShouldNeverThrow, db, rxTest )

	WFFunctionUtils.map( 
		runDbFuncWithError, 
			funcsPlusArgsThatShouldThrowWhenError, db, rxTest )

	rxTest:expectSingleValue( relayCorruptionLog, 
								'corrupt', ':memory:', ':ignore:', ':ignore:' )

	rxTest:expectEqual( sawHandleFatalError, true )

	corruptLoggerThreadIsReadySubscr:dispose()

	corruptionEventStreamSubscription:dispose()

end

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

function WFSQLiteDatabaseTest.rxTestIOErrorHandler( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable [[CREATE TABLE MyTest( uniqueField UNIQUE NOT NULL, otherField NOT NULL )]]

	local db = WFSQLiteDatabase( ":memory:", schema )

	db:exec( "INSERT INTO MyTest VALUES ( 12, 15 )" )
	db:exec( "INSERT INTO MyTest VALUES ( 9, 18 )" )

	local sawHandleFatalError

	-- guard against race conditions by waiting for the corruption logger to
	-- start before causing corruption.
	local corruptLoggerThreadIsReady = Rx.Subject()

	-- retain the subscr until after the test completes to ensure the corruption
	-- logger thread stays alive
	local corruptLoggerThreadIsReadySubscr =
			WFSQLiteCorruptionLogger.private.workerThreadIsActiveStream()
				:subscribe( corruptLoggerThreadIsReady )
	
	rxTest
		:expectSingleValue(
			corruptLoggerThreadIsReady,
			true )

	local relayCorruptionLog = Rx.ReplaySubject( 1 )

	local corruptionEventStreamSubscription =
			WFSQLiteCorruptionLogger.getCorruptionEventStream()
				:filter(
					function( errorType )
		
						sawHandleFatalError = errorType == "IO"

						return sawHandleFatalError

					end )
				:take( 1 )
				:subscribe( relayCorruptionLog )

	rxTest:expectError( function()
		db:_DEBUG_simulateIOError()

	end )

	rxTest:expectSingleValue( relayCorruptionLog, 
								'IO', ':memory:', ':ignore:', ':ignore:' )

	rxTest:expectEqual( sawHandleFatalError, true )

	corruptLoggerThreadIsReadySubscr:dispose()

	corruptionEventStreamSubscription:dispose()

end


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

function WFSQLiteDatabaseTest.rxTestTransactionAndDisabledForeignKeysDo( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable [[CREATE TABLE MyTest( 
						uniqueField UNIQUE NOT NULL, 
						otherField NOT NULL,
						referencedField INTEGER REFERENCES MyTest2( uniqueField ) ON DELETE SET NULL )]]
				
	schema:addTable [[CREATE TABLE MyTest2( 
						uniqueField UNIQUE NOT NULL )]]
				
	local db = WFSQLiteDatabase( ":memory:", schema )

	db:execOnce( "PRAGMA foreign_keys = true" )

	db:withTransactionDo( function()
	
		db:exec( "INSERT INTO MyTest2 VALUES ( 1 )" )
		db:exec( "INSERT INTO MyTest2 VALUES ( 2 )" )
		db:exec( "INSERT INTO MyTest2 VALUES ( 3 )" )
		db:exec( "INSERT INTO MyTest2 VALUES ( 4 )" )
	
		db:exec( "INSERT INTO MyTest VALUES ( 12, 15, 1 )" )
		db:exec( "INSERT INTO MyTest VALUES ( 15, 20, 2 )" )
		db:exec( "INSERT INTO MyTest VALUES ( 19, 18, 3 )" )
		db:exec( "INSERT INTO MyTest VALUES ( 44, 51, 4 )" )
		
	end )

	rxTest:expectEqual(
		{
			{ 1 },
			{ 2 },
			{ 3 },
			{ 4 },
		},
		db:gatherArrayOfArray( "SELECT * FROM MyTest2 ORDER BY uniqueField" ) )

	rxTest:expectEqual(
		{
			{ 12, 15, 1 },
			{ 15, 20, 2 },
			{ 19, 18, 3 },
			{ 44, 51, 4 },
		},
		db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" ) )

	rxTest:expectEqual( 1, db:nTransactions() )

	db:withTransactionDo( function()
	
		db:exec( "DELETE FROM MyTest2 WHERE uniqueField = 1" )
		
	end )

	rxTest:expectEqual( nil, db:exec( "PRAGMA foreign_key_check" ) )

	db:withTransactionAndDisabledForeignKeysDo( function()
	
		db:exec( "DELETE FROM MyTest2 WHERE uniqueField = 2" )
		
	end )

	rxTest:expectEqual( {
			fkid = 0,
			parent = "MyTest2",
			rowid = 2,
			table = "MyTest",
		}, db:exec( "PRAGMA foreign_key_check" ) )

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

	rxTest:expectEqual(
		{
			{ 3 },
			{ 4 },
		},
		db:gatherArrayOfArray( "SELECT * FROM MyTest2 ORDER BY uniqueField" ) )

	rxTest:expectEqual(
		{
			{ 12, 15, nil },
			{ 15, 20, 2 },
			{ 19, 18, 3 },
			{ 44, 51, 4 },
		},
		db:gatherArrayOfArray( "SELECT * FROM MyTest ORDER BY uniqueField" ) )


	db:close()

end

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

-- @mark logging

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

function WFSQLiteDatabaseTest.rxTestDiagnosticLoggingInfrastructure( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( blah, bar )]]

	local spy = databaseImpSpy( ':memory:' )

	local db = WFSQLiteDatabase( spy, schema )

	rxTest:expectEqual(
		{
		},
		{ db:exec( [[INSERT INTO foo VALUES( 'blah', 'bar' )]] ) } )

	db:execOnce( [[INSERT INTO foo VALUES( 'blah2', 'bar2' )]] )

	rxTest:expectEqual(
		{
			{
				bar = "bar",
				blah = "blah",
			},
			{
				bar = "bar2",
				blah = "blah2",
			},
		},
		{ db:exec( [[SELECT * FROM foo ORDER BY blah]] ) } )

	-- Running same query second time should reuse prepared statement
	-- and stats entries.

	rxTest:expectEqual(
		{
			{
				bar = "bar",
				blah = "blah",
			},
			{
				bar = "bar2",
				blah = "blah2",
			},
		},
		{ db:exec( [[SELECT * FROM foo ORDER BY blah]] ) } )

	-- Since these functions send their output to other logging
	-- infrastructure (console or WFDiagnosticLog), not much we can do
	-- to test, other than ensure they don't crash.

	db:sendDiagnosticLogData()

	rxTest:expectEqual( 'string', type( db:PERF_generateQueryStatsReport() ) )

	db:PERF_dumpQueryPerformanceStats()

end

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

function WFSQLiteDatabaseTest.rxTestSQLiteFull( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		[[CREATE TABLE foo( blah_id INTEGER PRIMARY KEY AUTOINCREMENT, bar )]]

	-- blah_id is an INTEGER PRIMARY KEY column, meaning this column is the alias
	-- of the rowid column for this specific table.

	-- The maximum value of the blah_id column is 9,223,372,036,854,775,807. If we
	-- insert an entry with this maximum value, and then attempt to insert a new row,
	-- SQLite will issue an SQLITE_FULL error.

	local spy = databaseImpSpy( ':memory:' )

	local db = WFSQLiteDatabase( spy, schema )

	-- Insert max-value entry.

	rxTest:expectEqual(
		{
		},
		{ db:execOnce( [[INSERT INTO foo( blah_id, bar ) VALUES( 9223372036854775807, 'bar' )]] ) } )

	local SQLITE_FULL_IDENTIFIER = 'database or disk is full'
	local SQLITE_MISUSE_IDENTIFIER = 'bad parameter or other API misuse'

	-- Attempt to insert a new entry. Note that we dont specify the blah_id value here, as we
	-- want the columns AUTOINCREMENT behavior. This should fail.

	local success, errorMessage = pcall( db.execOnce, db, "INSERT INTO foo( bar ) VALUES( 'bar2' )" )

	rxTest:log( errorMessage )

	rxTest:expectEqual( false, success )

	rxTest:expectEqual( true, not not string.match( errorMessage, SQLITE_FULL_IDENTIFIER ) )

	-- Now attempt to insert the data again. WF SQLite will attempt to reuse the stmt. Before the
	-- fix, the stmt was not reset and so the following returned a SQLITE_MISUSE error.

	success, errorMessage = pcall( db.execOnce, db, "INSERT INTO foo( bar ) VALUES( 'bar2' )" )

	rxTest:expectEqual( false, success )

	rxTest:log( errorMessage )

	rxTest:expectEqual( false, not not string.match( errorMessage, SQLITE_MISUSE_IDENTIFIER ) )

	rxTest:expectEqual( true, not not string.match( errorMessage, SQLITE_FULL_IDENTIFIER ) )

	db:execOnce( "DROP TABLE foo" )

end

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

-- SPAM [jandrew 2021-05-14]: The following test can be used to verify the fix
-- that was added for https://jira.corp.adobe.com/browse/WF-1591.
-- In order to run this test, you need a drive (external is fine) with less
-- than 1MB of free space. 
--[[
function WFSQLiteDatabaseTest.rxTestSQLiteFull_Real( rxTest )

	local schema = WFSQLiteDatabaseSchema( '1.0' )

	schema:addTable
		"CREATE TABLE foo( blah, bar )"

	local pathToLowDriveWithNoDiskSpace = '/Volumes/FOO/SQLiteFullTest.db'
		-- Modify the above path to point to your low disk space drive.
		
	local spy = databaseImpSpy( pathToLowDriveWithNoDiskSpace )

	local db = WFSQLiteDatabase( spy, schema )

	local testData = KSPathUtils.getStandardFilePath( 'testdata' )
	local filePath = testData .. '/rtmfp/1MB.txt'
	local f = io.open( filePath, 'r' )
	local content = f:read("*all")

	local SQLITE_FULL_IDENTIFIER = 'database or disk is full'
	local SQLITE_MISUSE_IDENTIFIER = 'bad parameter or other API misuse'

	-- Attempt to insert data into the DB, larger than the amount of free space on the drive.
	-- This will result in a SQLITE_FULL error, which is verified in a hacky-ish way using
	-- the error string.
	
	local success, errorMessage = pcall( db.execOnce, db, "INSERT INTO foo VALUES( ?, ? )", content, content )

	rxTest:log( errorMessage )

	rxTest:expectEqual( false, success )
	
	rxTest:expectEqual( true, not not string.match( errorMessage, SQLITE_FULL_IDENTIFIER ) )
	
	-- Now attempt to insert the data again. WF SQLite will attempt to reuse the stmt. Before the
	-- fix, the stmt was not reset and so the following returned a SQLITE_MISUSE error.
	
	success, errorMessage = pcall( db.execOnce, db, "INSERT INTO foo VALUES( ?, ? )", content, content )
	
	rxTest:expectEqual( false, success )
	
	rxTest:log( errorMessage )
	
	rxTest:expectEqual( false, not not string.match( errorMessage, SQLITE_MISUSE_IDENTIFIER ) )
	
	rxTest:expectEqual( true, not not string.match( errorMessage, SQLITE_FULL_IDENTIFIER ) )
		
	db:execOnce( "DROP TABLE foo" )

end]]

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

return WFRxTestCase.wrapSuite( WFSQLiteDatabaseTest )
