Hi all,
I wonder if there is a method to be able to maintain tables without having to use something made by me,
I'm an absolute beginner
TIA Maurizio Menabue
I wonder if there is a method to be able to maintain tables without having to use something made by me,
I'm an absolute beginner
*------------------------------------------------------------------------------------------------
FUNCTION MaintenanceTable(oCn, aNewStr, cTableName)
*------------------------------------------------------------------------------------------------
LOCAL aOldStr AS ARRAY
LOCAL aTmpStr AS ARRAY
LOCAL aResOld AS ARRAY
LOCAL aResTmp AS ARRAY
LOCAL aInsert AS ARRAY
LOCAL aChange AS ARRAY
LOCAL aDelete AS ARRAY
LOCAL nAt AS NUMERIC
LOCAL nLenOld AS NUMERIC
LOCAL nLenTmp AS NUMERIC
LOCAL nI1 AS NUMERIC
LOCAL nResult AS NUMERIC
LOCAL oRs AS OBJECT
LOCAL oField AS OBJECT
LOCAL cTableTemp AS CHARACTER
aOldStr := {}
aTmpStr := {}
aResOld := {}
aResTmp := {}
aInsert := {}
aChange := {}
aDelete := {}
nAt := 0
nLenOld := 0
nLenTmp := 0
nI1 := 0
nResult := 0
cTableTemp := "TempTable"
*--------------------------------------------------------------------------------------------
*- Verifica se esiste la tabella, se non esiste viene creata
*- Check if the table exists, if it doesn't exist it is created
*--------------------------------------------------------------------------------------------
IF .not. oCn:TableExists(cTableName)
oCn:createTable( cTableName, aNewStr, .F., "utf8" )
RETURN .T.
ENDIF
*--------------------------------------------------------------------------------------------
*- Rilevazione parametri dalla vecchia struttura
*- Parameter acquisition from the old structure
*--------------------------------------------------------------------------------------------
aOldStr := oCn:TableStructure(cTableName)
nLenOld := LEN(aOldStr)
oRs := oCn:RowSet( "select * from " + cTableName)
FOR nI1 := 1 TO nlenOld
nResult := 0
oField := oRs:Fields( nI1 - 1 )
nResult += oField:flags
nResult += IF(oField:lNoNull , 1, 0)
nResult += IF(oField:lReadOnly, 1, 0)
nResult += IF(oField:lAutoInc , 1, 0)
nResult += IF(oField:lPrimary , 1, 0)
nResult += IF(oField:lUnique , 1, 0)
nResult += IF(oField:lKey , 1, 0)
nResult += IF(oField:lNoNull , 1, 0)
nResult += IF(oField:lBinary , 1, 0)
nResult += oField:Len2
IF aOldStr[nI1,2] == "m"
aOldStr[nI1,3] := 10
ENDIF
aadd(aResOld, {aOldStr[nI1,1], aOldStr[nI1,2], aOldStr[nI1,3], aOldStr[nI1,4], aOldStr[nI1,5], nResult})
NEXT
oRs:close()
*--------------------------------------------------------------------------------------------
*- Creazione della nuova struttura tramite una tabella temporanea
*- Creation of the new structure using a temporary table
*--------------------------------------------------------------------------------------------
oCn:DropTable(cTableTemp)
oCn:CreateTable(cTableTemp, aNewStr, .F., "utf8")
aTmpStr := oCn:TableStructure(cTableTemp)
nLenTmp := LEN(aTmpStr)
oRs := oCn:RowSet( "select * from " + cTableTemp)
FOR nI1 := 1 TO nlenTmp
nResult := 0
oField := oRs:Fields( nI1 - 1 )
nResult += oField:flags
nResult += IF(oField:lNoNull , 1, 0)
nResult += IF(oField:lReadOnly, 1, 0)
nResult += IF(oField:lAutoInc , 1, 0)
nResult += IF(oField:lPrimary , 1, 0)
nResult += IF(oField:lUnique , 1, 0)
nResult += IF(oField:lKey , 1, 0)
nResult += IF(oField:lNoNull , 1, 0)
nResult += IF(oField:lBinary , 1, 0)
nResult += oField:Len2
IF aTmpStr[nI1,2] == "m"
aTmpStr[nI1,3] := 10
ENDIF
aadd(aResTmp, {aTmpStr[nI1,1], aTmpStr[nI1,2], aTmpStr[nI1,3], aTmpStr[nI1,4], aTmpStr[nI1,5], nResult, aNewStr[nI1]})
NEXT
oRs:close()
oCn:DropTable(cTableTemp)
*--------------------------------------------------------------------------------------------
*- Preparazione array aInsert, aChange, aDelete
*- Array preparation aInsert, aChange, aDelete
*--------------------------------------------------------------------------------------------
FOR nI1 := 1 TO LEN(aResTmp)
*--------------------------------------------------------------------------------------------
*- Un campo della nuova struttura viene ricercato nella vecchia struttura se non è trovato
*- il campo è da inserire, se invece è trovato si procede con la ricerca della differenze
*- a livello di 'TIPO', 'LEN', 'DEC', condizioni SQL, se ci sono differenze si procede con
*- la modifica.
*-
*- A field in the new structure is searched for in the old structure if it is not found
*- the field is to be inserted, if instead it is found we proceed with the search for the
*- differences at the level of 'TYPE', 'LEN', 'DEC', SQL conditions, etc. if there are
*- differences, proceed with AlterColumn.
*--------------------------------------------------------------------------------------------
nAt := AScan(aResOld, {|a| a[1] = aResTmp[nI1, 1]})
IF nAt = 0
aAdd(aInsert, aResTmp[nI1,7])
ELSE
IF .not. aResOld[nAt,2] == aResTmp[nI1,2] .OR. ;
.not. aResOld[nAt,3] == aResTmp[nI1,3] .OR. ;
.not. aResOld[nAt,4] == aResTmp[nI1,4] .OR. ;
.not. aResOld[nAt,5] == aResTmp[nI1,5] .OR. ;
.not. aResOld[nAt,6] == aResTmp[nI1,6]
aAdd(aChange, aResTmp[nI1,7])
ENDIF
ENDIF
NEXT
*--------------------------------------------------------------------------------------------
*- Se nella vecchia struttura vi è un campo non più presente nella nuova struttura il
*- campo è da eliminare.
*-
*- If in the old structure there is a field that is no longer present in the new structure
*- the field is to be deleted.
*--------------------------------------------------------------------------------------------
FOR nI1 := 1 TO LEN(aResOld)
nAt := AScan(aResTmp, {|a| a[1] = aResOld[nI1, 1]})
IF nAt = 0
aAdd(aDelete, aResOld[nI1,1])
ENDIF
NEXT
*--------------------------------------------------------------------------------------------
*- Azione sul database tramite aInsert, aChange, aDelete
*- Action on the database through aInsert, aChange, aDelete
*--------------------------------------------------------------------------------------------
oCn:lSilent := .T.
IF .not. EMPTY(aInsert)
FOR nI1 := 1 TO LEN(aInsert)
MsgAlert("Inserimento colonna : " + aInsert[nI1,1] + " sulla tabella : " + cTableName, "Avviso")
oCn:AddColumn( cTableName, aInsert[nI1] )
NEXT
ENDIF
IF .not. EMPTY(aChange)
FOR nI1 := 1 TO LEN(aChange)
MsgAlert("Variazione colonna : " + aChange[nI1,1] + " sulla tabella : " + cTableName, "Avviso")
oCn:Execute("ALTER TABLE " + cTableName + " DROP INDEX IF EXISTS " + aChange[nI1,1])
oCn:AlterColumn( cTableName, aChange[nI1] )
NEXT
ENDIF
IF .not. EMPTY(aDelete)
FOR nI1 := 1 TO LEN(aDelete)
MsgAlert("Cancellazione colonna : " + aDelete[nI1] + " sulla tabella : " + cTableName, "Avviso")
oCn:Execute("ALTER TABLE " + cTableName + " DROP COLUMN IF EXISTS " + aDelete[nI1])
NEXT
ENDIF
oCn:lSilent := .F.
RETURN .T.