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;
}
}
}
private string GenerateSql(MySqlEngine engine, BulkCopyOptions options, Func<string> statementGenerator)
{
switch (engine)
{
case MySqlEngine.InnoDB:
return GenerateInnoDbOptimizations(options, statementGenerator);
case MySqlEngine.MyISAM:
return statementGenerator();
default:
return statementGenerator();
}
}
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 sealed IDataStoreEngine GetEngine()
{
try
{
var connectionForParsing = new MySqlConnectionStringBuilder(ConnectionString);
_ = Log.InfoAsync($"{nameof(MySqlDataContext)} connecting to {connectionForParsing.Database}");
}
catch
{
_ = Log.InfoAsync($"{nameof(MySqlDataContext)} failed to parse {ConnectionString}");
}
var engine = new MySqlEngine(ConnectionString);
return(engine);
}
public static LolipopEngine CreateNewEngine(DataEngine engine)
{
LolipopEngine en = null;
switch (engine)
{
case DataEngine.MySqlDataEngine:
en = new MySqlEngine();
break;
case DataEngine.SqlDataEngine:
en = new SqlEngine();
break;
}
return(en);
}
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);
}
}
private string CreateInsertBatchText(string insertHeader, List <string> rows, MySqlEngine engine)
{
return(GenerateSql(engine, Options.BulkCopyOptions, () => insertHeader + " " + string.Join(",", rows) + ";"));
//return new StringBuilder(insertHeader)
// .Append(string.Join(",", rows))
// .Append(";")
// .AppendLine("COMMIT;")
// .ToString();
}
// MySql only.
// TODO: Fix for MSSql & SQLite
public async Task <bool> CreateAsync <TEntity>(MySqlEngine dbEngine = MySqlEngine.MyISAM, bool replaceTable = false) where TEntity : Entity, new()
{
if (Type != DatabaseType.MySql)
{
return(false);
}
// Check if table exists or is allowed to be replaced.
if (!await ExistsAsync <TEntity>() || replaceTable)
{
// Exclude foreign key and non db related properties.
var properties = typeof(TEntity).GetReadWriteProperties();
var fields = new Dictionary <string, PropertyInfo>();
var query = new QueryBuilder <TEntity>(connectorQuery, properties);
var entity = new TEntity();
// Key: GroupStartIndex, Value: GroupCount
var groups = new ConcurrentDictionary <int, int>();
var lastGroupName = "";
var lastGroupStartIndex = 0;
// Get Groups
for (var i = 0; i < properties.Length; i++)
{
var group = properties[i].GetCustomAttribute <GroupAttribute>();
if (group != null)
{
if (group.Name == lastGroupName)
{
groups[lastGroupStartIndex] += 1;
}
else
{
lastGroupName = group.Name;
lastGroupStartIndex = i;
groups.TryAdd(lastGroupStartIndex, 1);
}
}
}
for (var i = 0; i < properties.Length; i++)
{
var groupCount = 0;
if (!properties[i].PropertyType.IsArray)
{
fields.Add(properties[i].GetName(), properties[i]);
}
else
{
if (groups.TryGetValue(i, out groupCount))
{
var arr = properties[i].GetValue(Activator.CreateInstance(typeof(TEntity))) as Array;
for (var k = 1; k <= arr.Length; k++)
{
for (var j = 0; j < groupCount; j++)
{
fields.Add(properties[i + j].GetName() + k, properties[i + j]);
}
}
i += groupCount - 1;
}
else
{
var arr = (query.PropertyGetter[i](entity) as Array);
for (var j = 1; j <= arr.Length; j++)
{
fields.Add(properties[i].GetName() + j, properties[i]);
}
}
}
}
return(await ExecuteAsync(query.BuildTableCreate(fields, dbEngine)));
}
return(false);
}
public bool Create <TEntity>(MySqlEngine dbEngine = MySqlEngine.MyISAM, bool replaceTable = false) where TEntity : Entity, new()
{
return(CreateAsync <TEntity>(dbEngine, replaceTable).GetAwaiter().GetResult());
}
internal string BuildTableCreate(Dictionary <string, PropertyInfo> fields, MySqlEngine dbEngine)
{
var pluralized = Helper.Pluralize <T>();
sqlQuery.Append($"DROP TABLE IF EXISTS `{pluralized}`;");
sqlQuery.Append($"CREATE TABLE `{pluralized}` (");
var primaryKeys = fields.Values.Where(p => p.HasAttribute <PrimaryKeyAttribute>()).ToArray();
foreach (var f in fields)
{
var isAutoIncrement = f.Value.HasAttribute <AutoIncrementAttribute>();
var fieldOptions = f.Value.GetCustomAttribute <FieldAttribute>();
var fieldType = f.Value.PropertyType.IsArray ? f.Value.PropertyType.GetElementType() : f.Value.PropertyType;
var fieldSize = fieldOptions?.Size ?? Helper.GetDefaultFieldSize(fieldType);
var defaultValue = fieldOptions?.Default ?? Helper.GetDefault(fieldType);
var nullAllowed = fieldOptions?.Null ?? false;
var typeDefinition = "";
switch (fieldType.Name)
{
case "Boolean":
typeDefinition = $"tinyint({fieldSize})";
break;
case "SByte":
typeDefinition = $"tinyint({fieldSize})";
break;
case "Byte":
typeDefinition = $"tinyint({fieldSize}) unsigned";
break;
case "Int16":
typeDefinition = $"smallint({fieldSize})";
break;
case "UInt16":
typeDefinition = $"smallint({fieldSize}) unsigned";
break;
case "Int32":
typeDefinition = $"int({fieldSize})";
break;
case "UInt32":
typeDefinition = $"int({fieldSize}) unsigned";
break;
case "Int64":
typeDefinition = $"bigint({fieldSize})";
break;
case "UInt64":
typeDefinition = $"bigint({fieldSize}) unsigned";
break;
case "Single":
typeDefinition = $"float({fieldSize})";
break;
case "Double":
typeDefinition = $"double({fieldSize})";
break;
case "String":
nullAllowed = true;
if (fieldSize <= 255)
{
typeDefinition = $"varchar({fieldSize})";
}
else
{
typeDefinition = "text(0)";
}
break;
default:
break;
}
if (!nullAllowed)
{
typeDefinition += " NOT NULL";
}
typeDefinition += $" DEFAULT '{defaultValue}'";
if (isAutoIncrement)
{
typeDefinition += " AUTO_INCREMENT";
}
sqlQuery.Append($" `{f.Key}` {typeDefinition},");
}
if (primaryKeys.Length > 0)
{
sqlQuery.Append("PRIMARY KEY (");
}
for (var i = 0; i < primaryKeys.Length; i++)
{
if (i == primaryKeys.Length - 1)
{
sqlQuery.Append($"`{primaryKeys[i].Name}`)");
}
else
{
sqlQuery.Append($"`{primaryKeys[i].Name}`,");
}
}
sqlQuery.Append($") ENGINE={Enum.GetName(typeof(MySqlEngine), dbEngine)} DEFAULT CHARSET=utf8;");
sqlQuery.Replace("', ),", "'),");
sqlQuery.Replace("', );", "');");
sqlQuery.Replace("',)", "')");
return(sqlQuery.ToString());
}
private string CreateUpdateBatchText(StringBuilder updateSql, MySqlEngine engine)
{
return GenerateSql(engine, Options.BulkCopyOptions, () => updateSql.ToString());
}
public bool Create <TEntity>(MySqlEngine dbEngine = MySqlEngine.MyISAM, bool replaceTable = false) where TEntity : Entity, new()
{
return(RunSync(() => CreateAsync <TEntity>(dbEngine, replaceTable)));
}
