package org.jtester.module.dbfit.db.fixture;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.jtester.exception.HasMarkedException;
import org.jtester.fit.JTesterFixture;
import org.jtester.module.database.environment.DBEnvironment;
import org.jtester.module.database.environment.normalise.NameNormaliser;
import org.jtester.module.database.util.DBHelper;
import org.jtester.module.dbfit.db.model.DbAutoGeneratedKeyAccessor;
import org.jtester.module.dbfit.db.model.DbParameterAccessor;
import org.jtester.module.dbfit.db.model.SequenceAccessor;
import org.jtester.module.dbfit.db.model.SymbolAccessQueryBinding;
import org.jtester.module.dbfit.db.model.SymbolAccessSetBinding;
import fit.Binding;
import fit.Parse;
public class InsertFixture extends JTesterFixture {
protected DBEnvironment environment;
protected String tableName;
protected DbParameterAccessor[] accessors;
protected Binding[] columnBindings;
public InsertFixture(DBEnvironment dbEnvironment) {
this.environment = dbEnvironment;
}
public InsertFixture(DBEnvironment dbEnvironment, String tableName) {
this.tableName = tableName;
this.environment = dbEnvironment;
}
public PreparedStatement buildInsertCommand(String tableName, DbParameterAccessor[] accessors) throws SQLException {
String ins = environment.buildInsertCommand(tableName, accessors);
boolean supportsOutputOnInsert = environment.supportsOuputOnInsert();
Connection connection = environment.getConnection();
PreparedStatement cs = supportsOutputOnInsert ? connection.prepareCall(ins) : connection.prepareStatement(ins,
Statement.RETURN_GENERATED_KEYS);
int placeHolderIndex = 0;
List<DbParameterAccessor> sequence = new ArrayList<DbParameterAccessor>();
for (int i = 0; i < accessors.length; i++) {
int direction = accessors[i].getDirection();
if (direction == DbParameterAccessor.SEQUENCE) {
sequence.add(accessors[i]);
} else {
placeHolderIndex++;
accessors[i].bindTo(this, cs, placeHolderIndex);
}
}
for (DbParameterAccessor accessor : sequence) {
placeHolderIndex++;
accessor.bindTo(this, cs, placeHolderIndex);
}
return cs;
}
public void doRows(Parse rows) {
// if table not defined as parameter, read from fixture argument; if
// still not defined, read from first row
if ((tableName == null || tableName.trim().length() == 0) && args.length > 0) {
tableName = args[0];
} else if (tableName == null) {
tableName = rows.parts.text();
rows = rows.more;
}
PreparedStatement statement = null;
try {
initParameters(rows.parts);// init parameters from the first row
statement = buildInsertCommand(tableName, accessors);
Parse row = rows;
int rowNum = 0;
while ((row = row.more) != null) {
insertRowData(statement, row, rowNum++);
right(row);
}
} catch (Throwable e) {
exception(rows.parts, e);
} finally {
DBHelper.closeStatement(statement);
statement = null;
}
}
protected void initParameters(Parse headerCells) throws SQLException {
Map<String, DbParameterAccessor> allParams = environment.getAllColumns(tableName);
if (allParams.isEmpty()) {
throw new SQLException("Cannot retrieve list of columns for " + tableName
+ " - check spelling and access rights");
}
accessors = new DbParameterAccessor[headerCells.size()];
columnBindings = new Binding[headerCells.size()];
for (int i = 0; headerCells != null; i++, headerCells = headerCells.more) {
String name = headerCells.text();
int question_mark = name.indexOf('?');
String paramName = question_mark > 0 ? name.substring(0, question_mark) : name;
paramName = NameNormaliser.normaliseName(paramName);
accessors[i] = allParams.get(paramName);
if (accessors[i] == null) {
wrong(headerCells);
throw new SQLException("Cannot find column " + paramName);
}
if (question_mark > 0) {
if (question_mark < name.length() - 1) {
accessors[i] = new SequenceAccessor(accessors[i], name.substring(question_mark + 1));
} else {
accessors[i] = new DbAutoGeneratedKeyAccessor(accessors[i]);
}
columnBindings[i] = new SymbolAccessQueryBinding();
} else {
columnBindings[i] = new SymbolAccessSetBinding();
}
columnBindings[i].adapter = accessors[i];
}
}
protected void insertRowData(PreparedStatement statement, Parse row, int rowNum) {
Parse cell = row.parts;
try {
statement.clearParameters();
// first set input params
for (int column = 0; column < accessors.length; column++, cell = cell.more) {
int direction = accessors[column].getDirection();
if (direction == DbParameterAccessor.INPUT) {
columnBindings[column].doCell(this, cell);
}
}
statement.execute();
cell = row.parts;
// next evaluate output params
for (int column = 0; column < accessors.length; column++, cell = cell.more) {
int direction = accessors[column].getDirection();
if (direction == DbParameterAccessor.OUTPUT || direction == DbParameterAccessor.RETURN_VALUE
|| direction == DbParameterAccessor.SEQUENCE) {
Binding binding = columnBindings[column];
if (binding instanceof SymbolAccessQueryBinding) {
String keyName = accessors[column].getName();
((SymbolAccessQueryBinding) binding).doCell(this, cell, keyName, rowNum);
} else {
columnBindings[column].doCell(this, cell);
}
}
}
} catch (Throwable e) {
exception(cell, e);
throw new HasMarkedException(e);
}
}
}