public async Task NoAccessSelectTest()
{
var reqeust = new SqlessEditRequest()
{
Table = Tables.User,
Fields = new List <SqlessEditField>()
{
new SqlessEditField()
{
Field = "Password",
Value = "1",
Type = System.Data.DbType.String
}
}
};
using Sqless sqless = new Sqless(Global.GetOwnerAccessConfig("1"));
try
{
var result = await sqless.Update(reqeust);
}
catch (UnauthorizedAccessException)
{
Assert.IsTrue(true);
return;
}
Assert.IsTrue(false);
}
public async Task CountDistinctTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var selectResult = await sqless.Count(SqlessCountDistinctRequest);
Assert.IsTrue(selectResult > 0);
}
public async Task SelectTest()
{
using Sqless sqless = new Sqless(SelectConfig);
var result = await sqless.Select(SelectRequest);
Assert.IsTrue(result.Count > 0);
}
public async Task UpdateTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var result = await sqless.Update(UpdateRequest);
Assert.IsTrue(result > 0);
}
public async Task SelectNullTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var selectResult = await sqless.Select(SelectNullTestRequest);
Assert.IsTrue(selectResult.Count > 0);
}
public async Task SelectFieldTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var selectResult = await sqless.Select(SelectFieldTestRequest);
Assert.IsTrue(selectResult.Count > 0 && (selectResult[0].Uid) == "1");
}
public async Task UpsertInsertTest()
{
var newUid = "UpsertTest_" + Guid.NewGuid().ToString();
var request = new SqlessEditRequest()
{
Table = Tables.User,
Queries = new List <Query.SqlessQuery>()
{
new Query.SqlessQuery()
{
Field = "Uid",
Value = newUid,
Type = Query.SqlessQueryType.Equal
}
}
};
request.LoadFromObject(new
{
Uid = newUid,
Name = DateTime.Now.ToString("hhmmss"),
Password = new Random().Next(100000, 999999).ToString()
});
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var result = await sqless.Upsert(request);
Assert.IsTrue(result > 0);
}
public async Task CountDistinctSqlTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
SqlessCountSqlBuilder producer = new SqlessCountSqlBuilder(sqless, SqlessCountDistinctRequest);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "SELECT COUNT(DISTINCT [Uid]) FROM [User]");
}
public async Task SelectSqlTest()
{
using Sqless sqless = new Sqless(SelectConfig);
SqlessSelectSqlBuilder producer = new SqlessSelectSqlBuilder(sqless, SelectRequest);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "SELECT TOP 2 [Name],[Price] FROM [Product]");
}
public async Task SelectPageSqlTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
SqlessSelectSqlBuilder producer = new SqlessSelectSqlBuilder(sqless, SelectPageTestRequest);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "SELECT [Uid],[Name] FROM [User] ORDER BY [Uid] Asc OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY");
}
public async Task SelectNullSqlTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
SqlessSelectSqlBuilder producer = new SqlessSelectSqlBuilder(sqless, SelectNullTestRequest);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "SELECT [Name] FROM [User] WHERE [Phone] IS NULL");
}
public async Task SelectFieldSqlTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
SqlessSelectSqlBuilder producer = new SqlessSelectSqlBuilder(sqless, SelectFieldTestRequest);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "SELECT TOP 20 [Uid],[Name] FROM [User]");
}
public async Task GetSqlStrTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
SqlessUpdateSqlBuilder producer = new SqlessUpdateSqlBuilder(sqless, UpdateRequest);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "UPDATE [User] SET [Phone] = @Phone,[Password] = @Password WHERE [Uid] = @Query0");
}
public async Task GetSqlStrTest()
{
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
SqlessInsertSqlBuilder producer = new SqlessInsertSqlBuilder(sqless, InsertRequest);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "INSERT INTO [User] ([Uid],[Name],[Password],[Phone]) VALUES (@Uid,@Name,@Password,@Phone)");
}
public async Task DeleteTest()
{
var id = await Insert();
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var result = await sqless.Delete(GetDeleteSqlTestRequest(id));
Assert.IsTrue(result > 0);
}
public async Task DeleteSqlTest()
{
var id = await Insert();
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
SqlessDeleteSqlBuilder producer = new SqlessDeleteSqlBuilder(sqless, GetDeleteSqlTestRequest(id));
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "DELETE FROM [User] WHERE [Uid] = @Query0");
}
public async Task RightJoinTest()
{
var request = JoinRequest;
request.Joins[0].SqlessJoinType = SqlessJoinType.RightJoin;
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var selectResult = await sqless.Select(request);
Assert.IsTrue(selectResult.Count > 0);
}
public async Task RightJoinSqlTest()
{
var request = JoinRequest;
request.Joins[0].SqlessJoinType = SqlessJoinType.RightJoin;
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
SqlessSelectSqlBuilder producer = new SqlessSelectSqlBuilder(sqless, request);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "SELECT [User].[Name],[User].[Uid],[Order].[Time] FROM [User] RIGHT JOIN [Order] ON [User].[Uid] = [Order].[Uid] WHERE [User].[Phone] IS NOT NULL");
}
/// <summary>
/// 获取用户ID
/// </summary>
/// <param name="strs">第一个参数为Token，第二个参数可选，比如根据Platform有不同Token</param>
/// <returns></returns>
public async Task <string> GetUid(params string[] strs)
{
var request = GetRequest(strs);
using Sqless sqless = new Sqless(SqlessConfig);
var uid = await sqless.SelectFirstOrDefault <string>(request);
if (string.IsNullOrEmpty(uid))
{
throw new SqlessUnauthorizedAccessException();
}
return(uid);
}
public async Task UnspecifiedFreeTableSelectTest()
{
var request = JsonConvert.DeserializeObject <SqlessSelectRequest>(JsonConvert.SerializeObject(SelectRequest));
request.Table = Tables.AccessToken;
using Sqless sqless = new Sqless(SelectConfig);
try
{
var result = await sqless.Select(request);
}
catch (UnauthorizedAccessException)
{
Assert.IsTrue(true);
return;
}
Assert.IsTrue(false);
}
public async Task InsertTest2()
{
var request = new SqlessEditRequest()
{
Table = Tables.Product
};
request.LoadFromObject(new Product()
{
Id = Guid.NewGuid().ToString(),
Name = new Random().Next(100000, 999999).ToString(),
Price = new Random().Next(1, 100)
});
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var result = await sqless.Insert(request);
Assert.IsTrue(result > 0);
}
public async Task AccessSelectTest()
{
var request = new SqlessSelectRequest()
{
Table = Tables.User,
PageSize = 2,
Fields = new List <Query.SqlessField>
{
new Query.SqlessField()
{
Field = "Name"
}
}
};
using Sqless sqless = new Sqless(Global.GetOwnerAccessConfig("1"));
var result = await sqless.Select(request);
Assert.IsTrue(result.Count > 0);
}
public async Task AccessSelectSqlTest()
{
var request = new SqlessSelectRequest()
{
Table = Tables.User,
PageSize = 2,
Fields = new List <Query.SqlessField>
{
new Query.SqlessField()
{
Field = "Name"
}
}
};
using Sqless sqless = new Sqless(Global.GetOwnerAccessConfig("1"));
SqlessSelectSqlBuilder producer = new SqlessSelectSqlBuilder(sqless, request);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(sqlStr.Replace(" ", " ").Trim(), "SELECT TOP 2 [Name] FROM [User] WHERE [User].[Uid] = '1'");
}
public async Task UpdateOwnerField1Test()
{
var request = new SqlessEditRequest()
{
Table = Tables.User,
Fields = new List <SqlessEditField>()
{
new SqlessEditField()
{
Field = "Name",
Value = "1",
Type = System.Data.DbType.String
}
}
};
using Sqless sqless = new Sqless(Global.GetOwnerAccessConfig("1"));
var result = await sqless.Update(request);
Assert.IsTrue(result > 0);
}
public async Task InsertTest3()
{
var request = new SqlessEditRequest()
{
Table = Tables.Order
};
request.LoadFromObject(new Order()
{
Id = Guid.NewGuid().ToString(),
ProductId = new Random().Next(1, 4).ToString(),
Uid = new Random().Next(1, 5).ToString(),
Status = 1,
Time = DateTimeOffset.Now.ToUnixTimeSeconds()
});
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var result = await sqless.Insert(request);
Assert.IsTrue(result > 0);
}
private async Task <string> Insert()
{
var id = "DeleteTest_" + Guid.NewGuid().ToString("D");
var request = new SqlessEditRequest()
{
Table = Tables.User
};
request.LoadFromObject(new User
{
Uid = id,
Name = DateTime.Now.ToString("hhmmss"),
Password = new Random().Next(100000, 999999).ToString()
});
using Sqless sqless = new Sqless(Global.AllowUnspecifiedConfig);
var result = await sqless.Insert(request);
Assert.IsTrue(result > 0);
return(id);
}
public async Task UpdateOwnerFieldSqlTest()
{
var insertRequest = new SqlessEditRequest()
{
Table = Tables.User,
Fields = new List <SqlessEditField>()
{
new SqlessEditField()
{
Field = "Uid",
Value = "2",
Type = System.Data.DbType.String
}
}
};
using Sqless insertSqless = new Sqless(Global.GetOwnerAccessConfig("1"));
SqlessInsertSqlBuilder producer = new SqlessInsertSqlBuilder(insertSqless, insertRequest);
var sqlStr = await producer.GetSqlStrTest();
Assert.AreEqual(insertRequest.Fields[0].Value, "1");
}
public async Task UpdateOwnerFieldTest()
{
var updateRequest = new SqlessEditRequest()
{
Table = Tables.User,
Fields = new List <SqlessEditField>()
{
new SqlessEditField()
{
Field = "Uid",
Value = "2",
Type = System.Data.DbType.String
}
}
};
using Sqless updateSqless = new Sqless(Global.GetOwnerAccessConfig("1"));
var updateResult = await updateSqless.Update(updateRequest);
Assert.IsTrue(updateResult > 0);
var selectRequest = new SqlessSelectRequest()
{
Table = Tables.User,
Fields = new List <Query.SqlessField>()
{
new SqlessField()
{
Field = "Uid"
}
}
};
using Sqless selectSqless = new Sqless(Global.GetOwnerAccessConfig("1"));
var selectResult = await selectSqless.SelectFirstOrDefault <string>(selectRequest);
Assert.AreEqual(selectResult, "1");
}
public async Task InsertTest()
{
var order = new Order()
{
Id = Guid.NewGuid().ToString(),
ProductId = "1",
Status = 1,
Time = DateTimeOffset.Now.ToUnixTimeSeconds(),
};
var request = new SqlessEditRequest()
{
Table = Tables.Order,
AccessParams = new string[] { "AccessTestUid", "123456" },
};
request.LoadFromObject(order);
var uid = await Global.PasswordAccessConfig.GetUid(request.AccessParams);
using Sqless sqless = new Sqless(Global.GetOwnerAccessConfig(uid));
var result = await sqless.Insert(request);
Assert.IsTrue(result > 0);
}
public virtual async Task <ActionResult> Delete(SqlessDeleteRequest request)
{
using Sqless sqless = await GetSqless(request);
return(Ok(await sqless.Delete(request)));
}
