Пример #1
0
        private void Append(ForeignKeyConstraint constraint)
        {
            string strConstraintName;

            if (String.IsNullOrEmpty(constraint.Name) || !constraint.Name.StartsWith("FK_"))
            {
                strConstraintName = String.Format(System.Globalization.CultureInfo.InvariantCulture, "FK_{0}_{1}", constraint.Parent.Name, constraint.Name);
            }
            else
            {
                strConstraintName = constraint.Name;
            }

            if (strConstraintName.Length >= 128)
            {
                strConstraintName = strConstraintName.Substring(0, 127);
            }

            TemplatePlatformEmitter te = new TemplatePlatformEmitter(
                "ForeignKeyConstraintTemplate",
                constraint.Parent.Name,
                strConstraintName,
                new KeysBuilder(constraint.LocalColumnList).BuildForeignKeys(),
                constraint.Table,
                new KeysBuilder(constraint.ForeignColumnList).BuildForeignKeys()
            );
            _stringBuilder.Append(te.Emit(constraint));
            _stringBuilder.Append(NEWLINE);
        }
Пример #2
0
 public void AddForeignKeyConstraint(ForeignKeyConstraint constraint)
 {
     base.AddChild(constraint);
     _foreignKeyConstraintList.Add(constraint);
 }
Пример #3
0
        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);
            }
        }
Пример #4
0
        public static PhysicalTSQL.Table Lower(this AstTable.AstTableNode astNode)
        {
            if (astNode.AsClassOnly)
            {
                return null;
            }

            try
            {
                PhysicalTSQL.Table table = new Ssis2008Emitter.IR.TSQL.Table();
                table.Name = astNode.Name;
                table.Columns.Parent = table;
                table.Indexes.Parent = table;

                ProcessStaticSources(astNode, table);
                table.ConnectionConfiguration = astNode.Connection != null ? astNode.Connection.Lower() : null;

                foreach (AstTable.AstTableColumnBaseNode columnBase in astNode.Columns)
                {
                    ProcessAstTableColumnBaseNode(table, columnBase);
                }

                // TODO: Strip out UniqueKey if it is identical to keyPhysicalConstraint above
                foreach (AstTable.AstTableKeyBaseNode keyBase in astNode.Keys)
                {
                    PhysicalTSQL.Constraint keyPhysicalConstraint = null;
                    if (keyBase is AstTable.AstTablePrimaryKeyNode)
                    {
                        keyPhysicalConstraint = new Ssis2008Emitter.IR.TSQL.PrimaryKeyConstraint();
                    }
                    else if (keyBase is AstTable.AstTableIdentityNode)
                    {
                        PhysicalTSQL.IdentityConstraint physicalIdentity = new Ssis2008Emitter.IR.TSQL.IdentityConstraint();
                        physicalIdentity.Seed = ((AstTable.AstTableIdentityNode)keyBase).Seed;
                        physicalIdentity.Increment = ((AstTable.AstTableIdentityNode)keyBase).Increment;
                        keyPhysicalConstraint = physicalIdentity;
                    }
                    else
                    {
                        keyPhysicalConstraint = new Ssis2008Emitter.IR.TSQL.Constraint();
                    }

                    if (keyPhysicalConstraint != null)
                    {
                        keyPhysicalConstraint.Clustered = keyBase.Clustered;
                        keyPhysicalConstraint.DropExisting = keyBase.DropExisting;
                        keyPhysicalConstraint.IgnoreDupKey = keyBase.IgnoreDupKey;
                        keyPhysicalConstraint.Name = keyBase.Name;
                        keyPhysicalConstraint.Online = keyBase.Online;
                        keyPhysicalConstraint.PadIndex = keyBase.PadIndex;
                        keyPhysicalConstraint.Parent = table;
                        keyPhysicalConstraint.SortInTempdb = keyBase.SortInTempdb;
                        keyPhysicalConstraint.Unique = keyBase.Unique;
                        foreach (AstTable.AstTableKeyColumnNode key in keyBase.Columns)
                        {
                            PhysicalTSQL.Key physicalKey = new Ssis2008Emitter.IR.TSQL.Key();
                            physicalKey.Name = key.Column.Name;
                            keyPhysicalConstraint.Keys.Add(physicalKey);
                        }
                        table.ConstraintList.Add(keyPhysicalConstraint);
                    }
                }

                foreach (AstTable.AstTableForeignKeyNode foreignKey in astNode.ForeignKeys)
                {
                    PhysicalTSQL.ForeignKeyConstraint physicalForeignKey = new Ssis2008Emitter.IR.TSQL.ForeignKeyConstraint();
                    foreach (AstTable.AstTableForeignKeyColumnNode column in foreignKey.Columns)
                    {
                        PhysicalTSQL.Key localColumn = new Ssis2008Emitter.IR.TSQL.Key();
                        localColumn.Name = column.OutputName;
                        physicalForeignKey.LocalColumnList.Add(localColumn);
                        PhysicalTSQL.Key foreignColumn = new Ssis2008Emitter.IR.TSQL.Key();
                        foreignColumn.Name = column.ColumnName;
                        physicalForeignKey.ForeignColumnList.Add(foreignColumn);
                    }
                    physicalForeignKey.Table = foreignKey.Dimension.Name;
                    physicalForeignKey.Name = foreignKey.Name;
                    table.AddForeignKeyConstraint(physicalForeignKey);
                }

                foreach (AstTable.AstTableIndexNode index in astNode.Indexes)
                {
                    PhysicalTSQL.Index physicalIndex = new Ssis2008Emitter.IR.TSQL.Index();
                    physicalIndex.Clustered = index.Clustered;
                    physicalIndex.DropExisting = index.DropExisting;
                    physicalIndex.IgnoreDupKey = index.IgnoreDupKey;
                    physicalIndex.Name = index.Name;
                    physicalIndex.Online = index.Online;
                    physicalIndex.PadIndex = index.PadIndex;
                    physicalIndex.Parent = table;
                    physicalIndex.SortInTempdb = index.SortInTempdb;
                    physicalIndex.Unique = index.Unique;

                    foreach (AstTable.AstTableIndexColumnNode key in index.Columns)
                    {
                        PhysicalTSQL.Key physicalKey = new Ssis2008Emitter.IR.TSQL.Key();
                        physicalKey.Name = key.Column.Name;
                        physicalIndex.Keys.Add(physicalKey);
                    }
                    foreach (AstTable.AstTableColumnBaseNode leaf in index.Leafs)
                    {
                        PhysicalTSQL.Leaf physicalLeaf = new Ssis2008Emitter.IR.TSQL.Leaf();
                        physicalLeaf.Name = leaf.Name;
                        physicalIndex.Leaves.Add(physicalLeaf);
                    }

                    table.Indexes.IndexList.Add(physicalIndex);
                }
                //TODO: table.Tags;

                return table;
            }
            catch (Exception e)
            {
                throw new SSISEmitterException(astNode, e);
            }
        }
Пример #5
0
 public void AddForeignKeyConstraint(ForeignKeyConstraint constraint)
 {
     base.AddChild(constraint);
     _foreignKeyConstraintList.Add(constraint);
 }