private async Task CopyDataAsync(bool sampleData = false, SqlPipelines pipelines = null)
{
_logger.LogInformation("Copying data started...");
_primaryKeyNames = new Dictionary <string, List <string> >();
var tables = (await _sqlConnection.QueryAsync(@"
select SCHEMA_NAME(t.schema_id) AS TABLE_SCHEMA, t.name AS TABLE_NAME
from sys.tables t
where t.temporal_type_desc IN ('SYSTEM_VERSIONED_TEMPORAL_TABLE', 'NON_TEMPORAL_TABLE')
")).ToList();
// *** refresh to workaround NCBC-2784
Dispose();
await ConnectAsync();
await CreateBucketAsync();
// ****
foreach (var table in tables)
{
await CopyDataFromTableToCollectionAsync(table.TABLE_SCHEMA, table.TABLE_NAME, sampleData, pipelines);
}
_logger.LogInformation("Copying data complete.");
}
/// <summary>
/// Start one or more Migration processes
/// </summary>
/// <param name="validateNames">Check to make sure the table names are short enough or are mapped properly</param>
/// <param name="createBucket">Create a Couchbase bucket to match the SQL catalog</param>
/// <param name="createCollections">Create Couchbase collections to match the SQL tables</param>
/// <param name="createIndexes">Create Couchbase indexes to match the SQL indexes</param>
/// <param name="copyData">Create Couchbase documents to match the SQL rows</param>
/// <param name="sampleForDemo">Only create a sample set of Couchbase documents/indexes - only suitable for demos!</param>
/// <param name="createUsers">Create Couchbase users to match the SQL users</param>
/// <param name="pipelines">Pipelines mapped to tables to perform filters/transforms</param>
/// <returns></returns>
public async Task MigrateAsync(
bool validateNames = false,
bool createBucket = false,
bool createCollections = false,
bool createIndexes = false,
bool copyData = false,
bool sampleForDemo = false,
bool createUsers = false,
SqlPipelines pipelines = null)
{
if (validateNames)
{
await ValidateNamesAsync();
}
var shouldContinue = createBucket || createCollections || createIndexes || copyData || createUsers;
if (!shouldContinue)
{
return;
}
if (!_isValid)
{
throw new Exception("Validation on SQL Server names has not been performed. Cannot continue with migration.");
}
if (createBucket)
{
await CreateBucketAsync();
}
else
{
await ConnectBucketAsync();
}
if (createCollections)
{
await CreateCollectionsAsync();
}
if (createIndexes)
{
await CreateIndexesAsync(sampleForDemo);
}
if (copyData)
{
await CopyDataAsync(sampleForDemo, pipelines);
}
if (createUsers)
{
await CreateUsersAsync();
}
}
private async Task CopyDataFromTableToCollectionAsync(string tableSchema, string tableName, bool sampleData = false, SqlPipelines pipelines = null)
{
var collectionName = GetCollectionName(tableSchema, tableName);
_logger.LogInformation($"Copying data from `{tableSchema}.{tableName}` table to {collectionName} collection...");
var counter = 0;
ICouchbaseCollection collection;
if (_config.UseSchemaForScope)
{
var scopeName = GetScopeName(tableSchema);
var scope = await _bucket.ScopeAsync(scopeName);
collection = await scope.CollectionAsync(collectionName);
}
else
{
var scope = await _bucket.DefaultScopeAsync();
collection = await scope.CollectionAsync(collectionName);
}
// lookup and use custom pipeline defined for this table
SqlPipelineBase pipeline = new SqlPipelineDefault(tableSchema, tableName);
if (sampleData)
{
pipeline = new SqlPipelineDefaultSample(tableSchema, tableName);
}
var customPipeline = pipelines?.Get(tableSchema, tableName);
if (customPipeline != null)
{
pipeline = customPipeline;
}
// buffered false because this could be a very large amount of data
// see: https://dapper-tutorial.net/buffered
using (var outerConnection = new SqlConnection(_config.SourceSqlConnectionString))
{
_logger.LogInformation($"Opening non-buffered connection to `{tableSchema}.{tableName}`");
var rows = outerConnection.Query(pipeline.Query, buffered: false);
_logger.LogInformation("Writing row(s)...");
foreach (var row in rows)
{
if (!pipeline.IsIncluded(row))
{
continue;
}
var transformedRow = pipeline.Transform(row);
string documentKey = null;
try
{
documentKey = await GetDocumentKeyFromPrimaryKeyValuesAsync(transformedRow, tableSchema, tableName);
await collection.UpsertAsync(documentKey, transformedRow);
}
catch (Exception ex)
{
_logger.LogError($"Error writing.");
_logger.LogError($"Row data: {transformedRow}");
_logger.LogError($"Document key: {documentKey}");
_logger.LogError($"Exception message: {ex.Message}");
_logger.LogError($"Exception stack trace: {ex.StackTrace}");
}
counter++;
if ((counter % 1000) == 0)
{
_logger.LogInformation(counter + "...");
}
}
}
_logger.LogInformation($"Closing non-buffered connection to `{tableSchema}.{tableName}`");
_logger.LogInformation("Done");
}