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