本文主要演示sqlite数据库 增删改查创建数据库以及数据库表的基本操作,仅供学习参考。
一、sqlite数据库操作类封装:
sql\database.h
sql\database.cc
// Copyright 2012 The Chromium Authors
// Use of this source code is governed by a BSD-style license that can be
// found in the LICENSE file.#ifndef SQL_DATABASE_H_
#define SQL_DATABASE_H_#include <stddef.h>
#include <stdint.h>#include <memory>
#include <set>
#include <string>
#include <utility>
#include <vector>#include <optional>
#include "base/component_export.h"
#include "base/containers/flat_map.h"
#include "base/dcheck_is_on.h"
#include "base/feature_list.h"
#include "base/files/file_path.h"
#include "base/functional/callback.h"
#include "base/gtest_prod_util.h"
#include "base/memory/raw_ptr.h"
#include "base/memory/raw_ptr_exclusion.h"
#include "base/memory/ref_counted.h"
#include "base/sequence_checker.h"
#include "base/strings/string_piece.h"
#include "base/threading/scoped_blocking_call.h"
#include "base/types/pass_key.h"
#include "sql/internal_api_token.h"
#include "sql/sql_features.h"
#include "sql/sqlite_result_code.h"
#include "sql/sqlite_result_code_values.h"
#include "sql/statement_id.h"// Forward declaration for SQLite structures. Headers in the public sql:: API
// must NOT include sqlite3.h.
struct sqlite3;
struct sqlite3_file;
struct sqlite3_stmt;namespace base::trace_event {
class ProcessMemoryDump;
} // namespace base::trace_eventnamespace perfetto::protos::pbzero {
class ChromeSqlDiagnostics;
}namespace sql {class DatabaseMemoryDumpProvider;
class Recovery;
class Statement;namespace test {
class ScopedErrorExpecter;
} // namespace teststruct COMPONENT_EXPORT(SQL) DatabaseOptions {// Default page size for newly created databases.//// Guaranteed to match SQLITE_DEFAULT_PAGE_SIZE.static constexpr int kDefaultPageSize = 4096;// If true, the database can only be opened by one process at a time.//// SQLite supports a locking protocol that allows multiple processes to safely// operate on the same database at the same time. The locking protocol is used// on every transaction, and comes with a small performance penalty.//// Setting this to true causes the locking protocol to be used once, when the// database is opened. No other SQLite process will be able to access the// database at the same time. Note that this uses OS-level// advisory/cooperative locking, so this does not protect the database file// from uncooperative processes.//// More details at https://www.sqlite.org/pragma.html#pragma_locking_mode//// SQLite's locking protocol is summarized at// https://www.sqlite.org/c3ref/io_methods.html//// Exclusive mode is strongly recommended. It reduces the I/O cost of setting// up a transaction. It also removes the need of handling transaction failures// due to lock contention.bool exclusive_locking = true;// If true, enables exclusive=true vfs URI parameter on the database file.// This is only supported on Windows.//// If this option is true then the database file cannot be opened by any// processes on the system until the database has been closed. Note, this is// not the same as `exclusive_locking` above, which refers to// advisory/cooperative locks. This option sets file handle sharing attributes// to prevent the database files from being opened from any process including// being opened a second time by the hosting process.//// A side effect of setting this flag is that the database cannot be// preloaded. If you would like to set this flag on a preloaded database,// please reach out to a //sql owner.//// This option is experimental and will be merged into the `exclusive_locking`// option above if proven to cause no OS compatibility issues.// TODO(crbug.com/1429117): Merge into above option, if possible.bool exclusive_database_file_lock = false;// If true, enables SQLite's Write-Ahead Logging (WAL).//// WAL integration is under development, and should not be used in shipping// Chrome features yet. In particular, our custom database recovery code does// not support the WAL log file.//// WAL mode is currently not fully supported on FuchsiaOS. It will only be// turned on if the database is also using exclusive locking mode.// (https://crbug.com/1082059)//// Note: Changing page size is not supported when in WAL mode. So running// 'PRAGMA page_size = <new-size>' will result in no-ops.//// More details at https://www.sqlite.org/wal.htmlbool wal_mode =base::FeatureList::IsEnabled(sql::features::kEnableWALModeByDefault);// If true, transaction commit waits for data to reach persistent media.//// This is currently only meaningful on macOS. All other operating systems// only support flushing directly to disk.//// If both `flush_to_media` and `wal_mode` are false, power loss can lead to// database corruption.//// By default, SQLite considers that transactions commit when they reach the// disk controller's memory. This guarantees durability in the event of// software crashes, up to and including the operating system. In the event of// power loss, SQLite may lose data. If `wal_mode` is false (SQLite uses a// rollback journal), power loss can lead to database corruption.//// When this option is enabled, committing a transaction causes SQLite to wait// until the data is written to the persistent media. This guarantees// durability in the event of power loss, which is needed to guarantee the// integrity of non-WAL databases.bool flush_to_media = false;// Database page size.//// New Chrome features should set an explicit page size in their// DatabaseOptions initializers, even if they use the default page size. This// makes it easier to track the page size used by the databases on the users'// devices.//// The value in this option is only applied to newly created databases. In// other words, changing the value doesn't impact the databases that have// already been created on the users' devices. So, changing the value in the// code without a lot of work (re-creating existing databases) will result in// inconsistent page sizes across the fleet of user devices, which will make// it (even) more difficult to reason about database performance.//// Larger page sizes result in shallower B-trees, because they allow an inner// page to hold more keys. On the flip side, larger page sizes may result in// more I/O when making small changes to existing records.//// Must be a power of two between 512 and 65536 inclusive.//// TODO(pwnall): Replace the default with an invalid value after all// sql::Database users explicitly initialize page_size.int page_size = kDefaultPageSize;// The size of in-memory cache, in pages.//// New Chrome features should set an explicit cache size in their// DatabaseOptions initializers, even if they use the default cache size. This// makes it easier to track the cache size used by the databases on the users'// devices. The default page size of 4,096 bytes results in a cache size of// 500 pages.//// SQLite's database cache will take up at most (`page_size` * `cache_size`)// bytes of RAM.//// 0 invokes SQLite's default, which is currently to size up the cache to use// exactly 2,048,000 bytes of RAM.//// TODO(pwnall): Replace the default with an invalid value after all// sql::Database users explicitly initialize page_size.int cache_size = 0;// Stores mmap failures in the SQL schema, instead of the meta table.//// This option is strongly discouraged for new databases, and will eventually// be removed.//// If this option is true, the mmap status is stored in the database schema.// Like any other schema change, changing the mmap status invalidates all// pre-compiled SQL statements.bool mmap_alt_status_discouraged = false;// If true, enables SQL views (a discouraged feature) for this database.//// The use of views is discouraged for Chrome code. See README.md for details// and recommended replacements.//// If this option is false, CREATE VIEW and DROP VIEW succeed, but SELECT// statements targeting views fail.bool enable_views_discouraged = false;// If true, enables virtual tables (a discouraged feature) for this database.//// The use of virtual tables is discouraged for Chrome code. See README.md for// details and recommended replacements.//// If this option is false, CREATE VIRTUAL TABLE and DROP VIRTUAL TABLE// succeed, but statements targeting virtual tables fail.bool enable_virtual_tables_discouraged = false;
};// Holds database diagnostics in a structured format.
struct COMPONENT_EXPORT(SQL) DatabaseDiagnostics {DatabaseDiagnostics();~DatabaseDiagnostics();using TraceProto = perfetto::protos::pbzero::ChromeSqlDiagnostics;// Write a representation of this object into tracing proto.void WriteIntoTrace(perfetto::TracedProto<TraceProto> context) const;// This was the original error code that triggered the error callback. Should// generally match `error_code`, but this isn't guaranteed by the code.int reported_sqlite_error_code = 0;// Corresponds to `Database::GetErrorCode()`.int error_code = 0;// Corresponds to `Database::GetLastErrno()`.int last_errno = 0;// Corresponds to `Statement::GetSQLStatement()` of the problematic statement.// This doesn't include the bound values, and therefore is free of any PII.std::string sql_statement;// The 'version' value stored in the user database's meta table, if it can be// read. If we fail to read the version of the user database, it's left as 0.int version = 0;// Most rows in 'sql_schema' have a non-NULL 'sql' column. Those rows' 'sql'// contents are logged here, one element per row.std::vector<std::string> schema_sql_rows;// Some rows of 'sql_schema' have a NULL 'sql' column. They are typically// autogenerated indices, like "sqlite_autoindex_downloads_slices_1". These// are also logged here by their 'name' column, one element per row.std::vector<std::string> schema_other_row_names;// Sanity checks used for all errors.bool has_valid_header = false;bool has_valid_schema = false;// Corresponds to `Database::GetErrorMessage()`.std::string error_message;
};// Handle to an open SQLite database.
//
// Instances of this class are not thread-safe. After construction, a Database
// instance should only be accessed from one sequence.
//
// When a Database instance goes out of scope, any uncommitted transactions are
// rolled back.
class COMPONENT_EXPORT(SQL) Database {private:class StatementRef; // Forward declaration, see real one below.public:// Creates an instance that can receive Open() / OpenInMemory() calls.//// Some `options` members are only applied to newly created databases.//// Most operations on the new instance will fail until Open() / OpenInMemory()// is called.explicit Database(DatabaseOptions options);// This constructor is deprecated.//// When transitioning away from this default constructor, consider setting// DatabaseOptions::explicit_locking to true. For historical reasons, this// constructor results in DatabaseOptions::explicit_locking set to false.//// TODO(crbug.com/1126968): Remove this constructor after migrating all// uses to the explicit constructor below.Database();Database(const Database&) = delete;Database& operator=(const Database&) = delete;Database(Database&&) = delete;Database& operator=(Database&&) = delete;~Database();// Allows mmapping to be disabled globally by default in the calling process.// Must be called before any threads attempt to create a Database.//// TODO(crbug.com/1117049): Remove this global configuration.static void DisableMmapByDefault();// Pre-init configuration ----------------------------------------------------// The page size that will be used when creating a new database.int page_size() const { return options_.page_size; }// Returns whether a database will be opened in WAL mode.bool UseWALMode() const;// Opt out of memory-mapped file I/O.void set_mmap_disabled() { mmap_disabled_ = true; }// Set an error-handling callback. On errors, the error number (and// statement, if available) will be passed to the callback.//// If no callback is set, the default error-handling behavior is invoked. The// default behavior is to LOGs the error and propagate the failure.//// In DCHECK-enabled builds, the default error-handling behavior currently// DCHECKs on errors. This is not correct, because DCHECKs are supposed to// cover invariants and never fail, whereas SQLite errors can surface even on// correct usage, due to I/O errors and data corruption. At some point in the// future, errors will not result in DCHECKs.//// The callback will be called on the sequence used for database operations.// The callback will never be called after the Database instance is destroyed.using ErrorCallback = base::RepeatingCallback<void(int, Statement*)>;void set_error_callback(ErrorCallback callback) {DCHECK(!callback.is_null()) << "Use reset_error_callback() explicitly";DCHECK(error_callback_.is_null())<< "Overwriting previously set error callback";error_callback_ = std::move(callback);}void reset_error_callback() { error_callback_.Reset(); }bool has_error_callback() const { return !error_callback_.is_null(); }// Developer-friendly database ID used in logging output and memory dumps.void set_histogram_tag(const std::string& histogram_tag) {DCHECK(!is_open());histogram_tag_ = histogram_tag;}const std::string& histogram_tag() const { return histogram_tag_; }// Asks SQLite to perform a full integrity check on the database.//// Returns true if the integrity check was completed successfully. Success// does not necessarily entail that the database is healthy. Finding// corruption and reporting it in `messages` counts as success.//// If the method returns true, `messages` is populated with a list of// diagnostic messages. If the integrity check finds no errors, `messages`// will contain exactly one "ok" string. This unusual API design is explained// by the fact that SQLite exposes integrity check functionality as a PRAGMA,// and the PRAGMA returns "ok" in case of success.bool FullIntegrityCheck(std::vector<std::string>* messages);// Meant to be called from a client error callback so that it's able to// get diagnostic information about the database. `diagnostics` is an optional// out parameter. If `diagnostics` is defined, this method populates all of// its fields.std::string GetDiagnosticInfo(int extended_error,Statement* statement,DatabaseDiagnostics* diagnostics = nullptr);// Reports memory usage into provided memory dump with the given name.bool ReportMemoryUsage(base::trace_event::ProcessMemoryDump* pmd,const std::string& dump_name);// Initialization ------------------------------------------------------------// Opens or creates a database on disk.//// `db_file_path` points to the file storing database pages. Other files// associated with the database (rollback journal, write-ahead log,// shared-memory file) may be created.//// Returns true in case of success, false in case of failure.[[nodiscard]] bool Open(const base::FilePath& db_file_path);// Alternative to Open() that creates an in-memory database.//// Returns true in case of success, false in case of failure.//// The memory associated with the database will be released when the database// is closed.[[nodiscard]] bool OpenInMemory();// Alternative to Open() that creates a temporary on-disk database.//// Returns true in case of success, false in case of failure.//// The files associated with the temporary database will be deleted when the// database is closed.[[nodiscard]] bool OpenTemporary(base::PassKey<Recovery>);// Returns true if the database has been successfully opened.bool is_open() const;// Closes the database. This is automatically performed on destruction for// you, but this allows you to close the database early. You must not call// any other functions after closing it. It is permissable to call Close on// an uninitialized or already-closed database.void Close();// Hints the file system that the database will be accessed soon.//// This method should be called on databases that are on the critical path to// Chrome startup. Informing the filesystem about our expected access pattern// early on reduces the likelihood that we'll be blocked on disk I/O. This has// a high impact on startup time.//// This method should not be used for non-critical databases. While using it// will likely improve micro-benchmarks involving one specific database,// overuse risks randomizing the disk I/O scheduler, slowing down Chrome// startup.void Preload();// Release all non-essential memory associated with this database connection.void TrimMemory();// Raze the database to the ground. This approximates creating a// fresh database from scratch, within the constraints of SQLite's// locking protocol (locks and open handles can make doing this with// filesystem operations problematic). Returns true if the database// was razed.//// false is returned if the database is locked by some other// process.//// NOTE(shess): Raze() will DCHECK in the following situations:// - database is not open.// - the database has a transaction open.// - a SQLite issue occurs which is structural in nature (like the// statements used are broken).// Since Raze() is expected to be called in unexpected situations,// these all return false, since it is unlikely that the caller// could fix them.//// The database's page size is taken from |options_.page_size|. The// existing database's |auto_vacuum| setting is lost (the// possibility of corruption makes it unreliable to pull it from the// existing database). To re-enable on the empty database requires// running "PRAGMA auto_vacuum = 1;" then "VACUUM".//// NOTE(shess): For Android, SQLITE_DEFAULT_AUTOVACUUM is set to 1,// so Raze() sets auto_vacuum to 1.//// TODO(shess): Raze() needs a database so cannot clear SQLITE_NOTADB.// TODO(shess): Bake auto_vacuum into Database's API so it can// just pick up the default.bool Raze();// Breaks all outstanding transactions (as initiated by// BeginTransaction()), closes the SQLite database, and poisons the// object so that all future operations against the Database (or// its Statements) fail safely, without side effects.//// This is intended as an alternative to Close() in error callbacks.// Close() should still be called at some point.void Poison();// `Raze()` the database and `Poison()` the handle. Returns the return// value from `Raze()`.bool RazeAndPoison();// Delete the underlying database files associated with |path|. This should be// used on a database which is not opened by any Database instance. Open// Database instances pointing to the database can cause odd results or// corruption (for instance if a hot journal is deleted but the associated// database is not).//// Returns true if the database file and associated journals no// longer exist, false otherwise. If the database has never// existed, this will return true.static bool Delete(const base::FilePath& path);// Transactions --------------------------------------------------------------// Transaction management. We maintain a virtual transaction stack to emulate// nested transactions since sqlite can't do nested transactions. The// limitation is you can't roll back a sub transaction: if any transaction// fails, all transactions open will also be rolled back. Any nested// transactions after one has rolled back will return fail for Begin(). If// Begin() fails, you must not call Commit or Rollback().//// Normally you should use sql::Transaction to manage a transaction, which// will scope it to a C++ context.bool BeginTransaction();void RollbackTransaction();bool CommitTransaction();// Rollback all outstanding transactions. Use with care, there may// be scoped transactions on the stack.void RollbackAllTransactions();bool HasActiveTransactions() const {DCHECK_GE(transaction_nesting_, 0);return transaction_nesting_ > 0;}// Deprecated in favor of HasActiveTransactions().//// Returns the current transaction nesting, which will be 0 if there are// no open transactions.int transaction_nesting() const { return transaction_nesting_; }// Attached databases---------------------------------------------------------// Attaches an existing database to this connection.//// `attachment_point` must only contain lowercase letters.//// Attachment APIs are only exposed for use in recovery. General use is// discouraged in Chrome. The README has more details.//// On the SQLite version shipped with Chrome (3.21+, Oct 2017), databases can// be attached while a transaction is opened. However, these databases cannot// be detached until the transaction is committed or aborted.bool AttachDatabase(const base::FilePath& other_db_path,base::StringPiece attachment_point,InternalApiToken);// Detaches a database that was previously attached with AttachDatabase().//// `attachment_point` must match the argument of a previously successsful// AttachDatabase() call.//// Attachment APIs are only exposed for use in recovery. General use is// discouraged in Chrome. The README has more details.bool DetachDatabase(base::StringPiece attachment_point, InternalApiToken);// Statements ----------------------------------------------------------------// Executes a SQL statement. Returns true for success, and false for failure.//// `sql` should be a single SQL statement. Production code should not execute// multiple SQL statements at once, to facilitate crash debugging. Test code// should use ExecuteScriptForTesting().//// `sql` cannot have parameters. Statements with parameters can be handled by// sql::Statement. See GetCachedStatement() and GetUniqueStatement().[[nodiscard]] bool Execute(const char* sql);// Executes a sequence of SQL statements.//// Returns true if all statements execute successfully. If a statement fails,// stops and returns false. Calls should be wrapped in ASSERT_TRUE().//// The database's error handler is not invoked when errors occur. This method// is a convenience for setting up a complex on-disk database state, such as// an old schema version with test contents.[[nodiscard]] bool ExecuteScriptForTesting(const char* sql_script);// Returns a statement for the given SQL using the statement cache. It can// take a nontrivial amount of work to parse and compile a statement, so// keeping commonly-used ones around for future use is important for// performance.//// The SQL_FROM_HERE macro is the recommended way of generating a StatementID.// Code that generates custom IDs must ensure that a StatementID is never used// for different SQL statements. Failing to meet this requirement results in// incorrect behavior, and should be caught by a DCHECK.//// The SQL statement passed in |sql| must match the SQL statement reported// back by SQLite. Mismatches are caught by a DCHECK, so any code that has// automated test coverage or that was manually tested on a DCHECK build will// not exhibit this problem. Mismatches generally imply that the statement// passed in has extra whitespace or comments surrounding it, which waste// storage and CPU cycles.//// If the |sql| has an error, an invalid, inert StatementRef is returned (and// the code will crash in debug). The caller must deal with this eventuality,// either by checking validity of the |sql| before calling, by correctly// handling the return of an inert statement, or both.//// Example:// sql::Statement stmt(database_.GetCachedStatement(// SQL_FROM_HERE, "SELECT * FROM foo"));// if (!stmt)// return false; // Error creating statement.scoped_refptr<StatementRef> GetCachedStatement(StatementID id,const char* sql);// Used to check a |sql| statement for syntactic validity. If the statement is// valid SQL, returns true.bool IsSQLValid(const char* sql);// Returns a non-cached statement for the given SQL. Use this for SQL that// is only executed once or only rarely (there is overhead associated with// keeping a statement cached).//// See GetCachedStatement above for examples and error information.scoped_refptr<StatementRef> GetUniqueStatement(const char* sql);// Returns a non-cached statement same as `GetUniqueStatement()`, except// returns an invalid statement if the statement makes direct changes to the// database file. This readonly check does not include changes made by// application-defined functions. See more at:// https://www.sqlite.org/c3ref/stmt_readonly.html.scoped_refptr<Database::StatementRef> GetReadonlyStatement(const char* sql);// Performs a passive checkpoint on the main attached database if it is in// WAL mode. Returns true if the checkpoint was successful and false in case// of an error. It is a no-op if the database is not in WAL mode.//// Note: Checkpointing is a very slow operation and will block any writes// until it is finished. Please use with care.bool CheckpointDatabase();// Info querying -------------------------------------------------------------// Returns true if the given structure exists. Instead of test-then-create,// callers should almost always prefer the "IF NOT EXISTS" version of the// CREATE statement.bool DoesIndexExist(base::StringPiece index_name);bool DoesTableExist(base::StringPiece table_name);bool DoesViewExist(base::StringPiece table_name);// Returns true if a column with the given name exists in the given table.//// Calling this method on a VIEW returns an unspecified result.//// This should only be used by migration code for legacy features that do not// use MetaTable, and need an alternative way of figuring out the database's// current version.bool DoesColumnExist(const char* table_name, const char* column_name);// Returns sqlite's internal ID for the last inserted row. Valid only// immediately after an insert.int64_t GetLastInsertRowId() const;// Returns sqlite's count of the number of rows modified by the last// statement executed. Will be 0 if no statement has executed or the database// is closed.int64_t GetLastChangeCount();// Approximates the amount of memory used by SQLite for this database.//// This measures the memory used for the page cache (most likely the biggest// consumer), database schema, and prepared statements.//// The memory used by the page cache can be recovered by calling TrimMemory(),// which will cause SQLite to drop the page cache.int GetMemoryUsage();// Errors --------------------------------------------------------------------// Returns the error code associated with the last sqlite operation.int GetErrorCode() const;// Returns the errno associated with GetErrorCode(). See// SQLITE_LAST_ERRNO in SQLite documentation.int GetLastErrno() const;// Returns a pointer to a statically allocated string associated with the// last sqlite operation.const char* GetErrorMessage() const;// Return a reproducible representation of the schema equivalent to// running the following statement at a sqlite3 command-line:// SELECT type, name, tbl_name, sql FROM sqlite_schema ORDER BY 1, 2, 3, 4;std::string GetSchema();// Returns |true| if there is an error expecter (see SetErrorExpecter), and// that expecter returns |true| when passed |error|. Clients which provide an// |error_callback| should use IsExpectedSqliteError() to check for unexpected// errors; if one is detected, DLOG(DCHECK) is generally appropriate (see// OnSqliteError implementation).static bool IsExpectedSqliteError(int sqlite_error_code);// Computes the path of a database's rollback journal.//// The journal file is created at the beginning of the database's first// transaction. The file may be removed and re-created between transactions,// depending on whether the database is opened in exclusive mode, and on// configuration options. The journal file does not exist when the database// operates in WAL mode.//// This is intended for internal use and tests. To preserve our ability to// iterate on our SQLite configuration, features must avoid relying on// the existence of specific files.static base::FilePath JournalPath(const base::FilePath& db_path);// Computes the path of a database's write-ahead log (WAL).//// The WAL file exists while a database is opened in WAL mode.//// This is intended for internal use and tests. To preserve our ability to// iterate on our SQLite configuration, features must avoid relying on// the existence of specific files.static base::FilePath WriteAheadLogPath(const base::FilePath& db_path);// Computes the path of a database's shared memory (SHM) file.//// The SHM file is used to coordinate between multiple processes using the// same database in WAL mode. Thus, this file only exists for databases using// WAL and not opened in exclusive mode.//// This is intended for internal use and tests. To preserve our ability to// iterate on our SQLite configuration, features must avoid relying on// the existence of specific files.static base::FilePath SharedMemoryFilePath(const base::FilePath& db_path);// Internal state accessed by other classes in //sql.sqlite3* db(InternalApiToken) const { return db_; }bool poisoned(InternalApiToken) const { return poisoned_; }base::FilePath DbPath(InternalApiToken) const { return DbPath(); }// Interface with sql::test::ScopedErrorExpecter.using ScopedErrorExpecterCallback = base::RepeatingCallback<bool(int)>;static void SetScopedErrorExpecter(ScopedErrorExpecterCallback* expecter,base::PassKey<test::ScopedErrorExpecter>);static void ResetScopedErrorExpecter(base::PassKey<test::ScopedErrorExpecter>);private:// Statement accesses StatementRef which we don't want to expose to everybody// (they should go through Statement).friend class Statement;FRIEND_TEST_ALL_PREFIXES(SQLDatabaseTest, CachedStatement);FRIEND_TEST_ALL_PREFIXES(SQLDatabaseTest, CollectDiagnosticInfo);FRIEND_TEST_ALL_PREFIXES(SQLDatabaseTest, ComputeMmapSizeForOpen);FRIEND_TEST_ALL_PREFIXES(SQLDatabaseTest, ComputeMmapSizeForOpenAltStatus);FRIEND_TEST_ALL_PREFIXES(SQLDatabaseTest, OnMemoryDump);FRIEND_TEST_ALL_PREFIXES(SQLDatabaseTest, RegisterIntentToUpload);FRIEND_TEST_ALL_PREFIXES(SQLiteFeaturesTest, WALNoClose);FRIEND_TEST_ALL_PREFIXES(SQLEmptyPathDatabaseTest, EmptyPathTest);// Enables a special behavior for OpenInternal().enum class OpenMode {// No special behavior.kNone = 0,// Retry if the database error handler is invoked and closes the database.// Database error handlers that call RazeAndPoison() take advantage of this.kRetryOnPoision = 1,// Open an in-memory database. Used by OpenInMemory().kInMemory = 2,// Open a temporary database. Used by OpenTemporary().kTemporary = 3,};// Implements Open(), OpenInMemory(), and OpenTemporary().//// `db_file_path` is a UTF-8 path to the file storing the database pages. The// path must be empty if `mode` is kTemporary. The path must be the SQLite// magic memory path string if `mode` is kMemory.bool OpenInternal(const std::string& file_name, OpenMode mode);// Configures the underlying sqlite3* object via sqlite3_db_config().//// To minimize the number of possible SQLite code paths executed in Chrome,// this method must be called right after the underlying sqlite3* object is// obtained from sqlite3_open*(), before any other sqlite3_*() methods are// called on the object.void ConfigureSqliteDatabaseObject();// Internal close function used by Close() and RazeAndPoison().// |forced| indicates that orderly-shutdown checks should not apply.void CloseInternal(bool forced);// Construct a ScopedBlockingCall to annotate IO calls, but only if// database wasn't open in memory. ScopedBlockingCall uses |from_here| to// declare its blocking execution scope (see https://www.crbug/934302).void InitScopedBlockingCall(const base::Location& from_here,std::optional<base::ScopedBlockingCall>* scoped_blocking_call) const {if (!in_memory_)scoped_blocking_call->emplace(from_here, base::BlockingType::MAY_BLOCK);}// Internal helper for Does*Exist() functions.bool DoesSchemaItemExist(base::StringPiece name, base::StringPiece type);// Used to implement the interface with sql::test::ScopedErrorExpecter.static ScopedErrorExpecterCallback* current_expecter_cb_;// A StatementRef is a refcounted wrapper around a sqlite statement pointer.// Refcounting allows us to give these statements out to sql::Statement// objects while also optionally maintaining a cache of compiled statements// by just keeping a refptr to these objects.//// A statement ref can be valid, in which case it can be used, or invalid to// indicate that the statement hasn't been created yet, has an error, or has// been destroyed.//// The Database may revoke a StatementRef in some error cases, so callers// should always check validity before using.class COMPONENT_EXPORT(SQL) StatementRef: public base::RefCounted<StatementRef> {public:REQUIRE_ADOPTION_FOR_REFCOUNTED_TYPE();// |database| is the sql::Database instance associated with// the statement, and is used for tracking outstanding statements// and for error handling. Set to nullptr for invalid refs.// |stmt| is the actual statement, and should only be null// to create an invalid ref. |was_valid| indicates whether the// statement should be considered valid for diagnostic purposes.// |was_valid| can be true for a null |stmt| if the Database has// been forcibly closed by an error handler.StatementRef(Database* database, sqlite3_stmt* stmt, bool was_valid);StatementRef(const StatementRef&) = delete;StatementRef& operator=(const StatementRef&) = delete;StatementRef(StatementRef&&) = delete;StatementRef& operator=(StatementRef&&) = delete;// When true, the statement can be used.bool is_valid() const { return !!stmt_; }// When true, the statement is either currently valid, or was// previously valid but the database was forcibly closed. Used// for diagnostic checks.bool was_valid() const { return was_valid_; }// If we've not been linked to a database, this will be null.Database* database() const { return database_; }// Returns the sqlite statement if any. If the statement is not active,// this will return nullptr.sqlite3_stmt* stmt() const { return stmt_; }// Destroys the compiled statement and sets it to nullptr. The statement// will no longer be active. |forced| is used to indicate if// orderly-shutdown checks should apply (see Database::RazeAndPoison()).void Close(bool forced);// Construct a ScopedBlockingCall to annotate IO calls, but only if// database wasn't open in memory. ScopedBlockingCall uses |from_here| to// declare its blocking execution scope (see https://www.crbug/934302).void InitScopedBlockingCall(const base::Location& from_here,std::optional<base::ScopedBlockingCall>* scoped_blocking_call) const {if (database_)database_->InitScopedBlockingCall(from_here, scoped_blocking_call);}private:friend class base::RefCounted<StatementRef>;~StatementRef();raw_ptr<Database> database_;raw_ptr<sqlite3_stmt> stmt_;bool was_valid_;};friend class StatementRef;// Executes a rollback statement, ignoring all transaction state. Used// internally in the transaction management code.void DoRollback();// Called by a StatementRef when it's being created or destroyed. See// open_statements_ below.void StatementRefCreated(StatementRef* ref);void StatementRefDeleted(StatementRef* ref);// Used by sql:: internals to report a SQLite error related to this database.//// `sqlite_error_code` contains the error code reported by SQLite. Possible// values are documented at https://www.sqlite.org/rescode.html//// `statement` is non-null if the error is associated with a sql::Statement.// Otherwise, `sql_statement` will be a non-null string pointing to a// statically-allocated (valid for the entire duration of the process) buffer// pointing to either a SQL statement or a SQL comment (starting with "-- ")// pointing to a "sqlite3_" function name.void OnSqliteError(SqliteErrorCode sqlite_error_code,Statement* statement,const char* sql_statement);// Like Execute(), but returns a SQLite result code.//// This method returns SqliteResultCode::kOk or a SQLite error code. In other// words, it never returns SqliteResultCode::{kDone, kRow}.//// This method is only exposed to the Database implementation. Code that uses// sql::Database should not be concerned with SQLite result codes.[[nodiscard]] SqliteResultCode ExecuteAndReturnResultCode(const char* sql);// Like |Execute()|, but retries if the database is locked.[[nodiscard]] bool ExecuteWithTimeout(const char* sql,base::TimeDelta ms_timeout);// Implementation helper for GetUniqueStatement() and GetCachedStatement().scoped_refptr<StatementRef> GetStatementImpl(const char* sql,bool is_readonly);// Release page-cache memory if memory-mapped I/O is enabled and the database// was changed. Passing true for |implicit_change_performed| allows// overriding the change detection for cases like DDL (CREATE, DROP, etc),// which do not participate in the total-rows-changed tracking.void ReleaseCacheMemoryIfNeeded(bool implicit_change_performed);// Returns the results of sqlite3_db_filename(), which should match the path// passed to Open().base::FilePath DbPath() const;// Helper to collect diagnostic info for a corrupt database.std::string CollectCorruptionInfo();// Helper to collect diagnostic info for errors. `diagnostics` is an optional// out parameter. If `diagnostics` is defined, this method populates SOME of// its fields. Some of the fields are left unmodified for the caller.std::string CollectErrorInfo(int sqlite_error_code,Statement* stmt,DatabaseDiagnostics* diagnostics) const;// The size of the memory mapping that SQLite should use for this database.//// The return value follows the semantics of "PRAGMA mmap_size". In// particular, zero (0) means memory-mapping should be disabled, and the value// is capped by SQLITE_MAX_MMAP_SIZE. More details at// https://www.sqlite.org/pragma.html#pragma_mmap_size//// "Memory-mapped access" is usually shortened to "mmap", which is the name of// the POSIX system call used to implement. The same principles apply on// Windows, but its more-descriptive API names don't make for good shorthands.//// When mmap is enabled, SQLite attempts to use the memory-mapped area (by// calling xFetch() in the VFS file API) instead of requesting a database page// buffer from the pager and reading (via xRead() in the VFS API) into it.// When this works out, the database page cache ends up only storing pages// whose contents has been modified. More details at// https://sqlite.org/mmap.html//// I/O errors on memory-mapped files result in crashes in Chrome. POSIX// systems signal SIGSEGV or SIGBUS on I/O errors in mmap-ed files. Windows// raises the EXECUTE_IN_PAGE_ERROR strucuted exception in this case. Chrome// does not catch signals or structured exceptions.//// In order to avoid crashes, this method attempts to read the file using// regular I/O, and returns 0 (no mmap) if it encounters any error.size_t ComputeMmapSizeForOpen();// Helpers for ComputeMmapSizeForOpen().bool GetMmapAltStatus(int64_t* status);bool SetMmapAltStatus(int64_t status);// sqlite3_prepare_v3() flags for this database.int SqlitePrepareFlags() const;// Returns a SQLite VFS interface pointer to the file storing database pages.//// Returns null if the database is not backed by a VFS file. This is always// the case for in-memory databases. Temporary databases (only used by sq// ::Recovery) start without a backing VFS file, and only get a file when they// outgrow their page cache.//// This method must only be called while the database is successfully opened.sqlite3_file* GetSqliteVfsFile();// Will eventually be checked on all methods. See https://crbug.com/1306694SEQUENCE_CHECKER(sequence_checker_);// The actual sqlite database. Will be null before Init has been called or if// Init resulted in an error.// This field is not a raw_ptr<> because it was filtered by the rewriter for:// #addr-ofRAW_PTR_EXCLUSION sqlite3* db_ = nullptr;// TODO(shuagga@microsoft.com): Make `options_` const after removing all// setters.DatabaseOptions options_;// Holds references to all cached statements so they remain active.//// flat_map is appropriate here because the codebase has ~400 cached// statements, and each statement is at most one insertion in the map// throughout a process' lifetime.base::flat_map<StatementID, scoped_refptr<StatementRef>> statement_cache_;// A list of all StatementRefs we've given out. Each ref must register with// us when it's created or destroyed. This allows us to potentially close// any open statements when we encounter an error.std::set<StatementRef*> open_statements_;// Number of currently-nested transactions.int transaction_nesting_ = 0;// True if any of the currently nested transactions have been rolled back.// When we get to the outermost transaction, this will determine if we do// a rollback instead of a commit.bool needs_rollback_ = false;// True if database is open with OpenInMemory(), False if database is open// with Open().bool in_memory_ = false;// |true| if the Database was closed using RazeAndPoison(). Used// to enable diagnostics to distinguish calls to never-opened// databases (incorrect use of the API) from calls to once-valid// databases.bool poisoned_ = false;// |true| if SQLite memory-mapped I/O is not desired for this database.bool mmap_disabled_;// |true| if SQLite memory-mapped I/O was enabled for this database.// Used by ReleaseCacheMemoryIfNeeded().bool mmap_enabled_ = false;// Used by ReleaseCacheMemoryIfNeeded() to track if new changes have happened// since memory was last released.int64_t total_changes_at_last_release_ = 0;// Called when a SQLite error occurs.//// This callback may be null, in which case errors are handled using a default// behavior.//// This callback must never be exposed outside this Database instance. This is// a straight-forward way to guarantee that this callback will not be called// after the Database instance goes out of scope. set_error_callback() makes// this guarantee.ErrorCallback error_callback_;// Developer-friendly database ID used in logging output and memory dumps.std::string histogram_tag_;// Stores the dump provider object when db is open.std::unique_ptr<DatabaseMemoryDumpProvider> memory_dump_provider_;
};} // namespace sql#endif // SQL_DATABASE_H_
二、sqlite数据库查询和事务处理类:
sql\statement.h
sql\statement.cc
// Copyright 2012 The Chromium Authors
// Use of this source code is governed by a BSD-style license that can be
// found in the LICENSE file.#ifndef SQL_STATEMENT_H_
#define SQL_STATEMENT_H_#include <stdint.h>#include <string>
#include <vector>#include "base/component_export.h"
#include "base/containers/span.h"
#include "base/dcheck_is_on.h"
#include "base/memory/ref_counted.h"
#include "base/sequence_checker.h"
#include "base/strings/string_piece.h"
#include "base/thread_annotations.h"
#include "base/time/time.h"
#include "sql/database.h"namespace sql {enum class SqliteResultCode : int;// Possible return values from ColumnType in a statement. These should match
// the values in sqlite3.h.
enum class ColumnType {kInteger = 1,kFloat = 2,kText = 3,kBlob = 4,kNull = 5,
};// Compiles and executes SQL statements.
//
// This class is not thread-safe. An instance must be accessed from a single
// sequence. This is enforced in DCHECK-enabled builds.
//
// Normal usage:
// sql::Statement s(connection_.GetUniqueStatement(...));
// s.BindInt(0, a);
// if (s.Step())
// return s.ColumnString(0);
//
// If there are errors getting the statement, the statement will be inert; no
// mutating or database-access methods will work. If you need to check for
// validity, use:
// if (!s.is_valid())
// return false;
//
// Step() and Run() just return true to signal success. If you want to handle
// specific errors such as database corruption, install an error handler in
// in the connection object using set_error_delegate().
class COMPONENT_EXPORT(SQL) Statement {public:// Creates an uninitialized statement. The statement will be invalid until// you initialize it via Assign.Statement();explicit Statement(scoped_refptr<Database::StatementRef> ref);Statement(const Statement&) = delete;Statement& operator=(const Statement&) = delete;Statement(Statement&&) = delete;Statement& operator=(Statement&&) = delete;~Statement();// Initializes this object with the given statement, which may or may not// be valid. Use is_valid() to check if it's OK.void Assign(scoped_refptr<Database::StatementRef> ref);// Resets the statement to an uninitialized state corresponding to// the default constructor, releasing the StatementRef.void Clear();// Returns true if the statement can be executed. All functions can still// be used if the statement is invalid, but they will return failure or some// default value. This is because the statement can become invalid in the// middle of executing a command if there is a serious error and the database// has to be reset.bool is_valid() const {DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);return ref_->is_valid();}// Running -------------------------------------------------------------------// Executes the statement, returning true on success. This is like Step but// for when there is no output, like an INSERT statement.bool Run();// Executes the statement, returning true if there is a row of data returned.// You can keep calling Step() until it returns false to iterate through all// the rows in your result set.//// When Step returns false, the result is either that there is no more data// or there is an error. This makes it most convenient for loop usage. If you// need to disambiguate these cases, use Succeeded().//// Typical example:// while (s.Step()) {// ...// }// return s.Succeeded();bool Step();// Resets the statement to its initial condition. This includes any current// result row, and also the bound variables if the |clear_bound_vars| is true.void Reset(bool clear_bound_vars);// Returns true if the last executed thing in this statement succeeded. If// there was no last executed thing or the statement is invalid, this will// return false.bool Succeeded() const;// Binding -------------------------------------------------------------------// These all take a 0-based parameter index and return true on success.// strings there may be out of memory.void BindNull(int param_index);void BindBool(int param_index, bool val);void BindInt(int param_index, int val);void BindInt(int param_index,int64_t val) = delete; // Call BindInt64() instead.void BindInt64(int param_index, int64_t val);void BindDouble(int param_index, double val);void BindCString(int param_index, const char* val);void BindString(int param_index, base::StringPiece val);// If you need to store (potentially invalid) UTF-16 strings losslessly,// store them as BLOBs instead. `BindBlob()` has an overload for this purpose.void BindString16(int param_index, base::StringPiece16 value);void BindBlob(int param_index, base::span<const uint8_t> value);// Overload that makes it easy to pass in std::string values.void BindBlob(int param_index, base::span<const char> value) {BindBlob(param_index, base::as_bytes(base::make_span(value)));}// Overload that makes it easy to pass in std::u16string values.void BindBlob(int param_index, base::span<const char16_t> value) {BindBlob(param_index, base::as_bytes(base::make_span(value)));}// Conforms with base::Time serialization recommendations.//// This is equivalent to the following snippets, which should be replaced.// * BindInt64(col, val.ToInternalValue())// * BindInt64(col, val.ToDeltaSinceWindowsEpoch().InMicroseconds())//// Features that serialize base::Time in other ways, such as ToTimeT() or// InMillisecondsSinceUnixEpoch(), will require a database migration to be// converted to this (recommended) serialization method.//// TODO(crbug.com/1195962): Migrate all time serialization to this method, and// then remove the migration details above.void BindTime(int param_index, base::Time time);// Conforms with base::TimeDelta serialization recommendations.//// This is equivalent to the following snippets, which should be replaced.// * BindInt64(col, delta.ToInternalValue())// * BindInt64(col, delta.InMicroseconds())//// TODO(crbug.com/1402777): Migrate all TimeDelta serialization to this method// and remove the migration details above.void BindTimeDelta(int param_index, base::TimeDelta delta);// Retrieving ----------------------------------------------------------------// Returns the number of output columns in the result.int ColumnCount() const;// Returns the type associated with the given column.//// Watch out: the type may be undefined if you've done something to cause a// "type conversion." This means requesting the value of a column of a type// where that type is not the native type. For safety, call ColumnType only// on a column before getting the value out in any way.ColumnType GetColumnType(int col);// These all take a 0-based argument index.bool ColumnBool(int column_index);int ColumnInt(int column_index);int64_t ColumnInt64(int column_index);double ColumnDouble(int column_index);std::string ColumnString(int column_index);// If you need to store and retrieve (potentially invalid) UTF-16 strings// losslessly, store them as BLOBs instead. They may be retrieved with// `ColumnBlobAsString16()`.std::u16string ColumnString16(int column_index);// Conforms with base::Time serialization recommendations.//// This is equivalent to the following snippets, which should be replaced.// * base::Time::FromInternalValue(ColumnInt64(col))// * base::Time::FromDeltaSinceWindowsEpoch(// base::Microseconds(ColumnInt64(col)))//// TODO(crbug.com/1195962): Migrate all time serialization to this method, and// then remove the migration details above.base::Time ColumnTime(int column_index);// Conforms with base::TimeDelta deserialization recommendations.//// This is equivalent to the following snippets, which should be replaced.// * base::TimeDelta::FromInternalValue(ColumnInt64(column_index))//// TODO(crbug.com/1402777): Migrate all TimeDelta serialization to this method// and remove the migration details above.base::TimeDelta ColumnTimeDelta(int column_index);// Returns a span pointing to a buffer containing the blob data.//// The span's contents should be copied to a caller-owned buffer immediately.// Any method call on the Statement may invalidate the span.//// The span will be empty (and may have a null data) if the underlying blob is// empty. Code that needs to distinguish between empty blobs and NULL should// call GetColumnType() before calling ColumnBlob().base::span<const uint8_t> ColumnBlob(int column_index);bool ColumnBlobAsString(int column_index, std::string* result);bool ColumnBlobAsString16(int column_index, std::u16string* result);bool ColumnBlobAsVector(int column_index, std::vector<char>* result);bool ColumnBlobAsVector(int column_index, std::vector<uint8_t>* result);// Diagnostics --------------------------------------------------------------// Returns the original text of a SQL statement WITHOUT any bound values.// Intended for logging in case of failures. Note that DOES NOT return any// bound values, because that would cause a privacy / PII issue for logging.std::string GetSQLStatement();private:friend class Database;// Checks SQLite result codes and handles any errors.//// Returns `sqlite_result_code`. This gives callers the convenience of writing// "return CheckSqliteResultCode(sqlite_result_code)" and gives the compiler// the opportunity of doing tail call optimization (TCO) on the code above.//// This method reports error codes to the associated Database, and updates// internal state to reflect whether the statement succeeded or not.SqliteResultCode CheckSqliteResultCode(SqliteResultCode sqlite_result_code);// Should be called by all mutating methods to check that the statement is// valid. Returns true if the statement is valid. DCHECKS and returns false// if it is not.// The reason for this is to handle two specific cases in which a Statement// may be invalid. The first case is that the programmer made an SQL error.// Those cases need to be DCHECKed so that we are guaranteed to find them// before release. The second case is that the computer has an error (probably// out of disk space) which is prohibiting the correct operation of the// database. Our testing apparatus should not exhibit this defect, but release// situations may. Therefore, the code is handling disjoint situations in// release and test. In test, we're ensuring correct SQL. In release, we're// ensuring that contracts are honored in error edge cases.bool CheckValid() const;// Helper for Run() and Step(), calls sqlite3_step() and returns the checked// value from it.SqliteResultCode StepInternal();// Retrieve and log the count of VM steps required to execute the query.void ReportQueryExecutionMetrics() const;// The actual sqlite statement. This may be unique to us, or it may be cached// by the Database, which is why it's ref-counted. This pointer is// guaranteed non-null.scoped_refptr<Database::StatementRef> ref_GUARDED_BY_CONTEXT(sequence_checker_);// See Succeeded() for what this holds.bool succeeded_ GUARDED_BY_CONTEXT(sequence_checker_) = false;#if DCHECK_IS_ON()// Used to DCHECK() that Bind*() is called before Step() or Run() are called.bool step_called_ GUARDED_BY_CONTEXT(sequence_checker_) = false;bool run_called_ GUARDED_BY_CONTEXT(sequence_checker_) = false;
#endif // DCHECK_IS_ON()SEQUENCE_CHECKER(sequence_checker_);
};} // namespace sql#endif // SQL_STATEMENT_H_
事务处理类
sql\transaction.h
sql\transaction.cc
三、数据库操作演示:
1、demo\BUILD.gn
添加sql依赖
executable("03-sqlclient") {sources = ["03-sqlclient/client.cc",]if (is_win) {ldflags = [ "/LARGEADDRESSAWARE" ]}deps = ["//base","//sql","//build/win:default_exe_manifest",]
}
2、demo\03-sqlclient\client.cc
#include <stdarg.h>
#include <stddef.h>
#include <stdint.h>
#include <windows.h>
#include <iostream>#include "base/command_line.h"
#include "base/files/file_path.h"
#include "base/files/file_util.h"
#include "base/logging.h"
#include "base/strings/stringprintf.h"
#include "build/build_config.h"
#include "sql/database.h"
#include "sql/statement.h"
#include "third_party/abseil-cpp/absl/types/optional.h"int main(int argc, const char* argv[]) {base::CommandLine::Init(argc, argv);base::FilePath db_path(FILE_PATH_LITERAL("d:/jdtest.db"));sql::Database db;// 1、创建数据库LOG(ERROR)<<"open/create d:/jdtest.db";if (db.Open(db_path)) {LOG(INFO) << "open db ok : " << db_path;if (!db.DoesTableExist("test_tb")) {// 2、创建表LOG(ERROR)<<"CRATE TABLE test_tb";bool is_ok = db.Execute("CREATE TABLE test_tb(id INTEGER PRIMARY KEY NOT NULL, name ""varchar(256), ege int)");if (!is_ok) {LOG(ERROR) << "CREATE TABLE test_tb erro";}}// 3、插入数据LOG(ERROR)<<"INSERT ";for (int i = 0; i < 10; ++i) {std::string sql = base::StringPrintf("INSERT INTO test_tb(name,ege) VALUES('%s',%d)", "abc", 20 + i);bool is_ok = db.Execute(sql.c_str());if (!is_ok) {LOG(ERROR) << "INSERT INTO test_tb erro";}}// 4、查询数据LOG(ERROR)<<"SELECT ";sql::Statement s(db.GetUniqueStatement("SELECT *from test_tb"));if (s.is_valid()) {while (s.Step()) {int id = s.ColumnInt(0);std::string name = s.ColumnString(1);int age = s.ColumnInt(2);LOG(ERROR) << "id :" << id << " name :" << name << " age :" << age;}}// 5、删除数据LOG(ERROR)<<"DELETE ";if (!db.Execute("DELETE FROM test_tb WHERE ege >22")) {LOG(ERROR) << "DELETE FROM test_tberro";}} else {LOG(ERROR) << "open db erro : " << db_path;}return 0;
}