public DataTable QueryView(string viewName, string id)
{
DatabaseCommandResult databaseCommandResult =
_database.BuildCommand()
.AddInputParameter("@id", id)
.ForSQLQuery($"SELECT * FROM sale.{viewName} WHERE id = @id")
.Execute();
return(databaseCommandResult.DataTable);
}
private DatabaseCommandResult DeployFeaturesFromExtensions(IOptions options, UpgradeEngineConfig config)
{
var features = options.Features.ToList();
var filePaths = options.FilePaths.ToList();
// if no features or extensions then we just exit with success
if (!features.Any() || !filePaths.Any())
{
_logger.Debug("No features or file paths are found.");
return(new DatabaseCommandResult {
IsSuccessful = true
});
}
string engineTypeDirectory = options.Engine.Directory();
var commandResults = new List <DatabaseCommandResult>();
// deploy structure scripts
// NewVersion: /Artifacts/[EngineTypeFolder]/Structure/[DatabaseTypeFolder]/[Feature]/{*.sql}
// LegacyVersion: /Database/Structure/[DatabaseTypeFolder]/[Feature]/{*.sql}
foreach (string path in filePaths)
{
foreach (string feature in features)
{
commandResults.Add(RunScripts(path, feature, true));
}
}
// deploy data scripts
// NewVersion: /Artifacts/[EngineTypeFolder]/Data/[DatabaseTypeFolder]/[Feature]/{*.sql}
// LegacyVersion: /Database/Data/[DatabaseTypeFolder]/[Feature]/{*.sql}
foreach (string path in filePaths)
{
foreach (string feature in features)
{
commandResults.Add(RunScripts(path, feature, false));
}
}
return(DatabaseCommandResult.Create(commandResults));
DatabaseCommandResult RunScripts(string path, string feature, bool isStructureScripts)
{
// note we can only run one path at a time, so we must recreate the db up instance.
config.ParentPath = path;
config.ScriptPath = ScriptsPath(new ScriptPathResolver(path, options.DatabaseType, options.Engine, feature));
if (!Directory.Exists(config.ScriptPath))
{
_logger.Debug($"Feature {feature} is not found in path {config.ScriptPath}");
config.ScriptPath = ScriptsPath(new LegacyScriptPathResolver(path, options.DatabaseType, feature));
if (!Directory.Exists(config.ScriptPath))
{
_logger.Debug($"Feature {feature} is not found in path {config.ScriptPath}");
return(new DatabaseCommandResult {
IsSuccessful = true
});
}
// If config.Path test fails for NewVersion, engine should not be PostgreSql
// only if folder exists
if (!options.AreFeaturesValidForLegacyDatabaseDirectoryStructure())
{
_logger.Warn("Currently only SqlServer is supported to deploy features for Legacy versions of Ed-Fi ODS Model");
return(new DatabaseCommandResult {
IsSuccessful = true
});
}
}
_logger.Info($"Deploying feature {feature} from path {config.ScriptPath}");
var upgradeEngine = _upgradeEngineFactory.Create(config);
if (config.PerformWhatIf)
{
return(new DatabaseCommandResult
{
RequiresUpgrade = upgradeEngine.IsUpgradeRequired(),
IsSuccessful = true
});
}
var result = upgradeEngine.PerformUpgrade();
return(new DatabaseCommandResult
{
IsSuccessful = result.Successful,
Exception = result.Error
});
string ScriptsPath(IScriptPathResolver fileInfoProvider)
=> isStructureScripts
? fileInfoProvider.StructureScriptPath()
: fileInfoProvider.DataScriptPath();
}
}
public DatabaseCommandResult Execute(IOptions options)
{
Preconditions.ThrowIfNull(options, nameof(options));
var filePaths = options.FilePaths.ToList();
if (!filePaths.Any())
{
_logger.Debug("No file paths are found.");
return(new DatabaseCommandResult {
IsSuccessful = false
});
}
var commandResults = new List <DatabaseCommandResult>();
var config = UpgradeEngineConfig.Create(options);
// deploy structure scripts
// NewVersion: /Artifacts/[EngineTypeFolder]/Structure/[DatabaseTypeFolder]/{*.sql}
// LegacyVersion: /Database/Structure/[DatabaseTypeFolder]/{*.sql}
foreach (string path in filePaths)
{
var results = RunScripts(path, true);
commandResults.Add(results);
// If any filePath fails to upgrade, abort immediately to avoid further changing the database
if (!results.IsSuccessful)
{
DatabaseCommandResult.Create(commandResults);
}
}
// deploy data scripts
// NewVersion: /Artifacts/[EngineTypeFolder]/Data/[DatabaseTypeFolder]/{*.sql}
// LegacyVersion: /Database/Data/[DatabaseTypeFolder]/{*.sql}
foreach (string path in filePaths)
{
var results = RunScripts(path, false);
commandResults.Add(results);
// If any filePath fails to upgrade, abort immediately to avoid further changing the database
if (!results.IsSuccessful)
{
DatabaseCommandResult.Create(commandResults);
}
}
return(DatabaseCommandResult.Create(commandResults));
DatabaseCommandResult RunScripts(string path, bool isStructureScripts)
{
config.ParentPath = path;
config.ScriptPath = ScriptsPath(new ScriptPathResolver(path, options.DatabaseType, options.Engine));
if (!Directory.Exists(config.ScriptPath))
{
_logger.Debug($"No database scripts found in {config.ScriptPath}");
config.ScriptPath = ScriptsPath(new LegacyScriptPathResolver(path, options.DatabaseType));
if (!Directory.Exists(config.ScriptPath))
{
_logger.Debug($"No database scripts found in {config.ScriptPath}");
return(new DatabaseCommandResult {
IsSuccessful = true
});
}
}
_logger.Info($"Deploying database from path {config.ScriptPath}");
var upgradeEngine = _upgradeEngineFactory.Create(config);
if (config.PerformWhatIf)
{
return(new DatabaseCommandResult
{
IsSuccessful = true,
RequiresUpgrade = upgradeEngine.IsUpgradeRequired()
});
}
// Explicitly block database structure upgrades for the Ed-Fi Standard project on ODS if an upgrade is required and scripts have previously been executed for this path
if (isStructureScripts && config.DatabaseType == DatabaseType.ODS && IsEdFiStandardScriptsPath(path) &&
upgradeEngine.IsUpgradeRequired() && upgradeEngine.GetExecutedScripts().Any())
{
return(new DatabaseCommandResult
{
IsSuccessful = false,
RequiresUpgrade = true,
Exception = new Exception(
$"Upgrades are not supported at this time for database type {DatabaseType.ODS} using the {EdFiStandardIdentifier} data model. This tool only supports feature scripts for this type. Please use other tooling such as Migration Utility to upgrade this database.")
});
}
var result = upgradeEngine.PerformUpgrade();
return(new DatabaseCommandResult
{
IsSuccessful = result.Successful,
Exception = result.Error
});
string ScriptsPath(IScriptPathResolver fileInfoProvider)
=> isStructureScripts
? fileInfoProvider.StructureScriptPath()
: fileInfoProvider.DataScriptPath();
bool IsEdFiStandardScriptsPath(string scriptsPath) => scriptsPath.Contains(EdFiStandardIdentifier);
}
}
public DatabaseCommandResult Execute(IOptions options)
{
Preconditions.ThrowIfNull(options, nameof(options));
var filePaths = options.FilePaths.ToList();
if (!filePaths.Any())
{
_logger.Debug("No file paths are found.");
return(new DatabaseCommandResult {
IsSuccessful = false
});
}
var commandResults = new List <DatabaseCommandResult>();
var config = UpgradeEngineConfig.Create(options);
// deploy legacy extensions structure scripts
// /SupportingArtifacts/Database/Structure/[DatabaseTypeFolder]/{*.sql}
foreach (string path in filePaths)
{
commandResults.Add(RunScripts(path, true));
}
// deploy legacy extensions data scripts
// /SupportingArtifacts/Database/Data/[DatabaseTypeFolder]/{*.sql}
foreach (string path in filePaths)
{
commandResults.Add(RunScripts(path, true));
}
return(DatabaseCommandResult.Create(commandResults));
DatabaseCommandResult RunScripts(string path, bool isStructureScripts)
{
// note we can only run one path at a time, so we must recreate the db up instance.
config.ParentPath = path;
config.ScriptPath = ScriptsPath(new LegacyExtensionsScriptPathResolver(path, config.DatabaseType));
if (!Directory.Exists(config.ScriptPath))
{
_logger.Debug($"No legacy extension scripts found in {config.ScriptPath}");
return(new DatabaseCommandResult {
IsSuccessful = true
});
}
_logger.Info($"Deploying legacy extension from path {config.ScriptPath}");
var upgradeEngine = _upgradeEngineFactory.Create(config);
if (config.PerformWhatIf)
{
return(new DatabaseCommandResult
{
IsSuccessful = true,
RequiresUpgrade = upgradeEngine.IsUpgradeRequired()
});
}
var result = upgradeEngine.PerformUpgrade();
return(new DatabaseCommandResult
{
IsSuccessful = result.Successful,
Exception = result.Error
});
string ScriptsPath(IScriptPathResolver fileInfoProvider)
=> isStructureScripts
? fileInfoProvider.StructureScriptPath()
: fileInfoProvider.DataScriptPath();
}
}
protected override void Act()
{
Result = System.Execute(Options);
}
