#include-once

; #INDEX# =======================================================================================================================
; Title .........: Database UDF
; AutoIt Version : 3.3.14.2
; Description ...: A collection of functions to open, close and perform actions on databases or database type files (msi. msp)
; Author(s) .....: Benners
; ===============================================================================================================================

; #CURRENT# =====================================================================================================================
;_Database_SQL_LoadDll
;_Database_SQL_UnloadDll
; ===============================================================================================================================

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_AU3Check_Parameters=-q -d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w-7
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

#Region #### Globals #################################
; if updating the enum order, alter order in _Database_GetTableColumns as well
Global Enum _
		$UPD_ID_COL, _ ; updates col #### keep this order for array operations ####
		$UPD_KBARTICLENUMBER_COL, _
		$UPD_DESCRIPTION_COL, _
		$UPD_CLASSIFICATION_COL, _
		$UPD_STDPACKAGENAME_COL, _
		$UPD_TARGETPRODUCTNAME_COL, _
		$UPD_BUILDNUMBER_COL, _
		$UPD_CREATIONTIMEUTC_COL, _
		$UPD_MOREINFOURL_COL, _
		$UPD_PLATFORM_COL, _
		$UPD_FILESIZE_COL, _
		$UPD_SUPERSEDEDBY_COL, _
		$UPD_SUPERSEDES_COL, _
		$UPD_UPDATECATALOGURLS_COL, _
		$UPD_MAJORVERSION_COL, _
		$UPD_MSPSOURCEDIRECTORY_COL, _
		$UPD_MSPFILENAME_COL, _
		$UPD_MSPFILEHASH_COL, _
		$UPD_MSPMODIFIEDTIME_COL, _
		$UPD_ORIGINALPATH_COL, _
		$UPD_CHILDID_COL, _ ; #### keep this order for array operations ####
		$UPD_MAXCOLS

Global Enum _
		$LANG_ID_COL, _ ; language cols
		$LANG_LCID_COL

Global Enum _
		$PCD_ID_COL, _ ; product codes cols
		$PCD_GUID_COL

Global Enum _
		$UALLINK_UPDID_COL, _ ; updates and language link table cols
		$UALLINK_LANGID_COL

Global Enum _
		$UAPCLINK_UPID_COL, _ ; update and product codes link table cols
		$UAPCLINK_PRODID_COL

; if updating the enum order. update the order in _Database_GetTableNames as well
Global Enum _ ; row # for table names array
		$TBN_LANGUAGES = 1, _
		$TBN_PRODUCTCODES, _
		$TBN_UPDATES, _
		$TBN_UPDLANGLINK, _
		$TBN_UPDPRODLINK
#EndRegion #### Globals #################################

#Region #### AutoIt Includes #########################
#include <FileConstants.au3>
#include <sqlite.au3>
#include <SQLite.dll.au3>
#EndRegion #### AutoIt Includes #########################

#Region #### User Includes ###########################
#include 'OI_FileAndFolder.au3'
#include 'OI_Log.au3'
#include 'OI_Msi.au3'
#include 'OI_OfficeProperties.au3'
#EndRegion #### User Includes ###########################

;~ _Database_LoadDll()
;~ _Database_CreateDB()

;~ _Database_Close()
;~ _Database_UnloadDll()

Func _Database_CheckForMatchingMD5($s_MD5)
	_Log_Header('_Database_CheckForMatchingMD5')

	Local $as_UpdatesColumns = _Database_GetTableColumns($TBN_UPDATES)

	Local $s_Sql = _
			"SELECT " & '"' & $as_UpdatesColumns[$UPD_ID_COL] & '"' & _
			" FROM " & '"' & _Database_GetTableNames($TBN_UPDATES) & '"' & _
			" WHERE " & '"' & $as_UpdatesColumns[$UPD_MSPFILEHASH_COL] & '"' & "=" & '"' & $s_MD5 & '"' & ";"

	Local $s_Function = '_Database_RunSQLite_QuerySingleRow'
	Local $v_Parameters = $s_Sql
	Local $i_lineNumber = 735
	Local $i_Md5Match = _Database_RunSQLite_QuerySingleRow($v_Parameters) ; check the updates table for matching md5
	If Not @error Then Return SetError($SQLITE_OK, _Log_Header(), $i_Md5Match)

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>_Database_CheckForMatchingMD5

Func _Database_GetChildItemMetadata($i_TreeviewItemID)
	_Log_Header('_Database_GetChildItemMetadata')

	Local $as_UpdatesColumns = _Database_GetTableColumns() ; get the columns for the updates table

	Local $s_Sql = _
			"SELECT " & _
			'"' & $as_UpdatesColumns[$UPD_CLASSIFICATION_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_STDPACKAGENAME_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_TARGETPRODUCTNAME_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_BUILDNUMBER_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_CREATIONTIMEUTC_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_MOREINFOURL_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_PLATFORM_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_FILESIZE_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_SUPERSEDEDBY_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_SUPERSEDES_COL] & '", ' & _
			'"' & $as_UpdatesColumns[$UPD_UPDATECATALOGURLS_COL] & '"' & _
			" FROM " & '"' & _Database_GetTableNames($TBN_UPDATES) & '"' & _
			" WHERE " & '"' & $as_UpdatesColumns[$UPD_CHILDID_COL] & '"' & " = " & $i_TreeviewItemID & ";",

	Local $s_Function = '_Database_RunSQLite_Query'
	Local $v_Parameters = $s_Sql
	Local $i_lineNumber = 110
	Local $v_Ret = _Database_RunSQLite_Query($s_Sql)
	If Not @error Then Return SetError($SQLITE_OK, _Log_Header(), $v_Ret)

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>_Database_GetChildItemMetadata

