add_primary_keys.txt

  //should run on mono or server side! (apply to selection)

$primaryKeyDefaultName:="PK_UUID"

$logFolder:=Get 4D folder(Logs folder)
$logFileName:="add_primary_key"
$logFileSuffix:=".log"
$logFileIndex:=0
$logPath:=$logFolder+$logFileName+$logFileSuffix
While (Test path name($logPath)=Is a document)
	$logFileIndex:=$logFileIndex+1
	$logPath:=$logFolder+$logFileName+Char(Space)+String($logFileIndex)+$logFileSuffix
End while 

$logFileDocRef:=Create document($logPath)

If (OK=1)
	
	C_TEXT($logMessage)
	USE CHARACTER SET("utf-8";0)
	
	ARRAY LONGINT($TABLE_IDS;0)
	ARRAY TEXT($TABLE_NAMES;0)
	
	Begin SQL
		/* all tables without a primary key */
		SELECT TABLE_ID,TABLE_NAME
		FROM _USER_TABLES
		WHERE _USER_TABLES.TABLE_ID NOT IN 
		(SELECT TABLE_ID FROM _USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P')
		INTO :$TABLE_IDS,:$TABLE_NAMES;
	End SQL
	
	If (Num(Substring(Application version;1;2))<13)
		  //on v12 the above code return 0 tables if no table has primary key
		If (Size of array($TABLE_IDS)=0)
			Begin SQL
				/* all tables */
				SELECT TABLE_ID,TABLE_NAME
				FROM _USER_TABLES
				INTO :$TABLE_IDS,:$TABLE_NAMES;
			End SQL
		End if 
	End if 
	
	$logMessage:="number of tables with no primary key: "+String(Size of array($TABLE_IDS))+"\n"
	SEND PACKET($logFileDocRef;$logMessage)
	
	For ($i;1;Size of array($TABLE_IDS))
		
		$logMessage:="adding primary keys...\n"
		SEND PACKET($logFileDocRef;$logMessage)
		
		$TABLE_ID:=$TABLE_IDS{$i}
		$TABLE_NAME:=$TABLE_NAMES{$i}
		
		ARRAY TEXT($COLUMN_NAMES;0)
		
		Begin SQL
			/* get field names */
			SELECT COLUMN_NAME
			FROM _USER_COLUMNS
			WHERE TABLE_ID = :$TABLE_ID
			INTO :$COLUMN_NAMES;
		End SQL
		
		  //define primary key name
		$primaryKeyIndex:=0
		$primaryKeyName:=$primaryKeyDefaultName
		While (Find in array($COLUMN_NAMES;$primaryKeyName)#-1)
			$primaryKeyIndex:=$primaryKeyIndex+1
			$primaryKeyName:=$primaryKeyDefaultName+String($primaryKeyIndex)
		End while 
		
		If (Num(Substring(Application version;1;2))>13)
			$SQL:="ALTER TABLE "+"["+$TABLE_NAME+"]"+\
			" ADD TRAILING ["+$primaryKeyName+"] UUID AUTO_GENERATE PRIMARY KEY;"
		Else 
			  //trailing not supported
			$SQL:="ALTER TABLE "+"["+$TABLE_NAME+"]"+\
			" ADD ["+$primaryKeyName+"] UUID AUTO_GENERATE PRIMARY KEY;"
		End if 
		
		Begin SQL
			EXECUTE IMMEDIATE :$SQL;
		End SQL
		
		If (OK=1)
			
			$logMessage:=String($TABLE_IDS{$i})+"\t"+$TABLE_NAME+"\t"+$primaryKeyName+"\n"
			SEND PACKET($logFileDocRef;$logMessage)
			
			$COLUMN_ID:=0
			Begin SQL
				/* get primary key field number */
				SELECT _USER_CONS_COLUMNS.COLUMN_ID
				FROM _USER_CONSTRAINTS,_USER_CONS_COLUMNS
				WHERE CONSTRAINT_TYPE = 'P'
				AND _USER_CONS_COLUMNS.TABLE_ID = :$TABLE_ID
				AND _USER_CONS_COLUMNS.CONSTRAINT_ID = _USER_CONSTRAINTS.CONSTRAINT_ID
				INTO :$COLUMN_ID;
			End SQL
			
			If ($COLUMN_ID#0)
				
				$field:=Field($TABLE_ID;$COLUMN_ID)
				ALL RECORDS(Table($TABLE_ID)->)
				$table:=Table($TABLE_ID)  //to avoid compile error in line below
				APPLY TO SELECTION($table->;$field->:=Generate UUID)
				
				If (Num(Substring(Application version;1;2))<13)
					  //on v12 it is possible to add primary key while record is locked
					If (Records in set("LockedSet")=0)
						$logMessage:="updated records: "+String(Records in selection($table->))+"\n"
						SEND PACKET($logFileDocRef;$logMessage)
					Else 
						$logMessage:="could not update records: "+String(Records in set("LockedSet"))+"\n"
						SEND PACKET($logFileDocRef;$logMessage)
					End if 
					USE SET("LockedSet")
					ARRAY LONGINT($lockedRecords;0)
					LONGINT ARRAY FROM SELECTION($table->;$lockedRecords)
					
					  //on v12 is field value Null does not report autouuid nulls; need record number
					For ($j;1;Size of array($lockedRecords))
						$logMessage:=String($lockedRecords{$j})+"\n"
						SEND PACKET($logFileDocRef;$logMessage)
					End for 
				End if 
				
				If (Num(Substring(Application version;1;2))>13)
					$SQL:="ALTER TABLE "+"["+$TABLE_NAME+"] ENABLE LOG;"
					  //no need to create index here; in fact, it will result in error
					  //let 4D automatically add unique index for primary key
					Begin SQL
						EXECUTE IMMEDIATE :$SQL;
					End SQL
				End if 
				
			End if 
			
		Else 
			$logMessage:="error: "+$SQL+"\n"
			SEND PACKET($logFileDocRef;$logMessage)
		End if 
		
	End for 
	
	If (Num(Substring(Application version;1;2))<14)
		
		For ($i;1;Size of array($TABLE_IDS))
			
			$logMessage:="adding primary keys...\n"
			SEND PACKET($logFileDocRef;$logMessage)
			
			$TABLE_ID:=$TABLE_IDS{$i}
			
			$primaryKeyName:=""
			
			Begin SQL
				SELECT _USER_CONS_COLUMNS.COLUMN_NAME
				FROM _USER_CONSTRAINTS,_USER_CONS_COLUMNS
				WHERE CONSTRAINT_TYPE = 'P'
				AND _USER_CONS_COLUMNS.TABLE_ID = :$TABLE_ID
				AND _USER_CONS_COLUMNS.CONSTRAINT_ID = _USER_CONSTRAINTS.CONSTRAINT_ID
				INTO :$primaryKeyName;
			End SQL
			
			$indexName:=$TABLE_NAME+"."+$primaryKeyName
			
			$SQL:="CREATE UNIQUE INDEX "+"["+$indexName+"]"+\
			" ON ["+$TABLE_NAME+"] ("+"["+$primaryKeyName+"]);"
			
			Begin SQL
				EXECUTE IMMEDIATE :$SQL;
			End SQL
			
		End for
		
	end if
	
	USE CHARACTER SET(*)
	CLOSE DOCUMENT($logFileDocRef)
	SHOW ON DISK($logPath)
	
End if