//Copyright (c) 2024 Eric Lendvai MIT License

#include "hb_orm.ch"

#define INPOSSIBLEZERONULLEQUAL chr(2)

#define INPOSSIBLEZERONULLGREATER chr(3)

//=================================================================================================================

#include "hb_orm_sqldata_class_definition.prg"

//-----------------------------------------------------------------------------------------------------------------

method Init() class hb_orm_SQLData

// hb_HCaseMatch(::QueryString,.f.)

return Self

//-----------------------------------------------------------------------------------------------------------------

method IsConnected() class hb_orm_SQLData //Return .t. if has a connection

return (::p_oSQLConnection != NIL .and. ::p_oSQLConnection:GetHandle() > 0)

//-----------------------------------------------------------------------------------------------------------------

method UseConnection(par_oSQLConnection) class hb_orm_SQLData

::p_oSQLConnection := par_oSQLConnection

::p_BackendType := ::p_oSQLConnection:GetBackendType()

::p_SQLEngineType := ::p_oSQLConnection:GetSQLEngineType()

::p_ConnectionNumber := ::p_oSQLConnection:GetConnectionNumber()

::p_Database := ::p_oSQLConnection:GetDatabase()

::p_NamespaceName := ::p_oSQLConnection:GetCurrentNamespaceName() // Will "Freeze" the current connection p_NamespaceName

::p_CreationTimeFieldName := ::p_oSQLConnection:GetCreationTimeFieldName()

::p_ModificationTimeFieldName := ::p_oSQLConnection:GetModificationTimeFieldName()

return Self

//-----------------------------------------------------------------------------------------------------------------

//---------------------------------------------------------------------------------------------------------------

method Echo(par_cText) class hb_orm_SQLData

// return par_cText+trans(::p_SQLEngineType)

// local l_aArray := {{1,2},{3,4},{5,6},{7,8},{9,10}}

// local l_aSubArray

// local l_i

// for each l_aSubArray in l_aArray

// altd()

// l_i := l_aSubArray

// endfor

//Bogus call to force the linker

//el_GetVersion()

return par_cText

//-----------------------------------------------------------------------------------------------------------------

method destroy() class hb_orm_SQLData

// hb_orm_SendToDebugView("hb_orm_sqldata destroy")

::p_oSQLConnection := NIL

return .t.

//-----------------------------------------------------------------------------------------------------------------

method Table(par_xEventId,par_cNamespaceAndTableName,par_cAlias) class hb_orm_SQLData

local l_nPos

local l_aErrors := {}

local l_cNonTableAlias

local l_cNamespaceAndTableName

if pcount() > 0 .and. !empty(::p_oSQLConnection:p_hWharfConfig)

::SetEventId(par_xEventId)

// hb_HCaseMatch(::p_AliasToNamespaceAndTableNames,.f.) No Need to make it case insensitive since Aliases are always converted to lower case

hb_HClear(::p_AliasToNamespaceAndTableNames)

hb_HClear(::p_FieldsAndValues)

asize(::p_Join,0)

asize(::p_ColumnToReturn,0)

asize(::p_Where,0)

asize(::p_GroupBy,0)

asize(::p_Having,0)

asize(::p_OrderBy,0)

::Tally := 0

::p_TableFullPath := ""

::p_CursorName := ""

::p_CursorUpdatable := .f.

::p_LastSQLCommand := ""

::p_LastRunTime := 0

::p_LastUpdateChangedData := .f.

::p_LastDateTimeOfChangeFieldName := ""

::p_AddLeadingBlankRecord := .f.

::p_AddLeadingRecordsCursorName := ""

::p_DistinctMode := 0

::p_Limit := 0

::p_NumberOfFetchedFields := 0 // Used on Select *

asize(::p_FetchedFieldsNames,0)

::p_MaxTimeForSlowWarning := 2.000 // number of seconds

::p_ExplainMode := 0

l_cNonTableAlias := hb_HGetDef(::p_NonTableAliases,lower(par_cNamespaceAndTableName),"")

if !empty(l_cNonTableAlias)

::p_NamespaceAndTableName := l_cNonTableAlias // Will have the correct casing

if pcount() >= 3 .and. !empty(par_cAlias)

::p_TableAlias := lower(par_cAlias)

else

::p_TableAlias := l_cNonTableAlias

endif

else

// if ::p_SQLEngineType == HB_ORM_ENGINETYPE_MYSQL //empty(::p_NamespaceName) // Meaning not on HB_ORM_ENGINETYPE_POSTGRESQL

// ::p_NamespaceAndTableName = ::p_oSQLConnection:CaseTableName(par_cNamespaceAndTableName)

// if pcount() >= 3 .and. !empty(par_cAlias)

// ::p_TableAlias := lower(par_cAlias)

// else

// ::p_TableAlias := lower(::p_NamespaceAndTableName)

// endif

// else

// l_nPos = at(".",par_cNamespaceAndTableName)

// if empty(l_nPos)

// ::p_NamespaceAndTableName := ::p_oSQLConnection:CaseTableName(::p_NamespaceName+"."+par_cNamespaceAndTableName)

// l_nPos = at(".",::p_NamespaceAndTableName)

// else

// ::p_NamespaceAndTableName := ::p_oSQLConnection:CaseTableName(par_cNamespaceAndTableName)

// endif

// if pcount() >= 3 .and. !empty(par_cAlias)

// ::p_TableAlias := lower(par_cAlias)

// else

// ::p_TableAlias := lower(substr(::p_NamespaceAndTableName,l_nPos+1))

// endif

