Alter table in MariaDB from given Structure
Posted: Wed May 20, 2020 9:05 am
Hi Mr. Rao,
I try to write Alter_Table function according to my knowledge. This function compares cTableName's structure and given new structure and it decided to which colspec is added, changed or deleted.
There is not problem adding or deleting for aColSpec.
But When I changing in Numeric variables type and length is not mine that is described my aNew_Structure. It behaves it like in CreateTable function.
for example, my Numeric, 5 converted to INT, 11. This function always think this variable's spec is changed and start to run AlterTable().
How Can I solve this problem. Is there any function to convert this behavior?
I try to write Alter_Table function according to my knowledge. This function compares cTableName's structure and given new structure and it decided to which colspec is added, changed or deleted.
There is not problem adding or deleting for aColSpec.
But When I changing in Numeric variables type and length is not mine that is described my aNew_Structure. It behaves it like in CreateTable function.
for example, my Numeric, 5 converted to INT, 11. This function always think this variable's spec is changed and start to run AlterTable().
How Can I solve this problem. Is there any function to convert this behavior?
Code: Select all
...
...
lUpdate_Yap := Alter_Table(oCn, cTableName, aNew_Structure)
...
...
*-----------------------------------------------------------------------------------------------
FUNCTION Alter_Table(oCn, cTableName, aNew, lShow)
LOCAL l, lResult := .f., aCompare_Result, cDel_SQL, cOld
DEFAULT lShow := .F.
IF oCn<>nil .AND. oCn:TableExists(cTableName)
aOld := oCn:TableStructure(cTableName)
aCompare_Result := Compare_Two_Table(aOld, aNew)
if lShow
xbrowser aOld slnum TITLE cTableName+" - aOld STRUCTURE"
xbrowser aNew SLNUM TITLE cTableName+" - aNew STRUCTURE"
xbrowser aCompare_Result slnum
ENDIF
IF !EMPTY(aCompare_Result[1]) // EKLENECEK VAR
FOR l:=1 TO LEN(aCompare_Result[1])
oCn:AddColumn( cTableName, aCompare_Result[1][l] )
NEXT
lResult:=.T.
ENDIF
IF !EMPTY(aCompare_Result[2]) // İÇERİĞİ DEĞİŞECEK VAR
FOR l:=1 TO LEN(aCompare_Result[2])
oCn:AlterColumn( cTableName, aCompare_Result[2][l] )
NEXT
lResult:=.T.
ENDIF
IF !EMPTY(aCompare_Result[3]) // SİLİNECEK VAR
FOR l:=1 TO LEN(aCompare_Result[3])
cDel_SQL := "ALTER TABLE "+cTableName+" DROP COLUMN IF EXISTS "+aCompare_Result[3][l,1]
oCn:Execute(cDel_SQL)
NEXT
lResult:=.T.
ENDIF
ENDIF
RETURN lResult
*------------------------------------------------------------------------------------------------
FUNCTION Compare_Two_Table(aOld, aNew)
LOCAL aInsert:={}, aChange := {}, aDelete := {}, i
FOR i:=1 TO LEN(aOld)
IF aOld[i,2]="m"
aOld[i,3]=10
ENDIF
NEXT
FOR i:=1 TO LEN(aNew)
nAt := AScan(aOld, {|a|a[1]=aNew[i,1]})
IF nAt = 0 // Yani bizim table da aradığımız yok ise yenidir.
aAdd(aInsert, aNew[i])
ELSE
IF aOld[nAt,2]<>aNew[i,2] .OR. aOld[nAt,3]<>aNew[i,3] .OR. aOld[nAt,4]<>aNew[i,4]
aAdd(aChange, aNew[i])
ENDIF
ENDIF
NEXT
FOR i:=1 TO LEN(aOld)
nAt := AScan(aNew, {|a|a[1]=aOld[i,1]})
IF nAt = 0 // Yani bizim table da aradığımız yok ise yenidir.
aAdd(aDelete, aOld[i])
ENDIF
NEXT
RETURN {aInsert, aChange, aDelete}