/*
*
* CREATE DATABASE sandbox;
* CREATE SCHEMA dappercustomtypestuff;
*
* CREATE SEQUENCE dappercustomtypestuff.location_id_seq
* AS INTEGER
* MAXVALUE 2147483647;
*
* CREATE SEQUENCE dappercustomtypestuff.location_active_id_seq
* AS INTEGER
* MAXVALUE 2147483647;
*
* CREATE TYPE dappercustomtypestuff.ID_DATE AS
* (
* id INTEGER,
* date TIMESTAMP
* );
*
* CREATE TABLE dappercustomtypestuff.location
* (
* id SERIAL NOT NULL
* CONSTRAINT location_pkey
* PRIMARY KEY,
* name TEXT NOT NULL
* );
*
* CREATE TABLE dappercustomtypestuff.location_active
* (
* id SERIAL NOT NULL
* CONSTRAINT location_active_pkey
* PRIMARY KEY,
* locationid INTEGER
* CONSTRAINT fk_location_active_location_id
* REFERENCES location,
* start TIMESTAMP,
* "end" TIMESTAMP
* );
*
* insert into location (name) values ('location 1');
* insert into location (name) values ('location 2');
* insert into location (name) values ('location 3');
*
* insert into location_active (locationid, start, "end") values (1, '2018-01-01', '2018-01-15');
* insert into location_active (locationid, start, "end") values (1, '2018-01-20', '2018-01-25');
* insert into location_active (locationid, start, "end") values (2, '2018-01-01', '2018-01-05');
* insert into location_active (locationid, start, "end") values (3, '2018-02-01', '2018-02-10');
*
*/
public SomePostgresCustomTypeStuff() : base("sandbox", "dappercustomtypestuff")
{
var npgsqlSnakeCaseNameTranslator = new NpgsqlSnakeCaseNameTranslator();
SqlMapper.AddTypeHandler(new IdDateTypeHandler());
NpgsqlConnection.GlobalTypeMapper.MapComposite <IdDate>("dappercustomtypestuff.id_date", npgsqlSnakeCaseNameTranslator);
}
public static ModelBuilder UseSnakeCaseNamingConvention(this ModelBuilder modelBuilder)
{
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
entity.SetTableName(NpgsqlSnakeCaseNameTranslator.ConvertToSnakeCase(entity.ClrType.Name));
foreach (var property in entity.GetProperties())
{
property.SetColumnName(NpgsqlSnakeCaseNameTranslator.ConvertToSnakeCase(property.GetColumnName()));
}
}
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
foreach (var key in entity.GetKeys())
{
key.SetName(GetDefaultName(key));
}
foreach (var key in entity.GetForeignKeys())
{
key.SetConstraintName(GetDefaultName(key));
}
foreach (var index in entity.GetIndexes())
{
index.SetName(GetDefaultName(index));
}
}
return(modelBuilder);
}
public void FixSnakeCaseNames(ModelBuilder modelBuilder)
{
var mapper = new NpgsqlSnakeCaseNameTranslator();
foreach (var table in modelBuilder.Model.GetEntityTypes())
{
ConvertToSnake(mapper, table);
foreach (var property in table.GetProperties())
{
ConvertToSnake(mapper, property);
if (property.ClrType == typeof(string))
{
property.SetColumnType("varchar(100)");
}
}
foreach (var primaryKey in table.GetKeys())
{
ConvertToSnake(mapper, primaryKey);
}
foreach (var foreignKey in table.GetForeignKeys())
{
ConvertToSnake(mapper, foreignKey);
}
foreach (var indexKey in table.GetIndexes())
{
ConvertToSnake(mapper, indexKey);
}
}
}
private void FixSnakeCaseNames(ModelBuilder modelBuilder)
{
var mapper = new NpgsqlSnakeCaseNameTranslator();
foreach (var table in modelBuilder.Model.GetEntityTypes())
{
ConvertToSnake(mapper, table);
foreach (var property in table.GetProperties())
{
ConvertToSnake(mapper, property);
}
foreach (var primaryKey in table.GetKeys())
{
ConvertToSnake(mapper, primaryKey);
}
foreach (var foreignKey in table.GetForeignKeys())
{
ConvertToSnake(mapper, foreignKey);
}
foreach (var indexKey in table.GetIndexes())
{
ConvertToSnake(mapper, indexKey);
}
}
}
NpgsqlConnection OpenAndMapComposite <T>(T composite, string?schema, string nameSuffix, out string nameQualified)
where T : IComposite
{
var nameTranslator = new NpgsqlSnakeCaseNameTranslator();
var name = nameTranslator.TranslateTypeName(typeof(T).Name + nameSuffix);
if (schema == null)
{
nameQualified = name;
}
else
{
schema = nameTranslator.TranslateTypeName(schema);
nameQualified = schema + "." + name;
}
var connection = OpenConnection();
try
{
connection.ExecuteNonQuery(schema is null ? $"DROP TYPE IF EXISTS {name}" : $"DROP SCHEMA IF EXISTS {schema} CASCADE; CREATE SCHEMA {schema}");
connection.ExecuteNonQuery($"CREATE TYPE {nameQualified} AS ({composite.GetAttributes()})");
connection.ReloadTypes();
connection.TypeMapper.MapComposite <T>(nameQualified, nameTranslator);
return(connection);
}
catch
{
connection.Dispose();
throw;
}
}
public static void ApplyToModelBuilder(DatabaseFacade databaseFacade, ModelBuilder modelBuilder)
{
switch (DbProvider)
{
case DbProvider.SqlServer:
break;
case DbProvider.Sqlite:
default:
var mapper = new NpgsqlSnakeCaseNameTranslator();
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
foreach (var property in entity.GetProperties())
{
var storeObjectIdentifier = StoreObjectIdentifier.Create(property.DeclaringEntityType, StoreObjectType.Table);
if (storeObjectIdentifier.HasValue)
{
property.SetColumnName(mapper.TranslateMemberName(property.GetColumnName(storeObjectIdentifier.Value)));
}
}
entity.SetTableName(mapper.TranslateTypeName(entity.GetTableName()));
}
break;
}
}
public static IQueryable <TEntity> FromView <TEntity>(this DbSet <TEntity> dbSet, string viewName)
where TEntity : class
{
var mapper = new NpgsqlSnakeCaseNameTranslator();
return(dbSet.FromSql($"select * from {mapper.TranslateTypeName(viewName)}"));
}
public void SnakeCaseTest_Many()
{
var testVals = GetTestVals();
foreach (var testVal in testVals)
{
var trans = new NpgsqlSnakeCaseNameTranslator();
Assert.AreEqual(testVal.Item2, testVal.Item1.ToSnakeCase());
}
}
public void SnakeCaseTest_ManyWithNpgsql()
{
var testVals = GetTestVals();
foreach (var testVal in testVals)
{
var trans = new NpgsqlSnakeCaseNameTranslator(false);
Assert.AreEqual(testVal.Item2, trans.TranslateMemberName(testVal.Item1));
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
if (modelBuilder == null)
{
return;
}
var mapper = new NpgsqlSnakeCaseNameTranslator();
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
foreach (var property in entity.GetProperties())
{
modelBuilder.Entity(entity.Name).Property(property.Name).HasColumnName(mapper.TranslateMemberName(property.Name)); //.ToTable(mapper.TranslateMemberName(entity.Name));
//property.Relational().ColumnName = mapper.TranslateMemberName(property.Name);
}
//entity.Relational().TableName = mapper.TranslateMemberName(entity.Relational().TableName);
modelBuilder.Entity(entity.Name).ToTable(mapper.TranslateMemberName(entity.GetTableName()));
}
modelBuilder.Entity <Project>(p =>
{
p.HasOne(p => p.Creator).WithMany(du => du.CreatedProjects);
p.HasOne(p => p.Owner).WithMany(du => du.OwnedProjects);
p.HasIndex(p => new { p.Name, p.CreatorId }).IsUnique();
});
modelBuilder.Entity <PostTag>().Ignore(x => x.Post).HasKey(x => new { x.PostId, x.TagName });
modelBuilder.Entity <ProjectAdmin>(pa =>
{
pa.HasKey(t => new { t.ProjectId, t.AdminId });
pa.HasOne(pa => pa.Project).WithMany(a => a.Admins).HasForeignKey(pa => pa.ProjectId);
pa.HasOne(pa => pa.Admin).WithMany(p => p.AdminedProjects).HasForeignKey(pa => pa.AdminId);
});
modelBuilder.Entity <ProjectMaintainer>(pm =>
{
pm.HasKey(t => new { t.ProjectId, t.MaintainerId });
pm.HasOne(pm => pm.Project).WithMany(a => a.Maintainers).HasForeignKey(pm => pm.ProjectId);
pm.HasOne(pm => pm.Maintainer).WithMany(p => p.MaintainedProjects).HasForeignKey(pm => pm.MaintainerId);
});
modelBuilder.Entity <IssueIssueType>(iit =>
{
iit.HasKey(t => new { t.IssueId, t.IssueTypeId });
iit.HasOne(pi => pi.Issue).WithMany(i => i.Types).HasForeignKey(pi => pi.IssueId);
iit.HasOne(pi => pi.IssueType).WithMany(it => it.Issues).HasForeignKey(pi => pi.IssueTypeId);
});
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
var mapper = new NpgsqlSnakeCaseNameTranslator();
var types = modelBuilder.Model.GetEntityTypes().ToList();
// Refer to tables in snake_case internally
types.ForEach(e => e.Relational().TableName = mapper.TranslateMemberName(e.Relational().TableName));
// Refer to columns in snake_case internally
types.SelectMany(e => e.GetProperties())
.ToList()
.ForEach(p => p.Relational().ColumnName = mapper.TranslateMemberName(p.Relational().ColumnName));
}
public static void PostgresModelCreating(this ModelBuilder builder)
{
var mapper = new NpgsqlSnakeCaseNameTranslator();
foreach (var entity in builder.Model.GetEntityTypes())
{
// modify column names
foreach (var property in entity.GetProperties())
{
property.SetColumnName(mapper.TranslateMemberName(property.GetColumnName(StoreObjectIdentifier.Table(entity.GetTableName(), null))));
}
// modify table name
entity.SetTableName(mapper.TranslateMemberName(entity.GetTableName()));
// modify keys names
foreach (var key in entity.GetKeys())
{
key.SetName(mapper.TranslateMemberName(key.GetName()));
}
// modify foreign keys names
foreach (var key in entity.GetForeignKeys())
{
key.SetConstraintName(mapper.TranslateMemberName(key.GetConstraintName()));
}
// modify indexes names
foreach (var index in entity.GetIndexes())
{
index.SetDatabaseName(mapper.TranslateMemberName(index.GetDatabaseName()));
}
// move asp_net tables into schema 'identity'
if (entity.GetTableName().StartsWith("asp_net_"))
{
entity.SetTableName(entity.GetTableName().Replace("asp_net_", string.Empty));
entity.SetSchema("identity");
}
}
}
public static void ApplyToModelBuilder(DatabaseFacade databaseFacade, ModelBuilder modelBuilder)
{
switch (DbProvider)
{
case DbProvider.SqlServer:
break;
case DbProvider.Sqlite:
default:
var mapper = new NpgsqlSnakeCaseNameTranslator();
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
foreach (var property in entity.GetProperties())
{
property.SetColumnName(mapper.TranslateMemberName(property.GetColumnName()));
}
entity.SetTableName(mapper.TranslateTypeName(entity.GetTableName()));
}
break;
}
}
private void ApplySnakeCaseNames(ModelBuilder modelBuilder)
{
var mapper = new NpgsqlSnakeCaseNameTranslator();
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
// modify column names
foreach (var property in entity.GetProperties())
{
property.SetColumnName(mapper.TranslateMemberName(property.GetColumnName()));
}
// modify table name
entity.SetTableName(mapper.TranslateMemberName(entity.GetTableName()));
// move asp_net tables into schema 'identity'
if (entity.GetTableName().StartsWith("asp_net_"))
{
entity.SetTableName(entity.GetTableName().Replace("asp_net_", string.Empty));
entity.SetSchema("identity");
}
}
}
public static void ApplySnakeCaseNames(this ModelBuilder modelBuilder)
{
var mapper = new NpgsqlSnakeCaseNameTranslator();
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
// modify column names
foreach (var property in entity.GetProperties())
{
property.Relational().ColumnName = mapper.TranslateMemberName(property.Relational().ColumnName);
}
// modify table name
entity.Relational().TableName = mapper.TranslateMemberName(entity.Relational().TableName);
// move asp_net tables into schema 'identity'
if (!entity.Relational().TableName.StartsWith("asp_net_"))
{
continue;
}
entity.Relational().TableName = entity.Relational().TableName.Replace("asp_net_", string.Empty);
entity.Relational().Schema = "identity";
}
}
internal GlobalTypeMapper()
{
Mappings = new Dictionary <string, NpgsqlTypeMapping>();
DefaultNameTranslator = new NpgsqlSnakeCaseNameTranslator();
}
public static string ToSnakeCase(this string input) => NpgsqlSnakeCaseNameTranslator.ConvertToSnakeCase(input);