// endif

l_cNamespaceAndTableName := ::p_oSQLConnection:NormalizeTableNameInternal(par_cNamespaceAndTableName)

::p_NamespaceAndTableName := ::p_oSQLConnection:CaseTableName(l_cNamespaceAndTableName)

if empty(::p_NamespaceAndTableName)

AAdd(l_aErrors,{par_cNamespaceAndTableName,NIL,[Auto-Casing Error: Failed To find table "]+par_cNamespaceAndTableName+[".],hb_orm_GetApplicationStack()})

::p_oSQLConnection:LogErrorEvent(::p_cEventId,l_aErrors)

::p_TableAlias := "" // To ensure will crash later on.

else

if pcount() >= 3 .and. !empty(par_cAlias)

::p_TableAlias := lower(par_cAlias)

else

l_nPos = at(".",::p_NamespaceAndTableName)

::p_TableAlias := lower(substr(::p_NamespaceAndTableName,l_nPos+1))

endif

::p_AliasToNamespaceAndTableNames[::p_TableAlias] := ::p_NamespaceAndTableName

endif

endif

::p_Key := 0

else

::p_NamespaceAndTableName := ""

endif

return ::p_NamespaceAndTableName

//-----------------------------------------------------------------------------------------------------------------

method SetEventId(par_xId) class hb_orm_SQLData

if ValType(par_xId) == "N"

::p_cEventId := trans(par_xId)

else

::p_cEventId := left(AllTrim(par_xId),HB_ORM_MAX_EVENTID_SIZE)

endif

return NIL

//-----------------------------------------------------------------------------------------------------------------

method Distinct(par_lMode) class hb_orm_SQLData

::p_DistinctMode := iif(par_lMode,1,0)

return NIL

//-----------------------------------------------------------------------------------------------------------------

method Limit(par_Limit) class hb_orm_SQLData

::p_Limit := par_Limit

return NIL

//-----------------------------------------------------------------------------------------------------------------

method Key(par_iKey) class hb_orm_SQLData //Set the key or retrieve the last used key

if pcount() == 1

::p_Key := par_iKey

else

return ::p_Key

endif

return NIL

//-----------------------------------------------------------------------------------------------------------------

method FieldSet(par_cName,par_nType,par_xValue,par_nPrecision) class hb_orm_SQLData // Called by all other Field* methods. par_nType 1 = Regular Value, 2 = Server Side Expression, 3 = Array, 4 = Time with precision

local l_xResult := NIL

local l_cFieldName

local l_aErrors := {}

local l_nPos

local l_hColumnDefinition

if !empty(par_cName)

// Due to handling NamespaceName+TableName or only TableName, the simplest is to ignore table names info in par_cName.

l_cFieldName := Strtran(allt(par_cName),"->",".") // in case Harbour field notification was used instead of SQL notification.

l_nPos := rat(".",l_cFieldName)

if l_nPos > 0

l_cFieldName := substr(l_cFieldName,l_nPos+1)

endif

l_cFieldName := ::p_oSQLConnection:CaseFieldName(::p_NamespaceAndTableName,l_cFieldName)

if empty(l_cFieldName)

AAdd(l_aErrors,{::p_NamespaceAndTableName,NIL,[Auto-Casing Error: Failed To find Field "]+par_cName+["],hb_orm_GetApplicationStack()})

::p_oSQLConnection:LogErrorEvent(::p_cEventId,l_aErrors)

else

l_hColumnDefinition := ::p_oSQLConnection:GetColumnConfiguration(::p_NamespaceAndTableName,l_cFieldName)

if pcount() >= 3

if par_nType == 4

::p_FieldsAndValues[l_cFieldName] := {par_nType,par_xValue,par_nPrecision}

else

if l_hColumnDefinition["NullZeroEquivalent"] .and. (ValType(par_xValue) == "N") .and. (par_xValue == 0)

::p_FieldsAndValues[l_cFieldName] := {par_nType,nil ,nil}

else

::p_FieldsAndValues[l_cFieldName] := {par_nType,par_xValue,nil}

endif

endif

else

l_xResult := hb_HGetDef(::p_FieldsAndValues, l_cFieldName, NIL)

l_xResult := l_xResult[2]

if hb_IsNil(l_xResult) .and. l_hColumnDefinition["NullZeroEquivalent"]

l_xResult := 0

endif

endif

endif

endif

return l_xResult

//-----------------------------------------------------------------------------------------------------------------

//The following method existed before the new FieldValue(), FieldExpression and FieldArray() methods.

method Field(par_cName,par_xValue) class hb_orm_SQLData //To set a field (par_cName) in the Table() to the value (par_xValue). If par_xValue is not provided, will return the value from previous set field value

local l_xResult

if pcount() == 1

l_xResult := ::FieldSet(par_cName)

else

// if ValType( par_xValue ) == "A" .and. par_xValue[1] == "S"

// l_xResult := ::FieldSet(par_cName,2,par_xValue[2])

// else

// l_xResult := ::FieldSet(par_cName,1,par_xValue)

// endif

if ValType( par_xValue ) == "A"

do case

case left(par_xValue[1],1) == "S" // Server side value

l_xResult := ::FieldSet(par_cName,2,par_xValue[2])

case left(par_xValue[1],1) == "T" // A Harbour Time with precision

l_xResult := ::FieldSet(par_cName,4,par_xValue[2],par_xValue[3])

otherwise

l_xResult := ::FieldSet(par_cName,1,par_xValue)

endcase

else

l_xResult := ::FieldSet(par_cName,1,par_xValue)

