//to prepare TableAttribute
internal static TableAttribute PrepareTableAttribute(Type entity)
{
TableAttribute result = (TableAttribute)entity.GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
if (result == null)
{
result = new TableAttribute
{
Name = entity.Name, //assuming entity class name is table name
NeedsHistory = Config.NeedsHistory,
NoCreatedBy = Config.NoCreatedBy,
NoCreatedOn = Config.NoCreatedOn,
NoUpdatedBy = Config.NoUpdatedBy,
NoUpdatedOn = Config.NoUpdatedOn,
NoVersionNo = Config.NoVersionNo,
NoIsActive = Config.NoIsActive
};
}
if (string.IsNullOrEmpty(result.Name))
{
result.Name = entity.Name;
}
//find all properties
var properties = entity.GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo property in properties)
{
//TODO: check for valid property types to be added in list
if ((property.Name.Equals("keyid", StringComparison.OrdinalIgnoreCase) ||
property.Name.Equals("operation", StringComparison.OrdinalIgnoreCase)))
{
continue;
}
//check for ignore property attribute
var ignoreInfo = (IgnoreColumnAttribute)property.GetCustomAttribute(typeof(IgnoreColumnAttribute));
var primaryKey = (PrimaryKeyAttribute)property.GetCustomAttribute(typeof(PrimaryKeyAttribute));
var column = (ColumnAttribute)property.GetCustomAttribute(typeof(ColumnAttribute));
if (column == null)
{
column = new ColumnAttribute();
}
if (string.IsNullOrEmpty(column.Name))
{
column.Name = property.Name;
}
if (property.Name.Equals("CreatedBy", StringComparison.OrdinalIgnoreCase))
{
column.Name = Config.CreatedByColumnName;
}
else if (property.Name.Equals("CreatedByName"))
{
column.Name = Config.CreatedByNameColumnName;
}
else if (property.Name.Equals("CreatedOn"))
{
column.Name = Config.CreatedOnColumnName;
}
else if (property.Name.Equals("UpdatedBy"))
{
column.Name = Config.UpdatedByColumnName;
}
else if (property.Name.Equals("UpdatedByName"))
{
column.Name = Config.UpdatedByNameColumnName;
}
else if (property.Name.Equals("UpdatedOn"))
{
column.Name = Config.UpdatedOnColumnName;
}
else if (property.Name.Equals("VersionNo"))
{
column.Name = Config.VersionNoColumnName;
}
else if (property.Name.Equals("IsActive"))
{
column.Name = Config.IsActiveColumnName;
}
if (!column.IsColumnDbTypeDefined)
{
if (column.Name.Equals(Config.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase))
{
column.ColumnDbType = Config.CreatedUpdatedByColumnType;
}
else if (property.PropertyType.IsEnum)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType.GetEnumUnderlyingType()];
}
else if (property.PropertyType.IsValueType)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType];
}
else
{
TypeCache.TypeToDbType.TryGetValue(property.PropertyType, out DbType columnDbType);
column.ColumnDbType = columnDbType;
}
}
column.SetPropertyInfo(property, entity);
column.IgnoreInfo = ignoreInfo ?? new IgnoreColumnAttribute(false);
//Primary Key details
if (primaryKey != null)
{
column.PrimaryKeyInfo = primaryKey;
var virtualForeignKeys = (IEnumerable <ForeignKey>)property.GetCustomAttributes(typeof(ForeignKey));
if (virtualForeignKeys != null && virtualForeignKeys.Count() > 0)
{
if (result.VirtualForeignKeys == null)
{
result.VirtualForeignKeys = new List <ForeignKey>();
}
result.VirtualForeignKeys.AddRange(virtualForeignKeys);
}
}
if (result.NoCreatedBy && (column.Name.Equals(Config.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.CreatedByNameColumnName, StringComparison.OrdinalIgnoreCase)))
{
continue;
}
else if (result.NoCreatedOn && column.Name.Equals(Config.CreatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoUpdatedBy && ((column.Name.Equals(Config.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.UpdatedByNameColumnName, StringComparison.OrdinalIgnoreCase))))
{
continue;
}
else if (result.NoUpdatedOn && column.Name.Equals(Config.UpdatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoIsActive && column.Name.Equals(Config.IsActiveColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoVersionNo && column.Name.Equals(Config.VersionNoColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else
{
if (!column.IgnoreInfo.Insert)
{
result.DefaultInsertColumns.Add(column.Name);
}
//isactive,createdon,createdby column shall not be included in default update columns
if (!column.IgnoreInfo.Update &&
!column.Name.Equals(Config.IsActiveColumnName, StringComparison.OrdinalIgnoreCase) &&
!column.Name.Equals(Config.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) &&
!column.Name.Equals(Config.CreatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
result.DefaultUpdateColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Read)
{
result.DefaultReadColumns.Add(column.Name);
}
result.Columns[column.Name] = column;
}
}
if (result.Columns.LongCount(p => p.Value.IsPrimaryKey && p.Value.PrimaryKeyInfo.IsIdentity) > 1)
{
throw new NotSupportedException("Primary key with multiple Identity is not supported on " + result.Name);
}
if (result.Columns.LongCount(p => p.Value.IsPrimaryKey) > 1 && result.NeedsHistory)
{
throw new NotSupportedException($"History for {result.Name} is not supported as it has composite Primary key");
}
return(result);
}
internal static TableAttribute GetAuditTrialTableAttribute()
{
TableAttribute result = new TableAttribute
{
Name = Config.VegaConfig.AuditTableName,
NeedsHistory = false,
NoCreatedBy = false,
NoCreatedOn = false,
NoUpdatedBy = true,
NoUpdatedOn = true,
NoVersionNo = true,
NoIsActive = true
};
var type = typeof(AuditTrial);
var pkProperty = typeof(AuditTrial).GetProperty("AuditTrailId");
result.PrimaryKeyAttribute = (PrimaryKeyAttribute)pkProperty.GetCustomAttributes(typeof(PrimaryKeyAttribute)).FirstOrDefault();
result.PrimaryKeyColumn = new ColumnAttribute()
{
Name = Config.VegaConfig.AuditKeyColumnName,
ColumnDbType = DbType.Int64,
Property = pkProperty,
SetMethod = pkProperty.GetSetMethod(),
GetMethod = pkProperty.GetGetMethod(),
SetAction = Helper.CreateSetProperty(type, pkProperty.Name),
GetAction = Helper.CreateGetProperty(type, pkProperty.Name),
};
foreach (PropertyInfo property in type.GetProperties())
{
//TODO: check for valid property types to be added in list
if ((property.Name.Equals("keyid", StringComparison.OrdinalIgnoreCase) ||
property.Name.Equals("operation", StringComparison.OrdinalIgnoreCase)))
{
continue;
}
//check for ignore property attribute
var ignoreInfo = (IgnoreColumnAttribute)property.GetCustomAttribute(typeof(IgnoreColumnAttribute));
var column = (ColumnAttribute)property.GetCustomAttribute(typeof(ColumnAttribute));
if (column == null)
{
column = new ColumnAttribute();
}
if (property.Name == "AuditTrailId")
{
column.Name = Config.VegaConfig.AuditKeyColumnName;
}
else if (property.Name == "OperationType")
{
column.Name = Config.VegaConfig.AuditOperationTypeColumnName;
}
else if (property.Name == "TableName")
{
column.Name = Config.VegaConfig.AuditTableNameColumnName;
}
else if (property.Name == "RecordId")
{
column.Name = Config.VegaConfig.AuditRecordIdColumnName;
}
else if (property.Name == "Details")
{
column.Name = Config.VegaConfig.AuditDetailsColumnName;
}
else if (property.Name == "RecordVersionNo")
{
column.Name = Config.VegaConfig.AuditRecordVersionColumnName;
}
else if (property.Name.Equals("CreatedBy", StringComparison.OrdinalIgnoreCase))
{
column.Name = Config.VegaConfig.CreatedByColumnName;
}
else if (property.Name.Equals("CreatedOn"))
{
column.Name = Config.VegaConfig.CreatedOnColumnName;
}
else
{
column.Name = property.Name;
}
if (!column.IsColumnDbTypeDefined)
{
if (column.Name.Equals(Config.VegaConfig.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase))
{
column.ColumnDbType = Config.VegaConfig.CreatedUpdatedByColumnType;
}
else if (property.PropertyType.IsEnum)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType.GetEnumUnderlyingType()];
}
else if (property.PropertyType.IsValueType)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType];
}
else
{
TypeCache.TypeToDbType.TryGetValue(property.PropertyType, out DbType columnDbType);
column.ColumnDbType = columnDbType;
}
}
column.Property = property;
column.SetMethod = property.GetSetMethod();
column.GetMethod = property.GetGetMethod();
column.SetAction = Helper.CreateSetProperty(type, property.Name);
column.GetAction = Helper.CreateGetProperty(type, property.Name);
column.IgnoreInfo = ignoreInfo ?? new IgnoreColumnAttribute(false);
if (result.NoCreatedBy && (column.Name.Equals(Config.VegaConfig.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.CreatedByNameColumnName, StringComparison.OrdinalIgnoreCase)))
{
continue;
}
else if (result.NoCreatedOn && column.Name.Equals(Config.VegaConfig.CreatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoUpdatedBy && ((column.Name.Equals(Config.VegaConfig.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.UpdatedByNameColumnName, StringComparison.OrdinalIgnoreCase))))
{
continue;
}
else if (result.NoUpdatedOn && column.Name.Equals(Config.VegaConfig.UpdatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoIsActive && column.Name.Equals(Config.VegaConfig.IsActiveColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoVersionNo && column.Name.Equals(Config.VegaConfig.VersionNoColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else
{
if (!column.IgnoreInfo.Insert)
{
result.DefaultInsertColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Update)
{
result.DefaultUpdateColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Read)
{
result.DefaultReadColumns.Add(column.Name);
}
result.Columns[column.Name] = column;
}
}
return(result);
}
internal static TableAttribute Get(Type entity)
{
TableAttribute result;
try
{
cacheLock.EnterReadLock();
if (Entities.TryGetValue(entity, out result))
{
return(result);
}
}
finally
{
cacheLock.ExitReadLock();
}
//AuditTrial table attribute is configured based on configuration
if (entity == typeof(AuditTrial))
{
result = GetAuditTrialTableAttribute();
try
{
cacheLock.EnterWriteLock();
Entities[entity] = result;
}
finally
{
cacheLock.ExitWriteLock();
}
return(result);
}
result = (TableAttribute)entity.GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
if (result == null)
{
result = new TableAttribute
{
Name = entity.Name, //assuming entity class name is table name
NeedsHistory = Config.VegaConfig.NeedsHistory,
NoCreatedBy = Config.VegaConfig.NoCreatedBy,
NoCreatedOn = Config.VegaConfig.NoCreatedOn,
NoUpdatedBy = Config.VegaConfig.NoUpdatedBy,
NoUpdatedOn = Config.VegaConfig.NoUpdatedOn,
NoVersionNo = Config.VegaConfig.NoVersionNo,
NoIsActive = Config.VegaConfig.NoIsActive
};
}
if (string.IsNullOrEmpty(result.Name))
{
result.Name = entity.Name;
}
//find all properties
var properties = entity.GetProperties(BindingFlags.Public | BindingFlags.Instance);
//find primary key column attribute
var primaryKeyProperty = properties.FirstOrDefault(p => p.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Count() == 1);
ColumnAttribute primaryKeyColumn = null;
if (primaryKeyProperty != null)
{
result.PrimaryKeyAttribute = (PrimaryKeyAttribute)primaryKeyProperty.GetCustomAttributes(typeof(PrimaryKeyAttribute)).FirstOrDefault();
//find column attribute on this property
primaryKeyColumn = (ColumnAttribute)primaryKeyProperty.GetCustomAttributes(typeof(ColumnAttribute)).FirstOrDefault();
if (primaryKeyColumn == null)
{
//if no column attribute defined assume Propertyname is key column name
primaryKeyColumn = new ColumnAttribute
{
Name = primaryKeyProperty.Name,
ColumnDbType = TypeCache.TypeToDbType[primaryKeyProperty.PropertyType]
};
}
primaryKeyColumn.Property = primaryKeyProperty;
primaryKeyColumn.SetMethod = primaryKeyProperty.GetSetMethod();
primaryKeyColumn.GetMethod = primaryKeyProperty.GetGetMethod();
primaryKeyColumn.SetAction = Helper.CreateSetProperty(entity, primaryKeyProperty.Name);
primaryKeyColumn.GetAction = Helper.CreateGetProperty(entity, primaryKeyProperty.Name);
result.PrimaryKeyColumn = primaryKeyColumn;
//check for virtual foreign key
var virtualForeignKeys = (IEnumerable <ForeignKey>)primaryKeyProperty.GetCustomAttributes(typeof(ForeignKey));
if (virtualForeignKeys != null && virtualForeignKeys.Count() > 0)
{
if (result.VirtualForeignKeys == null)
{
result.VirtualForeignKeys = new List <ForeignKey>();
}
result.VirtualForeignKeys.AddRange(virtualForeignKeys);
}
}
foreach (PropertyInfo property in properties)
{
//TODO: check for valid property types to be added in list
if ((property.Name.Equals("keyid", StringComparison.OrdinalIgnoreCase) ||
property.Name.Equals("operation", StringComparison.OrdinalIgnoreCase)))
{
continue;
}
//check for ignore property attribute
var ignoreInfo = (IgnoreColumnAttribute)property.GetCustomAttribute(typeof(IgnoreColumnAttribute));
var column = (ColumnAttribute)property.GetCustomAttribute(typeof(ColumnAttribute));
if (column == null)
{
column = new ColumnAttribute();
}
if (string.IsNullOrEmpty(column.Name))
{
column.Name = property.Name;
}
if (property.Name.Equals("CreatedBy", StringComparison.OrdinalIgnoreCase))
{
column.Name = Config.VegaConfig.CreatedByColumnName;
}
else if (property.Name.Equals("CreatedByName"))
{
column.Name = Config.VegaConfig.CreatedByNameColumnName;
}
else if (property.Name.Equals("CreatedOn"))
{
column.Name = Config.VegaConfig.CreatedOnColumnName;
}
else if (property.Name.Equals("UpdatedBy"))
{
column.Name = Config.VegaConfig.UpdatedByColumnName;
}
else if (property.Name.Equals("UpdatedByName"))
{
column.Name = Config.VegaConfig.UpdatedByNameColumnName;
}
else if (property.Name.Equals("UpdatedOn"))
{
column.Name = Config.VegaConfig.UpdatedOnColumnName;
}
else if (property.Name.Equals("VersionNo"))
{
column.Name = Config.VegaConfig.VersionNoColumnName;
}
else if (property.Name.Equals("IsActive"))
{
column.Name = Config.VegaConfig.IsActiveColumnName;
}
if (!column.IsColumnDbTypeDefined)
{
if (column.Name.Equals(Config.VegaConfig.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase))
{
column.ColumnDbType = Config.VegaConfig.CreatedUpdatedByColumnType;
}
else if (property.PropertyType.IsEnum)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType.GetEnumUnderlyingType()];
}
else if (property.PropertyType.IsValueType)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType];
}
else
{
TypeCache.TypeToDbType.TryGetValue(property.PropertyType, out DbType columnDbType);
column.ColumnDbType = columnDbType;
}
}
column.Property = property;
column.SetMethod = property.GetSetMethod();
column.GetMethod = property.GetGetMethod();
column.SetAction = Helper.CreateSetProperty(entity, property.Name);
column.GetAction = Helper.CreateGetProperty(entity, property.Name);
column.IgnoreInfo = ignoreInfo ?? new IgnoreColumnAttribute(false);
if (result.NoCreatedBy && (column.Name.Equals(Config.VegaConfig.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.CreatedByNameColumnName, StringComparison.OrdinalIgnoreCase)))
{
continue;
}
else if (result.NoCreatedOn && column.Name.Equals(Config.VegaConfig.CreatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoUpdatedBy && ((column.Name.Equals(Config.VegaConfig.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.UpdatedByNameColumnName, StringComparison.OrdinalIgnoreCase))))
{
continue;
}
else if (result.NoUpdatedOn && column.Name.Equals(Config.VegaConfig.UpdatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoIsActive && column.Name.Equals(Config.VegaConfig.IsActiveColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoVersionNo && column.Name.Equals(Config.VegaConfig.VersionNoColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else
{
if (!column.IgnoreInfo.Insert)
{
result.DefaultInsertColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Update)
{
result.DefaultUpdateColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Read)
{
result.DefaultReadColumns.Add(column.Name);
}
result.Columns[column.Name] = column;
}
}
try
{
cacheLock.EnterWriteLock();
Entities[entity] = result;
}
finally
{
cacheLock.ExitWriteLock();
}
return(result);
}
public override string CreateTableQuery(Type entity)
{
TableAttribute tableInfo = EntityCache.Get(entity);
//check if composite primary key and have identity
//sqlite doesn't support composite primary key where one is autoincrement
//have to create unique index in such case
bool isCompositeKey = tableInfo.PkColumnList.Count > 1;
bool isCompositeKeyAndIdentity = tableInfo.PkColumnList.Count > 1 && tableInfo.PkColumnList.Exists(p => p.PrimaryKeyInfo.IsIdentity);
StringBuilder createSQL = new StringBuilder($"CREATE TABLE {tableInfo.FullName} (");
string primaryKeyCols = string.Empty;
for (int i = 0; i < tableInfo.Columns.Count; i++)
{
ColumnAttribute col = tableInfo.Columns.ElementAt(i).Value;
if (col.IsPrimaryKey)
{
primaryKeyCols += col.Name + ",";
if (col.PrimaryKeyInfo.IsIdentity)
{
createSQL.Append($"{col.Name} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT");
}
else if (!isCompositeKey)
{
createSQL.Append($"{col.Name} {DbTypeString[col.ColumnDbType]} NOT NULL PRIMARY KEY");
}
else
{
createSQL.Append($"{col.Name} {DbTypeString[col.ColumnDbType]} NOT NULL");
}
createSQL.Append(",");
}
else if (col.IgnoreInfo.Insert && col.IgnoreInfo.Update)
{
continue;
}
else
{
createSQL.Append($"{col.Name} {GetDBTypeWithSize(col.ColumnDbType, col.Size, col.NumericScale)}");
if (IsNullableType(col.Property.PropertyType))
{
createSQL.Append(" NULL ");
}
if (col.Name == Config.CREATEDON_COLUMN.Name || col.Name == Config.UPDATEDON_COLUMN.Name)
{
createSQL.Append($" DEFAULT ({CURRENTDATETIMESQL})");
}
createSQL.Append(",");
}
}
//SQLite doesn't support composite primary key with autoincrement
//https://stackoverflow.com/questions/6154730/sqlite-multi-primary-key-on-a-table-one-of-them-is-auto-increment
if (isCompositeKey && !isCompositeKeyAndIdentity)
{
primaryKeyCols = primaryKeyCols.RemoveLastComma();
createSQL.Append($"PRIMARY KEY ({primaryKeyCols})");
}
createSQL.RemoveLastComma(); //Remove last comma if exists
createSQL.Append(");");
//create unique index for compositekey with identity
if (isCompositeKey && isCompositeKeyAndIdentity)
{
primaryKeyCols = primaryKeyCols.RemoveLastComma();
createSQL.Append($"CREATE UNIQUE INDEX pk_{tableInfo.Name} ON {tableInfo.Name}({primaryKeyCols});");
}
return(createSQL.ToString());
}