public virtual void Insert_NoneTimestamp()
{
using (var conn = CreateDbConnection())
{
var datetime = DateTime.Now.AddSeconds(-50);
try
{
conn.Execute($"use test; DROP TABLE timestamp;");
}
catch { }
var spider = new DefaultSpider();
var pipeline = CreatePipeline();
pipeline.AutoTimestamp = false;
var metadata = new ModelDefine <Timestamp>();
var resultItems = new ResultItems();
resultItems.Request = new Request();
resultItems.AddOrUpdateResultItem(metadata.Identity, new Tuple <IModel, IEnumerable <dynamic> >(metadata, new dynamic[]
{
new Timestamp {
Sku = "110", Category = "3C", Name = "Product 1"
},
new Timestamp {
Sku = "111", Category = "3C", Name = "Product 2"
},
new Timestamp {
Sku = "112", Category = null, Name = "Product 3"
}
}));
pipeline.Process(new ResultItems[] { resultItems }, spider);
var list = conn.Query($"use test; select * from timestamp").Select(r => r as IDictionary <string, dynamic>).ToList();
Assert.Equal(4, list[0].Count);
try
{
conn.Execute($"use test; DROP TABLE timestamp;");
}
catch { }
}
}
public virtual void Insert_NonePrimaryKey()
{
using (var conn = CreateDbConnection())
{
try
{
conn.Execute($"use test; DROP TABLE noneprimarykey;");
}
catch { }
var spider = new DefaultSpider();
var pipeline = CreatePipeline();
var metadata = new ModelDefine <NonePrimaryKey>();
var resultItems = new ResultItems();
resultItems.Request = new Request();
resultItems.AddOrUpdateResultItem(metadata.Identity, new Tuple <IModel, IEnumerable <dynamic> >(metadata, new dynamic[]
{
new NonePrimaryKey {
Sku = "110", Category = "3C", Name = "Product 1"
},
new NonePrimaryKey {
Sku = "111", Category = "3C", Name = "Product 2"
},
new NonePrimaryKey {
Sku = "112", Category = null, Name = "Product 3"
},
new NonePrimaryKey {
Sku = "110", Category = "3C", Name = "Product 1"
},
}));
pipeline.Process(new ResultItems[] { resultItems }, spider);
var list = conn.Query($"use test; select * from noneprimarykey").Select(r => r as IDictionary <string, dynamic>).ToList();
Assert.Equal(4, list.Count);
Assert.Equal("110", list[0]["sku"]);
Assert.Equal("111", list[1]["sku"]);
Assert.Null(list[2]["category"]);
try
{
conn.Execute($"use test; DROP TABLE noneprimarykey;");
}
catch { }
}
}
public void InsertByAutoTimestamp()
{
ClearDb();
using (MySqlConnection conn = new MySqlConnection(DefaultMySqlConnection))
{
conn.Execute($"DROP TABLE if exists test.sku;");
var spider = new DefaultSpider();
MySqlEntityPipeline insertPipeline = new MySqlEntityPipeline(DefaultMySqlConnection);
var metadata = new ModelDefine <ProductInsert>();
var resultItems = new ResultItems();
resultItems.Request = new Request();
var data1 = new ProductInsert {
Sku = "110", Category = "3C", Url = "http://jd.com/110"
};
var data2 = new ProductInsert {
Sku = "111", Category = "3C", Url = "http://jd.com/111"
};
var data3 = new ProductInsert {
Sku = "112", Category = null, Url = "http://jd.com/111"
};
resultItems.AddOrUpdateResultItem(metadata.Identity, new Tuple <IModel, IEnumerable <dynamic> >(metadata, new dynamic[] {
data1,
data2,
data3
}));
insertPipeline.Process(new ResultItems[] { resultItems }, spider);
var list = conn.Query($"select * from test.{metadata.TableInfo.FullName}").Select(r => r as IDictionary <string, dynamic>).ToList();
Assert.Equal(3, list.Count);
Assert.Equal(5, list[0].Count);
Assert.Equal(1, list[0]["sku"]);
Assert.Equal(2, list[1]["sku"]);
Assert.Equal(DateTime.Now.Date, list[1]["creation_date"]);
Assert.True(list[1]["creation_time"] > new DateTime(2000, 1, 1));
Assert.Null(list[2]["category"]);
conn.Execute($"DROP TABLE test.{metadata.TableInfo.FullName};");
}
}
public virtual void Insert_AutoIncrementPrimaryKey()
{
using (var conn = CreateDbConnection())
{
try
{
conn.Execute($"use test; DROP TABLE autoincrementprimarykey;");
}
catch { }
var spider = new DefaultSpider();
var pipeline = CreatePipeline();
var metadata = new ModelDefinition <AutoIncrementPrimaryKey>();
var resultItems = new ResultItems();
resultItems.Request = new Request();
resultItems.AddOrUpdateResultItem(metadata.Identity, new Tuple <IModel, IList <dynamic> >(metadata, new dynamic[]
{
new AutoIncrementPrimaryKey {
Sku = "110", Category = "3C", Name = "Product 1"
},
new AutoIncrementPrimaryKey {
Sku = "111", Category = "3C", Name = "Product 2"
},
new AutoIncrementPrimaryKey {
Sku = "112", Category = null, Name = "Product 3"
}
}));
pipeline.Process(new ResultItems[] { resultItems }, spider.Logger, spider);
var list = conn.Query($"use test; select * from autoincrementprimarykey").Select(r => r as IDictionary <string, dynamic>).ToList();
Assert.Equal(3, list.Count);
Assert.Equal(1, list[0]["id"]);
Assert.Equal(2, list[1]["id"]);
Assert.Equal(3, list[2]["id"]);
try
{
conn.Execute($"use test; DROP TABLE autoincrementprimarykey;");
}
catch { }
}
}
public virtual void DataTypes()
{
using (MySqlConnection conn = new MySqlConnection("Database='mysql';Data Source=localhost;User ID=root;Password=;Port=3306;SslMode=None;"))
{
try
{
conn.Execute("use test; drop table table15;");
}
catch
{
}
var spider = new DefaultSpider();
EntityProcessor <Entity15> processor = new EntityProcessor <Entity15>();
var pipeline = new MySqlEntityPipeline("Database='mysql';Data Source=localhost;User ID=root;Password=;Port=3306;SslMode=None;");
var resultItems = new ResultItems();
resultItems.Request = new Request();
resultItems.AddOrUpdateResultItem(processor.Model.Identity,
new Tuple <IModel, IList <dynamic> >(processor.Model, new[] {
new Dictionary <string, dynamic>
{
{ "int", "1" },
{ "bool", "1" },
{ "bigint", "11" },
{ "string", "aaa" },
{ "time", "2018-06-12" },
{ "float", "1" },
{ "double", "1" },
{ "string1", "abc" },
{ "string2", "abcdd" },
{ "decimal", "1" }
}
}));
pipeline.Process(new ResultItems[] { resultItems }, spider.Logger, spider);
var columns = conn.Query <ColumnInfo>("SELECT COLUMN_NAME as `Name`, COLUMN_TYPE as `Type` FROM information_schema.columns WHERE table_name='table15' AND table_schema = 'test';").ToList();;
Assert.Equal(12, columns.Count);
Assert.Equal("int".ToLower(), columns[0].Name);
Assert.Equal("bool".ToLower(), columns[1].Name);
Assert.Equal("bigint".ToLower(), columns[2].Name);
Assert.Equal("string".ToLower(), columns[3].Name);
Assert.Equal("time".ToLower(), columns[4].Name);
Assert.Equal("float".ToLower(), columns[5].Name);
Assert.Equal("double".ToLower(), columns[6].Name);
Assert.Equal("string1".ToLower(), columns[7].Name);
Assert.Equal("string2".ToLower(), columns[8].Name);
Assert.Equal("decimal".ToLower(), columns[9].Name);
Assert.Equal("creation_time".ToLower(), columns[10].Name);
Assert.Equal("creation_date".ToLower(), columns[11].Name);
Assert.Equal("int(11)", columns[0].Type);
Assert.Equal("tinyint(1)", columns[1].Type);
Assert.Equal("bigint(20)", columns[2].Type);
Assert.Equal("varchar(255)", columns[3].Type);
Assert.Equal("timestamp", columns[4].Type);
Assert.Equal("float", columns[5].Type);
Assert.Equal("double", columns[6].Type);
Assert.Equal("varchar(100)", columns[7].Type);
Assert.Equal("longtext", columns[8].Type);
Assert.Equal("decimal(18,2)", columns[9].Type);
Assert.Equal("timestamp", columns[10].Type);
Assert.Equal("date", columns[11].Type);
try
{
conn.Execute("use test; drop table table15;");
}
catch
{
}
}
}
public override void DataTypes()
{
if (!Env.IsWindows)
{
return;
}
using (var conn = new SqlConnection("Server=.\\SQLEXPRESS;Database=master;Trusted_Connection=True;MultipleActiveResultSets=true"))
{
try
{
conn.Execute("create database test;");
}
catch
{
}
try
{
conn.Execute("USE [test]; drop table [test].dbo.[table15]");
}
catch
{
}
var spider = new DefaultSpider();
EntityProcessor <Entity15> processor = new EntityProcessor <Entity15>();
var pipeline = new SqlServerEntityPipeline("Server=.\\SQLEXPRESS;Database=master;Trusted_Connection=True;MultipleActiveResultSets=true");
var resultItems = new ResultItems();
resultItems.Request = new Request();
resultItems.AddOrUpdateResultItem(processor.Model.Identity, new Tuple <IModel, IEnumerable <dynamic> >(processor.Model, new dynamic[] {
new Dictionary <string, dynamic>
{
{ "int", "1" },
{ "bool", "1" },
{ "bigint", "11" },
{ "string", "aaa" },
{ "time", "2018-06-12" },
{ "float", "1" },
{ "double", "1" },
{ "string1", "abc" },
{ "string2", "abcdd" },
{ "decimal", "1" }
}
}));
pipeline.Process(new ResultItems[] { resultItems }, spider);
var columns = conn.Query <ColumnInfo>("USE [test];select b.name Name,c.name+'(' + cast(c.length as varchar)+')' [Type] from sysobjects a,syscolumns b,systypes c where a.id=b.id and a.name='table15' and a.xtype='U'and b.xtype=c.xtype").ToList();
Assert.Equal(15, columns.Count);
Assert.Equal("creation_date".ToLower(), columns[0].Name);
Assert.Equal("int".ToLower(), columns[1].Name);
Assert.Equal("time".ToLower(), columns[2].Name);
Assert.Equal("creation_time".ToLower(), columns[3].Name);
Assert.Equal("float".ToLower(), columns[4].Name);
Assert.Equal("double".ToLower(), columns[5].Name);
Assert.Equal("bool".ToLower(), columns[6].Name);
Assert.Equal("decimal".ToLower(), columns[7].Name);
Assert.Equal("bigint".ToLower(), columns[8].Name);
Assert.Equal("string".ToLower(), columns[9].Name);
Assert.Equal("string1".ToLower(), columns[10].Name);
Assert.Equal("string2".ToLower(), columns[11].Name);
Assert.Equal("date(3)", columns[0].Type);
Assert.Equal("int(4)", columns[1].Type);
Assert.Equal("datetime(8)", columns[2].Type);
Assert.Equal("datetime(8)", columns[3].Type);
Assert.Equal("float(8)", columns[4].Type);
Assert.Equal("float(8)", columns[5].Type);
Assert.Equal("bit(1)", columns[6].Type);
Assert.Equal("decimal(17)", columns[7].Type);
Assert.Equal("bigint(8)", columns[8].Type);
Assert.Equal("nvarchar(8000)", columns[9].Type);
Assert.Equal("nvarchar(8000)", columns[10].Type);
Assert.Equal("nvarchar(8000)", columns[11].Type);
conn.Execute("USE [test]; drop table [test].dbo.[table15]");
}
}
