Merge pull request #660 from iceljc/master · SciSharp/BotSharp@a0e7117 · GitHub
Skip to content

Commit a0e7117

Browse files
iceljciceljc
authored
Merge pull request #660 from iceljc/master
refine sql
2 parents b385d82 + fd17c21 commit a0e7117

15 files changed

Lines changed: 102 additions & 73 deletions

File tree

‎src/Plugins/BotSharp.Plugin.Planner/Functions/PrimaryStagePlanFn.cs‎

Lines changed: 1 addition & 1 deletion

‎src/Plugins/BotSharp.Plugin.Planner/Functions/SecondaryStagePlanFn.cs‎

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -17,16 +17,14 @@ public SecondaryStagePlanFn(IServiceProvider services, ILogger<SecondaryStagePla
1717

1818
public async Task<bool> Execute(RoleDialogModel message)
1919
{
20-
var fn = _services.GetRequiredService<IRoutingService>();
2120
var agentService = _services.GetRequiredService<IAgentService>();
2221
var knowledgeService = _services.GetRequiredService<IKnowledgeService>();
2322
var knowledgeSettings = _services.GetRequiredService<KnowledgeBaseSettings>();
2423
var states = _services.GetRequiredService<IConversationStateService>();
2524

2625
var msgSecondary = RoleDialogModel.From(message);
27-
var collectionName = knowledgeSettings.Default.CollectionName ?? KnowledgeCollectionName.BotSharp;
26+
var collectionName = knowledgeSettings.Default.CollectionName;
2827
var planPrimary = states.GetState("planning_result");
29-
var taskPrimary = states.GetState("requirement_detail");
3028

3129
var taskSecondary = JsonSerializer.Deserialize<SecondaryBreakdownTask>(msgSecondary.FunctionArgs);
3230

@@ -35,8 +33,8 @@ public async Task<bool> Execute(RoleDialogModel message)
3533
{
3634
Confidence = 0.6f
3735
});
38-
var knowledgeResults = "";
39-
knowledgeResults = string.Join("\r\n\r\n=====\r\n", knowledges.Select(x => x.ToQuestionAnswer()));
36+
37+
var knowledgeResults = string.Join("\r\n\r\n=====\r\n", knowledges.Select(x => x.ToQuestionAnswer()));
4038

4139
// Get second stage planning prompt
4240
var currentAgent = await agentService.LoadAgent(message.CurrentAgentId);
@@ -45,7 +43,7 @@ public async Task<bool> Execute(RoleDialogModel message)
4543

4644
var plannerAgent = new Agent
4745
{
48-
Id = string.Empty,
46+
Id = BuiltInAgentId.Planner,
4947
Name = "planning_2nd",
5048
Instruction = secondPlanningPrompt,
5149
TemplateDict = new Dictionary<string, object>(),

‎src/Plugins/BotSharp.Plugin.Planner/Functions/SummaryPlanFn.cs‎

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -33,15 +33,21 @@ public async Task<bool> Execute(RoleDialogModel message)
3333
var states = _services.GetRequiredService<IConversationStateService>();
3434
var steps = states.GetState("planning_result").JsonArrayContent<SecondStagePlan>();
3535
var allTables = new List<string>();
36-
var ddlStatements = "";
36+
var ddlStatements = string.Empty;
3737
var relevantKnowledge = states.GetState("planning_result");
3838
var dictionaryItems = states.GetState("dictionary_items");
39+
var items = new List<string>();
40+
if (!string.IsNullOrWhiteSpace(dictionaryItems))
41+
{
42+
items = JsonSerializer.Deserialize<List<string>>(dictionaryItems);
43+
}
3944

4045
foreach (var step in steps)
4146
{
4247
allTables.AddRange(step.Tables);
4348
}
4449
var distinctTables = allTables.Distinct().ToList();
50+
4551
foreach (var table in distinctTables)
4652
{
4753
var msgCopy = RoleDialogModel.From(message);
@@ -54,7 +60,7 @@ public async Task<bool> Execute(RoleDialogModel message)
5460
}
5561

5662
// Summarize and generate query
57-
var summaryPlanPrompt = await GetSummaryPlanPrompt(taskRequirement, relevantKnowledge, dictionaryItems, ddlStatements);
63+
var summaryPlanPrompt = await GetSummaryPlanPrompt(taskRequirement, relevantKnowledge, items, ddlStatements);
5864
_logger.LogInformation($"Summary plan prompt:\r\n{summaryPlanPrompt}");
5965

6066
var plannerAgent = new Agent
@@ -74,7 +80,7 @@ await HookEmitter.Emit<IPlanningHook>(_services, x =>
7480
return true;
7581
}
7682

77-
private async Task<string> GetSummaryPlanPrompt(string taskDescription, string relevantKnowledge, string dictionaryItems, string ddlStatement)
83+
private async Task<string> GetSummaryPlanPrompt(string taskDescription, string relevantKnowledge, IEnumerable<string> dictionaryItems, string ddlStatement)
7884
{
7985
var agentService = _services.GetRequiredService<IAgentService>();
8086
var render = _services.GetRequiredService<ITemplateRender>();
@@ -92,9 +98,9 @@ await HookEmitter.Emit<IPlanningHook>(_services, async x =>
9298
return render.Render(template, new Dictionary<string, object>
9399
{
94100
{ "task_description", taskDescription },
95-
{ "summary_requirements", string.Join("\r\n",additionalRequirements) },
101+
{ "summary_requirements", string.Join("\r\n", additionalRequirements) },
96102
{ "relevant_knowledges", relevantKnowledge },
97-
{ "dictionary_items", dictionaryItems },
103+
{ "dictionary_items", string.Join("\r\n\r\n", dictionaryItems) },
98104
{ "table_structure", ddlStatement },
99105
});
100106
}

‎src/Plugins/BotSharp.Plugin.Planner/data/agents/282a7128-69a1-44b0-878c-a9159b88f3b9/agent.json‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
"profiles": [ "planning" ],
1212
"utilities": [ "two-stage-planner" ],
1313
"llmConfig": {
14-
"provider": "azure-openai",
14+
"provider": "openai",
1515
"model": "gpt-4o",
1616
"max_recursion_depth": 10
1717
}

‎src/Plugins/BotSharp.Plugin.SqlDriver/BotSharp.Plugin.SqlDriver.csproj‎

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -11,8 +11,11 @@
1111
</PropertyGroup>
1212

1313
<ItemGroup>
14+
<Compile Remove="data\agents\beda4c12-e1ec-4b4b-b328-3df4a6687c4f\templates\**" />
1415
<Compile Remove="packages\**" />
16+
<EmbeddedResource Remove="data\agents\beda4c12-e1ec-4b4b-b328-3df4a6687c4f\templates\**" />
1517
<EmbeddedResource Remove="packages\**" />
18+
<None Remove="data\agents\beda4c12-e1ec-4b4b-b328-3df4a6687c4f\templates\**" />
1619
<None Remove="packages\**" />
1720
</ItemGroup>
1821

@@ -80,8 +83,4 @@
8083
<ProjectReference Include="..\..\Infrastructure\BotSharp.Core\BotSharp.Core.csproj" />
8184
</ItemGroup>
8285

83-
<ItemGroup>
84-
<Folder Include="data\agents\beda4c12-e1ec-4b4b-b328-3df4a6687c4f\templates\" />
85-
</ItemGroup>
86-
8786
</Project>

‎src/Plugins/BotSharp.Plugin.SqlDriver/Controllers/SqlDriverController.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,6 @@ public SqlDriverController(IServiceProvider services)
1919
public async Task<bool> ImportDbKnowledge(ImportDbKnowledgeRequest request)
2020
{
2121
var dbKnowledge = _services.GetRequiredService<DbKnowledgeService>();
22-
return await dbKnowledge.Import(request.Provider ?? "openai", request.Model ?? "gpt-4o", request.Schema);
22+
return await dbKnowledge.Import(request);
2323
}
2424
}

‎src/Plugins/BotSharp.Plugin.SqlDriver/Functions/ExecuteQueryFn.cs‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -44,7 +44,6 @@ private IEnumerable<dynamic> RunQueryInSqlServer(string[] sqlTexts)
4444
{
4545
var settings = _services.GetRequiredService<SqlDriverSetting>();
4646
using var connection = new SqlConnection(settings.SqlServerExecutionConnectionString ?? settings.SqlServerConnectionString);
47-
var dictionary = new Dictionary<string, object>();
4847
return connection.Query(string.Join("\r\n", sqlTexts));
4948
}
5049
}

‎src/Plugins/BotSharp.Plugin.SqlDriver/Functions/GetTableDefinitionFn.cs‎

Lines changed: 22 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,4 @@
11
using BotSharp.Plugin.SqlDriver.Models;
2-
using Fluid.Ast.BinaryExpressions;
32
using Microsoft.Data.SqlClient;
43
using Microsoft.Extensions.Logging;
54
using MySqlConnector;
@@ -37,18 +36,14 @@ public async Task<bool> Execute(RoleDialogModel message)
3736
};
3837