Func _Database_GetTableColumns($i_TableName = $TBN_UPDATES)
	Local $s_Columns = ''

	Switch $i_TableName
		Case $TBN_LANGUAGES
			$s_Columns = _
					'Id,' & _
					'LCID'
		Case $TBN_PRODUCTCODES
			$s_Columns = _
					'Id,' & _
					'GUID'
		Case $TBN_UPDATES
			$s_Columns = _
					'Id,' & _
					'KbArticleNumber,' & _
					'Description,' & _
					'Classification,' & _
					'StdPackageName,' & _
					'TargetProductName,' & _
					'BuildNumber,' & _
					'CreationTimeUTC,' & _
					'MoreInfoUrl,' & _
					'Platform,' & _
					'FileSize,' & _
					'SupersededBy,' & _
					'Supersedes,' & _
					'UpdateCatalogUrls,' & _
					'MajorVersion,' & _
					'MspSourceDirectory,' & _
					'MspFileName,' & _
					'MspFileHash,' & _
					'MspModifiedTime,' & _
					'OriginalPath,' & _
					'ChildID'
		Case $TBN_UPDLANGLINK
			$s_Columns = _
					'UpdId,' & _
					'LangId'
		Case $TBN_UPDPRODLINK
			$s_Columns = _
					'UpdId,' & _
					'ProdId'
	EndSwitch

	Return StringSplit($s_Columns, ',', $STR_NOCOUNT)
EndFunc   ;==>_Database_GetTableColumns

Func _Database_GetTableNames($i_ReturnName = 0)
	Local $as_TableNames = StringSplit('Languages,ProductCodes,Updates,UpdLngLink,UpdProdLink', ',')

	If Not $i_ReturnName Then Return $as_TableNames ; return the array
	Return $as_TableNames[$i_ReturnName] ; return the table name
EndFunc   ;==>_Database_GetTableNames

Func _Database_GetTableRowCount($s_TableName)
	_Log_Header('_Database_GetTableRowCount')

	Local $s_Sql = _
			"SELECT Count(*) " & _
			"FROM " & $s_TableName & ";", ; sql string

	Local $s_Function = '_Database_RunSQLite_Query' ; running function
	Local $v_Parameters = $s_Sql
	Local $i_lineNumber = 181
	Local $i_Ret = _Database_RunSQLite_Query($s_Sql)

	If Not @error Then
		_Log_Write('Row Count: ' & $i_Ret[0])
		Return SetError($SQLITE_OK, _Log_Header(), $i_Ret[0])
	EndIf

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>_Database_GetTableRowCount

Func _Database_GetUpdatesDescription($i_KbArticleNumber)
	_Log_Header('_Database_GetUpdatesDescription')

	Local $as_UpdatesColumns = _Database_GetTableColumns() ; get the columns for the updates table

	Local $s_Sql = _
			"SELECT " & '"' & $as_UpdatesColumns[$UPD_DESCRIPTION_COL] & '"' & _ ; description
			" FROM " & '"' & _Database_GetTableNames($TBN_UPDATES) & '"' & _ ; updates
			" WHERE " & '"' & $as_UpdatesColumns[$UPD_KBARTICLENUMBER_COL] & '"' & " = " & $i_KbArticleNumber & ";" ; KBArticleNumber

	Local $s_Function = '_Database_RunSQLite_QuerySingleRow'
	Local $v_Parameters = $s_Sql
	Local $i_lineNumber = 205
	Local $i_Ret = _Database_RunSQLite_QuerySingleRow($v_Parameters)

	If Not @error Then
		_Log_Write('Description: ' & $i_Ret)
		Return SetError($SQLITE_OK, _Log_Header(), $i_Ret)
	EndIf

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>_Database_GetUpdatesDescription

Func _Database_InsertRow($s_TableName, $s_Values, $s_Columns = '')
	_Log_Header('_Database_InsertRow')
	_Log_Write('Inserting Row..')
	_Log_Write('Columns: ' & $s_Columns)
	_Log_Write('Values : ' & $s_Values)

	Local $s_Sql = ''

	; check if columns were supplied and set the correct string
	If Not $s_Columns Then
		$s_Sql = _
				"INSERT INTO " & '"' & $s_TableName & '"' & & _
				" VALUES (" & $s_Values & ");" ; sql string
	Else
		$s_Sql = _
				"INSERT INTO " & '"' * $s_TableName & '"' & _
				" (" & $s_Columns & ") VALUES (" & $s_Values & ");" ; sql string
	EndIf

	_Log_Write('SQL     : ' & $s_Sql)

	Local $s_Function = '_Database_RunSQLite_Exec'
	Local $v_Parameters = $s_Sql
	Local $i_lineNumber = 236
	Local $i_Ret = _Database_RunSQLite_Exec($v_Parameters)

	If Not _Log_GetFatalErrorState() Then
		; get the number of the inserted row
		$s_Sql = 'SELECT last_insert_rowid();'
		$s_Function = '_Database_RunSQLite_Exec'
		$v_Parameters = $s_Sql
		$i_lineNumber = 244
		$i_Ret = _Database_RunSQLite_QuerySingleRow($s_Sql)
		If Not _Log_GetFatalErrorState() Then Return SetError($SQLITE_OK, _Log_Header(), $i_Ret)
	EndIf

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>_Database_InsertRow

