public static NpgsqlDbType GetDatabaseFieldType(DbBaseField field)
{
NpgsqlDbType pgType = NpgsqlDbType.Numeric;
if (field is DbAutoNumberField)
{
pgType = NpgsqlDbType.Numeric;
}
else if (field is DbCheckboxField)
{
pgType = NpgsqlDbType.Boolean;
}
else if (field is DbCurrencyField)
{
pgType = NpgsqlDbType.Numeric;
}
else if (field is DbDateField)
{
pgType = NpgsqlDbType.Date;
}
else if (field is DbDateTimeField)
{
pgType = NpgsqlDbType.TimestampTZ;
}
else if (field is DbEmailField)
{
pgType = NpgsqlDbType.Varchar;
}
else if (field is DbFileField)
{
pgType = NpgsqlDbType.Varchar;
}
else if (field is DbGuidField)
{
pgType = NpgsqlDbType.Uuid;
}
else if (field is DbHtmlField)
{
pgType = NpgsqlDbType.Text;
}
else if (field is DbImageField)
{
pgType = NpgsqlDbType.Varchar;
}
else if (field is DbMultiLineTextField)
{
pgType = NpgsqlDbType.Text;
}
else if (field is DbMultiSelectField)
{
pgType = NpgsqlDbType.Array | NpgsqlDbType.Text;
}
else if (field is DbNumberField)
{
pgType = NpgsqlDbType.Numeric;
}
else if (field is DbPasswordField)
{
pgType = NpgsqlDbType.Varchar;
}
else if (field is DbPercentField)
{
pgType = NpgsqlDbType.Numeric;
}
else if (field is DbPhoneField)
{
pgType = NpgsqlDbType.Varchar;
}
else if (field is DbSelectField)
{
pgType = NpgsqlDbType.Varchar;
}
else if (field is DbTextField)
{
pgType = NpgsqlDbType.Text;
}
else if (field is DbTreeSelectField)
{
pgType = NpgsqlDbType.Array | NpgsqlDbType.Uuid;
}
else if (field is DbUrlField)
{
pgType = NpgsqlDbType.Varchar;
}
else
{
throw new Exception("FieldType is not supported.");
}
return(pgType);
}
public bool Create(DbEntityRelation relation)
{
try
{
if (relation == null)
{
throw new ArgumentNullException("relation");
}
List <DbParameter> parameters = new List <DbParameter>();
JsonSerializerSettings settings = new JsonSerializerSettings {
TypeNameHandling = TypeNameHandling.Auto
};
DbParameter parameterId = new DbParameter();
parameterId.Name = "id";
parameterId.Value = relation.Id;
parameterId.Type = NpgsqlDbType.Uuid;
parameters.Add(parameterId);
DbParameter parameterJson = new DbParameter();
parameterJson.Name = "json";
parameterJson.Value = JsonConvert.SerializeObject(relation, settings);
parameterJson.Type = NpgsqlDbType.Json;
parameters.Add(parameterJson);
List <DbEntity> entities = DbContext.Current.EntityRepository.Read();
DbEntity originEntity = entities.FirstOrDefault(e => e.Id == relation.OriginEntityId);
DbEntity targetEntity = entities.FirstOrDefault(e => e.Id == relation.TargetEntityId);
DbBaseField originField = originEntity.Fields.FirstOrDefault(f => f.Id == relation.OriginFieldId);
DbBaseField targetField = targetEntity.Fields.FirstOrDefault(f => f.Id == relation.TargetFieldId);
string originTableName = $"rec_{originEntity.Name}";
string targetTableName = $"rec_{targetEntity.Name}";
using (DbConnection con = DbContext.Current.CreateConnection())
{
con.BeginTransaction();
try
{
bool result = DbRepository.InsertRecord("entity_relations", parameters);
if (relation.RelationType == EntityRelationType.ManyToMany)
{
DbRepository.DeleteNtoNRelation(relation.Name, originTableName, targetTableName);
DbRepository.CreateNtoNRelation(relation.Name, originTableName, originField.Name, targetTableName, targetField.Name);
}
else
{
DbRepository.DeleteRelation(relation.Name, targetTableName);
if (originField.Name != "id")
{
DbRepository.DropIndex($"idx_r_{relation.Name}_{originField.Name}");
}
if (targetField.Name != "id")
{
DbRepository.DropIndex($"idx_r_{relation.Name}_{targetField.Name}");
}
DbRepository.CreateRelation(relation.Name, originTableName, originField.Name, targetTableName, targetField.Name);
if (originField.Name != "id")
{
DbRepository.CreateIndex($"idx_r_{relation.Name}_{originField.Name}", originTableName, originField.Name);
}
if (targetField.Name != "id")
{
DbRepository.CreateIndex($"idx_r_{relation.Name}_{targetField.Name}", targetTableName, targetField.Name);
}
}
con.CommitTransaction();
return(true);
}
catch
{
con.RollbackTransaction();
throw;
}
}
}
finally
{
Cache.Clear();
}
}
public bool Delete(Guid relationId)
{
DbEntityRelation relation = Read(relationId);
if (relation == null)
{
throw new StorageException("There is no record with specified relation id.");
}
List <DbEntity> entities = DbContext.Current.EntityRepository.Read();
DbEntity originEntity = entities.FirstOrDefault(e => e.Id == relation.OriginEntityId);
DbEntity targetEntity = entities.FirstOrDefault(e => e.Id == relation.TargetEntityId);
DbBaseField originField = originEntity.Fields.FirstOrDefault(f => f.Id == relation.OriginFieldId);
DbBaseField targetField = targetEntity.Fields.FirstOrDefault(f => f.Id == relation.TargetFieldId);
string originTableName = $"rec_{originEntity.Name}";
string targetTableName = $"rec_{targetEntity.Name}";
try
{
using (DbConnection con = DbContext.Current.CreateConnection())
{
con.BeginTransaction();
NpgsqlCommand command = con.CreateCommand("DELETE FROM entity_relations WHERE id=@id;");
var parameterId = command.CreateParameter() as NpgsqlParameter;
parameterId.ParameterName = "id";
parameterId.Value = relationId;
parameterId.NpgsqlDbType = NpgsqlDbType.Uuid;
command.Parameters.Add(parameterId);
try
{
command.ExecuteNonQuery();
if (originField.Name != "id")
{
DbRepository.DropIndex($"idx_r_{relation.Name}_{originField.Name}");
}
if (targetField.Name != "id")
{
DbRepository.DropIndex($"idx_r_{relation.Name}_{targetField.Name}");
}
if (relation.RelationType == EntityRelationType.ManyToMany)
{
DbRepository.DeleteNtoNRelation(relation.Name, originTableName, targetTableName);
}
else
{
DbRepository.DeleteRelation(relation.Name, targetTableName);
}
con.CommitTransaction();
return(true);
}
catch (Exception)
{
con.RollbackTransaction();
}
}
return(false);
}
finally
{
Cache.Clear();
}
}
public static NpgsqlDbType GetDatabaseFieldType(DbBaseField field)
{
NpgsqlDbType pgType = NpgsqlDbType.Numeric;
if (field is DbAutoNumberField)
pgType = NpgsqlDbType.Numeric;
else if (field is DbCheckboxField)
pgType = NpgsqlDbType.Boolean;
else if (field is DbCurrencyField)
pgType = NpgsqlDbType.Numeric;
else if (field is DbDateField)
pgType = NpgsqlDbType.Date;
else if (field is DbDateTimeField)
pgType = NpgsqlDbType.TimestampTZ;
else if (field is DbEmailField)
pgType = NpgsqlDbType.Varchar;
else if (field is DbFileField)
pgType = NpgsqlDbType.Varchar;
else if (field is DbGuidField)
pgType = NpgsqlDbType.Uuid;
else if (field is DbHtmlField)
pgType = NpgsqlDbType.Text;
else if (field is DbImageField)
pgType = NpgsqlDbType.Varchar;
else if (field is DbMultiLineTextField)
pgType = NpgsqlDbType.Text;
else if (field is DbMultiSelectField)
pgType = NpgsqlDbType.Array | NpgsqlDbType.Text;
else if (field is DbNumberField)
pgType = NpgsqlDbType.Numeric;
else if (field is DbPasswordField)
pgType = NpgsqlDbType.Varchar;
else if (field is DbPercentField)
pgType = NpgsqlDbType.Numeric;
else if (field is DbPhoneField)
pgType = NpgsqlDbType.Varchar;
else if (field is DbSelectField)
pgType = NpgsqlDbType.Varchar;
else if (field is DbTextField)
pgType = NpgsqlDbType.Text;
else if (field is DbTreeSelectField)
pgType = NpgsqlDbType.Array | NpgsqlDbType.Uuid;
else if (field is DbUrlField)
pgType = NpgsqlDbType.Varchar;
else
throw new Exception("FieldType is not supported.");
return pgType;
}
