public static void InitStatusDatabase(string connectString)
{
using (var conn = new MySqlConnection(connectString))
{
try
{
conn.Execute("CREATE DATABASE IF NOT EXISTS `dotnetspider` DEFAULT CHARACTER SET utf8;");
var dbEngine = MySqlEngine.IsSupportToku(conn) ? "TokuDB" : "InnoDB";
var sql = $"CREATE TABLE IF NOT EXISTS `dotnetspider`.`status` (`identity` varchar(120) NOT NULL,`node` varchar(120) NOT NULL,`logged` timestamp NULL DEFAULT NULL,`status` varchar(20) DEFAULT NULL,`thread` int(13),`left` bigint(20),`success` bigint(20),`error` bigint(20),`total` bigint(20),`avgdownloadspeed` float,`avgprocessorspeed` bigint(20),`avgpipelinespeed` bigint(20), PRIMARY KEY (`identity`,`node`)) ENGINE={dbEngine} DEFAULT CHARSET=utf8;";
conn.Execute(sql);
var trigger = conn.QueryFirstOrDefault($"SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = 'status_AFTER_UPDATE' and EVENT_OBJECT_SCHEMA='dotnetspider' and EVENT_OBJECT_TABLE='status'");
if (trigger == null)
{
var timeTrigger = $"CREATE TRIGGER `dotnetspider`.`status_AFTER_UPDATE` BEFORE UPDATE ON `status` FOR EACH ROW BEGIN set NEW.logged = NOW(); END";
conn.Execute(timeTrigger);
}
}
catch (Exception e)
{
Logger.MyLog("Prepare dotnetspider.status failed.", LogLevel.Error, e);
throw e;
}
}
}
public DbMonitor(string identity)
{
Identity = identity;
NetworkCenter.Current.Execute("dm", () =>
{
using (var conn = new MySqlConnection(Config.ConnectString))
{
try
{
conn.Execute("CREATE DATABASE IF NOT EXISTS `dotnetspider` DEFAULT CHARACTER SET utf8;");
var dbEngine = MySqlEngine.IsSupportToku(conn) ? "TokuDB" : "InnoDB";
var sql = $"CREATE TABLE IF NOT EXISTS `dotnetspider`.`status` (`identity` varchar(120) NOT NULL,`node` varchar(120) NOT NULL,`logged` timestamp NULL DEFAULT NULL,`status` varchar(20) DEFAULT NULL,`thread` int(13),`left` bigint(20),`success` bigint(20),`error` bigint(20),`total` bigint(20),`avgdownloadspeed` float,`avgprocessorspeed` bigint(20),`avgpipelinespeed` bigint(20), PRIMARY KEY (`identity`,`node`)) ENGINE={dbEngine} DEFAULT CHARSET=utf8;";
conn.Execute(sql);
var trigger = conn.QueryFirstOrDefault($"SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = 'status_AFTER_UPDATE' and EVENT_OBJECT_SCHEMA='dotnetspider' and EVENT_OBJECT_TABLE='status'");
if (trigger == null)
{
var timeTrigger = $"CREATE TRIGGER `dotnetspider`.`status_AFTER_UPDATE` BEFORE UPDATE ON `status` FOR EACH ROW BEGIN set NEW.logged = NOW(); END";
conn.Execute(timeTrigger);
}
}
catch (Exception e)
{
Logger.MyLog("Prepare dotnetspider.status failed.", LogLevel.Error, e);
throw e;
}
var insertSql = $"insert ignore into dotnetspider.status (`identity`, `node`, `logged`, `status`, `thread`, `left`, `success`, `error`, `total`, `avgdownloadspeed`, `avgprocessorspeed`, `avgpipelinespeed`) values (@identity, @node, current_timestamp, @status, @thread, @left, @success, @error, @total, @avgdownloadspeed, @avgprocessorspeed, @avgpipelinespeed);";
conn.Execute(insertSql,
new
{
identity = identity,
node = NodeId.Id,
status = "INIT",
left = 0,
total = 0,
success = 0,
error = 0,
avgDownloadSpeed = 0,
avgProcessorSpeed = 0,
avgPipelineSpeed = 0,
thread = 0
});
}
});
}
protected override string GetCreateTableSql(EntityDbMetadata metadata)
{
StringBuilder builder = new StringBuilder($"CREATE TABLE IF NOT EXISTS `{metadata.Table.Database }`.`{metadata.Table.Name}` (");
string columNames = string.Join(", ", metadata.Columns.Select(p => $"`{p.Name}` {GetDataTypeSql(p)} "));
builder.Append(columNames);
builder.Append(",`cdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP");
if (metadata.Table.Primary.ToLower() == Core.Infrastructure.Environment.IdColumn)
{
builder.Append($", `{Core.Infrastructure.Environment.IdColumn}` bigint AUTO_INCREMENT");
}
if (metadata.Table.Indexs != null)
{
foreach (var index in metadata.Table.Indexs)
{
var columns = index.Split(',');
string name = string.Join("_", columns.Select(c => c));
string indexColumNames = string.Join(", ", columns.Select(c => $"`{c}`"));
builder.Append($", KEY `index_{name}` ({indexColumNames.Substring(0, indexColumNames.Length)})");
}
}
if (metadata.Table.Uniques != null)
{
foreach (var unique in metadata.Table.Uniques)
{
var columns = unique.Split(',');
string name = string.Join("_", columns.Select(c => c));
string uniqueColumNames = string.Join(", ", columns.Select(c => $"`{c}`"));
builder.Append($", UNIQUE KEY `unique_{name}` ({uniqueColumNames.Substring(0, uniqueColumNames.Length)})");
}
}
builder.Append($", PRIMARY KEY ({ metadata.Table.Primary})");
using (var conn = new MySqlConnection(ConnectString))
{
var dbEngine = MySqlEngine.IsSupportToku(conn) ? "TokuDB" : "InnoDB";
builder.Append($") ENGINE={dbEngine} AUTO_INCREMENT=1 DEFAULT CHARSET=utf8");
string sql = builder.ToString();
return(sql);
}
}
