/// <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)); }