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