/// <summary>
/// Gets a list of statements required to add a new database table using the schema specified.
/// </summary>
/// <param name="schema">a canonical schema of the new database table</param>
internal static List <string> GetCreateTableStatements(this DbTableSchema schema, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
var lines = schema.Fields.Select(field => field.GetFieldDefinition(false, agent)).ToArray();
var result = new List <string>()
{
string.Format("CREATE TABLE {0}({1});",
schema.Name.ToConventional(agent), string.Join(", ", lines))
};
foreach (var field in schema.Fields)
{
if (field.IndexType == DbIndexType.Simple || field.IndexType == DbIndexType.Unique)
{
result.AddRange(field.GetAddIndexStatements(schema.Name, agent));
}
}
return(result);
}
private static async Task <ITable> FetchResults(DbTableSchema dbTableSchema, CloudTable table, TableQuery <DynamicTableEntity> projectionQuery)
{
// Initialize the continuation token to null to start from the beginning of the table.
TableContinuationToken continuationToken = null;
var tableResult = new AzureTable(dbTableSchema);
do
{
// Retrieve a segment (up to 1,000 entities).
var tableQueryResult =
await table.ExecuteQuerySegmentedAsync(projectionQuery, continuationToken);
// Assign the new continuation token to tell the service where to
// continue on the next iteration (or null if it has reached the end).
continuationToken = tableQueryResult.ContinuationToken;
foreach (var result in tableQueryResult.Results)
{
tableResult.AddRow(result);
}
// Loop until a null continuation token is received, indicating the end of the table.
} while (continuationToken != null);
return(tableResult);
}
/// <summary>
/// Gets a list of statements required to add a new database table using the schema specified.
/// </summary>
/// <param name="schema">a canonical schema of the new database table</param>
/// <param name="dbName">the database to add the table for</param>
/// <param name="engine">the SQL engine to use for the table</param>
/// <param name="charset">the default charset for the table</param>
internal static List <string> GetCreateTableStatements(this DbTableSchema schema, string dbName,
string engine, string charset, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (dbName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(dbName));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
var lines = schema.Fields.Select(field => field.GetFieldDefinition(agent)).ToList();
lines.AddRange(from field in schema.Fields
where field.IndexType == DbIndexType.Simple ||
field.IndexType == DbIndexType.Unique ||
field.IndexType == DbIndexType.ForeignKey
select field.GetIndexDefinition(agent));
lines.AddRange(from field in schema.Fields
where field.IndexType.IsForeignKey()
select field.GetForeignKeyDefinition(agent));
return(new List <string>()
{
string.Format("CREATE TABLE {0}.{1}({2}) ENGINE={3} DEFAULT CHARSET={4};",
dbName.Trim(), schema.Name.ToConventional(agent), string.Join(", ", lines.ToArray()),
engine.Trim(), charset.Trim())
});
}
private EntityMetadata GetEntityMetadata(Type entityType)
{
if (_entityMetadata.ContainsKey(entityType))
{
return(_entityMetadata[entityType]);
}
var metaData = new EntityMetadata(entityType);
foreach (var property in entityType.GetProperties())
{
var field = new EntityFieldMetadata
{
Property = property,
IsMapped = !(property.GetCustomAttribute <NotMappedAttribute>() != null),
IsPrimitive = ObjectExtensions.IsPrimitiveType(property.PropertyType)
};
var colAtt = property.GetCustomAttribute <ColumnNameAttribute>();
field.DataFieldName = colAtt != null ? colAtt.Name : property.Name;
if (!field.IsPrimitive)
{
field.IsList = typeof(IEnumerable).IsAssignableFrom(property.PropertyType);
field.TableReference = TableReference.Create(property);
DbTableSchema.ValidateTableRef(field.TableReference, DbTableSchema.Create(entityType));
}
metaData.Fields.Add(field);
}
_entityMetadata[entityType] = metaData;
return(metaData);
}
private async Task <DbTableSchema> GetDbTableSchemaAsync(SQLiteConnection conn, LightDataRow tableStatusRow,
LightDataTable indexData, CancellationToken cancellationToken)
{
var result = new DbTableSchema
{
Name = tableStatusRow.GetString(0).Trim(),
Fields = new List <DbFieldSchema>()
};
var indexes = indexData.Rows.Where(
row => row.GetStringOrDefault(1, string.Empty).EqualsByConvention(result.Name)).ToList();
var foreignKeys = await MyAgent.FetchUsingConnectionAsync(conn, string.Format("PRAGMA foreign_key_list({0});", result.Name),
cancellationToken).ConfigureAwait(false);
var fieldsData = await MyAgent.FetchUsingConnectionAsync(conn, string.Format("PRAGMA table_info({0});", result.Name),
cancellationToken).ConfigureAwait(false);
var createSql = tableStatusRow.GetString(1);
createSql = createSql.Replace("[", "").Replace("]", "").Substring(createSql.IndexOf("(") + 1);
createSql = createSql.Substring(0, createSql.Length - 1);
foreach (var row in fieldsData.Rows)
{
result.Fields.Add(this.GetDbFieldSchema(row, indexes, foreignKeys,
createSql.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries), result.Name));
}
return(result);
}
public SqlEntityAdapter(Database database, DbDialectProvider dialectFactory, DbTableSchema sqlTableSchema)
{
Database = database;
DialectFactory = dialectFactory;
Schema = sqlTableSchema;
PrimaryKey = Schema.Columns.Single(i => i.IsPrimaryKey);
}
public static string ToSelect(this DbTableSchema table)
{
var prefix = table.Name.Trim().ToLower().Substring(0, 1);
var fields = table.Fields.Select(f => string.Format("{0}.{1} AS {2}", prefix, f.Name, f.Name.ToPropName())).ToArray();
var primaryKey = table.PrimaryKey().Name;
return(string.Format("SELECT {0} FROM {1} {2} WHERE {2}.{3}=?{3};", string.Join(", ", fields),
table.Name, prefix, primaryKey));
}
private static string ToOrmIdentityMap(this DbTableSchema table)
{
var pk = table.PrimaryKey();
var pk_name = pk?.Name.Trim() ?? string.Empty;
var pk_field = pk_name.ToFieldName();
var pk_prop = pk_name.ToPropName();
return(string.Format(@"private static OrmIdentityMapParentInt32Autoincrement<{0}> _identityMap = new OrmIdentityMapParentInt32Autoincrement<{0}>(
""{1}"", ""{2}"", nameof({3}), () => new {0}(), a => a.{4}, (a, v) => a.{4} = v); ",
table.Name.ToClassName(), table.Name.Trim(), pk_name, pk_prop, pk_field));
}
public static ActiveRecord Create(DbTableSchema dbTableSchema, IDictionary <string, object> propertyValues)
{
var vs = new ActiveRecord {
Schema = dbTableSchema
};
foreach (var prop in dbTableSchema.Columns)
{
vs.Values.Add(prop.ColumnName, propertyValues[prop.OriginatingPropertyName]);
}
return(vs);
}
/// <summary>
/// Inserts table data from the reader to the current SqlAgent instance,
/// </summary>
/// <param name="table">a schema of the table to insert the data to</param>
/// <param name="reader">an IDataReader to read the table data from.</param>
/// <remarks>Required for <see cref="SqlAgentBase.CloneDatabase">CloneDatabase</see> infrastructure.
/// The insert is performed using a transaction that is already initiated by the
/// <see cref="SqlAgentBase.CloneDatabase">CloneDatabase</see>.</remarks>
protected override async Task <long> InsertTableDataAsync(DbTableSchema table, IDataReader reader,
long totalRowCount, long currentRow, int currentProgress, IProgress <DbCloneProgressArgs> progress,
CancellationToken ct)
{
var fields = table.Fields.Select(field => field.Name.ToConventional(Agent)).ToList();
var paramPrefixedNames = new List <string>();
var paramNames = new List <string>();
for (int i = 0; i < fields.Count; i++)
{
var paramName = GetParameterName(i);
paramNames.Add(paramName);
paramPrefixedNames.Add(ParamPrefix + paramName);
}
var insertStatement = string.Format("INSERT INTO {0}({1}) VALUES({2});",
table.Name.ToConventional(Agent), string.Join(", ", fields.ToArray()),
string.Join(", ", paramPrefixedNames.ToArray()));
while (reader.Read())
{
var paramValues = new List <SqlParam>();
for (int i = 0; i < fields.Count; i++)
{
paramValues.Add(new SqlParam(paramNames[i], reader.GetValue(i)));
}
await Agent.ExecuteCommandRawAsync(insertStatement, paramValues.ToArray()).ConfigureAwait(false);
if (CloneCanceled(progress, ct))
{
return(-1);
}
currentRow += 1;
if (progress != null && totalRowCount > 0)
{
var recalculatedProgress = (int)(100 * currentRow / (double)totalRowCount);
if (recalculatedProgress > currentProgress)
{
currentProgress = recalculatedProgress;
progress.Report(new DbCloneProgressArgs(DbCloneProgressArgs.Stage.CopyingData,
table.Name, currentProgress));
}
}
}
return(currentRow);
}
public async Task <ITable> Retrieve(DbTableSchema dbTableSchema)
{
var client = _clientFactory.CreateCloudTableClient();
var table = client.GetTableReference(dbTableSchema.TableName);
await CreateTableIfNotExistsAsync(dbTableSchema, table);
var allColumns = dbTableSchema.Columns.Select(x => x.ColumnName).ToList();
TableQuery <DynamicTableEntity> projectionQuery = new TableQuery <DynamicTableEntity>().Select(allColumns);
var tableResult = await FetchResults(dbTableSchema, table, projectionQuery);
return(tableResult);
}
public async Task <ITable> Retrieve(DbTableSchema dbTableSchema, Guid id)
{
var client = _clientFactory.CreateCloudTableClient();
var table = client.GetTableReference(dbTableSchema.TableName);
await CreateTableIfNotExistsAsync(dbTableSchema, table);
var condition = TableQuery.GenerateFilterConditionForGuid("RowKey", QueryComparisons.Equal, id);
var allColumns = dbTableSchema.Columns.Select(x => x.ColumnName).ToList();
TableQuery <DynamicTableEntity> projectionQuery = new TableQuery <DynamicTableEntity>().Select(allColumns).Where(condition);
var tableResult = await FetchResults(dbTableSchema, table, projectionQuery);
return(tableResult);
}
private static string ToOrmMapSection(this DbTableSchema table)
{
var result = new List <string>
{
table.ToOrmIdentityMap()
};
foreach (var f in table.Fields)
{
if (f.IndexType != DbIndexType.ForeignPrimary && f.IndexType != DbIndexType.Primary)
{
result.Add(f.ToOrmFieldMap(table.Name));
}
}
return(string.Join(Environment.NewLine, result.ToArray()));
}
/// <summary>
/// Gets a list of statements required to drop the database table.
/// </summary>
/// <param name="schema">the table schema to drop</param>
internal static List <string> GetDropTableStatements(this DbTableSchema schema, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
return(new List <string>()
{
string.Format("DROP TABLE {0};", schema.Name.ToConventional(agent))
});
}
public AzureRow(DbTableSchema dbTableSchema, DynamicTableEntity result)
{
foreach (var column in dbTableSchema.Columns)
{
Columns.Add(column.ColumnName, null);
}
foreach (var property in result.Properties)
{
try
{
Columns[property.Key] = property.Value.PropertyAsObject;
}
catch (Exception e)
{
Console.WriteLine("Schema broken.");
}
}
}
public static string ToClass(this DbTableSchema table)
{
var fields = string.Join(Environment.NewLine, table.Fields.Select(f => f.ToFieldDefinition()).ToArray());
var props = string.Join(Environment.NewLine + Environment.NewLine, table.Fields.Select(f => f.ToPropertyDefinition()).ToArray());
return(string.Format(@"
public class {0} : ParentBase<{0}>
{{
{1}
{2}
{3}
}}", table.Name.ToClassName(), table.ToOrmMapSection(), fields, props));
}
public async Task InsertOrUpdate(DbTableSchema dbTableSchema, IRow entityValueSet)
{
var client = _clientFactory.CreateCloudTableClient();
var table = client.GetTableReference(dbTableSchema.TableName);
await CreateTableIfNotExistsAsync(dbTableSchema, table);
var dynamicRow = new DynamicTableEntity();
foreach (var column in dbTableSchema.Columns)
{
var columnValue = entityValueSet[column.ColumnName];
dynamicRow.Properties.Add(column.ColumnName, EntityProperty.CreateEntityPropertyFromObject(columnValue));
}
dynamicRow.RowKey = entityValueSet[dbTableSchema.PrimaryKeyColumnName]?.ToString();
var insertTask = TableOperation.InsertOrReplace(dynamicRow);
var insertResult = await table.ExecuteAsync(insertTask);
if (insertResult.Result == null)
{
throw new AzureDataAccessException("Table " + dbTableSchema.TableName + " could not be inserted to. ");
}
}
private async Task <DbTableSchema> GetDbTableSchemaAsync(MySqlConnection conn, LightDataRow tableStatusRow,
Dictionary <string, Dictionary <string, string> > indexDictionary,
Dictionary <string, Dictionary <string, ForeignKeyData> > fkDictionary, CancellationToken cancellationToken)
{
var result = new DbTableSchema
{
Name = tableStatusRow.GetString(0).Trim(),
EngineName = tableStatusRow.GetString(1).Trim(),
CharsetName = tableStatusRow.GetString(14).Trim(),
Description = tableStatusRow.GetString(17).Trim(),
Fields = new List <DbFieldSchema>()
};
Dictionary <string, string> tableIndexDictionary = null;
Dictionary <string, ForeignKeyData> tableFkDictionary = null;
if (indexDictionary.ContainsKey(result.Name))
{
tableIndexDictionary = indexDictionary[result.Name];
}
if (fkDictionary.ContainsKey(result.Name))
{
tableFkDictionary = fkDictionary[result.Name];
}
var fieldsData = await MyAgent.FetchUsingConnectionAsync(conn,
string.Format("SHOW FULL COLUMNS FROM {0};", result.Name), cancellationToken).ConfigureAwait(false);
foreach (var row in fieldsData.Rows)
{
result.Fields.Add(this.GetDbFieldSchema(row, tableIndexDictionary, tableFkDictionary));
}
return(result);
}
private static async Task CreateTableIfNotExistsAsync(DbTableSchema dbTableSchema, CloudTable table)
{
await table.CreateIfNotExistsAsync();
}
private List <DbSchemaError> GetDbTableSchemaErrors(DbTableSchema gaugeSchema,
DbTableSchema actualSchema)
{
var result = new List <DbSchemaError>();
foreach (var gaugeField in gaugeSchema.Fields)
{
var gaugeFieldFound = false;
foreach (var actualField in actualSchema.Fields)
{
if (gaugeField.Name.EqualsByConvention(actualField.Name))
{
var schemasMatch = gaugeField.FieldSchemaMatch(actualField);
var indexMatch = gaugeField.FieldIndexMatch(actualField);
if (!schemasMatch)
{
result.Add(GetUnrepairableDbSchemaError(DbSchemaErrorType.FieldDefinitionObsolete,
string.Format(Properties.Resources.FieldObsoleteErrorDescription,
actualSchema.Name, actualField.Name, actualField.GetFieldDefinition(false, MyAgent),
gaugeField.GetFieldDefinition(false, MyAgent)), gaugeSchema.Name, gaugeField.Name));
}
if (!indexMatch)
{
if (actualField.IndexType == DbIndexType.None &&
(gaugeField.IndexType == DbIndexType.Simple ||
gaugeField.IndexType == DbIndexType.Unique))
{
result.Add(GetDbSchemaError(DbSchemaErrorType.IndexMissing,
string.Format(Properties.Resources.IndexMissingErrorDescription,
gaugeField.IndexName, actualSchema.Name, actualField.Name),
actualSchema.Name, actualField.Name,
gaugeField.GetAddIndexStatements(actualSchema.Name, MyAgent).ToArray()));
}
else if ((actualField.IndexType == DbIndexType.Simple &&
gaugeField.IndexType == DbIndexType.Unique) ||
(actualField.IndexType == DbIndexType.Unique &&
gaugeField.IndexType == DbIndexType.Simple))
{
var statements = actualField.GetDropIndexStatements(MyAgent);
statements.AddRange(gaugeField.GetAddIndexStatements(actualSchema.Name, MyAgent));
result.Add(GetDbSchemaError(DbSchemaErrorType.IndexObsolete,
string.Format(Properties.Resources.IndexObsoleteErrorDescription,
gaugeField.IndexName, actualSchema.Name, actualField.Name),
actualSchema.Name, actualField.Name, statements.ToArray()));
}
else if ((actualField.IndexType == DbIndexType.Simple ||
actualField.IndexType == DbIndexType.Unique) &&
gaugeField.IndexType == DbIndexType.None)
{
var statements = actualField.GetDropIndexStatements(MyAgent);
statements.AddRange(gaugeField.GetAddIndexStatements(actualSchema.Name, MyAgent));
result.Add(GetDbSchemaError(DbSchemaErrorType.IndexObsolete,
string.Format(Properties.Resources.IndexRedundantErrorDescription,
gaugeField.IndexName, actualSchema.Name, actualField.Name),
actualSchema.Name, actualField.Name,
actualField.GetDropIndexStatements(MyAgent).ToArray()));
}
else
{
result.Add(GetUnrepairableDbSchemaError(DbSchemaErrorType.IndexObsolete,
string.Format(Properties.Resources.IndexObsoleteUnreparableErrorDescription,
gaugeField.IndexName, actualSchema.Name, actualField.Name),
actualSchema.Name, actualField.Name));
}
}
gaugeFieldFound = true;
break;
}
}
if (!gaugeFieldFound)
{
if (gaugeField.DataType.ToBaseType() == DbDataType.Blob && gaugeField.NotNull)
{
result.Add(GetUnrepairableDbSchemaError(DbSchemaErrorType.FieldMissing,
string.Format(Properties.Resources.FieldMissingUnreparableErrorDescription,
gaugeField.Name, gaugeSchema.Name), gaugeSchema.Name, gaugeField.Name));
}
else
{
result.Add(GetDbSchemaError(DbSchemaErrorType.FieldMissing,
string.Format(Properties.Resources.FieldMissingErrorDescription,
gaugeField.Name, gaugeSchema.Name), gaugeSchema.Name, gaugeField.Name,
gaugeField.GetAddFieldStatements(gaugeSchema.Name, MyAgent).ToArray()));
}
}
}
foreach (var actualField in actualSchema.Fields)
{
if (!gaugeSchema.Fields.Any(gaugeField => actualField.Name.EqualsByConvention(gaugeField.Name)))
{
result.Add(GetUnrepairableDbSchemaError(DbSchemaErrorType.FieldObsolete,
string.Format(Properties.Resources.FieldRedundantUnreparableErrorDescription, actualField.Name,
actualSchema.Name), actualSchema.Name, actualField.Name));
}
}
return(result);
}
public AzureTable(DbTableSchema dbTableSchema)
{
_dbTableSchema = dbTableSchema;
}
private List <DbSchemaError> GetDbTableSchemaErrors(DbTableSchema gaugeSchema, DbTableSchema actualSchema)
{
var result = new List <DbSchemaError>();
foreach (var gaugeField in gaugeSchema.Fields)
{
var gaugeFieldFound = false;
foreach (var actualField in actualSchema.Fields)
{
if (gaugeField.Name.EqualsByConvention(actualField.Name))
{
var schemasMatch = gaugeField.FieldSchemaMatch(actualField);
var indexMatch = gaugeField.FieldIndexMatch(actualField);
var statements = new List <string>();
var inconsistencyType = DbSchemaErrorType.FieldDefinitionObsolete;
var description = string.Empty;
if (!schemasMatch && !indexMatch)
{
statements.AddRange(actualField.GetDropIndexStatements(Agent.CurrentDatabase, actualSchema.Name, MyAgent));
statements.AddRange(gaugeField.GetAlterFieldStatements(Agent.CurrentDatabase, actualSchema.Name, MyAgent));
statements.AddRange(gaugeField.GetAddIndexStatements(Agent.CurrentDatabase, actualSchema.Name, MyAgent));
description = string.Format(Properties.Resources.DbSchemaErrorFieldAndIndexObsolete,
gaugeSchema.Name, actualField.Name);
}
else if (!schemasMatch)
{
statements.AddRange(gaugeField.GetAlterFieldStatements(Agent.CurrentDatabase, actualSchema.Name, MyAgent));
description = string.Format(Properties.Resources.DbSchemaErrorFieldObsolete,
gaugeSchema.Name, actualField.Name);
}
else if (!indexMatch)
{
statements.AddRange(actualField.GetDropIndexStatements(Agent.CurrentDatabase, actualSchema.Name, MyAgent));
statements.AddRange(gaugeField.GetAddIndexStatements(Agent.CurrentDatabase, actualSchema.Name, MyAgent));
inconsistencyType = DbSchemaErrorType.IndexObsolete;
description = string.Format(Properties.Resources.DbSchemaErrorIndexObsolete,
gaugeSchema.Name, actualField.Name);
}
if (!indexMatch || !schemasMatch)
{
result.Add(GetDbSchemaError(inconsistencyType, description, gaugeSchema.Name,
gaugeField.Name, statements.ToArray()));
}
gaugeFieldFound = true;
break;
}
}
if (!gaugeFieldFound)
{
result.Add(GetDbSchemaError(DbSchemaErrorType.FieldMissing,
string.Format(Properties.Resources.DbSchemaErrorFieldMissing,
gaugeField.Name, gaugeSchema.Name), gaugeSchema.Name, gaugeField.Name,
gaugeField.GetAddFieldStatements(Agent.CurrentDatabase, gaugeSchema.Name, MyAgent).ToArray()));
}
}
foreach (var actualField in actualSchema.Fields)
{
if (!gaugeSchema.Fields.Any(gaugeField => actualField.Name.EqualsByConvention(gaugeField.Name)))
{
result.Add(GetDbSchemaError(DbSchemaErrorType.FieldObsolete,
string.Format(Properties.Resources.DbSchemaErrorFieldRedundant,
actualField.Name, actualSchema.Name), actualSchema.Name, actualField.Name,
actualField.GetDropFieldStatements(Agent.CurrentDatabase, actualSchema.Name, MyAgent).ToArray()));
}
}
return(result);
}
public async void Update(DbTableSchema dbTableSchema, IRow entityValueSet)
{
await InsertOrUpdate(dbTableSchema, entityValueSet);
}
private static DbFieldSchema PrimaryKey(this DbTableSchema table)
{
return(table.Fields.FirstOrDefault(f => f.IndexType == DbIndexType.ForeignPrimary ||
f.IndexType == DbIndexType.Primary));
}
