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