endif

endif

return l_xResult

//-----------------------------------------------------------------------------------------------------------------

method FieldValue(par_cName,par_xValue) class hb_orm_SQLData //To set a field (par_cName) in the Table() to the value (par_xValue). If par_xValue is not provided, will return the value from previous set field value

local l_xResult

if pcount() == 1

l_xResult := ::FieldSet(par_cName)

else

l_xResult := ::FieldSet(par_cName,1,par_xValue)

endif

return l_xResult

//-----------------------------------------------------------------------------------------------------------------

method FieldExpression(par_cName,par_cValue) class hb_orm_SQLData //To set a field (par_cName) in the Table() to the value (par_xValue). If par_xValue is not provided, will return the value from previous set field value

local l_xResult := NIL

local l_cValue := par_cValue

local l_hFieldInfo

local l_nFieldDec

local l_aErrors := {}

do case

case pcount() == 1

l_xResult := ::FieldSet(par_cName)

l_cValue := ""

case ::p_SQLEngineType == HB_ORM_ENGINETYPE_MYSQL

do case

case el_IsInlist(upper(par_cValue),"NOW()","NOW")

// Auto-determine the precision parameter of current_timestamp()

l_hFieldInfo := ::p_oSQLConnection:GetFieldInfo(::p_NamespaceAndTableName,par_cName)

if empty(l_hFieldInfo)

AAdd(l_aErrors,{::p_NamespaceAndTableName,NIL,[Auto-Casing Error: Failed To find Field "]+par_cName+["],hb_orm_GetApplicationStack()})

::p_oSQLConnection:LogErrorEvent(::p_cEventId,l_aErrors)

l_cValue := ""

else

l_nFieldDec := hb_HGetDef(l_hFieldInfo,HB_ORM_GETFIELDINFO_FIELDDECIMALS,0)

if l_nFieldDec > 0

l_cValue := "current_timestamp("+alltrim(str(l_nFieldDec))+")"

else

l_cValue := "current_timestamp()"

endif

endif

otherwise

endcase

case ::p_SQLEngineType == HB_ORM_ENGINETYPE_POSTGRESQL

do case

case el_IsInlist(upper(par_cValue),"NOW()","NOW")

l_cValue := "now()"

endcase

endcase

if !empty(l_cValue)

l_xResult := ::FieldSet(par_cName,2,l_cValue)

endif

return l_xResult

//-----------------------------------------------------------------------------------------------------------------

method FieldArray(par_cName,par_xValue) class hb_orm_SQLData //To set a field (par_cName) in the Table() to the value (par_xValue). If par_xValue is not provided, will return the value from previous set field value

local l_xResult

//Arrays are only supported in PostgreSQL

do case

case pcount() == 1

l_xResult := ::FieldSet(par_cName)

case ::p_SQLEngineType == HB_ORM_ENGINETYPE_MYSQL

l_xResult := NIL

case ::p_SQLEngineType == HB_ORM_ENGINETYPE_POSTGRESQL

l_xResult := ::FieldSet(par_cName,3,par_xValue)

endcase

return l_xResult

//-----------------------------------------------------------------------------------------------------------------

method ErrorMessage() class hb_orm_SQLData //Retrieve the error text of the last call to :SQL(), :Get(), :Count(), :Add() :Update() :Delete()

local l_cErrorMessage

if ValType(::p_ErrorMessage) == "A"

l_cErrorMessage := hb_jsonEncode(::p_ErrorMessage)

else

l_cErrorMessage := ::p_ErrorMessage

endif

return l_cErrorMessage

//-----------------------------------------------------------------------------------------------------------------

// method GetFormattedErrorMessage() class hb_orm_SQLData //Retrieve the error text of the last call to .SQL() or .Get() in an HTML formatted Fasion (ELS)

// return iif(empty(::p_ErrorMessage),[],g_OneCellTable(0,0,o_cw.p_Form_Label_Font_Start+[<font color="#FF0000">]+::p_ErrorMessage))

//-----------------------------------------------------------------------------------------------------------------

method Add(par_iKey) class hb_orm_SQLData //Adds a record. par_iKey is optional and can only be used with table with non auto-increment key field

local l_cFields

local l_nSelect

local l_cSQLCommand

local l_cFieldName,l_hFieldInfo

local l_aValue

local l_xValue

local l_cValues

local l_cFieldValue

local l_cArrayValue

local l_oField

local l_aAutoTrimmedFields := {}

local l_aErrors := {}

local l_xKeyFieldValue

local l_nPos

local l_aPrimaryKeyInfo

local l_cPrimaryKeyFieldName

local l_lNonDefaultKey := .f.

::p_ErrorMessage := ""

::Tally := 0

::p_Key := 0

do case

case !::IsConnected()

::p_ErrorMessage := [Missing SQL Connection]

case empty(::p_oSQLConnection:p_hWharfConfig)

::p_ErrorMessage := [WharfConfig structure required]

endcase

if empty(::p_ErrorMessage)

do case

case len(::p_FieldsAndValues) == 0

::p_ErrorMessage = [Missing Fields]

case empty(::p_NamespaceAndTableName)

::p_ErrorMessage = [Missing Table]

otherwise

l_aPrimaryKeyInfo := hb_HGetDef(::p_oSQLConnection:p_hTablePrimaryKeyInfo,::p_NamespaceAndTableName,{"",""})

l_cPrimaryKeyFieldName := l_aPrimaryKeyInfo[PRIMARY_KEY_INFO_NAME]

if empty(l_cPrimaryKeyFieldName)

