/// <summary>
/// Diagnostic function: stores same item N times in a single transaction. Used to grow DB in stress test.
/// </summary>
/// <returns>UID of last stored item.</returns>
public string StoreBatch(StoredResult sr, int count)
{
long uid = 0;
lock (conn)
{
SqliteTransaction trans = null;
try
{
trans = conn.BeginTransaction();
for (int i = 0; i != count; ++i)
{
uid = getNextUid(sr.Date, trans);
storeResult(uid, trans, sr);
}
trans.Commit(); trans.Dispose(); trans = null;
}
catch (Exception ex)
{
logger.LogError(new EventId(), ex, "Failed to store quiz results.");
if (trans != null)
{
trans.Rollback();
}
throw;
}
finally { if (trans != null)
{
trans.Dispose();
}
}
}
return(uidToString(uid));
}
public void Commit()
{
if (_tran != null)
{
lock (_lock) {
_tran.Commit();
_tran.Dispose();
_tran = null;
}
}
}
private int GetDisabledCommandsInt()
{
Connection connection = DatabaseConnection.connection;
connection.connectionObject.Open();
using (SqliteTransaction transaction = connection.connectionObject.BeginTransaction())
{
SqliteCommand selectCmd = connection.connectionObject.CreateCommand();
selectCmd.Transaction = transaction;
selectCmd.CommandText = $"SELECT guildID FROM blacklistedcommands WHERE guildID={_guildId}";
var reader = selectCmd.ExecuteReader();
int tries = 0;
int disabledCommands = 0;
while (reader.Read())
{
//f
transaction.Commit();
connection.connectionObject.Close();
transaction.Dispose();
}
return(disabledCommands);
}
}
public override void EndTransaction()
{
if (Connection.State != ConnectionState.Open)
{
throw new InvalidOperationException("Database is not open.");
}
if (Interlocked.Decrement(ref transactionCount) > 0)
{
return;
}
if (currentTransaction == null)
{
if (shouldCommit)
{
throw new InvalidOperationException("Transaction missing.");
}
return;
}
if (shouldCommit)
{
currentTransaction.Commit();
shouldCommit = false;
}
else
{
currentTransaction.Rollback();
}
currentTransaction.Dispose();
currentTransaction = null;
}
public override void Dispose()
{
Close();
dbConnection?.Dispose();
dbTransaction?.Dispose();
}
public void SetHeshValueAsync(IList <KeyValuePair <string, string> > Hash)
{
SqliteTransaction Transaction = OLEDB.BeginTransaction();
try
{
StringBuilder sb = new StringBuilder("Delete From HashTable;");
foreach (var Command in from Command in Hash
select "Insert Into HashTable Values ('" + Command.Key + "','" + Command.Value + "');")
{
sb.Append(Command);
}
using (SqliteCommand SQLCommand = new SqliteCommand(sb.ToString(), OLEDB, Transaction))
{
SQLCommand.ExecuteNonQuery();
}
Transaction.Commit();
}
catch (Exception)
{
Transaction.Rollback();
}
finally
{
Transaction.Dispose();
}
}
public override void SetSummonBuffs(string characterId, List <CharacterBuff> summonBuffs)
{
SqliteTransaction transaction = connection.BeginTransaction();
try
{
ExecuteNonQuery(transaction, "DELETE FROM summonbuffs WHERE characterId=@characterId", new SqliteParameter("@characterId", characterId));
foreach (CharacterBuff summonBuff in summonBuffs)
{
ExecuteNonQuery(transaction, "INSERT INTO summonbuffs (id, characterId, buffId, type, dataId, level, buffRemainsDuration) VALUES (@id, @characterId, @buffId, @type, @dataId, @level, @buffRemainsDuration)",
new SqliteParameter("@id", characterId + "_" + summonBuff.id),
new SqliteParameter("@characterId", characterId),
new SqliteParameter("@buffId", summonBuff.id),
new SqliteParameter("@type", (byte)summonBuff.type),
new SqliteParameter("@dataId", summonBuff.dataId),
new SqliteParameter("@level", summonBuff.level),
new SqliteParameter("@buffRemainsDuration", summonBuff.buffRemainsDuration));
}
transaction.Commit();
}
catch (System.Exception ex)
{
Logging.LogError(ToString(), "Transaction, Error occurs while replacing buffs of summon: " + characterId);
Logging.LogException(ToString(), ex);
transaction.Rollback();
}
transaction.Dispose();
}
public string GetPrefix()
{
var connection = DatabaseConnection.connection;
if (connection.connectionObject.State == ConnectionState.Closed)
{
connection.connectionObject.Open();
}
using (SqliteTransaction transaction = connection.connectionObject.BeginTransaction())
{
var selectCommand = connection.connectionObject.CreateCommand();
selectCommand.Transaction = transaction;
selectCommand.CommandText = $"SELECT prefix FROM guildprefix WHERE guildID={_guildId}";
var reader = selectCommand.ExecuteReader();
string prefix = "";
while (reader.Read())
{
prefix = reader.GetString(0);
}
transaction.Commit();
connection.connectionObject.Close();
transaction.Dispose();
if (string.IsNullOrEmpty(prefix))
{
return(Environment.GetEnvironmentVariable("EdgyBot_Prefix", EnvironmentVariableTarget.User));
}
return(prefix);
}
}
/// <summary>
/// Close this DB.
/// </summary>
public void Close()
{
#if !UNITY_WEBPLAYER
if (reader != null)
{
reader.Close();
reader = null;
}
if (dbcmd != null)
{
dbcmd.Dispose();
dbcmd = null;
}
if (dbconn != null)
{
dbconn.Close();
dbconn = null;
}
if (dbtrans != null)
{
dbtrans.Dispose();
dbtrans = null;
}
#endif
isConnectionOpen = false;
}
public override async UniTask DeleteCharacter(string userId, string id)
{
await UniTask.Yield();
object result = ExecuteScalar("SELECT COUNT(*) FROM characters WHERE id=@id AND userId=@userId",
new SqliteParameter("@id", id),
new SqliteParameter("@userId", userId));
long count = result != null ? (long)result : 0;
if (count > 0)
{
SqliteTransaction transaction = connection.BeginTransaction();
try
{
ExecuteNonQuery(transaction, "DELETE FROM characters WHERE id=@characterId", new SqliteParameter("@characterId", id));
DeleteCharacterAttributes(transaction, id);
DeleteCharacterBuffs(transaction, id);
DeleteCharacterHotkeys(transaction, id);
DeleteCharacterItems(transaction, id);
DeleteCharacterQuests(transaction, id);
DeleteCharacterSkills(transaction, id);
DeleteCharacterSkillUsages(transaction, id);
DeleteCharacterSummons(transaction, id);
transaction.Commit();
}
catch (System.Exception ex)
{
Logging.LogError(ToString(), "Transaction, Error occurs while deleting character: " + id);
Logging.LogException(ToString(), ex);
transaction.Rollback();
}
transaction.Dispose();
this.InvokeInstanceDevExtMethods("DeleteCharacter", userId, id);
}
}
public async Task ExecuteQueryAsync(string query)
{
if (_connection == null)
{
throw new InvalidOperationException("_connection == null");
}
if (_connection.connectionObject.State == ConnectionState.Closed)
{
_connection.connectionObject.Open();
}
using (SqliteTransaction transaction = _connection.connectionObject.BeginTransaction())
{
SqliteCommand command = _connection.connectionObject.CreateCommand();
command.Transaction = transaction;
command.CommandText = query;
await command.ExecuteNonQueryAsync();
transaction.Commit();
_connection.connectionObject.Close();
transaction.Dispose();
}
}
public bool CommandDisabled(string rawCommand)
{
Connection connection = DatabaseConnection.connection;
connection.connectionObject.Open();
using (SqliteTransaction transaction = connection.connectionObject.BeginTransaction())
{
SqliteCommand selectCommand = connection.connectionObject.CreateCommand();
selectCommand.Transaction = transaction;
selectCommand.CommandText = $"SELECT command FROM blacklistedcommands WHERE guildID={_guildId}";
var reader = selectCommand.ExecuteReader();
int attempt = 0;
bool exists = false;
while (reader.Read())
{
string value = reader.GetString(attempt);
if (!string.IsNullOrEmpty(value))
{
if (value == rawCommand)
{
exists = true;
transaction.Commit();
connection.connectionObject.Close();
transaction.Dispose();
return(exists);
}
exists = false;
transaction.Commit();
connection.connectionObject.Close();
transaction.Dispose();
return(exists);
}
else
{
attempt++;
}
}
transaction.Commit();
connection.connectionObject.Close();
transaction.Dispose();
return(exists);
}
}
public void Dispose()
{
_transaction?.Dispose();
if (_connection.IsValueCreated)
{
_connection.Value.Dispose();
}
}
public void Dispose_does_not_throw_exception_if_connection_is_null()
{
var context = new TestableSqliteTransaction();
var sut = new SqliteTransaction(context.ConnectionStringFactory.Object, IsolationLevel.ReadCommitted);
sut.Dispose();
Assert.False(sut.IsOpen);
Assert.True(sut.IsOwner);
}
public void CommitTransection()
{
if (transection == null)
{
return;
}
transection.Commit();
transection.Dispose();
transection = null;
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (_ownsTransaction)
{
_transaction.Dispose();
}
if (_ownsConnection)
{
_connection.Dispose();
}
}
}
public void ProcessData(IEnumerable <IEntity> data, ProcessMode mode)
{
SqliteTransaction tran = null;
var toRemoveFromCache = new List <Guid>();
try
{
foreach (IEnumerable <IEntity> lst in GetBlock(data.GetEnumerator()))
{
tran = ActiveConnection.BeginTransaction();
ProcessAllInternal(lst, mode, tran, mode == ProcessMode.LocalChanges);
tran.Commit();
tran.Dispose();
tran = null;
if (mode == ProcessMode.ServerChanges)
{
foreach (ISqliteEntity e in lst)
{
toRemoveFromCache.Add(e.EntityId);
}
}
GC.Collect();
}
Cache.Clear(toRemoveFromCache);
}
catch
{
if (tran != null)
{
tran.Rollback();
tran.Dispose();
}
throw;
}
}
protected override void Close()
{
if (_conn != null && _conn.State == System.Data.ConnectionState.Open)
{
if (_transaction != null)
{
_transaction.Dispose();
}
_conn.Close();
_conn.Dispose();
GC.Collect();
_conn = null;
}
}
/// <summary>
/// Set state of ip addresses
/// </summary>
/// <param name="ipAddresses">IP addresses to set state for. Pass null to set the entire database.</param>
/// <param name="state">State to set</param>
/// <param name="transaction">Transaction</param>
/// <returns>Number of rows affected</returns>
public int SetIPAddressesState(IEnumerable <string> ipAddresses, IPAddressState state, object transaction = null)
{
int count = 0;
IPBanDBTransaction ipDBTransaction = transaction as IPBanDBTransaction;
bool commit = (transaction == null);
SqliteConnection conn = (ipDBTransaction?.DBConnection ?? CreateConnection());
if (commit)
{
OpenConnection(conn);
}
SqliteTransaction tran = (ipDBTransaction?.DBTransaction ?? conn.BeginTransaction(transactionLevel));
int stateInt = (int)state;
try
{
if (ipAddresses == null)
{
count += ExecuteNonQuery(conn, tran, "UPDATE IPAddresses SET State = @Param0", stateInt);
}
else
{
foreach (string ipAddress in ipAddresses)
{
if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj))
{
byte[] ipBytes = ipAddressObj.GetAddressBytes();
count += ExecuteNonQuery(conn, tran, "UPDATE IPAddresses SET State = @Param0 WHERE IPAddress = @Param1", stateInt, ipBytes);
}
}
}
}
finally
{
if (commit)
{
tran.Commit();
tran.Dispose();
CloseConnection(conn);
}
}
return(count);
}
public override void UpdateStorageItems(StorageType storageType, string storageOwnerId, List <CharacterItem> characterItems)
{
SqliteTransaction transaction = connection.BeginTransaction();
try
{
DeleteStorageItems(transaction, storageType, storageOwnerId);
for (int i = 0; i < characterItems.Count; ++i)
{
CreateStorageItem(transaction, i, storageType, storageOwnerId, characterItems[i]);
}
transaction.Commit();
}
catch (System.Exception ex)
{
Logging.LogError(ToString(), "Transaction, Error occurs while replacing storage items");
Logging.LogException(ToString(), ex);
transaction.Rollback();
}
transaction.Dispose();
}
private bool CheckIfRegisteredAsync(ulong guildID)
{
var connection = DatabaseConnection.connection;
SQLProcessor processor = new SQLProcessor(connection);
connection.connectionObject.Open();
using (SqliteTransaction transaction = connection.connectionObject.BeginTransaction())
{
var selectCommand = connection.connectionObject.CreateCommand();
selectCommand.Transaction = transaction;
selectCommand.CommandText = $"SELECT guildID FROM guildprefix WHERE guildID={guildID}";
var reader = selectCommand.ExecuteReader();
string msg = "";
while (reader.Read())
{
msg = reader.GetString(0);
}
transaction.Commit();
connection.connectionObject.Close();
transaction.Dispose();
bool x = false;
try
{
ulong id = ulong.Parse(msg);
if (id == guildID)
{
x = true;
}
} catch
{
x = false;
}
return(x);
}
}
public static int ExecSqliteModifyTransaction(string sql)
{
SqliteConnection connection = null;
SqliteTransaction transaction = null;
SqliteCommand command = null;
try
{
// Открываем соединение и начинаем транзацкию
connection = new SqliteConnection(ConnectionClass.NewDatabasePath);
connection.Open();
transaction = connection.BeginTransaction();
command = connection.CreateCommand();
command.CommandText = sql;
command.CommandTimeout = 30;
command.CommandType = CommandType.Text;
var count = command.ExecuteNonQuery();
return(count);
}
catch (SqliteException ex)
{
Debug.WriteLine($"Ошибка в запросе БД: {ex.Message}");
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch (SqliteException ex2)
{
Debug.WriteLine($"Откат транзакции вылетел с ошибкой: {ex2.Message}");
}
}
finally
{
transaction.Dispose();
}
return(-1);
}
protected override TResult RunInTransaction <TResult>(Func <TResult> action, bool createTransaction)
{
TResult result;
SqliteTransaction trans = null;
if (createTransaction)
{
trans = this.Connection.BeginTransaction();
}
try
{
result = action();
if (createTransaction)
{
trans.Commit();
}
}
catch
{
if (createTransaction)
{
trans.Rollback();
}
throw;
}
finally
{
if (createTransaction)
{
trans.Dispose();
}
}
return(result);
}
/// <summary>
/// 连接sql,执行SQL命令,不返回查询结果,然后立刻关闭连接
/// 事务概念
/// 事务（Transaction）是指一个或多个更改数据库的扩展。
/// 例如，如果您正在创建一个记录或者更新一个记录或者从表中删除一个记录，
/// 那么您正在该表上执行事务。
/// 重要的是要控制事务以确保数据的完整性和处理数据库错误。
/// 实际上，可以把许多的 SQLite查询联合成一组，把所有这些放在一起作为事务的一部分进行执行
/// </summary>
/// <param name="queryString"></param>
public void ExecuteNonQueryOnce(string queryString)
{
using (SqliteConnection conn = new SqliteConnection(_connectionString))
{
conn.Open();
SqliteCommand cmd = new SqliteCommand();
cmd.Connection = conn;
//开启事务
//事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。
//不过在数据库关闭或发生错误时，事务处理也会回滚。
SqliteTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
cmd.CommandText = queryString;
cmd.ExecuteNonQuery();
//提交(事务执行成功)
//COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令。
//命令把自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库。
tx.Commit();
tx.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
catch
{
//回滚(事务执行失败，不提交)
//ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令。
//命令只能用于撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。
tx.Rollback();
throw;
}
}
}
protected async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) where T : class
{
SqliteConnection connection = isAsync ? await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false)
: OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
bool doExplicitCommit = false;
SqliteTransaction transaction = null;
try
{
if (context.Database.CurrentTransaction == null)
{
//context.Database.UseTransaction(connection.BeginTransaction());
doExplicitCommit = true;
}
transaction = doExplicitCommit ? connection.BeginTransaction()
: (SqliteTransaction)context.Database.CurrentTransaction.GetUnderlyingTransaction(tableInfo.BulkConfig);
SqliteCommand command = connection.CreateCommand();
command.Transaction = transaction;
// CREATE
command.CommandText = SqlQueryBuilderSqlite.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName);
if (isAsync)
{
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
else
{
command.ExecuteNonQuery();
}
tableInfo.BulkConfig.OperationType = OperationType.Insert;
tableInfo.InsertToTempTable = true;
tableInfo.SqliteConnection = connection;
tableInfo.SqliteTransaction = transaction;
// INSERT
if (isAsync)
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
else
{
InsertAsync(context, type, entities, tableInfo, progress, cancellationToken, isAsync: false).GetAwaiter().GetResult();
}
// JOIN
List <T> existingEntities;
var sqlSelectJoinTable = SqlQueryBuilder.SelectJoinTable(tableInfo);
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlSelectJoinTable, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
tableInfo.UpdateReadEntities(type, entities, existingEntities);
// DROP
command.CommandText = SqlQueryBuilderSqlite.DropTable(tableInfo.FullTempTableName);
if (isAsync)
{
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
else
{
command.ExecuteNonQuery();
}
if (doExplicitCommit)
{
transaction.Commit();
}
}
finally
{
if (doExplicitCommit)
{
if (isAsync)
{
await transaction.DisposeAsync();
await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
else
{
transaction.Dispose();
context.Database.CloseConnection();
}
}
}
}
public void Dispose()
{
_command.Dispose();
_transaction.Dispose();
}
private void FillCharacterRelatesData(IPlayerCharacterData characterData)
{
// Delete all character then add all of them
string characterId = characterData.Id;
SqliteTransaction transaction = connection.BeginTransaction();
try
{
DeleteCharacterAttributes(transaction, characterId);
DeleteCharacterBuffs(transaction, characterId);
DeleteCharacterHotkeys(transaction, characterId);
DeleteCharacterItems(transaction, characterId);
DeleteCharacterQuests(transaction, characterId);
DeleteCharacterSkills(transaction, characterId);
DeleteCharacterSkillUsages(transaction, characterId);
DeleteCharacterSummons(transaction, characterId);
int i;
for (i = 0; i < characterData.SelectableWeaponSets.Count; ++i)
{
CreateCharacterEquipWeapons(transaction, (byte)i, characterData.Id, characterData.SelectableWeaponSets[i]);
}
for (i = 0; i < characterData.EquipItems.Count; ++i)
{
CreateCharacterEquipItem(transaction, i, characterData.Id, characterData.EquipItems[i]);
}
for (i = 0; i < characterData.NonEquipItems.Count; ++i)
{
CreateCharacterNonEquipItem(transaction, i, characterData.Id, characterData.NonEquipItems[i]);
}
for (i = 0; i < characterData.Attributes.Count; ++i)
{
CreateCharacterAttribute(transaction, i, characterData.Id, characterData.Attributes[i]);
}
for (i = 0; i < characterData.Skills.Count; ++i)
{
CreateCharacterSkill(transaction, i, characterData.Id, characterData.Skills[i]);
}
for (i = 0; i < characterData.SkillUsages.Count; ++i)
{
CreateCharacterSkillUsage(transaction, characterData.Id, characterData.SkillUsages[i]);
}
for (i = 0; i < characterData.Summons.Count; ++i)
{
CreateCharacterSummon(transaction, i, characterData.Id, characterData.Summons[i]);
}
for (i = 0; i < characterData.Quests.Count; ++i)
{
CreateCharacterQuest(transaction, i, characterData.Id, characterData.Quests[i]);
}
for (i = 0; i < characterData.Buffs.Count; ++i)
{
CreateCharacterBuff(transaction, characterData.Id, characterData.Buffs[i]);
}
for (i = 0; i < characterData.Hotkeys.Count; ++i)
{
CreateCharacterHotkey(transaction, characterData.Id, characterData.Hotkeys[i]);
}
transaction.Commit();
}
catch (System.Exception ex)
{
Logging.LogError(ToString(), "Transaction, Error occurs while filling character relates data");
Logging.LogException(ToString(), ex);
transaction.Rollback();
}
transaction.Dispose();
}
/// <summary>
/// 回滚事务
/// </summary>
void IDataBase.Rollback()
{
Transaction?.Rollback();
Transaction?.Dispose();
Transaction = null;
}
/// <summary>
/// Rollbacks the current transaction.
/// </summary>
public void Rollback()
{
_currentTransaction.Rollback();
_currentTransaction.Dispose();
_currentTransaction = null;
}
/// <summary>
/// Removes the specified user names from the specified roles for the configured applicationName.
/// </summary>
/// <param name="usernames">A string array of user names to be removed from the specified roles.</param>
/// <param name="roleNames">A string array of role names to remove the specified user names from.</param>
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
foreach (string roleName in roleNames)
{
if (!RoleExists(roleName))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
foreach (string roleName in roleNames)
{
if (!IsUserInRole(username, roleName))
{
throw new ProviderException("User is not in role.");
}
}
}
SqliteTransaction tran = null;
SqliteConnection cn = GetDbConnectionForRole();
try {
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
if (!IsTransactionInProgress())
{
tran = cn.BeginTransaction();
}
using (SqliteCommand cmd = cn.CreateCommand()) {
cmd.CommandText = "DELETE FROM " + USERS_IN_ROLES_TB_NAME
+ " WHERE UserId = (SELECT UserId FROM " + USER_TB_NAME + " WHERE LoweredUsername = $Username AND ApplicationId = $MembershipApplicationId)"
+ " AND RoleId = (SELECT RoleId FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName AND ApplicationId = $ApplicationId)";
SqliteParameter userParm = cmd.Parameters.Add("$Username", DbType.String, MAX_USERNAME_LENGTH);
SqliteParameter roleParm = cmd.Parameters.Add("$RoleName", DbType.String, MAX_ROLENAME_LENGTH);
cmd.Parameters.AddWithValue("$MembershipApplicationId", _membershipApplicationId);
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
foreach (string username in usernames)
{
foreach (string roleName in roleNames)
{
userParm.Value = username.ToLowerInvariant();
roleParm.Value = roleName.ToLowerInvariant();
cmd.ExecuteNonQuery();
}
}
// Commit the transaction if it's the one we created in this method.
if (tran != null)
{
tran.Commit();
}
}
} catch {
if (tran != null)
{
try {
tran.Rollback();
} catch (SqliteException) {
}
}
throw;
} finally {
if (tran != null)
{
tran.Dispose();
}
if (!IsTransactionInProgress())
{
cn.Dispose();
}
}
}