private void AdjustImporter()
{
var cataRepo = Activator.RepositoryLocator.CatalogueRepository;
try
{
DiscoveredTable tbl = serverDatabaseTableSelector1.GetDiscoveredTable();
if (tbl == null)
{
btnImport.Enabled = false;
return;
}
//if it isn't a table valued function
if (tbl is DiscoveredTableValuedFunction)
{
Importer = new TableValuedFunctionImporter(cataRepo, (DiscoveredTableValuedFunction)tbl, (DataAccessContext)ddContext.SelectedValue);
}
else
{
Importer = new TableInfoImporter(cataRepo, tbl, (DataAccessContext)ddContext.SelectedValue);
}
btnImport.Enabled = true;
}
catch (Exception exception)
{
ExceptionViewer.Show(exception);
}
}
public void Create(DiscoveredDatabase databaseICanCreateRandomTablesIn, ICatalogueRepository catalogueRepository)
{
CreateFunctionSQL = @"
if exists (select 1 from sys.objects where name = 'MyAwesomeFunction')
drop function MyAwesomeFunction
GO
CREATE FUNCTION MyAwesomeFunction
(
-- Add the parameters for the function here
@startNumber int ,
@stopNumber int,
@name varchar(50)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
Number int,
Name varchar(50)
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @i int;
set @i = @startNumber
while(@i < @stopNumber)
begin
INSERT INTO @ReturnTable(Name,Number) VALUES (@name,@i);
set @i = @i + 1;
end
RETURN
END
";
using (var con = databaseICanCreateRandomTablesIn.Server.GetConnection())
{
con.Open();
UsefulStuff.ExecuteBatchNonQuery(CreateFunctionSQL, con);
}
var tbl = databaseICanCreateRandomTablesIn.ExpectTableValuedFunction("MyAwesomeFunction");
TableValuedFunctionImporter importer = new TableValuedFunctionImporter(catalogueRepository, tbl);
importer.DoImport(out TableInfoCreated, out ColumnInfosCreated);
importer.ParametersCreated[0].Value = "5";
importer.ParametersCreated[0].SaveToDatabase();
importer.ParametersCreated[1].Value = "10";
importer.ParametersCreated[1].SaveToDatabase();
importer.ParametersCreated[2].Value = "'fish'";
importer.ParametersCreated[2].SaveToDatabase();
ForwardEngineerCatalogue forwardEngineerCatalogue = new ForwardEngineerCatalogue(TableInfoCreated, ColumnInfosCreated, true);
forwardEngineerCatalogue.ExecuteForwardEngineering(out Cata, out CataItems, out ExtractionInformations);
}
private void CreateTvfCatalogue(string cohortDatabaseName)
{
var svr = _database.Server;
using (var con = svr.GetConnection())
{
con.Open();
//create the newID view
svr.GetCommand("create view getNewID as select newid() as new_id", con).ExecuteNonQuery();
var sql = string.Format(
@"create function GetTopXRandom (@numberOfRecords int)
RETURNS @retTable TABLE
(
chi varchar(10),
definitionID int
)
AS
BEGIN
while(@numberOfRecords >0)
begin
insert into @retTable select top 1 chi,cohortDefinition_id from {0}..Cohort order by (select new_id from getNewID)
set @numberOfRecords = @numberOfRecords - 1
end
return
end
", cohortDatabaseName);
svr.GetCommand(sql, con).ExecuteNonQuery();
}
var tblvf = _database.ExpectTableValuedFunction("GetTopXRandom");
var importer = new TableValuedFunctionImporter(CatalogueRepository, tblvf);
TableInfo tbl;
ColumnInfo[] cols;
importer.DoImport(out tbl, out cols);
var engineer = new ForwardEngineerCatalogue(tbl, cols, true);
Catalogue cata;
CatalogueItem[] cis;
ExtractionInformation[] eis;
engineer.ExecuteForwardEngineering(out cata, out cis, out eis);
Assert.AreEqual("chi", eis[0].GetRuntimeName());
eis[0].IsExtractionIdentifier = true;
eis[0].SaveToDatabase();
_tvfCatalogue = cata;
_tvfTableInfo = tbl;
}
public override void Execute()
{
base.Execute();
ICatalogue c = null;
ITableInfoImporter importer;
DiscoveredTable t;
t = _table ?? SelectTable(false, "Select table to import");
if (t == null)
{
return;
}
//if it isn't a table valued function
if (t is DiscoveredTableValuedFunction)
{
importer = new TableValuedFunctionImporter(BasicActivator.RepositoryLocator.CatalogueRepository, (DiscoveredTableValuedFunction)t);
}
else
{
importer = new TableInfoImporter(BasicActivator.RepositoryLocator.CatalogueRepository, t);
}
importer.DoImport(out var ti, out ColumnInfo[] cis);
BasicActivator.Show($"Successfully imported new TableInfo { ti.Name} with ID {ti.ID}");
if (_createCatalogue)
{
var forwardEngineer = new ForwardEngineerCatalogue(ti, cis, true);
forwardEngineer.ExecuteForwardEngineering(out c, out _, out _);
BasicActivator.Show($"Successfully imported new Catalogue { c.Name} with ID {c.ID}");
}
Publish((IMapsDirectlyToDatabaseTable)c ?? ti);
}
public void TestCreateTableInSchemaAndImportAsTableInfo()
{
var db = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
using (var con = db.Server.GetConnection())
{
con.Open();
db.Server.GetCommand("CREATE SCHEMA Omg", con).ExecuteNonQuery();
var tbl = db.CreateTable("Fish", new [] { new DatabaseColumnRequest("MyCol", "int")
{
IsPrimaryKey = true
} }, schema: "Omg");
Assert.AreEqual("Fish", tbl.GetRuntimeName());
Assert.AreEqual("Omg", tbl.Schema);
Assert.IsTrue(tbl.GetFullyQualifiedName().EndsWith("[Omg].[Fish]"));
Assert.IsTrue(tbl.Exists());
TableInfo ti;
ColumnInfo[] cols;
Import(tbl, out ti, out cols);
Assert.AreEqual("Omg", ti.Schema);
var tbl2 = ti.Discover(DataAccessContext.InternalDataProcessing);
Assert.AreEqual("Omg", tbl2.Schema);
Assert.IsTrue(tbl2.Exists());
Assert.IsTrue(ti.Name.EndsWith("[Omg].[Fish]"));
Assert.IsTrue(ti.GetFullyQualifiedName().EndsWith("[Omg].[Fish]"));
var c = cols.Single();
Assert.AreEqual("MyCol", c.GetRuntimeName());
StringAssert.Contains("[Omg].[Fish]", c.GetFullyQualifiedName());
//should be primary key
Assert.IsTrue(c.IsPrimaryKey);
var triggerFactory = new TriggerImplementerFactory(DatabaseType.MicrosoftSQLServer);
var impl = triggerFactory.Create(tbl);
Assert.AreEqual(TriggerStatus.Missing, impl.GetTriggerStatus());
impl.CreateTrigger(new ThrowImmediatelyCheckNotifier());
Assert.AreEqual(TriggerStatus.Enabled, impl.GetTriggerStatus());
Assert.IsTrue(impl.CheckUpdateTriggerIsEnabledAndHasExpectedBody());
//should be synced
var sync = new TableInfoSynchronizer(ti);
sync.Synchronize(new AcceptAllCheckNotifier());
//Test importing the _Legacy table valued function that should be created in the Omg schema and test synching that too.
var tvf = ti.Discover(DataAccessContext.InternalDataProcessing).Database.ExpectTableValuedFunction("Fish_Legacy", "Omg");
Assert.IsTrue(tvf.Exists());
var importerTvf = new TableValuedFunctionImporter(CatalogueRepository, tvf);
TableInfo tvfTi;
ColumnInfo[] tvfCols;
importerTvf.DoImport(out tvfTi, out tvfCols);
Assert.AreEqual("Omg", tvfTi.Schema);
var syncTvf = new TableInfoSynchronizer(tvfTi);
syncTvf.Synchronize(new ThrowImmediatelyCheckNotifier());
StringAssert.EndsWith("[Omg].Fish_Legacy(@index) AS Fish_Legacy", tvfTi.Name);
}
}
