public static void DeleteDirectoryFilesFromDb(SqlDatabaseConnection conn, string tableName, string schemaName,
IImportExportFactory factory, RootPathObject rootPath, List <string> paths)
{
try
{
var tableNames = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName)
.GetAllTableNames();
foreach (var table in tableNames)
{
var tableNameId = $"[{table.SchemaName}].[{table.TableName}]";
Logger.Info($"Purging table: {tableNameId}");
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText =
$@"DROP TABLE IF EXISTS {tableNameId}"
};
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Logger.Error(e, e.Message);
Logger.Error(e, "Skipping delete");
}
}
public static IEnumerable <Schema> GetSchemasForDirectory(ServerCallContext context,
IImportExportFactory factory, RootPathObject rootPath, List <string> paths,
int sampleSize = 5)
{
if (paths.Count == 0)
{
return(new List <Schema>());
}
if (sampleSize == 0)
{
sampleSize = 5;
}
if (factory.CustomDiscover)
{
return(factory.MakeDiscoverer().DiscoverSchemas(context, factory, rootPath, paths, sampleSize));
}
var schemaName = Constants.SchemaName;
var tableName = string.IsNullOrWhiteSpace(rootPath.Name)
? new DirectoryInfo(rootPath.RootPath).Name
: rootPath.Name;
var conn = Utility.Utility.GetSqlConnection(Constants.DiscoverDbPrefix);
Utility.Utility.LoadDirectoryFilesIntoDb(factory, conn, rootPath, tableName, schemaName, paths, false, sampleSize,
1);
var tableNames = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName)
.GetAllTableNames();
var schemas = new List <Schema>();
foreach (var table in tableNames)
{
var tableNameId = $"[{table.SchemaName}].[{table.TableName}]";
var schema = new Schema
{
Id = tableNameId,
Name = table.TableName,
DataFlowDirection = Schema.Types.DataFlowDirection.Read,
Properties = { },
};
schema = GetSchemaForQuery(context, schema, sampleSize,
rootPath?.ModeSettings?.FixedWidthSettings?.Columns);
schemas.Add(schema);
}
return(schemas);
}
private static long ImportRecordsForPath(IImportExportFactory factory, SqlDatabaseConnection conn,
RootPathObject rootPath,
string tableName, string schemaName, string path, bool downloadToLocal = false, long limit = long.MaxValue)
{
Logger.Info($"Preparing to load file: {path} from {rootPath.FileReadMode}");
var importExportFile = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName);
Logger.Info($"Begin loading file: {path} from {rootPath.FileReadMode}");
var rowsWritten = importExportFile.ImportTable(path, rootPath, downloadToLocal, limit);
Logger.Info($"Loaded file {path} with {rowsWritten} rows from {rootPath.FileReadMode}");
return(rowsWritten);
}
public IEnumerable <Schema> DiscoverSchemas(ServerCallContext context, IImportExportFactory factory, RootPathObject rootPath,
List <string> paths, int sampleSize = 5)
{
var schemaName = Constants.SchemaName;
var tableName = string.IsNullOrWhiteSpace(rootPath.Name)
? new DirectoryInfo(rootPath.RootPath).Name
: rootPath.Name;
var conn = Utility.Utility.GetSqlConnection(Constants.DiscoverDbPrefix);
Utility.Utility.LoadDirectoryFilesIntoDb(factory, conn, rootPath, tableName, schemaName,
paths, false, sampleSize, 1);
var schemas = new List <Schema>();
// foreach (var format in AS400.Format25) // POC
foreach (var format in rootPath.ModeSettings.AS400Settings.Formats)
{
if (format.IsGlobalHeader)
{
continue;
}
tableName = $"{tableName}_{format.KeyValue.Name}";
var schemaId = $"[{schemaName}].[{tableName}]";
var publisherMetaJson = new SchemaPublisherMetaJson
{
RootPath = rootPath
};
var schema = new Schema
{
Id = schemaId,
Name = tableName,
DataFlowDirection = Schema.Types.DataFlowDirection.ReadWrite,
Query = $"SELECT * FROM {schemaId}",
Properties = { },
};
schema = Discover.Discover.GetSchemaForQuery(context, schema, sampleSize, rootPath.Columns);
schema.PublisherMetaJson = JsonConvert.SerializeObject(publisherMetaJson);
schemas.Add(schema);
}
return(schemas);
}
public static void LoadDirectoryFilesIntoDb(
IImportExportFactory factory, SqlDatabaseConnection conn, RootPathObject rootPath,
string tableName, string schemaName, List <string> paths, bool downloadToLocal, long recordLimit = long.MaxValue, int fileLimit = int.MaxValue
)
{
Logger.Info($"Loading files:\n {JsonConvert.SerializeObject(paths, Formatting.Indented)}");
DeleteDirectoryFilesFromDb(conn, tableName, schemaName, factory, rootPath, paths);
foreach (var path in paths.Take(fileLimit))
{
ImportRecordsForPath(factory, conn, rootPath, tableName, schemaName, path, downloadToLocal,
recordLimit);
}
if (paths.Count > 0)
{
Logger.Info($"Adding indexes for {JsonConvert.SerializeObject(paths, Formatting.Indented)}");
var importExportFile = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName);
AddIndexesToTables(importExportFile, conn);
Logger.Info($"Added indexes for {JsonConvert.SerializeObject(paths, Formatting.Indented)}");
}
}
