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;
}
}