::p_ErrorMessage = [Failed to find Primary Field Name.]

else

l_nSelect := iif(used(),select(),0)

do case

case ::p_SQLEngineType == HB_ORM_ENGINETYPE_MYSQL

if pcount() == 1

//Used in case the KEY field is not auto-increment

l_cFields := ::p_oSQLConnection:FormatIdentifier(l_cPrimaryKeyFieldName)

l_cValues := Trans(par_iKey)

l_lNonDefaultKey := .t.

else

l_cFields := ""

l_cValues := ""

endif

//Check if a CreationTimeFieldName exists and if yes, set it to now()

if !empty(::p_CreationTimeFieldName) .and. hb_HGetRef(::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD],::p_CreationTimeFieldName)

if !empty(l_cFields)

l_cFields += ","

l_cValues += ","

endif

l_cFields += ::p_oSQLConnection:FormatIdentifier(::p_CreationTimeFieldName)

l_cValues += "current_timestamp()"

endif

//Check if a ModificationTimeFieldName exists and if yes, set it to now()

if !empty(::p_ModificationTimeFieldName) .and. hb_HGetRef(::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD],::p_ModificationTimeFieldName)

if !empty(l_cFields)

l_cFields += ","

l_cValues += ","

endif

l_cFields += ::p_oSQLConnection:FormatIdentifier(::p_ModificationTimeFieldName)

l_cValues += "current_timestamp()"

endif

for each l_oField in ::p_FieldsAndValues

l_cFieldName := l_oField:__enumKey() // Will not fix Field name casing since this was already done in the method Field()

l_hFieldInfo := ::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD][l_cFieldName]

l_aValue := l_oField:__enumValue()

switch l_aValue[1]

case 1 // Value

l_cFieldValue := ""

if !el_AUnpack(::PrepValueForMySQL("adding",l_aValue[2],::p_NamespaceAndTableName,0,l_cFieldName,l_hFieldInfo,@l_aAutoTrimmedFields,@l_aErrors),,@l_cFieldValue)

loop

endif

exit

case 2 // Expression

l_cFieldValue := l_aValue[2]

exit

case 3 // Array

loop

case 4 // Time with precision

l_cFieldValue := ::FormatDateTimeForSQLUpdate(l_aValue[2],l_aValue[3])

exit

otherwise

loop

endswitch

if !empty(l_cFields)

l_cFields += ","

l_cValues += ","

endif

l_cFields += ::p_oSQLConnection:FormatIdentifier(l_cFieldName)

l_cValues += l_cFieldValue

endfor

l_cSQLCommand := [INSERT INTO ]+::p_oSQLConnection:FormatIdentifier(::p_oSQLConnection:NormalizeTableNamePhysical(::p_NamespaceAndTableName))+[ (]+l_cFields+[) VALUES (]+l_cValues+[)]

// l_cSQLCommand := strtran(l_cSQLCommand,"->",".") // Harbour can use "table->field" instead of "table.field"

::p_LastSQLCommand = l_cSQLCommand

if ::p_oSQLConnection:SQLExec(::p_cEventId,l_cSQLCommand)

do case

case pcount() == 1

::p_Key = par_iKey

otherwise

// LastInsertedID := hb_RDDInfo(RDDI_INSERTID,,"SQLMIX",::p_oSQLConnection:GetHandle())

if ::p_oSQLConnection:SQLExec(::p_cEventId,[SELECT LAST_INSERT_ID() as result],"c_DB_Result")

::Tally := 1

if Valtype(c_DB_Result->result) == "C"

::p_Key := val(c_DB_Result->result)

else

::p_Key := c_DB_Result->result

endif

// ::SQLSendToLogFileAndMonitoringSystem(0,0,l_cSQLCommand+[ -> Key = ]+trans(::p_Key))

else

// ::SQLSendToLogFileAndMonitoringSystem(0,1,l_cSQLCommand+[ -> Failed Get Added Key])

::p_ErrorMessage = [Failed To Get Added KEY]

endif

CloseAlias("c_DB_Result")

endcase

else

//Failed To Add

// ::SQLSendToLogFileAndMonitoringSystem(0,1,l_cSQLCommand+[ -> ]+::p_ErrorMessage)

::p_ErrorMessage := ::p_oSQLConnection:GetSQLExecErrorMessage()

// hb_orm_SendToDebugView(::p_ErrorMessage)

endif

case ::p_SQLEngineType == HB_ORM_ENGINETYPE_POSTGRESQL

if pcount() == 1

//Used in case the KEY field is not auto-increment

l_cFields := ::p_oSQLConnection:FormatIdentifier(l_cPrimaryKeyFieldName)

l_cValues := Trans(par_iKey)

l_lNonDefaultKey := .t.

else

l_cFields := ""

l_cValues := ""

endif

//Check if a CreationTimeFieldName exists and if yes, set it to now()

if !empty(::p_CreationTimeFieldName) .and. hb_HGetRef(::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD],::p_CreationTimeFieldName)

if !empty(l_cFields)

l_cFields += ","

l_cValues += ","

endif

l_cFields += ::p_oSQLConnection:FormatIdentifier(::p_CreationTimeFieldName)

l_cValues += "now()"

endif

//Check if a ModificationTimeFieldName exists and if yes, set it to now()

if !empty(::p_ModificationTimeFieldName) .and. hb_HGetRef(::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD],::p_ModificationTimeFieldName)

if !empty(l_cFields)

l_cFields += ","

l_cValues += ","

endif

l_cFields += ::p_oSQLConnection:FormatIdentifier(::p_ModificationTimeFieldName)

