public CClass Convert(CTableType tableType)
{
if (string.IsNullOrEmpty(tableType.TableName))
{
throw new Exception("cannot create CClass without a class name");
}
var @class = new CClass(tableType.TableName);
@class.Namespace = new CNamespace {
NamespaceName = tableType.Schema.SchemaName
};
@class.NamespaceRef.Add(new CNamespaceRef("System"));
if (!string.IsNullOrEmpty(tableType.TableName))
{
foreach (var column in tableType.Column)
{
var prop = new CProperty();
prop.PropertyName = column.ColumnName;
prop.Type = column.ColumnType.ToClrTypeName();
prop.MaxLength = column.ColumnLength;
@class.Property.Add(prop);
}
}
return(@class);
}
public string Convert(CTableType tableType)
{
var codeWriter = new CodeWriter();
codeWriter.WriteLine($@"DROP TYPE IF EXISTS {tableType.Schema.SchemaName.WrapReservedAndSnakeCase( DataStoreTypes.Postgres, tableType.ConvertToSnakeCase)}.{tableType.TableName.WrapReservedAndSnakeCase( DataStoreTypes.Postgres, tableType.ConvertToSnakeCase)} /* CASCADE */;");
codeWriter.WriteLine();
codeWriter.WriteLine($@"CREATE TYPE {tableType.Schema.SchemaName.WrapReservedAndSnakeCase(DataStoreTypes.Postgres, tableType.ConvertToSnakeCase)}.{tableType.TableName.WrapReservedAndSnakeCase(DataStoreTypes.Postgres, tableType.ConvertToSnakeCase)} AS (");
codeWriter.Indent();
var first = true;
foreach (var col in tableType.Column)
{
if (!first)
{
codeWriter.WriteLine(",");
}
first = false;
codeWriter.Write($"{col.ColumnName.WrapReservedAndSnakeCase(DataStoreTypes.Postgres, tableType.ConvertToSnakeCase)}");
codeWriter.Write($" {SqlMapper.NpgsqlDbTypeToPostgres(SqlMapper.DbTypeToNpgsqlDbType(col.ColumnType))}");
if (col.DoesNeedLength())
{
codeWriter.Write($"({col.ColumnLength})");
}
}
codeWriter.Unindent();
codeWriter.WriteLine(");");
return(codeWriter.ToString());
}
protected CClass GetTableTypeDto(CTableType tableType, string tableName)
{
if (string.IsNullOrEmpty(tableName))
{
return(null);
}
var converter = new CTableTypeToCClassConverter();
var @class = converter.Convert(tableType);
@class.DerivedFrom = tableType;
//overrite the default namespace logic
@class.Namespace = new CNamespace
{
NamespaceName =
$"{_dataLayerKProject.CompanyName}.{_dataLayerKProject.ProjectName}{_dataLayerKProject.NamespaceSuffix}.Data.Entities"
};
return(@class);
}
public string GetFlywayFileName(CTableType tableType)
{
var fileName = $"{FlywayVersionNumber}__Create_Type_{tableType.Schema.SchemaName}_{tableType.TableName}.sql";
return(fileName);
}
private void InferTableType(IList <CTableType> tableTypes, CProtoRpc rpc, CProtoMessage messageIn)
{
var tableType = new CTableType(DataStoreTypes.SqlServer);
tableType.Schema = InferSchema(rpc.ProtoService);
tableType.TableName = "tt" + InferTableName(messageIn);
tableType.DerivedFrom = messageIn;
//add PK
tableType.Column.Add(new CColumn(tableType)
{
ColumnName = $"{tableType.TableName}Id", IsNullable = false, ColumnTypeRaw = "bigint", ColumnType = System.Data.DbType.Int64, IsPrimaryKey = true, IsIdentity = false
});
foreach (var p in messageIn.ProtoField)
{
var parameter = p;
if (parameter.FieldType == GrpcType.__message)
{
//if the inner message is just a single scalar field, lets use that
var m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm => pm.MessageName == parameter.MessageType);
if (m.ProtoField.Count == 1 && !parameter.Repeated)
{
parameter = m.ProtoField.First();
}
else if (m.ProtoField.Count > 1)
{
continue;
}
else
{
continue;
}
}
var columns = InferColumns(tableType, rpc, parameter);
foreach (var col in columns)
{
if (!tableType.ColumnExists(col))
{
tableType.Column.Add(col);
}
}
}
var existingTable = tableTypes.FirstOrDefault(t => t.TableName == tableType.TableName);
if (existingTable != null)
{
//merge the tableTypes
foreach (var col in tableType.Column)
{
if (!existingTable.ColumnExists(col))
{
existingTable.Column.Add(col);
}
}
}
else
{
tableTypes.Add(tableType);
}
//now that tables has been added, add children
//this will attempt to make sure they are in correct order for FK constraints
foreach (var p in messageIn.ProtoField)
{
if (p.FieldType == GrpcType.__message && p.Repeated)
{
//if the inner message is just a single scalar field, lets use that
var m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm =>
pm.MessageName == p.MessageType);
// if (m.ProtoField.Count > 1) //its repeated, so field count doesn't matter
{
//recurse
InferTableType(tableTypes, rpc, m);
}
}
}
}
public string Convert(CTableType tableType)
{
string[] parts = { tableType.Schema.SchemaName, tableType.TableName };
var createTypeTable = new CreateTypeTableStatement();
///set schema and table name
createTypeTable.Name = new SchemaObjectName();
createTypeTable.Name.Identifiers.Add(new Identifier {
Value = tableType.Schema.SchemaName
});
createTypeTable.Name.Identifiers.Add(new Identifier {
Value = tableType.TableName
});
//add columns
createTypeTable.Definition = new TableDefinition();
foreach (var col in tableType.Column)
{
var dataType = new SqlDataTypeReference
{
SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(col.ColumnTypeRaw)
};
if (DoesNeedLength(col.ColumnSqlDbType))
{
if (col.ColumnLength > 0)
{
dataType.Parameters.Add(new IntegerLiteral {
Value = col.ColumnLength.ToString()
});
}
}
var column = new ColumnDefinition
{
ColumnIdentifier = new Identifier {
Value = col.ColumnName
},
DataType = dataType
};
if (col.IsIdentity)
{
column.IdentityOptions = new IdentityOptions
{
IdentitySeed = new IntegerLiteral {
Value = "1000"
},
IdentityIncrement = new IntegerLiteral {
Value = "1"
}
}
}
;
column.Constraints.Add(new NullableConstraintDefinition {
Nullable = col.IsNullable
});
if (col.IsUnique)
{
column.Constraints.Add(new UniqueConstraintDefinition());
}
if (col.IsIndexed)
{
column.Index = new IndexDefinition
{
Name = new Identifier {
Value = $"IX_{col.ColumnName}"
},
IndexType = new IndexType
{
IndexTypeKind = IndexTypeKind.NonClustered
}
}
}
;
createTypeTable.Definition.ColumnDefinitions.Add(column);
}
/*
* //generate DDL
* var script = new TSqlScript();
* var batch = new TSqlBatch();
* script.Batches.Add(batch);
* batch.Statements.Add(createTypeTable);
* var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
* dacpacModel.AddObjects(script);
* var existing = dacpacModel.GetObject(Table.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
* return existing.GetScript();
*/
string scriptOut;
var scriptGen = new Sql120ScriptGenerator();
scriptGen.GenerateScript(createTypeTable, out scriptOut);
return(scriptOut);
}
public CTableType Read(string schema, string tableTypeName, string tableTypeText)
{
var tableType = new CTableType(DataStoreTypes.SqlServer);
tableType.Schema = new CSchema {
SchemaName = schema
};
tableType.TableName = tableTypeName;
tableType.TableTypeBody = GetTableTypeBody(tableTypeText);
using (var sqlConnection = new SqlConnection(ConnectionString))
{
var sqlViewQuery =
$@"select
c.name AS column_name,
st.name AS data_type,
c.is_nullable,
c.max_length as character_maximum_length
,s.name
from sys.table_types tt
inner join sys.columns c on c.object_id = tt.type_table_object_id
--INNER JOIN sys.systypes AS ST ON ST.xtype = c.system_type_id
INNER JOIN sys.systypes AS ST ON ST.xusertype = c.user_type_id
join sys.schemas s on s.schema_id = tt.schema_id
WHERE tt.name= '{tableTypeName}' and s.name = '{tableType.Schema.SchemaName}'";
sqlConnection.Open();
var sqlCommand = new SqlCommand(sqlViewQuery, sqlConnection);
var dataReader = sqlCommand.ExecuteReader();
var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn {
ColumnName = "column_name"
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "data_type"
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "is_nullable"
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "character_maximum_length", DataType = typeof(int)
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "tabletype_name"
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "tabletype_schema"
});
while (dataReader.Read())
{
var dataRow = dataTable.NewRow();
dataRow["column_name"] = dataReader["column_name"];
dataRow["data_type"] = dataReader["data_type"];
dataRow["is_nullable"] = dataReader["is_nullable"];
dataRow["character_maximum_length"] = dataReader["character_maximum_length"];
dataRow["tabletype_name"] = tableTypeName;
dataRow["tabletype_schema"] = schema;
dataTable.Rows.Add(dataRow);
}
foreach (var row2 in dataTable.Rows)
{
var row = row2 as DataRow;
var column = new CColumn(tableType)
{
ColumnName = (string)row[0],
ColumnTypeRaw = (string)row[1],
ColumnSqlDbType = SqlMapper.ParseValueAsSqlDbType((string)row[1]),
ColumnType = SqlMapper.GetDbType((string)row[1]),
ColumnLength = row[3] != DBNull.Value ? (int)row[3] : -1
};
tableType.Column.Add(column);
}
return(tableType);
}
}
