public override int SelectCount(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
StringBuilder sb = new StringBuilder();
SqlCommand command = (SqlCommand)db.Connection.CreateCommand();
int parameterCounter = 1;
//创建一个QueryExpression
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
var sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
sb.Append("select count(0) ");
GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
IDataReader dataReader = db.ExecuteReader(command);
int count = 0;
if (dataReader.Read())
{
count = Convert.ToInt32(dataReader[0]);
}
dataReader.Close();
return(count);
}
public override bool Exists(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
var sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
StringBuilder sb = new StringBuilder();
SqlCommand command = (SqlCommand)db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("select top 1 1 ");
GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
//sb.Append(conditionCollection.Count > 0 ? " and " : " where ");
//sb.Append(" rownum<2");
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
IDataReader dataReader = db.ExecuteReader(command);
bool exists = dataReader.Read();
dataReader.Close();
return(exists);
}
public override int Delete(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
//ITable table=getTables().getTableByClass(clazz);
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
//执行关联删除
var sb = new StringBuilder();
var command = db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("delete ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
int returnCount = db.ExecuteNonQuery(command);
return(returnCount);
}
public void GetMaxEnergyTest_ReturnAverageEnergy(double expectedResult)
{
// Arange
var sqlAnalizer = new SqlAnalyzer <IEnergyObservation>(ConnectionString);
// Act
var result = sqlAnalizer.GetMaxEnergy();
// Assert
Assert.AreEqual(expectedResult, result, 1);
}
public override SelectionResult SelectCollection(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, IQueryExpression queryExpression)
{
//检查pageSize和pageIndex是否合法
Util.CheckPageSizeAndPageIndex(queryExpression.PageSize, queryExpression.PageIndex);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
StringBuilder sb = new StringBuilder();
IDbCommand cmd = db.Connection.CreateCommand();
int counter = 1;
GenerateSelectSql_SelectFromWhereOrder(sb, cmd.Parameters, ref counter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
//执行sql
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
if (queryExpression.PageIndex != null && queryExpression.PageSize != null)
{
//分页的时候需要包装分页语句
WrapPaging(db, cmd, queryExpression.PageSize.Value, queryExpression.PageIndex.Value);
}
SelectionResult result = new SelectionResult();
result.ObjectFiller = sqlAnalyseResult.ObjectFiller;
//先关闭datarader
if (queryExpression.ReturnMatchedCount)
{
if (sqlAnalyseResult.HasGroup)
{
result.TotalMatchedCount = SelectCountGroup(db, mappingProvider, tableMapping, queryExpression.EntityType, queryExpression, sqlAnalyseResult);
}
else
{
result.TotalMatchedCount = SelectCount(db, mappingProvider, tableMapping, queryExpression.EntityType, queryExpression.Wheres);
}
}
result.DataReader = db.ExecuteReader(cmd);
return(result);
}
public SqlAnalyzer GetAnalyzer(bool force = false)
{
if (myAnalyser != null && !force)
{
return(myAnalyser);
}
if (ScriptId > 0)
{
var cnn = new DatabaseConnection();
myAnalyser = SqlAnalyzer.Load(cnn, this.ScriptId);
}
return(myAnalyser);
}
public override int Delete(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
//ITable table=getTables().getTableByClass(clazz);
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
//执行关联删除
var sb = new StringBuilder();
var command = db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("delete ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
sb.Append(" from ");
sb.Append(GetTableName(entityType, mappingProvider, tableMapping));
sb.Append(" ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
if (sqlAnalyseResult.ForeignTables.Count == 0)
{
//说明是无关联表的条件，只是本表的条件
sb.Append(" where ");
GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, true, mappingProvider);
}
else
{
//说明有关联表的条件，需要多表关联
sb.Append(" where exists (");
SqlServerCreateSqlExists(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, mappingProvider, tableMapping);
sb.Append(")");
}
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
int returnCount = db.ExecuteNonQuery(command);
return(returnCount);
}
private void BtReload_Click(object sender, EventArgs e)
{
lstBloc.DataSource = null;
lstDatabaseObject.DataSource = null;
lstResume.DataSource = null;
var analyzer = SqlAnalyzer.Analyse(myScript.ScriptId, myScript.ScriptText);
lstBloc.DataSource = analyzer.Blocs;
lstDatabaseObject.DataSource = analyzer.SqlObjets;
lstResume.DataSource = analyzer.Resumes;
using var cnn = new DatabaseConnection();
analyzer.Save(cnn);
lstBloc.SelectedItem = null;
lstDatabaseObject.SelectedItem = null;
lstResume.SelectedItem = null;
EnableButtons();
}
private void AddScriptToObjectAction(Object currentObjectEdited, int action)
{
// Ajouter un script à la version
var script = new ScriptObject(currentObjectEdited, new SqlAction()
{
SqlActionId = action
});
var crudScript = new Script()
{
ScriptText = script.ToString(),
VersionId = currentObjectEdited.VersionId,
};
var scriptId = cnn.ExecuteScalar(Script.SQLInsert, crudScript);
// Ajouter l'anlyse pour ce script
var analyzer = SqlAnalyzer.Analyse(scriptId, crudScript.ScriptText);
analyzer.Valide = EValidation.Valide;
analyzer.Save(cnn);
}
public override int Delete(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
//ITable table=getTables().getTableByClass(clazz);
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
if (sqlAnalyseResult.ForeignTables.Count > 0)
{
//不支持
throw ExceptionFactory.NotSupported("SQLite不支持多表关联更新");
}
//执行关联删除
StringBuilder sb = new StringBuilder();
IDbCommand command = db.Connection.CreateCommand();
int parameterCounter = 1;
string tableName = GetTableName(entityType, mappingProvider, tableMapping);
sb.Append("delete from ");
sb.Append(tableName);
//说明是无关联表的条件，只是本表的条件
sb.Append(" where ");
GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, false, mappingProvider);
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
int returnCount = db.ExecuteNonQuery(command);
return(returnCount);
}
public async Task DoUpdateAsync()
{
if (IsDisposed)
{
return;
}
var buffer = _currentSnapshot;
var path = _document.FilePath;
// replace with user token
var token = _cts.Token;
var instance = await FsLintVsPackage.Instance.WithCancellation(token);
// this acts as a throttle
await Task.Delay(200, token);
if (Project == null)
{
await instance.JoinableTaskFactory.SwitchToMainThreadAsync();
var project = instance.Dte.Solution.FindProjectItem(path)?.ContainingProject;
if (project == null)
{
return;
}
if (instance.SolutionService.GetProjectOfUniqueName(project.UniqueName, out var vsHierarchy) != VSConstants.S_OK)
{
return;
}
if (instance.SolutionService.GetGuidOfProject(vsHierarchy, out var guid) != VSConstants.S_OK)
{
return;
}
Project = new LintProjectInfo(project.Name, guid, vsHierarchy);
}
await Task.Yield();
var connectionString = SqlAnalyzer.tryFindConnectionString(path);
if (string.IsNullOrWhiteSpace(connectionString))
{
return;
}
var loadedSchema = SqlAnalysis.databaseSchema(connectionString);
if (loadedSchema.IsError)
{
return;
}
var source = _currentSnapshot.GetText();
var sourceText = SourceText.ofString(source);
var getProjectOptions = _provider.CheckerInstance.GetProjectOptionsFromScript(
filename: path,
sourceText: sourceText,
assumeDotNetFramework: false,
useSdkRefs: true,
useFsiAuxLib: true,
previewEnabled: true,
otherFlags: new string[] { "--targetprofile:netstandard" },
loadedTimeStamp: FSharpOption <DateTime> .None,
extraProjectInfo: FSharpOption <object> .None,
optionsStamp: FSharpOption <long> .None,
userOpName: FSharpOption <string> .None
);
var(options, errors) = await FSharpAsync.StartAsTask(getProjectOptions, null, token);
if (errors.Any())
{
return;
}
var performParseAndCheck = _provider.CheckerInstance.ParseAndCheckFileInProject(
filename: path,
fileversion: 1,
sourceText: sourceText,
options: options,
textSnapshotInfo: FSharpOption <object> .None,
userOpName: FSharpOption <string> .None
);
var(parseResults, checkAnswer) = await FSharpAsync.StartAsTask(performParseAndCheck, null, token);
if (parseResults.ParseHadErrors || checkAnswer.IsAborted)
{
return;
}
var checkResults = SqlAnalyzer.checkAnswerResult(checkAnswer).Value;
var context = new SqlAnalyzerContext(
fileName: path,
content: source.Split('\n'),
parseTree: parseResults.ParseTree.Value,
symbols: SqlAnalyzer.getSymbols(checkResults)
);
var databaseSchema = loadedSchema.ResultValue;
var errorMessages =
from operation in SyntacticAnalysis.findSqlOperations(context)
from analysisOutput in SqlAnalysis.analyzeOperation(operation, connectionString, databaseSchema)
select analysisOutput;
var oldLintingErrors = this.Factory.CurrentSnapshot;
var newLintErrors = new LintingErrorsSnapshot(_document, oldLintingErrors.VersionNumber + 1);
foreach (var error in errorMessages)
{
var span = RangeToSpan(error.Range, buffer);
newLintErrors.Errors.Add(new LintError(span, error, Project));
}
await instance.JoinableTaskFactory.SwitchToMainThreadAsync();
if (token.IsCancellationRequested)
{
return;
}
UpdateLintingErrors(newLintErrors);
}
public async Task DoUpdateAsync()
{
if (IsDisposed)
{
return;
}
var buffer = _currentSnapshot;
var path = _document.FilePath;
// replace with user token
var token = _cts.Token;
var instance = await FsLintVsPackage.Instance.WithCancellation(token);
// this acts as a throttle
await Task.Delay(200, token);
if (Project == null)
{
await instance.JoinableTaskFactory.SwitchToMainThreadAsync();
var project = instance.Dte.Solution.FindProjectItem(path)?.ContainingProject;
if (project == null)
{
return;
}
if (instance.SolutionService.GetProjectOfUniqueName(project.UniqueName, out var vsHierarchy) != VSConstants.S_OK)
{
return;
}
if (instance.SolutionService.GetGuidOfProject(vsHierarchy, out var guid) != VSConstants.S_OK)
{
return;
}
Project = new LintProjectInfo(project.Name, guid, vsHierarchy);
}
await Task.Yield();
var connectionString = SqlAnalyzer.tryFindConnectionString(path);
if (string.IsNullOrWhiteSpace(connectionString))
{
return;
}
var source = _currentSnapshot.GetText();
var sourceText = SourceText.ofString(source);
var getProjectOptions = _provider.CheckerInstance.GetProjectOptionsFromScript(
filename: path,
source: sourceText,
assumeDotNetFramework: false,
useSdkRefs: true,
useFsiAuxLib: true,
previewEnabled: true,
otherFlags: new string[] { "--targetprofile:netstandard" },
loadedTimeStamp: FSharpOption <DateTime> .None,
extraProjectInfo: FSharpOption <object> .None,
optionsStamp: FSharpOption <long> .None,
userOpName: FSharpOption <string> .None,
sdkDirOverride: FSharpOption <string> .None
);
var(options, errors) = await FSharpAsync.StartAsTask(getProjectOptions, null, token);
if (errors.Any())
{
return;
}
var performParseAndCheck = _provider.CheckerInstance.ParseAndCheckFileInProject(
filename: path,
fileVersion: 1,
sourceText: sourceText,
options: options,
userOpName: FSharpOption <string> .None
);
var(parseResults, checkAnswer) = await FSharpAsync.StartAsTask(performParseAndCheck, null, token);
if (parseResults.ParseHadErrors || checkAnswer.IsAborted)
{
return;
}
var checkResults = SqlAnalyzer.checkAnswerResult(checkAnswer).Value;
var context = new SqlAnalyzerContext(
fileName: path,
content: source.Split('\n'),
parseTree: parseResults.ParseTree.Value,
symbols: SqlAnalyzer.getSymbols(checkResults)
);
var operationBlocks = SyntacticAnalysis.findSqlOperations(context);
var loadedSchema = SqlAnalysis.databaseSchema(connectionString);
var errorMessages = new List <Message>();
if (loadedSchema.IsError)
{
foreach (var operation in operationBlocks)
{
var containsSkipAnalysis = operation.blocks.Any(block => block.IsSkipAnalysis);
foreach (var block in operation.blocks)
{
var blockRange = block.Range();
if ((block.IsQuery || block.IsTransaction) && OptionModule.IsSome(blockRange) && !containsSkipAnalysis)
{
var internalError = (loadedSchema.ErrorValue ?? "").Replace("3D000:", "").Replace("28P01:", "");
var fullError = $"Error occured while trying to connect to the database using the configured connection string in NPGSQL_FSHARP in order to perform static analysis:{internalError}";
var errorMessage = new Message(
type: "Error",
message: fullError,
code: "SQL101",
severity: Severity.Warning,
range: blockRange.Value,
fixes: Microsoft.FSharp.Collections.FSharpList <Fix> .Empty
);
errorMessages.Add(errorMessage);
}
}
}
}
else
{
var databaseSchema = loadedSchema.ResultValue;
foreach (var operation in operationBlocks)
{
foreach (var analysisOutput in SqlAnalysis.analyzeOperation(operation, connectionString, databaseSchema))
{
errorMessages.Add(analysisOutput);
}
}
}
var oldLintingErrors = this.Factory.CurrentSnapshot;
var newLintErrors = new LintingErrorsSnapshot(_document, oldLintingErrors.VersionNumber + 1);
foreach (var error in errorMessages)
{
var span = RangeToSpan(error.Range, buffer);
newLintErrors.Errors.Add(new LintError(span, error, Project));
}
await instance.JoinableTaskFactory.SwitchToMainThreadAsync();
if (token.IsCancellationRequested)
{
return;
}
UpdateLintingErrors(newLintErrors);
}
public override int Update(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition, IDictionary <string, object> updates)
{
ITable table = mappingProvider.GetTable(entityType);
QueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
foreach (string propertyName in updates.Keys)
{
queryExpression.Selects.Add(propertyName);
}
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
var sb = new StringBuilder();
var command = db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("update ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
sb.Append(" set ");
var index = 0;
foreach (KeyValuePair <string, object> kv in updates)
{
if (index > 0)
{
sb.Append(",");
}
index++;
IColumn column = table.Columns[kv.Key];
sb.Append(column.ColumnName);
sb.Append("=");
sb.Append(CreateParameterNameInStatement(parameterCounter));
//object propertyValue = column.PropertyAdapter.GetValue(obj);
//Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size);
}
sb.Append(" from ");
sb.Append(GetTableName(entityType, mappingProvider, tableMapping));
sb.Append(" ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
sb.Append(" ");
if (sqlAnalyseResult.ForeignTables.Count == 0)
{
//说明是无关联表的条件，只是本表的条件
sb.Append(" where ");
GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, condition, sqlAnalyseResult, true, mappingProvider);
}
else
{
//说明有关联表的条件，需要多表关联
sb.Append(" where exists (");
SqlServerCreateSqlExists(sb, command.Parameters, ref parameterCounter, condition, sqlAnalyseResult, mappingProvider, tableMapping);
sb.Append(")");
}
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
int returnCount = db.ExecuteNonQuery(command);
return(returnCount);
}
protected virtual void GenerateSelectSql_Where_Item(
StringBuilder sb,
IDataParameterCollection parameters, ref int parameterCounter,
IConditionExpression conditionExpression, SqlColumn sqlColumn, bool includeTableNameAlias, IMappingProvider mappingProvider
)
{
//为处理Like自动增加%
object objValue = conditionExpression.ConditionValue;
switch (conditionExpression.ConditionOperator)
{
case ConditionOperator.Like:
case ConditionOperator.NotLike:
objValue = "%" + objValue + "%";
break;
case ConditionOperator.RightLike:
case ConditionOperator.NotRightLike:
objValue = "%" + objValue;
break;
case ConditionOperator.LeftLike:
case ConditionOperator.NotLeftLike:
objValue = objValue + "%";
break;
}
bool hasParameter = false;
//添加字段名
sb.Append(includeTableNameAlias ? sqlColumn.TableAliasAndColumn : sqlColumn.Column.ColumnName);
//添加操作符
switch (conditionExpression.ConditionOperator)
{
case ConditionOperator.Equal:
if (conditionExpression.ConditionValue == null)
{
sb.Append(" is null");
}
else
{
sb.Append("=");
hasParameter = true;
}
break;
case ConditionOperator.GreaterThan:
sb.Append(">");
hasParameter = true;
break;
case ConditionOperator.GreaterThanOrEqual:
sb.Append(">=");
hasParameter = true;
break;
case ConditionOperator.LessThan:
sb.Append("<");
hasParameter = true;
break;
case ConditionOperator.LessThanOrEqual:
sb.Append("<=");
hasParameter = true;
break;
case ConditionOperator.NotEqual:
if (conditionExpression.ConditionValue == null)
{
sb.Append(" is not null");
}
else
{
sb.Append(" <>");
hasParameter = true;
}
break;
case ConditionOperator.In:
case ConditionOperator.NotIn:
if (conditionExpression.ConditionOperator == ConditionOperator.NotIn)
{
sb.Append(" not");
}
sb.Append(" in ");
if (objValue is IQueryExpression)
{
hasParameter = true;
}
else
{
string v = conditionExpression.ConditionValue + "";
v = v.Trim();
if (!v.StartsWith("("))
{
sb.Append("(");
}
sb.Append(v);
if (!v.EndsWith(")"))
{
sb.Append(")");
}
}
break;
case ConditionOperator.Like:
case ConditionOperator.LeftLike:
case ConditionOperator.RightLike:
sb.Append(" like ");
hasParameter = true;
break;
case ConditionOperator.NotLike:
case ConditionOperator.NotLeftLike:
case ConditionOperator.NotRightLike:
sb.Append(" not like ");
hasParameter = true;
break;
case ConditionOperator.Custom:
sb.Append(conditionExpression.ConditionValue);
break;
}
if (hasParameter)
{
if (objValue is IQueryExpression)
{
IQueryExpression query = objValue as IQueryExpression;
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, query);
sb.Append(" (");
GenerateSelectSql_SelectFromWhereOrder(sb, parameters, ref parameterCounter, query, sqlAnalyseResult, mappingProvider, null);
sb.Append(")");
}
else
{
sb.Append(CreateParameterNameInStatement(parameterCounter));
Type propertyType = sqlColumn.FullPropertyName.GroupFunction == GroupFunction.Count ? typeof(int) : sqlColumn.Column.PropertyType;
int size = sqlColumn.FullPropertyName.GroupFunction == GroupFunction.Count ? 4 : sqlColumn.Column.Size;
AddParameter(parameters, ref parameterCounter, objValue, propertyType, size);
}
}
}