l_cValues += "now()"

endif

for each l_oField in ::p_FieldsAndValues

l_cFieldName := l_oField:__enumKey() // Will not fix Field name casing since this was already done in the method Field()

l_hFieldInfo := ::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD][l_cFieldName]

l_aValue := l_oField:__enumValue()

switch l_aValue[1]

case 1 // Value

l_cFieldValue := ""

if !el_AUnpack(::PrepValueForPostgreSQL("adding",l_aValue[2],::p_NamespaceAndTableName,0,l_cFieldName,l_hFieldInfo,@l_aAutoTrimmedFields,@l_aErrors),,@l_cFieldValue)

loop

endif

exit

case 2 // Expression

l_cFieldValue := l_aValue[2]

exit

case 3 // Array

//Example: array['614417fb-9aec-4a6a-961a-12c9b3f58985','11111111-2222-3333-4444-000000000001']::uuid[]

l_cFieldValue := "array["

for each l_xValue in l_aValue[2]

l_cArrayValue := ""

if el_AUnpack(::PrepValueForPostgreSQL("adding",l_xValue,::p_NamespaceAndTableName,0,l_cFieldName,l_hFieldInfo,@l_aAutoTrimmedFields,@l_aErrors),,@l_cArrayValue)

if l_xValue:__enumindex > 1

l_cFieldValue += ","

endif

//Will be casting the entire array afterwards, will remove any casting in l_cFieldValue

l_nPos := at("::",l_cArrayValue)

if l_nPos > 0

l_cArrayValue := left(l_cArrayValue,l_nPos-1)

endif

l_cFieldValue += l_cArrayValue

else

loop

endif

endfor

l_cFieldValue += "]::"+::GetPostgreSQLCastForFieldType(l_hFieldInfo[HB_ORM_SCHEMA_FIELD_TYPE],;

hb_HGetDef(l_hFieldInfo,HB_ORM_SCHEMA_FIELD_LENGTH,0),;

hb_HGetDef(l_hFieldInfo,HB_ORM_SCHEMA_FIELD_DECIMALS,0))+"[]"

exit

case 4 // Time with precision

l_cFieldValue := ::FormatDateTimeForSQLUpdate(l_aValue[2],l_aValue[3])

exit

otherwise

loop

endswitch

if !empty(l_cFields)

l_cFields += ","

l_cValues += ","

endif

l_cFields += ::p_oSQLConnection:FormatIdentifier(l_cFieldName)

l_cValues += l_cFieldValue

endfor

l_cSQLCommand := [INSERT INTO ]+::p_oSQLConnection:FormatIdentifier(::p_oSQLConnection:NormalizeTableNamePhysical(::p_NamespaceAndTableName))+[ (]+l_cFields+[)]

if l_lNonDefaultKey

l_cSQLCommand += [ OVERRIDING SYSTEM VALUE]

endif

l_cSQLCommand += [ VALUES (]+l_cValues+[) RETURNING ]+::p_oSQLConnection:FormatIdentifier(l_cPrimaryKeyFieldName)

// l_cSQLCommand := strtran(l_cSQLCommand,"->",".") // Harbour can use "table->field" instead of "table.field"

::p_LastSQLCommand = l_cSQLCommand

if ::p_oSQLConnection:SQLExec(::p_cEventId,l_cSQLCommand,"c_DB_Result")

do case

case pcount() == 1

::p_Key = par_iKey

otherwise

::Tally := 1

l_xKeyFieldValue := c_DB_Result->(FieldGet(FieldPos(l_cPrimaryKeyFieldName)))

if Valtype(l_xKeyFieldValue) == "C"

::p_Key := val(l_xKeyFieldValue)

else

::p_Key := l_xKeyFieldValue

endif

// ::SQLSendToLogFileAndMonitoringSystem(0,0,l_cSQLCommand+[ -> Key = ]+trans(::p_Key))

endcase

else

//Failed To Add

::p_ErrorMessage := ::p_oSQLConnection:GetSQLExecErrorMessage()

endif

CloseAlias("c_DB_Result")

endcase

select (l_nSelect)

endif

endcase

endif

if empty(::p_ErrorMessage)

if len(l_aAutoTrimmedFields) > 0

::p_oSQLConnection:LogAutoTrimEvent(::p_cEventId,::p_NamespaceAndTableName,::p_KEY,l_aAutoTrimmedFields)

endif

else

::p_Key := -1

::Tally := -1

endif

if len(l_aErrors) > 0

::p_ErrorMessage := l_aErrors

::p_oSQLConnection:LogErrorEvent(::p_cEventId,l_aErrors)

endif

return (::p_Key > 0)

//-----------------------------------------------------------------------------------------------------------------

method Delete(par_xEventId,par_cNamespaceAndTableName,par_iKey) class hb_orm_SQLData //Delete record. Should be called as .Delete(Key) or .Delete(TableName,Key). The first form require a previous call to .Table(TableName)

local l_nSelect

local l_cSQLCommand

local l_cNonTableAlias

local l_cNamespaceAndTableName

local l_aPrimaryKeyInfo

local l_cPrimaryKeyFieldName

::p_ErrorMessage := ""

::Tally := 0

if pcount() != 3

::p_ErrorMessage := [Invalid number of parameters when calling :Delete()]

endif

if empty(::p_ErrorMessage)

do case

case !::IsConnected()

::p_ErrorMessage := [Missing SQL Connection]

case empty(::p_oSQLConnection:p_hWharfConfig)

::p_ErrorMessage := [WharfConfig structure required]

