void ManifestCache::save ( DatabaseCon& dbCon, std::string const& dbTable, std::function <bool (PublicKey const&)> isTrusted) { std::lock_guard<std::mutex> lock{apply_mutex_}; auto db = dbCon.checkoutDb (); soci::transaction tr(*db); *db << "DELETE FROM " << dbTable; std::string const sql = "INSERT INTO " + dbTable + " (RawData) VALUES (:rawData);"; for (auto const& v : map_) { // Save all revocation manifests, // but only save trusted non-revocation manifests. if (! v.second.revoked() && ! isTrusted (v.second.masterKey)) { JLOG(j_.info()) << "Untrusted manifest in cache not saved to db"; continue; } // soci does not support bulk insertion of blob data // Do not reuse blob because manifest ecdsa signatures vary in length // but blob write length is expected to be >= the last write soci::blob rawData(*db); convert (v.second.serialized, rawData); *db << sql, soci::use (rawData); } tr.commit (); }
void ManifestCache::load ( DatabaseCon& dbCon, std::string const& dbTable) { // Load manifests stored in database std::string const sql = "SELECT RawData FROM " + dbTable + ";"; auto db = dbCon.checkoutDb (); soci::blob sociRawData (*db); soci::statement st = (db->prepare << sql, soci::into (sociRawData)); st.execute (); while (st.fetch ()) { std::string serialized; convert (sociRawData, serialized); if (auto mo = Manifest::make_Manifest (std::move (serialized))) { if (!mo->verify()) { JLOG(j_.warn()) << "Unverifiable manifest in db"; continue; } applyManifest (std::move(*mo)); } else { JLOG(j_.warn()) << "Malformed manifest in database"; } } }
bool SHAMapStoreImp::clearSql (DatabaseCon& database, LedgerIndex lastRotated, std::string const& minQuery, std::string const& deleteQuery) { LedgerIndex min = std::numeric_limits <LedgerIndex>::max(); { auto db = database.checkoutDb (); boost::optional<std::uint64_t> m; *db << minQuery, soci::into(m); if (!m) return false; min = *m; } if(min > lastRotated || health() != Health::ok) return false; boost::format formattedDeleteQuery (deleteQuery); JLOG(journal_.debug()) << "start: " << deleteQuery << " from " << min << " to " << lastRotated; while (min < lastRotated) { min = std::min(lastRotated, min + setup_.deleteBatch); { auto db = database.checkoutDb (); *db << boost::str (formattedDeleteQuery % min); } if (health()) return true; if (min < lastRotated) std::this_thread::sleep_for ( std::chrono::milliseconds (setup_.backOff)); } JLOG(journal_.debug()) << "finished: " << deleteQuery; return true; }
void accountTxPage ( DatabaseCon& connection, AccountIDCache const& idCache, std::function<void (std::uint32_t)> const& onUnsavedLedger, std::function<void (std::uint32_t, std::string const&, Blob const&, Blob const&)> const& onTransaction, AccountID const& account, std::int32_t minLedger, std::int32_t maxLedger, bool forward, Json::Value& token, int limit, bool bAdmin, std::uint32_t page_length) { bool lookingForMarker = token.isObject(); std::uint32_t numberOfResults; if (limit <= 0 || (limit > page_length && !bAdmin)) numberOfResults = page_length; else numberOfResults = limit; // As an account can have many thousands of transactions, there is a limit // placed on the amount of transactions returned. If the limit is reached // before the result set has been exhausted (we always query for one more // than the limit), then we return an opaque marker that can be supplied in // a subsequent query. std::uint32_t queryLimit = numberOfResults + 1; std::uint32_t findLedger = 0, findSeq = 0; if (lookingForMarker) { try { if (!token.isMember(jss::ledger) || !token.isMember(jss::seq)) return; findLedger = token[jss::ledger].asInt(); findSeq = token[jss::seq].asInt(); } catch (std::exception const&) { return; } } // We're using the token reference both for passing inputs and outputs, so // we need to clear it in between. token = Json::nullValue; static std::string const prefix ( R"(SELECT AccountTransactions.LedgerSeq,AccountTransactions.TxnSeq, Status,RawTxn,TxnMeta FROM AccountTransactions INNER JOIN Transactions ON Transactions.TransID = AccountTransactions.TransID AND AccountTransactions.Account = '%s' WHERE )"); std::string sql; // SQL's BETWEEN uses a closed interval ([a,b]) if (forward && (findLedger == 0)) { sql = boost::str (boost::format( prefix + (R"(AccountTransactions.LedgerSeq BETWEEN '%u' AND '%u' ORDER BY AccountTransactions.LedgerSeq ASC, AccountTransactions.TxnSeq ASC LIMIT %u;)")) % idCache.toBase58(account) % minLedger % maxLedger % queryLimit); } else if (forward && (findLedger != 0)) { auto b58acct = idCache.toBase58(account); sql = boost::str (boost::format( (R"(SELECT AccountTransactions.LedgerSeq,AccountTransactions.TxnSeq, Status,RawTxn,TxnMeta FROM AccountTransactions, Transactions WHERE (AccountTransactions.TransID = Transactions.TransID AND AccountTransactions.Account = '%s' AND AccountTransactions.LedgerSeq BETWEEN '%u' AND '%u') OR (AccountTransactions.TransID = Transactions.TransID AND AccountTransactions.Account = '%s' AND AccountTransactions.LedgerSeq = '%u' AND AccountTransactions.TxnSeq >= '%u') ORDER BY AccountTransactions.LedgerSeq ASC, AccountTransactions.TxnSeq ASC LIMIT %u; )")) % b58acct % (findLedger + 1) % maxLedger % b58acct % findLedger % findSeq % queryLimit); } else if (!forward && (findLedger == 0)) { sql = boost::str (boost::format( prefix + (R"(AccountTransactions.LedgerSeq BETWEEN '%u' AND '%u' ORDER BY AccountTransactions.LedgerSeq DESC, AccountTransactions.TxnSeq DESC LIMIT %u;)")) % idCache.toBase58(account) % minLedger % maxLedger % queryLimit); } else if (!forward && (findLedger != 0)) { auto b58acct = idCache.toBase58(account); sql = boost::str (boost::format( (R"(SELECT AccountTransactions.LedgerSeq,AccountTransactions.TxnSeq, Status,RawTxn,TxnMeta FROM AccountTransactions, Transactions WHERE (AccountTransactions.TransID = Transactions.TransID AND AccountTransactions.Account = '%s' AND AccountTransactions.LedgerSeq BETWEEN '%u' AND '%u') OR (AccountTransactions.TransID = Transactions.TransID AND AccountTransactions.Account = '%s' AND AccountTransactions.LedgerSeq = '%u' AND AccountTransactions.TxnSeq <= '%u') ORDER BY AccountTransactions.LedgerSeq DESC, AccountTransactions.TxnSeq DESC LIMIT %u; )")) % b58acct % minLedger % (findLedger - 1) % b58acct % findLedger % findSeq % queryLimit); } else { assert (false); // sql is empty return; } { auto db (connection.checkoutDb()); Blob rawData; Blob rawMeta; boost::optional<std::uint64_t> ledgerSeq; boost::optional<std::uint32_t> txnSeq; boost::optional<std::string> status; soci::blob txnData (*db); soci::blob txnMeta (*db); soci::indicator dataPresent, metaPresent; soci::statement st = (db->prepare << sql, soci::into (ledgerSeq), soci::into (txnSeq), soci::into (status), soci::into (txnData, dataPresent), soci::into (txnMeta, metaPresent)); st.execute (); while (st.fetch ()) { if (lookingForMarker) { if (findLedger == ledgerSeq.value_or (0) && findSeq == txnSeq.value_or (0)) { lookingForMarker = false; } } else if (numberOfResults == 0) { token = Json::objectValue; token[jss::ledger] = rangeCheckedCast<std::uint32_t>(ledgerSeq.value_or (0)); token[jss::seq] = txnSeq.value_or (0); break; } if (!lookingForMarker) { if (dataPresent == soci::i_ok) convert (txnData, rawData); else rawData.clear (); if (metaPresent == soci::i_ok) convert (txnMeta, rawMeta); else rawMeta.clear (); // Work around a bug that could leave the metadata missing if (rawMeta.size() == 0) onUnsavedLedger(ledgerSeq.value_or (0)); onTransaction(rangeCheckedCast<std::uint32_t>(ledgerSeq.value_or (0)), *status, rawData, rawMeta); --numberOfResults; } } } return; }