Func _Database_InsertUpdate(ByRef $as_Metadata, $s_UpdatePath, $i_TreeviewItemID)
	_Log_Header('_Database_InsertUpdate')
	_Log_Write('Update Path: ' & $s_UpdatePath)
	_Log_Write('¦Getting TargetProductCodes...')

	; get the target product codes for the msp
	Local $s_Function = '_OP_GetTargetProductCodesArray'
	Local $v_Parameters = $s_UpdatePath
	Local $i_lineNumber = 261
	Local $as_TPC = _OP_GetTargetProductCodesArray($v_Parameters)

	If IsArray($as_TPC) Then
		$s_Function = '_Database_InsertUpdateProductCodes'
		$v_Parameters = '$as_Metadata' & '#' & '$as_TPC' & '#' & $s_UpdatePath & '#' & $i_TreeviewItemID
		$i_lineNumber = 267
		Local $i_Updates_Id = __Databse_InsertUpdateDetails($as_Metadata, $as_TPC, $s_UpdatePath, $i_TreeviewItemID)

		If Not @error Then
			; update the product codes table
			$s_Function = '_Database_InsertUpdateProductCodes'
			$v_Parameters = '$as_TPC' & '#' & $i_Updates_Id
			$i_lineNumber = 274
			__Database_InsertUpdateProductCodes($as_TPC, $i_Updates_Id)

			If Not @error Then
				; update the languages table
				__Database_InsertUpdateLanguages($as_TPC, $i_Updates_Id)
				If Not @error Then Return SetError($SQLITE_OK, _Log_Header())
			EndIf
		EndIf
	EndIf

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>_Database_InsertUpdate

Func _Database_RunSQLite_Exec($s_Sql, $h_DB = -1)
	_Log_Header('_Database_RunSQLite_Exec')

	Local $s_Function = '_SQLite_Exec'
	Local $v_Parameters = $h_DB & '#' & $s_Sql
	Local $i_lineNumber = 340
	Local $i_Ret = _SQLite_Exec($h_DB, $s_Sql)
	If $i_Ret = $SQLITE_OK Then Return SetError(_Log_Write('_SQLite_Exec was Successful'), _Log_Header(), $i_Ret) ; success

	; write fatal error info
	_Log_WriteFatalInfo($s_Function, 'Database', $i_lineNumber, $v_Parameters, @error, $i_Ret, _SQLite_ErrMsg())
	Return SetError(1, _Log_Header(), $i_Ret)
EndFunc   ;==>_Database_RunSQLite_Exec

Func _Database_RunSQLite_Query($s_Sql, $s_DBPath = -1)
	_Log_Header('_Database_RunSQLite_Query')

	Local $h_Query = 0 ; handle for returned query
	Local $av_Data = 0 ; array for return
	Local $s_Function = '_SQLite_Query'
	Local $v_Parameters = $s_DBPath & '#' & $s_Sql
	Local $i_lineNumber = 296
	Local $i_Ret = _SQLite_Query($s_DBPath, $s_Sql, $h_Query)

	If Not @error Then
		$s_Function = '_SQLite_FetchData'
		$v_Parameters = '$h_Query#$av_Data'
		$i_lineNumber = 302
		$i_Ret = _SQLite_FetchData($h_Query, $av_Data)

		If Not @error Then
			_Log_Write('Query completed successfully')

			$s_Function = '_SQLite_QueryFinalize'
			$v_Parameters = '$h_Query'
			$i_lineNumber = 310
			$i_Ret = _SQLite_QueryFinalize($h_Query)

			If Not @error Then
				_Log_Write('Query finalized successfully')

				If IsArray($av_Data) Then
					_Log_Write('Query returned an array')
				Else
					_Log_Write('Query did not return an array')
				EndIf

				Return SetError($SQLITE_OK, _Log_Header(), $av_Data)
			EndIf
		ElseIf $i_Ret = $SQLITE_DONE Then
			_Log_Write('Query completed successfully')
			_Log_Write('No values were returned from the specified query (' & $s_Sql & ')', 1)
			Return SetError($SQLITE_OK, _Log_Header(), 0)
		EndIf
	EndIf

	_Log_WriteFatalInfo($s_Function, 'Database', $i_lineNumber, $v_Parameters, @error, $i_Ret, _SQLite_ErrMsg())
	Return SetError(1, _Log_Header(), 0)
EndFunc   ;==>_Database_RunSQLite_Query

