private void btnTotalProfitCalculator_Click(object sender, RoutedEventArgs e)
{
try
{
var path = "total-profit.xlsx";
var mySqlCOntext = new MySqlModel();
var products = ProductsReportsLoader.GetReportsFromMySql(mySqlCOntext);
// TODO: Change it with entity framework
var vendorsProduct = SqliteParser.GetVendorsProducts();
ExcelGenerator.Generate(products, vendorsProduct, path);
MessageBox.Show("The total profit report was successfully generated!",
"Generated successfully",
MessageBoxButton.OK,
MessageBoxImage.Information);
Process.Start(path);
}
catch (Exception)
{
MessageBox.Show("Cannot generate the total profit report!",
"Generation failed",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
}
public void TestSqlitePage1()
{
var sql = "select id,a.name as fullname,a.city address from person2 a order by a.name";
var parser = new SqliteParser();
var result = parser.ParserPage(sql, 1, 10);
Assert.Equal("select * from (select id,a.name as fullname,a.city address from person2 a order by a.name) sbOuter limit :sbPageSize offset :sbPageSkip", result.PageSql);
Assert.Equal("select count(1) from (select id,a.name as fullname,a.city address from person2 a order by a.name) sbCount", result.CountSql);
Assert.Equal(2, result.SqlParameters.Count);
Assert.Equal(10, result.SqlParameters.FirstOrDefault(it => it.ParameterName == "sbPageSize")?.Value);
Assert.Equal(0, result.SqlParameters.FirstOrDefault(it => it.ParameterName == "sbPageSkip")?.Value);
}
private static void ParseSqlite(DataTable data)
{
SqliteParser sp = new SqliteParser();
ParseResult pr = sp.Parse(data);
if (_OutputType.Equals("console"))
{
Console.WriteLine(Common.SerializeJson(pr, true));
Console.WriteLine("");
}
else if (_OutputType.Equals("file") && !String.IsNullOrEmpty(_OutputFile))
{
File.WriteAllBytes(_OutputFile, Encoding.UTF8.GetBytes(Common.SerializeJson(pr, true)));
}
}
protected Ast.SqliteSyntaxProduction ParseSqlStmt(string text)
{
var tokens = Notebook.Tokenize(text);
var q = new TokenQueue(tokens, Notebook);
Ast.SqliteSyntaxProduction ast;
var result = SqliteParser.ReadStmt(q, out ast);
if (result.IsValid && q.Eof())
{
return(ast);
}
else
{
throw new MacroProcessorException($"\"{text}\" is not a valid SQL statement.");
}
}
public void CreateTableTest()
{
List <ISqliteStatement> statements = SqliteParser.Parse(@"
CREATE TABLE person
(
Id INTEGER not null
constraint test_pk
primary key autoincrement,
FirstName TEXT not null,
LastName TEXT not null,
Birthday TEXT,
age INTEGER
)
").ToList();
Assert.AreEqual(1, statements.Count);
SqliteCreateTable statement = statements.First().CastTo <SqliteCreateTable>();
Assert.AreEqual(5, statement.Columns.Count);
Assert.AreEqual("Id", statement.Columns[0].Name);
Assert.AreEqual("INTEGER", statement.Columns[0].Type);
Assert.IsTrue(statement.Columns[0].PrimaryKey);
Assert.IsTrue(statement.Columns[0].AutoIncrement);
Assert.IsFalse(statement.Columns[0].Nullable);
Assert.AreEqual("FirstName", statement.Columns[1].Name);
Assert.AreEqual("TEXT", statement.Columns[1].Type);
Assert.IsFalse(statement.Columns[1].PrimaryKey);
Assert.IsFalse(statement.Columns[1].AutoIncrement);
Assert.IsFalse(statement.Columns[1].Nullable);
Assert.AreEqual("LastName", statement.Columns[2].Name);
Assert.AreEqual("TEXT", statement.Columns[2].Type);
Assert.IsFalse(statement.Columns[2].PrimaryKey);
Assert.IsFalse(statement.Columns[2].AutoIncrement);
Assert.IsFalse(statement.Columns[2].Nullable);
Assert.AreEqual("Birthday", statement.Columns[3].Name);
Assert.AreEqual("TEXT", statement.Columns[3].Type);
Assert.IsFalse(statement.Columns[3].PrimaryKey);
Assert.IsFalse(statement.Columns[3].AutoIncrement);
Assert.IsTrue(statement.Columns[3].Nullable);
Assert.AreEqual("age", statement.Columns[4].Name);
Assert.AreEqual("INTEGER", statement.Columns[4].Type);
Assert.IsFalse(statement.Columns[4].PrimaryKey);
Assert.IsFalse(statement.Columns[4].AutoIncrement);
Assert.IsTrue(statement.Columns[4].Nullable);
}
protected Ast.Expr NewExpr(string text)
{
var tokens = Notebook.Tokenize(text);
var q = new TokenQueue(tokens, Notebook);
Ast.SqliteSyntaxProduction ast;
var result = SqliteParser.ReadExpr(q, out ast);
if (result.IsValid && q.Eof())
{
return(new Ast.Expr {
Sql = text, SqliteSyntax = ast
});
}
else
{
throw new MacroProcessorException($"\"{text}\" is not a valid SQL expression.");
}
}
public void TestSqlStatements()
{
var filePath = Path.Combine(Path.GetTempPath(), "SqliteGrammarTestDb");
File.WriteAllBytes(filePath, new byte[0]);
// these are other SQLite DBs we will attach to, to test schema names
var otherFilePath = Path.Combine(Path.GetTempPath(), "SqliteGrammarOtherDb");
File.WriteAllBytes(otherFilePath, Resources.OtherDb);
var other2FilePath = Path.Combine(Path.GetTempPath(), "SqliteGrammarOtherDb2");
File.WriteAllBytes(other2FilePath, Resources.OtherDb);
try {
using (var notebook = new Notebook(filePath, true)) {
notebook.Execute("ATTACH DATABASE ? AS other", new[] { filePath });
var stmts =
from line in Resources.SqlTests.Replace('\r', '\n').Split('\n')
let shouldFail = line.StartsWith("<ERR>")
let trimLine = line.Replace("<ERR>", "").Trim()
where trimLine.Any() && !trimLine.StartsWith("--")
select new { Cmd = trimLine, ShouldFail = shouldFail };
var errors = new List <string>();
foreach (var stmt in stmts)
{
// try first in SQLite directly to validate the test
try {
notebook.Execute(stmt.Cmd, new Dictionary <string, object> {
["@other2_path"] = other2FilePath,
["@int"] = 1,
["@str"] = "hello"
});
if (stmt.ShouldFail)
{
errors.Add($"Should have failed, but SQLite accepted.\n{stmt.Cmd}");
continue;
}
} catch (Exception ex) {
if (!stmt.ShouldFail)
{
errors.Add($"Should have passed, but SQLite rejected.\n{stmt.Cmd}\n" +
$"SQLite error: " + ex.Message);
continue;
}
}
// then try with our parser to see if we match SQLite
var tokens = notebook.Tokenize(stmt.Cmd);
var q = new TokenQueue(tokens, notebook);
SqlNotebookScript.Interpreter.Ast.SqliteSyntaxProduction ast;
var result = SqliteParser.ReadStmt(q, out ast);
var success = result.InvalidMessage == null && result.IsValid &&
tokens.Count == result.NumValidTokens;
if (stmt.ShouldFail && success)
{
errors.Add($"Should have failed, but we accepted.\n{stmt.Cmd}");
continue;
}
else if (!stmt.ShouldFail && !success)
{
errors.Add($"Should have passed, but we rejected.\n{stmt.Cmd}\n" +
$"InvalidMessage: {result.InvalidMessage}\nIsValid: {result.IsValid}\n" +
$"NumValidTokens: {result.NumValidTokens}");
continue;
}
}
if (errors.Any())
{
Assert.Fail(string.Join("\n\n", errors));
}
}
} finally {
File.Delete(filePath);
File.Delete(otherFilePath);
File.Delete(other2FilePath);
}
}
public void Read(SqliteReadDatabaseCommandParameters parameters, string outputPath)
{
Match match = connectionStringRegex.Match(parameters.ConnectionString);
match.Success.AssertIs(true, "ConnectionString", "is not in valid format. Has to be like 'Data Source=my.db'");
string originalFile = match.Groups["file"].Value;
string file = originalFile;
Logger.Trace($"Try to find sqlite database '{originalFile}'");
if (!FileSystem.FileExists(file))
{
file = FileSystem.Combine(outputPath, originalFile);
Logger.Trace($"Try to find sqlite database '{file}'");
}
if (!FileSystem.FileExists(file))
{
string fileName = FileSystem.GetFileName(file);
Logger.Trace($"Start wildcard search for {fileName}");
string[] files = FileSystem.GetFiles(outputPath, fileName, SearchOption.AllDirectories);
if (files.Length > 0)
{
file = files.First();
Logger.Trace($"Found in {file}");
}
}
if (!FileSystem.FileExists(file))
{
throw new FileNotFoundException("File not found. File has to be in source or output directory", originalFile);
}
string connectionString = parameters.ConnectionString.Replace(originalFile, file);
using SqliteConnection connection = new(connectionString);
connection.Open();
List <string> tableNames = parameters.ReadAll ? this.ReadTables(connection).ToList() : parameters.Tables;
List <SqliteCreateTable> statements = tableNames.SelectMany(tableName =>
{
string sql = this.ReadTableSql(connection, tableName);
return(SqliteParser.Parse(sql).OfType <SqliteCreateTable>());
}).ToList();
foreach (SqliteCreateTable statement in statements)
{
ModelTransferObject model = new()
{
Name = statement.TableName,
Language = SqliteLanguage.Instance
};
foreach (SqliteCreateTableColumn column in statement.Columns)
{
model.Fields.Add(new FieldTransferObject
{
Name = column.Name,
Type = new ()
{
Name = column.Type,
IsNullable = column.Nullable
}
});
}
transferObjects.Add(model);
}
public Page <T> PageBaseExecute <T>(MethodInfo method, object[] args, IServiceProvider serviceProvider)
{
ServiceProvider = serviceProvider;
Init();
//处理select逻辑
var selectAttribute = method.GetCustomAttribute <SelectAttribute>();
if (selectAttribute != null)
{
var repositoryOption = serviceProvider.GetService <RepositoryOption>();
//获得动态参数
var dbArgs = GetParameters(method, args);
if (pageable == null)
{
throw new Exception("method argument must have pageable");
}
OpenDb();
var sql = selectAttribute.Sql;
if (!sql.Contains("order by", StringComparison.OrdinalIgnoreCase))
{
throw new NotSupportedException("sql must contain order by clause");
}
sql = ReplaceSqlBindWhereCondition(sql);
var result = new Page <T>()
{
};
SqlParser.SqlParser parser;
if (repositoryOption.IsOracle)
{
parser = new OracleParser();
}
else if (repositoryOption.IsSqlServer)
{
parser = new SqlServerParser();
}
else if (repositoryOption.IsMysql)
{
parser = new MysqlParser();
}
else
{
parser = new SqliteParser();
}
var parseResult = parser.ParserPage(sql, pageable.PageNumber, pageable.PageSize);
ChangeDynamicParameters(parseResult.SqlParameters, dbArgs);
var count = dbConnection.QueryFirst <int>(parseResult.CountSql, dbArgs, transaction: dbTransaction);
var resultList = dbConnection.Query <T>(parseResult.PageSql, dbArgs, transaction: dbTransaction).ToList();
result.TotalPages = count;
result.Data = resultList;
result.PageSize = pageable.PageSize;
result.PageNumber = pageable.PageNumber;
CloseDb();
return(result);
}
throw new Exception("can not process method name:" + method.Name);
}