endcase

endif

if empty(::p_ErrorMessage)

l_cNonTableAlias := hb_HGetDef(::p_NonTableAliases,lower(par_cNamespaceAndTableName),"")

if !empty(l_cNonTableAlias)

l_cNamespaceAndTableName := l_cNonTableAlias // Will have the correct casing

else

// if ::p_SQLEngineType == HB_ORM_ENGINETYPE_MYSQL //empty(::p_NamespaceName) // Meaning not on HB_ORM_ENGINETYPE_POSTGRESQL

// l_cNamespaceAndTableName = ::p_oSQLConnection:CaseTableName(par_cNamespaceAndTableName)

// else

// l_nPos = at(".",par_cNamespaceAndTableName)

// if empty(l_nPos)

// l_cNamespaceAndTableName := ::p_oSQLConnection:CaseTableName(::p_NamespaceName+"."+par_cNamespaceAndTableName)

// // l_nPos = at(".",l_cNamespaceAndTableName)

// else

// l_cNamespaceAndTableName := ::p_oSQLConnection:CaseTableName(par_cNamespaceAndTableName)

// endif

// endif

// if empty(l_cNamespaceAndTableName)

// ::p_ErrorMessage := [Auto-Casing Error: Failed To find table "]+par_cNamespaceAndTableName+[".]

// else

// // ::p_AliasToNamespaceAndTableNames[::p_TableAlias] := l_cNamespaceAndTableName

// endif

l_cNamespaceAndTableName := ::p_oSQLConnection:NormalizeTableNameInternal(par_cNamespaceAndTableName)

l_cNamespaceAndTableName := ::p_oSQLConnection:CaseTableName(l_cNamespaceAndTableName)

if empty(l_cNamespaceAndTableName)

::p_ErrorMessage := [Auto-Casing Error: Failed To find table "]+par_cNamespaceAndTableName+[".]

endif

endif

if empty(::p_ErrorMessage) .and. !empty(l_cNamespaceAndTableName)

l_aPrimaryKeyInfo := hb_HGetDef(::p_oSQLConnection:p_hTablePrimaryKeyInfo,l_cNamespaceAndTableName,{"",""})

l_cPrimaryKeyFieldName := l_aPrimaryKeyInfo[PRIMARY_KEY_INFO_NAME]

if empty(l_cPrimaryKeyFieldName)

::p_ErrorMessage := [Failed to find Primary Field Name.]

endif

endif

do case

case !empty(::p_ErrorMessage)

case empty(l_cNamespaceAndTableName)

::p_ErrorMessage := [Missing Table]

case empty(par_iKey)

::p_ErrorMessage := [Missing ]+upper(l_cPrimaryKeyFieldName)

otherwise

l_nSelect := iif(used(),select(),0)

l_cSQLCommand := [DELETE FROM ]+::p_oSQLConnection:FormatIdentifier(::p_oSQLConnection:NormalizeTableNamePhysical(l_cNamespaceAndTableName))+[ WHERE ]+::p_oSQLConnection:FormatIdentifier(l_cPrimaryKeyFieldName)+[=]+trans(par_iKey)

::p_LastSQLCommand = l_cSQLCommand

if empty(::p_ErrorMessage)

if ::p_oSQLConnection:SQLExec(par_xEventId,l_cSQLCommand)

// ::SQLSendToLogFileAndMonitoringSystem(0,0,l_cSQLCommand)

::Tally := 1

else

::p_ErrorMessage := ::p_oSQLConnection:GetSQLExecErrorMessage()

// ::SQLSendToLogFileAndMonitoringSystem(0,1,l_cSQLCommand+[ -> ]+::p_ErrorMessage)

endif

endif

select (l_nSelect)

endcase

endif

if !empty(::p_ErrorMessage)

::Tally := -1

endif

return empty(::p_ErrorMessage)

//-----------------------------------------------------------------------------------------------------------------

method Update(par_iKey) class hb_orm_SQLData //Update a record in .Table(TableName) where .Field(...) was called first

local l_nSelect

local l_cSQLCommand

local l_cFieldName

local l_hFieldInfo

local l_aValue

local l_xValue

local l_cFieldValue

local l_cArrayValue

local l_oField

local l_aAutoTrimmedFields := {}

local l_aErrors := {}

local l_nPos

local l_aPrimaryKeyInfo

local l_cPrimaryKeyFieldName

if pcount() == 1

::p_KEY = par_iKey

endif

::p_ErrorMessage := ""

::Tally := 0

::p_LastUpdateChangedData := .f.

*::p_LastDateTimeOfChangeFieldName := ""

do case

case !::IsConnected()

::p_ErrorMessage := [Missing SQL Connection]

case empty(::p_oSQLConnection:p_hWharfConfig)

::p_ErrorMessage := [WharfConfig structure required]

endcase

if empty(::p_ErrorMessage) .and. !empty(::p_NamespaceAndTableName)

l_aPrimaryKeyInfo := hb_HGetDef(::p_oSQLConnection:p_hTablePrimaryKeyInfo,::p_NamespaceAndTableName,{"",""})

l_cPrimaryKeyFieldName := l_aPrimaryKeyInfo[PRIMARY_KEY_INFO_NAME]

if empty(l_cPrimaryKeyFieldName)

::p_ErrorMessage := [Failed to find Primary Field Name.]

endif

endif

if empty(::p_ErrorMessage)

do case

case len(::p_FieldsAndValues) == 0

::p_ErrorMessage = [Missing Fields]

case empty(::p_NamespaceAndTableName)