3938
message.Content = string.Join("\r\n\r\n", tableDdls);
40-
41-
//var states = _services.GetRequiredService<IConversationStateService>();
42-
//states.SetState($"table_definition_{args.Table}", message.Content);
43-
4439
return true;
4540
}
4641

4742
private List<string> GetDdlFromMySql(string[] tables)
4843
{
4944
var settings = _services.GetRequiredService<SqlDriverSetting>();
5045
var tableDdls = new List<string>();
51-
using var connection = new MySqlConnection(settings.MySqlExecutionConnectionString);
46+
using var connection = new MySqlConnection(settings.MySqlExecutionConnectionString ?? settings.MySqlConnectionString);
5247
connection.Open();
5348

5449
foreach (var table in tables)
@@ -76,7 +71,6 @@ private List<string> GetDdlFromMySql(string[] tables)
7671
}
7772

7873
connection.Close();
79-
8074
return tableDdls;
8175
}
8276

@@ -92,27 +86,27 @@ private List<string> GetDdlFromSqlServer(string[] tables)
9286
try
9387
{
9488
var sql = @$"DECLARE @TableName NVARCHAR(128) = '{table}';
95-
DECLARE @SQL NVARCHAR(MAX) = 'CREATE TABLE ' + @TableName + ' (';
96-
97-
SELECT @SQL = @SQL + '
98-
' + COLUMN_NAME + ' ' +
99-
DATA_TYPE +
100-
CASE
101-
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND DATA_TYPE LIKE '%char%'
102-
THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
103-
WHEN DATA_TYPE IN ('decimal', 'numeric')
104-
THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR(10)) + ',' + CAST(NUMERIC_SCALE AS VARCHAR(10)) + ')'
105-
ELSE ''
106-
END + ' ' +
107-
CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END + ','
108-
FROM INFORMATION_SCHEMA.COLUMNS
109-
WHERE TABLE_NAME = @TableName
110-
ORDER BY ORDINAL_POSITION;
111-
112-
-- Remove the last comma and add closing parenthesis
113-
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ');';
114-
115-
SELECT @SQL;";
89+
DECLARE @SQL NVARCHAR(MAX) = 'CREATE TABLE ' + @TableName + ' (';
90+
91+
SELECT @SQL = @SQL + '
92+
' + COLUMN_NAME + ' ' +
93+
DATA_TYPE +
94+
CASE
95+
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND DATA_TYPE LIKE '%char%'
96+
THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
97+
WHEN DATA_TYPE IN ('decimal', 'numeric')
98+
THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR(10)) + ',' + CAST(NUMERIC_SCALE AS VARCHAR(10)) + ')'
99+
ELSE ''
100+
END + ' ' +
101+
CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END + ','
102+
FROM INFORMATION_SCHEMA.COLUMNS
103+
WHERE TABLE_NAME = @TableName
104+
ORDER BY ORDINAL_POSITION;
105+
106+
-- Remove the last comma and add closing parenthesis
107+
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ');';
108+
109+
SELECT @SQL;";
116110