Func _Database_RunSQLite_QuerySingleRow($s_Sql, $s_DBPath = -1)
	_Log_Header('_Database_RunSQLite_QuerySingleRow')

	Local $av_Data = '' ; array to hold returned query
	Local $s_Function = '_SQLite_QuerySingleRow'
	Local $v_Parameters = $s_DBPath & '#' & $s_Sql & '#' & '$av_Data'
	Local $i_lineNumber = 355
	Local $i_Ret = _SQLite_QuerySingleRow($s_DBPath, $s_Sql, $av_Data)

	If $i_Ret = $SQLITE_OK Then Return SetError(_Log_Write('_SQLite_Query was Successful'), _Log_Header(), $av_Data[0]) ; success
	If $i_Ret = $SQLITE_DONE Then Return SetError(_Log_Write('_SQLite_Query was Successful¦No values were returned from the specified query (' & $s_Sql & ')', 1), _Log_Header(), 0) ; success but no values found

	_Log_WriteFatalInfo($s_Function, 'Database', $i_lineNumber, $v_Parameters, @error, $i_Ret, _SQLite_ErrMsg())
	Return SetError(1, _Log_Header(), $i_Ret)
EndFunc   ;==>_Database_RunSQLite_QuerySingleRow

#Region #### Database Creation #######################
Func _Database_Close()
	_Log_Header('_Database_Close')
	_Log_Write('Closing Database')

	Local $s_Function = '_SQLite_Close'
	Local $v_Parameters = ''
	Local $i_lineNumber = 372
	Local $i_Ret = _SQLite_Close()
	If Not @error Then Return SetError(@error, _Log_Write('Database closed', 1), _Log_Header())

	_Log_WriteFatalInfo($s_Function, 'Database', $i_lineNumber, $v_Parameters, @error, $i_Ret, _SQLite_ErrMsg())
	Return SetError(1, 0, _Log_Header())
EndFunc   ;==>_Database_Close

Func _Database_CreateDB($s_DBPath = 'D:\Updates.db')
	_Log_Header('_Database_CreateDB')

	If FileExists($s_DBPath) Then FileDelete($s_DBPath)

	Local $s_Function = '_Database_Open'
	Local $v_Parameters = $s_DBPath
	Local $i_lineNumber = 387
	Local $h_Database = _Database_Open($v_Parameters) ; open the database

	If Not @error Then
		$s_Function = '__Database_CreateAllTables'
		$v_Parameters = ''
		$i_lineNumber = 393
		If __Database_CreateAllTables() = $SQLITE_OK Then Return SetError($SQLITE_OK, _Log_Header(), $SQLITE_OK) ; create the database tables
	EndIf

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header(), $h_Database)
EndFunc   ;==>_Database_CreateDB

Func _Database_Open($s_DBPath = '')
	_Log_Header('_Database_Open')
	_Log_Write('Opening Database')
	_Log_Write('Database: ' & $s_DBPath)

	Local $s_Function = '_SQLite_Open'
	Local $v_Parameters = $s_DBPath
	Local $i_lineNumber = 408
	Local $h_Database = _SQLite_Open($v_Parameters) ; open the database

	If Not @error Then ; no errors occured
		_Log_Write('Handle: ' & $h_Database, 1)
		Return SetError($SQLITE_OK, _Log_Header(), $h_Database) ; return the handle (not needed currently as only 1 DB used)
	EndIf

	; write error info
	_Log_WriteFatalInfo($s_Function, 'Database', $i_lineNumber, $v_Parameters, @error, $h_Database, _SQLite_ErrMsg())
	Return SetError(1, 0, _Log_Header())
EndFunc   ;==>_Database_Open

Func _Database_LoadDll($s_Path = @TempDir)
	_Log_Header('_Database_LoadDll')
	_Log_Write('Loading the SQLite Dll')
	_Log_Write('Destination Path: ' & $s_Path)
	_Log_Write('Path Exists     : ' & FileExists($s_Path))

	$s_Path &= '\' ; add a trailing backslash for FileInstall

	Local _
			$s_DLL = 'sqlite3.dll', _ ; name of the sqlite dll
			$s_Function = 'FileInstall' ; running function
	Local _
			$v_Parameters = 'V:\AutoIt\Office Integrator v2\Files\' & $s_DLL & '#' & $s_Path & '#' & $FC_OVERWRITE

	Local $i_lineNumber = 435
	Local $i_UseDefinedDLL = FileInstall('V:\AutoIt\Office Integrator v2\Files\sqlite3.dll', $s_Path, $FC_OVERWRITE) ; returns 1 if installed

	If $i_UseDefinedDLL Then ; use the a defined dll
		Local $s_DLLPath = $s_Path & $s_DLL

		If FileExists($s_DLLPath) Then
			$s_Function = '_SQLite_Startup'
			$v_Parameters = $s_DLLPath & '#' & False & '#' & $i_UseDefinedDLL
			$i_lineNumber = 444
			Local $v_Ret = _SQLite_Startup($s_DLLPath, False, $i_UseDefinedDLL) ; load the sqlite dll using the dll defined by $s_DllPath

			If Not @error Then
				_Log_Write('¦' & $v_Ret & ' successfully loaded')
				_Log_Write('File Path: ' & $v_Ret, 1)
				_Log_Write('Version  : ' & FileGetVersion($v_Ret), 1)
				Return _Log_Header()
			Else
				#Region #### Fatal error processing ###################
				_Log_WriteFatalInfo($s_Function, 'Database', $i_lineNumber, $v_Parameters, @error, @extended, $v_Ret)
				#EndRegion #### Fatal error processing ###################
			EndIf
		Else
			#Region #### Fatal error processing ###################
			_Log_WriteFatalInfo($s_Function, 'Database', $i_lineNumber, $v_Parameters, 1, @extended, $s_DLLPath & ' was not found')
			#EndRegion #### Fatal error processing ###################
		EndIf
	Else
		#Region #### Fatal error processing ###################
		_Log_WriteFatalInfo($s_Function, 'Database', $i_lineNumber, $v_Parameters, 1, @extended, $s_DLLPath & ' was not found')
		#EndRegion #### Fatal error processing ###################
	EndIf

	Return SetError(1, @extended, _Log_Header())
