public bool IsIdentityColumn(Column column)
{
IdentityConstraint c = this.ContainsIdentities();
if (c != null)
{
foreach (Key k in c.Keys)
{
if (k.Name.Equals(column.Name, StringComparison.InvariantCultureIgnoreCase))
{
return true;
}
}
}
return false;
}
private void Append(Column column)
{
if(String.IsNullOrEmpty(column.Computed) && !column.IsComputed)
{
_stringBuilder.AppendFormat(System.Globalization.CultureInfo.InvariantCulture,
"\t[{0}] {1}{2}{3}{4}",
column.Name,
column.Type,
_table.IsIdentityColumn(column) ? " IDENTITY(1,1)" : "",
column.IsNullable ? "" : " NOT NULL",
String.IsNullOrEmpty(column.Default) || _table.IsIdentityColumn(column) ? "" : " DEFAULT " + column.Default
);
}
else
{
_stringBuilder.AppendFormat(System.Globalization.CultureInfo.InvariantCulture,
"\t[{0}] {1}",
column.Name,
column.Computed
);
}
}
private void AppendDefaultValueColumn(Column column)
{
_stringBuilder.AppendFormat("[{0}]", column.Name);
}
private static void ProcessAstTableColumnBaseNode(PhysicalTSQL.Table table, AstTable.AstTableColumnBaseNode columnBase)
{
if (columnBase.AsClassOnly)
{
return;
}
try
{
AstDimension.AstAttributeColumnNode attributeColumn = new AstDimension.AstAttributeColumnNode();
attributeColumn.Column = columnBase;
AstTable.AstTableColumnBaseNode column = columnBase;
AstTable.AstTableDimensionReferenceNode dimReference = columnBase as AstTable.AstTableDimensionReferenceNode;
AstTable.AstTableHashedKeyColumnNode hashKey = columnBase as AstTable.AstTableHashedKeyColumnNode;
if (hashKey != null)
{
StringBuilder hashBytesBuilder = new StringBuilder();
foreach (AstTable.AstTableKeyColumnNode keyColumn in hashKey.Constraint.Columns)
{
string expression = "+ HASHBYTES('SHA1',{0})";
switch (keyColumn.Column.Type)
{
case VulcanEngine.IR.Ast.Table.ColumnType.WSTR:
expression = String.Format(expression, String.Format("UPPER(RTRIM(LTRIM(COALESCE({0},''))))", keyColumn.Column.Name));
break;
case VulcanEngine.IR.Ast.Table.ColumnType.STR:
expression = String.Format(expression, String.Format("UPPER(RTRIM(LTRIM(COALESCE({0},''))))", keyColumn.Column.Name));
break;
case VulcanEngine.IR.Ast.Table.ColumnType.INT32:
case VulcanEngine.IR.Ast.Table.ColumnType.INT64:
case VulcanEngine.IR.Ast.Table.ColumnType.UINT32:
case VulcanEngine.IR.Ast.Table.ColumnType.UINT64:
expression = String.Format(expression, String.Format("CONVERT(binary varying(64),COALESCE({0},-1))", keyColumn.Column.Name));
break;
default:
expression = String.Format(expression, keyColumn.Column.Name);
break;
}
hashBytesBuilder.Append(expression);
}
string hashExpression = String.Format("(CONVERT(binary varying(32),HASHBYTES('SHA1',{0})))", hashBytesBuilder.ToString().Substring(1));
PhysicalTSQL.Column physicalColumn = new Ssis2008Emitter.IR.TSQL.Column();
physicalColumn.Name = hashKey.Name;
physicalColumn.Type = "BINARY VARYING(32)";
physicalColumn.IsNullable = hashKey.IsNullable;
physicalColumn.Computed = String.Format("AS {0} PERSISTED NOT NULL UNIQUE", hashExpression);
physicalColumn.IsAssignable = hashKey.IsAssignable;
table.Columns.ColumnList.Add(physicalColumn);
}
if (attributeColumn != null)
{
column = attributeColumn.Column;
}
if (column != null && hashKey == null && dimReference == null)
{
PhysicalTSQL.Column physicalColumn = new Ssis2008Emitter.IR.TSQL.Column();
physicalColumn.Name = column.Name;
physicalColumn.Type = Ssis2008Emitter.Emitters.TSQL.PhysicalTypeTranslator.Translate(column.Type, column.Length, column.Precision, column.Scale, column.CustomType);
physicalColumn.IsNullable = column.IsNullable;
physicalColumn.Default = column.Default;
physicalColumn.Computed = column.Computed;
physicalColumn.IsAssignable = column.IsAssignable;
physicalColumn.IsComputed = column.IsComputed;
table.Columns.ColumnList.Add(physicalColumn);
}
if (dimReference != null)
{
// TODO: This is wrong in general. We need to ensure that this is always a single column constraint
AstTable.AstTableKeyBaseNode primaryKey = dimReference.Dimension.PreferredKey;
if (primaryKey == null)
{
throw new Exception("Error: All Detego Tables need a Primary Key!");
}
//TODO: We currently support only a single primary key column. This should be fixed :).
PhysicalTSQL.DimensionMapping physicalDimReference = new Ssis2008Emitter.IR.TSQL.DimensionMapping();
PhysicalTSQL.Column physicalColumn = new PhysicalTSQL.Column();
physicalColumn.Name = dimReference.OutputName;
physicalColumn.IsNullable = primaryKey.Columns[0].Column.IsNullable;
physicalColumn.Type = Ssis2008Emitter.Emitters.TSQL.PhysicalTypeTranslator.Translate(primaryKey.Columns[0].Column.Type, primaryKey.Columns[0].Column.Length, primaryKey.Columns[0].Column.Precision, primaryKey.Columns[0].Column.Scale, primaryKey.Columns[0].Column.CustomType);
physicalColumn.Default = primaryKey.Columns[0].Column.Default;
table.Columns.ColumnList.Add(physicalColumn);
PhysicalTSQL.ForeignKeyConstraint constraint = new PhysicalTSQL.ForeignKeyConstraint();
constraint.Name = String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}_{1}_{2}", dimReference.Dimension.Name, dimReference.OutputName, primaryKey.Columns[0].Column.Name);
constraint.Table = dimReference.Dimension.Name;
PhysicalTSQL.Key localKey = new PhysicalTSQL.Key();
localKey.Name = physicalColumn.Name;
constraint.LocalColumnList.Add(localKey);
PhysicalTSQL.Key foreignKey = new PhysicalTSQL.Key();
foreignKey.Name = primaryKey.Columns[0].Column.Name;
constraint.ForeignColumnList.Add(foreignKey);
table.AddForeignKeyConstraint(constraint);
// TODO: Do we still need this? table.Columns.DimensionMappingList.Add(physicalDimReference);
}
}
catch (Exception e)
{
throw new SSISEmitterException(columnBase, e);
}
}
private SPColumn IsKeyOrIdentityColumn(Column column)
{
SPColumn sqlColumn = new SPColumn();
sqlColumn.Column = column;
sqlColumn.IsKey = false;
sqlColumn.IsIdentity = false;
/* TODO
foreach (LogicalReference r in column.References)
{
if (r is Key)
{
if (r.Parent is PrimaryKeyConstraint)
{
sqlColumn.IsKey = true;
_keyColumn = sqlColumn;
break;
}
if (r.Parent is IdentityConstraint)
{
sqlColumn.IsIdentity = true;
_identityColumn = sqlColumn;
break;
}
}
}*/
return sqlColumn;
}