public void CreateTable_GuessSettings_ExplicitDateTimeFormat(DatabaseType dbType, bool useCustomDate)
{
//Values like 013020 would normally be treated as string data (due to leading zero) but maybe the user wants it to be a date?
var db = GetTestDatabase(dbType);
DataTable dt = new DataTable();
dt.Columns.Add("DateCol");
dt.Rows.Add("013020");
var args = new CreateTableArgs(db, "Hb", null, dt, false);
Assert.AreEqual(args.GuessSettings.ExplicitDateFormats, GuessSettingsFactory.Defaults.ExplicitDateFormats, "Default should match the static default");
Assert.IsFalse(args.GuessSettings == GuessSettingsFactory.Defaults, "Args should not be the same instance! otherwise we would unintentionally edit the defaults!");
//change the args settings to treat this date format
args.GuessSettings.ExplicitDateFormats = useCustomDate ? new string[] { "MMddyy" } :null;
var tbl = db.CreateTable(args);
var col = tbl.DiscoverColumn("DateCol");
Assert.AreEqual(useCustomDate ? typeof(DateTime): typeof(string), col.DataType.GetCSharpDataType());
var dtDown = tbl.GetDataTable();
Assert.AreEqual(useCustomDate? new DateTime(2020, 01, 30): (object)"013020", dtDown.Rows[0][0]);
}
private void CopySettings(Guesser guesser, CreateTableArgs args)
{
//cannot change the instance so have to copy across the values. If this gets new properties that's a problem
//See tests GuessSettings_CopyProperties
guesser.Settings.CharCanBeBoolean = args.GuessSettings.CharCanBeBoolean;
guesser.Settings.ExplicitDateFormats = args.GuessSettings.ExplicitDateFormats;
}
public void DoesNotThrowIfTableAlreadyExists()
{
// arrange
var args = new CreateTableArgs <Book>(book => book.Name, book => book.PublishYear);
Context.CreateTableIfNotExists(args);
// act
Context.CreateTableIfNotExists(args);
}
public void CreatesTableWithHashAndRangeKeys()
{
// arrange
var args = new CreateTableArgs<Book>(book => book.Name, book => book.PublishYear);
// act
Context.CreateTableIfNotExists(args);
var tableData = DynamoDbClient.DescribeTable(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
}
public async Task CreatesTableWithHashAndRangeKeys()
{
// arrange
var args = new CreateTableArgs <Book>(book => book.Name, book => book.PublishYear);
// act
await Context.CreateTableIfNotExistsAsync(args);
var tableData = await DynamoDbClient.DescribeTableAsync(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
}
public void CreatesTableWithHashKey()
{
// arrange
var args = new CreateTableArgs <Book>(book => book.Name);
// act
Context.CreateTableIfNotExists(args);
var tableData = DynamoDbClient.DescribeTable(new DescribeTableRequest {
TableName = BooksTableName
});
// assert
Assert.IsNotNull(tableData, "Table was not created");
}
public void CreatesTableWithLocalSecondaryIndexes()
{
// arrange
var args = new CreateTableArgs<Book>(book => book.Name, book => book.PublishYear, book => book.NumPages, book => book.PopularityRating);
// act
Context.CreateTableIfNotExists(args);
var tableData = DynamoDbClient.DescribeTable(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
var secondaryIndexes = tableData.Table.LocalSecondaryIndexes;
Assert.AreEqual(2, secondaryIndexes.Count, "Expected 2 local secondary indexes to be created");
Assert.IsTrue(secondaryIndexes.Any(description => description.IndexName.Contains("NumPages")));
Assert.IsTrue(secondaryIndexes.Any(description => description.IndexName.Contains("PopularityRating")));
}
public void CreatesTableWithGlobalSecondaryIndexes()
{
// arrange
var args = new CreateTableArgs <Book>
(
book => book.Name,
book => book.PublishYear,
localSecondaryIndexFieldExps: null,
globalSecondaryIndexFieldExps: new GlobalSecondaryIndexDefinitions <Book>
{
book => new GlobalSecondaryIndexDefinition
{
HashKeyField = book.PublishYear
},
book => new GlobalSecondaryIndexDefinition
{
HashKeyField = book.Author,
RangeKeyField = book.NumPages
}
}
);
// act
Context.CreateTableIfNotExists(args);
var tableData = DynamoDbClient.DescribeTable(new DescribeTableRequest {
TableName = BooksTableName
});
// assert
Assert.IsNotNull(tableData, "Table was not created");
var secondaryIndexes = tableData.Table.GlobalSecondaryIndexes;
Assert.AreEqual(2, secondaryIndexes.Count, "Expected 2 global secondary indexes to be created");
Assert.IsTrue(secondaryIndexes.Any(description => description.IndexName.Contains("PublishYear")));
Assert.IsTrue
(
secondaryIndexes.Any
(
description =>
description.IndexName.Contains("Author")
&&
description.IndexName.Contains("NumPages")
)
);
}
/// <summary>
/// Creates a table in the database big enough to store the supplied DataTable with appropriate types.
/// </summary>
/// <param name="typeDictionary">The computers used to determine column types</param>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <param name="explicitColumnDefinitions"></param>
/// <param name="createEmpty"></param>
/// <param name="adjuster"></param>
/// <returns></returns>
public DiscoveredTable CreateTable(out Dictionary <string, Guesser> typeDictionary, string tableName, DataTable dt, DatabaseColumnRequest[] explicitColumnDefinitions = null, bool createEmpty = false, IDatabaseColumnRequestAdjuster adjuster = null)
{
var args = new CreateTableArgs(this, tableName, null, dt, createEmpty)
{
Adjuster = adjuster,
ExplicitColumnDefinitions = explicitColumnDefinitions
};
var table = Helper.CreateTable(args);
if (!args.TableCreated)
{
throw new Exception(FAnsiStrings.DiscoveredDatabase_CreateTableDidNotPopulateTableCreatedProperty);
}
typeDictionary = args.ColumnCreationLogic;
return(table);
}
public async Task CreatesTableWithLocalSecondaryIndexes()
{
// arrange
var args = new CreateTableArgs <Book>(book => book.Name, book => book.PublishYear, book => book.NumPages, book => book.PopularityRating);
// act
await Context.CreateTableIfNotExistsAsync(args);
var tableData = await DynamoDbClient.DescribeTableAsync(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
var secondaryIndexes = tableData.Table.LocalSecondaryIndexes;
Assert.AreEqual(2, secondaryIndexes.Count, "Expected 2 local secondary indexes to be created");
Assert.IsTrue(secondaryIndexes.Any(description => description.IndexName.Contains("NumPages")));
Assert.IsTrue(secondaryIndexes.Any(description => description.IndexName.Contains("PopularityRating")));
}
/// <summary>
/// Creates a table in the database big enough to store the supplied DataTable with appropriate types.
/// </summary>
/// <param name="typeDictionary">The computers used to determine column types</param>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <param name="explicitColumnDefinitions"></param>
/// <param name="createEmpty"></param>
/// <param name="adjuster"></param>
/// <returns></returns>
public DiscoveredTable CreateTable(out Dictionary <string, DataTypeComputer> typeDictionary, string tableName, DataTable dt, DatabaseColumnRequest[] explicitColumnDefinitions = null, bool createEmpty = false, IDatabaseColumnRequestAdjuster adjuster = null)
{
var args = new CreateTableArgs(this, tableName, null, dt, createEmpty)
{
Adjuster = adjuster,
ExplicitColumnDefinitions = explicitColumnDefinitions
};
var table = Helper.CreateTable(args);
if (!args.TableCreated)
{
throw new Exception("CreateTable completed but arguments were not Consumed");
}
typeDictionary = args.ColumnCreationLogic;
return(table);
}
public async Task CreatesTableWithConstantCapacity()
{
// arrange
const long readCapacity = 2;
const long writeCapacity = 1;
var args = new CreateTableArgs <Book>(readCapacity, writeCapacity, book => book.Name, book => book.PublishYear);
// act
await Context.CreateTableIfNotExistsAsync(args);
var tableData = await DynamoDbClient.DescribeTableAsync(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
var tableCapacity = tableData.Table.ProvisionedThroughput;
Assert.AreEqual(readCapacity, tableCapacity.ReadCapacityUnits);
Assert.AreEqual(writeCapacity, tableCapacity.WriteCapacityUnits);
}
public async Task DeletesExistingTable()
{
// arrange
var args = new CreateTableArgs <Book>(book => book.Name, book => book.PublishYear);
await Context.CreateTableIfNotExistsAsync(args);
// act
await Context.DeleteTableAsync <Book>();
try
{
await DynamoDbClient.DescribeTableAsync(new DescribeTableRequest { TableName = BooksTableName });
Assert.Fail();
}
catch (ResourceNotFoundException)
{
}
}
public async Task CreatesTableWithRuntimeVariableCapacity()
{
// arrange
Func <long> getReadCapacityFunc = () => 2;
Func <long> getWriteCapacityFunc = () => 1;
var args = new CreateTableArgs <Book>(getReadCapacityFunc(), getWriteCapacityFunc(), book => book.Name, book => book.PublishYear);
// act
await Context.CreateTableIfNotExistsAsync(args);
var tableData = await DynamoDbClient.DescribeTableAsync(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
var tableCapacity = tableData.Table.ProvisionedThroughput;
Assert.AreEqual(getReadCapacityFunc(), tableCapacity.ReadCapacityUnits);
Assert.AreEqual(getWriteCapacityFunc(), tableCapacity.WriteCapacityUnits);
}
public void CreatesTableWithGlobalSecondaryIndexUsingRuntimeVariableCapacity()
{
// arrange
Func <long> getReadCapacityFunc = () => 2;
Func <long> getWriteCapacityFunc = () => 1;
var args = new CreateTableArgs <Book>
(
book => book.Name,
book => book.PublishYear,
localSecondaryIndexFieldExps: null,
globalSecondaryIndexFieldExps: new GlobalSecondaryIndexDefinitions <Book>
{
book => new GlobalSecondaryIndexDefinition
{
HashKeyField = book.Author,
RangeKeyField = book.NumPages,
ReadCapacityUnits = getReadCapacityFunc(),
WriteCapacityUnits = getWriteCapacityFunc()
}
}
);
// act
Context.CreateTableIfNotExists(args);
var tableData = DynamoDbClient.DescribeTable(new DescribeTableRequest {
TableName = BooksTableName
});
// assert
Assert.IsNotNull(tableData, "Table was not created");
var secondaryIndexes = tableData.Table.GlobalSecondaryIndexes;
Assert.AreEqual(1, secondaryIndexes.Count, "Expected 1 global secondary index to be created");
var indexThroughput = secondaryIndexes[0].ProvisionedThroughput;
Assert.AreEqual(getReadCapacityFunc(), indexThroughput.ReadCapacityUnits);
Assert.AreEqual(getWriteCapacityFunc(), indexThroughput.WriteCapacityUnits);
}
public async Task CreatesTableWithGlobalSecondaryIndexUsingConstantCapacity()
{
// arrange
const long readCapacity = 2;
const long writeCapacity = 1;
var args = new CreateTableArgs <Book>
(
book => book.Name,
book => book.PublishYear,
localSecondaryIndexFieldExps: null,
globalSecondaryIndexFieldExps: new GlobalSecondaryIndexDefinitions <Book>
{
book => new GlobalSecondaryIndexDefinition
{
HashKeyField = book.Author,
RangeKeyField = book.NumPages,
ReadCapacityUnits = readCapacity,
WriteCapacityUnits = writeCapacity
}
}
);
// act
await Context.CreateTableIfNotExistsAsync(args);
var tableData = await DynamoDbClient.DescribeTableAsync(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
var secondaryIndexes = tableData.Table.GlobalSecondaryIndexes;
Assert.AreEqual(1, secondaryIndexes.Count, "Expected 1 global secondary index to be created");
var indexThroughput = secondaryIndexes[0].ProvisionedThroughput;
Assert.AreEqual(readCapacity, indexThroughput.ReadCapacityUnits);
Assert.AreEqual(writeCapacity, indexThroughput.WriteCapacityUnits);
}
public void CreateTable_GuessSettings_InArgs_TF(DatabaseType dbType, bool treatAsBoolean)
{
//T and F is normally True and False. If you want to keep it as a string set DoNotRetype
var db = GetTestDatabase(dbType);
DataTable dt = new DataTable();
dt.Columns.Add("Hb");
dt.Rows.Add("T");
dt.Rows.Add("F");
var args = new CreateTableArgs(db, "Hb", null, dt, false);
Assert.AreEqual(args.GuessSettings.CharCanBeBoolean, GuessSettingsFactory.Defaults.CharCanBeBoolean, "Default should match the static default");
Assert.IsFalse(args.GuessSettings == GuessSettingsFactory.Defaults, "Args should not be the same instance! otherwise we would unintentionally edit the defaults!");
//change the args settings
args.GuessSettings.CharCanBeBoolean = treatAsBoolean;
var tbl = db.CreateTable(args);
var col = tbl.DiscoverColumn("Hb");
Assert.AreEqual(treatAsBoolean ? typeof(bool): typeof(string), col.DataType.GetCSharpDataType());
Assert.AreEqual(treatAsBoolean ? -1: 1, col.DataType.GetLengthIfString(), "Expected string length to be 1 for 'T'");
}
public void CreatesTableWithConstantCapacity()
{
// arrange
const long readCapacity = 2;
const long writeCapacity = 1;
var args = new CreateTableArgs<Book>(readCapacity, writeCapacity, book => book.Name, book => book.PublishYear);
// act
Context.CreateTableIfNotExists(args);
var tableData = DynamoDbClient.DescribeTable(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
var tableCapacity = tableData.Table.ProvisionedThroughput;
Assert.AreEqual(readCapacity, tableCapacity.ReadCapacityUnits);
Assert.AreEqual(writeCapacity, tableCapacity.WriteCapacityUnits);
}
public void CreatesTableWithGlobalSecondaryIndexes()
{
// arrange
var args = new CreateTableArgs<Book>
(
book => book.Name,
book => book.PublishYear,
localSecondaryIndexFieldExps: null,
globalSecondaryIndexFieldExps: new GlobalSecondaryIndexDefinitions<Book>
{
book => new GlobalSecondaryIndexDefinition
{
HashKeyField = book.PublishYear
},
book => new GlobalSecondaryIndexDefinition
{
HashKeyField = book.Author,
RangeKeyField = book.NumPages
}
}
);
// act
Context.CreateTableIfNotExists(args);
var tableData = DynamoDbClient.DescribeTable(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
var secondaryIndexes = tableData.Table.GlobalSecondaryIndexes;
Assert.AreEqual(2, secondaryIndexes.Count, "Expected 2 global secondary indexes to be created");
Assert.IsTrue(secondaryIndexes.Any(description => description.IndexName.Contains("PublishYear")));
Assert.IsTrue
(
secondaryIndexes.Any
(
description =>
description.IndexName.Contains("Author")
&&
description.IndexName.Contains("NumPages")
)
);
}
public void DoesNotThrowIfTableAlreadyExists()
{
// arrange
var args = new CreateTableArgs<Book>(book => book.Name, book => book.PublishYear);
Context.CreateTableIfNotExists(args);
// act
Context.CreateTableIfNotExists(args);
}
public void CreatesTableWithGlobalSecondaryIndexUsingRuntimeVariableCapacity()
{
// arrange
Func<long> getReadCapacityFunc = () => 2;
Func<long> getWriteCapacityFunc = () => 1;
var args = new CreateTableArgs<Book>
(
book => book.Name,
book => book.PublishYear,
localSecondaryIndexFieldExps: null,
globalSecondaryIndexFieldExps: new GlobalSecondaryIndexDefinitions<Book>
{
book => new GlobalSecondaryIndexDefinition
{
HashKeyField = book.Author,
RangeKeyField = book.NumPages,
ReadCapacityUnits = getReadCapacityFunc(),
WriteCapacityUnits = getWriteCapacityFunc()
}
}
);
// act
Context.CreateTableIfNotExists(args);
var tableData = DynamoDbClient.DescribeTable(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
var secondaryIndexes = tableData.Table.GlobalSecondaryIndexes;
Assert.AreEqual(1, secondaryIndexes.Count, "Expected 1 global secondary index to be created");
var indexThroughput = secondaryIndexes[0].ProvisionedThroughput;
Assert.AreEqual(getReadCapacityFunc(), indexThroughput.ReadCapacityUnits);
Assert.AreEqual(getWriteCapacityFunc(), indexThroughput.WriteCapacityUnits);
}
/// <summary>
/// Assembles and runs a CREATE TABLE sql statement and returns the table created as a <see cref="DiscoveredTable"/>.
/// </summary>
public DiscoveredTable CreateTable(CreateTableArgs args)
{
return(Helper.CreateTable(args));
}
private void DoOKWhenCreateTable()
{
if (string.IsNullOrEmpty(textBox1.Text))
{
"CreatTableSchema_NeedTableName".GetFromResourece().Notify();
return;
}
string tableName = textBox1.Text;
if (dataGridViewForCreateTable.Rows.Count > 0)
{
List <CreateTableArgs> createTableArgsList = new List <CreateTableArgs>();
foreach (System.Windows.Forms.DataGridViewRow row in this.dataGridViewForCreateTable.Rows)
{
CreateTableArgs args = new CreateTableArgs();
if (row.Cells[ColumnName_AllowNull].Value != null)
{
try
{
args.allowNulls = CommonUtil.SwitchToBool(row.Cells[ColumnName_AllowNull].Value);
args.isPrimaryKey = CommonUtil.SwitchToBool(row.Cells[ColumnName_PrimaryKey].Value);
args.isUnique = CommonUtil.SwitchToBool(row.Cells[ColumnName_Unique].Value);
if (row.Cells[ColumnName_DataType].Value == null)
{
"Data Type Can not be empty".Notify();
return;
}
args.dataType = row.Cells[ColumnName_DataType].Value.ToString();
if (row.Cells[ColumnName_Length].Value == null)
{
"Column Length Can not be empty".Notify();
return;
}
args.dataLength = int.Parse(row.Cells[ColumnName_Length].Value.ToString());
if (row.Cells[ColumnName_ColumName].Value == null)
{
"Column Name Can not be empty".Notify();
return;
}
args.fieldName = row.Cells[ColumnName_ColumName].Value.ToString();
//Some type no need length properties ,so set empty
//These types include int,interge,
if (GlobalDefine.NoLenghtType.Collections.Contains(args.dataType))
{
args.dataLength = 0;
}
createTableArgsList.Add(args);
}
catch (Exception ee)
{
ProcessException.DisplayErrors(ee);
}
}
}
if (App.MainEngineer.CreateTableWithSchemaInfo(createTableArgsList, tableName))
{
if (RaiseAddNewTable != null)
{
RaiseAddNewTable();
}
"CreateTableSchmea_CreateTableOkMsg".GetFromResourece().Notify();
Close();
}
}
}
public DiscoveredTable CreateTable(CreateTableArgs args)
{
var typeDictionary = new Dictionary <string, Guesser>(StringComparer.CurrentCultureIgnoreCase);
List <DatabaseColumnRequest> columns = new List <DatabaseColumnRequest>();
List <DatabaseColumnRequest> customRequests = args.ExplicitColumnDefinitions != null
? args.ExplicitColumnDefinitions.ToList()
: new List <DatabaseColumnRequest>();
if (args.DataTable != null)
{
ThrowIfObjectColumns(args.DataTable);
//If we have a data table from which to create the table from
foreach (DataColumn column in args.DataTable.Columns)
{
//do we have an explicit overriding column definition?
DatabaseColumnRequest overriding = customRequests.SingleOrDefault(c => c.ColumnName.Equals(column.ColumnName, StringComparison.CurrentCultureIgnoreCase));
//yes
if (overriding != null)
{
columns.Add(overriding);
customRequests.Remove(overriding);
//Type requested is a proper FAnsi type (e.g. string, at least 5 long)
var request = overriding.TypeRequested;
if (request == null)
{
if (!string.IsNullOrWhiteSpace(overriding.ExplicitDbType))
{
//Type is for an explicit SQL Type e.g. varchar(5)
//Translate the sql type to a FAnsi type definition
var tt = args.Database.Server.GetQuerySyntaxHelper().TypeTranslater;
request = tt.GetDataTypeRequestForSQLDBType(overriding.ExplicitDbType);
}
else
{
throw new Exception(string.Format(FAnsiStrings.DiscoveredDatabaseHelper_CreateTable_DatabaseColumnRequestMustHaveEitherTypeRequestedOrExplicitDbType, column));
}
}
var guesser = GetGuesser(request);
CopySettings(guesser, args);
typeDictionary.Add(overriding.ColumnName, guesser);
}
else
{
//no, work out the column definition using a guesser
Guesser guesser = GetGuesser(column);
guesser.Culture = args.Culture;
CopySettings(guesser, args);
guesser.AdjustToCompensateForValues(column);
//if DoNotRetype is set on the column adjust the requested CSharpType to be the original type
if (column.GetDoNotReType())
{
guesser.Guess.CSharpType = column.DataType;
}
typeDictionary.Add(column.ColumnName, guesser);
columns.Add(new DatabaseColumnRequest(column.ColumnName, guesser.Guess, column.AllowDBNull)
{
IsPrimaryKey = args.DataTable.PrimaryKey.Contains(column)
});
}
}
}
else
{
//If no DataTable is provided just use the explicitly requested columns
columns = customRequests;
}
if (args.Adjuster != null)
{
args.Adjuster.AdjustColumns(columns);
}
//Get the table creation SQL
string bodySql = GetCreateTableSql(args.Database, args.TableName, columns.ToArray(), args.ForeignKeyPairs, args.CascadeDelete, args.Schema);
//connect to the server and send it
var server = args.Database.Server;
using (var con = server.GetConnection())
{
con.Open();
ExecuteBatchNonQuery(bodySql, con);
}
//Get reference to the newly created table
var tbl = args.Database.ExpectTable(args.TableName, args.Schema);
//unless we are being asked to create it empty then upload the DataTable to it
if (args.DataTable != null && !args.CreateEmpty)
{
using (var bulk = tbl.BeginBulkInsert(args.Culture))
{
bulk.DateTimeDecider.Settings.ExplicitDateFormats = args.GuessSettings.ExplicitDateFormats;
bulk.Upload(args.DataTable);
}
}
args.OnTableCreated(typeDictionary);
return(tbl);
}
public void DLELoadTwoTables(DatabaseType databaseType)
{
//setup the data tables
var defaults = new ServerDefaults(CatalogueRepository);
var logServer = defaults.GetDefaultFor(PermissableDefaults.LiveLoggingServer_ID);
var logManager = new LogManager(logServer);
var db = GetCleanedServer(databaseType);
var dtParent = new DataTable();
dtParent.Columns.Add("ID", typeof(int));
dtParent.Columns.Add("Name");
dtParent.Columns.Add("Height");
dtParent.PrimaryKey = new[] { dtParent.Columns[0] };
dtParent.Rows.Add("1", "Dave", "3.5");
var dtChild = new DataTable();
dtChild.Columns.Add("Parent_ID");
dtChild.Columns.Add("ChildNumber");
dtChild.Columns.Add("Name");
dtChild.Columns.Add("DateOfBirth");
dtChild.Columns.Add("Age");
dtChild.Columns.Add("Height");
dtChild.Rows.Add("1", "1", "Child1", "2001-01-01", "20", "3.5");
dtChild.Rows.Add("1", "2", "Child2", "2002-01-01", "19", "3.4");
dtChild.PrimaryKey = new[] { dtChild.Columns[0], dtChild.Columns[1] };
//create the parent table based on the DataTable
var parentTbl = db.CreateTable("Parent", dtParent);
//go find the primary key column created
var pkParentID = parentTbl.DiscoverColumn("ID");
//forward declare this column as part of pk (will be used to specify foreign key
var fkParentID = new DatabaseColumnRequest("Parent_ID", "int")
{
IsPrimaryKey = true
};
var args = new CreateTableArgs(
db,
"Child",
null,
dtChild,
false,
new Dictionary <DatabaseColumnRequest, DiscoveredColumn>()
{
{ fkParentID, pkParentID }
},
true);
args.ExplicitColumnDefinitions = new[]
{
fkParentID
};
var childTbl = db.CreateTable(args);
Assert.AreEqual(1, parentTbl.GetRowCount());
Assert.AreEqual(2, childTbl.GetRowCount());
//create a new load
var lmd = new LoadMetadata(CatalogueRepository, "MyLoading2");
TableInfo childTableInfo = Import(childTbl, lmd, logManager);
TableInfo parentTableInfo = Import(parentTbl, lmd, logManager);
var projectDirectory = SetupLoadDirectory(lmd);
CreateCSVProcessTask(lmd, parentTableInfo, "parent.csv");
CreateCSVProcessTask(lmd, childTableInfo, "child.csv");
//create a text file to load where we update Frank's favourite colour (it's a pk field) and we insert a new record (MrMurder)
File.WriteAllText(
Path.Combine(projectDirectory.ForLoading.FullName, "parent.csv"),
@"ID,Name,Height
2,Man2,3.1
1,Dave,3.2");
File.WriteAllText(
Path.Combine(projectDirectory.ForLoading.FullName, "child.csv"),
@"Parent_ID,ChildNumber,Name,DateOfBirth,Age,Height
1,1,UpdC1,2001-01-01,20,3.5
2,1,NewC1,2000-01-01,19,null");
//clean SetUp RAW / STAGING etc and generally accept proposed cleanup operations
var checker = new CheckEntireDataLoadProcess(lmd, new HICDatabaseConfiguration(lmd), new HICLoadConfigurationFlags(), CatalogueRepository.MEF);
checker.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(lmd);
var loadFactory = new HICDataLoadFactory(
lmd,
config,
new HICLoadConfigurationFlags(),
CatalogueRepository,
logManager
);
try
{
var exe = loadFactory.Create(new ThrowImmediatelyDataLoadEventListener());
var exitCode = exe.Run(
new DataLoadJob(RepositoryLocator, "Go go go!", logManager, lmd, projectDirectory, new ThrowImmediatelyDataLoadEventListener(), config),
new GracefulCancellationToken());
Assert.AreEqual(ExitCodeType.Success, exitCode);
//should now be 2 parents (the original - who was updated) + 1 new one (Man2)
Assert.AreEqual(2, parentTbl.GetRowCount());
var result = parentTbl.GetDataTable();
var dave = result.Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "Dave");
Assert.AreEqual(3.2f, dave["Height"]); //should now be only 3.2 inches high
AssertHasDataLoadRunId(dave);
//should be 3 children (Child1 who gets updated to be called UpdC1) and NewC1
Assert.AreEqual(3, childTbl.GetRowCount());
result = childTbl.GetDataTable();
var updC1 = result.Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "UpdC1");
Assert.AreEqual(1, updC1["Parent_ID"]);
Assert.AreEqual(1, updC1["ChildNumber"]);
AssertHasDataLoadRunId(updC1);
var newC1 = result.Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "NewC1");
Assert.AreEqual(2, newC1["Parent_ID"]);
Assert.AreEqual(1, newC1["ChildNumber"]);
Assert.AreEqual(DBNull.Value, newC1["Height"]); //the "null" in the input file should be DBNull.Value in the final database
AssertHasDataLoadRunId(newC1);
}
finally
{
Directory.Delete(lmd.LocationOfFlatFiles, true);
foreach (Catalogue c in RepositoryLocator.CatalogueRepository.GetAllObjects <Catalogue>())
{
c.DeleteInDatabase();
}
foreach (TableInfo t in RepositoryLocator.CatalogueRepository.GetAllObjects <TableInfo>())
{
t.DeleteInDatabase();
}
foreach (LoadMetadata l in RepositoryLocator.CatalogueRepository.GetAllObjects <LoadMetadata>())
{
l.DeleteInDatabase();
}
}
}
public DiscoveredTable CreateTable(CreateTableArgs args)
{
var typeDictionary = new Dictionary <string, DataTypeComputer>(StringComparer.CurrentCultureIgnoreCase);
List <DatabaseColumnRequest> columns = new List <DatabaseColumnRequest>();
List <DatabaseColumnRequest> customRequests = args.ExplicitColumnDefinitions != null
? args.ExplicitColumnDefinitions.ToList()
: new List <DatabaseColumnRequest>();
if (args.DataTable != null)
{
//If we have a data table from which to create the table from
foreach (DataColumn column in args.DataTable.Columns)
{
//do we have an explicit overriding column definition?
DatabaseColumnRequest overriding = customRequests.SingleOrDefault(c => c.ColumnName.Equals(column.ColumnName, StringComparison.CurrentCultureIgnoreCase));
//yes
if (overriding != null)
{
columns.Add(overriding);
customRequests.Remove(overriding);
//Type reqeuested
var request = overriding.TypeRequested;
//Type is for an explicit Type e.g. datetime
if (request == null)
{
if (!string.IsNullOrWhiteSpace(overriding.ExplicitDbType))
{
var tt = args.Database.Server.GetQuerySyntaxHelper().TypeTranslater;
request = new DatabaseTypeRequest(
tt.GetCSharpTypeForSQLDBType(overriding.ExplicitDbType),
tt.GetLengthIfString(overriding.ExplicitDbType),
tt.GetDigitsBeforeAndAfterDecimalPointIfDecimal(overriding.ExplicitDbType));
}
else
{
throw new Exception("explicitColumnDefinitions for column " + column + " did not contain either a TypeRequested or ExplicitDbType");
}
}
typeDictionary.Add(overriding.ColumnName, GetDataTypeComputer(request));
}
else
{
//no, work out the column definition using a datatype computer
DataTypeComputer computer = GetDataTypeComputer(column);
typeDictionary.Add(column.ColumnName, computer);
columns.Add(new DatabaseColumnRequest(column.ColumnName, computer.GetTypeRequest(), column.AllowDBNull)
{
IsPrimaryKey = args.DataTable.PrimaryKey.Contains(column)
});
}
}
}
else
{
//If no DataTable is provided just use the explicitly requested columns
columns = customRequests;
}
if (args.Adjuster != null)
{
args.Adjuster.AdjustColumns(columns);
}
//Get the table creation SQL
string bodySql = GetCreateTableSql(args.Database, args.TableName, columns.ToArray(), args.ForeignKeyPairs, args.CascadeDelete, args.Schema);
//connect to the server and send it
var server = args.Database.Server;
using (var con = server.GetConnection())
{
con.Open();
ExecuteBatchNonQuery(bodySql, con);
}
//Get reference to the newly created table
var tbl = args.Database.ExpectTable(args.TableName, args.Schema);
//unless we are being asked to create it empty then upload the DataTable to it
if (args.DataTable != null && !args.CreateEmpty)
{
tbl.BeginBulkInsert().Upload(args.DataTable);
}
args.OnTableCreated(typeDictionary);
return(tbl);
}
public void CreatesTableWithRuntimeVariableCapacity()
{
// arrange
Func<long> getReadCapacityFunc = () => 2;
Func<long> getWriteCapacityFunc = () => 1;
var args = new CreateTableArgs<Book>(getReadCapacityFunc(), getWriteCapacityFunc(), book => book.Name, book => book.PublishYear);
// act
Context.CreateTableIfNotExists(args);
var tableData = DynamoDbClient.DescribeTable(new DescribeTableRequest { TableName = BooksTableName });
// assert
Assert.IsNotNull(tableData, "Table was not created");
var tableCapacity = tableData.Table.ProvisionedThroughput;
Assert.AreEqual(getReadCapacityFunc(), tableCapacity.ReadCapacityUnits);
Assert.AreEqual(getWriteCapacityFunc(), tableCapacity.WriteCapacityUnits);
}
public void DeletesExistingTable()
{
// arrange
var args = new CreateTableArgs<Book>(book => book.Name, book => book.PublishYear);
Context.CreateTableIfNotExists(args);
// act
Context.DeleteTable<Book>();
try
{
DynamoDbClient.DescribeTable(new DescribeTableRequest { TableName = BooksTableName });
Assert.Fail();
}
catch (ResourceNotFoundException)
{
}
}