EndFunc   ;==>_Database_LoadDll

Func _Database_UnloadDll()
	_Log_Header('_Database_UnloadDll')
	_Log_Write('Unloading Sqlite.dll')
	_SQLite_Shutdown()
	_Log_Header('')
EndFunc   ;==>_Database_UnloadDll
#EndRegion #### Database Creation #######################

; #INTERNAL_USE_ONLY# ===========================================================================================================
#Region #### Table Creation ##########################
Func __Database_CreateTable($s_TableName, $s_Columns)
	_Log_Header('__Database_CreateTable')
	_Log_Write('Creating Table...')
	_Log_Write('Table Name: ' & $s_TableName, 1)

	Local $s_Sql = _
			"CREATE TABLE IF NOT EXISTS " & '"' & $s_TableName & '"' & _
			" (" & @CRLF & $s_Columns & ");"

	Local $s_Function = '_Database_RunSQLite_Exec'
	Local $v_Parameters = $s_Sql
	Local $i_lineNumber = 490
	Local $i_Ret = _Database_RunSQLite_Exec($v_Parameters)
	If $i_Ret = $SQLITE_OK Then Return SetError(_Log_Write('Table Created', 1), _Log_Header(), $i_Ret)

	; log fatal errors
	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>__Database_CreateTable

Func __Database_CreateAllTables()
	_Log_Header('__Database_CreateAllTables')

	Local _
			$as_TableNames = _Database_GetTableNames(), _ ; get an array of table names
			$as_TableColumns = ''
	Local _
			$s_Fields = '', _
			$s_Function = ''
	Local _
			$v_Parameters = ''
	Local _
			$i_Ret = $SQLITE_OK, _
			$i_lineNumber = 512

	For $i = 1 To $as_TableNames[0] ; loop through the table names
		$as_TableColumns = _Database_GetTableColumns($i)

		Switch $i
			Case $TBN_LANGUAGES ; languages
				$s_Fields = __Database_LanguageTableGetFields($as_TableNames[$i], $as_TableColumns)
			Case $TBN_PRODUCTCODES ; product codes
				$s_Fields = __Database_ProductCodeTableGetFields($as_TableNames[$i], $as_TableColumns)
			Case $TBN_UPDATES ; updates
				$s_Fields = __Database_UpdatesTableGetFields($as_TableColumns)
			Case $TBN_UPDLANGLINK ; link between updates and languages
				$s_Fields = __Database_UpdLangLinkTableGetFields($as_TableNames, $as_TableColumns)
			Case $TBN_UPDPRODLINK ; link between updates and product codes
				$s_Fields = __Database_UpdProdLinkTableGetFields($as_TableNames, $as_TableColumns)
			Case Else
				ExitLoop
		EndSwitch

		$s_Function = '__Database_CreateTable'
		$v_Parameters = $as_TableNames[$i] & '#' & $s_Fields
		$i_lineNumber = 534
		$i_Ret = __Database_CreateTable($as_TableNames[$i], $s_Fields) ; create the table, exit on errors
		If $i_Ret <> $SQLITE_OK Then ExitLoop
	Next

	If $i_Ret = $SQLITE_OK Then Return SetError(0, _Log_Header(), $i_Ret)

	; log fatal errors
	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>__Database_CreateAllTables

Func __Database_LanguageTableGetFields($s_TableName, ByRef $as_TableColumns)
	Return _
			'  "' & $as_TableColumns[$LANG_ID_COL] & '" INTEGER PRIMARY KEY,' & @CRLF & _
			'  "' & $as_TableColumns[$LANG_LCID_COL] & '" TEXT NOT NULL);' & @CRLF & @CRLF & _
			'CREATE UNIQUE INDEX "idxLanguage" ON ' & '"' & $s_TableName & '"' & ' ("' & $as_TableColumns[$LANG_LCID_COL] & '"'
EndFunc   ;==>__Database_LanguageTableGetFields

Func __Database_ProductCodeTableGetFields($s_TableName, ByRef $as_TableColumns)
	Return _
			'  "' & $as_TableColumns[$PCD_ID_COL] & '" INTEGER PRIMARY KEY,' & @CRLF & _
			'  "' & $as_TableColumns[$PCD_GUID_COL] & '" TEXT NOT NULL);' & @CRLF & @CRLF & _
			'CREATE UNIQUE INDEX "idxProductCode" ON ' & '"' & $s_TableName & '"' & ' ("' & $as_TableColumns[$PCD_GUID_COL] & '"'
EndFunc   ;==>__Database_ProductCodeTableGetFields