::p_ErrorMessage = [Missing Table]

case empty(::p_KEY)

::p_ErrorMessage = [Missing ]+l_cPrimaryKeyFieldName

otherwise

l_nSelect = iif(used(),select(),0)

do case

case ::p_SQLEngineType == HB_ORM_ENGINETYPE_MYSQL

l_cSQLCommand := ""

//Check if a ModificationTimeFieldName exists and if yes, set it to now()

if !empty(::p_ModificationTimeFieldName) .and. hb_HGetRef(::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD],::p_ModificationTimeFieldName)

if !empty(l_cSQLCommand)

l_cSQLCommand += ","

endif

l_cSQLCommand += ::p_oSQLConnection:FormatIdentifier(::p_ModificationTimeFieldName)+[ = current_timestamp()]

endif

for each l_oField in ::p_FieldsAndValues

l_cFieldName := l_oField:__enumKey() // Will not fix Field name casing since this was already done in the method Field()

l_hFieldInfo := ::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD][l_cFieldName]

l_aValue := l_oField:__enumValue()

switch l_aValue[1]

case 1 // Value

l_cFieldValue := ""

if !el_AUnpack(::PrepValueForMySQL("adding",l_aValue[2],::p_NamespaceAndTableName,0,l_cFieldName,l_hFieldInfo,@l_aAutoTrimmedFields,@l_aErrors),,@l_cFieldValue)

loop

endif

exit

case 2 // Expression

l_cFieldValue := l_aValue[2]

exit

case 3 // Array

loop

case 4 // Time with precision

l_cFieldValue := ::FormatDateTimeForSQLUpdate(l_aValue[2],l_aValue[3])

exit

otherwise

loop

endswitch

if !empty(l_cSQLCommand)

l_cSQLCommand += ","

endif

l_cSQLCommand += ::p_oSQLConnection:FormatIdentifier(l_cFieldName)+[ = ]+l_cFieldValue

endfor

l_cSQLCommand := [UPDATE ]+::p_oSQLConnection:FormatIdentifier(::p_oSQLConnection:NormalizeTableNamePhysical(::p_NamespaceAndTableName))+[ SET ]+l_cSQLCommand+[ WHERE ]+::p_oSQLConnection:FormatIdentifier(l_cPrimaryKeyFieldName)+[ = ]+trans(::p_KEY)

::p_LastSQLCommand = l_cSQLCommand

if ::p_oSQLConnection:SQLExec(::p_cEventId,l_cSQLCommand)

::Tally := 1

// ::SQLSendToLogFileAndMonitoringSystem(0,0,l_cSQLCommand)

::p_LastUpdateChangedData := .t. // _M_ For now I am assuming the record changed. Later on create a generic Store Procedure that will do these data changes.

else

::p_ErrorMessage = [Failed SQL Update.]

// ::SQLSendToLogFileAndMonitoringSystem(0,1,l_cSQLCommand+[ -> ]+::p_ErrorMessage)

endif

case ::p_SQLEngineType == HB_ORM_ENGINETYPE_POSTGRESQL

// M_ find a way to integrate the same concept as the code below. Should the update be a stored Procedure ?

*if !empty(::p_LastDateTimeOfChangeFieldName)

* replace (::p_NamespaceAndTableName+"->"+::p_LastDateTimeOfChangeFieldName) with v_LocalTime

*endif

l_cSQLCommand := ""

//Check if a ModificationTimeFieldName exists and if yes, set it to now()

if !empty(::p_ModificationTimeFieldName) .and. hb_HGetRef(::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD],::p_ModificationTimeFieldName)

if !empty(l_cSQLCommand)

l_cSQLCommand += ","

endif

l_cSQLCommand += ::p_oSQLConnection:FormatIdentifier(::p_ModificationTimeFieldName)+[ = now()]

endif

for each l_oField in ::p_FieldsAndValues

l_cFieldName := l_oField:__enumKey() // Will not fix Field name casing since this was already done in the method Field()

l_hFieldInfo := ::p_oSQLConnection:p_hMetadataTable[::p_NamespaceAndTableName][HB_ORM_SCHEMA_FIELD][l_cFieldName]

l_aValue := l_oField:__enumValue()

switch l_aValue[1]

case 1 // Value

l_cFieldValue := ""

if !el_AUnpack(::PrepValueForPostgreSQL("adding",l_aValue[2],::p_NamespaceAndTableName,0,l_cFieldName,l_hFieldInfo,@l_aAutoTrimmedFields,@l_aErrors),,@l_cFieldValue)

loop

endif

exit

case 2 // Expression

l_cFieldValue := l_aValue[2]

exit

case 3 // Array

//array['614417fb-9aec-4a6a-961a-12c9b3f58985','11111111-2222-3333-4444-000000000001']::uuid[]

l_cFieldValue := "array["

for each l_xValue in l_aValue[2]

l_cArrayValue := ""

if el_AUnpack(::PrepValueForPostgreSQL("adding",l_xValue,::p_NamespaceAndTableName,0,l_cFieldName,l_hFieldInfo,@l_aAutoTrimmedFields,@l_aErrors),,@l_cArrayValue)

if l_xValue:__enumindex > 1

l_cFieldValue += ","

endif

//Will be casting the entire array afterwards, will remove any casting in l_cFieldValue

l_nPos := at("::",l_cArrayValue)

if l_nPos > 0

l_cArrayValue := left(l_cArrayValue,l_nPos-1)

endif

l_cFieldValue += l_cArrayValue

else

loop

endif

endfor

