public override async Task <ConvertDDLResponse> ConvertDDL(ConvertDDLRequest request, ServerCallContext context)
{
var sw = Stopwatch.StartNew();
GrpcEnvironment.Logger.Info($@"Processing ConvertDDL Request");
var databaseProject = new KDataStoreProject()
{
KickstartCRUDStoredProcedures = false, ConvertToSnakeCase = request.ConvertToSnakeCase,
DataStoreType = Utility.DataStoreTypes.Postgres,
SqlTableText = request.UnconvertedTableDDL,
SqlTableTypeText = request.UnconvertedTableTypeDDL,
SqlStoredProcedureText = request.UnconvertedStoredProcedureDDL
};
var connectionString = "Server=localhost;";
var outputRootPath = string.Empty;
_dbToKSolutionConverter.BuildSqlMeta(connectionString, outputRootPath, databaseProject);
var response = new ConvertDDLResponse();
ICSchemaToSchemaConverter schemaConverter;
ICTableToTableConverter tableConverter;
ICTableTypeToTableTypeConverter tableTypeConverter;
ICStoredProcedureToStoredProcedureConverter storedProcConverter;
if (request.DatabaseType == Types.DatabaseTypes.Postgres)
{
schemaConverter = new CSchemaToPostgresSchemaConverter();
tableConverter = new CTableToPostgresTableConverter();
tableTypeConverter = new CTableTypeToPostgresTableTypeConverter();
storedProcConverter = new CStoredProcedureToPostgresStoredProcedureConverter();
}
else if (request.DatabaseType == Types.DatabaseTypes.SqlServer)
{
schemaConverter = new CSchemaToSqlServerSchemaConverter();
tableConverter = new CTableToSqlServerTableConverter();
tableTypeConverter = new CTableTypeToSqlServerTableTypeConverter();
storedProcConverter = new CStoredProcedureToSSqlServerStoredProcedureConverter();
}
else
{
throw new NotImplementedException();
}
var codeWriter = new CodeWriter();
var codeWriterTableType = new CodeWriter();
var codeWriterStoredProc = new CodeWriter();
using (var memoryStream = new MemoryStream( ))
{
using (var zipFile = new ZipArchive(memoryStream, ZipArchiveMode.Create))
{
foreach (var schema in databaseProject.GetAllGeneratedSchemas())
{
if (request.DatabaseType == Types.DatabaseTypes.SqlServer && schema.SchemaName.ToLower() == "dbo")
{
continue;// dbo already exists on Sql Server
}
var ddl = schemaConverter.Convert(schema);
var fileName = _flywayFileNameService.GetFlywayFileName(schema);
//fileWriter.WriteFile(fileName, ddl);
var newEntry = zipFile.CreateEntry(Path.Combine("migrations", fileName));
using (var writer = new StreamWriter(newEntry.Open()))
{
writer.Write(ddl);
}
}
foreach (var table in databaseProject.Table)
{
var ddl = tableConverter.Convert(table.GeneratedTable);
codeWriter.WriteLine(ddl);
codeWriter.WriteLine();
var fileName = _flywayFileNameService.GetFlywayFileName(table.GeneratedTable);
//fileWriter.WriteFile(fileName, ddl);
var newEntry = zipFile.CreateEntry(Path.Combine("migrations", fileName));
using (var writer = new StreamWriter(newEntry.Open()))
{
writer.Write(ddl);
}
}
{
var rules = new CRules();
foreach (var table in databaseProject.Table)
{
var rList = _cTableToDmsRuleConverter.Convert(table.GeneratedTable);
rules.Rule.AddRange(rList);
}
var json = rules.ToJson();
var fileName = "DmsRules.json";
var newEntry = zipFile.CreateEntry(fileName);
using (var writer = new StreamWriter(newEntry.Open()))
{
writer.Write(json);
}
response.ConvertedDmsJson = json;
}
foreach (var tableType in databaseProject.TableType)
{
var ddl = tableTypeConverter.Convert(tableType.GeneratedTableType);
codeWriterTableType.WriteLine(ddl);
codeWriterTableType.WriteLine();
var fileName = _flywayFileNameService.GetFlywayFileName(tableType.GeneratedTableType);
//fileWriter.WriteFile(fileName, ddl);
var newEntry = zipFile.CreateEntry(Path.Combine("migrations", fileName));
using (var writer = new StreamWriter(newEntry.Open()))
{
writer.Write(ddl);
}
}
foreach (var storedProc in databaseProject.StoredProcedure)
{
var ddl = storedProcConverter.Convert(storedProc.GeneratedStoredProcedure);
codeWriterStoredProc.WriteLine(ddl);
codeWriter.WriteLine();
var fileName = _flywayFileNameService.GetFlywayFileName(storedProc.GeneratedStoredProcedure);
//fileWriter.WriteFile(fileName, ddl);
var newEntry = zipFile.CreateEntry(Path.Combine("migrations", fileName));
using (var writer = new StreamWriter(newEntry.Open()))
{
writer.Write(ddl);
}
}
}
response.ZipAsBase64 = Convert.ToBase64String(memoryStream.ToArray());
}
response.ConvertedTableDDL = codeWriter.ToString();
response.ConvertedTableTypeDDL = codeWriterTableType.ToString();
response.ConvertedStoredProcedureDDL = codeWriterStoredProc.ToString();
sw.Stop();
GrpcEnvironment.Logger.Info($"Processed request in {sw.ElapsedMilliseconds} ms");
return(response);
}
public override void Visit(CPart part)
{
if (part is CTableType)
{
var tableType = part as CTableType;
//var ddl = (part as STableType).TableTypeBody;
if (tableType.DatabaseType == Utility.DataStoreTypes.SqlServer)
{
var converter = new CTableTypeToSqlServerTableTypeConverter();
var ddl = converter.Convert(part as CTableType);
_codeWriter.Write(ddl);
}
else if (tableType.DatabaseType == Utility.DataStoreTypes.Postgres)
{
var converter = new CTableTypeToPostgresTableTypeConverter();
var ddl = converter.Convert(part as CTableType);
_codeWriter.Write(ddl);
}
else
{
throw new NotImplementedException();
}
}
else if (part is CTable)
{
var table = part as CTable;
if (table.DatabaseType == Utility.DataStoreTypes.SqlServer)
{
if (!string.IsNullOrEmpty(table.TableText))
{
_codeWriter.Write(table.TableText);
}
else
{
var converter = new CTableToSqlServerTableConverter();
var ddl = converter.Convert(table);
_codeWriter.Write(ddl);
}
}
else if (table.DatabaseType == Utility.DataStoreTypes.Postgres)
{
var converter = new CTableToPostgresTableConverter();
var ddl = converter.Convert(table);
_codeWriter.Write(ddl);
}
else if (table.DatabaseType == Utility.DataStoreTypes.MySql)
{
var converter = new CTableToMySqlTableConverter();
var ddl = converter.Convert(table);
_codeWriter.Write(ddl);
}
else
{
throw new NotImplementedException();
}
}
else if (part is CView)
{
var ddl = (part as CView).ViewText;
_codeWriter.Write(ddl);
}
else if (part is CSchema)
{
var schema = part as CSchema;
if (schema.DatabaseType == Utility.DataStoreTypes.SqlServer)
{
var converter = new CSchemaToSqlServerSchemaConverter();
var ddl = converter.Convert(schema);
_codeWriter.Write(ddl);
}
else if (schema.DatabaseType == Utility.DataStoreTypes.Postgres)
{
var converter = new CSchemaToPostgresSchemaConverter();
var ddl = converter.Convert(schema);
_codeWriter.Write(ddl);
}
else if (schema.DatabaseType == Utility.DataStoreTypes.MySql)
{
var converter = new CSchemaToMySqlSchemaConverter();
var ddl = converter.Convert(schema);
_codeWriter.Write(ddl);
}
else
{
throw new NotImplementedException();
}
}
else if (part is CStoredProcedure)
{
var storedProc = part as CStoredProcedure;
ICStoredProcedureToStoredProcedureConverter converter = null;
if (storedProc.DatabaseType == Utility.DataStoreTypes.SqlServer)
{
converter = new CStoredProcedureToSSqlServerStoredProcedureConverter();
}
else if (storedProc.DatabaseType == Utility.DataStoreTypes.Postgres)
{
converter = new CStoredProcedureToPostgresStoredProcedureConverter();
}
else
{
throw new NotImplementedException();
}
var ddl = converter.Convert(storedProc);
_codeWriter.Write(ddl);
}
else if (part is CRules)
{
var rules = part as CRules;
var json = rules.ToJson();
_codeWriter.Write(json);
}
else if (part is CSeedScript)
{
var seedScript = part as CSeedScript;
_codeWriter.Write(seedScript.SeedScriptBody);
}
else if (part is CProtoFile)
{
var protoFile = part as CProtoFile;
if (!string.IsNullOrEmpty(protoFile.SourceProtoText))
{
//source may have full comments
_codeWriter.Write(protoFile.SourceProtoText);
}
else
{
var converter = new SProtoFileToProtoFileConverter();
_codeWriter.Write(converter.Convert(protoFile));
}
}
else if (part is CProtoFileRef)
{
var protoFileRef = part as CProtoFileRef;
var converter = new SProtoFileToProtoFileConverter();
_codeWriter.Write(converter.Convert(protoFileRef.ProtoFile));
}
else if (part is CText)
{
var textFile = part as CText;
_codeWriter.Write(textFile.Text);
}
else if (part is CBatchFile)
{
var batchFile = part as CBatchFile;
_codeWriter.Write(batchFile.BatchFileContent);
}
else
{
base.Visit(part);
}
}
private string InferStoredProcedures(KProtoFile kProtoFile)
{
var codeWriter = new CodeWriter();
var convert = new CStoredProcedureToSSqlServerStoredProcedureConverter();
foreach (var service in kProtoFile.GeneratedProtoFile.ProtoService)
{
foreach (var rpc in service.Rpc)
{
var request = rpc.GetInnerMessageOrRequest();
//if (!request.ProtoField.Any(f=>f.FieldType != GrpcType.__message))
// continue;
var storedProc = new CStoredProcedure(DataStoreTypes.Unknown)
{
DerivedFrom = request, // rpc,
Schema = InferSchema(service),
StoredProcedureName = rpc.RpcName,
ResultSetName = rpc.Response.MessageName + "Dto"//"ResultSet"
};
foreach (var pp in request.ProtoField)
{
var parameter = pp;
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 = m.ProtoField.First();
/*
* if (parameter.FieldType == GrpcType.__message)
* {
* m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm =>
* pm.MessageName == parameter.MessageType);
* if (m.ProtoField.Count == 1)
* {
* parameter = m.ProtoField.First();
*
* }
* else if (m.ProtoField.Count > 1)
* {
* Debugger.Break();
*
* }
* }*/
}
else if (m.ProtoField.Count > 1)
{
/*
* //todo: we need to pass table valued parameters to query
* Debugger.Break();
* parameter = m.ProtoField.First();
*
* if (parameter.FieldType == GrpcType.__message)
* {
* m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm =>
* pm.MessageName == parameter.MessageType);
* if (m.ProtoField.Count == 1)
* {
* parameter = m.ProtoField.First();
*
* }
* else if (m.ProtoField.Count > 1)
* {
* //todo: we need to pass table valued parameters to query
* Debugger.Break();
* parameter = m.ProtoField.First();
*
* }
* }
*/
}
}
var p = InferStoredProcedureParameter(parameter);
if (!storedProc.Parameter.Exists(p2 => p2.ParameterName == p.ParameterName))
{
storedProc.Parameter.Add(p);
}
}
var tables = new List <CTable>();
var request2 = rpc.GetInnerMessageOrRequest();
InferTable(tables, rpc, request2, null);
var table = tables.First();
if (rpc.OperationIs.HasFlag(COperationIs.Get) ||
rpc.OperationIs.HasFlag(COperationIs.Find) ||
rpc.OperationIs.HasFlag(COperationIs.List) ||
rpc.OperationIs.HasFlag(COperationIs.Read) ||
rpc.OperationIs.HasFlag(COperationIs.Dequeue) ||
rpc.OperationIs.HasFlag(COperationIs.Check)
)
{
storedProc.StoredProcedureBody = $@"SELECT {table.ColumnAsColumnList()} FROM {table.Schema.SchemaName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, false)}.{table.TableName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, false)}";
}
else if (rpc.OperationIs.HasFlag(COperationIs.Set) ||
rpc.OperationIs.HasFlag(COperationIs.Update) ||
rpc.OperationIs.HasFlag(COperationIs.Create))
{
storedProc.StoredProcedureBody = $@"PRINT 'todo: UPDATE {table.Schema.SchemaName}.{table.TableName} SET ....'";
}
codeWriter.WriteLine(convert.Convert(storedProc));
codeWriter.WriteLine();
codeWriter.WriteLine("GO");
codeWriter.WriteLine();
this.StoredProcedure.Add(new KStoredProcedure
{
StoredProcedureName = storedProc.StoredProcedureName,
Schema = storedProc.Schema.SchemaName,
ReturnsMultipleRows = storedProc.ReturnsMultipleRows,
ResultSetName = storedProc.ResultSetName,
ParameterSetName = storedProc.ParameterSetName,
DerivedFrom = storedProc.DerivedFrom,
GeneratedStoredProcedure = storedProc
});
}
}
return(codeWriter.ToString());
}