Func __Database_UpdatesTableGetFields(ByRef $as_TableColumns)
	Return _
			'  "' & $as_TableColumns[$UPD_ID_COL] & '" INTEGER PRIMARY KEY,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_KBARTICLENUMBER_COL] & '" INTEGER NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_DESCRIPTION_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_CLASSIFICATION_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_STDPACKAGENAME_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_TARGETPRODUCTNAME_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_BUILDNUMBER_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_CREATIONTIMEUTC_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_MOREINFOURL_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_PLATFORM_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_FILESIZE_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_SUPERSEDEDBY_COL] & '" TEXT,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_SUPERSEDES_COL] & '" TEXT,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_UPDATECATALOGURLS_COL] & '" TEXT,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_MAJORVERSION_COL] & '" INTEGER NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_MSPSOURCEDIRECTORY_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_MSPFILENAME_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_MSPFILEHASH_COL] & '" TEXT NOT NULL,' & @CRLF & _ ; #### should this be unique? very small chance of collisions ####
			'  "' & $as_TableColumns[$UPD_MSPMODIFIEDTIME_COL] & '" INTEGER NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_ORIGINALPATH_COL] & '" TEXT NOT NULL,' & @CRLF & _
			'  "' & $as_TableColumns[$UPD_CHILDID_COL] & '" INTEGER NOT NULL' ; #### should this be unique? it's generated on treeview item creation so should be unique ####
EndFunc   ;==>__Database_UpdatesTableGetFields

Func __Database_UpdLangLinkTableGetFields(ByRef $as_TableNames, ByRef $as_TableColumns)
	Return _
			'  "' & $as_TableColumns[$UALLINK_UPDID_COL] & '" INTEGER CONSTRAINT "fkUpdLangUpd" REFERENCES "' & $as_TableNames[$TBN_UPDATES] & '"("' & $as_TableColumns[$UPD_ID_COL] & '") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,' & @CRLF & _
			'  "' & $as_TableColumns[$UALLINK_LANGID_COL] & '" INTEGER CONSTRAINT "fkUpdLangLang" REFERENCES "' & $as_TableNames[$TBN_LANGUAGES] & '"("' & $as_TableColumns[$LANG_ID_COL] & '") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,' & @CRLF & _
			'  CONSTRAINT "" PRIMARY KEY ("' & $as_TableColumns[$UALLINK_UPDID_COL] & '", "' & $as_TableColumns[$UALLINK_LANGID_COL] & '"));' & @CRLF & @CRLF & _
			'CREATE INDEX "idxUpdLangLangUpd" ON ' & '"' & $as_TableNames[$TBN_UPDLANGLINK] & '"' & ' ("' & $as_TableColumns[$UALLINK_LANGID_COL] & '", "' & $as_TableColumns[$UALLINK_UPDID_COL] & '"'
EndFunc   ;==>__Database_UpdLangLinkTableGetFields

Func __Database_UpdProdLinkTableGetFields(ByRef $as_TableNames, ByRef $as_TableColumns)
	Return _
			'  "' & $as_TableColumns[$UAPCLINK_UPID_COL] & '" INTEGER CONSTRAINT "fkUpdProdUpd" REFERENCES "' & $as_TableNames[$TBN_UPDATES] & '"("' & $as_TableColumns[$UPD_ID_COL] & '") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,' & @CRLF & _
			'  "' & $as_TableColumns[$UAPCLINK_PRODID_COL] & '" INTEGER CONSTRAINT "fkUpdProdProd" REFERENCES "' & $as_TableNames[$TBN_PRODUCTCODES] & '"("' & $as_TableColumns[$PCD_ID_COL] & '") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,' & @CRLF & _
			'  CONSTRAINT "" PRIMARY KEY ("' & $as_TableColumns[$UAPCLINK_UPID_COL] & '", "' & $as_TableColumns[$UAPCLINK_PRODID_COL] & '"));' & @CRLF & @CRLF & _
			'CREATE INDEX "idxUpdProdProdUpd" ON ' & '"' & $as_TableNames[$TBN_UPDPRODLINK] & '"' & ' ("' & $as_TableColumns[$UAPCLINK_PRODID_COL] & '", "' & $as_TableColumns[$UAPCLINK_UPID_COL] & '"'
EndFunc   ;==>__Database_UpdProdLinkTableGetFields
#EndRegion #### Table Creation ##########################

#Region #### Updates Table Actions ###################
Func __Database_GetUpdateMetadata(ByRef $as_Metadata)
	Local $s_Values = $as_Metadata[1] & ',' ; set kb article number as integer

	For $i = 2 To $as_Metadata[0]
		$s_Values &= _SQLite_FastEscape($as_Metadata[$i]) & ',' ; escape remaining string values
	Next

	Return $s_Values
EndFunc   ;==>__Database_GetUpdateMetadata

Func __Database_GetUpdatePlatform(ByRef $as_TPC)
	Local $s_Platform = _OP_GetPlatform($as_TPC)

	_Log_Write('Platform          : ' & $s_Platform)
	Return _SQLite_FastEscape($s_Platform)
EndFunc   ;==>__Database_GetUpdatePlatform

Func __Database_GetUpdateFileSize($s_UpdatePath)
	Local $i_Size = _FAF_FileGetSize($s_UpdatePath)

	_Log_Write('File Size         : ' & $i_Size)
	Return _SQLite_FastEscape($i_Size)