117111
using var command = new SqlCommand(sql, connection);
118112
using var reader = command.ExecuteReader();
@@ -129,7 +123,6 @@ FROM INFORMATION_SCHEMA.COLUMNS
129123
}
130124

131125
connection.Close();
132-
133126
return tableDdls;
134127
}
135128
}

‎src/Plugins/BotSharp.Plugin.SqlDriver/Functions/LookupDictionaryFn.cs‎

Lines changed: 46 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,9 @@
1-
using Azure;
21
using BotSharp.Abstraction.Agents.Enums;
3-
using BotSharp.Abstraction.MLTasks;
42
using BotSharp.Abstraction.Routing;
5-
using BotSharp.Core.Agents.Services;
63
using BotSharp.Core.Infrastructures;
74
using BotSharp.Plugin.SqlDriver.Models;
85
using MySqlConnector;
9-
using System.Text.RegularExpressions;
106
using static Dapper.SqlMapper;
11-
using static System.Net.Mime.MediaTypeNames;
127

138
namespace BotSharp.Plugin.SqlDriver.Functions;
149

@@ -35,15 +30,16 @@ public async Task<bool> Execute(RoleDialogModel message)
3530
var agentService = _services.GetRequiredService<IAgentService>();
3631
var currentAgent = await agentService.LoadAgent(message.CurrentAgentId);
3732
var dictionarySqlPrompt = await GetDictionarySQLPrompt(args.SqlStatement, msgCopy.Content);
38-
var plannerAgent = new Agent
33+
var agent = new Agent
3934
{
40-
Id = string.Empty,
35+
Id = message.CurrentAgentId ?? string.Empty,
4136
Name = "sqlDriver_DictionarySearch",
4237
Instruction = dictionarySqlPrompt,
4338
TemplateDict = new Dictionary<string, object>(),
4439
LlmConfig = currentAgent.LlmConfig
4540
};
46-
var response = await GetAiResponse(plannerAgent);
41+
42+
var response = await GetAiResponse(agent);
4743
args = JsonSerializer.Deserialize<LookupDictionary>(response.Content);
4844

