symmetricds_unqiue_errorfilter.sql

insert into sym_load_filter (load_filter_id, load_filter_type, handle_error_script, source_node_group_id, target_node_group_id, create_time, last_update_time)
values
('unqiue_error_fixer', 'BSH', '', 'central', 'mobil', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

update sym_load_filter set last_update_time = CURRENT_TIMESTAMP, handle_error_script = 
'
//this deletes the conflicting row, so that it can insert the incoming row

import java.util.regex.Pattern;
import java.util.regex.Matcher;
import org.jumpmind.db.model.Table;
import java.util.Map;
import org.jumpmind.symmetric.io.data.CsvData;
import org.jumpmind.db.sql.DmlStatement;
import org.jumpmind.db.sql.DmlStatement.DmlType;
import org.jumpmind.db.sql.Row;
import org.jumpmind.db.model.Column;

//change this method to return the failing columns (if any)
findUniqueErrorAndColumns(String errorMsg, Table table){
	Matcher m = Pattern.compile("\\[SQLITE_[^\\]]*?\\].*?UNIQUE constraint failed: ([^\\)]+)\\)").matcher(errorMsg);
	if(!m.find()){
		return null;
	}else{
		String[] columns = m.group(1).split(",");
		Column[] cols = new Column[columns.length];
		for(int i = 0; i < columns.length; ++i){
			String column = columns[i].split("\\.")[1];
			cols[i] = table.findColumn(column);
		}
		return cols;
	}
}

String preventRecursion = "PREVENT_RECURSIVE_ERROR_FIX";
if(context.get(preventRecursion) != null){
	log.info("prevent recuring call if error inside this script happens");
	return true;
}

Column[] cols = findUniqueErrorAndColumns(error.getMessage(), table);

if(cols == null){
	log.info("no unique constraint error detected");
	return true;
}else if(cols.length == 0){
	log.info("could not find the matching columns");
	return true;
}

Map sourceValues = data.toColumnNameValuePairs(table.getColumnNames(), CsvData.ROW_DATA);
Row row = new Row(cols.length);
for(int i = 0; i < cols.length; ++i){
	row.put(cols[i].getName(), sourceValues.get(cols[i].getName()));
}

DmlStatement dml = engine.getDatabasePlatform().createDmlStatement(DmlType.DELETE, table.getCatalog(), table.getSchema(), table.getName(),
													cols, (Column[])null, (boolean[])null, "");

String sql = dml.buildDynamicDeleteSql(context.getBatch().getBinaryEncoding(), row, false, true);
	
context.findTransaction().execute(sql);
	
try{
	context.put(preventRecursion, true);
	context.getWriter().write(data);
}finally{
	context.remove(preventRecursion);
}

log.info("auto fix UNIQUE error on table ''{}''", table.getName());

return false;

' where load_filter_id = 'unqiue_error_fixer'