EndFunc   ;==>__Database_GetUpdateFileSize

Func __Database_GetUpdateSupersededBy()
	Return 'NULL'
EndFunc   ;==>__Database_GetUpdateSupersededBy

Func __Database_GetUpdateSupersedes()
	Return 'NULL'
EndFunc   ;==>__Database_GetUpdateSupersedes

Func __Database_GetUpdateUpdateCatalogUrls()
	Return 'NULL'
EndFunc   ;==>__Database_GetUpdateUpdateCatalogUrls

Func __Database_GetUpdateMajorVersion(ByRef $as_TPC)
	Local $i_Version = _OP_GetMajorVersion($as_TPC)

	_Log_Write('Major Version     : ' & $i_Version)
	Return $i_Version
EndFunc   ;==>__Database_GetUpdateMajorVersion

Func __Database_GetUpdateMSPSourceDirectory($s_UpdatePath)
	Local $s_Source = _FAF_DirectoryGetPathFromPath($s_UpdatePath)

	_Log_Write('Source Directory  : ' & $s_Source)
	Return _SQLite_FastEscape($s_Source)
EndFunc   ;==>__Database_GetUpdateMSPSourceDirectory

Func __Database_GetUpdateMspFileName($s_UpdatePath)
	Local $s_FileName = _FAF_FileGetNameFromPath($s_UpdatePath)

	_Log_Write('MSP File Name     : ' & $s_FileName)
	Return _SQLite_FastEscape($s_FileName)
EndFunc   ;==>__Database_GetUpdateMspFileName

Func __Database_GetUpdateMSPFileHash($s_UpdatePath)
	Local $s_MD5 = _FAF_FileGetMD5($s_UpdatePath)

	_Log_Write('File MD5 Hash     : ' & $s_MD5)
	Return _SQLite_FastEscape($s_MD5)
EndFunc   ;==>__Database_GetUpdateMSPFileHash

Func __Database_GetUpdateMSPModifiedTime($s_UpdatePath)
	Local $i_Modified = Number(FileGetTime($s_UpdatePath, $FT_MODIFIED, $FT_STRING))

	_Log_Write('Modified Time     : ' & $i_Modified)
	Return $i_Modified
EndFunc   ;==>__Database_GetUpdateMSPModifiedTime

Func __Database_GetUpdateOriginalPath($s_UpdatePath)
	_Log_Write('OriginalPath      : ' & $s_UpdatePath)
	Return _SQLite_FastEscape($s_UpdatePath)
EndFunc   ;==>__Database_GetUpdateOriginalPath

Func __Databse_InsertUpdateDetails(ByRef $as_Metadata, ByRef $as_TPC, $s_UpdatePath, $i_TreeviewItemID)
	_Log_Header('_Database_InsertUpdateProductCodes')

	Local $s_Values = _ ; organise values string format for insert
			'NULL,' & _ ; table ID, auto increments
			__Database_GetUpdateMetadata($as_Metadata) & _ ; returns 8 strings in 1 with trailing ','
			__Database_GetUpdatePlatform($as_TPC) & ',' & _ ; returns string
			__Database_GetUpdateFileSize($s_UpdatePath) & ',' & _ ; returns string
			__Database_GetUpdateSupersededBy() & ',' & _ ; returns null for time being
			__Database_GetUpdateSupersedes() & ',' & _ ; returns null for time being
			__Database_GetUpdateUpdateCatalogUrls() & ',' & _ ; returns null for time being
			__Database_GetUpdateMajorVersion($as_TPC) & ',' & _ ; returns number
			__Database_GetUpdateMSPSourceDirectory($s_UpdatePath) & ',' & _ ; returns string
			__Database_GetUpdateMspFileName($s_UpdatePath) & ',' & _ ; returns string
			__Database_GetUpdateMSPFileHash($s_UpdatePath) & ',' & _ ; returns string
			__Database_GetUpdateMSPModifiedTime($s_UpdatePath) & ',' & _ ; returns number
			__Database_GetUpdateOriginalPath($s_UpdatePath) & ',' & _ ; returns string
			$i_TreeviewItemID ; integer

	; create an insert the row into the updates table
	Local _
			$s_TableName = _Database_GetTableNames($TBN_UPDATES), _
			$s_Function = '_Database_InsertRow'
	Local $v_Parameters = $s_TableName & '#' & $s_Values
	Local $i_lineNumber = 703
	Local $i_Updates_Id = _Database_InsertRow($s_TableName, $s_Values)
	If Not @error Then Return SetError($SQLITE_OK, _Log_Header(), $i_Updates_Id)

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>__Databse_InsertUpdateDetails

#EndRegion #### Updates Table Actions ###################

