/// <summary>
/// 更新一条或一组记录
/// </summary>
/// <param name="table">数据表名称</param>
/// <param name="filter">过滤条件</param>
/// <param name="dataArray">需要更新的记录</param>
/// <returns>返回更新条数</returns>
public int Update <T>(string table, object filter, params T[] dataArray)
{
if (dataArray == null || dataArray.Length <= 0)
{
return(0);
}
var filterEntities = JsonParser.ConvertEntities(filter);
if (filterEntities.Count <= 0)
{
throw new Exception("未传入筛选条件,未防止错误,该执行已停止.");
}
var filterSql = new FilterParser().Parse(table, filterEntities[0]);
if (string.IsNullOrEmpty(filterSql))
{
throw new Exception("未能解析出需要筛选sql语句,未防止错误,该执行已停止.");
}
var entities = JsonParser.ConvertEntities(dataArray);
var filterNames = JsonParser.GetProperties(filterEntities[0]).Select(item => item.Name);
var sql = new UpdateParser().Parse(table, entities[0], filterNames.ToArray());
if (string.IsNullOrEmpty(sql))
{
throw new Exception("未能解析出需要执行的sql语句.");
}
return(this.Execute(sql + " WHERE " + filterSql, entities.Select((source) =>
{
return new UpdateModel(source, filterEntities[0]);
}).ToArray()));
}
public void ParseTableNameUpdate()
{
UpdateParser updateParser = new UpdateParser();
string tableName = updateParser.GetTableName("update loops where col1 = '345'");
Assert.AreEqual("loops", tableName);
}
public void ParallelTest()
{
var updateParser = new UpdateParser();
var stringParser = new StringParser();
var reader = new Reader();
var writer = new Writer(reader);
var lockManager = new LockManager(writer, reader);
var schemaFetcher = new SchemaFetcher(reader);
var interpreter = new Interpreter(
new SelectParser(),
new InsertParser(schemaFetcher),
updateParser,
schemaFetcher,
new GeneralParser(),
new CreateParser(),
stringParser,
lockManager,
reader,
new PredicateParser());
interpreter.ProcessStatement(@"create table house4 (
NumBedrooms int,
NumBath int,
Price decimal,
IsListed bool,
Address varchar(50)
)");
var allHouses = new List <List <List <IComparable> > >();
interpreter.ProcessStatement("insert into house4 values (5,3,295000,true,'800 Wormwood Dr')");
Parallel.For(0, 200, i =>
{
interpreter.ProcessStatement("insert into house4 values (5,3,295000,true,'800 Wormwood Dr')");
var houses = (List <List <IComparable> >)interpreter.ProcessStatement("select * FROM house4");
allHouses.Add(houses);
});
var housesOut = (List <List <IComparable> >)interpreter.ProcessStatement("select * FROM house4");
allHouses.Add(housesOut);
var allHousesCountCorrect = allHouses.Count() == 201;
var insertCountCorrect = allHouses[200].Count() == 201;
Assert.AreEqual(true, allHousesCountCorrect);
Assert.AreEqual(true, insertCountCorrect);
}
public void GetSetClause()
{
UpdateParser updateParser = new UpdateParser();
List <KeyValuePair <string, string> > setClause = updateParser.GetUpdates(@"update houses
Set Price = 456000, Address = 'gtggtt', Neigbs = 'gfff'
where houseID = 908");
Assert.AreEqual(3, setClause.Count);
Assert.AreEqual("price", setClause[0].Key.ToLower());
Assert.AreEqual("address", setClause[1].Key.ToLower());
Assert.AreEqual("neigbs", setClause[2].Key.ToLower());
Assert.AreEqual("456000", setClause[0].Value.ToLower());
Assert.AreEqual("'gtggtt'", setClause[1].Value.ToLower());
Assert.AreEqual("'gfff'", setClause[2].Value.ToLower());
}
public static Executor GetInstance(string databaseName = Constants.FILE_NAME)
{
if (_instance == null)
{
lock (_lockObject)
{
if (databaseName != null)
{
if (!File.Exists(databaseName))
{
using (File.Create(databaseName));
}
}
var updateParser = new UpdateParser();
var stringParser = new StringParser();
var reader = new Reader();
var writer = new Writer(reader);
var lockManager = new LockManager(writer, reader);
var schemaFetcher = new SchemaFetcher(reader);
var selectParser = new SelectParser();
var insertParser = new InsertParser(schemaFetcher);
var generalParser = new GeneralParser();
var createParser = new CreateParser();
var predicateParser = new PredicateParser();
var interpreter = new Interpreter(
selectParser,
insertParser,
updateParser,
schemaFetcher,
generalParser,
createParser,
stringParser,
lockManager,
reader,
predicateParser);
_instance = new Executor(interpreter);
}
}
return(_instance);
}
public Interpreter(SelectParser selectParser,
InsertParser insertParser,
UpdateParser updateParser,
SchemaFetcher schemaFetcher,
GeneralParser generalParser,
CreateParser createParser,
StringParser stringParser,
LockManager lockManager,
Reader reader,
PredicateParser predicateParser)
{
_selectParser = selectParser;
_insertParser = insertParser;
_updateParser = updateParser;
_schemaFetcher = schemaFetcher;
_generalParser = generalParser;
_createParser = createParser;
_stringParser = stringParser;
_lockManager = lockManager;
_reader = reader;
_predicateParser = predicateParser;
}
private void Update_Pressed(object sender, EventArgs args)
{
UpdateParser.BeginParse();
}
public void FullIntegration()
{
File.WriteAllText(Constants.FILE_NAME, null);
var updateParser = new UpdateParser();
var stringParser = new StringParser();
var reader = new Reader();
var writer = new Writer(reader);
var lockManager = new LockManager(writer, reader);
var schemaFetcher = new SchemaFetcher(reader);
var interpreter = new Interpreter(
new SelectParser(),
new InsertParser(schemaFetcher),
updateParser,
schemaFetcher,
new GeneralParser(),
new CreateParser(),
stringParser,
lockManager,
reader,
new PredicateParser());
string identityTable = @"create table Skateboards (
SkateBoardId int Identity,
Name varchar(100),
Price decimal
)";
Random rd = new Random();
interpreter.ProcessStatement(identityTable);
var emptySkateboardRows = (List <List <IComparable> >)interpreter.ProcessStatement("select * from skateboards where Name = 'bob'");
for (int i = 0; i < 500; i++)
{
string insertIdentity = "insert into Skateboards values ('HotSauce', " + rd.Next() + ")";
interpreter.ProcessStatement(insertIdentity);
}
string readAllSkateboards = @"select * from Skateboards";
var skateboardRows = (List <List <IComparable> >)interpreter.ProcessStatement(readAllSkateboards);
string readSkateboardByName = @"select * from Skateboards where name = 'HotSauce'";
var readSkateboardByNameRows = (List <List <IComparable> >)interpreter.ProcessStatement(readSkateboardByName);
string createHousesTable = @"create table houses (
Address varchar(100),
Price decimal,
SqFeet bigint,
IsListed bool,
NumBedrooms int,
)";
interpreter.ProcessStatement(createHousesTable);
for (int i = 0; i < 200; i++)
{
string insertStatement = @"insert into houses values ('" + CreateString(10) + "'," +
rd.Next().ToString() + "," + rd.Next().ToString() + "," + "true," + rd.Next().ToString() + ")";
interpreter.ProcessStatement(insertStatement);
}
string insertStatement2 = @"insert into houses values ('" + "450 Adams St" + "'," +
"320000" + "," + "2300" + "," + "false," + "3" + ")";
interpreter.ProcessStatement(insertStatement2);
string createToolsTable = @"create table tools (
Name varchar(30),
Price decimal,
NumInStock bigint,
IsWooden bool,
Manufacturer varchar(50)
)";
interpreter.ProcessStatement(createToolsTable);
for (int i = 0; i < 500; i++)
{
string insertStatement = @"insert into tools values ('" + CreateString(10) + "'," +
rd.Next().ToString() + "," + rd.Next().ToString() + "," + "true," + CreateString(10) + ")";
interpreter.ProcessStatement(insertStatement);
}
string insertStatement3 = @"insert into tools values ('" + "hammer" + "'," +
"23.99" + "," + "67" + "," + "false," + "'craftsman'" + ")";
interpreter.ProcessStatement(insertStatement3);
for (int i = 0; i < 200; i++)
{
string insertStatement = @"insert into houses values ('" + CreateString(10) + "'," +
rd.Next().ToString() + "," + rd.Next().ToString() + "," + "true," + rd.Next().ToString() + ")";
interpreter.ProcessStatement(insertStatement);
}
string insertStatement6 = @"insert into houses values ('" + "999 Adams St" + "'," +
"269000" + "," + "2300" + "," + "false," + "3" + ")";
interpreter.ProcessStatement(insertStatement6);
string insertStatement7 = @"insert into houses values ('" + "999 Adams St" + "'," +
"270000" + "," + "2300" + "," + "false," + "3" + ")";
interpreter.ProcessStatement(insertStatement7);
string insertStatement4 = @"insert into tools values ('" + "drill" + "'," +
"45.99" + "," + "90" + "," + "false," + "'dewalt'" + ")";
interpreter.ProcessStatement(insertStatement4);
for (int i = 0; i < 250; i++)
{
string insertStatement = @"insert into tools values ('" + CreateString(10) + "'," +
rd.Next().ToString() + "," + rd.Next().ToString() + "," + "true," + CreateString(10) + ")";
interpreter.ProcessStatement(insertStatement);
}
string selectInOperator = "select * from tools where name in (select name from tools)";
var selectInOperatorRows = (List <List <IComparable> >)interpreter.ProcessStatement(selectInOperator);
//houses count should be: 1401
//tools count should be: 752
string readAllHouses = @"select * from houses";
var rows = (List <List <IComparable> >)interpreter.ProcessStatement(readAllHouses);
bool rowCountCorrect = rows.Count() == 403;
bool columnCountCorrect = rows[0].Count() == 5;
string readAllTools = @"select price, numInstock from tools";
var tools = (List <List <IComparable> >)interpreter.ProcessStatement(readAllTools);
bool rowCountCorrect2 = tools.Count() == 752;
bool columnCountCorrect2 = tools[0].Count() == 2;
string querySearchHousesByName = @"select * from houses where address = '450 Adams St'";
var result = (List <List <IComparable> >)interpreter.ProcessStatement(querySearchHousesByName);
bool resultCountCorrect = result.Count() == 1;
string querySearchHousesByNameAndPrice = @"select *
from houses
where address = '450 Adams St'
AND price > 315000";
var result2 = (List <List <IComparable> >)interpreter.ProcessStatement(querySearchHousesByNameAndPrice);
bool resultCountCorrect2 = result2.Count() == 1;
string subQueryTools = @"select * from tools
where name = (select name from tools where price = 45.99 )";
var toolsSubQueryResult = (List <List <IComparable> >)interpreter.ProcessStatement(subQueryTools);
var toolSubQueryCompare = ((string)toolsSubQueryResult[0][0]).Trim() == "drill" && (decimal)toolsSubQueryResult[0][1]
== 45.99m && (bool)toolsSubQueryResult[0][3] == false;
string toolsInClause = @"select * from tools
where name IN ('drill', 'hammer' )";
var toolsInClauseResults = (List <List <IComparable> >)interpreter.ProcessStatement(toolsInClause);
var compare = toolsInClauseResults.Count() == 2;
string selectWithPredicatesAndOrderBy = @"select * from houses
where address != '98765 ABC str'
AND Price > 269000
order by price";
var predicatesAndOrderResults = (List <List <IComparable> >)interpreter.ProcessStatement(selectWithPredicatesAndOrderBy);
bool colCountCorrect = ((int)predicatesAndOrderResults[0].Count()) == 5;
bool orderIsCorrect = ((decimal)predicatesAndOrderResults[1][1]) > ((decimal)predicatesAndOrderResults[0][1]) &&
((decimal)predicatesAndOrderResults[15][1]) > ((decimal)predicatesAndOrderResults[6][1]) &&
((decimal)predicatesAndOrderResults[90][1]) > ((decimal)predicatesAndOrderResults[89][1]) &&
((decimal)predicatesAndOrderResults[100][1]) > ((decimal)predicatesAndOrderResults[98][1]) &&
((decimal)predicatesAndOrderResults[120][1]) > ((decimal)predicatesAndOrderResults[118][1]) &&
((decimal)predicatesAndOrderResults[150][1]) > ((decimal)predicatesAndOrderResults[145][1]);
//*******group by tests
string createTable = @"create table houses2( Price int, NumBedRooms int, NumBathrooms int )";
interpreter.ProcessStatement(createTable);
string insert1 = "insert into houses2 values (300000, 3, 2)";
interpreter.ProcessStatement(insert1);
string insert2 = "insert into houses2 values (300000, 4, 3)";
interpreter.ProcessStatement(insert2);
string insert3 = "insert into houses2 values (300000, 5, 4)";
interpreter.ProcessStatement(insert3);
string insert4 = "insert into houses2 values (330000, 6, 5)";
interpreter.ProcessStatement(insert4);
string insert5 = "insert into houses2 values (330000, 7, 6)";
interpreter.ProcessStatement(insert5);
string select = @"select Price, Max(NumBedRooms), Min(NumBathrooms)
from houses2
GROUP BY PRICE";
var groupedRows = (List <List <IComparable> >)interpreter.ProcessStatement(select);
var groupedCountCorrect = groupedRows.Count() == 2;
var groupedValuesCorrect = (int)groupedRows[0][0] == 300000 &&
(int)groupedRows[0][1] == 5 &&
(int)groupedRows[0][2] == 2 &&
(int)groupedRows[1][0] == 330000 &&
(int)groupedRows[1][1] == 7 &&
(int)groupedRows[1][2] == 5;
//parallel tests
interpreter.ProcessStatement(@"create table house4 (
NumBedrooms int,
NumBath int,
Price decimal,
IsListed bool,
Address varchar(50)
)");
var allHouses = new List <List <List <IComparable> > >();
interpreter.ProcessStatement("insert into house4 values (5,3,295000,true,'800 Wormwood Dr')");
Parallel.For(0, 200, i =>
{
interpreter.ProcessStatement("insert into house4 values (5,3,295000,true,'800 Wormwood Dr')");
var houses = (List <List <IComparable> >)interpreter.ProcessStatement("select * FROM house4");
allHouses.Add(houses);
});
var housesOut = (List <List <IComparable> >)interpreter.ProcessStatement("select * FROM house4");
allHouses.Add(housesOut);
var allHousesCountCorrect = allHouses.Count() == 201;
var insertCountCorrect = allHouses[200].Count() == 201;
//UPDATE TESTS
string createTable9 = @"create table houses9( Price int, NumBedRooms int, NumBathrooms int )";
interpreter.ProcessStatement(createTable9);
string insert19 = "insert into houses9 values (300000, 3, 2)";
interpreter.ProcessStatement(insert19);
string insert29 = "insert into houses9 values (310000, 4, 3)";
interpreter.ProcessStatement(insert29);
string insert39 = "insert into houses9 values (300000, 5, 4)";
interpreter.ProcessStatement(insert39);
string insert49 = "insert into houses9 values (330000, 6, 5)";
interpreter.ProcessStatement(insert49);
string insert59 = "insert into houses9 values (330000, 7, 6)";
interpreter.ProcessStatement(insert59);
string updateStatement = @"update houses9 Set Price = 440000, NumBathrooms = 90 where Numbedrooms = 7";
interpreter.ProcessStatement(updateStatement);
var updatedRows = (List <List <IComparable> >)interpreter.ProcessStatement("select * from houses9");
bool updatedOneCorrect = (int)updatedRows[4][0] == 440000;
bool updatedTwoCorrect = (int)updatedRows[4][2] == 90;
bool updatedRowsCountCorrect = updatedRows.Count() == 5;
string createTable10 = @"create table houses10( Price int, NumBedRooms int, NumBathrooms int, DateListed DateTime)";
interpreter.ProcessStatement(createTable10);
string insert60 = "insert into houses10 values (300000, 5, 4, '10/15/2021 9:03:37 pm')";
interpreter.ProcessStatement(insert60);
var housesWithDateTime = (List <List <IComparable> >)interpreter.ProcessStatement("select * from houses10 where DateListed > '10/15/2021 9:00:00 pm' ");
bool housesWithDateTimeCount = housesWithDateTime.Count == 1;
Assert.AreEqual(true, rowCountCorrect);
Assert.AreEqual(true, columnCountCorrect);
Assert.AreEqual(true, rowCountCorrect2);
Assert.AreEqual(true, columnCountCorrect2);
Assert.AreEqual(true, resultCountCorrect2);
Assert.AreEqual(true, resultCountCorrect);
Assert.AreEqual(true, toolSubQueryCompare);
Assert.AreEqual(true, compare);
Assert.AreEqual(true, groupedCountCorrect);
Assert.AreEqual(true, groupedValuesCorrect);
Assert.AreEqual(true, colCountCorrect);
Assert.AreEqual(true, orderIsCorrect);
Assert.AreEqual(true, insertCountCorrect);
Assert.AreEqual(true, updatedOneCorrect);
Assert.AreEqual(true, updatedTwoCorrect);
Assert.AreEqual(true, updatedRowsCountCorrect);
Assert.AreEqual(true, housesWithDateTimeCount);
}
