/// <summary>
/// Recursively load data into the tuple current being loaded.
/// </summary>
/// <param name="handler">The handler constructing the current element.</param>
/// <param name="schema">Schema of tuple being constructed.</param>
/// <param name="row">Data source for tuple to construct.</param>
/// <param name="absIndex">Absolute index into <code>row</code> for the next column to process.</param>
/// <returns>The updated absolute index.</returns>
private int fillTuple(OplDataHandler handler, ITupleSchema schema, IRow row, int absIndex)
{
handler.StartTuple();
for (int i = 0; i < schema.Size; ++i)
{
if (schema.IsTuple(i))
{
ILOG.Opl_Core.Cppimpl.IloTupleSchema s = (ILOG.Opl_Core.Cppimpl.IloTupleSchema)schema;
absIndex = fillTuple(handler, s.getTupleColumn(i), row, absIndex);
continue;
}
if (schema.IsNum(i))
{
SET_FLOAT(handler, row, absIndex);
}
else if (schema.IsInt(i))
{
SET_INT(handler, row, absIndex);
}
else if (schema.IsSymbol(i))
{
SET_STRING(handler, row, absIndex);
}
else
{
throw new NotImplementedException("tuple element type not supported");
}
++absIndex;
}
handler.EndTuple();
return(absIndex);
}
/// <summary>
/// Reads a table and inject it as a tuple set in OPL.
/// </summary>
/// <param name="name">The name of the Data Set</param>
/// <param name="reader">The SqlDataReader to read from</param>
private void ReadTupleSet(String name, DbDataReader reader)
{
OplDataHandler handler = DataHandler;
OplElement element = handler.getElement(name);
ITupleSchema schema = element.AsTupleSet().Schema;
int size = schema.Size;
String[] oplFieldsName = new String[size];
OplElementDefinitionType.Type[] oplFieldsType = new OplElementDefinitionType.Type[size];
this.FillNamesAndTypes(schema, oplFieldsName, oplFieldsType);
handler.StartElement(name);
handler.StartSet();
while (reader.Read())
{
handler.StartTuple();
for (int column = 0; column < oplFieldsName.Length; column++)
{
String columnName = oplFieldsName[column];
HandleColumnValue(handler, oplFieldsType[column], reader[columnName]);
}
handler.EndTuple();
}
handler.EndSet();
}
/// <summary>
/// Returns a table definition from an OPL tuple schema.
///
/// The table definition is an array of N strings, each describing an element of the tuple.
/// </summary>
/// <param name="tupleSchema">The OPL Tuple schema</param>
/// <param name="includeType">If True, The table definition has the form "NAME TYPE" otherwise just "NAME"</param>
/// <returns>The table definition</returns>
private String[] TableDefinitionFromOplTupleSchema(ITupleSchema tupleSchema, bool includeType)
{
OplElementDefinition elementDefinition = this.Model.ModelDefinition.getElementDefinition(tupleSchema.Name);
OplTupleSchemaDefinition oplTupleSchema = elementDefinition.asTupleSchema();
String[] fields = new String[tupleSchema.Size];
for (int i = 0; i < tupleSchema.Size; i++)
{
String columnName = tupleSchema.GetColumnName(i);
String field = columnName;
if (includeType)
{
OplElementDefinitionType.Type oplType = oplTupleSchema.getComponent(i).getElementDefinitionType();
String sqlType = null;
if (oplType == OplElementDefinitionType.Type.INTEGER)
{
sqlType = "INT";
}
else if (oplType == OplElementDefinitionType.Type.FLOAT)
{
sqlType = "FLOAT";
}
else if (oplType == OplElementDefinitionType.Type.STRING)
{
sqlType = "VARCHAR(30)";
}
field += " " + sqlType;
}
fields[i] = field;
}
return(fields);
}
private void InsertIntoTable(DbConnection con, ITupleSchema tupleSchema, ITuple tuple, string insertCommand)
{
OplElementDefinition elementDefinition = this.Model.ModelDefinition.getElementDefinition(tupleSchema.Name);
OplTupleSchemaDefinition tupleDef = elementDefinition.asTupleSchema();
using (DbCommand insert = con.CreateCommand())
{
insert.CommandText = insertCommand;
for (int i = 0; i < tupleSchema.Size; i++)
{
OplElementDefinitionType.Type oplType = tupleDef.getComponent(i).getElementDefinitionType();
object oplValue = null;
if (oplType == OplElementDefinitionType.Type.INTEGER)
{
oplValue = tuple.GetIntValue(i);
}
else if (oplType == OplElementDefinitionType.Type.FLOAT)
{
oplValue = tuple.GetNumValue(i);
}
else if (oplType == OplElementDefinitionType.Type.STRING)
{
oplValue = tuple.GetStringValue(i);
}
DbUtils.AddParameterWithValue(insert, "@value" + i, oplValue);
}
insert.ExecuteNonQuery();
}
}
/// <summary>
/// Populates array names and types with the information from the tuple schema.
/// </summary>
/// <param name="schema">The tuple definition</param>
/// <param name="names">Contains the field names</param>
/// <param name="types">Contains the field types</param>
private void FillNamesAndTypes(ITupleSchema schema, string[] names, OplElementDefinitionType.Type[] types)
{
OplElementDefinition elementDefinition = this.modelDefinition.getElementDefinition(schema.Name);
OplTupleSchemaDefinition tupleSchema = elementDefinition.asTupleSchema();
for (int i = 0; i < schema.Size; i++)
{
types[i] = tupleSchema.getComponent(i).getElementDefinitionType();
names[i] = schema.GetColumnName(i);
}
}
/// <summary>
/// Creates the SQL insert command.
///
/// </summary>
/// <param name="tupleSchema">The tuple schema</param>
/// <param name="table">The target table</param>
/// <returns></returns>
private String CreateInsertCommand(ITupleSchema tupleSchema, string table)
{
String[] values = new String[tupleSchema.Size];
for (int i = 0; i < tupleSchema.Size; i++)
{
values[i] = "@value" + i;
}
String[] fields = TableDefinitionFromOplTupleSchema(tupleSchema, false);
String insertCommand = "INSERT INTO " + table + "(" + String.Join(",", fields) + ") VALUES(" + String.Join(",", values) + ")";
return(insertCommand);
}
/// <summary>
/// Look for a string field in a tuple and return its value.
/// </summary>
/// <param name="tuple">The tuple to search.</param>
/// <param name="field">The name of the field to find.</param>
/// <param name="def">The value to return if the field is not found or is not of string type.</param>
/// <returns></returns>
private static string tryLoadField(ITuple tuple, String field, string def = null)
{
ITupleSchema schema = tuple.Schema;
int size = schema.Size;
for (int i = 0; i < size; ++i)
{
if (schema.IsSymbol(i) && field.Equals(schema.GetColumnName(i)))
{
return(tuple.GetStringValue(i));
}
}
return(def);
}
/// <summary>
/// Writes the specified data set as the specified target table.
/// </summary>
/// <param name="name">The name of the OPL data set</param>
/// <param name="targetTable">The target table</param>
public void WriteTable(DbConnection con, string name, String targetTable)
{
// The opl element to write
OplElement element = this.Model.GetElement(name);
ITupleSet tupleSet = element.AsTupleSet();
ITupleSchema tupleSchema = tupleSet.Schema;
// drop existing table
DropTable(con, targetTable);
// Create table
String[] tableDef = TableDefinitionFromOplTupleSchema(tupleSchema, true);
CreateTable(con, tableDef, targetTable);
// Populate table
String insertCommand = CreateInsertCommand(tupleSchema, targetTable);
foreach (ITuple t in tupleSet)
{
InsertIntoTable(con, tupleSchema, t, insertCommand);
}
}
/// <summary>
/// Load a collection of tuples types.
/// </summary>
private void loadTupleCollection(OplDataHandler handler, string name, IEnumerator <IRow> rows, ITupleSchema schema, Action <OplDataHandler> startCollection, Action <OplDataHandler> endCollection)
{
handler.StartElement(name);
startCollection(handler);
while (rows.MoveNext())
{
fillTuple(handler, schema, rows.Current, 0);
}
endCollection(handler);
handler.EndElement();
}