#Region #### Language Table Actions ##################
Func __Database_InsertUpdateLanguages(ByRef $as_TPC, $i_Updates_Id)
	_Log_Header('__Database_InsertUpdateLanguages')

	Local _
			$s_Sql = '', _
			$s_Function = '_Database_RunSQLite_QuerySingleRow'
	Local _
			$v_Parameters = ''
	Local _
			$i_Language_Id = 0, _
			$i_lineNumber = 724
	Local _
			$as_TableNames = _Database_GetTableNames(), _ ; array of table names
			$as_LanguageCols = _Database_GetTableColumns($TBN_LANGUAGES) ; array of table columns

	; loop through the TPC array and check the language table for previous entries
	For $i = 0 To UBound($as_TPC) - 1
		$s_Sql = "SELECT " & '"' & $as_LanguageCols[$LANG_ID_COL] & '"' & _
				" FROM " & '"' & $as_TableNames[$TBN_LANGUAGES] & '"' & _
				" WHERE " & '"' & $as_LanguageCols[$LANG_LCID_COL] & '"' & "=" & '"' & $as_TPC[$i][$OP_TPC_LANGUAGEIDENTIFIER] & '"' & ";"

		$v_Parameters = $s_Sql
		$i_lineNumber = 735
		$i_Language_Id = _Database_RunSQLite_QuerySingleRow($v_Parameters) ; check the language table for current language LCID
		If @error Then ExitLoop

		If Not $i_Language_Id Then
			$s_Sql = _SQLite_FastEscape($as_TPC[$i][$OP_TPC_LANGUAGEIDENTIFIER])
			$s_Function = '_Database_RunSQLite_Exec'
			$v_Parameters = $as_TableNames[$TBN_LANGUAGES] & '#' & $s_Sql
			$i_lineNumber = 744
			$i_Language_Id = _Database_InsertRow($as_TableNames[$TBN_LANGUAGES], 'NULL,' & $s_Sql) ; add the LCID to the Languages table
			If @error Then ExitLoop
		EndIf

		; update the updates and product codes link table
		$s_Function = '_Database_RunSQLite_Exec'
		$v_Parameters = $as_TableNames[$TBN_UPDLANGLINK] & '#' & $i_Updates_Id & ',' & $i_Language_Id
		$i_lineNumber = 752
		_Database_InsertRow($as_TableNames[$TBN_UPDLANGLINK], $i_Updates_Id & ',' & $i_Language_Id)
		If Not @error Then Return SetError($SQLITE_OK, _Log_Header())
	Next

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>__Database_InsertUpdateLanguages

Func __Database_GetUpdateLanguage()
EndFunc   ;==>__Database_GetUpdateLanguage
#EndRegion #### Language Table Actions ##################

#Region #### ProductCodes Table Actions ##############
Func __Database_InsertUpdateProductCodes(ByRef $as_TPC, $i_Updates_Id)
	_Log_Header('_Database_InsertUpdateProductCodes')

	Local _
			$s_Sql = '', _
			$s_Function = '_Database_RunSQLite_QuerySingleRow'
	Local _
			$v_Parameters = ''
	Local _
			$i_ProductCode_Id = 0, _
			$i_lineNumber = 776
	Local _
			$as_TableNames = _Database_GetTableNames(), _ ; array of table names
			$as_ProductCodeCols = _Database_GetTableColumns($TBN_PRODUCTCODES)

	; loop through the TPC array and check the ProductCode table for previous entries
	For $i = 0 To UBound($as_TPC) - 1
		$s_Sql = "SELECT " & '"' & $as_ProductCodeCols[$PCD_ID_COL] & '"' & _
				" FROM " & '"' & $as_TableNames[$TBN_PRODUCTCODES] & '"' & _
				" WHERE " & '"' & $as_ProductCodeCols[$PCD_GUID_COL] & '"' & "=" & '"' & $as_TPC[$i][$OP_TPC_PRODUCTCODE] & '"' & ";"

		$v_Parameters = $s_Sql
		$i_lineNumber = 787
		$i_ProductCode_Id = _Database_RunSQLite_QuerySingleRow($v_Parameters) ; check the product code table for current ProductCode
		If @error Then ExitLoop

		If Not $i_ProductCode_Id Then
			; add the product code to the ProductCode table
			$s_Sql = _SQLite_FastEscape($as_TPC[$i][$OP_TPC_PRODUCTCODE])
			$s_Function = '_Database_RunSQLite_Exec'
			$v_Parameters = $as_TableNames[$TBN_PRODUCTCODES] & '#' & $s_Sql
			$i_lineNumber = 796
			$i_ProductCode_Id = _Database_InsertRow($as_TableNames[$TBN_PRODUCTCODES], 'NULL,' & $s_Sql)
			If @error Then ExitLoop
		EndIf

		; update the updates and product codes link table
		$s_Function = '_Database_RunSQLite_Exec'
		$v_Parameters = $as_TableNames[$TBN_UPDPRODLINK] & '#' & $i_Updates_Id & ',' & $i_ProductCode_Id
		$i_lineNumber = 804
		_Database_InsertRow($as_TableNames[$TBN_UPDPRODLINK], $i_Updates_Id & ',' & $i_ProductCode_Id)
		If Not @error Then Return SetError($SQLITE_OK, _Log_Header())
	Next

	_Log_WriteFatalRoute($s_Function, 'Database', $i_lineNumber, $v_Parameters)
	Return SetError(1, _Log_Header())
EndFunc   ;==>__Database_InsertUpdateProductCodes

Func __Database_GetUpdateTargetProductCodes()
EndFunc   ;==>__Database_GetUpdateTargetProductCodes
#EndRegion #### ProductCodes Table Actions ##############