l_cFieldValue += "]::"+::GetPostgreSQLCastForFieldType(l_hFieldInfo[HB_ORM_SCHEMA_FIELD_TYPE],;

hb_HGetDef(l_hFieldInfo,HB_ORM_SCHEMA_FIELD_LENGTH,0),;

hb_HGetDef(l_hFieldInfo,HB_ORM_SCHEMA_FIELD_DECIMALS,0))+"[]"

exit

case 4 // Time with precision

l_cFieldValue := ::FormatDateTimeForSQLUpdate(l_aValue[2],l_aValue[3])

exit

otherwise

loop

endswitch

if !empty(l_cSQLCommand)

l_cSQLCommand += ","

endif

l_cSQLCommand += ::p_oSQLConnection:FormatIdentifier(l_cFieldName)+[ = ]+l_cFieldValue

endfor

l_cSQLCommand := [UPDATE ]+::p_oSQLConnection:FormatIdentifier(::p_oSQLConnection:NormalizeTableNamePhysical(::p_NamespaceAndTableName))+[ SET ]+l_cSQLCommand+[ WHERE ]+::p_oSQLConnection:FormatIdentifier(l_cPrimaryKeyFieldName)+[ = ]+trans(::p_KEY)

::p_LastSQLCommand = l_cSQLCommand

if ::p_oSQLConnection:SQLExec(::p_cEventId,l_cSQLCommand)

::Tally := 1

// ::SQLSendToLogFileAndMonitoringSystem(0,0,l_cSQLCommand)

::p_LastUpdateChangedData := .t. // _M_ For now I am assuming the record changed. Later on create a generic Store Procedure that will do these data changes.

else

::p_ErrorMessage = [Failed SQL Update.]

// ::SQLSendToLogFileAndMonitoringSystem(0,1,l_cSQLCommand+[ -> ]+::p_ErrorMessage)

endif

endcase

select (l_nSelect)

endcase

endif

if empty(::p_ErrorMessage)

if len(l_aAutoTrimmedFields) > 0

::p_oSQLConnection:LogAutoTrimEvent(::p_cEventId,::p_NamespaceAndTableName,::p_KEY,l_aAutoTrimmedFields)

endif

else

::Tally := -1

endif

if len(l_aErrors) > 0

::p_ErrorMessage := l_aErrors

::p_oSQLConnection:LogErrorEvent(::p_cEventId,l_aErrors)

endif

return empty(::p_ErrorMessage)

//-----------------------------------------------------------------------------------------------------------------

method PrepExpression(par_cExpression,...) class hb_orm_SQLData //Used to convert from Source Language syntax to MySQL, and to make parameter static

local l_aParams := { ... }

local l_cChar

local l_nMergeCodeNumber

local l_nPos

local l_cResult

local l_xValue

local l_aErrors := {}

if pcount() > 1 .and. "^" $ par_cExpression

l_cResult := ""

l_nMergeCodeNumber := 0

do case

case ::p_SQLEngineType == HB_ORM_ENGINETYPE_MYSQL

for l_nPos := 1 to len(par_cExpression)

l_cChar := substr(par_cExpression,l_nPos,1)

//l_cChar := par_cExpression[l_nPos]

if l_cChar == "^"

l_nMergeCodeNumber += 1

l_xValue = l_aParams[l_nMergeCodeNumber]

switch valtype(l_xValue)

case "C" // Character string https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

case "M" // Memo field

l_cResult += HB_ORM_INVALUEWITCH+'"'+hb_StrReplace( l_xValue, {'\' => '\\',;

'"' => '\"',;

"'" => "\'"} )+'"'+HB_ORM_INVALUEWITCH

exit

case "N" // Numeric

l_cResult += HB_ORM_INVALUEWITCH+hb_ntoc(l_xValue)+HB_ORM_INVALUEWITCH

exit

case "D" // Date https://dev.mysql.com/doc/refman/8.0/en/datetime.html

// l_xValue := '"'+hb_DtoC(l_xValue,"YYYY-MM-DD")+'"' //_M_ Test on 1753-01-01

l_cResult += HB_ORM_INVALUEWITCH+::FormatDateForSQLUpdate(l_xValue)+HB_ORM_INVALUEWITCH

exit

case "T" // TimeStamp (*) https://dev.mysql.com/doc/refman/8.0/en/datetime.html

// l_xValue := '"'+hb_TtoC(l_xValue,"YYYY-MM-DD","hh:mm:ss")+'"' //_M_ Test on 1753-01-01

l_cResult += HB_ORM_INVALUEWITCH+::FormatDateTimeForSQLUpdate(l_xValue)+HB_ORM_INVALUEWITCH

exit

case "L" // Boolean (logical) https://dev.mysql.com/doc/refman/8.0/en/boolean-literals.html

l_cResult += HB_ORM_INVALUEWITCH+iif(l_xValue,"TRUE","FALSE")+HB_ORM_INVALUEWITCH

exit

case "U" // Undefined (NIL)

l_cResult += HB_ORM_INVALUEWITCH+"NULL"+HB_ORM_INVALUEWITCH

exit

// case "A" // Array

// case "B" // Code-Block

// case "O" // Object

// case "H" // Hash table (*)

// case "P" // Pointer to function, procedure or method (*)

// case "S" // Symbolic name (*)

otherwise

AAdd(l_aErrors,{::p_NamespaceAndTableName,NIL,[Wrong Parameter Type in PrepExpression()],hb_orm_GetApplicationStack()})

::p_oSQLConnection:LogErrorEvent(::p_cEventId,l_aErrors)