public bool TryAddSizes(ConcurrentQueue <BlobSize> sizes, out int errorCode, out string error) { errorCode = 0; error = null; try { using (SqliteTransaction insertTransaction = this.connection.BeginTransaction()) { this.addCommand.Transaction = insertTransaction; BlobSize blobSize; while (sizes.TryDequeue(out blobSize)) { blobSize.Sha.ToBuffer(this.shaBuffer); this.shaParam.Value = this.shaBuffer; this.sizeParam.Value = blobSize.Size; this.addCommand.ExecuteNonQuery(); } insertTransaction.Commit(); } } catch (SqliteException e) { errorCode = e.SqliteErrorCode; error = e.Message; return(false); } return(true); }
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> /// load data from the sql file in Postgresql COPY format /// </summary> static private bool LoadDataFromReader(TDataDefinitionStore ADataDefinition, SqliteConnection conn, StreamReader sr, string ATablename, StringCollection AColumnNames) { using (SqliteTransaction dbTrans = conn.BeginTransaction()) { using (SqliteCommand cmd = conn.CreateCommand()) { TTable table = ADataDefinition.GetTable(ATablename); // prepare the statement PrepareSqlStatement(cmd, ATablename, table, AColumnNames); string line; while ((line = sr.ReadLine()) != "\\.") { ProcessLine(cmd, line, table, AColumnNames); cmd.ExecuteNonQuery(); } } dbTrans.Commit(); } return(true); }
public async Task AddTransactions(IEnumerable <ByteString> transactions) { using (SqliteTransaction context = Connection.BeginTransaction(System.Data.IsolationLevel.Serializable)) { foreach (ByteString rawTransaction in transactions) { byte[] rawTransactionBuffer = rawTransaction.ToByteArray(); Transaction transaction = MessageSerializer.DeserializeTransaction(rawTransaction); byte[] transactionHash = MessageSerializer.ComputeHash(rawTransactionBuffer); byte[] mutationHash = MessageSerializer.ComputeHash(transaction.Mutation.ToByteArray()); Mutation mutation = MessageSerializer.DeserializeMutation(transaction.Mutation); await UpdateAccounts(mutation, mutationHash); await ExecuteAsync(@" INSERT INTO Transactions (Hash, MutationHash, RawData) VALUES (@hash, @mutationHash, @rawData)", new Dictionary <string, object>() { ["@hash"] = transactionHash, ["@mutationHash"] = mutationHash, ["@rawData"] = rawTransactionBuffer }); await AddTransaction(mutation); } context.Commit(); } }
//if this changes, jumpType.cs constructor should change protected internal static void initializeTableJumpRjType() { string [] iniJumpTypes = { //name:startIn:weight:jumpsLimited:limitValue:description "RJ(j):0:0:1:0:RJ limited by jumps", "RJ(t):0:0:0:0:RJ limited by time", "RJ(unlimited):1:0:1:-1:Jump unlimited until finish is clicked", "RJ(hexagon):1:0:1:18:Reactive Jump on a hexagon until three full revolutions are done", "triple jump:0:0:1:3:Triple jump", //"RunAnalysis:0:0:1:-1:Run between two photocells recording contact and flight times in contact platform/s. Until finish button is clicked." }; using (SqliteTransaction tr = dbcon.BeginTransaction()) { using (SqliteCommand dbcmdTr = dbcon.CreateCommand()) { dbcmdTr.Transaction = tr; foreach (string myJumpType in iniJumpTypes) { JumpRjTypeInsert(myJumpType, true, dbcmdTr); } } tr.Commit(); } AddGraphLinksRj(); }
protected override void CommitTransaction() { if (_transaction != null) { _transaction.Commit(); } }
/// <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(); } }
/// <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(); } }
/// <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(); } }
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 void TransactionIsolationTest() { var dir = TestUtils.GetTestDirectoryPath(); using var sqliteContext = new SqliteContext(new SqliteConnectionProvider(Path.Join(dir, "test.db"))); using (var transaction = new SqliteTransaction(sqliteContext, ITransaction.TransactionMode.Create)) { transaction.ExecuteNonQuery(() => @"CREATE TABLE IF NOT EXISTS TestTable (Name TEXT, Data Text);", "create"); transaction.Commit(); } using (var transaction = new SqliteTransaction(sqliteContext, ITransaction.TransactionMode.Mutation)) { transaction.ExecuteNonQuery(() => @"INSERT INTO TestTable (Name, Data) VALUES('foo', 'bar');", "mutation"); using (var queryTransaction = new SqliteTransaction(sqliteContext, ITransaction.TransactionMode.Query, true)) { var result = queryTransaction.ExecuteScalar(() => @"SELECT Data FROM TestTable WHERE Name='foo';", "query"); Assert.AreEqual(null, result); } transaction.Commit(); using (var queryTransaction = new SqliteTransaction(sqliteContext, ITransaction.TransactionMode.Query, true)) { var result = queryTransaction.ExecuteScalar(() => @"SELECT Data FROM TestTable WHERE Name='foo';", "query"); Assert.AreEqual("bar", result); } } }
public void SaveAnchor(byte[] anchor) { SqliteTransaction tran = ActiveConnection.BeginTransaction(); try { using (var cmd = new SqliteCommand(ActiveConnection)) { cmd.Transaction = tran; cmd.CommandText = String.Format("DELETE FROM {0}", AnchorTable); 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; } }
public bool LinkRegion(UUID regionID, int estateID) { 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); } } }
public override void DeleteCharacter(string userId, string id) { 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)); ExecuteNonQuery(transaction, "DELETE FROM friend WHERE characterId1 LIKE @characterId OR characterId2 LIKE @characterId", new SqliteParameter("@characterId", id)); DeleteCharacterAttributes(transaction, id); DeleteCharacterCurrencies(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 AddTransactions(IEnumerable <ByteString> transactions) { using (SqliteTransaction context = Connection.BeginTransaction(IsolationLevel.Serializable)) { foreach (ByteString rawTransaction in transactions) { byte[] rawTransactionBuffer = rawTransaction.ToByteArray(); Transaction transaction = MessageSerializer.DeserializeTransaction(rawTransaction); byte[] transactionHash = MessageSerializer.ComputeHash(rawTransactionBuffer); byte[] mutationHash = MessageSerializer.ComputeHash(transaction.Mutation.ToByteArray()); Mutation mutation = MessageSerializer.DeserializeMutation(transaction.Mutation); await UpdateAccounts(mutation, mutationHash); IReadOnlyList <long> rowId = await ExecuteAsync(@" INSERT INTO Transactions (Hash, MutationHash, RawData) VALUES (@hash, @mutationHash, @rawData); SELECT last_insert_rowid();", reader => (long)reader.GetValue(0), new Dictionary <string, object>() { ["@hash"] = transactionHash, ["@mutationHash"] = mutationHash, ["@rawData"] = rawTransactionBuffer }); await AddTransaction(rowId[0], mutationHash, mutation); } context.Commit(); } }
public void InsertWithTransaction() { _conn.Open(); SqliteTransaction t = _conn.BeginTransaction() as SqliteTransaction; SqliteCommand c1 = new SqliteCommand("INSERT INTO t1 VALUES ('a',0.1,0,'0')", _conn, t); SqliteCommand c2 = new SqliteCommand("INSERT INTO t1 VALUES ('b',1.2,0,'0')", _conn, t); SqliteCommand c3 = new SqliteCommand("INSERT INTO t1 VALUES ('c',0.3,1,'0')", _conn, t); SqliteCommand c4 = new SqliteCommand("INSERT INTO t1 VALUES ('d',0.4,0,'1')", _conn, t); using (_conn) { try { c1.ExecuteNonQuery(); c2.ExecuteNonQuery(); c3.ExecuteNonQuery(); c4.ExecuteNonQuery(); t.Commit(); } catch (Exception e) { t.Rollback(); throw new AssertionException("Sqlite Commands failed", e); } } }
public void InsertWithFailingTransaction() { _conn.Open(); SqliteTransaction t = _conn.BeginTransaction() as SqliteTransaction; SqliteCommand c1 = new SqliteCommand("INSERT INTO t1 VALUES ('1','0','0','0')", _conn, t); SqliteCommand c2 = new SqliteCommand("INSERT INTO t1 VALUES ('0','1','0','0')", _conn, t); SqliteCommand c3 = new SqliteCommand("INSERT INTO t1 VALUES ('x',?,'x',?,'x',?,'x')", _conn, t); SqliteCommand c4 = new SqliteCommand("INSERT INTO t1 VALUES ('0','0','0','1')", _conn, t); using (_conn) { try { c1.ExecuteNonQuery(); c2.ExecuteNonQuery(); c3.ExecuteNonQuery(); c4.ExecuteNonQuery(); t.Commit(); } catch (Exception e) { t.Rollback(); throw e; } } }
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 static void Commit() { try { if (_transaction != null) { _transaction.Commit(); } } catch (Exception) { Log.Debug("Commit collision"); } _transaction = null; }
/// <summary> /// Updates the passed items within the matching SQLite table, and updates the interger primary keys of the objects. /// </summary> /// <param name="items">And IEnumerable of objects to be updated within SQLite.</param> public void Update(IEnumerable <dynamic> items) { if (!items.Any()) { return; } SqliteTransaction transaction = Connection.BeginTransaction(); foreach (var item in items) { MetaSqliteRow sqliteRow = GetMetaDataRowWithValue(item); string updateStatement = sqliteRow.GetUpdateStatement(); SqliteCommand updateCommand = Connection.CreateCommand(); var values = sqliteRow.MetaSqliteCells.Where(x => !x.IsPrimaryId).Select(x => x.Value); int count = 1; foreach (var value in values) { updateCommand.Parameters.AddWithValue($"@param{count++}", value != null ? value : string.Empty); } updateCommand.Transaction = transaction; updateCommand.CommandText = updateStatement; updateCommand.ExecuteNonQuery(); } transaction.Commit(); }
internal static void CreateSchema() { using (var conn = new SqliteConnection("Data Source=" + Program.DbPath + ";")) { conn.Open(); using (SqliteTransaction transaction = conn.BeginTransaction()) using (var command = new SqliteCommand(conn)) { command.Transaction = transaction; command.CommandType = CommandType.Text; command.CommandText = "CREATE TABLE pages (name VARCHAR(100), platform VARCHAR(10), lang VARCHAR(7), local INTEGER)"; command.ExecuteNonQuery(); command.CommandText = "CREATE TABLE config (parameter VARCHAR(20), value VARCHAR(100))"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO config (parameter, value) VALUES(@parameter, @value)"; command.Parameters.AddWithValue("@parameter", "last-update"); command.Parameters.AddWithValue("@value", DateTime.UtcNow.Date.ToString(CultureInfo.InvariantCulture)); command.ExecuteNonQuery(); // Create indexes command.CommandText = "CREATE INDEX os_names ON pages (platform, name)"; command.ExecuteNonQuery(); command.CommandText = "CREATE INDEX lang_names ON pages (lang, name)"; command.ExecuteNonQuery(); command.CommandText = "CREATE INDEX names_index ON pages (lang, platform, name)"; command.ExecuteNonQuery(); transaction.Commit(); } } }
internal static async Task initializeAsync() { SqliteTransaction transaction = CreateTransaction(); SqliteCommand command = CreateCommand(); command.CommandText = "create table starboardChannels (" + " guildId bigint(18) not null unique," + " channelId bigint(18) not null," + " emoji varchar(100) not null," + " amount int not null" + ");" + " " + "create table starboardMessages (" + " guildId bigint(18) not null unique," + " channelId bigint(18) not null," + " messageId bigint(18) not null," + " starboardMessageId bigint(18) not null" + ");" + " " + "create table tags (" + " guildId bigint(18) not null unique," + " userId bigint(18) not null," + " content varchar(2000) not null" + ");"; await command.ExecuteNonQueryAsync(); transaction.Commit(); }
public static void ClearDatabase() { using (SqliteConnection db = CreateSqlConnection()) { db.Open(); string[] tablesToDelete = { "library", "media_file", "calendar", "location", "tag", "playlist", "video", "series", "global_setting", "publisher", "character" }; using (SqliteTransaction txn = db.BeginTransaction()) { foreach (var table in tablesToDelete) { new SqliteCommand($"DELETE FROM {table}", db, txn).ExecuteReader(); } txn.Commit(); } } }
public void SyncComplete(bool success) { if (success) { SqliteTransaction tran = ActiveConnection.BeginTransaction(); try { CreateSchema(); foreach (String tableName in _tablesByIdx) { if (tableName.StartsWith("_Catalog_") || tableName.StartsWith("_Document_")) { using (var cmd = new SqliteCommand(String.Format("UPDATE {0} SET IsDirty = 0 WHERE IsDirty = 1", tableName), ActiveConnection, tran)) cmd.ExecuteNonQuery(); } } Cache.ClearNew(); tran.Commit(); } catch { tran.Rollback(); throw; } } UpdateDbStatus(_userId, success); }
public void AddPosters(IEnumerable <Poster> posters) { Stopwatch watch = new Stopwatch(); watch.Start(); using (SqliteConnection dbWrite = new SqliteConnection(_connect)) { dbWrite.Open(); using (SqliteTransaction trans = dbWrite.BeginTransaction()) { String sql = @"INSERT OR IGNORE INTO Poster (posterid, postername) VALUES (@p3, @p8);"; using (SqliteCommand cmd = new SqliteCommand(sql, dbWrite, trans)) { SqliteParameter pPosterId = new SqliteParameter("@p3"); SqliteParameter pPosterName = new SqliteParameter("@p8"); cmd.Parameters.Add(pPosterName); cmd.Parameters.Add(pPosterId); foreach (Poster p in posters) { pPosterName.Value = p.Name; pPosterId.Value = p.Id; int e = cmd.ExecuteNonQuery(); } } trans.Commit(); } } watch.Stop(); //Trace.TraceInformation("after AddPosters {0}", watch.Elapsed.ToString()); }
/// <summary> /// Delete ip addresses from the database /// </summary> /// <param name="ipAddresses">IP addresses to delete</param> /// <returns>Number of deleted ip addresses</returns> public int DeleteIPAddresses(IEnumerable <string> ipAddresses) { int count = 0; SqliteConnection conn = CreateConnection(); try { OpenConnection(conn); using SqliteTransaction tran = conn.BeginTransaction(TransactionLevel); foreach (string ipAddress in ipAddresses) { if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj)) { ipAddressObj = ipAddressObj.Clean(); count += ExecuteNonQuery("DELETE FROM IPAddresses WHERE IPAddress = @Param0", conn, tran, ipAddressObj.GetAddressBytes()); } } tran.Commit(); } finally { CloseConnection(conn); } return(count); }
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 static void Commit() { if (_transaction != null) { _transaction.Commit(); } _transaction = null; }
public static void Commit() { if (Transaction != null) { Transaction.Commit(); } Transaction = null; }
public void Commit() { if (HasTransaction()) { _transaction.Commit(); _transaction = null; } }