// In the PreExecute phase we will be caching the column indexes looked up by the LineageID
public override void PreExecute()
{
// Initialize the cache
columnIndexes = new Dictionary <string, Dictionary <string, int> >();
// Get the model
JSONDataModel model = getModel();
// For each table definition
foreach (DataTable table in model.Tables)
{
// Find the ouput
IDTSOutput100 output = ComponentMetaData.OutputCollection[table.TableName];
// ... and initialize the corresponding cache index
columnIndexes[table.TableName] = new Dictionary <string, int>();
// For every column
foreach (IDTSOutputColumn100 col in output.OutputColumnCollection)
{
// We wrap this in a try-catch without exception handling so that
// we allow for a subset of the outputs to be used. If an output is not
// connected, the columns defined in that output will not be in the Buffer
try
{
// Cache the buffer column index
columnIndexes[table.TableName][col.Name] = BufferManager.FindColumnByLineageID(output.Buffer, col.LineageID);
}
catch
{
// Do nothing for now
}
}
}
}
public CouchbaseSourceAdapterUIForm(Connections cons, Variables vars, IDTSComponentMetaData100 md)
{
InitializeComponent();
variables = vars;
connections = cons;
metaData = md;
this.Text = metaData.Name + " Configuration";
if (designTimeInstance == null)
{
designTimeInstance = metaData.Instantiate();
}
txtURL.Text = metaData.CustomPropertyCollection["url"].Value.ToString();
txtBucket.Text = metaData.CustomPropertyCollection["bucket"].Value.ToString();
txtPassword.Text = metaData.CustomPropertyCollection["password"].Value.ToString();
txtDesignDoc.Text = metaData.CustomPropertyCollection["designDoc"].Value.ToString();
txtView.Text = metaData.CustomPropertyCollection["view"].Value.ToString();
cbForceReindex.Checked = metaData.CustomPropertyCollection["forceReindex"].Value;
cbDescending.Checked = metaData.CustomPropertyCollection["descending"].Value;
txtStartKey.Text = metaData.CustomPropertyCollection["startKey"].Value.ToString();
txtEndKey.Text = metaData.CustomPropertyCollection["endKey"].Value.ToString();
string modelXML = metaData.CustomPropertyCollection["modelXML"].Value.ToString();
model = new JSONDataModel();
if (!modelXML.Trim().Equals(""))
{
model = new JSONDataModel(modelXML);
}
}
private void btnClearModel_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure?", "Clear Model", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
{
currentModel = new JSONDataModel();
}
}
/// <summary>
/// Write a json document to the output buffers
/// </summary>
/// <param name="id">The document key</param>
/// <param name="json">The JSON representation of the document</param>
/// <param name="model">A model used to decompose the document</param>
/// <param name="outputIDs">Indexes of the output buffers</param>
/// <param name="buffers">The Pipline Buffers</param>
private void writeDocToBuffers(string id, string json, JSONDataModel model, int[] outputIDs, PipelineBuffer[] buffers)
{
// Deserialize the document
JsonObject jp = new JsonObject(json);
// For each model table
foreach (DataTable modelTable in model.Tables)
{
// Obtain the output
IDTSOutput100 output = ComponentMetaData.OutputCollection[modelTable.TableName];
// Continue if output is not connected
if (Array.IndexOf(outputIDs, output.ID) == -1)
{
continue;
}
// Obtain the buffer tho write to
PipelineBuffer buffer = buffers[Array.IndexOf(outputIDs, output.ID)];
// If we are processing a table that has indexes on the second column
// this must be a child table
if (modelTable.Rows.Count > 2 && modelTable.Rows[1]["xpath"].ToString().StartsWith("#"))
{
// Go and process the related table
processChildTable(id, jp, model, modelTable.TableName, outputIDs, buffers);
}
else
{
// Add a new row to the buffer
buffer.AddRow();
// For every column definition in teh model
foreach (DataRow row in modelTable.Rows)
{
// Find the column index that the buffer will recognize
int columnIndex = columnIndexes[modelTable.TableName][row["shortColumn"].ToString()];
// Get the model datatype
Type dataType = Type.GetType(row["datatype"].ToString());
// And XPath
string xpath = row["xpath"].ToString();
// Handle special case of DOC_ID
if (xpath.Equals("#ID"))
{
buffer.SetString(columnIndex, id);
}
else
{
// Write all other fields as usual
writeDataToBuffer(jp, buffer, columnIndex, dataType, xpath);
}
}
}
}
}
private void btnMapping_Click(object sender, EventArgs e)
{
// Create and display the form for the user interface.
DataMappingForm dataMappingForm = new DataMappingForm(metaData, designTimeInstance, currentView);
DialogResult result = dataMappingForm.ShowDialog();
if (result == DialogResult.OK)
{
model = dataMappingForm.currentModel;
}
}
/// <summary>
/// Performs a deep merge between two model datasets by comparing column definition rows by
/// their first element, and enforcing type precedence over the second elements
/// </summary>
/// <param name="ds1">First Dataset</param>
/// <param name="model">Model to merge with</param>
/// <returns>A merged vercion of both datasets. The merged version prefers the first dataset's entries
/// for column indexes greater than 1</returns>
public JSONDataModel mergeWith(DataSet model)
{
// The result will begin as a copy of the first dataset
JSONDataModel result = this.Copy();
// Compare tables
foreach (DataTable table2 in model.Tables)
{
// Add the table if missing
if (!result.Tables.Contains(table2.TableName))
{
result.Tables.Add(table2.TableName);
foreach (DataColumn col2 in table2.Columns)
{
DataColumn col1 = new DataColumn(col2.ColumnName);
col1.DataType = col2.DataType;
result.Tables[table2.TableName].Columns.Add(col1);
}
}
// Cache the table from the first dataset
DataTable table1 = result.Tables[table2.TableName];
// Compare the rows and perform the merge
foreach (DataRow row2 in table2.Rows)
{
// Find the row by comparing the first column which holds the column name of the output
int rowIndex = -1;
for (int i = 0; i < table1.Rows.Count; i++)
{
if (row2[0].Equals(table1.Rows[i][0]))
{
rowIndex = i;
break;
}
}
// If the row exists, merge the datatype stored in the second column
if (rowIndex >= 0)
{
table1.Rows[rowIndex][1] = getTypePrecedence(table1.Rows[rowIndex][1].ToString(), row2[1].ToString());
}
// otherwise copy the row
else
{
table1.ImportRow(row2);
}
}
}
return(result);
}
/// <summary>
/// Writes out a child table. Uses an iterative approach. Would have been much more
/// readable if this was recursive, but we really should not be using it.
/// </summary>
/// <param name="id">The document key</param>
/// <param name="jp">A deserialized object holding the document</param>
/// <param name="model">A model used to decompose the document</param>
/// <param name="tableName">The name of the child table</param>
/// <param name="outputIDs">Indexes of the output buffers</param>
/// <param name="buffers">The Pipline Buffers</param>
void processChildTable(string id, JsonObject jp, JSONDataModel model, string tableName, int[] outputIDs, PipelineBuffer[] buffers)
{
// Obtain references to the model table, output and buffer
DataTable modelTable = model.Tables[tableName];
IDTSOutput100 output = ComponentMetaData.OutputCollection[modelTable.TableName];
PipelineBuffer buffer = buffers[Array.IndexOf(outputIDs, output.ID)];
// The counters will track how many levels of nesting we have
int counters = 0;
// We will first represent all records that need to be written out as XPaths
List <List <string> > xPathRows = new List <List <string> >();
//
List <string> xPathRow = new List <string>();
// Collect counters and initial row XPaths
foreach (DataRow row in modelTable.Rows)
{
string xPath = row["xpath"].ToString();
if (xPath.Equals("#ID"))
{
// Nothing for now
}
else if (xPath.StartsWith("#"))
{
counters++;
}
xPathRow.Add(xPath);
}
// Add the initial Row
xPathRows.Add(xPathRow);
// Iteratively expand each row based on the indexes found
for (int i = 0; i < counters; i++)
{
xPathRows = expandRows(jp, i, xPathRows, modelTable);
}
// Iterate over the expanded rows. They should contain
// full XPaths to be used to obtain the values for each cell
foreach (List <string> row in xPathRows)
{
// Add a row to the buffer
buffer.AddRow();
// Keep track of which column we are looking at
int colIdx = 0;
// For each column definition in the model
foreach (DataRow modelCol in modelTable.Rows)
{
// Get the name and the index
string col = modelCol["shortColumn"].ToString();
int columnIndex = columnIndexes[modelTable.TableName][col];
// Handle special case of DOC_ID
if (row[colIdx].Equals("#ID"))
{
buffer.SetString(columnIndex, id);
}
// Handle special case of child table indexes
else if (row[colIdx].StartsWith("#"))
{
// Obtain the nesting index from the XPath by looking at the first index found
int counterIndex = int.Parse(row[colIdx].Substring(1));
// Extract the current value of the index
Match m = Regex.Match(row[row.Count - 1], "\\[([0-9]+)\\]");
long index = long.Parse(m.Groups[counterIndex + 1].Value);
// and write it out to the buffer
buffer.SetInt64(columnIndex, index);
}
else
{
// Write all other fields as usual
Type dataType = Type.GetType(modelCol["datatype"].ToString());
string xpath = row[colIdx];
writeDataToBuffer(jp, buffer, columnIndex, dataType, xpath);
}
// Increment to look at the next column
colIdx++;
}
}
}
// The main data flow function
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
// Get teh model
JSONDataModel model = getModel();
// Initialize Couchbase Client
CouchbaseClientConfiguration config = new CouchbaseClientConfiguration();
config.Urls.Add(new Uri(ComponentMetaData.CustomPropertyCollection["url"].Value.ToString().TrimEnd('/') + "/pools/"));
config.Bucket = ComponentMetaData.CustomPropertyCollection["bucket"].Value.ToString();
config.BucketPassword = ComponentMetaData.CustomPropertyCollection["password"].Value.ToString();
CouchbaseClient client = new CouchbaseClient(config);
// Extract the parameters
string designDoc = ComponentMetaData.CustomPropertyCollection["designDoc"].Value.ToString();
string viewName = ComponentMetaData.CustomPropertyCollection["view"].Value.ToString();
bool forceReindex = (bool)ComponentMetaData.CustomPropertyCollection["forceReindex"].Value;
bool descending = (bool)ComponentMetaData.CustomPropertyCollection["descending"].Value;
// Define the view to be executed
IView <IViewRow> view = ((IView <IViewRow>)client.GetView(designDoc, viewName))
.Stale(forceReindex ? StaleMode.False : StaleMode.AllowStale)
.Descending(descending);
// Extract the variables from the package
IDTSVariables100 variables = null;
// StartKey can be set from another task prior of running this task
string startKey = ComponentMetaData.CustomPropertyCollection["startKey"].Value;
if (startKey != null && startKey.StartsWith("@"))
{
VariableDispenser.LockOneForRead(startKey.Substring(1), ref variables);
startKey = variables[0].Value.ToString();
variables.Unlock();
ComponentMetaData.PostLogMessage("Couchbase", ComponentMetaData.Name, "Found a variable StartKey. Using " + startKey + " as value.", DateTime.Now, DateTime.Now, 0, null);
}
// EndKey can be set from another task prior of running this task
string endKey = ComponentMetaData.CustomPropertyCollection["endKey"].Value;
if (endKey != null && endKey.StartsWith("@"))
{
VariableDispenser.LockOneForRead(endKey.Substring(1), ref variables);
endKey = variables[0].Value.ToString();
variables.Unlock();
ComponentMetaData.PostLogMessage("Couchbase", ComponentMetaData.Name, "Found a variable EndKey. Using " + endKey + " as value.", DateTime.Now, DateTime.Now, 0, null);
}
// Apply variables to the view if necessary
if (startKey != null && !startKey.Equals(""))
{
view = view.StartKey <string>(startKey);
}
if (endKey != null && !endKey.Equals(""))
{
view = view.EndKey <string>(endKey);
}
// Iterate over each document returned by the view
foreach (IViewRow row in view)
{
// Say that we have read it
ComponentMetaData.IncrementPipelinePerfCounter(101, 1);
// Write it out to the outputs
writeDocToBuffers(row.ItemId, row.GetItem().ToString(), model, outputIDs, buffers);
// Say that we wrote it
ComponentMetaData.IncrementPipelinePerfCounter(103, 1);
}
// Flush out all buffers and get outta here
foreach (PipelineBuffer buffer in buffers)
{
/// Notify the data flow task that no more rows are coming.
buffer.SetEndOfRowset();
}
}
// Rebuild the component outputs based on the model
public override void ReinitializeMetaData()
{
// Clean out the outputs
ComponentMetaData.OutputCollection.RemoveAll();
// Get the model
JSONDataModel model = getModel();
// For every output table model crete the SSIS outputs
foreach (DataTable table in model.Tables)
{
// Define a new output
IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
output.Name = table.TableName;
// For every column definition in the model, create the corresponding output column
foreach (DataRow row in table.Rows)
{
// Create a new Output Column
IDTSOutputColumn100 outColumn = output.OutputColumnCollection.New();
// Set column data type properties.
bool isLong = false;
// Assume string for missing datatypes
Type dataType = row["datatype"].Equals("")
? typeof(string)
: Type.GetType(row["datatype"].ToString());
// Translate the datatype into the SSIS intermediate type
DataType dt = DataRecordTypeToBufferType(dataType);
dt = ConvertBufferDataTypeToFitManaged(dt, ref isLong);
// Enforce numeric datatypes
switch (dt)
{
case DataType.DT_R4:
case DataType.DT_R8:
case DataType.DT_DECIMAL:
dt = DataType.DT_NUMERIC;
break;
case DataType.DT_BOOL:
case DataType.DT_I1:
case DataType.DT_I2:
case DataType.DT_I4:
dt = DataType.DT_I4;
break;
case DataType.DT_UI1:
case DataType.DT_UI2:
case DataType.DT_UI4:
dt = DataType.DT_UI4;
break;
case DataType.DT_DBTIMESTAMP:
case DataType.DT_DBTIMESTAMP2:
case DataType.DT_DBDATE:
case DataType.DT_DATE:
case DataType.DT_FILETIME:
dt = DataType.DT_DBTIMESTAMP;
break;
}
// Assume defaults and limits
int length = 0;
int precision = 2000;
int scale = 0;
int codepage = table.Locale.TextInfo.ANSICodePage;
// Handle the datatype cases
switch (dt)
{
// The length cannot be zero, and the code page property must contain a valid code page.
case DataType.DT_STR:
case DataType.DT_TEXT:
length = precision;
precision = 0;
scale = 0;
break;
case DataType.DT_WSTR:
length = precision;
codepage = 0;
scale = 0;
precision = 0;
break;
case DataType.DT_NUMERIC:
length = 0;
codepage = 0;
precision = 24;
scale = 6;
break;
default:
length = 0;
precision = 0;
codepage = 0;
scale = 0;
break;
}
// Set the properties of the output column.
outColumn.Name = (string)row["shortColumn"];
outColumn.Description = (string)row["xpath"];
outColumn.SetDataTypeProperties(dt, length, precision, scale, codepage);
// Set the properties of the metadata column to facilitate automatic binding
IDTSExternalMetadataColumn100 extColumn = output.ExternalMetadataColumnCollection.New();
extColumn.Name = (string)row["shortColumn"];
extColumn.Description = (string)row["xpath"];
extColumn.DataType = dt;
extColumn.Length = length;
extColumn.Precision = precision;
extColumn.Scale = scale;
extColumn.CodePage = codepage;
extColumn.MappedColumnID = outColumn.ID;
}
}
}
// The Validate phase will be checking the model against its outputs
// and will ensure that all properties are set correctly
public override DTSValidationStatus Validate()
{
bool pbCancel = false;
// Validate that the url custom property is set.
if (ComponentMetaData.CustomPropertyCollection["url"].Value == null ||
((string)ComponentMetaData.CustomPropertyCollection["url"].Value).Length == 0)
{
ComponentMetaData.FireError(0, ComponentMetaData.Name, "The URL property must be set.", "", 0, out pbCancel);
return(DTSValidationStatus.VS_ISBROKEN);
}
// Validate that the bucket custom property is set.
if (ComponentMetaData.CustomPropertyCollection["bucket"].Value == null ||
((string)ComponentMetaData.CustomPropertyCollection["bucket"].Value).Length == 0)
{
ComponentMetaData.FireError(0, ComponentMetaData.Name, "The Bucket property must be set.", "", 0, out pbCancel);
return(DTSValidationStatus.VS_ISBROKEN);
}
// Validate that the password custom property is set.
if (ComponentMetaData.CustomPropertyCollection["password"].Value == null ||
((string)ComponentMetaData.CustomPropertyCollection["password"].Value).Length == 0)
{
ComponentMetaData.FireError(0, ComponentMetaData.Name, "The Password property must be set.", "", 0, out pbCancel);
return(DTSValidationStatus.VS_ISBROKEN);
}
// Validate that the designDoc custom property is set.
if (ComponentMetaData.CustomPropertyCollection["designDoc"].Value == null ||
((string)ComponentMetaData.CustomPropertyCollection["designDoc"].Value).Length == 0)
{
ComponentMetaData.FireError(0, ComponentMetaData.Name, "The Design Document property must be set.", "", 0, out pbCancel);
return(DTSValidationStatus.VS_ISBROKEN);
}
// Validate that the view custom property is set.
if (ComponentMetaData.CustomPropertyCollection["view"].Value == null ||
((string)ComponentMetaData.CustomPropertyCollection["view"].Value).Length == 0)
{
ComponentMetaData.FireError(0, ComponentMetaData.Name, "The View property must be set.", "", 0, out pbCancel);
return(DTSValidationStatus.VS_ISBROKEN);
}
// Validate outputs against the model
JSONDataModel model = getModel();
// If the output count does not match, regenerate it
if (ComponentMetaData.OutputCollection.Count != model.Tables.Count)
{
ComponentMetaData.FireError(0, ComponentMetaData.Name, "Number of outputs must match the number of tables in the model", "", 0, out pbCancel);
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
// Inpect every table
foreach (DataTable table in model.Tables)
{
string name = table.TableName;
IDTSOutput100 output = ComponentMetaData.OutputCollection[name];
// If the column count does not match, or the specific output is not found, regenerate it
if (output == null || output.OutputColumnCollection.Count != table.Rows.Count)
{
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
// If the columns do not match, regenerate it
foreach (DataRow row in table.Rows)
{
string colName = row["shortColumn"].ToString();
IDTSOutputColumn100 outputColumn = output.OutputColumnCollection[colName];
// If the specific column is not found, regenerate it
if (outputColumn == null)
{
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
}
}
// Let the base class verify that the input column reflects the output
// of the upstream component.
return(base.Validate());
}
private void btnGenerateModel_Click(object sender, EventArgs e)
{
// Start with an empty model
//currentModel = new JSONDataModel();
// Clear the logs
txtLog.Text = "";
if (cbRepresentative.Checked)
{
if (File.Exists(txtModelFile.Text))
{
// Initialize the logs
string logMessages;
// Create the model of this document
JSONDataModel model = new JSONDataModel(File.ReadAllText(txtModelFile.Text), txtRootXPath.Text, txtTableNameXPath.Text, out logMessages);
// Merge it with the current model
currentModel = currentModel.mergeWith(model);
// Append the logs
txtLog.Text += logMessages;
}
else
{
MessageBox.Show("File does not exists. Please select a valid representative file.");
}
}
if (cbSample.Checked)
{
// Sample documents and union the results
foreach (var row in currentView.Limit((int)nudDocumentsToSample.Value))
{
// Initialize the logs
string logMessages;
// Create the model of this document
JSONDataModel model = new JSONDataModel(row.GetItem().ToString(), txtRootXPath.Text, txtTableNameXPath.Text, out logMessages);
// Merge it with the current model
currentModel = currentModel.mergeWith(model);
// Append the logs
txtLog.Text += logMessages;
}
}
// Draw the model
comboModelTables.Items.Clear();
foreach (DataTable table in currentModel.Tables)
{
comboModelTables.Items.Add(table.TableName);
}
if (comboModelTables.Items.Count > 0)
{
comboModelTables.SelectedIndex = 0;
}
}