4945
// check if need to instantely
@@ -59,13 +55,24 @@ public async Task<bool> Execute(RoleDialogModel message)
5955
{
6056
message.Content = JsonSerializer.Serialize(result);
6157
}
58+
6259
var states = _services.GetRequiredService<IConversationStateService>();
63-
var dictionaryItems = states.GetState("dictionary_items", "");
64-
dictionaryItems += "\r\n\r\n" + args.Table + ":\r\n" + args.Reason + ":\r\n" + message.Content + "\r\n";
65-
states.SetState("dictionary_items", dictionaryItems);
60+
var dictionaryItems = states.GetState("dictionary_items");
61+
var newItem = BuildDictionaryItem(args.Table, args.Reason, message.Content);
62+
63+
var items = new List<string>();
64+
if (!string.IsNullOrWhiteSpace(dictionaryItems))
65+
{
66+
items = JsonSerializer.Deserialize<List<string>>(dictionaryItems);
67+
}
68+
69+
items.Add(newItem);
70+
//dictionaryItems += "\r\n\r\n" + args.Table + ":\r\n" + args.Reason + ":\r\n" + message.Content + "\r\n";
71+
states.SetState("dictionary_items", JsonSerializer.Serialize(items));
6672

6773
return true;
6874
}
75+
6976
private async Task<string> GetDictionarySQLPrompt(string originalSql, string tableStructure)
7077
{
7178
var agentService = _services.GetRequiredService<IAgentService>();
@@ -83,15 +90,39 @@ private async Task<string> GetDictionarySQLPrompt(string originalSql, string tab
8390
{ "response_format", responseFormat }
8491
});
8592
}
86-
private async Task<RoleDialogModel> GetAiResponse(Agent plannerAgent)
93+
94+
private async Task<RoleDialogModel> GetAiResponse(Agent agent)
8795
{
8896
var text = "Check and correct the SQL statement.";
8997
var message = new RoleDialogModel(AgentRole.User, text);
9098

9199
var completion = CompletionProvider.GetChatCompletion(_services,
92-
provider: plannerAgent.LlmConfig.Provider,
93-
model: plannerAgent.LlmConfig.Model);
100+
provider: agent.LlmConfig.Provider,
101+
model: agent.LlmConfig.Model);
102+
103+
return await completion.GetChatCompletions(agent, new List<RoleDialogModel> { message });
104+
}
105+
106+
private string BuildDictionaryItem(string? table, string? reason, string? result)
107+
{
108+
var res = new List<string>();
109+
if (!string.IsNullOrWhiteSpace(table))
110+
{
111+
res.Add($"Table: {table}");
112+
}
113+
114+
if (!string.IsNullOrWhiteSpace(reason))
115+
{
116+
res.Add($"Reason: {reason}");
117+
}
118+
119+
if (!string.IsNullOrWhiteSpace(result))
120+
{
121+
res.Add($"Result: {result}");
122+
}
123+
124+
if (res.IsNullOrEmpty()) return string.Empty;
94125

95-
return await completion.GetChatCompletions(plannerAgent, new List<RoleDialogModel> { message });
126+
return string.Join("\r\n", res);
96127
}
97128
}

‎src/Plugins/BotSharp.Plugin.SqlDriver/Hooks/SqlDictionaryLookupHook.cs‎

Lines changed: 0 additions & 1 deletion

0 commit comments

Comments
 (0)