Beispiel #1
0
 public IndexDefinition(ITableDefinition table, string name)
 {
     Table = table;
     Name = name;
     Columns = new List<IndexColumnDefinition>();
     Grbit = CreateIndexGrbit.None;
     Density = 100;
 }
        private void BuildAddTableNode(ITableDefinition table, IAstNode parent)
        {
            IAddTableNode addTableNode = new AddTableNode(parent, table.Name);
            parent.ChildNodes.Add(addTableNode);

            foreach(IColumnDefinition column in table.Columns)
            {
                BuildAddColumnNode(column, addTableNode);
            } // foreach
        }
 public PagingControlConfiguration(ITableDefinition tableDefinition)
 {
     TableDefinition = tableDefinition;
     ContainerCssClass = "table-pagination";
     DisabledCssClass = "disabled";
     ActiveCssClass = "active";
     FirstPageText = "<<";
     PreviousPageText = "<";
     NextPageText = ">";
     LastPageText = ">>";
 }
        /// <summary>
        /// Adds fields to table.
        /// </summary>
        /// <param name="tableDefinition">Table definition.</param>
        /// <param name="tableDescription">Table Description.</param>
        /// <param name="columns">Database columns.</param>
        private void _AddFieldsToTable(ITableDefinition tableDefinition,
                                       TableDescription tableDescription,
                                       ADOX.Columns columns)
        {
            Debug.Assert(null != tableDefinition);
            Debug.Assert(null != tableDescription);
            Debug.Assert(null != columns);

            ICollection<string> fields = tableDefinition.Fields;
            foreach (string field in fields)
            {
                FieldInfo info = tableDescription.GetFieldInfo(field);
                Debug.Assert(null != info);
                columns.Append(info.Name, _ConvertType(info.Type), info.Size);

                // make field not required
                ADOX.Column column = columns[info.Name];
                column.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
            }
        }
        /// <summary>
        /// Gets the automatic number constraint.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns>System.String.</returns>
        private string GetAutoNumberConstraint(ITableDefinition table)
        {
            string sql = string.Empty;
            foreach (var field in table.FieldList.Where(field => field.GenerateDbColumn && !string.IsNullOrWhiteSpace(field.ColumnName) && field.ColumnType == ColumnTypes.AutoNumber))
            {
                sql += string.Format(@"
IF EXISTS (SELECT * FROM sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id WHERE i.name = N'IXU_{0}_{1}' AND t.name = N'{0}')
    DROP INDEX IXU_{0}_{1} ON {0}"
                    , table.Name, field.ColumnName);
            }
            return sql;
        }
        /// <summary>
        /// Gets the add foreign key SQL.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="relationship">The relationship.</param>
        /// <param name="prefix">The prefix.</param>
        /// <returns>System.String.</returns>
        protected override string GetAddForeignKeySql(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null)
        {
            return string.Format(
                @"
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE  name = N'{4}{1}')
BEGIN
    ALTER TABLE [{4}{0}] WITH CHECK ADD CONSTRAINT [{4}{1}] FOREIGN KEY([{2}])
    REFERENCES [{3}] ([Id])

    ALTER TABLE [{4}{0}] CHECK CONSTRAINT [{4}{1}]
END
{5}
",
                table.Name,
                relationship.Name,
                relationship.IsManyToMany ? relationship.JoinFieldName : relationship.FKName,
                relationship.FKTableName,
                prefix,
                GenerateCreateIndexIfNotExists(CreateIndexDefinition(table, relationship, prefix)));
        }
 private static bool IsFullTextSearchSupported(ITableDefinition table)
 {
     return !Constants.NonFullTextSearchableProcesses.Contains(table.Name);
 }
        private FullTextIndexDefinition GetFullTextIndexDefinition(ITableDefinition table)
        {
            var index = new FullTextIndexDefinition { TableName = table.Name, CatalogName = Constants.SqlServerFullTextCatalogName };

            foreach (var field in table.FieldList.Where(IncludeInFullTextIndex))
            {
                var columnName = field.ColumnName;
                if (columnName == Constants.DerivedProcessColumnName)
                {
                    if (Constants.SealedProcesses.Contains(table.Name))
                    {
                        continue;
                    }

                    columnName = Constants.DerivedProcessDisplayNameColumnName;
                }

                index.Columns.Add(new FullTextIndexColumnDefinition { ColumnName = columnName, LCID = GetFullTextLanguageId(field.CultureName) });
            }

            return index;
        }
        /// <summary>
        /// Generates a script that creates the full-text index on the specified table.
        /// </summary>
        /// <param name="table">
        /// The table.
        /// </param>
        /// <returns>
        /// The script.
        /// </returns>
        protected override string GetCreateFullTextIndexScript(ITableDefinition table)
        {
            if (!IsFullTextSearchSupported(table))
            {
                return string.Empty;
            }

            var sql = new StringBuilder();
            var index = GetFullTextIndexDefinition(table);

            if (index != null && index.Columns.Any())
            {
                sql.AppendFormat(
                    CultureInfo.InvariantCulture,
                    @"
IF NOT EXISTS(
    SELECT 1
    FROM sys.fulltext_catalogs ftc
    WHERE ftc.name = '{2}'
)
BEGIN
    CREATE FULLTEXT CATALOG {2} WITH ACCENT_SENSITIVITY = OFF AUTHORIZATION [dbo]
END

IF NOT EXISTS(
    SELECT 1
    FROM sys.fulltext_indexes fti
    WHERE fti.object_id = OBJECT_ID('[dbo].[{0}]'))
BEGIN
    CREATE FULLTEXT INDEX ON [dbo].[{0}]
    ({1})
    KEY INDEX PK_{0}
    ON {2}
    WITH STOPLIST = OFF
END
",
                    table.Name,
                    string.Join(", ", index.Columns.Select(x => string.Format("[{0}] LANGUAGE {1}", x.ColumnName, x.LCID))),
                    Constants.SqlServerFullTextCatalogName);
            }

            return sql.ToString();
        }
 private static IEnumerable<DataIndexDbDefinition> GetForeignKeyIndexes(ITableDefinition table)
 {
     return table.Relationships.Select(relationship => CreateIndexDefinition(table, relationship));
 }
        private static IEnumerable<DataIndexDbDefinition> GetTableIndexes(ITableDefinition table, string prefix)
        {
            const char EscapeCharacter = '!';

            using (var ctx = GetRuntimeDatabaseConnectionManager())
            {
                var commandText = string.Format(CultureInfo.InvariantCulture, @"
SELECT
     ind.name as IndexName
    ,t.name	as ProcessName
    ,ind.filter_definition as FilterDefinition
    ,col.name as FieldName
    ,ic.is_included_column as IsIncluded
FROM
    sys.indexes ind
    INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
    INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
    INNER JOIN sys.tables t ON ind.object_id = t.object_id  AND ind.name LIKE '{0}%' ESCAPE '{1}'
WHERE ind.object_id = (SELECT OBJECT_ID(@tableName))
ORDER BY ind.name, ic.key_ordinal", AdoHelper.EscapeLikePattern(prefix, EscapeCharacter), EscapeCharacter);

                using (var command = new SqlCommand(commandText, ctx.Connection))
                {
                    command.Parameters.AddWithValue("@tableName", table.Name);

                    using (var reader = new SafeDataReader(command.ExecuteReader()))
                    {
                        var indexList = new Collection<DataIndexDbDefinition>();

                        while (reader.Read())
                        {
                            var indexName = reader.GetString(0);
                            var processName = reader.GetString(1);
                            var index = indexList.FirstOrDefault(ix => ix.IndexName == indexName && ix.ProcessName == processName);

                            if (index == null)
                            {
                                index = new DataIndexDbDefinition { IndexName = indexName, ProcessName = processName, FilterDefinition = reader.GetString(2) };
                                indexList.Add(index);
                            }

                            var fieldName = reader.GetString(3);
                            var included = reader.GetBoolean(4);

                            index.IndexFields.Add(new DataIndexFieldDbDefinition(fieldName, !included));
                        }

                        return indexList;
                    }
                }
            }
        }
        /// <summary>
        /// Gets the fk constraints SQL.
        /// </summary>
        /// <param name="dbTable">The database table.</param>
        /// <param name="fk">The fk.</param>
        /// <param name="table">The table.</param>
        /// <returns>System.String.</returns>
        protected override string GetFKConstraintsSql(DbTableDefinition dbTable, DbForeignKeyDefinition fk, ITableDefinition table)
        {
            var fKeys = string.Join(",", fk.ForeignKeyColumnsName);
            var pKeys = string.Join(",", fk.PrimaryKeyColumnsName);

            return string.Format(
                @"
IF (EXISTS (SELECT * FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_SCHEMA] = 'dbo' AND [TABLE_NAME] = N'{0}'))
BEGIN
ALTER TABLE [{0}] WITH CHECK ADD CONSTRAINT [{1}] FOREIGN KEY([{2}])
REFERENCES [{3}] ({4})

ALTER TABLE [{0}] CHECK CONSTRAINT [{1}]

END
",
                fk.ForeignKeyTableName,
                fk.ConstraintName,
                fKeys,
                fk.PrimaryKeyTableName,
                pKeys);
        }
        /// <summary>Gets the audit trigger script for multi cr table.</summary>
        /// <param name="table">The table.</param>
        /// <param name="fieldDefinition">The field definition.</param>
        /// <param name="primaryTable">The primary table.</param>
        /// <param name="foreignTable">The foreign table.</param>
        /// <returns></returns>
        private string GetAuditTriggerScriptForMultiCRTable(ITableDefinition table, TableFieldDefinition fieldDefinition, TableRelationshipDefinition primaryTable, TableRelationshipDefinition foreignTable)
        {
            if (fieldDefinition.ReferenceDisplayFields.Any(x =>
                    x.SystemName == fieldDefinition.ReferenceDisplayField
                    && (x.ColumnType == ColumnTypes.MultiReference || x.IsReverseRef)))
                return string.Empty;

            if (string.IsNullOrEmpty(fieldDefinition.ReferenceColumnTable) || string.IsNullOrEmpty(fieldDefinition.ReferencedColumnName))
                return string.Empty;

            var result = string.Format(@"
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_Audit]'))
	drop trigger [dbo].[{0}_Audit];
GO

CREATE TRIGGER [dbo].[{0}_Audit]
   ON [dbo].[{0}]
   AFTER INSERT,DELETE
AS 
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS ( SELECT * FROM INSERTED ) AND NOT EXISTS ( SELECT * FROM DELETED )
		RETURN
", table.Name);

            if (fieldDefinition.ReferenceColumnTable.Equals(fieldDefinition.ReferencedProcessName))
            {
                result += string.Format(@"
	IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED )
	BEGIN
		INSERT INTO audit.[{0}]
		SELECT 
			'D'
			,p.Id
			,'{1}'
			,'Link removed: Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX))
			,NULL
			,p.LastModifiedOn
			,p.LastModifiedBy 
		FROM DELETED d
		INNER JOIN dbo.{0} p ON p.Id = d.[{4}]
		LEFT OUTER JOIN [{2}] si ON si.Id = d.[{5}]
		RETURN
	END

	IF NOT EXISTS ( SELECT * FROM DELETED )
	BEGIN
		INSERT INTO audit.[{0}]
		SELECT 
			'I'
			,p.[Id]
			,'{1}'
			,NULL
			,'Link added: Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX))
			,p.LastModifiedOn
			,p.LastModifiedBy 
		FROM INSERTED i
		INNER JOIN dbo.{0} p ON p.Id = i.[{4}]
		LEFT OUTER JOIN [{2}] si ON si.Id = i.[{5}]
		RETURN
	END
END
GO
", primaryTable.FKTableName, fieldDefinition.DisplayName.DoubleSingleQuote(), fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferenceDisplayField, primaryTable.JoinFieldName, foreignTable.JoinFieldName);

            }
            else
            {
                result += string.Format(@"
	IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED )
	BEGIN
		INSERT INTO audit.[{0}]
		SELECT 
			'D'
			,d.{0}Id
			,'{1}'
			,'Link removed: Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX))
			,NULL
			,t.LastModifiedOn
			,t.LastModifiedBy 
		FROM DELETED d
		LEFT OUTER JOIN dbo.{2} p ON p.Id = d.[{5}]
		INNER JOIN [{4}] spi ON spi.Id = p.ParentId
		INNER JOIN dbo.[{0}] t on t.Id = d.{0}Id
		RETURN
	END

	IF NOT EXISTS ( SELECT * FROM DELETED )
	BEGIN
		INSERT INTO audit.[{0}]
		SELECT 
			'I'
			,i.{0}Id
			,'{1}'
			,NULL
			,'Link added: Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX))
			,t.LastModifiedOn
			,t.LastModifiedBy 
		FROM INSERTED i
		LEFT OUTER JOIN dbo.{2} p ON p.Id = i.[{5}]
		INNER JOIN [{4}] spi ON spi.Id = p.ParentId
		INNER JOIN dbo.[{0}] t on t.Id = i.{0}Id
		RETURN
	END
END
GO
", primaryTable.FKTableName, fieldDefinition.DisplayName.DoubleSingleQuote(), foreignTable.FKTableName, fieldDefinition.ReferenceDisplayField, fieldDefinition.ReferenceColumnTable, foreignTable.JoinFieldName);

            }
            return result;
        }
        /// <summary>
        /// Generate trigger code for every process field.
        /// </summary>
        /// <param name="table">Table where we create this trigger.</param>
        /// <param name="result">Result code.</param>
        /// <param name="isForInsert">true if insert trigger is generating, false if update trigger.</param>
        private static void GenerateAuditTriggerFields(ITableDefinition table, StringBuilder result, bool isForInsert)
        {
            foreach (var fieldDefinition in
                table.FieldList.Where(f => (!f.IsHidden || f.IsLocalizedCopy) && !f.IsSystemField && f.GenerateDbColumn)
                     .Where(
                         fieldDefinition =>
                         fieldDefinition.ColumnType != ColumnTypes.Image && fieldDefinition.ColumnType != ColumnTypes.AuditTrail))
            {
                if (fieldDefinition.ColumnName == Constants.CurrentStateColumnName)
                {
                    if (isForInsert)
                    {
                        result.AppendFormat(@"
		INSERT INTO [audit].[{0}]
		SELECT 'I', i.Id, '{1}', NULL, si.Name, i.LastModifiedOn, i.LastModifiedBy 
		FROM INSERTED i
		LEFT OUTER JOIN [{2}] si ON si.Id = i.[{3}]
", table.Name, fieldDefinition.ColumnName, Constants.StateProcessName, Constants.CurrentStateColumnName);
                    }
                    else
                    {
                        result.AppendFormat(@"
	IF UPDATE([{1}])
		INSERT INTO [audit].[{0}]
		SELECT 'U', d.Id, '{1}', sd.Name, si.Name, i.LastModifiedOn, i.LastModifiedBy 
		FROM DELETED d
		INNER JOIN INSERTED i ON i.Id = d.Id
		LEFT OUTER JOIN [{2}] sd ON sd.Id = d.[{3}]
		LEFT OUTER JOIN [{2}] si ON si.Id = i.[{3}]
		WHERE  (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL)
			OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL)
			OR (i.[{1}] <> d.[{1}])
", table.Name, fieldDefinition.ColumnName, Constants.StateProcessName, Constants.CurrentStateColumnName);
                    }
                }
                else if (fieldDefinition.ColumnType == ColumnTypes.Reference)
                {
                    if (
                        fieldDefinition.ReferenceDisplayFields.Any(
                            x =>
                            x.SystemName == fieldDefinition.ReferencedColumnName
                            && (x.ColumnType == ColumnTypes.MultiReference || x.IsReverseRef)))
                    {
                        continue;
                    }

                    if (string.IsNullOrEmpty(fieldDefinition.ReferenceColumnTable)
                        || string.IsNullOrEmpty(fieldDefinition.ReferencedColumnName))
                    {
                        continue;
                    }

                    if (fieldDefinition.ReferenceColumnTable.Equals(fieldDefinition.ReferencedProcessName))
                    {
                        if (isForInsert)
                        {
                            result.AppendFormat(
    @"
		    INSERT INTO [audit].[{0}]
		    SELECT 'I', i.Id, '{1}', NULL, 'Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX)), i.LastModifiedOn, i.LastModifiedBy 
		    FROM INSERTED i
		    LEFT OUTER JOIN [{2}] si ON si.Id = i.[{1}]
    ", table.Name, fieldDefinition.ColumnName, fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferencedColumnName);

                        }
                        else
                        {
                            result.AppendFormat(
                                @"
	    IF UPDATE([{1}])
		    INSERT INTO [audit].[{0}]
		    SELECT 'U', d.Id, '{1}', 'Process={2}, Id=' + CAST(sd.Id AS VARCHAR(10)) + ', ' + CAST(sd.[{3}] AS NVARCHAR(MAX)), 'Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX)), i.LastModifiedOn, i.LastModifiedBy 
		    FROM DELETED d
		    INNER JOIN INSERTED i ON i.Id = d.Id
		    LEFT OUTER JOIN [{2}] sd ON sd.Id = d.[{1}]
		    LEFT OUTER JOIN [{2}] si ON si.Id = i.[{1}]
		    WHERE  (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL)
			    OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL)
			    OR (i.[{1}] <> d.[{1}])
    ", table.Name, fieldDefinition.ColumnName, fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferencedColumnName);
                        }
                    }
                    else
                    {
                        if (isForInsert)
                        {
                            result.AppendFormat(
                                @"
		    INSERT INTO [audit].[{0}]
		    SELECT 'I', i.Id, '{1}', NULL, 'Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX)), i.LastModifiedOn, i.LastModifiedBy 
		    FROM INSERTED i 
		    LEFT OUTER JOIN dbo.[{4}] si ON i.[{1}] = si.Id
		    INNER JOIN [{2}] spi ON spi.Id = si.ParentId
    ", table.Name, fieldDefinition.ColumnName, fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferencedColumnName, fieldDefinition.ReferencedProcessName);
                        }
                        else
                        {
                            result.AppendFormat(
                                @"
	    IF UPDATE([{1}])
		    INSERT INTO [audit].[{0}]
		    SELECT 'U', d.Id, '{1}', 'Process={2}, Id=' + CAST(spd.Id AS VARCHAR(10)) + ', ' + CAST(spd.[{3}] AS NVARCHAR(MAX)), 'Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX)), i.LastModifiedOn, i.LastModifiedBy 
		    FROM DELETED d
		    INNER JOIN INSERTED i ON i.Id = d.Id
		    LEFT OUTER JOIN dbo.[{4}] sd ON d.[{1}] = sd.Id
		    LEFT OUTER JOIN dbo.[{4}] si ON i.[{1}] = si.Id
		    LEFT OUTER JOIN [{2}] spd ON spd.Id = sd.ParentId
		    LEFT OUTER JOIN [{2}] spi ON spi.Id = si.ParentId
		    WHERE  (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL)
			    OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL)
			    OR (i.[{1}] <> d.[{1}])
    ", table.Name, fieldDefinition.ColumnName, fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferencedColumnName, fieldDefinition.ReferencedProcessName);
                        }
                    }
                }
                else if (fieldDefinition.ColumnType == ColumnTypes.File)
                {
                    if (isForInsert)
                    {
                        result.AppendFormat(@"
		INSERT INTO [audit].[{0}]
		SELECT 'I', i.Id, '{1}', NULL, COALESCE(NULLIF(fp.OriginalFileName,''), NULLIF(fp.ReportName,''), ''), i.LastModifiedOn, i.LastModifiedBy 
		FROM INSERTED i 
		LEFT OUTER JOIN dbo.[{2}] fp ON fp.Id = i.[{1}]
", table.Name, fieldDefinition.ColumnName, Constants.FileProcessName);
                    }
                    else
                    {
                        result.AppendFormat(@"
	IF UPDATE([{1}])
		INSERT INTO [audit].[{0}]
		SELECT 'U',d.Id,'{1}',d.[{1}], i.[{1}], i.LastModifiedOn, i.LastModifiedBy 
		FROM DELETED d
		INNER JOIN INSERTED i ON i.Id = d.Id
		WHERE  (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL)
			OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL)
			OR (i.[{1}] <> d.[{1}])
", table.Name, fieldDefinition.ColumnName);
                    }
                }
                else if (fieldDefinition.ColumnType == ColumnTypes.Approval)
                {
                    if (isForInsert)
                    {
                        result.AppendFormat(
                            @"
        INSERT INTO [audit].[{0}]
        SELECT 'I', i.[Id], '{1}', NULL, {2}, i.[LastModifiedOn], i.[LastModifiedBy]
        FROM INSERTED i
            LEFT OUTER JOIN [dbo].[{3}] ap ON ap.[Id] = i.[{4}]
",
                            table.Name,
                            AdoHelper.Escape(fieldDefinition.DisplayName),
                            ConvertApprovalStateToDisplayName("ap.[ApprovalState]"),
                            Constants.ApprovalProcessName,
                            fieldDefinition.ColumnName);
                    }
                    else
                    {
                        result.AppendFormat(
                            @"
    IF UPDATE([{0}])
        INSERT INTO [audit].[{1}]
        SELECT 'U', d.[Id], '{2}', {3}, {4}, i.[LastModifiedOn], i.[LastModifiedBy]
        FROM DELETED d
            INNER JOIN INSERTED i ON i.[Id] = d.[Id]
            LEFT OUTER JOIN [dbo].[{5}] a1 ON a1.[Id] = d.[{0}]
            LEFT OUTER JOIN [dbo].[{5}] a2 ON a2.[Id] = i.[{0}]
        WHERE  (d.[{0}] IS NULL AND i.[{0}] IS NOT NULL)
            OR (i.[{0}] IS NULL AND d.[{0}] IS NOT NULL)
            OR (i.[{0}] <> d.[{0}])
",
                            fieldDefinition.ColumnName,
                            table.Name,
                            AdoHelper.Escape(fieldDefinition.DisplayName),
                            ConvertApprovalStateToDisplayName("a1.[ApprovalState]"),
                            ConvertApprovalStateToDisplayName("a2.[ApprovalState]"),
                            Constants.ApprovalProcessName);
                    }
                }
                else if (fieldDefinition.AllowRichText)
                {
                    if (isForInsert)
                    {
                        result.AppendFormat(@"
		INSERT INTO [audit].[{0}]
		SELECT 'I', i.Id, '{1}', NULL, dbo.GetRichTextText(i.[{1}]), i.LastModifiedOn, i.LastModifiedBy 
		FROM INSERTED i
", table.Name, fieldDefinition.ColumnName);
                    }
                    else
                    {
                        result.AppendFormat(@"
	IF UPDATE([{1}])
		INSERT INTO [audit].[{0}]
		SELECT 'U', d.Id, '{1}', dbo.GetRichTextText(d.[{1}]), dbo.GetRichTextText(i.[{1}]), i.LastModifiedOn, i.LastModifiedBy 
		FROM DELETED d
		INNER JOIN INSERTED i ON i.Id = d.Id
		WHERE  (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL)
			OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL)
			OR (i.[{1}] <> d.[{1}])
", table.Name, fieldDefinition.ColumnName);
                    }
                }
                else if (GetSqlType(fieldDefinition) == "[xml]")
                {
                    if (isForInsert)
                    {
                        if (fieldDefinition.ColumnType == ColumnTypes.Choice)
                        {
                            result.AppendFormat(
@"
        INSERT INTO [audit].[{0}]
		SELECT 
			'I', 
			i.Id, 
			'{1}', 
			NULL, 

			(SELECT 
				STUFF(
					(SELECT 
        				CHAR(13) + CHAR(10) + ISNULL(Choice, '')
			        FROM (
				        SELECT
					        CASE 
						        WHEN choice.value('../@IsGlobalChoice', 'bit') = 1 
						            THEN 'Global Choice' 
						        ELSE 'Choice: ' + choice.value('@Choice', 'nvarchar(50)') + ', ' + 
							        'Acceptance criteria: ' + choice.value('@AcceptanceCriteria', 'nvarchar(50)') + ', ' +
							        'Score: ' + choice.value('@Score', 'nvarchar(50)')
					        END AS Choice
				        FROM i.[{1}].nodes('/ChoiceInfo/Choice') col(choice)
	        ) AS T FOR XML PATH(''), ROOT('root'), TYPE).value('/root[1]', 'NVARCHAR(MAX)'), 1, 2, '')),
						
			i.LastModifiedOn, 
			i.LastModifiedBy 
		FROM INSERTED i

",
 table.Name,
 fieldDefinition.ColumnName);
                        }
                        else
                        {
                            result.AppendFormat(
@"
		INSERT INTO [audit].[{0}]
		SELECT 'I', i.Id, '{1}', NULL, CAST(i.[{1}] AS nvarchar(MAX)), i.LastModifiedOn, i.LastModifiedBy 
		FROM INSERTED i
",
 table.Name,
 fieldDefinition.ColumnName);
                        }
                    }
                    else
                    {
                        if (fieldDefinition.ColumnType == ColumnTypes.Choice)
                        {
                            result.AppendFormat(
@"
	IF UPDATE([{1}])
		INSERT INTO [audit].[{0}]
        SELECT 
			'U', 
			d.Id, 
			'{1}', 

			(SELECT 
				STUFF(
					(SELECT 
				        CHAR(13) + CHAR(10) + ISNULL(Choice, '')
			        FROM (
				        SELECT
					        CASE 
						        WHEN choice.value('../@IsGlobalChoice', 'bit') = 1 
						            THEN 'Global Choice' 
						        ELSE 'Choice: ' + choice.value('@Choice', 'nvarchar(50)') + ', ' + 
							        'Acceptance criteria: ' + choice.value('@AcceptanceCriteria', 'nvarchar(50)') + ', ' +
							        'Score: ' + choice.value('@Score', 'nvarchar(50)')
					        END AS Choice
				        FROM d.[{1}].nodes('/ChoiceInfo/Choice') col(choice)
			) AS T FOR XML PATH(''), ROOT('root'), TYPE).value('/root[1]', 'NVARCHAR(MAX)'), 1, 2, '')),

			(SELECT 
				STUFF(
					(SELECT 
				        CHAR(13) + CHAR(10) + ISNULL(Choice, '')
			        FROM (
				        SELECT
					        CASE 
						        WHEN choice.value('../@IsGlobalChoice', 'bit') = 1 
						            THEN 'Global Choice' 
						        ELSE 'Choice: ' + choice.value('@Choice', 'nvarchar(50)') + ', ' + 
							        'Acceptance criteria: ' + choice.value('@AcceptanceCriteria', 'nvarchar(50)') + ', ' +
							        'Score: ' + choice.value('@Score', 'nvarchar(50)')
					        END AS Choice
				        FROM i.[{1}].nodes('/ChoiceInfo/Choice') col(choice)
			) AS T FOR XML PATH(''), ROOT('root'), TYPE).value('/root[1]', 'NVARCHAR(MAX)'), 1, 2, '')),

			i.LastModifiedOn, 
			i.LastModifiedBy 

		FROM DELETED d
		INNER JOIN INSERTED i ON i.Id = d.Id
		WHERE  (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL)
			OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL)
",
 table.Name,
 fieldDefinition.ColumnName);
                        }
                        else
                        {
                            result.AppendFormat(
@"
	IF UPDATE([{1}])
		INSERT INTO [audit].[{0}]
		SELECT 'U', d.Id, '{1}', CAST(d.[{1}] AS nvarchar(MAX)), CAST(i.[{1}] AS nvarchar(MAX)), i.LastModifiedOn, i.LastModifiedBy 
		FROM DELETED d
		INNER JOIN INSERTED i ON i.Id = d.Id
		WHERE  (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL)
			OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL)
",
 table.Name,
 fieldDefinition.ColumnName);
                        }
                    }
                }
                else
                {
                    if (isForInsert)
                    {
                        result.AppendFormat(@"
		INSERT INTO [audit].[{0}]
		SELECT 'I', i.Id, '{1}', NULL, i.[{1}], i.LastModifiedOn, i.LastModifiedBy 
		FROM INSERTED i 
", table.Name, fieldDefinition.ColumnName);
                    }
                    else
                    {
                        result.AppendFormat(@"
	IF UPDATE([{1}])
		INSERT INTO [audit].[{0}]
		SELECT 'U',d.Id,'{1}',d.[{1}], i.[{1}], i.LastModifiedOn, i.LastModifiedBy 
		FROM DELETED d
		INNER JOIN INSERTED i ON i.Id = d.Id
		WHERE  (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL)
			OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL)
			OR (i.[{1}] <> d.[{1}])
", table.Name, fieldDefinition.ColumnName);
                    }
                }
            }
        }
        /// <summary>
        /// Gets the audit trigger script.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns>System.String.</returns>
        protected override string GetAuditTriggerScript(ITableDefinition table)
        {
            var sql = string.Format(@"
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_Audit]'))
	drop trigger [dbo].[{0}_Audit];
GO
", table.Name);

            if (Constants.NonAuditableProcesses.Contains(table.Name))
                return sql;

            var result = new StringBuilder(sql);
            result.AppendFormat(
                @"
CREATE TRIGGER [dbo].[{0}_Audit] 
   ON  [dbo].[{0}] 
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS ( SELECT * FROM INSERTED ) AND NOT EXISTS ( SELECT * FROM DELETED )
		RETURN
	
	IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED )
	BEGIN
		-- deleted from outside of Elements
		INSERT INTO audit.[{0}]
		SELECT 
			'D'
			,d.Id
			,NULL
			,'Last modified by: ' + d.LastModifiedBy
			,'Last modified on: ' + CONVERT(VARCHAR(8), d.LastModifiedOn, 112) + ' ' + CONVERT(VARCHAR(12), d.LastModifiedOn, 114)
			,GETDATE()
			,SYSTEM_USER 
		FROM DELETED d
		RETURN
	END

	IF NOT EXISTS ( SELECT * FROM DELETED )
	BEGIN
		INSERT INTO audit.[{0}]
		SELECT 
			'I'
			,i.Id
			,NULL
			,NULL
			,NULL
			,GETDATE()
			,i.LastModifiedBy 
		FROM INSERTED i

", table.Name);

            GenerateAuditTriggerFields(table, result, true);

            result.Append(@"
		RETURN
	END

");

            if (table.FieldList.Any(f => f.SystemName.Equals(Constants.IsRemovedColumnName)))
            {
                result.AppendFormat(@"
	IF UPDATE([{1}])
		INSERT INTO [audit].[{0}]
		SELECT 'D',d.Id,'{1}',d.[{1}], i.[{1}], i.LastModifiedOn, i.LastModifiedBy 
		FROM DELETED d
		INNER JOIN INSERTED i ON i.Id = d.Id
		WHERE  (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL)
			OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL)
			OR (i.[{1}] <> d.[{1}])
", table.Name, Constants.IsRemovedColumnName);
            }

            GenerateAuditTriggerFields(table, result, false);

            result.AppendLine("END");

            result.AppendLine("GO");

            return result.ToString();
        }
        /// <summary>
        /// Generates a script that returns the id mappings for the specified reverse cross-reference or tree-view field.
        /// </summary>
        /// <param name="table">
        /// The table that contains the reverse cross-reference or tree-view field.
        /// </param>
        /// <param name="reverseReferenceField">
        /// The reverse cross-reference or tree-view field.
        /// </param>
        /// <returns>
        /// The script.
        /// </returns>
        private string GetReverseCrossReferenceIdMappingsSelect(ITableDefinition table, TableFieldDefinition reverseReferenceField)
        {
            var referenceField = reverseReferenceField.ReferenceField;

            if (reverseReferenceField.ColumnType == ColumnTypes.ReverseReference)
            {
                if (referenceField.ColumnType == ColumnTypes.Reference)
                {
                    if (reverseReferenceField.ShowLatestVersion)
                    {
                        return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t4.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        CROSS APPLY (
            SELECT TOP 1 t2.[Id]
            FROM [dbo].[{1}] t2
            WHERE t2.[Id] = (
                SELECT TOP 1 t3.[Id]
                FROM [dbo].[{1}] t3
                WHERE t3.[IsRemoved] = 0 AND t3.[{2}] = t1.[Id] AND t3.[{3}] = t2.[{3}]
                ORDER BY LEN(t3.[VersionNumber]) DESC, t3.[VersionNumber] DESC)
            ORDER BY t2.[Id]
        ) t4
    WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName, Constants.VersionSeriesIdColumnName);
                    }

                    return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        CROSS APPLY (
            SELECT TOP 1 [Id]
            FROM [dbo].[{1}]
            WHERE [IsRemoved] = 0 AND [{2}] = t1.[Id]) t2
    WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName);
                }

                if (referenceField.ColumnType == ColumnTypes.MultiReference)
                {
                    if (referenceField.ReferenceField != null)
                    {
                        // MCR with link field.
                        // Note: A RCR based on a MCR with link field will always return just one item, therefore we don't need to check latest version.
                        return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.ReferenceField.SystemName);
                    }

                    // MCR with join table.
                    var joinTableName = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableName(
                        referenceField.DefinedIn.SystemName,
                        referenceField.SystemName,
                        table.Name);
                    var masterKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableMasterKey(referenceField.DefinedIn.SystemName, table.Name);
                    var childKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableChildKey(table.Name);

                    if (reverseReferenceField.ShowLatestVersion)
                    {
                        return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t5.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        CROSS APPLY (
            SELECT TOP 1 t2.[Id]
            FROM [dbo].[{1}] t2
            WHERE t2.[Id] = (
                SELECT TOP 1 t3.[Id]
                FROM [dbo].[{1}] t3
                    INNER JOIN [dbo].[{2}] t4 ON t4.[{3}] = t3.[Id]
                WHERE t3.[IsRemoved] = 0 AND t3.[{4}] = t2.[{4}] AND t4.[{5}] = t1.[Id]
                ORDER BY LEN(t3.[VersionNumber]) DESC, t3.[VersionNumber] DESC)
            ORDER BY t2.[Id]
        ) t5
    WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, joinTableName, masterKey, Constants.VersionSeriesIdColumnName, childKey);
                    }

                    return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t4.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        CROSS APPLY (
            SELECT TOP 1 t3.[Id]
            FROM [dbo].[{1}] t2
                INNER JOIN [dbo].[{2}] t3 ON t3.[Id] = t2.[{3}] AND t3.[IsRemoved] = 0
            WHERE t2.[{4}] = t1.[Id]
            ORDER BY t3.[Id]) t4
    WHERE t1.[IsRemoved] = 0
", table.Name, joinTableName, referenceField.DefinedIn.SystemName, masterKey, childKey);
                }

                if (referenceField.ColumnType == ColumnTypes.Checklist)
                {
                    return string.Format(
                        CultureInfo.InvariantCulture,
                        @"
    SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id]
        INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
",
                        table.Name,
                        QueryGeneratorUtils.GetChecklistJoinTableName(referenceField.DefinedIn.SystemName, referenceField.SystemName),
                        QueryGeneratorUtils.GetChecklistJoinTableChildKey(referenceField.ReferencedProcess.SystemName),
                        referenceField.DefinedIn.SystemName,
                        QueryGeneratorUtils.GetChecklistJoinTableMasterKey(referenceField.DefinedIn.SystemName));
                }

                throw new NotSupportedException("Invalid reference field.");
            }

            if (reverseReferenceField.ColumnType == ColumnTypes.ReverseMultiReference)
            {
                if (referenceField.ColumnType == ColumnTypes.Reference)
                {
                    if (reverseReferenceField.ShowLatestVersion)
                    {
                        return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0 AND t2.[Id] = (
        SELECT TOP 1 t3.[Id]
        FROM [dbo].[{1}] t3
        WHERE t3.[IsRemoved] = 0 AND t3.[{2}] = t1.[Id] AND t3.[{3}] = t2.[{3}]
        ORDER BY LEN(t3.[VersionNumber]) DESC, t3.[VersionNumber] DESC)
", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName, Constants.VersionSeriesIdColumnName);
                    }

                    return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName);
                }

                if (referenceField.ColumnType == ColumnTypes.MultiReference)
                {
                    if (referenceField.ReferenceField != null)
                    {
                        // MCR with link field.
                        // Note: A RCR based on a MCR with link field will always return just one item, therefore we don't need to check latest version.
                        return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.DefinedIn.SystemName, referenceField.ReferenceField.SystemName);
                    }

                    // MCR with join table.
                    var joinTableName = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableName(
                        referenceField.DefinedIn.SystemName,
                        referenceField.SystemName,
                        table.Name);
                    var masterKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableMasterKey(referenceField.DefinedIn.SystemName, table.Name);
                    var childKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableChildKey(table.Name);

                    if (reverseReferenceField.ShowLatestVersion)
                    {
                        return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id]
        INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0 AND t3.[Id] = (
        SELECT TOP 1 t4.[Id]
        FROM [dbo].[{3}] t4
            INNER JOIN [dbo].[{1}] t5 ON t5.[{4}] = t4.[Id]
        WHERE t4.[IsRemoved] = 0 AND t4.[{5}] = t3.[{5}] AND t5.[{2}] = t1.[Id]
        ORDER BY LEN(t4.[VersionNumber]) DESC, t4.[VersionNumber] DESC)
", table.Name, joinTableName, childKey, referenceField.DefinedIn.SystemName, masterKey, Constants.VersionSeriesIdColumnName);
                    }

                    return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id]
        INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
