public async Task UpdateAllColumns()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync("drop table if exists test.dbo.createtableprimay;");
var services = SpiderProvider.Value.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.InsertIgnoreDuplicate);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity4).FullName;
var entity = new CreateTableEntity4();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity4>
{
entity
};
dfc.AddParseItem(typeName, items);
await storage.HandleAsync(dfc);
var dfc2 = new DataFlowContext(null, services);
dfc2.Add(typeName, entity.GetTableMetadata());
var now = DateTime.Now;
dfc2.AddParseItem(typeName, new ParseResult <CreateTableEntity4>
{
new CreateTableEntity4
{
Str1 = "TTT",
DateTime = now,
DateTimeOffset = now,
Double = 888
}
});
var storage2 = CreateStorage(StorageType.Update);
await storage2.HandleAsync(dfc2);
var list = (await conn.QueryAsync <CreateTableEntity4>("SELECT * FROM test.dbo.createtableprimay"))
.ToList();
Assert.Single(list);
entity = list.First();
Assert.Equal("TTT", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(888, entity.Double);
Assert.Equal(200.0F, entity.Float);
await conn.ExecuteAsync("drop table if exists test.dbo.createtableprimay;");
}
}
public async Task AutoIncPrimary()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableautoincprimay{Escape};");
var services = SpiderProvider.Value.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.Insert);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity5).FullName;
var entity = new CreateTableEntity5();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity5>
{
entity,
entity
};
dfc.AddParseItem(typeName, items);
await storage.HandleAsync(dfc);
var list =
(await conn.QueryAsync <CreateTableEntity5>(
$"SELECT * FROM {Escape}test{Escape}.{Escape}createtableautoincprimay{Escape}"))
.ToList();
Assert.Equal(2, list.Count);
entity = list.First();
Assert.Equal("xxx", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(400, entity.Double);
Assert.Equal(200.0F, entity.Float);
Assert.Equal(1, entity.Id);
Assert.Equal(2, list[1].Id);
var primaries = (await conn.QueryAsync <PrimaryInfo>(
$"SELECT t.CONSTRAINT_TYPE, c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c WHERE t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = 'test' AND t.CONSTRAINT_TYPE = 'PRIMARY KEY' AND t.TABLE_NAME='createtableautoincprimay';")
).ToList();
Assert.Single(primaries);
Assert.Equal("id", primaries[0].COLUMN_NAME);
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableautoincprimay{Escape};");
}
}
public async Task CreateTableAutoIncPrimary()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync("drop table if exists test.dbo.createtableautoincprimay;");
var services = SpiderProvider.Value.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.Insert);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity5).FullName;
var entity = new CreateTableEntity5();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity5>
{
entity,
entity
};
dfc.AddParseItem(typeName, items);
await storage.HandleAsync(dfc);
var list =
(await conn.QueryAsync <CreateTableEntity5>("SELECT * FROM test.dbo.createtableautoincprimay"))
.ToList();
Assert.Equal(2, list.Count);
entity = list.First();
Assert.Equal("xxx", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(400, entity.Double);
Assert.Equal(200.0F, entity.Float);
Assert.Equal(1, entity.Id);
Assert.Equal(2, list[1].Id);
var primaries = (await conn.QueryAsync <IndexInfo>
(@"USE test; EXEC sp_pkeys @table_name='createtableautoincprimay'")
).ToList();
Assert.Single(primaries);
Assert.Equal("id", primaries[0].COLUMN_NAME);
Assert.Equal(1, primaries[0].KEY_SEQ);
await conn.ExecuteAsync("drop table if exists test.dbo.createtableautoincprimay;");
}
}
//public DatabaseSpiderDataParser()
//{
// CanParse = DataParserHelper.CanParseByRegex("cnblogs\\.com");
// QueryFollowRequests = DataParserHelper.QueryFollowRequestsByXPath(".");
//}
protected override Task <DataFlowResult> Parse(DataFlowContext context)
{
context.AddItem("URL", context.Response.Request.Url);
context.AddItem("Title", context.GetSelectable().XPath(".//title").GetValue());
#region add mysql database
var typeName = typeof(EntitySpider.CnblogsEntry).FullName;
var entity = new EntitySpider.CnblogsEntry();
context.Add(typeName, entity.GetTableMetadata());
ParseResult <EntitySpider.CnblogsEntry> items = new ParseResult <EntitySpider.CnblogsEntry>();
entity.WebSite = context.Response.Request.Url;
entity.Url = context.Response.Request.Url;
entity.Title = context.GetSelectable().XPath(".//title").GetValue();
items.Add(entity);
context.AddParseItem(typeName, items);
#endregion
return(Task.FromResult(DataFlowResult.Success));
}
public async Task Store_Empty_Should_Success()
{
var serviceProvider = Mock.Of <IServiceProvider>();
var mongoClient = new Mock <IMongoClient>();
var mongoEntityStorage = new MongoEntityStorage(mongoClient.Object);
var dfc = new DataFlowContext(null, serviceProvider);
var typeName = typeof(CreateTableEntity1).FullName;
var entity = new CreateTableEntity1();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity1>();
dfc.AddParseItem(typeName, items);
var result = await mongoEntityStorage.HandleAsync(dfc);
Assert.Equal(DataFlowResult.Success, result);
}
public async Task InsertAndUpdate()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableprimay{Escape};");
var services = SpiderProvider.Value.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.InsertAndUpdate);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity4).FullName;
var entity = new CreateTableEntity4();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity4>
{
entity,
new CreateTableEntity4
{
Str1 = "zzz"
}
};
dfc.AddParseItem(typeName, items);
await storage.HandleAsync(dfc);
var list = (await conn.QueryAsync <CreateTableEntity4>(
$"SELECT * FROM {Escape}test{Escape}.{Escape}createtableprimay{Escape}"))
.ToList();
Assert.Single(list);
entity = list.First();
Assert.Equal("zzz", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(400, entity.Double);
Assert.Equal(200.0F, entity.Float);
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableprimay{Escape};");
}
}
public async Task Indexes()
{
using (var conn = CreateConnection())
{
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableindexes{Escape};");
var services = SpiderProvider.Value.CreateScopeServiceProvider();
var storage = CreateStorage(StorageType.Insert);
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity5).FullName;
var entity = new CreateTableEntity9();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity9>
{
entity
};
dfc.AddParseItem(typeName, items);
await storage.HandleAsync(dfc);
var indexes = (await conn.QueryAsync <IndexInfo>
($"show index from test.createtableindexes")
).ToList();
Assert.Equal(6, indexes.Count);
Assert.Contains(indexes,
x => x.Key_name == "INDEX_STR1" && x.Non_unique == 1 && x.Column_name == "str1");
Assert.Contains(indexes, x =>
x.Key_name == "INDEX_STR1_STR2" && x.Non_unique == 1 && x.Column_name == "str1");
Assert.Contains(indexes, x =>
x.Key_name == "INDEX_STR1_STR2" && x.Non_unique == 1 && x.Column_name == "str2");
Assert.Contains(indexes,
x => x.Key_name == "UNIQUE_STR3" && x.Non_unique == 0 && x.Column_name == "str3");
Assert.Contains(indexes, x =>
x.Key_name == "UNIQUE_STR3_STR4" && x.Non_unique == 0 && x.Column_name == "str3");
Assert.Contains(indexes, x =>
x.Key_name == "UNIQUE_STR3_STR4" && x.Non_unique == 0 && x.Column_name == "str4");
await conn.ExecuteAsync(
$"drop table if exists {Escape}test{Escape}.{Escape}createtableindexes{Escape};");
}
}
public async Task UseTransaction()
{
using (var conn = CreateConnection())
{
// 如果实体的 Schema 没有配置表名，则使用类名
await conn.ExecuteAsync("drop table if exists test.dbo.createtableprimay;");
var services = SpiderProvider.Value.CreateScopeServiceProvider();
var storage = (RelationalDatabaseEntityStorageBase)CreateStorage(StorageType.InsertIgnoreDuplicate);
storage.UseTransaction = true;
var dfc = new DataFlowContext(null, services);
var typeName = typeof(CreateTableEntity4).FullName;
var entity = new CreateTableEntity4();
dfc.Add(typeName, entity.GetTableMetadata());
var items = new ParseResult <CreateTableEntity4>
{
entity,
entity,
entity
};
dfc.AddParseItem(typeName, items);
await storage.HandleAsync(dfc);
var list = (await conn.QueryAsync <CreateTableEntity4>("SELECT * FROM test.dbo.createtableprimay"))
.ToList();
Assert.Single(list);
entity = list.First();
Assert.Equal("xxx", entity.Str1);
Assert.Equal("yyy", entity.Str2);
Assert.Equal(655, entity.Required);
Assert.Equal(0, entity.Decimal);
Assert.Equal(600, entity.Long);
Assert.Equal(400, entity.Double);
Assert.Equal(200.0F, entity.Float);
await conn.ExecuteAsync("drop table if exists test.dbo.createtableprimay;");
}
}
protected override Task <DataFlowResult> Parse(DataFlowContext context)
{
if (!context.Contains(_model.TypeName))
{
context.Add(_model.TypeName, _tableMetadata);
}
var selectable = context.GetSelectable();
var results = new ParseResult <T>();
if (selectable.Properties == null)
{
selectable.Properties = new Dictionary <string, object>();
}
var environments = new Dictionary <string, string>();
foreach (var property in context.Response.Request.Properties)
{
environments.Add(property.Key, property.Value);
}
if (_model.ShareValueSelectors != null)
{
foreach (var selector in _model.ShareValueSelectors)
{
string name = selector.Name;
var value = selectable.Select(selector.ToSelector()).GetValue();
if (!environments.ContainsKey(name))
{
environments.Add(name, value);
}
else
{
environments[name] = value;
}
}
}
bool singleExtractor = _model.Selector == null;
if (!singleExtractor)
{
var selector = _model.Selector.ToSelector();
var list = selectable.SelectList(selector).Nodes()?.ToList();
if (list != null)
{
if (_model.Take > 0 && list.Count > _model.Take)
{
list = _model.TakeFromHead
? list.Take(_model.Take).ToList()
: list.Skip(list.Count - _model.Take).ToList();
}
for (var i = 0; i < list.Count; ++i)
{
var item = list.ElementAt(i);
var obj = ParseObject(environments, item, i);
if (obj != null)
{
results.Add(obj);
}
else
{
Logger?.LogWarning($"解析到空数据，类型: {_model.TypeName}");
}
}
}
}
else
{
var obj = ParseObject(environments, selectable, 0);
if (obj != null)
{
results.Add(obj);
}
else
{
Logger?.LogWarning($"解析到空数据，类型: {_model.TypeName}");
}
}
if (results.Count > 0)
{
var items = context.GetParseItem(_model.TypeName);
if (items == null)
{
context.AddParseItem(_model.TypeName, results);
}
else
{
((ParseResult <T>)items).AddRange(results);
}
}
return(Task.FromResult(DataFlowResult.Success));
}