/// <summary>
/// Copy a file from sync folder and update action table
/// </summary>
/// <param name="action"></param>
/// <param name="profile"></param>
public static void CopyToSyncFolderAndUpdateActionTable(SyncAction action, SyncJob job)
{
if (!Directory.Exists(job.SyncSource.Path))
{
throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.SyncSource.Path));
}
if (!Directory.Exists(job.IntermediaryStorage.Path))
{
throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.IntermediaryStorage.Path));
}
// TODO: atomic....
string absolutePathInIntermediateStorage = job.IntermediaryStorage.DirtyFolderPath + action.RelativeFilePath;
string absolutePathInSyncSource = job.SyncSource.Path + action.RelativeFilePath;
SQLiteAccess dbAccess = new SQLiteAccess(Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME), true);
SqliteConnection con = dbAccess.NewSQLiteConnection();
if (con == null)
{
throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME)));
}
SqliteTransaction trasaction = (SqliteTransaction)con.BeginTransaction();
try
{
SQLiteSyncActionsProvider actProvider = (SQLiteSyncActionsProvider)SyncClient.GetSyncActionsProvider(job.IntermediaryStorage.Path);
actProvider.Delete(action, con);
if (!Files.FileUtils.Copy(absolutePathInIntermediateStorage, absolutePathInSyncSource, true))
{
throw new Exception(String.Format(m_ResourceManager.GetString("err_cannotCopyFile"), absolutePathInIntermediateStorage));
}
trasaction.Commit();
Files.FileUtils.DeleteFileAndFolderIfEmpty(job.IntermediaryStorage.DirtyFolderPath, absolutePathInIntermediateStorage, true);
}
catch (OutOfDiskSpaceException)
{
trasaction.Rollback();
throw;
}
catch (Exception)
{
trasaction.Rollback();
throw;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
}
public override void Rollback()
{
if (Status == DatabaseTransactionStatus.Open)
{
dbTransaction?.Rollback();
}
Status = DatabaseTransactionStatus.RolledBack;
Dispose();
}
public SqliteTransaction BeginTransaction()
{
_transaction?.Rollback();
if (_connection.State == System.Data.ConnectionState.Closed)
{
_connection.Open();
}
_transaction = _connection.BeginTransaction();
_useResultManager = true;
return(_transaction);
}
/// <summary>
/// 执行SQL事务
/// </summary>
/// <param name="_queryStringParametersPair">SQL命令字符串参数组</param>
/// <returns>true:成功,false:失败</returns>
public bool ExecuteTransaction(Dictionary <string, List <SqliteParameter> > _queryStringParametersPair)
{
bool result = false;
if (_queryStringParametersPair != null && _queryStringParametersPair.Count > 0)
{
OnOpenDbConnection();
SqliteCommand cmd = new SqliteCommand(mDbConnection);
SqliteTransaction trans = mDbConnection.BeginTransaction();
try
{
foreach (KeyValuePair <string, List <SqliteParameter> > key in _queryStringParametersPair)
{
cmd.CommandText = key.Key;
cmd.Parameters.AddRange(key.Value.ToArray());
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ep)
{
trans.Rollback();
Debug.LogError(ep.Message);
}
finally
{
cmd.Dispose();
cmd = null;
mDbConnection.Close();
result = true;
}
}
return(result);
}
/// <summary>
/// 执行SQL事务
/// </summary>
/// <param name="_queryStrings">SQL命令字符串组</param>
/// <returns>true:成功,false:失败</returns>
public bool ExecuteTransaction(params string[] _queryStrings)
{
bool result = false;
if (_queryStrings != null && _queryStrings.Length > 0)
{
OnOpenDbConnection();
SqliteCommand cmd = new SqliteCommand(mDbConnection);
SqliteTransaction trans = mDbConnection.BeginTransaction();
try
{
foreach (string sql in _queryStrings)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ep)
{
trans.Rollback();
Debug.LogError(ep.Message);
}
finally
{
cmd.Dispose();
cmd = null;
mDbConnection.Close();
result = true;
}
}
return(result);
}
/// <summary>
/// 删除记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
/// <returns></returns>
internal bool Delete <T>(List <T> ts) where T : SqliteDataTableBase
{
Open();
bool hasIdPk = HasIdPK <T>();
string cmdStr = SqliteTabelCmdStrTool.GetDeleteStr(ts, hasIdPk);
SqliteTransaction st = connection.BeginTransaction();
try
{
int res = 0;
lock (_dbLocker)
{
res = OperateRecords(cmdStr);
if (res > 0)
{
st.Commit();
}
}
return(res > 0);
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("删除记录时异常", e.Message);
return(false);
}
finally
{
Close();
}
}
private void FixPlaysetLoadorderPositions(ObservableCollection <ModInPlayset> loadOrder, SqliteConnection connection)
{
SqliteTransaction transaction = connection.BeginTransaction();
try
{
foreach (ModInPlayset currentMod in loadOrder)
{
SqliteCommand fixCommand = connection.CreateCommand();
fixCommand.CommandText = $"UPDATE playsets_mods SET position = @newPos WHERE modID = @modID AND playsetID = @playsetID;";
fixCommand.Parameters.AddWithValue("@newPos", currentMod.DatabaseIndex);
fixCommand.Parameters.AddWithValue("@modID", currentMod.TargetMod.UUID);
fixCommand.Parameters.AddWithValue("@playsetID", currentMod.TargetPlayset.UUID);
fixCommand.Prepare();
int res = fixCommand.ExecuteNonQuery();
Debug.WriteLineIf(res != 1, $"No row was Updated with the fixed Load Order! Mod was {currentMod.ModName}");
}
transaction.Commit();
}
catch (Exception e)
{
Debug.WriteLine("Exception: " + e.Message + "\r\nRolling Back!");
transaction.Rollback();
}
}
public bool ActivatePlayset(Playset playset)
{
using (SqliteConnection connection = new SqliteConnection($"Data Source={connectionPath}"))
{
connection.Open();
SqliteTransaction transaction = connection.BeginTransaction();
try
{
SqliteCommand deactivateCurrentCommand = connection.CreateCommand();
deactivateCurrentCommand.CommandText = $"UPDATE playsets SET isActive = false WHERE isActive = true;";
deactivateCurrentCommand.ExecuteNonQuery();
SqliteCommand activateNewCommand = connection.CreateCommand();
activateNewCommand.CommandText = $"UPDATE playsets SET isActive = true WHERE id = @playsetID;";
activateNewCommand.Parameters.AddWithValue("@playsetID", playset.UUID);
activateNewCommand.Prepare();
activateNewCommand.ExecuteNonQuery();
transaction.Commit();
return(true);
}
catch (Exception e)
{
Debug.WriteLine("Exception: " + e.Message + "\r\nRolling Back!");
transaction.Rollback();
return(false);
}
}
}
protected override void RollbackTransaction()
{
if (_transaction != null)
{
_transaction.Rollback();
}
}
/// <summary>
/// Clears any Rate Limits a user has accumulated.
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public static async Task <VOption <bool> > ClearUserRateLimits(string userid)
{
using (SqliteConnection conn = new SqliteConnection(AppConstants.ConnectionString)) {
await conn.OpenAsync();
using (SqliteTransaction t = conn.BeginTransaction()) {
try {
using (SqliteCommand comm = conn.CreateCommand()) {
comm.CommandText = "DELETE FROM RateLimits WHERE SpotifyUserId=@sid;";
comm.Parameters.Add("@sid", SqliteType.Text).Value = userid;
await comm.ExecuteNonQueryAsync();
}
t.Commit();
logger.Info("Successfully deleted user's rate limits");
return(new VOption <bool>());
}
catch (Exception e) {
logger.Error(e, "Failed to delete users spotify errors");
t.Rollback();
return(new VOption <bool>(ErrorCodes.DatabaseWriteError, "Failed to delete users rate limits"));
}
}
}
}
/// <summary>
/// Writes a Rate Limit for the given user
/// </summary>
/// <param name="aspnetuserid"></param>
/// <param name="tracks"></param>
/// <returns></returns>
public static async Task <VOption <bool> > InsertRateLimitForUser(string aspnetuserid, string spotifyid, RateLimit rl)
{
using (SqliteConnection conn = new SqliteConnection(AppConstants.ConnectionString)) {
await conn.OpenAsync();
using (SqliteTransaction t = conn.BeginTransaction()) {
try {
using (SqliteCommand comm = conn.CreateCommand()) {
comm.CommandText = "INSERT OR REPLACE INTO RateLimits (AspNetUserId, SpotifyUserId, ExpiresAt, LimitedAtOffset) VALUES (@auid, @sid, @ea, @of)";
comm.Parameters.Add("@auid", SqliteType.Text).Value = aspnetuserid;
comm.Parameters.Add("@sid", SqliteType.Text).Value = spotifyid;
comm.Parameters.Add("@ea", SqliteType.Integer).Value = rl.RateLimitExpiresAt.ToUnixTimeMilliseconds();
comm.Parameters.Add("@of", SqliteType.Integer).Value = rl.LimitedAtOffset;
await comm.ExecuteNonQueryAsync();
}
t.Commit();
logger.Info("Successfully inserted user rate limits");
return(new VOption <bool>());
}
catch (Exception e) {
logger.Error(e, "Failed to write a users rate limits");
t.Rollback();
return(new VOption <bool>(ErrorCodes.DatabaseWriteError, "Failed to write a users rate limits"));
}
}
}
}
public void SyncComplete(bool success)
{
if (success)
{
using (SqliteTransaction tran = ActiveConnection.BeginTransaction())
{
try
{
CreateSchema();
foreach (string tableName in _tablesByIdx)
{
if (tableName.StartsWith("_Catalog_") || tableName.StartsWith("_Document_"))
{
Exec(string.Format("UPDATE {0} SET IsDirty = 0 WHERE IsDirty = 1", tableName), cmd =>
{
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
});
}
}
Cache.ClearNew();
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
UpdateDbStatus(_userId, success);
}
/// <summary>
/// Writes a Spotify Error for the given user
/// </summary>
/// <param name="aspnetuserid"></param>
/// <param name="tracks"></param>
/// <returns></returns>
public static async Task <VOption <bool> > WriteSpotifyErrorForUser(string aspnetuserid, string spotifyid, SpotifyError serr)
{
using (SqliteConnection conn = new SqliteConnection(AppConstants.ConnectionString)) {
await conn.OpenAsync();
using (SqliteTransaction t = conn.BeginTransaction()) {
try {
using (SqliteCommand comm = conn.CreateCommand()) {
comm.CommandText = "INSERT OR REPLACE INTO SpotifyErrors (AspNetUserId, SpotifyUserId, ErrorMessage, ErrorCode) VALUES (@auid, @sid, @erm, @erc)";
comm.Parameters.Add("@auid", SqliteType.Text).Value = aspnetuserid;
comm.Parameters.Add("@sid", SqliteType.Text).Value = spotifyid;
comm.Parameters.Add("@erm", SqliteType.Text).Value = serr.ErrorMessage;
comm.Parameters.Add("@erc", SqliteType.Integer).Value = serr.ErrorCode;
await comm.ExecuteNonQueryAsync();
}
t.Commit();
logger.Info("Successfully wrote user spotify error");
return(new VOption <bool>());
}
catch (Exception e) {
logger.Error(e, "Failed to write a users spotify error");
t.Rollback();
return(new VOption <bool>(ErrorCodes.DatabaseWriteError, "Failed to write a users spotify error"));
}
}
}
}
public void SaveAnchor(byte[] anchor)
{
using (SqliteTransaction tran = ActiveConnection.BeginTransaction())
{
try
{
Exec(string.Format("DELETE FROM {0}", AnchorTable), cmd =>
{
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
if (anchor != null)
{
string data = Convert.ToBase64String(anchor);
cmd.CommandText = String.Format("INSERT INTO {0}([Data]) VALUES(@Data)", AnchorTable);
cmd.Parameters.AddWithValue("@Data", data);
cmd.ExecuteNonQuery();
}
});
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
/// <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 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;
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="DB">数据库</param>
/// <param name="SQLs">sql语句</param>
/// <returns>返回影响行数</returns>
public static int Command(string DB, params string[] SQLs)
{
int result = 0;
if (File.Exists(DB) && SQLs != null)
{
using SqliteConnection con = new(@"Data Source=" + DB);
con.Open();
using (SqliteTransaction trans = con.BeginTransaction())
{
try
{
foreach (string SQLstr in SQLs)
{
using SqliteCommand cmd = con.CreateCommand();
cmd.CommandText = SQLstr;
result += cmd.ExecuteNonQuery();
}
}
catch
{
trans.Rollback();//出错，回滚
result = -1;
}
if (result != -1)
{
trans.Commit();
}
}
con.Close();
SqliteConnection.ClearAllPools();
}
return(result);
}
public void InsertWithTransaction()
{
_conn.Open();
using (_conn)
using (SqliteTransaction t = _conn.BeginTransaction() as SqliteTransaction)
using (SqliteCommand c1 = new SqliteCommand("INSERT INTO t1 VALUES ('a',0.1,0,'0')", _conn, t))
using (SqliteCommand c2 = new SqliteCommand("INSERT INTO t1 VALUES ('b',1.2,0,'0')", _conn, t))
using (SqliteCommand c3 = new SqliteCommand("INSERT INTO t1 VALUES ('c',0.3,1,'0')", _conn, t))
using (SqliteCommand c4 = new SqliteCommand("INSERT INTO t1 VALUES ('d',0.4,0,'1')", _conn, t))
{
try
{
c1.ExecuteNonQuery();
c2.ExecuteNonQuery();
c3.ExecuteNonQuery();
c4.ExecuteNonQuery();
t.Commit();
}
catch (Exception e)
{
t.Rollback();
throw new AssertionException("Sqlite Commands failed", e);
}
}
}
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 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 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);
}
}
/// <summary>
/// 更新记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
/// <param name="args">存的是一个实例中的字段名与值，key为字段名字，value的List是实例的字段名字、类型和值</param>
/// <returns></returns>
internal bool Update <T>(List <T> ts, Dictionary <string, List <object> > args = null) where T : SqliteDataTableBase
{
Open();
bool hasIdPk = HasIdPK <T>();
Dictionary <string, string> argsStr = args != null?args.Select(arg => new { key = arg.Key, value = SqliteTabelCmdStrTool.GetFieldValue(arg.Value[0].ToString(), arg.Value[1] as Type, arg.Value[2]) }).ToDictionary(p => p.key, p => p.value) : null;
string cmdStr = SqliteTabelCmdStrTool.GetUpdateStr(ts, hasIdPk, argsStr);
SqliteTransaction st = connection.BeginTransaction();
try
{
int res = 0;
lock (_dbLocker)
{
res = OperateRecords(cmdStr);
if (res > 0)
{
st.Commit();
}
}
return(res > 0);
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("更新记录时异常", e.Message);
return(false);
}
finally
{
Close();
}
}
public void InsertWithFailingTransaction()
{
_conn.Open();
using (_conn)
using (SqliteTransaction t = _conn.BeginTransaction() as SqliteTransaction)
using (SqliteCommand c1 = new SqliteCommand("INSERT INTO t1 VALUES ('1','0','0','0')", _conn, t))
using (SqliteCommand c2 = new SqliteCommand("INSERT INTO t1 VALUES ('0','1','0','0')", _conn, t))
using (SqliteCommand c3 = new SqliteCommand("INSERT INTO t1 VALUES ('x',?,'x',?,'x',?,'x')", _conn, t))
using (SqliteCommand c4 = new SqliteCommand("INSERT INTO t1 VALUES ('0','0','0','1')", _conn, t))
{
try
{
c1.ExecuteNonQuery();
c2.ExecuteNonQuery();
c3.ExecuteNonQuery();
c4.ExecuteNonQuery();
t.Commit();
}
catch (Exception e)
{
t.Rollback();
throw e;
}
}
}
public void CommitTransaction()
{
var lst = new List <string>();
using (var cmd = new SqliteCommand(String.Format("SELECT DISTINCT [TableName] FROM {0}", TranStatusTable), ActiveConnection))
{
using (SqliteDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
lst.Add(r.GetString(0));
}
}
}
SqliteTransaction tran = ActiveConnection.BeginTransaction();
try
{
foreach (String tableName in lst)
{
using (var cmd = new SqliteCommand(String.Format("DELETE FROM __{0}", tableName), tran.Connection, tran))
cmd.ExecuteNonQuery();
}
using (var cmd = new SqliteCommand(String.Format("DELETE FROM {0}", TranStatusTable), tran.Connection, tran))
cmd.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
public void DeletePlayset(Playset playset)
{
using (SqliteConnection connection = new SqliteConnection($"Data Source={connectionPath}"))
{
connection.Open();
SqliteTransaction transaction = connection.BeginTransaction();
try
{
SqliteCommand deleteChildrenCommand = connection.CreateCommand();
deleteChildrenCommand.CommandText = $"DELETE FROM playsets_mods WHERE playsetId = @playsetID;";
deleteChildrenCommand.Parameters.AddWithValue("@playsetID", playset.UUID);
deleteChildrenCommand.Prepare();
deleteChildrenCommand.ExecuteNonQuery();
SqliteCommand deleteParentCommand = connection.CreateCommand();
deleteParentCommand.CommandText = $"DELETE FROM playsets WHERE id = @playsetID;";
deleteParentCommand.Parameters.AddWithValue("@playsetID", playset.UUID);
deleteParentCommand.Prepare();
deleteParentCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
Debug.WriteLine("Exception: " + e.Message + "\r\nRolling Back!");
transaction.Rollback();
}
}
}
public override bool Update(SyncJob job)
{
if (this.SyncJobExists(job.Name, job.ID))
{
throw new SyncJobNameExistException(String.Format(m_ResourceManager.GetString("err_syncjobCreated"), job.Name));
}
SQLiteSyncSourceProvider provider = (SQLiteSyncSourceProvider)SyncClient.GetSyncSourceProvider(job.IntermediaryStorage.Path);
if (provider.GetSyncSourceCount() > 2)
{
throw new SyncSourcesNumberExceededException(m_ResourceManager.GetString("err_onlyTwoSyncSourceFolders"));
}
// Update a profile requires update 2 tables at the same time,
// If one update on a table fails, the total update action must fail too.
string updateProfileText = "UPDATE " + SYNCJOB_TABLE +
" SET " + COL_METADATA_SOURCE_LOCATION + " = @mdSource, " +
COL_SYNCJOB_NAME + " = @name WHERE " + COL_SYNCJOB_ID + " = @id;";
SqliteParameterCollection paramList = new SqliteParameterCollection();
// Add parameters for 1st Update statement
paramList.Add(new SqliteParameter("@mdSource", System.Data.DbType.String)
{
Value = job.IntermediaryStorage.Path
});
paramList.Add(new SqliteParameter("@name", System.Data.DbType.String)
{
Value = job.Name
});
paramList.Add(new SqliteParameter("@id", System.Data.DbType.String)
{
Value = job.ID
});
SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME), false);
using (SqliteConnection con = db.NewSQLiteConnection())
{
if (con == null)
{
throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, DATABASE_NAME)));
}
SqliteTransaction transaction = (SqliteTransaction)con.BeginTransaction();
try
{
SQLiteSyncSourceProvider.Update(job.SyncSource, con);
db.ExecuteNonQuery(updateProfileText, paramList);
transaction.Commit();
return(true);
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
public void ClonePlaysetToNew(string newPlaysetName, Playset fromPlayset)
{
using (SqliteConnection connection = new SqliteConnection($"Data Source={connectionPath}"))
{
connection.Open();
SqliteTransaction transaction = connection.BeginTransaction();
Playset newPlayset = createNewPlaysetOnConnection(newPlaysetName, connection);
try
{
SqliteCommand fillCommand = connection.CreateCommand();
fillCommand.CommandText = $"INSERT INTO playsets_mods(playsetId, modId, position, enabled) SELECT '{newPlayset.UUID}', modId, position, enabled FROM playsets_mods WHERE playsetId = @oldUUID;";
fillCommand.Parameters.AddWithValue("@oldUUID", fromPlayset.UUID);
fillCommand.Prepare();
Debug.WriteLine("Command: " + fillCommand.CommandText + " with oldUUID = " + fromPlayset.UUID);
fillCommand.ExecuteNonQuery();
transaction.Commit();
}catch (Exception e)
{
Debug.WriteLine("Exception: " + e.Message + "\r\nRolling Back!");
transaction.Rollback();
}
}
}
/// <summary>
/// 启动事务插入SQLlite数据库数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static bool TransactionSQLlist(List <string> list)
{
SqliteConnection conn = GetSQLiteConnection();
using (SqliteCommand cmd = new SqliteCommand())
{
cmd.Connection = conn;
if (!(conn.State == ConnectionState.Open))
{
conn.Open();
}
SqliteTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < list.Count; n++)
{
string strsql = list[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
return(true);
}
catch (SqliteException E)
{
tx.Rollback();
return(false);
}
}
}
public bool LinkRegion(UUID regionID, int estateID)
{
using (SqliteTransaction transaction = m_connection.BeginTransaction())
{
// Delete any existing estate mapping for this region.
using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
{
cmd.CommandText = "delete from estate_map where RegionID = :RegionID";
cmd.Transaction = transaction;
cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
cmd.ExecuteNonQuery();
}
using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
{
cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
cmd.Transaction = transaction;
cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
cmd.Parameters.AddWithValue(":EstateID", estateID.ToString());
if (cmd.ExecuteNonQuery() == 0)
{
transaction.Rollback();
return(false);
}
else
{
transaction.Commit();
return(true);
}
}
}
}
/// <summary>
/// 插入数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
/// <returns></returns>
internal bool Insert <T>(List <T> ts) where T : SqliteDataTableBase
{
Open();
bool hasIdIncre = HasAutoIncrementID <T>();
string cmdStr = SqliteTabelCmdStrTool.GetInsertStr(ts, hasIdIncre);
Debug.Log(cmdStr);
SqliteTransaction st = connection.BeginTransaction();
try
{
int res = 0;
lock (_dbLocker)
{
res = OperateRecords(cmdStr);
if (res > 0)
{
st.Commit();
}
}
return(res > 0);
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("插入数据时异常", e.Message);
return(false);
}
finally
{
Close();
}
}
