/// <summary> /// Read a five column name map file, which is typically created by sqlserver2pgsql.pl /// It is a tab-delimited file with five columns: /// SourceTable SourceName Schema NewTable NewName /// </summary> /// <param name="mapFile">Tab-delimited text file to read</param> /// <param name="defaultSchema">Default schema name</param> /// <param name="warnDuplicateTargetColumnNames">If true, warn the user at the console if multiple columns in a table have the same target column name</param> /// <param name="tableNameMap"> /// Dictionary where keys are the original (source) table names /// and values are WordReplacer classes that track the new table names and new column names in PostgreSQL /// </param> /// <param name="columnNameMap"> /// Dictionary where keys are new table names /// and values are a Dictionary of mappings of original column names to new column names in PostgreSQL; /// names should not have double quotes around them /// </param> /// <returns></returns> public bool LoadSqlServerToPgSqlColumnMapFile( FileSystemInfo mapFile, string defaultSchema, bool warnDuplicateTargetColumnNames, out Dictionary <string, WordReplacer> tableNameMap, out Dictionary <string, Dictionary <string, WordReplacer> > columnNameMap) { var linesRead = 0; tableNameMap = new Dictionary <string, WordReplacer>(StringComparer.OrdinalIgnoreCase); columnNameMap = new Dictionary <string, Dictionary <string, WordReplacer> >(StringComparer.OrdinalIgnoreCase); try { using (var reader = new StreamReader(new FileStream(mapFile.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))) { while (!reader.EndOfStream) { var dataLine = reader.ReadLine(); linesRead++; if (string.IsNullOrWhiteSpace(dataLine)) { continue; } var lineParts = dataLine.Split('\t'); if (lineParts.Length < 5) { continue; } if (linesRead == 1 && lineParts[0].Equals("SourceTable", StringComparison.OrdinalIgnoreCase) && lineParts[1].Equals("SourceName", StringComparison.OrdinalIgnoreCase)) { // Header line; skip it continue; } var sourceTableName = lineParts[0]; var sourceColumnName = lineParts[1]; var newSchema = lineParts[2]; var newTableName = PossiblyUnquote(lineParts[3]); var newColumnName = PossiblyUnquote(lineParts[4]); if (!tableNameMap.ContainsKey(sourceTableName)) { string newSchemaToUse; if (string.IsNullOrEmpty(newSchema)) { newSchemaToUse = defaultSchema; } else { newSchemaToUse = newSchema; } var replacer = new WordReplacer(sourceTableName, newTableName, newSchemaToUse); tableNameMap.Add(sourceTableName, replacer); } if (!columnNameMap.TryGetValue(newTableName, out var targetTableColumnMap)) { targetTableColumnMap = new Dictionary <string, WordReplacer>(StringComparer.OrdinalIgnoreCase); columnNameMap.Add(newTableName, targetTableColumnMap); } if (targetTableColumnMap.ContainsKey(sourceColumnName)) { OnWarningEvent(string.Format( "In file {0}, table {1} has multiple columns with the same source name, {2}", mapFile.Name, newTableName, sourceColumnName)); continue; } if (targetTableColumnMap.Values.Any(item => item.ReplacementText.Equals(newColumnName))) { if (warnDuplicateTargetColumnNames) { OnWarningEvent(string.Format( "In file {0}, table {1} has multiple columns with the same new name, {2}", mapFile.Name, newTableName, newColumnName)); } } var columnNameReplacer = new WordReplacer(sourceColumnName, newColumnName); targetTableColumnMap.Add(sourceColumnName, columnNameReplacer); } } return(true); } catch (Exception ex) { OnErrorEvent(string.Format("Error in LoadMapFile, reading line {0}", linesRead), ex); return(false); } }
/// <summary> /// Read a three column name map file, which is typically sent to DB_Schema_Export_Tool.exe via the ColumnMap parameter when using the ExistingDDL option /// It is a tab-delimited file with three columns: /// SourceTableName SourceColumnName TargetColumnName /// </summary> /// <param name="mapFile">Tab-delimited text file to read</param> /// <param name="tableNameMap"> /// Dictionary where keys are the original (source) table names /// and values are WordReplacer classes that track the new table names and new column names in PostgreSQL /// </param> /// <param name="columnNameMap"> /// Dictionary where keys are new table names /// and values are a Dictionary of mappings of original column names to new column names in PostgreSQL; /// names should not have double quotes around them /// </param> /// <returns></returns> public bool LoadTableColumnMapFile( FileSystemInfo mapFile, IReadOnlyDictionary <string, WordReplacer> tableNameMap, IDictionary <string, Dictionary <string, WordReplacer> > columnNameMap ) { var linesRead = 0; var missingTablesWarned = new SortedSet <string>(StringComparer.OrdinalIgnoreCase); try { using (var reader = new StreamReader(new FileStream(mapFile.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))) { while (!reader.EndOfStream) { var dataLine = reader.ReadLine(); linesRead++; if (string.IsNullOrWhiteSpace(dataLine)) { continue; } var lineParts = dataLine.Split('\t'); if (lineParts.Length < 3) { continue; } if (linesRead == 1 && lineParts[0].Equals("SourceTableName", StringComparison.OrdinalIgnoreCase) && lineParts[1].Equals("SourceColumnName", StringComparison.OrdinalIgnoreCase)) { // Header line; skip it continue; } var sourceTableName = lineParts[0]; var sourceColumnName = lineParts[1]; var newColumnName = PossiblyUnquote(lineParts[2]); // Look for the table in tableNameMap if (!tableNameMap.TryGetValue(sourceTableName, out var replacer)) { if (missingTablesWarned.Contains(sourceTableName)) { continue; } OnWarningEvent(string.Format( "Table {0} not found in tableNameMap; ignoring column map info for column {1}", sourceTableName, sourceColumnName)); missingTablesWarned.Add(sourceTableName); continue; } var newTableName = PossiblyUnquote(replacer.ReplacementText); if (!columnNameMap.TryGetValue(newTableName, out var targetTableColumnMap)) { targetTableColumnMap = new Dictionary <string, WordReplacer>(StringComparer.OrdinalIgnoreCase); columnNameMap.Add(newTableName, targetTableColumnMap); } if (targetTableColumnMap.ContainsKey(sourceColumnName)) { // The column rename map has already been defined; this is OK OnDebugEvent(string.Format("Column mapping already defined for {0} in table {1}", sourceColumnName, sourceTableName)); continue; } var columnNameReplacer = new WordReplacer(sourceColumnName, newColumnName); targetTableColumnMap.Add(sourceColumnName, columnNameReplacer); } } return(true); } catch (Exception ex) { OnErrorEvent(string.Format("Error in LoadSecondaryMapFile, reading line {0}", linesRead), ex); return(false); } }