", table.Name, joinTableName, childKey, referenceField.DefinedIn.SystemName, masterKey);
                }

                throw new NotSupportedException("Invalid reference field.");
            }

            if (reverseReferenceField.ColumnType == ColumnTypes.TreeView)
            {
                if (!string.IsNullOrEmpty(reverseReferenceField.ReferenceMatchField) && reverseReferenceField.ReferenceMatchField != Constants.IdColumnName)
                {
                    var matchField = table.FieldList.FirstOrDefault(f => f.SystemName == reverseReferenceField.ReferenceMatchField);
                    if (matchField == null)
                    {
                        throw new InvalidOperationException(
                            string.Format("Cannot find match field '{0}' in table '{1}'.", reverseReferenceField.ReferenceMatchField, table.Name));
                    }

                    return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0
        INNER JOIN [dbo].[{3}] t3 ON t3.[{4}] = t2.[Id] AND t3.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
", table.Name, matchField.ReferenceProcess.SystemName, matchField.ColumnName, referenceField.DefinedIn.SystemName, referenceField.SystemName);
                }

                return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
", table.Name, reverseReferenceField.ReferenceProcess.SystemName, referenceField.SystemName);
            }

            throw new NotSupportedException("Invalid reference field.");
        }
        /// <summary>
        /// Gets the data copy SQL.
        /// </summary>
        /// <param name="dbTable">The database table.</param>
        /// <param name="table">The table.</param>
        /// <param name="prefix">The prefix.</param>
        /// <returns>System.String.</returns>
        protected override string GetDataCopySql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null)
        {
            var result = new StringBuilder();
            var insertColumnList = new StringBuilder();
            var selectColumnList = new StringBuilder();

            // copy all fields
            foreach (var field in table.FieldList)
            {
                if (!field.GenerateDbColumn)
                    continue;

                var dbColumn = dbTable.Columns.FirstOrDefault(x => x.ColumnName == field.ColumnName);
                // only if they present in old table
                if (dbColumn == null)
                    continue;

                insertColumnList.Append("[").Append(field.ColumnName).Append("],");

                var sqlType = GetSqlType(field);

                // convert values
                var oldType = GetSqlType(dbColumn);
                var newType = GetSqlType(field).ToLower();
                if (AreSameDataTypes(oldType, newType) == false)
                {
                    var defaultValue = string.IsNullOrEmpty(field.DefaultValue) ? "NULL" : field.DefaultValue;

                    if (IsNumericType(newType))
                        selectColumnList.Append("CASE WHEN IsNumeric(")
                                        .Append(dbColumn.ColumnName)
                                        .Append(")=1 THEN ")
                                        .Append("CAST(REPLACE([")
                                        .Append(dbColumn.ColumnName)
                                        .Append("], ',', '.') AS ")
                                        .Append(sqlType)
                                        .Append(") ELSE ")
                                        .Append(defaultValue)
                                        .Append(" END,");
                    else if (IsDateType(newType))
                        selectColumnList.Append("CASE WHEN IsDate(")
                                        .Append(dbColumn.ColumnName)
                                        .Append(")=1 THEN ")
                                        .Append("CAST([")
                                        .Append(dbColumn.ColumnName)
                                        .Append("] AS ")
                                        .Append(sqlType)
                                        .Append(") ELSE NULL END,");
                    else if (IsStringType(newType))
                        selectColumnList.Append("CAST([").Append(dbColumn.ColumnName).Append("] AS ").Append(sqlType).Append("),");
                    else
                        selectColumnList.Append(defaultValue).Append(",");
                }
                else if (dbColumn.ColumnType == "nvarchar" && dbColumn.ColumnSubType == string.Empty && newType != "nvarchar" && newType.Contains("nvarchar"))
                {
                    selectColumnList.Append("CONVERT(")
                                        .Append(newType)
                                        .Append(",")
                                        .Append(dbColumn.ColumnName)
                                        .Append("),");
                }
                else
                    selectColumnList.Append("[").Append(dbColumn.ColumnName).Append("],");
            }

            //if a key field has been changed
            var keyFields = table.FieldList.Where(x => x.IsPrimaryKey);
            var insertColumnListString = insertColumnList.ToString();
            foreach (var field in keyFields)
            {
                if (!insertColumnListString.Contains(field.ColumnName))
                {
                    Log4NetLogger.Instance.Log(LogSeverity.Error, "SqlServerDatabaseGenerator", string.Format("Failed to save data for the table '{0}'. Missing key column '{1}'.", dbTable.TableName, field.ColumnName));
                    return string.Empty;
                }
            }

            insertColumnList.Remove(insertColumnList.Length - 1, 1);
            selectColumnList.Remove(selectColumnList.Length - 1, 1);

            result.Append("IF EXISTS(SELECT * FROM ").Append(dbTable.TableName).AppendLine(")");
            result.Append("INSERT INTO [").Append(prefix).Append(table.Name).Append("] (").Append(insertColumnList).AppendLine(")");
            result.Append("SELECT ").Append(selectColumnList).Append(" FROM [").Append(dbTable.TableName).AppendLine("] WITH (HOLDLOCK TABLOCKX)");

            return result.ToString();
        }
        private static DataIndexDbDefinition CreateIndexDefinition(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null)
        {
            var tableName = prefix + table.Name;
            var indexColumns = new[] { relationship.IsManyToMany ? relationship.JoinFieldName : relationship.FKName };
            var filter = relationship.ExcludeRemovedItems ? "([IsRemoved]=(0))" : string.Empty;

            var index = new DataIndexDbDefinition(GetForeignKeyIndexName(tableName, indexColumns), tableName);
            index.IndexFields.AddRange(indexColumns.Select(columnName => new DataIndexFieldDbDefinition(columnName, true)));
            index.FilterDefinition = filter;

            return index;
        }
        /// <summary>
        /// Generates a script that drops the full-text index on the specified table.
        /// </summary>
        /// <param name="table">
        /// The table.
        /// </param>
        /// <param name="forceDrop">
        /// A value indicating whether the index should be dropped even if its definition hasn't changed.
        /// </param>
        /// <returns>
        /// The script.
        /// </returns>
        protected override string GetDropFullTextIndexScript(ITableDefinition table, bool forceDrop = false)
        {
            var sql = new StringBuilder();

            if (IsFullTextInstalled())
            {
                var currentIndex = GetExistingFullTextIndexDefinition(table);

                if (currentIndex != null)
                {
                    var dropIndex = forceDrop || !IsFullTextSearchSupported(table);

                    if (!dropIndex)
                    {
                        // Check if index structure has changed.
                        var newIndex = GetFullTextIndexDefinition(table);

                        if (!currentIndex.Equals(newIndex))
                        {
                            dropIndex = true;
                        }
                    }

                    if (dropIndex)
                    {
                        sql.AppendFormat(CultureInfo.InvariantCulture, @"
IF EXISTS(
    SELECT 1
    FROM sys.fulltext_indexes fti
    WHERE fti.object_id = OBJECT_ID('[dbo].[{0}]'))
BEGIN
    DROP FULLTEXT INDEX ON [dbo].[{0}]
END

GO
", table.Name);
                    }
                }
            }

            return sql.ToString();
        }
        /// <summary>
        /// Gets the primary key alter constraint SQL.
        /// </summary>
        /// <param name="artifPkField">The artif pk field.</param>
        /// <param name="table">The table.</param>
        /// <param name="prefix">The prefix.</param>
        /// <returns>System.String.</returns>
        protected override string GetPrimaryKeyAlterConstraintSql(IEnumerable<TableFieldDefinition> artifPkField, ITableDefinition table, string prefix = null)
        {
            var columnsList = from c in artifPkField select string.Format("[{0}] DESC", c.ColumnName);
            return string.Format(
                @"
                ALTER TABLE [{2}{0}] ADD CONSTRAINT [PK_{2}{0}] PRIMARY KEY CLUSTERED({1}) WITH (
	                    PAD_INDEX = OFF,
	                    STATISTICS_NORECOMPUTE = OFF,
	                    IGNORE_DUP_KEY = OFF,
	                    ALLOW_ROW_LOCKS = ON,
	                    ALLOW_PAGE_LOCKS = ON
	            ) {3}
                ",
                table.Name,
                string.Join(",", columnsList),
                prefix,
                CnGo);
        }
        private static FullTextIndexDefinition GetExistingFullTextIndexDefinition(ITableDefinition table)
        {
            const string CommandText = @"
DECLARE @tableId INT = OBJECT_ID(@tableName)

SELECT
     OBJECT_NAME(i.object_id)
    ,fc.name
FROM sys.fulltext_indexes i
    INNER JOIN sys.fulltext_catalogs fc ON fc.fulltext_catalog_id = i.fulltext_catalog_id
WHERE i.object_id = @tableId

SELECT
     c.name
    ,ic.language_id
FROM sys.fulltext_index_columns ic
    INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.object_id = @tableId";

            var fullTableName = string.Format(CultureInfo.InvariantCulture, "[dbo].[{0}]", table.Name);

            using (var connectionManager = GetRuntimeDatabaseConnectionManager())
            {
                using (var cmd = new SqlCommand(CommandText, connectionManager.Connection))
                {
                    cmd.Parameters.AddWithValue("@tableName", fullTableName);

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            var index = new FullTextIndexDefinition { TableName = reader.GetString(0), CatalogName = reader.GetString(1) };

                            if (reader.NextResult())
                            {
                                while (reader.Read())
                                {
                                    var column = new FullTextIndexColumnDefinition { ColumnName = reader.GetString(0), LCID = reader.GetInt32(1) };
                                    index.Columns.Add(column);
                                }
                            }

                            return index;
                        }

                        return null;
                    }
                }
            }
        }
 /// <summary>
 /// Gets the rename table SQL.
 /// </summary>
 /// <param name="dbTable">The database table.</param>
 /// <param name="table">The table.</param>
 /// <param name="prefix">The prefix.</param>
 /// <returns>System.String.</returns>
 protected override string GetRenameTableSql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null)
 {
     return string.Format("EXECUTE sp_rename N'{1}{0}', N'{2}', 'OBJECT'", table.Name, prefix, dbTable.TableName);
 }
 /// <summary>
 /// Gets the identity insert SQL.
 /// </summary>
 /// <param name="on">if set to <c>true</c> [on].</param>
 /// <param name="table">The table.</param>
 /// <param name="prefix">The prefix.</param>
 /// <returns>System.String.</returns>
 protected override string GetIdentityInsertSql(bool @on, ITableDefinition table, string prefix = null)
 {
     return string.Format("SET IDENTITY_INSERT {1}{0} {2} {3}", table.Name, prefix, on ? "ON" : "OFF", Environment.NewLine);
 }
        /// <summary>
        /// Gets the create indexes SQL.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns>System.String.</returns>
        protected override string GetCreateIndexesSql(ITableDefinition table)
        {
            var result = string.Empty;

            if (table.FieldList.Any(f => !string.IsNullOrEmpty(f.SystemName) && f.SystemName.Equals("IsRemoved")))
                result += string.Format(@"
IF NOT EXISTS (SELECT * FROM sys.indexes i WHERE name = N'IX_{0}_IsRemoved')
    CREATE NONCLUSTERED INDEX IX_{0}_IsRemoved ON dbo.{0} (IsRemoved)", table.Name);

            if (table.FieldList.Any(f => !string.IsNullOrEmpty(f.SystemName) && f.SystemName.Equals("VersionMasterId")))
                result += string.Format(@"
IF NOT EXISTS (SELECT * FROM sys.indexes i WHERE name = N'IX_{0}_VersionMasterId')
    CREATE NONCLUSTERED INDEX [IX_{0}_VersionMasterId] ON [dbo].[{0}] ([VersionMasterId]) INCLUDE ([Id])", table.Name);

            return result;
        }
        /// <summary>
        /// Gets the create table SQL.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="prefix">The prefix.</param>
        /// <param name="isTemporary">if set to <c>true</c> [is temporary].</param>
        /// <returns>System.String.</returns>
        protected override string GetCreateTableSql(ITableDefinition table, string prefix = null, bool isTemporary = false)
        {
            var result = new StringBuilder();
            result.AppendFormat("CREATE TABLE {2}{0}({1}", table.Name, Environment.NewLine, prefix ?? string.Empty);
            foreach (var field in table.FieldList.Where(field => field.GenerateDbColumn && !string.IsNullOrWhiteSpace(field.ColumnName) && field.ColumnType != ColumnTypes.AuditTrail))
            {
                result.AppendFormat("\t[{0}] {1}", field.ColumnName, GetSqlType(field));

                if (!string.IsNullOrEmpty(field.DefaultValue))
                    result.Append(GetDefaultValueClause(field));

                if (field.IsIdentity) result.Append("IDENTITY(1,1)");
                if (field.IsPrimaryKey) result.Append(" NOT NULL");
                if (field.ColumnName == Constants.LastModifiedColumnName) result.Append("NOT NULL DEFAULT GetDate()");

                result.AppendLine(",");
            }

            var idField = table.FieldList.FirstOrDefault(f => f.SystemName == Constants.IdColumnName && f.GenerateDbColumn);
            var versionMasterIdField = table.FieldList.FirstOrDefault(f => f.SystemName == Constants.VersionMasterId && f.GenerateDbColumn);
            if (idField != null && versionMasterIdField != null)
            {
                result.AppendFormat(
                    CultureInfo.InvariantCulture,
                    "[{0}] AS (CASE WHEN [{1}] > 0 THEN [{1}] ELSE [{2}] END) PERSISTED,",
                    Constants.VersionSeriesIdColumnName,
                    Constants.VersionMasterId,
                    Constants.IdColumnName).AppendLine();
            }

            if (!Constants.SealedProcesses.Contains(table.Name))
            {
                // Add a computed column that retrieves the derived process display name from DerivedProcess column.
                var derivedProcessField = table.FieldList.FirstOrDefault(f => f.SystemName == Constants.DerivedProcessColumnName);
                if (derivedProcessField != null)
                {
                    result.AppendFormat(CultureInfo.InvariantCulture, @"[{1}] AS (CASE
	WHEN LEN({0}) - LEN(REPLACE({0}, '|', '')) = 2
		THEN REVERSE(SUBSTRING(REVERSE({0}), 1, CHARINDEX('|', REVERSE({0})) - 1))
	WHEN LEN({0}) - LEN(REPLACE({0}, '|', '')) = 1
		THEN SUBSTRING({0}, 1, CHARINDEX('|', {0}) - 1)
	ELSE {0}
END) PERSISTED,", Constants.DerivedProcessColumnName, Constants.DerivedProcessDisplayNameColumnName).AppendLine();
                }
            }

            result.AppendLine(")");

            //TODO: this call can be removed after september, 2013
            //remove autoNumber field index
            result.AppendLine(GetAutoNumberConstraint(table));

            return result.ToString();
        }
        /// <summary>
        /// Gets the definition of the view that returns the id mappings for the specified reference field.
        /// </summary>
        /// <param name="table">
        /// The table that contains the reference field.
        /// </param>
        /// <param name="referenceField">
        /// The reference field.
        /// </param>
        /// <returns>
        /// The <see cref="ViewDefinition"/>.
        /// </returns>
        private ViewDefinition GetReferenceFieldIdMappingsView(ITableDefinition table, TableFieldDefinition referenceField)
        {
            var viewName = QueryGeneratorUtils.GetReferenceFieldIdMappingsViewName(table.Name, referenceField.SystemName);
            var sql = new StringBuilder();
            sql.AppendFormat(CultureInfo.InvariantCulture, @"CREATE VIEW [dbo].[{0}]
AS
(", viewName);

            sql.Append(GetReferenceFieldIdMappingsSelect(table, referenceField));

            sql.AppendFormat(CultureInfo.InvariantCulture, @"
)");

            return new ViewDefinition { Name = viewName, Definition = sql.ToString() };
        }
        /// <summary>
        /// Gets the create audit table.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns>System.String.</returns>
        protected override string GetCreateAuditTable(ITableDefinition table)
        {
            if (Constants.NonAuditableProcesses.Contains(table.Name))
            {
                return string.Format(@"
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('audit.{0}')) DROP TABLE [audit].[{0}]
", table.Name);
            }

            var result = GetCreateAuditSchema();

            return result + string.Format(@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[audit].[{0}]') AND type in (N'U'))
BEGIN
	CREATE TABLE [audit].[{0}](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Type] [char](1) NULL,
		[ItemId] [int] NOT NULL,
		[FieldName] [nvarchar](128) NULL,
		[OldValue] [nvarchar](MAX) NULL,
		[NewValue] [nvarchar](MAX) NULL,
		[UpdateDate] [datetime] NOT NULL DEFAULT (getdate()),
		[UserName] [nvarchar](128) NULL,
	 CONSTRAINT [PK_{0}_AuditId] PRIMARY KEY CLUSTERED ( [Id] DESC )
	)
END

IF NOT EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_ItemId'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_{0}_ItemId ON audit.{0}(ItemId)
END

IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_FieldName'
)
BEGIN
    DROP INDEX IX_{0}_FieldName ON audit.{0}
END

IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_Id'
)
BEGIN
    DROP INDEX IX_{0}_Id ON audit.{0}
END

IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_UpdateDate'
)
BEGIN
    DROP INDEX IX_{0}_UpdateDate ON audit.{0}
END

IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_UserName'
)
BEGIN
    DROP INDEX IX_{0}_UserName ON audit.{0}
END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetRichTextText]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		CEBOS, Inc.
-- Create date: 10/10/2012
-- Description:	Extract body from html for the radRichText
-- =============================================
CREATE FUNCTION [dbo].[GetRichTextText] 
(
	@text NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @startIndex INT, @endIndex INT
	DECLARE @body NVARCHAR(MAX)
	
	SET @startIndex = CHARINDEX(''<body>'', @text)
	IF @startIndex = 0 RETURN @text

	SET @startIndex = @startIndex + 6

	SET @endIndex = CHARINDEX(''</body>'', @text, @startIndex)
	IF @endIndex = 0 RETURN @text

	RETURN SUBSTRING(@text, @startIndex, @endIndex - @startIndex)
END
' 
END

", table.Name);
        }
        /// <summary>
        /// Generates a script that returns the id mappings for the specified reference (cross-reference, reverse cross-reference or tree-view) field.
        /// </summary>
        /// <param name="table">
        /// The table that contains the reference field.
        /// </param>
        /// <param name="field">
        /// The reference field.
        /// </param>
        /// <returns>
        /// The script.
        /// </returns>
        private string GetReferenceFieldIdMappingsSelect(ITableDefinition table, TableFieldDefinition field)
        {
            if (field.IsRef)
            {
                return GetCrossReferenceIdMappingsSelect(table, field);
            }

            if (field.IsReverseRef)
            {
                return GetReverseCrossReferenceIdMappingsSelect(table, field);
            }

            throw new NotSupportedException("Invalid reference field.");
        }
        /// <summary>
        /// Adds keys to index of table.
        /// </summary>
        /// <param name="tableDescription">Table description.</param>
        /// <param name="tableDefinition">Table definition.</param>
        /// <param name="indexes">Database indexes.</param>
        private void _AddKeysToTableIndex(TableDescription tableDescription,
                                          ITableDefinition tableDefinition,
                                          ADOX.Indexes indexes)
        {
            ICollection<TableInfo> patternTables = _structureKeeper.GetPattern(ExportType.Access);
            foreach (TableInfo tableInfo in patternTables)
            {
                if (tableInfo.Type != tableDefinition.Type)
                    continue; // skip

                foreach(TableIndex indexDefinition in tableInfo.Indexes)
                {
                    if (_IsIndexFieldSelected(indexDefinition.FieldNames, tableDefinition.Fields))
                        _AddKeyToTableIndex(tableDescription, indexDefinition, indexes);
                }

                break; // process done
            }
        }
        /// <summary>
        /// Generates a script that returns the id mappings for the specified cross-reference field.
        /// </summary>
        /// <param name="table">
        /// The table that contains the cross-reference field.
        /// </param>
        /// <param name="referenceField">
        /// The cross-reference field.
        /// </param>
        /// <returns>
        /// The script.
        /// </returns>
        private string GetCrossReferenceIdMappingsSelect(ITableDefinition table, TableFieldDefinition referenceField)
        {
            if (referenceField.ColumnType == ColumnTypes.Reference)
            {
                return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.ReferenceProcess.SystemName, referenceField.SystemName);
            }

            if (referenceField.ColumnType == ColumnTypes.MultiReference)
            {
                if (referenceField.ReferenceField != null)
                {
                    // MCR with link field.
                    return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
", table.Name, referenceField.ReferenceProcess.SystemName, referenceField.ReferenceField.SystemName);
                }
                else
                {
                    // MCR with junction table.
                    var junctionTableName = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableName(
                        table.Name,
                        referenceField.SystemName,
                        referenceField.ReferenceProcess.SystemName);
                    var masterKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableMasterKey(table.Name, referenceField.ReferenceProcess.SystemName);
                    var childKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableChildKey(referenceField.ReferenceProcess.SystemName);

                    return string.Format(CultureInfo.InvariantCulture, @"
    SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId]
    FROM [dbo].[{0}] t1
        INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id]
        INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0
    WHERE t1.[IsRemoved] = 0
", table.Name, junctionTableName, masterKey, referenceField.ReferenceProcess.SystemName, childKey);
                }
            }

            throw new NotSupportedException("Invalid reference field.");
        }