public async Task <TEntity> InsertThenGet(TEntity entity, int?timeoutSeconds = null)
{
using (var connection = GetConnection())
{
connection.Open();
var tableName = DbConverter <TEntity> .ToTableName();
var columnNames = DbConverter <TEntity> .ToColumnNames();
var parameterNames = DbConverter <TEntity> .ToParameterNames();
var sql = QueryGenerator.GenerateInsertQuery(tableName, columnNames, parameterNames);
using (var transaction = connection.BeginTransaction())
{
var insertResult = await connection.ExecuteAsync(sql, entity, commandTimeout : timeoutSeconds);
if (insertResult == 1)
{
const string getLastIdQuery = "SELECT LAST_INSERT_ID();";
entity.Id = (await connection.QueryAsync <int>(getLastIdQuery, null, commandTimeout: timeoutSeconds)).First();
}
else
{
throw new Exception($"Inserted result must equal to 1: {insertResult}");
}
transaction.Commit();
}
connection.Close();
return(entity);
}
}
public bool TestDbToStringConverter()
{
var table = DbConverter <Team> .ToTableName();
var columnNames = DbConverter <Team> .ToColumnNames(false);
_logger.Information($"TestDbToStringConverter(): {table} - {columnNames}");
return(true);
}
private ItemCategory getFromDbRow(DataRow row)
{
return(new ItemCategory
{
Id = DbConverter.ToInt32(row, "Id"),
Comment = DbConverter.ToString(row, "Comment"),
DisplayName = DbConverter.ToString(row, "DisplayName"),
Name = DbConverter.ToString(row, "Name")
});
}
public async void OnExecution(object sender, CommandEventArgs command)
{
var bot = sender as Pieprz;
var stopwatch = new Stopwatch();
stopwatch.Start();
await using var db = PerchikDB.GetContext();
var message = command.Message;
var dateNow = DbConverter.ToEpochTime(DateTime.UtcNow.Date);
var now = DbConverter.ToEpochTime(DateTime.UtcNow);
var users = db.Users
.AsNoTracking()
.Select(x => new
{
x.Id,
x.FirstName,
x.LastName,
activity = x.Messages.Where(m => m.Date > dateNow).Sum(m => m.Text.Length) /
(double)db.Messages.Where(m => m.Date > dateNow).Sum(m => m.Text.Length)
})
.ToList();
var usersDescending = users.OrderByDescending(x => x.activity);
var msgString = "*Топ 10 по активности за сегодня:*\n";
for (var i = 0; i < 10 && i < users.Count; i++)
{
var user = usersDescending?.ElementAt(i);
if (Math.Abs(user.activity) < 0.01)
{
continue;
}
var firstName = user.FirstName?.Replace('[', '<').Replace(']', '>');
var lastName = user.LastName?.Replace('[', '<').Replace(']', '>');
var fullName = $"`{firstName} {lastName}`";
msgString += $"{i + 1}. {fullName} -- {user.activity * 100:F2}%\n";
}
stopwatch.Stop();
await bot.SendTextMessageAsync(
chatId : message.Chat.Id,
text : $"{msgString}\n`{stopwatch.ElapsedMilliseconds / 1000.0}сек`",
parseMode : ParseMode.Markdown);
}
private static void Bind(object data, IDataRecord row, DbRecordInfo info)
{
var identityRecordInfo = info as DbIdentityRecordInfo;
if (identityRecordInfo != null)
{
DbFieldInfo field = identityRecordInfo.PrimaryKey;
object value = row[field.Name];
if (value == DBNull.Value)
{
if (field.DefaultValue != null)
{
value = field.DefaultValue;
}
}
else
{
value = DbConverter.GetValue(field, value);
}
setValue(field, data, value);
}
int count = info.Fields.Length;
for (int j = 0; j < count; j++)
{
DbFieldInfo field = info.Fields[j];
object value = row[field.Name];
if (value == DBNull.Value)
{
if (field.DefaultValue != null)
{
value = field.DefaultValue;
}
}
else
{
value = DbConverter.GetValue(field, value);
}
setValue(field, data, value);
// i++;
}
}
/// <summary>
/// returns Key-Value collection
/// </summary>
/// <param name="data"></param>
/// <param name="argsToAppend"></param>
/// <returns></returns>
public object [] GetValues(object data, params object[] argsToAppend)
{
var args = new object[Fields.Length * 2 + argsToAppend.Length];
int j = 0;
for (int i = 0; i < Fields.Length; i++)
{
args[j++] = Fields[i].Name;
object value = Fields[i].GetValue(data);
args[j++] = DbConverter.SetValue(Fields[i], value);
}
argsToAppend.CopyTo(args, j);
return(args);
}
public async Task <int> DeleteAsync(IEnumerable <TEntity> entities, string condition = "Id = @Id", int?timeoutSeconds = null)
{
using (var connection = GetConnection())
{
connection.Open();
var tableName = DbConverter <TEntity> .ToTableName();
var sql = QueryGenerator.GenerateDeleteQuery(tableName, condition);
int deleteResult;
using (var transaction = connection.BeginTransaction())
{
deleteResult = await connection.ExecuteAsync(sql, entities, commandTimeout : timeoutSeconds);
transaction.Commit();
}
connection.Close();
return(deleteResult);
}
}
private static void setValue(DbFieldInfo field, object data, object value)
{
try
{
if (value == DBNull.Value)
{
field.SetValue(data, null);
}
else
{
Type type = DbConverter.GetType(field.FieldType);
object convertedValue = Convert.ChangeType(value, type);
field.SetValue(data, convertedValue);
}
}
catch (Exception ex)
{
throw new NdbException("Can't set field value.\r\nField: " + field.Name + "\r\nValue: '" + value + "'\r\nError: " + ex.Message);
}
}
private Catering getFromDbRow(DataRow row)
{
var catering = new Catering
{
Id = DbConverter.ToInt32(row, nameof(Catering.Id)),
Name = DbConverter.ToString(row, nameof(Catering.Name)),
Comment = DbConverter.ToString(row, nameof(Catering.Comment)),
AddressText = DbConverter.ToString(row, nameof(Catering.AddressText)),
Latitude = DbConverter.ToDecimal(row, nameof(Catering.Latitude)),
Longitude = DbConverter.ToDecimal(row, nameof(Catering.Longitude)),
CateringCategory = new CateringCategory
{
Id = DbConverter.ToInt32(row, "CategoryId"),
Name = DbConverter.ToString(row, "CateringName"),
Comment = DbConverter.ToString(row, "CateringComment"),
DisplayName = DbConverter.ToString(row, "DisplayName"),
}
};
return(catering);
}
private SqlParameter SetParameter(Parameter parameter)
{
switch (parameter.ParameterType)
{
case ParameterType.INT:
return(new SqlParameter()
{
DbType = DbType.Int32, ParameterName = parameter.Name, Value = DbConverter.ToDbInteger(parameter.Value)
});
case ParameterType.VARCHAR:
case ParameterType.NVARCHAR:
case ParameterType.TEXT:
return(new SqlParameter()
{
DbType = DbType.String, Size = parameter.Size, ParameterName = parameter.Name, Value = DbConverter.ToDbString(parameter.Value)
});
case ParameterType.MONEY:
return(new SqlParameter()
{
DbType = DbType.Currency, ParameterName = parameter.Name, Value = DbConverter.ToDbDecimal(parameter.Value)
});
case ParameterType.DECIMAL:
return(new SqlParameter()
{
DbType = DbType.Decimal, ParameterName = parameter.Name, Value = DbConverter.ToDbDecimal(parameter.Value)
});
case ParameterType.GUID:
return(new SqlParameter()
{
DbType = DbType.Guid, ParameterName = parameter.Name, Value = DbConverter.ToDbGuid(parameter.Value)
});
default:
throw new Exception("unknown parameter type.");
}
}
public async Task <int> InsertAsync(IEnumerable <TEntity> entities, int?timeoutSeconds = null)
{
using (var connection = GetConnection())
{
connection.Open();
var tableName = DbConverter <TEntity> .ToTableName();
var columnNames = DbConverter <TEntity> .ToColumnNames();
var parameterNames = DbConverter <TEntity> .ToParameterNames();
var sql = QueryGenerator.GenerateInsertQuery(tableName, columnNames, parameterNames);
int insertResult;
using (var transaction = connection.BeginTransaction())
{
insertResult = await connection.ExecuteAsync(sql, entities, commandTimeout : timeoutSeconds);
transaction.Commit();
}
connection.Close();
return(insertResult);
}
}
public async void OnExecution(object sender, CommandEventArgs command)
{
try
{
var bot = sender as Pieprz;
if (string.IsNullOrEmpty(command.Text))
{
if (bot != null)
{
await bot.SendTextMessageAsync(
chatId : command.Message.Chat.Id,
text : StringManager.FromFile("aboutusage.txt"),
parseMode : ParseMode.Markdown);
}
return;
}
var msg = command.Message;
await using var db = PerchikDB.GetContext();
await db.UpsertUser(DbConverter.GenUser(msg.From, command.Text), msg.Chat.Id);
if (bot != null)
{
await bot.SendTextMessageAsync(
chatId : command.Message.Chat.Id,
replyToMessageId : command.Message.MessageId,
text : "*Описание обновлено.*",
parseMode : ParseMode.Markdown);
}
}
catch (Exception exp)
{
Logger.Log(LogType.Error, $"Exception: {exp.Message}\nTrace: {exp.StackTrace}");
}
}
public async void OnExecution(object sender, RegExArgs command)
{
var bot = sender as Pieprz;
var message = command.Message;
if (message.Chat.Type == ChatType.Private)
{
return;
}
const int defaultSecond = 40;
var seconds = defaultSecond;
var number = defaultSecond;
var word = "сек.";
var comment = "...";
if (command.Match.Success)
{
if (command.Match.Groups["number"].Value != string.Empty)
{
number = int.Parse(command.Match.Groups["number"].Value);
seconds = number;
}
if (command.Match.Groups["letter"].Value != string.Empty)
{
switch (command.Match.Groups["letter"].Value.First())
{
case 'с':
seconds = number;
word = "сек.";
break;
case 'м':
seconds *= 60;
word = "мин.";
break;
case 'ч':
word = "ч.";
seconds *= 3600;
break;
case 'д':
word = "д.";
seconds *= 86400;
break;
}
}
if (command.Match.Groups["comment"].Value != string.Empty)
{
comment = command.Match.Groups["comment"].Value;
}
}
try
{
if (message.ReplyToMessage != null)
{
if (!bot.IsUserAdmin(message.Chat.Id, message.From.Id) &&
message.From.Id != ViaTcpId)
{
return;
}
if (message.ReplyToMessage.From.Id == bot.BotId)
{
return;
}
await(sender as Pieprz).FullyRestrictUserAsync(
chatId: message.Chat.Id,
userId: message.ReplyToMessage.From.Id,
forSeconds: seconds);
if (seconds >= defaultSecond)
{
await bot.SendTextMessageAsync(
chatId : message.Chat.Id,
text : string.Format(Program.strManager.GetSingle("BANNED"), bot.MakeUserLink(message.ReplyToMessage.From), number, word, comment, bot.MakeUserLink(message.From)),
parseMode : ParseMode.Markdown);
}
else
{
seconds = int.MaxValue;
await bot.SendTextMessageAsync(
chatId : message.Chat.Id,
text : string.Format(Program.strManager.GetSingle("SELF_PERMANENT"), bot.MakeUserLink(message.ReplyToMessage.From), number, word, comment),
parseMode : ParseMode.Markdown);
}
await using (var db = PerchikDB.GetContext())
{
var restriction = DbConverter.GenRestriction(message.ReplyToMessage, DateTime.UtcNow.AddSeconds(seconds));
db.AddRestriction(restriction);
}
await bot.DeleteMessageAsync(message.Chat.Id, message.MessageId);
}
else
{
if (seconds >= defaultSecond)
{
await(sender as Pieprz).FullyRestrictUserAsync(
chatId: message.Chat.Id,
userId: message.From.Id,
forSeconds: seconds);
await bot.SendTextMessageAsync(
chatId : message.Chat.Id,
string.Format(Program.strManager.GetSingle("SELF_BANNED"), bot.MakeUserLink(message.From), number, word, comment),
parseMode : ParseMode.Markdown);
await using var db = PerchikDB.GetContext();
var restriction = DbConverter.GenRestriction(message, DateTime.UtcNow.AddSeconds(seconds));
db.AddRestriction(restriction);
}
else
{
await(sender as Pieprz).FullyRestrictUserAsync(
chatId: message.Chat.Id,
userId: message.From.Id);
await bot.SendTextMessageAsync(
chatId : message.Chat.Id,
text : String.Format(Program.strManager.GetSingle("SELF_BANNED"), bot.MakeUserLink(message.From), 40, word, comment),
parseMode : ParseMode.Markdown);
await using var db = PerchikDB.GetContext();
var restriction = DbConverter.GenRestriction(message.ReplyToMessage, DateTime.UtcNow.AddSeconds(40));
db.AddRestriction(restriction);
}
await bot.DeleteMessageAsync(message.Chat.Id, message.MessageId);
}
}
catch (Exception exp)
{
Logger.Log(LogType.Error, $"Exception: {exp.Message}\nTrace: {exp.StackTrace}");
}
}
public async Task Convert()
{
DatabaseType sourceDbType = this.sourceInterpreter.DatabaseType;
DatabaseType targetDbType = this.targetInterpreter.DatabaseType;
int dataBatchSize = 500;
DbInterpreterOption sourceScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.WriteToString
};
DbInterpreterOption targetScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = (GenerateScriptOutputMode.WriteToFile | GenerateScriptOutputMode.WriteToString)
};
this.sourceInterpreter.Option = sourceScriptOption;
this.targetInterpreter.Option = targetScriptOption;
GenerateScriptMode scriptMode = GenerateScriptMode.Schema | GenerateScriptMode.Data;
DbConveterInfo source = new DbConveterInfo()
{
DbInterpreter = sourceInterpreter
};
DbConveterInfo target = new DbConveterInfo()
{
DbInterpreter = targetInterpreter
};
try
{
using (DbConverter dbConverter = new DbConverter(source, target))
{
dbConverter.Option.GenerateScriptMode = scriptMode;
dbConverter.Subscribe(this);
if (sourceDbType == DatabaseType.MySql)
{
source.DbInterpreter.Option.InQueryItemLimitCount = 2000;
}
if (targetDbType == DatabaseType.SqlServer)
{
target.DbOwner = "dbo";
}
else if (targetDbType == DatabaseType.MySql)
{
target.DbInterpreter.Option.RemoveEmoji = true;
}
dbConverter.Option.SplitScriptsToExecute = true;
FeedbackHelper.EnableLog = true;
await dbConverter.Convert();
}
}
catch (Exception ex)
{
string msg = ExceptionHelper.GetExceptionDetails(ex);
this.Feedback(new FeedbackInfo()
{
InfoType = FeedbackInfoType.Error, Message = msg
});
}
}
internal string GetSqlType(DbFieldInfo fieldInfo)
{
Type type = DbConverter.GetType(fieldInfo.FieldType);
return(GetSqlType(type, fieldInfo.Size));
}
private static string GetStatisticsText(string search)
{
var sw = new Stopwatch();
sw.Start();
using var db = PerchikDB.GetContext();
var name = search.Replace("@", "");
var today = DbConverter.ToEpochTime(DateTime.UtcNow.Date);
var lastday = DbConverter.ToEpochTime(DateTime.UtcNow.AddDays(-1).Date);
var user = db.Users
.AsNoTracking()
.Where(u =>
(u.FirstName.Contains(name, StringComparison.OrdinalIgnoreCase)) ||
(u.LastName.Contains(name, StringComparison.OrdinalIgnoreCase)) ||
(u.UserName.Contains(name, StringComparison.OrdinalIgnoreCase)))
.Select(x => new
{
x.Id,
x.Restricted,
x.Description,
x.FirstName,
x.LastName,
x.UserName,
x.Restrictions.OrderByDescending(x => x.Until).FirstOrDefault().Until,
msgLastday = x.Messages.Count(m => m.Date > lastday && m.Date < today),
msgToday = x.Messages.Count(m => m.Date > today),
msgTotal = x.Messages.Count,
RestrictionCount = x.Restrictions.Count,
activity = x.Messages.Where(m => m.Date > today).Sum(m => m.Text.Length) /
(double)db.Messages.Where(m => m.Date > today).Sum(m => m.Text.Length)
})
.FirstOrDefault();
if (user == null)
{
throw new Exception($"*Пользователя \"{search}\" нет в базе.*");
}
var remaining = new TimeSpan(0);
if (user.Restricted)
{
remaining = user.Until - DateTime.UtcNow;
}
sw.Stop();
return($"*Имя: {user.FirstName} {user.LastName}\n*" +
$"*ID: {user.Id}\n*" +
$"*Ник: {user.UserName}*\n\n" +
string.Format("*Активность:* {0:F2}%\n", user.activity * 100) +
$"*Сообщений сегодня:* { user.msgToday }\n" +
$"*Сообщений вчера:* { user.msgLastday }\n" +
$"*Всего сообщений:* { user.msgTotal }\n" +
$"*Банов:* { user.RestrictionCount }\n\n" +
(user.Description != null ? $"*О себе:* \n_{ user.Description }_\n\n" : "") +
(remaining.Ticks != 0 ? $"💢`Сейчас забанен, осталось: { $"{remaining:hh\\:mm\\:ss}`\n" }" : "") +
$"`{sw.ElapsedMilliseconds / 1000.0}сек`");
}
public async void OnExecution(object sender, CommandEventArgs command)
{
var bot = sender as Pieprz;
var msg = command.Message;
await using var db = Db.PerchikDB.GetContext();
var pidr =
db.Pidrs
.AsNoTracking()
.Where(p => p.ChatId == msg.Chat.Id && p.Date.Date == DbConverter.DateTimeUtc2.Date)
.Select(x => new
{
x.UserId,
x.User.FirstName
})
.FirstOrDefault();
if (pidr == null)
{
await bot.SendChatActionAsync(msg.Chat.Id, ChatAction.Typing);
await bot.SendTextMessageAsync(
chatId : msg.Chat.Id,
text : Program.strManager["PIDR_ONE"],
parseMode : ParseMode.Markdown);
await bot.SendChatActionAsync(msg.Chat.Id, ChatAction.Typing);
await Task.Delay(2000);
await bot.SendTextMessageAsync(
chatId : msg.Chat.Id,
text : Program.strManager["PIDR_TWO"],
parseMode : ParseMode.Markdown);
await bot.SendChatActionAsync(msg.Chat.Id, ChatAction.Typing);
await Task.Delay(1000);
await bot.SendTextMessageAsync(
chatId : msg.Chat.Id,
text : Program.strManager["PIDR_THREE"],
parseMode : ParseMode.Markdown);
await bot.SendChatActionAsync(msg.Chat.Id, ChatAction.Typing);
await Task.Delay(5000);
var lastday = DbConverter.ToEpochTime(DateTime.UtcNow.AddDays(-1).Date);
var users =
db.Users
.AsNoTracking()
.Where(u => u.Messages.Any(m => m.Date > lastday && m.ChatId == msg.Chat.Id))
.Select(x => new
{
x.Id,
x.FirstName
}).ToList();
var newPidr = users[new Random(DbConverter.DateTimeUtc2.Second).Next(0, users.Count)];
await bot.SendTextMessageAsync(
chatId : msg.Chat.Id,
text : string.Format(Program.strManager["PIDR_DONE"], newPidr.FirstName, newPidr.Id),
parseMode : ParseMode.Markdown);
await db.Pidrs.AddAsync(new Db.Tables.Pidr()
{
UserId = newPidr.Id,
ChatId = msg.Chat.Id,
Date = DbConverter.DateTimeUtc2
});
await db.SaveChangesAsync();
}
else
{
await bot.SendTextMessageAsync(
chatId : msg.Chat.Id,
text : string.Format(Program.strManager["PIDR_EXIST"], pidr.FirstName),
parseMode : ParseMode.Markdown);
}
}
private async Task Convert()
{
SchemaInfo schemaInfo = this.tvDbObjects.GetSchemaInfo();
if (!this.ValidateSource(schemaInfo))
{
return;
}
if (this.targetDbConnectionInfo == null)
{
MessageBox.Show("Target connection info is null.");
return;
}
if (this.sourceDbConnectionInfo.Server == this.targetDbConnectionInfo.Server && this.sourceDbConnectionInfo.Database == this.targetDbConnectionInfo.Database)
{
MessageBox.Show("Source database cannot be equal to the target database.");
return;
}
DatabaseType sourceDbType = this.useSourceConnector ? this.sourceDbProfile.DatabaseType : this.sourceDatabaseType;
DatabaseType targetDbType = this.targetDbProfile.DatabaseType;
DbInterpreterOption sourceScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.None, SortObjectsByReference = true, GetTableAllObjects = true
};
DbInterpreterOption targetScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = (GenerateScriptOutputMode.WriteToString)
};
this.SetGenerateScriptOption(sourceScriptOption, targetScriptOption);
if (this.chkGenerateSourceScripts.Checked)
{
sourceScriptOption.ScriptOutputMode = sourceScriptOption.ScriptOutputMode | GenerateScriptOutputMode.WriteToFile;
}
if (this.chkOutputScripts.Checked)
{
targetScriptOption.ScriptOutputMode = targetScriptOption.ScriptOutputMode | GenerateScriptOutputMode.WriteToFile;
}
if (this.chkTreatBytesAsNull.Checked)
{
sourceScriptOption.TreatBytesAsNullForReading = true;
targetScriptOption.TreatBytesAsNullForExecuting = true;
}
targetScriptOption.TableScriptsGenerateOption.GenerateIdentity = this.chkGenerateIdentity.Checked;
GenerateScriptMode scriptMode = this.GetGenerateScriptMode();
if (scriptMode == GenerateScriptMode.None)
{
MessageBox.Show("Please specify the script mode.");
return;
}
DbConveterInfo source = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(sourceDbType, this.sourceDbConnectionInfo, sourceScriptOption)
};
DbConveterInfo target = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(targetDbType, this.targetDbConnectionInfo, targetScriptOption)
};
try
{
using (this.dbConverter = new DbConverter(source, target))
{
this.dbConverter.Option.GenerateScriptMode = scriptMode;
this.dbConverter.Option.BulkCopy = this.chkBulkCopy.Checked;
this.dbConverter.Option.ExecuteScriptOnTargetServer = this.chkExecuteOnTarget.Checked;
this.dbConverter.Option.UseTransaction = this.chkUseTransaction.Checked;
this.dbConverter.Option.SkipScriptError = this.chkSkipScriptError.Checked;
this.dbConverter.Option.PickupTable = this.chkPickup.Checked;
this.dbConverter.Option.ConvertComputeColumnExpression = this.chkComputeColumn.Checked;
this.dbConverter.Option.OnlyCommentComputeColumnExpressionInScript = this.chkOnlyCommentComputeExpression.Checked;
this.dbConverter.Subscribe(this);
if (sourceDbType == DatabaseType.MySql)
{
source.DbInterpreter.Option.InQueryItemLimitCount = 2000;
}
if (targetDbType == DatabaseType.SqlServer)
{
target.DbOwner = this.txtTargetDbOwner.Text ?? "dbo";
}
else if (targetDbType == DatabaseType.MySql)
{
target.DbInterpreter.Option.RemoveEmoji = true;
}
this.dbConverter.Option.SplitScriptsToExecute = true;
this.btnExecute.Enabled = false;
this.btnCancel.Enabled = true;
await this.dbConverter.Convert(schemaInfo);
if (!this.hasError && !this.dbConverter.HasError && !source.DbInterpreter.HasError && !target.DbInterpreter.HasError)
{
this.btnExecute.Enabled = true;
this.btnCancel.Enabled = false;
if (!this.dbConverter.CancelRequested)
{
this.txtMessage.AppendText(Environment.NewLine + DONE);
MessageBox.Show(DONE, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Task has been canceled.");
}
}
}
}
catch (Exception ex)
{
this.hasError = true;
this.HandleException(ex);
}
}
public async Task Translate(DatabaseType sourceDbType, DatabaseType targetDbType, DatabaseObject dbObject, ConnectionInfo connectionInfo, TranslateHandler translateHandler = null)
{
DbInterpreterOption sourceScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.None
};
DbInterpreterOption targetScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.WriteToString
};
DbConveterInfo source = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(sourceDbType, connectionInfo, sourceScriptOption)
};
DbConveterInfo target = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(targetDbType, new ConnectionInfo(), sourceScriptOption)
};
using (DbConverter dbConverter = new DbConverter(source, target))
{
dbConverter.Option.OnlyForTranslate = true;
dbConverter.Option.GenerateScriptMode = GenerateScriptMode.Schema;
dbConverter.Option.ExecuteScriptOnTargetServer = false;
dbConverter.Option.ConvertComputeColumnExpression = true;
dbConverter.Subscribe(this.observer);
if (translateHandler != null)
{
dbConverter.OnTranslated += translateHandler;
}
if (targetDbType == DatabaseType.SqlServer)
{
target.DbOwner = "dbo";
}
SchemaInfo schemaInfo = new SchemaInfo();
if (dbObject is Table)
{
schemaInfo.Tables.Add(dbObject as Table);
}
else if (dbObject is View)
{
schemaInfo.Views.Add(dbObject as DatabaseInterpreter.Model.View);
}
else if (dbObject is Function)
{
schemaInfo.Functions.Add(dbObject as Function);
}
else if (dbObject is Procedure)
{
schemaInfo.Procedures.Add(dbObject as Procedure);
}
else if (dbObject is TableTrigger)
{
schemaInfo.TableTriggers.Add(dbObject as TableTrigger);
}
await dbConverter.Convert(schemaInfo);
}
}
private async Task CopyTable()
{
string name = this.txtName.Text.Trim();
if (!this.ValidateInputs())
{
return;
}
try
{
GenerateScriptMode scriptMode = this.GetGenerateScriptMode();
bool isTableExisted = false;
if (scriptMode.HasFlag(GenerateScriptMode.Schema))
{
isTableExisted = await this.IsNameExisted();
}
if (isTableExisted)
{
name = name + "_copy";
}
SchemaInfo schemaInfo = new SchemaInfo();
schemaInfo.Tables.Add(this.Table);
DatabaseType targetDatabaseType = this.rbAnotherDatabase.Checked ? this.ucConnection.DatabaseType : this.DatabaseType;
ConnectionInfo targetConnectionInfo = this.rbAnotherDatabase.Checked ? this.targetDbConnectionInfo : this.ConnectionInfo;
DbInterpreterOption sourceOption = new DbInterpreterOption();
DbInterpreterOption targetOption = new DbInterpreterOption();
targetOption.TableScriptsGenerateOption.GenerateIdentity = this.chkGenerateIdentity.Checked;
DbConveterInfo source = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, this.ConnectionInfo, sourceOption)
};
DbConveterInfo target = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(targetDatabaseType, targetConnectionInfo, targetOption)
};
source.TableNameMappings.Add(this.Table.Name, name);
this.btnExecute.Enabled = false;
using (this.dbConverter = new DbConverter(source, target))
{
this.dbConverter.Option.RenameTableChildren = isTableExisted || this.rbSameDatabase.Checked;
this.dbConverter.Option.GenerateScriptMode = scriptMode;
this.dbConverter.Option.BulkCopy = true;
this.dbConverter.Option.UseTransaction = true;
this.dbConverter.Option.ConvertComputeColumnExpression = true;
this.dbConverter.Option.IgnoreNotSelfForeignKey = true;
this.dbConverter.Subscribe(this);
if (this.DatabaseType == DatabaseType.MySql)
{
source.DbInterpreter.Option.InQueryItemLimitCount = 2000;
}
if (this.DatabaseType != targetDatabaseType)
{
if (targetDatabaseType == DatabaseType.SqlServer)
{
target.DbOwner = "dbo";
}
else if (targetDatabaseType == DatabaseType.MySql)
{
target.DbInterpreter.Option.RemoveEmoji = true;
}
}
else
{
target.DbOwner = this.Table.Owner;
}
this.dbConverter.Option.SplitScriptsToExecute = true;
await this.dbConverter.Convert(schemaInfo);
if (!this.hasError && !this.dbConverter.HasError && !source.DbInterpreter.HasError && !target.DbInterpreter.HasError)
{
if (!this.dbConverter.CancelRequested)
{
MessageBox.Show("Copy finished", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Task has been canceled.");
}
}
}
}
catch (Exception ex)
{
this.hasError = true;
this.HandleException(ex);
}
finally
{
this.btnExecute.Enabled = true;
}
}
public async void OnExecution(object sender, CommandEventArgs command)
{
var bot = sender as Pieprz;
if (string.IsNullOrEmpty(command.Text))
{
await bot.SendTextMessageAsync(
chatId : command.Message.Chat.Id,
text : StringManager.FromFile("votebanusage.txt"),
parseMode : ParseMode.Markdown);
return;
}
try
{
if (_votebanningGroups.Contains(command.Message.Chat.Id))
{
await bot.SendTextMessageAsync(
chatId : command.Message.Chat.Id,
text : Program.strManager["VOTEBAN_ALREADY"],
parseMode : ParseMode.Markdown);
return;
}
const int time_secs = 60 * 3; //3 minutes
const int min_vote_count = 6;
const double vote_ratio = 0.7;
const int alert_period = 30;
var username = command.Text.Replace("@", "");
await using var db = PerchikDB.GetContext();
var user = db.Users
.AsNoTracking()
.FirstOrDefault(u => (u.FirstName.Contains(username, StringComparison.OrdinalIgnoreCase)) ||
(u.LastName.Contains(username, StringComparison.OrdinalIgnoreCase)) ||
(u.UserName.Contains(username, StringComparison.OrdinalIgnoreCase)));
if (user == null)
{
await bot.SendTextMessageAsync(
chatId : command.Message.Chat.Id,
text : $"*Пользователя \"{command.Text}\" нет в базе.*",
parseMode : ParseMode.Markdown);
return;
}
username = user.FirstName.Replace('[', '<').Replace(']', '>');
var userLink = $"[{username}](tg://user?id={user.Id})";
var message = command.Message;
string[] opts = { "За", "Против" };
var pollMsg = await bot.SendPollAsync(
chatId : message.Chat.Id,
question : string.Format(Program.strManager["VOTEBAN_QUESTION"], username),
options : opts,
disableNotification : false,
isAnonymous : false);
var chat = await bot.GetChatAsync(message.Chat.Id);
Logger.Log(LogType.Info, $"<{chat.Title}>: Voteban poll started for {username}:{user.Id}");
int legitvotes = 0, ignored = 0;
int forban = 0, againstban = 0;
var recentPoll = pollMsg.Poll;
var answers = new List <PollAnswer>();
_votebanningGroups.Add(command.Message.Chat.Id);
(sender as Pieprz).RegisterPoll(pollMsg.Poll.Id, (_, p) =>
{
if (p.PollAnswer == null)
{
return;
}
recentPoll = p.Poll;
var pollAnswer = p.PollAnswer;
var existingUser = db.Users.FirstOrDefault(x => x.Id == pollAnswer.User.Id);
if (existingUser != null)
{
if (pollAnswer.OptionIds.Length > 0)
{
answers.Add(pollAnswer);
Logger.Log(LogType.Info,
$"<{chat.Title}>: Voteban {pollAnswer?.User.FirstName}:{pollAnswer?.User.Id} voted {pollAnswer.OptionIds[0]}");
}
else
{
answers.RemoveAll(a => a.User.Id == pollAnswer.User.Id);
Logger.Log(LogType.Info,
$"<{chat.Title}>: Voteban {pollAnswer?.User.FirstName}:{pollAnswer?.User.Id} retracted vote");
}
}
else
{
Logger.Log(LogType.Info,
$"<{chat.Title}>: Voteban ignored user from another chat {pollAnswer?.User.FirstName}:{pollAnswer?.User.Id}");
}
});
var msg2delete = new List <Message>();
const int alertsCount = time_secs / alert_period;
for (var alerts = 1; alerts < alertsCount; alerts++)
{
await Task.Delay(1000 *alert_period);
forban = answers.Sum(a => a.OptionIds[0] == 0 ? 1 : 0);
againstban = answers.Sum(a => a.OptionIds[0] == 1 ? 1 : 0);
legitvotes = answers.Count;
ignored = recentPoll.TotalVoterCount - legitvotes;
msg2delete.Add(await bot.SendTextMessageAsync(
chatId: message.Chat.Id,
text: string.Format(Program.strManager["VOTEBAN_ALERT"],
user.FirstName, time_secs - alerts * alert_period, legitvotes, min_vote_count,
forban, againstban),
replyToMessageId: pollMsg.MessageId,
parseMode: ParseMode.Markdown));
Logger.Log(LogType.Info,
$"<{chat.Title}>: Voteban poll status {forban}<>{againstban}, totalvotes: {recentPoll.TotalVoterCount}, ignored: {ignored}");
}
await Task.Delay(1000 *alert_period);
await bot.StopPollAsync(message.Chat.Id, pollMsg.MessageId);
(sender as Pieprz).RemovePoll(pollMsg.Poll.Id);
_votebanningGroups.Remove(command.Message.Chat.Id);
msg2delete.ForEach(m => bot.DeleteMessageAsync(m.Chat.Id, m.MessageId));
forban = answers.Sum(a => a.OptionIds[0] == 0 ? 1 : 0);
againstban = answers.Sum(a => a.OptionIds[0] == 1 ? 1 : 0);
legitvotes = answers.Count;
ignored = recentPoll.TotalVoterCount - legitvotes;
var ignoreText = ignored > 0 ? string.Format(Program.strManager["VOTEBAN_IGNORED"], ignored) : "";
if (legitvotes < min_vote_count)
{
await bot.SendTextMessageAsync(
chatId : message.Chat.Id,
text : string.Format($"{Program.strManager["VOTEBAN_NOTENOUGH"]}\n\n{ignoreText}", legitvotes, min_vote_count,
forban, againstban),
replyToMessageId : pollMsg.MessageId,
parseMode : ParseMode.Markdown);
Logger.Log(LogType.Info, $"<{chat.Title}>: {forban}<>{againstban} Poll result: Not enough votes");
return;
}
var ratio = (double)forban / (double)legitvotes;
if (ratio < vote_ratio)
{
await bot.SendTextMessageAsync(
chatId : message.Chat.Id,
text : string.Format($"{Program.strManager["VOTEBAN_RATIO"]}\n\n {ignoreText}", ratio * 100),
replyToMessageId : pollMsg.MessageId,
parseMode : ParseMode.Markdown);
Logger.Log(LogType.Info, $"<{chat.Title}>: {forban}<>{againstban} Poll result: Decided not to ban");
return;
}
await bot.FullyRestrictUserAsync(
chatId : message.Chat.Id,
userId : user.Id,
forSeconds : 60 * 15);
var restriction = DbConverter.GenRestriction(command.Message, DateTime.UtcNow.AddSeconds(60 * 15));
await db.Restrictions.AddAsync(restriction);
await db.SaveChangesAsync();
await bot.SendTextMessageAsync(
chatId : message.Chat.Id,
text : string.Format($"{Program.strManager["VOTEBAN_BANNED"]}\n\n {ignoreText}", userLink,
forban, againstban),
replyToMessageId : pollMsg.MessageId,
parseMode : ParseMode.Markdown);
Logger.Log(LogType.Info,
$"<{chat.Title}>: Poll result: {forban}<>{againstban} The user has been banned!");
}
catch (Exception exp)
{
Logger.Log(LogType.Error, $"Exception: {exp.Message}\nTrace: {exp.StackTrace}");
}
}