/// <summary>
/// 创建数据库和表
/// </summary>
/// <param name="conn">数据库连接</param>
/// <param name="sqlStatements">SQL 语句</param>
protected virtual void EnsureDatabaseAndTableCreated(IDbConnection conn,
SqlStatements sqlStatements)
{
if (!string.IsNullOrWhiteSpace(sqlStatements.CreateDatabaseSql))
{
conn.Execute(sqlStatements.CreateDatabaseSql);
}
conn.Execute(sqlStatements.CreateTableSql);
}
protected override SqlStatements GenerateSqlStatements(TableMetadata tableMetadata)
{
var sqlStatements = new SqlStatements
{
InsertSql = GenerateInsertSql(tableMetadata, false),
InsertIgnoreDuplicateSql = GenerateInsertSql(tableMetadata, true),
InsertAndUpdateSql = GenerateInsertAndUpdateSql(tableMetadata),
UpdateSql = GenerateUpdateSql(tableMetadata),
CreateTableSql = GenerateCreateTableSql(tableMetadata),
CreateDatabaseSql = GenerateCreateDatabaseSql(tableMetadata)
};
return(sqlStatements);
}
/// <summary>
/// 生成 SQL 语句
/// </summary>
/// <param name="tableMetadata">表元数据</param>
/// <returns></returns>
protected override SqlStatements GenerateSqlStatements(TableMetadata tableMetadata)
{
var sqlStatements = new SqlStatements
{
InsertSql = GenerateInsertSql(tableMetadata),
InsertIgnoreDuplicateSql = GenerateInsertSql(tableMetadata),
InsertAndUpdateSql = GenerateInsertAndUpdateSql(tableMetadata),
UpdateSql = GenerateUpdateSql(tableMetadata),
CreateTableSql = GenerateCreateTableSql(tableMetadata),
CreateDatabaseSql = GenerateCreateDatabaseSql(tableMetadata),
DatabaseSql = string.IsNullOrWhiteSpace(tableMetadata.Schema.Database)
? ""
: $"[{GetNameSql(tableMetadata.Schema.Database)}]"
};
return(sqlStatements);
}
/// <summary>
/// 创建数据库和表
/// </summary>
/// <param name="conn">数据库连接</param>
/// <param name="sqlStatements">SQL 语句</param>
protected override void EnsureDatabaseAndTableCreated(IDbConnection conn,
SqlStatements sqlStatements)
{
if (!string.IsNullOrWhiteSpace(sqlStatements.CreateDatabaseSql))
{
try
{
conn.Execute(sqlStatements.CreateDatabaseSql);
conn.Execute(sqlStatements.CreateDatabaseSql);
}
catch (Exception e)
{
if (e.Message != $"42P04: database {sqlStatements.DatabaseSql} already exists")
{
throw;
}
}
}
conn.Execute(sqlStatements.CreateTableSql);
}
protected override async Task <DataFlowResult> Store(DataFlowContext context)
{
IDbConnection conn = TryCreateDbConnection(context);
using (conn)
{
foreach (var item in context.GetParseItems())
{
var tableMetadata = (TableMetadata)context[item.Key];
SqlStatements sqlStatements = GetSqlStatements(tableMetadata);
lock (this)
{
EnsureDatabaseAndTableCreated(conn, sqlStatements);
}
for (int i = 0; i < RetryTimes; ++i)
{
IDbTransaction transaction = null;
try
{
if (UseTransaction)
{
transaction = conn.BeginTransaction();
}
var list = item.Value;
switch (StorageType)
{
case StorageType.Insert:
{
await conn.ExecuteAsync(sqlStatements.InsertSql, list, transaction);
break;
}
case StorageType.InsertIgnoreDuplicate:
{
await conn.ExecuteAsync(sqlStatements.InsertIgnoreDuplicateSql, list, transaction);
break;
}
case StorageType.Update:
{
if (string.IsNullOrWhiteSpace(sqlStatements.UpdateSql))
{
throw new SpiderException("未能生成更新 SQL");
}
await conn.ExecuteAsync(sqlStatements.UpdateSql, list, transaction);
break;
}
case StorageType.InsertAndUpdate:
{
await conn.ExecuteAsync(sqlStatements.InsertAndUpdateSql, list, transaction);
break;
}
}
transaction?.Commit();
break;
}
catch (Exception ex)
{
Logger?.LogError($"尝试插入数据失败: {ex}");
// 网络异常需要重试，并且不需要 Rollback
var endOfStreamException = ex.InnerException as EndOfStreamException;
if (endOfStreamException == null)
{
try
{
transaction?.Rollback();
}
catch (Exception e)
{
Logger?.LogError($"数据库回滚失败: {e}");
}
break;
}
}
finally
{
transaction?.Dispose();
}
}
}
}
return(DataFlowResult.Success);
}
protected abstract void EnsureDatabaseAndTableCreated(IDbConnection conn, SqlStatements sqlStatements);