/// <inheritdoc />
/// <summary>
/// Add field SQL
/// </summary>
/// <param name="table"></param>
/// <param name="tableName"></param>
/// <param name="connectionString"></param>
/// <param name="isNew"></param>
/// <param name="tableSchema"></param>
/// <returns></returns>
public override ResultModel <bool> AddFieldSql(CreateTableFieldViewModel table, string tableName, string connectionString,
bool isNew, string tableSchema)
{
var returnModel = new ResultModel <bool>
{
IsSuccess = false,
Result = false
};
var sqlQuery = isNew
? QueryTableBuilder.AddFieldQuery(table, tableName, tableSchema)
: QueryTableBuilder.UpdateFieldQuery(table, tableName, tableSchema);
if (!string.IsNullOrEmpty(sqlQuery))
{
using (var connection = new SqlConnection(connectionString))
{
var command = new SqlCommand(sqlQuery, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
returnModel.IsSuccess = true;
returnModel.Result = true;
return(returnModel);
}
// Empty query
return(returnModel);
}
/// <inheritdoc />
/// <summary>
/// Get table fields for builder mode
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public virtual async Task <IEnumerable <CreateTableFieldViewModel> > GetTableFieldsForBuildMode(TableModel table)
{
Arg.NotNull(table, nameof(TableModel));
var fieldTypes = await _context.TableFieldTypes
.Include(x => x.TableFieldGroups)
.ToListAsync();
var result = new List <CreateTableFieldViewModel>();
foreach (var field in table.TableFields)
{
var fieldTypeName = fieldTypes.FirstOrDefault(u => u.DataType.Equals(field.DataType))?.Name;
var configurations = await GetTableFieldConfigurations(field, table.EntityType);
var model = new CreateTableFieldViewModel
{
Id = field.Id,
Name = field.Name,
Description = field.Description,
AllowNull = field.AllowNull,
Parameter = fieldTypeName,
DataType = field.DataType,
DisplayName = field.DisplayName,
Configurations = configurations.ToList()
};
result.Add(model);
}
return(result);
}
public async Task <IActionResult> EditField(Guid fieldId, Guid type)
{
if (type == Guid.Empty || fieldId == Guid.Empty)
{
return(NotFound());
}
var field = await Context.TableFields
.Include(x => x.TableFieldConfigValues)
.FirstOrDefaultAsync(x => x.Id == fieldId);
if (field == null)
{
return(NotFound());
}
var fieldType = await Context.TableFieldTypes.FirstOrDefaultAsync(x => x.Id == type);
var fieldTypeConfig = Context.TableFieldConfigs.Where(x => x.TableFieldTypeId == fieldType.Id).ToList();
var configFields = field.TableFieldConfigValues
.Select(y =>
{
var fTypeConfig = fieldTypeConfig.Single(x => x.Id == y.TableFieldConfigId);
return(new FieldConfigViewModel
{
Name = fTypeConfig.Name,
Type = fTypeConfig.Type,
ConfigId = y.TableFieldConfigId,
Description = fTypeConfig.Description,
ConfigCode = fTypeConfig.Code,
Value = y.Value
});
}).ToList();
configFields.AddRange(fieldTypeConfig.Select(item => new FieldConfigViewModel
{
Name = item.Name,
Type = item.Type,
ConfigId = item.Id,
Description = item.Description,
ConfigCode = item.Code
}).Where(x => configFields.FirstOrDefault(y => y.ConfigCode == x.ConfigCode) == null).ToList());
var model = new CreateTableFieldViewModel
{
Id = fieldId,
TableId = field.TableId,
Configurations = configFields,
TableFieldTypeId = field.TableFieldTypeId,
DataType = field.DataType,
Parameter = fieldType.Name,
Name = field.Name,
DisplayName = field.DisplayName,
AllowNull = field.AllowNull,
Description = field.Description
};
return(View(model));
}
/// <summary>
/// Get add new field view model
/// </summary>
/// <param name="id"></param>
/// <param name="type"></param>
/// <returns></returns>
public virtual async Task <ResultModel <CreateTableFieldViewModel> > GetAddFieldCreateViewModel(Guid id, string type)
{
var rs = new ResultModel <CreateTableFieldViewModel>();
var entitiesList = await _context.Table
.Where(x => x.IsPartOfDbContext || x.EntityType.Equals(GearSettings.DEFAULT_ENTITY_SCHEMA))
.ToListAsync();
if (!entitiesList.Any(x => x.Id.Equals(id)))
{
rs.Errors.Add(new ErrorModel("error", "Entity not found!"));
return(rs);
}
var fieldType = await _context.TableFieldTypes.AsNoTracking().FirstOrDefaultAsync(x => x.Name == type.Trim());
var fieldTypeConfig = _context.TableFieldConfigs.AsNoTracking().Where(x => x.TableFieldTypeId == fieldType.Id).ToList();
var configurations = fieldTypeConfig.Select(item => new FieldConfigViewModel
{
Name = item.Name,
Type = item.Type,
ConfigId = item.Id,
Description = item.Description,
ConfigCode = item.Code
}).ToList();
var model = new CreateTableFieldViewModel
{
TableId = id,
Configurations = configurations,
TableFieldTypeId = fieldType.Id,
DataType = fieldType.DataType,
Parameter = type,
EntitiesList = entitiesList.Select(x => x.Name).OrderBy(x => x).ToList()
};
rs.IsSuccess = true;
rs.Result = model;
return(rs);
}
/// <inheritdoc />
/// <summary>
/// Add field SQL
/// </summary>
/// <param name="table"></param>
/// <param name="tableName"></param>
/// <param name="connectionString"></param>
/// <param name="isNew"></param>
/// <param name="tableSchema"></param>
/// <returns></returns>
public override ResultModel <bool> AddFieldSql(CreateTableFieldViewModel table, string tableName, string connectionString,
bool isNew, string tableSchema)
{
var returnModel = new ResultModel <bool>
{
IsSuccess = false,
Result = false,
Errors = new List <IErrorModel>()
};
var sqlQuery = isNew
? QueryTableBuilder.AddFieldQuery(table, tableName, tableSchema)
: QueryTableBuilder.UpdateFieldQuery(table, tableName, tableSchema);
if (string.IsNullOrEmpty(sqlQuery))
{
return(returnModel);
}
try
{
using (var connection = new NpgsqlConnection(connectionString))
{
var command = new NpgsqlCommand(sqlQuery, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
returnModel.IsSuccess = true;
returnModel.Result = true;
return(returnModel);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
returnModel.Errors.Add(new ErrorModel(nameof(Exception), ex.ToString()));
return(returnModel);
}
}
public override string UpdateFieldQuery(CreateTableFieldViewModel field, string tableName, string tableSchema)
{
var sql = new StringBuilder();
var alterSql = new StringBuilder();
sql.AppendFormat(" ALTER TABLE [{1}].[{0}] ", tableName, tableSchema);
sql.AppendFormat(" ALTER COLUMN [{0}] ", field.Name);
var defaultValue = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.DefaultValue)?.Value;
switch (field.DataType.Trim())
{
case "smallint":
sql.Append(" smallint");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "int":
sql.Append(" int");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "bigint":
sql.Append(" bigint");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "uniqueidentifier":
sql.Append(" uniqueidentifier");
break;
case "date":
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
else
{
sql.AppendFormat(" default drop");
}
break;
case "datetime":
sql.Append(" datetime");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
else
{
sql.AppendFormat(" default drop");
}
break;
case "datetime2":
sql.Append(" datetime2");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
else
{
sql.AppendFormat(" default drop");
}
break;
case "time":
sql.Append(" time");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
else
{
sql.AppendFormat(" default drop");
}
break;
case "nvarchar":
var maxLenght = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.ContentLen)?.Value;
sql.AppendFormat(" nvarchar({0})", maxLenght);
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "double":
sql.Append(" double");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "decimal":
var precision = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.Precision)?.Value;
var scale = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.Scale)?.Value;
sql.AppendFormat(" decimal({0},{1})", precision, scale);
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "bool":
sql.Append(" bit");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "char":
sql.Append(" char");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
default:
sql.AppendFormat(" nvarchar({0})", "10");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
}
if (!field.AllowNull)
{
sql.Append(" NOT NULL");
}
if (field.Parameter == FieldType.EntityReference)
{
var foreingTableName =
field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.ForeingTable)?.Value;
//var foreingTableSchemaName =
// field.Configurations.FirstOrDefault(x => x.ConfigCode.Equals("9999"))?.Value;
sql.AppendFormat(" FOREIGN KEY REFERENCES {2}.{0}({1})", foreingTableName, "Id", tableSchema);
}
sql.AppendFormat("{0}", alterSql);
return(sql.ToString());
}
public override string AddFieldQuery(CreateTableFieldViewModel field, string tableName, string tableSchema)
{
var sql = new StringBuilder();
var alterSql = new StringBuilder();
sql.AppendFormat(" ALTER TABLE [{1}].[{0}] ", tableName, tableSchema);
sql.AppendFormat("ADD [{0}]", field.Name);
var defaultValue = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.DefaultValue)?.Value;
switch (field.DataType.Trim())
{
case "smallint":
sql.Append(" smallint");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "int":
sql.Append(" int");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "bigint":
sql.Append(" bigint");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "uniqueidentifier":
sql.Append(" uniqueidentifier");
break;
case "date":
sql.Append(" date");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
break;
case "datetime":
sql.Append(" datetime");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
break;
case "datetime2":
sql.Append(" datetime2");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
break;
case "time":
sql.Append(" time");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
break;
case "nvarchar":
var maxLenght = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.ContentLen)?.Value;
if (string.IsNullOrEmpty(maxLenght) || maxLenght.Trim() == "0")
{
sql.AppendFormat(" nvarchar(max)");
}
else
{
sql.AppendFormat(" nvarchar({0})", maxLenght);
}
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "double":
sql.Append(" double");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "decimal":
var precision = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.Precision)?.Value;
var scale = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.Scale)?.Value;
if (string.IsNullOrEmpty(scale) && string.IsNullOrEmpty(precision))
{
sql.AppendFormat(" decimal(18,2)");
}
else
{
sql.AppendFormat(" decimal({0},{1})", precision, scale);
}
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "bool":
sql.Append(" bit");
if (!field.AllowNull)
{
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
else
{
sql.AppendFormat(" default '0' ");
}
}
break;
case "char":
sql.Append(" char");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}'", defaultValue);
}
break;
default:
sql.AppendFormat(" nvarchar({0})", "10");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}'", defaultValue);
}
break;
}
if (!field.AllowNull)
{
sql.Append(" NOT NULL");
}
if (field.Parameter == FieldType.EntityReference || field.Parameter == FieldType.File)
{
var foreingTableName =
field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.ForeingTable)?.Value;
var foreingTableSchemaName =
field.Configurations.FirstOrDefault(x => x.ConfigCode.Equals("9999"))?.Value;
var constraintName = foreingTableName + "_" + field.Name;
if (foreingTableName != null)
{
sql.AppendFormat(" CONSTRAINT FK_{0} FOREIGN KEY REFERENCES {3}.{1}({2})", constraintName,
foreingTableName, "Id", foreingTableSchemaName);
}
}
sql.AppendFormat("{0}", alterSql);
return(sql.ToString());
}
public async Task <IActionResult> EditField([Required] CreateTableFieldViewModel field)
{
var table = await Context.Table
.Include(x => x.TableFields)
.FirstOrDefaultAsync(x => x.Id == field.TableId && x.TableFields.FirstOrDefault(y => y.Id == field.Id) != null);
if (table == null)
{
return(NotFound());
}
var model = table.TableFields.FirstOrDefault(x => x.Id == field.Id);
if (model == null)
{
return(NotFound());
}
switch (field.Parameter)
{
case FieldType.EntityReference:
field.DataType = TableFieldDataType.Guid;
break;
case FieldType.Boolean:
FieldConfigViewModel defaultBool = null;
foreach (var c in field.Configurations)
{
if (c.Name != FieldConfig.DefaultValue)
{
continue;
}
defaultBool = c;
break;
}
if (defaultBool?.Value != null && defaultBool.Value.Trim() == "on")
{
defaultBool.Value = "1";
}
if (defaultBool?.Value != null && defaultBool.Value.Trim() == "off")
{
defaultBool.Value = "0";
}
break;
case FieldType.DateTime:
case FieldType.Date:
case FieldType.Time:
FieldConfigViewModel defaultTime = null;
foreach (var c in field.Configurations)
{
if (c.Name != FieldConfig.DefaultValue)
{
continue;
}
defaultTime = c;
break;
}
if (defaultTime?.Value != null && defaultTime.Value.Trim() == "on")
{
defaultTime.Value = "CURRENT_TIMESTAMP";
}
if (defaultTime?.Value != null && defaultTime.Value.Trim() == "off")
{
defaultTime.Value = null;
}
break;
}
var fieldType = await Context.TableFieldTypes
.AsNoTracking()
.FirstOrDefaultAsync(x => x.DataType == field.DataType);
var fieldTypeConfig = Context.TableFieldConfigs.AsNoTracking()
.Where(x => x.TableFieldTypeId == fieldType.Id).ToList();
var newConfigs = field.Configurations;
var dbFieldConfigs = Context.TableFieldConfigValues.AsNoTracking()
.Include(x => x.TableFieldConfig)
.Include(x => x.TableModelField)
.Where(x => x.TableModelFieldId == field.Id).ToList();
field.Configurations = dbFieldConfigs
.Select(y =>
{
var conf = fieldTypeConfig.FirstOrDefault(x => x.Id == y.TableFieldConfigId);
return(new FieldConfigViewModel
{
Name = conf?.Name,
Type = conf?.Type,
ConfigId = y.TableFieldConfigId,
Description = fieldTypeConfig.Single(x => x.Id == y.TableFieldConfigId).Description,
Value = y.Value
});
}).ToList();
var updateStructure = _tablesService.AddFieldSql(field, table.Name, ConnectionString, false, table.EntityType);
// Save field model structure in the dataBase
if (!updateStructure.IsSuccess)
{
return(View(field));
}
model.Description = field.Description;
model.Name = field.Name;
model.DisplayName = field.DisplayName;
model.AllowNull = field.AllowNull;
try
{
Context.TableFields.Update(model);
Context.SaveChanges();
_entityService.UpdateTableFieldConfigurations(model.Id, newConfigs, dbFieldConfigs);
return(RedirectToAction("Edit", "Table", new { id = field.TableId, tab = "two" }));
}
catch (Exception ex)
{
Debug.WriteLine(ex);
ModelState.AddModelError("Fail", ex.Message);
}
return(View(field));
}
public async Task <IActionResult> AddField(CreateTableFieldViewModel field)
{
var entitiesList = _entityService.Tables;
var table = entitiesList.FirstOrDefault(x => x.Id == field.TableId);
var tableName = table?.Name;
var schema = table?.EntityType;
field.EntitiesList = entitiesList.Select(x => x.Name).ToList();
if (table == null)
{
ModelState.AddModelError(string.Empty, "Table not found");
return(View(field));
}
var baseEntityProps = BaseModel.GetPropsName().Select(x => x.ToLower()).ToList();
if (baseEntityProps.Contains(field.Name.Trim().ToLower()))
{
ModelState.AddModelError(string.Empty, "This field name can't be used, is system name!");
return(View(field));
}
var configurationsRq = await _entityService.RetrieveConfigurationsOnAddNewTableFieldAsyncTask(field);
if (configurationsRq.IsSuccess)
{
field.Configurations = configurationsRq.Result.ToList();
}
field = field.CreateSqlField();
var insertField = _tablesService.AddFieldSql(field, tableName, ConnectionString, true, schema);
// Save field model in the dataBase
if (!insertField.Result)
{
ModelState.AddModelError(string.Empty, "Fail to apply changes to database!");
return(View(field));
}
if (!table.IsCommon)
{
var isDynamic = true;
var isReference = false;
var referenceIsCommon = true;
var tenants = _organizationService.GetAllTenants().Where(x => x.MachineName != GearSettings.DEFAULT_ENTITY_SCHEMA).ToList();
if (field.Parameter == FieldType.EntityReference)
{
isReference = true;
var referenceTableName = field.Configurations
.FirstOrDefault(x => x.Name == nameof(TableFieldConfigCode.Reference.ForeingTable))?.Value;
if (!referenceTableName.IsNullOrEmpty())
{
var refTable = await Context.Table.FirstOrDefaultAsync(x =>
x.Name.Equals(referenceTableName) && x.EntityType.Equals(GearSettings.DEFAULT_ENTITY_SCHEMA) ||
x.Name.Equals(referenceTableName) && x.IsPartOfDbContext);
if (refTable.IsPartOfDbContext)
{
isDynamic = false;
}
else if (!refTable.IsCommon)
{
referenceIsCommon = false;
}
}
}
foreach (var tenant in tenants)
{
if (isDynamic && isReference && !referenceIsCommon)
{
var schemaConf = field.Configurations?.FirstOrDefault(x =>
x.ConfigCode.Equals(TableFieldConfigCode.Reference.ForeingSchemaTable));
if (schemaConf != null)
{
var index = field.Configurations.IndexOf(schemaConf);
schemaConf.Value = tenant.MachineName;
field.Configurations = field.Configurations.Replace(index, schemaConf).ToList();
}
}
_tablesService.AddFieldSql(field, tableName, ConnectionString, true, tenant.MachineName);
}
}
var configs = field.Configurations.Select(item => new TableFieldConfigValue
{
TableFieldConfigId = item.ConfigId,
Value = item.Value,
}).ToList();
var model = new TableModelField
{
DataType = field.DataType,
TableId = field.TableId,
Description = field.Description,
Name = field.Name,
DisplayName = field.DisplayName,
AllowNull = field.AllowNull,
Synchronized = true,
TableFieldTypeId = field.TableFieldTypeId,
TableFieldConfigValues = configs
};
Context.TableFields.Add(model);
var result = await Context.SaveAsync();
if (result.IsSuccess)
{
RefreshRuntimeTypes();
return(RedirectToAction("Edit", "Table", new { id = field.TableId, tab = "two" }));
}
ModelState.AppendResultModelErrors(result.Errors);
return(View(field));
}
public static CreateTableFieldViewModel CreateSqlField(this CreateTableFieldViewModel field)
{
switch (field.Parameter)
{
case FieldType.EntityReference:
field.DataType = TableFieldDataType.Guid;
break;
case FieldType.Boolean:
FieldConfigViewModel defaultBool = null;
foreach (var c in field.Configurations)
{
if (c.Name != FieldConfig.DefaultValue)
{
continue;
}
defaultBool = c;
break;
}
if (defaultBool?.Value != null && defaultBool.Value.Trim() == "on")
{
defaultBool.Value = "1";
}
if (defaultBool?.Value != null && defaultBool.Value.Trim() == "off")
{
defaultBool.Value = "0";
}
break;
case FieldType.DateTime:
case FieldType.Date:
case FieldType.Time:
FieldConfigViewModel defaultTime = null;
foreach (var c in field.Configurations)
{
if (c.Name != FieldConfig.DefaultValue)
{
continue;
}
defaultTime = c;
break;
}
if (defaultTime?.Value != null && defaultTime.Value.Trim() == "on")
{
defaultTime.Value = "CURRENT_TIMESTAMP";
}
if (defaultTime?.Value != null && defaultTime.Value.Trim() == "off")
{
defaultTime.Value = null;
}
break;
case FieldType.File:
field.DataType = TableFieldDataType.Guid;
var foreignTable = field.Configurations.FirstOrDefault(s => s.Name == "ForeingTable");
if (foreignTable != null)
{
foreignTable.Value = "FileReferences";
}
break;
}
return(field);
}
/// <summary>
/// Get configurations on add new field
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public virtual async Task <ResultModel <IEnumerable <FieldConfigViewModel> > > RetrieveConfigurationsOnAddNewTableFieldAsyncTask(CreateTableFieldViewModel field)
{
Arg.NotNull(field, nameof(RetrieveConfigurationsOnAddNewTableFieldAsyncTask));
var rs = new ResultModel <IEnumerable <FieldConfigViewModel> >();
var data = field.Configurations ?? new List <FieldConfigViewModel>();
var dbConfigs = await _context.TableFieldConfigs.AsNoTracking().Where(x => x.TableFieldTypeId == field.TableFieldTypeId).ToListAsync();
var fieldTypeConfig = dbConfigs.Select(item => new FieldConfigViewModel
{
Name = item.Name,
Type = item.Type,
ConfigId = item.Id,
Description = item.Description,
ConfigCode = item.Code
}).ToList();
if (field.Parameter != FieldType.EntityReference)
{
return(rs);
}
{
var foreignSchema = fieldTypeConfig.FirstOrDefault(x => x.ConfigCode == TableFieldConfigCode.Reference.ForeingSchemaTable);
var foreignTable = await _context.Table
.AsNoTracking()
.FirstOrDefaultAsync(x => x.Name == field.Configurations
.FirstOrDefault(y => y.Name == FieldConfig.ForeingTable)
.Value);
if (foreignSchema == null)
{
return(rs);
}
if (foreignTable != null)
{
foreignSchema.Value = foreignTable.IsPartOfDbContext ? foreignTable.EntityType : GearSettings.DEFAULT_ENTITY_SCHEMA;
}
var exist = data.FirstOrDefault(x =>
x.Name == nameof(TableFieldConfigCode.Reference.ForeingSchemaTable));
if (exist == null)
{
data.Add(foreignSchema);
}
else
{
var index = data.IndexOf(exist);
data = data.Replace(index, foreignSchema).ToList();
}
}
rs.Result = data;
rs.IsSuccess = true;
return(rs);
}
public override string UpdateFieldQuery(CreateTableFieldViewModel field, string tableName, string tableSchema)
{
var sql = new StringBuilder();
var alterSql = new StringBuilder();
sql.AppendFormat(" ALTER TABLE \"{1}\".\"{0}\" ", tableName, tableSchema);
sql.AppendFormat(" ALTER COLUMN \"{0}\" TYPE", field.Name);
var defaultValue = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.DefaultValue)?.Value;
switch (field.DataType.Trim())
{
case "smallint":
sql.Append(" smallint");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "int":
sql.Append(" int");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "bigint":
sql.Append(" bigint");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "uniqueidentifier":
sql.Append(" uuid");
break;
case "date":
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
else
{
sql.AppendFormat(" default drop");
}
break;
case "datetime":
sql.Append(" TIMESTAMP");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
else
{
sql.AppendFormat(" default drop");
}
break;
case "datetime2":
sql.Append(" TIMESTAMP");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
else
{
sql.AppendFormat(" default drop");
}
break;
case "time":
sql.Append(" time");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
else
{
sql.AppendFormat(" default drop");
}
break;
case "nvarchar":
var maxLength = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.ContentLen)?.Value;
if (string.IsNullOrEmpty(maxLength) || maxLength.Trim() == "0")
{
sql.AppendFormat(" varchar(3999)");
}
else
{
sql.AppendFormat(" varchar({0})", maxLength);
}
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "double":
sql.Append(" double precision");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "decimal":
var precision = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.Precision)?.Value;
var scale = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.Scale)?.Value;
sql.AppendFormat(" decimal({0},{1})", precision, scale);
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "bool":
sql.Append(" boolean");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
case "char":
sql.Append(" char");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
default:
sql.AppendFormat(" varchar({0})", "10");
if (defaultValue != null)
{
sql.AppendFormat(" default'{0}'", defaultValue);
}
break;
}
sql.AppendFormat(", ALTER COLUMN \"{0}\" ", field.Name);
sql.Append(!field.AllowNull ? "SET NOT NULL" : "DROP NOT NULL");
//if (field.Parameter == FieldType.EntityReference)
//{
// var foreignTableName =
// field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.ForeingTable)?.Value;
// var foreignTableSchemaName =
// field.Configurations.FirstOrDefault(x => x.ConfigCode == "9999");
// sql.AppendFormat(" FOREIGN KEY REFERENCES \"{2}\".\"{0}\"({1})", foreignTableName, "\"Id\"", foreignTableSchemaName);
//}
sql.AppendFormat("{0}", alterSql);
return(sql.ToString());
}
public override string AddFieldQuery(CreateTableFieldViewModel field, string tableName, string tableSchema)
{
var sql = new StringBuilder();
var alterSql = new StringBuilder();
sql.AppendFormat(" ALTER TABLE \"{1}\".\"{0}\" ", tableName, tableSchema);
sql.AppendFormat("ADD COLUMN \"{0}\"", field.Name);
var defaultValue = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.DefaultValue)?.Value;
switch (field.DataType.Trim())
{
case "smallint":
sql.Append(" smallint");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "int":
sql.Append(" int");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "bigint":
sql.Append(" bigint");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "uniqueidentifier":
sql.Append(" uuid");
break;
case "date":
sql.Append(" date");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
break;
case "datetime":
sql.Append(" TIMESTAMP");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
break;
case "datetime2":
sql.Append(" TIMESTAMP");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
break;
case "time":
sql.Append(" time");
if (defaultValue != null)
{
sql.AppendFormat(" default {0} ", defaultValue);
}
break;
case "nvarchar":
var maxLength = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.ContentLen)?.Value;
if (string.IsNullOrEmpty(maxLength) || maxLength.Trim() == "0")
{
sql.AppendFormat(" varchar(3999)");
}
else
{
sql.AppendFormat(" varchar({0})", maxLength);
}
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "double":
sql.Append(" double precision");
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "decimal":
var precision = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.Precision)?.Value;
var scale = field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.Scale)?.Value;
if (string.IsNullOrEmpty(scale) && string.IsNullOrEmpty(precision))
{
sql.AppendFormat(" decimal(18,2)");
}
else
{
sql.AppendFormat(" decimal({0},{1})", precision, scale);
}
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
break;
case "bool":
sql.Append(" boolean");
if (!field.AllowNull)
{
if (defaultValue != null)
{
sql.AppendFormat(" default '{0}' ", defaultValue);
}
else
{
sql.AppendFormat(" default '0' ");
}
}
break;
case "char":
sql.Append(" CHAR");
if (defaultValue != null)
{
sql.AppendFormat(" DEFAULT '{0}'", defaultValue);
}
break;
default:
sql.AppendFormat(" VARCHAR({0})", "10");
if (defaultValue != null)
{
sql.AppendFormat(" DEFAULT '{0}'", defaultValue);
}
break;
}
sql.Append(!field.AllowNull ? " NOT NULL" : " NULL");
if (field.Parameter == FieldType.EntityReference || field.Parameter == FieldType.File)
{
var foreignTableName =
field.Configurations.FirstOrDefault(x => x.Name == FieldConfig.ForeingTable)?.Value;
var foreignSchemaTableName = field.Configurations.FirstOrDefault(x => x.ConfigCode == "9999")?.Value;
var constraintName = foreignTableName + "_" + field.Name;
if (foreignTableName != null)
{
sql.AppendFormat(" ,ADD CONSTRAINT FK_{0} FOREIGN KEY (\"{3}\") REFERENCES \"{4}\".\"{1}\"({2})", constraintName,
foreignTableName, "\"Id\"", field.Name, foreignSchemaTableName);
}
}
sql.AppendFormat("{0}", alterSql);
return(sql.ToString());
}
public abstract string AddFieldQuery(CreateTableFieldViewModel field, string tableName, string tableSchema);
/// <summary>
/// Get entity data
/// </summary>
/// <param name="prop"></param>
/// <param name="context"></param>
/// <returns></returns>
protected virtual SynchronizeTableViewModel GetEntityData(PropertyInfo prop, Type context)
{
var result = new SynchronizeTableViewModel();
var fields = new List <CreateTableFieldViewModel>();
var baseProps = BaseModel.GetPropsName().ToList();
var entity = prop.PropertyType.GenericTypeArguments[0];
//Exclude context not mapped entities
if (Attribute.IsDefined(prop, typeof(NotMappedAttribute)))
{
return(null);
}
result.Name = prop.Name;
result.IsStaticFromEntityFramework = true;
result.IsSystem = true;
if (context.GetField("Schema") == null)
{
throw new Exception("This context does not have the Schema field, which stores the schema name");
}
result.Schema = context.GetField("Schema").GetValue(context).ToString();
result.Description = $"System {prop.Name} entity";
var entityProps = entity.GetProperties().Select(x => x.Name).Except(baseProps).ToList();
foreach (var field in entityProps)
{
var propField = entity.GetProperties().FirstOrDefault(x => x.Name.Equals(field));
if (propField == null)
{
continue;
}
var propType = propField.PropertyType.FullName;
switch (propType)
{
case "System.String":
fields.Add(new CreateTableFieldViewModel
{
Name = field,
DisplayName = field,
TableFieldCode = "10",
DataType = TableFieldDataType.String,
Configurations = new List <FieldConfigViewModel>
{
new FieldConfigViewModel
{
ConfigCode = "1000",
Value = "500"
}
}
});
break;
case "System.Guid":
var f = new CreateTableFieldViewModel
{
Name = field,
DisplayName = field,
TableFieldCode = "30",
DataType = TableFieldDataType.Guid
};
if (field.EndsWith("Id"))
{
var ent = field.Remove(field.Length - "Id".Length);
if (entityProps.Contains(ent))
{
f.Parameter = "EntityReference";
f.Configurations = new List <FieldConfigViewModel>
{
new FieldConfigViewModel
{
ConfigCode = TableFieldConfigCode.Reference.ForeingSchemaTable,
Value = result.Schema
},
new FieldConfigViewModel
{
ConfigCode = TableFieldConfigCode.Reference.ForeingTable,
Value = ent
}
};
}
}
fields.Add(f);
break;
case "System.Int":
case "System.Int32":
fields.Add(new CreateTableFieldViewModel
{
Name = field,
DisplayName = field,
TableFieldCode = "01",
DataType = TableFieldDataType.Int,
Configurations = new List <FieldConfigViewModel>
{
new FieldConfigViewModel
{
ConfigCode = "0100",
Value = null
},
new FieldConfigViewModel
{
ConfigCode = "0101",
Value = null
}
}
});
break;
case "System.Double":
fields.Add(new CreateTableFieldViewModel
{
Name = field,
DisplayName = field,
TableFieldCode = "01",
DataType = TableFieldDataType.Int,
Configurations = new List <FieldConfigViewModel>
{
new FieldConfigViewModel
{
ConfigCode = "0100",
Value = null
},
new FieldConfigViewModel
{
ConfigCode = "0101",
Value = null
}
}
});
break;
case "System.Boolean":
fields.Add(new CreateTableFieldViewModel
{
Name = field,
DisplayName = field,
TableFieldCode = "40",
DataType = TableFieldDataType.Boolean,
Configurations = new List <FieldConfigViewModel>
{
new FieldConfigViewModel
{
ConfigCode = "4000",
Value = null
}
}
});
break;
}
}
result.Fields = fields;
return(result);
}
public abstract ResultModel <bool> AddFieldSql(CreateTableFieldViewModel table, string tableName,
string connectionString, bool isNew,
string tableSchema);
