public string GetCreateForeignKeysQuery(IManageable model, Dictionary <string, ConstraintDefinition> constraints = null) { StringBuilder queryBuilder = new StringBuilder(); Dictionary <string, PropertyInfo> properties; ITransactionable sqlTransaction = new MySqlTransaction(); FullyQualifiedTableName tableName = new FullyQualifiedTableName(model.Configuration.Schema, $"{Manager.TablePrefix}{model.Configuration.TableName}"); if (constraints == null) { properties = model.Configuration.ForeignKeyProperties; } else { properties = model.Configuration.ForeignKeyProperties.Where(q => !constraints.ContainsKey(q.Value.Name)).ToDictionary(q => q.Key, q => q.Value); } if (properties.Count == 0) { return(string.Empty); } foreach (KeyValuePair <string, PropertyInfo> property in properties) { queryBuilder.Append(sqlTransaction.AddForeignKeyToColumn(tableName, property.Value)); } Logger.Info("(MySql) Created a new query for Create Foreign Keys:"); Logger.Info(queryBuilder.ToString()); return(queryBuilder.ToString()); }
public string CreateQueryForTableCreation(IManageable model) { if (model.Configuration.ManagedProperties.Count == 0) { return(string.Empty); } StringBuilder queryBuilder = new StringBuilder(); ITransactionable sqlTransaction = new MySqlTransaction(); // TODO: This schema setting should be set depending on which connection is beign used. FullyQualifiedTableName tableName = new FullyQualifiedTableName(model.Configuration.Schema, $"{Manager.TablePrefix}{model.Configuration.TableName}"); queryBuilder.Append(sqlTransaction.AddTable(tableName, model.Configuration.PrimaryKeyProperty.Name, GetSqlDataType(model.Configuration.PrimaryKeyProperty.PropertyType, false, 0), model.Configuration.PrimaryKeyProperty.PropertyType.Equals(typeof(int?)) || model.Configuration.PrimaryKeyProperty.PropertyType.Equals(typeof(int)))); // Aqui se colocan las propiedades del objeto. Una por columna por su puesto. foreach (KeyValuePair <string, PropertyInfo> property in model.Configuration.ManagedProperties.Where(q => q.Key != model.Configuration.PrimaryKeyProperty.Name)) { string sqlDataType = GetSqlDataType(property.Value.PropertyType, model.Configuration.UniqueKeyProperties.ContainsKey(property.Value.Name), GetDataLengthFromProperty(model, property.Key)); queryBuilder.Append(sqlTransaction.AddColumn(tableName, property.Value.Name, sqlDataType)); queryBuilder.Append(!IsNullable(property.Value) ? sqlTransaction.AddNotNullToColumn(tableName, property.Value.Name, sqlDataType) : string.Empty); queryBuilder.Append(IsUnique(model, property.Value.Name) ? sqlTransaction.AddUniqueToColumn(tableName, property.Value.Name) : string.Empty); queryBuilder.Append(IsDefault(model, property.Value.Name) ? sqlTransaction.AddDefaultToColumn(tableName, property.Value.Name, model.Configuration.DefaultAttributes[property.Value.Name].Value) : string.Empty); } Logger.Info("(MySql) Created a new query for Create Table:"); Logger.Info(queryBuilder.ToString()); return(queryBuilder.ToString()); }
public string AddForeignKeyToColumn(FullyQualifiedTableName tableName, PropertyInfo property) { ForeignKey foreignAttribute = property.GetCustomAttribute <ForeignKey>(); IManageable foreignModel = (IManageable)Activator.CreateInstance(foreignAttribute.Model); FullyQualifiedTableName foreignTableName = new FullyQualifiedTableName(foreignModel.Configuration.Schema, $"{Manager.TablePrefix}{foreignModel.Configuration.TableName}"); return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] ADD CONSTRAINT FK_{tableName.Schema}_{tableName.Table}_{property.Name} FOREIGN KEY([{property.Name}]) REFERENCES [{foreignTableName.Schema}].[{foreignTableName.Table}]([{foreignModel.Configuration.PrimaryKeyProperty.Name}]); \n"); }
public string AddForeignKeyToColumn(FullyQualifiedTableName tableName, PropertyInfo property) { ForeignKey foreignAttribute = property.GetCustomAttribute <ForeignKey>(); IManageable foreignModel = (IManageable)Activator.CreateInstance(foreignAttribute.Model); FullyQualifiedTableName foreignTableName = new FullyQualifiedTableName(foreignModel.Configuration.Schema, $"{Manager.TablePrefix}{foreignModel.Configuration.TableName}"); // TODO: Falta agregar ON DELETE y ON UPDATE !!! return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` ADD CONSTRAINT FK_{tableName.Schema}_{tableName.Table}_{property.Name} FOREIGN KEY(`{property.Name}`) REFERENCES `{foreignTableName.Schema}`.`{foreignTableName.Table}`(`{foreignModel.Configuration.PrimaryKeyProperty.Name}`); \n"); }
public string CreateQueryForTableAlteration(IManageable model, Dictionary <string, ColumnDefinition> columnDetails, Dictionary <string, ConstraintDefinition> constraints) { if (model.Configuration.ManagedProperties.Count == 0) { return(string.Empty); } StringBuilder queryBuilder = new StringBuilder(); ITransactionable sqlTransaction = new MySqlTransaction(); FullyQualifiedTableName tableName = new FullyQualifiedTableName(model.Configuration.Schema, $"{Manager.TablePrefix}{model.Configuration.TableName}"); foreach (KeyValuePair <string, PropertyInfo> property in model.Configuration.ManagedProperties) { columnDetails.TryGetValue(property.Value.Name, out ColumnDefinition columnDefinition); string sqlDataType = GetSqlDataType(property.Value.PropertyType, model.Configuration.UniqueKeyProperties.ContainsKey(property.Value.Name), GetDataLengthFromProperty(model, property.Key)); if (IsNewColumn(columnDefinition)) { queryBuilder.Append(sqlTransaction.AddColumn(tableName, property.Value.Name, sqlDataType)); continue; } if (IsColumnDataTypeChanged(columnDefinition, sqlDataType)) { if (!string.IsNullOrWhiteSpace(columnDefinition.Column_Default)) { queryBuilder.Append(sqlTransaction.RemoveDefaultFromColumn(tableName, $"DF_{tableName.Schema}_{tableName.Table}_{property.Value.Name}")); columnDefinition.Column_Default = null; } queryBuilder.Append(sqlTransaction.ChangeColumnDataType(tableName, property.Value.Name, sqlDataType)); columnDefinition.Is_Nullable = null; } queryBuilder.Append(IsNowNullable(columnDefinition, property.Value) ? sqlTransaction.RemoveNotNullFromColumn(tableName, property.Value.Name, sqlDataType) : string.Empty); queryBuilder.Append(IsNoLongerNullable(columnDefinition, property.Value) ? sqlTransaction.AddNotNullToColumn(tableName, property.Value.Name, sqlDataType) : string.Empty); queryBuilder.Append(IsNowUnique(constraints, $"UQ_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.AddUniqueToColumn(tableName, property.Value.Name) : string.Empty); queryBuilder.Append(IsNoLongerUnique(constraints, $"UQ_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.RemoveUniqueFromColumn(tableName, $"UQ_{tableName.Schema}_{tableName.Table}_{property.Value.Name}") : string.Empty); queryBuilder.Append(IsNowDefault(columnDefinition, property.Value) ? sqlTransaction.AddDefaultToColumn(tableName, property.Value.Name, model.Configuration.DefaultAttributes[property.Value.Name].Value) : string.Empty); queryBuilder.Append(IsDefaultChanged(columnDefinition, property.Value) ? sqlTransaction.RenewDefaultInColumn(tableName, property.Value.Name, model.Configuration.DefaultAttributes[property.Value.Name].Value) : string.Empty); queryBuilder.Append(IsNoLongerDefault(columnDefinition, property.Value) ? sqlTransaction.RemoveDefaultFromColumn(tableName, $"DF_{tableName.Schema}_{tableName.Table}_{property.Value.Name}") : string.Empty); queryBuilder.Append(IsNowPrimaryKey(constraints, $"PK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.AddPrimaryKeyToColumn(tableName, property.Value.Name) : string.Empty); queryBuilder.Append(IsNoLongerPrimaryKey(constraints, $"PK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.RemovePrimaryKeyFromColumn(tableName, $"PK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}") : string.Empty); queryBuilder.Append(IsNoLongerForeignKey(constraints, $"FK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.RemoveForeignKeyFromColumn(tableName, $"FK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}") : string.Empty); } foreach (KeyValuePair <string, ColumnDefinition> columnDetail in columnDetails.Where(q => !model.Configuration.ManagedProperties.Keys.Contains(q.Key))) { queryBuilder.Append(sqlTransaction.RemoveColumn(tableName, columnDetail.Key)); } return(queryBuilder.ToString()); }
public string GetCreateForeignKeysQuery(IManageable model, Dictionary <string, ConstraintDefinition> constraints = null) { StringBuilder queryBuilder = new StringBuilder(); ITransactionable sqlTransaction = new MsSqlTransaction(); FullyQualifiedTableName tableName = new FullyQualifiedTableName(model.Configuration.Schema, $"{Manager.TablePrefix}{model.Configuration.TableName}"); foreach (KeyValuePair <string, PropertyInfo> property in model.Configuration.ForeignKeyProperties) { if (constraints == null) { queryBuilder.Append(sqlTransaction.AddForeignKeyToColumn(tableName, property.Value)); continue; } if (!constraints.ContainsKey($"FK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}")) { queryBuilder.Append(sqlTransaction.AddForeignKeyToColumn(tableName, property.Value)); } } Logger.Info("Created a new query for Create Foreign Keys:"); Logger.Info(queryBuilder.ToString()); return(queryBuilder.ToString()); }
public string RemoveDefaultFromColumn(FullyQualifiedTableName tableName, string defaultConstraintName) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] DROP CONSTRAINT [{defaultConstraintName}]; \n"); }
public string ChangeColumnDataType(FullyQualifiedTableName tableName, string columnName, string sqlDataType) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] ALTER COLUMN [{columnName}] {sqlDataType}; \n"); }
public string RemoveColumn(FullyQualifiedTableName tableName, string columnName) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] DROP COLUMN [{columnName}]; \n"); }
public string AddDefaultToColumn(FullyQualifiedTableName tableName, string columnName, string defaultValue) { return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` ALTER `{columnName}` SET DEFAULT '{defaultValue}'; \n"); }
public string RemoveColumn(FullyQualifiedTableName tableName, string columnName) { return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` DROP COLUMN `{columnName}`; \n"); }
public string RemoveTable(FullyQualifiedTableName tableName) { return($"DROP TABLE [{tableName.Schema}].[{tableName.Table}]; \n"); }
public string RenewDefaultInColumn(FullyQualifiedTableName tableName, string columnName, string defaultValue) { return($"{RemoveDefaultFromColumn(tableName, $"DF_{tableName.Schema}_{tableName.Table}_{columnName}")}{AddDefaultToColumn(tableName, columnName, defaultValue)}"); }
public string AddDefaultToColumn(FullyQualifiedTableName tableName, string columnName, string defaultValue) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] ADD CONSTRAINT DF_{tableName.Schema}_{tableName.Table}_{columnName} DEFAULT '{defaultValue}' FOR [{columnName}]; \n"); }
public string AddTable(FullyQualifiedTableName tableName, string primaryKeyName, string primaryKeySqlDataType, bool isAutoIncrement) { return($"CREATE TABLE `{tableName.Schema}`.`{tableName.Table}` ({primaryKeyName} {primaryKeySqlDataType} NOT NULL {(isAutoIncrement ? "AUTO_INCREMENT" : "")}, PRIMARY KEY ({primaryKeyName})) ENGINE=InnoDB; \n"); }
public string RemoveUniqueFromColumn(FullyQualifiedTableName tableName, string uniqueKeyName) { return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` DROP INDEX {uniqueKeyName}; \n"); }
public string AddColumn(FullyQualifiedTableName tableName, string columnName, string sqlDataType) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] ADD [{columnName}] {sqlDataType}; \n"); }
public string RemovePrimaryKeyFromColumn(FullyQualifiedTableName tableName, string primaryKeyName) { return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` DROP PRIMARY KEY; \n"); }
public string RemoveNotNullFromColumn(FullyQualifiedTableName tableName, string columnName, string sqlDataType) { return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` MODIFY `{columnName}` {sqlDataType}; \n"); }
public string RemoveForeignKeyFromColumn(FullyQualifiedTableName tableName, string foreignKeyName) { return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` DROP FOREIGN KEY {foreignKeyName}; \n"); }
public string RemoveNotNullFromColumn(FullyQualifiedTableName tableName, string columnName, string sqlDataType) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] ALTER COLUMN [{columnName}] {sqlDataType}; \n"); }
public string AddColumn(FullyQualifiedTableName tableName, string columnName, string sqlDataType) { return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` ADD `{columnName}` {sqlDataType}; \n"); }
public string RemovePrimaryKeyFromColumn(FullyQualifiedTableName tableName, string primaryKeyName) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] DROP CONSTRAINT {primaryKeyName}; \n"); }
public string AddPrimaryKeyToColumn(FullyQualifiedTableName tableName, string columnName) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] ADD CONSTRAINT PK_{tableName.Schema}_{tableName.Table}_{columnName} PRIMARY KEY({columnName}); \n"); }
public string RemoveUniqueFromColumn(FullyQualifiedTableName tableName, string uniqueKeyName) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] DROP CONSTRAINT {uniqueKeyName}; \n"); }
public string AddTable(FullyQualifiedTableName tableName, string primaryKeyName, string primaryKeySqlDataType, bool isAutoIncrement) { string processedDataType = isAutoIncrement ? "INT IDENTITY(1,1)" : $"{primaryKeySqlDataType}"; return($"CREATE TABLE [{tableName.Schema}].[{tableName.Table}] ({primaryKeyName} {processedDataType}); \n{AddNotNullToColumn(tableName, primaryKeyName, primaryKeySqlDataType)}{AddPrimaryKeyToColumn(tableName, primaryKeyName)}"); }
public string AddNotNullToColumn(FullyQualifiedTableName tableName, string columnName, string sqlDataType) { return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` MODIFY `{columnName}` {sqlDataType} NOT NULL; \n"); }
public string AddUniqueToColumn(FullyQualifiedTableName tableName, string columnName) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] ADD CONSTRAINT UQ_{tableName.Schema}_{tableName.Table}_{columnName} UNIQUE({columnName}); \n"); }
public string AddNotNullToColumn(FullyQualifiedTableName tableName, string columnName, string sqlDataType) { return($"ALTER TABLE [{tableName.Schema}].[{tableName.Table}] ALTER COLUMN [{columnName}] {sqlDataType} NOT NULL; \n"); }
public string ChangeColumnDataType(FullyQualifiedTableName tableName, string columnName, string sqlDataType) { return($"ALTER TABLE `{tableName.Schema}`.`{tableName.Table}` MODIFY COLUMN `{columnName}` {sqlDataType}; \n"); }