public void InferSqlFromProto(KProtoFile kProtoFile)
{
SqlTableText = InferTables(kProtoFile);
SqlTableTypeText = InferTableTypes(kProtoFile);
SqlStoredProcedureText = InferStoredProcedures(kProtoFile);
SqlViewText = InferViews(kProtoFile);
InferQueries(kProtoFile);
}
private string InferTables(KProtoFile kProtoFile)
{
var tables = new List <CTable>();
//generate Sql text, it will turn back into a KTable later
var codeWriter = new CodeWriter();
foreach (var rpc in kProtoFile.GeneratedProtoFile.GetRpcListThatGet())
{
var request = rpc.GetInnerMessageOrRequest();
InferTable(tables, rpc, request, null);
var response = rpc.GetInnerMessageOrResponse();
InferTable(tables, rpc, response, null);
}
foreach (var rpc in kProtoFile.GeneratedProtoFile.GetRpcListThatSet())
{
var request = rpc.GetInnerMessageOrRequest();
InferTable(tables, rpc, request, null);
var response = rpc.GetInnerMessageOrResponse();
InferTable(tables, rpc, response, null);
}
var converter = new CTableToSqlServerTableConverter();
foreach (var table in tables)
{
codeWriter.WriteLine(converter.Convert(table));
codeWriter.WriteLine("GO");
codeWriter.WriteLine();
this.Table.Add(new KTable {
DerivedFrom = table.DerivedFrom, Schema = new CSchema {
SchemaName = table.Schema.SchemaName
}, TableName = table.TableName, GeneratedTable = table
});
}
return(codeWriter.ToString());
}
private void InferQueries(KProtoFile kProtoFile)
{
var codeWriter = new CodeWriter();
foreach (var message in kProtoFile.GeneratedProtoFile.GetRepeatedMessagesUsedInAResponse())
{
if (message.IsExternal)
{
continue;
}
if (message.IsRequest)
{
continue;
}
if (message.HasFields)
{
Query.Add(new KQuery {
QueryName = $"{message.MessageName}Query"
});
}
}
}
private string InferViews(KProtoFile kProtoFile)
{
var codeWriter = new CodeWriter();
foreach (var message in kProtoFile.GeneratedProtoFile.GetRepeatedMessagesUsedInAResponse())
{
if (message.IsExternal)
{
continue;
}
if (message.IsRequest)
{
continue;
}
if (message.HasFields)
{
codeWriter.WriteLine($"CREATE VIEW [dbo].[{message.MessageName}View] AS");
codeWriter.WriteLine("SELECT");
codeWriter.Indent();
bool first = true;
foreach (var field in message.ProtoField)
{
if (!first)
{
codeWriter.Write(",");
}
codeWriter.WriteLine($"NULL AS '{field.FieldName}'");
first = false;
}
codeWriter.Unindent();
codeWriter.WriteLine();
codeWriter.WriteLine("GO");
codeWriter.WriteLine();
}
}
return(codeWriter.ToString());
}
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());
}
