Transcription
Oracle Berkeley DBGetting Started withtheSQL APIs12c Release 1Library Version 12.1.6.2
Legal NoticeThis documentation is distributed under an open source license. You may review the terms of this license at: ydb/downloads/oslicense-093458.htmlOracle, Berkeley DB, and Sleepycat are trademarks or registered trademarks of Oracle. All rights to these marks are reserved. Nothird-party use is permitted without the express prior written consent of Oracle.Other names may be trademarks of their respective owners.To obtain a copy of this document's original source code, please submit a request to the Oracle Technology Network forum umID 271Published 4/13/2017
Table of ContentsPreface . viConventions Used in this Book . viFor More Information . viContact Us . vii1. Berkeley DB SQL: The Absolute Basics . 1BDB SQL Is Nearly Identical to SQLite . 1Getting and Installing BDB SQL . 1On Windows Systems . 1On Unix . 2The BDB SQL ADO.NET Interface . 2Prerequisites For Building The ADO.NET Package . 2Building BDB SQL ADO.NET Interface For Windows . 2Building BDB SQL ADO.NET Interface For Windows Mobile . 3Accessing BDB SQL Databases . 3The Journal Directory . 4User Authentication . 4BDB User Authentication . 4The Interface . 5Bootstrap . 6Transaction . 6Security Considerations . 6BDB SQL Key-store Based User Authentication . 7Interface . 7Bootstrap . 8User Log In . 8Transaction . 9The Lock File . 9Unsupported PRAGMAs . 9Changed PRAGMAs . 9PRAGMA auto vacuum . 9PRAGMA cache size . 10PRAGMA incremental vacuum . 10PRAGMA journal size limit . 10Added PRAGMAs . 10PRAGMA bdbsql error file . 11PRAGMA bdbsql lock tablesize . 11PRAGMA bdbsql shared resources . 11PRAGMA bdbsql single process . 11PRAGMA bdbsql system memory . 12PRAGMA bdbsql vacuum fillpercent . 12PRAGMA bdbsql vacuum pages . 12PRAGMA large record opt . 12PRAGMA multiversion . 13PRAGMA snapshot isolation . 13PRAGMA statistics . 13PRAGMA statistics file . 134/13/2017Getting Started with the BDB SQL APIsPage iii
2.3.4.5.4/13/2017PRAGMA trickle .PRAGMA txn bulk .Replication PRAGMAs .PRAGMA bdbsql userauth add .PRAGMA bdbsql user login .PRAGMA bdbsql user edit .PRAGMA bdbsql user delete .Miscellaneous Differences .Berkeley DB Concepts .Encryption .Berkeley DB encryption .SQLite Encryption Extension .Using Sequences .create sequence .nextval .currval .drop sequence .Differences for Users of other SQL Engines .Locking Notes .Internal Database Usage .Lock Handling .SQLite Lock Usage .Lock Usage with the BDB SQL Interface .Berkeley DB Features .Using Bulk Loading .Using Multiversion Concurrency Control .Selecting the Page Size .Controlling the Number of Accumulated Log Files .Using DB CONFIG to configure the Berkeley DB SQL interface .Introduction to Environments .The DB CONFIG File .Creating the DB CONFIG File Before Creating the Database .Re-creating the Environment .Configuring the In-Memory Cache .Using Replication with the SQL API .Replication Overview .Replication Masters .Elections .Durability Guarantees .Permanent Message Handling .Two-Site Replication Groups .Replication PRAGMAs .PRAGMA replication .PRAGMA replication ack policy .PRAGMA replication ack timeout .PRAGMA replication get master .PRAGMA replication initial master .PRAGMA replication local site .PRAGMA replication num sites .Getting Started with the BDB SQL 36Page iv
PRAGMA replication perm failed .PRAGMA replication priority .PRAGMA replication remote site .PRAGMA replication remove site .PRAGMA replication site status .PRAGMA replication verbose output .PRAGMA replication verbose file .Displaying Replication Statistics .Replication Usage Examples .Example 1: Distributed Read at 3 Sites .Example 2: 2-Site Failover .6. Administrating Berkeley DB SQL Databases .Backing Up Berkeley DB SQL Databases .Backing Up Replicated Berkeley DB SQL Databases .Syncing with Oracle Databases .Syncing on Unix Platforms .Syncing on Windows Platforms .Syncing on Windows Mobile Platforms .Data Migration .Migration Using the Shells .Catastrophic Recovery .Database Statistics .Verify Database Structure .A. Using the BFILE Extension .Supported Platforms and Languages .BFILE SQL Objects and Functions .BFILE CREATE DIRECTORY .BFILE REPLACE DIRECTORY .BFILE DROP DIRECTORY .BFILE NAME .BFILE FULLPATH .BFILE OPEN .BFILE READ .BFILE CLOSE .BFILE SIZE .BFILE C/C Objects and Functions .sqlite3 column bfile .sqlite3 bfile open .sqlite3 bfile close .sqlite3 bfile is open .sqlite3 bfile read .sqlite3 bfile file exists .sqlite3 bfile size .sqlite3 bfile final .4/13/2017Getting Started with the BDB SQL 464646474747474747474748484849494950505151Page v
PrefaceWelcome to the Berkeley DB SQL interface. This manual describes how to configure anduse the SQL interface to Berkeley DB 12c Release 1. This manual also describes commonadministrative tasks, such as backup and restore, database dump and load, and data migrationwhen using the BDB SQL interface.This manual is intended for anyone who wants to use the BDB SQL interface. Because usage ofthe BDB SQL interface is very nearly identical to SQLite, prior knowledge of SQLite is assumedby this manual. No prior knowledge of Berkeley DB is necessary, but it is helpful.To learn about SQLite, see the official SQLite website at: http://www.sqlite.orgConventions Used in this BookThe following typographical conventions are used within in this manual:Keywords or literal text that you are expected to type is presented in a monospaced font.For example: "Use the DB HOME environment variable to identify the location of yourenvironment directory."Variable or non-literal text is presented in italics. For example: "Go to your DB INSTALLdirectory."Program examples and literal text that you might type are displayed in a monospaced font ona shaded background. For example:/* File: gettingstarted common.h */typedef struct stock dbs {DB *inventory dbp; /* Database containing inventory information */DB *vendor dbp;/* Database containing vendor information */char *db home dir;/* Directory containing the database files */char *inventory db name; /* Name of the inventory database */char *vendor db name;/* Name of the vendor database */} STOCK DBS;NoteFinally, notes of interest are represented using a note block such as this.For More InformationBeyond this manual, you may also find the following sources of information useful when usingthe Berkeley DB SQL interface: Berkeley DB Installation and Build Guide Berkeley DB Programmer's Reference Guide4/13/2017Getting Started with the BDB SQL APIsPage vi
Berkeley DB Getting Started with Replicated ApplicationsTo download the latest documentation along with white papers and other collateral, umentation/index.html.For the latest version of the Oracle downloads, visit tact UsYou can post your comments and questions at the Oracle Technology (OTN) forum for OracleBerkeley DB at: D 271, or for OracleBerkeley DB High Availability at: D 272.For sales or support information, email to: berkeleydb-info us@oracle.com You can subscribeto a low-volume email announcement list for the Berkeley DB product family by sending emailto: bdb-join@oss.oracle.com4/13/2017Getting Started with the BDB SQL APIsPage vii
Chapter 1. Berkeley DB SQL: The Absolute BasicsWelcome to the Berkeley DB SQL interface. If you are a SQLite user who is using the BDBSQL interface for reasons other than performance enhancements, this chapter tells you theminimum things you need to know about the interface. You should simply read this chapterand then skip the rest of this book.If, however, you are using the BDB SQL interface for performance reasons, then you needto read this chapter, plus most of the rest of the chapters in this book (although you canprobably skip most of Administrating Berkeley DB SQL Databases (page 42), unless you wantto administer your database "the Berkeley DB way").Also, if you are an existing Berkeley DB user who is interested in the BDB SQL interface, readthis chapter plus the rest of this book.BDB SQL Is Nearly Identical to SQLiteYour interaction with the BDB SQL interface is almost identical to SQLite. You use the sameAPIs, the same command shell environment, the same SQL statements, and the same PRAGMAsto work with the database created by the BDB SQL interface as you would if you were usingSQLite.To learn how to use SQLite, see the official SQLite Documentation Page.That said, there are a few small differences between the two interfaces. These are describedin the remainder of this chapter.Getting and Installing BDB SQLThe BDB SQL interface comes as a part of the Oracle Berkeley DB download. This can bedownloaded from the Oracle Berkeley DB download page.On Windows SystemsThe BDB SQL interface is automatically built and installed whenever you build or installBerkeley DB for a Windows system. The BDB SQL interface dlls and the command lineinterpreter have names that differ from a standard SQLite distribution as follows: dbsql.exeThis is the command line shell. It operates identically to the SQLite sqlite3.exe shell. libdb sql60.dllThis is the library that provides the BDB SQL interface. It is the equivalent of the SQLitesqlite3.dll library.NoteIf you are upgrading an existing BDB SQL installation, and you are upgrading fromrelease 6.1.19 or lower, then see the SQL database upgrade instructions at UpdatedSQLite Version in the Berkeley DB Installation and Build Guide.4/13/2017Getting Started with the BDB SQL APIsPage 1
Berkeley DB SQL: The Absolute BasicsLibrary Version 12.1.6.2On UnixIn order to build the BDB SQL interface, you download and build Berkeley DB, configuring itso that the BDB SQL interface is also built. Be aware that it is not built by default. Instead,you need to tell the Berkeley DB configure script to also build the BDB SQL interface. Forinstructions on building the BDB SQL interface, see Building the DB SQL Interface in theBerkeley DB Installation and Build Guide.The library and application names used when building the BDB SQL interface are differentthan those used by SQLite. If you want library and command shell names that are consistentwith the names used by SQLite, configure the BDB SQL interface build using the compatibility(--enable-sql compat) option.WarningThe compatibility option can break other applications on your platform that rely onstandard SQLite. This is especially true of Mac OS X, which uses standard SQLite for anumber of default applications.Use the compatibility option only if you know exactly what you are doing.Unless you built the BDB SQL interface with the compatibility option, libraries and a commandline shell are built with the following names: dbsqlThis is the command line shell. It operates identically to the SQLite sqlite3 shell. libdb sqlThis is the library that provides the BDB SQL interface. It is the equivalent of the SQLitelibsqlite3 library.The BDB SQL ADO.NET InterfaceDownload the ADO.NET package from the Oracle Berkeley DB download page.Prerequisites For Building The ADO.NET Package To build the Linq package, you will need to install Microsoft .NET Framework 3.5 SP1. To build SQLite.Designer, you will need to install the Microsoft Visual Studio SDK. To build on Windows Mobile you will need to install the Microsoft Windows Mobile 6.5.3Developer Tool Kit (DTK). To build on Windows Mobile you will need to use Visual Studio 2008.Building BDB SQL ADO.NET Interface For Windows The package contains Visual Studio solution files:4/13/2017Getting Started with the BDB SQL APIsPage 2
Berkeley DB SQL: The Absolute BasicsLibrary Version 12.1.6.2 SQLite.NET.2008.sln and SQLite.NET.2010.slnFor use by with Visual Studio 2008 or 2010. Note that these solution files do not buildsupport for Linq or SQLite Designer. SQLite.NET.2008.MSBuild.sln and SQLite.NET.2010.MSBuild.slnFor use with MSBuild (Microsoft Build Engine). These can also be used with Visual Studio.These solutions exclude SQLite Designer and CompactFramework. By default, these do notbuild support for Linq. Change the current platform target to ReleaseNativeOnly choose either Win32 or x64depending on your target platform. Build the solution.Building BDB SQL ADO.NET Interface For Windows MobileBuilding BDB SQL ADO.NET for Windows Mobile requires Windows Mobile 6.5.3 ProfessionalDTK. Typical requirements for installing this toolkit are: Visual Studio 2005 SP1 or Later ActiveSync 4.5 .NET CompactFramework 2.0 SP1 Windows Mobile 6 SDKTo build BDB SQL ADO.NET for Windows Mobile, do the following: Open the SQLite.NET.2008.WinCE.sln solution file in Visual Studio 2008. Select Load Project Normally Change the current platform to ReleaseNativeOnly. Select Configuration Manager- new, then type or select the platform Windows Mobile6.5.3 Professional DTK (ARMV4I). Choose to copy settings from Pocket PC 2003(ARMV4I) Build the solution.Accessing BDB SQL DatabasesBDB SQL databases can be accessed using a number of different drivers, applications and APIs.Only some of these are supported by all major platforms, as identified in the following table.DBSQL ndroidiOSxxxxxGetting Started with the BDB SQL APIsPage 3
Berkeley DB SQL: The Absolute BasicsLibrary Version SDBSQL ShellxxxxxODBCxxJDBCxxADO.NETxxThe Journal DirectoryWhen you create a database using the BDB SQL interface, a directory is created alongside ofit. This directory has the same name as your database file, but with a -journal suffix.That is, if you create a database called "mydb" then the BDB SQL interface also creates adirectory alongside of the "mydb" file called "mydb-journal".This directory contains files that are very important for the proper functioning of the BDB SQLinterface. Do not delete this directory or any of its files unless you know what you are doing.In Berkeley DB terms, the journal directory contains the environment files that are required toprovide access to databases across multiple processes.User AuthenticationSee the following section for more information: BDB User Authentication (page 4) BDB SQL Key-store Based User Authentication (page 7)BDB User AuthenticationWith the BDB user authentication extension, a database can be marked as requiringauthentication. To visit an authentication-required BDB database, an authenticated usermust be logged into the database connection first. Once a BDB database is marked asauthentication-required, it cannot be converted back into a no-authentication-requireddatabase. Encryption is mandatory if user authentication is activated.By default a database does not require authentication. The BDB user authentication modulewill be activated by adding the -DBDBSQL USER AUTHENTICATION compile-time option.The client application must add lang/sql/generated/sqlite3.h to work with BDB userauthentication. BDB user authentication is based on SQLite User Authentication.See the following sections for more information: The Interface (page 5) Bootstrap (page 6) Transaction (page 6)4/13/2017Getting Started with the BDB SQL APIsPage 4
Berkeley DB SQL: The Absolute BasicsLibrary Version 12.1.6.2 Security Considerations (page 6)The InterfaceThe users can use the following 3 ways to work with BDB user authentication: via C APIsint sqlite3 user authenticate(sqlite3 *db,/* The database connection */const char *zUsername, /* Username */const char *aPW,/* Password or credentials */int nPW/* Number of bytes in aPW[] */);int sqlite3 user add(sqlite3 *db,const char *zUsername,const char *aPW,int nPW,int isAdmin);/*/*/*/*/*Database connection */Username to be added */Password or credentials */Number of bytes in aPW[] */True to give new user admin privilege */int sqlite3 user change(sqlite3 *db,const char *zUsername,const void *aPW,int nPW,int isAdmin);/*/*/*/*/*Database connection */Username to change */Modified password or credentials */Number of bytes in aPW[] */Modified admin privilege for the user */int sqlite3 user delete(sqlite3 *db,/* Database connection */const char *zUsername /* Username to remove */); Via the BDB SQL user authentication PRAGMAs below: PRAGMA bdbsql user login "{USER NAME}:{USER PWD}"; PRAGMA bdbsql user add "{USER NAME}:{USER PWD}:{IS ADMIN}"; PRAGMA bdbsql user edit "{USER NAME}:{USER PWD}:{IS ADMIN}"; PRAGMA bdbsql user delete "{USER NAME}"; Via the BDB SQL shell commands as below: .user login {USER NAME} {USER PWD} .user add {USER NAME} {USER PWD} {IS ADMIN} .user edit {USER NAME} {USER PWD} {IS ADMIN}4/13/2017Getting Started with the BDB SQL APIsPage 5
Berkeley DB SQL: The Absolute BasicsLibrary Version 12.1.6.2 .user delete {USER NAME}You can use the sqlite3 user authenticate() interface to log in a user into the databaseconnection. Calling sqlite3 user authenticate() on a no-authentication-required databaseconnection will return an error. This is different from the original SQLite behavior.You can use the sqlite3 user add()/sqlite3 user delete() interfaces to add/delete a user.It resuts in an error to call sqlite3 user add()/sqlite3 user delete() on an authenticationrequired database connection without an administrative user loged in. The currently logged-inuser cannot be deleted.You can use the sqlite3 user change() interface to change a users login credentials oradmin privilege. Any user can change their own password, but no user can change their ownadministrative privilege setting. Only an administrative user can change another users logincredentials or administrative privilege setting.The sqlite3 set authorizer() callback is modified to take a 7th parameter which is theusername of the currently logged in user, or NULL for a no-authentication-required database.When ATTACH-ing new database files to a connection, each newly attached database that isan authentication-required database is checked using the same username and password asprovided to the main database. If that check fails, then the ATTACH command fails with anSQLITE AUTH error.BootstrapNo-authentication-required database becomes an authentication-required database whenthe first user was added into the BDB database. This is called user authentication bootstrap.In bootstrap, the isAdmin parameter of the sqlite3 user add() call must be true. Afterbootstrap, the first added user is logged into the database connection.TransactionBDB user authentication APIs sqlite3 user add()/sqlite3 user change()/sqlite3 user delete()work in their own transaction. It results in an error to call these APIs inside a transaction.Security ConsiderationsA BDB database is not considered as secure if it has only BDB user authentication appliedstatus. The security issues are as follows: Anyone with access to the device can just open the database file in binary editor to see andmodify the data. An authentication-required BDB database requires no authentication if opened by a versionof BDB that omits the user authentication compile-time optionDue to the above issues BDB encryption has to be turned on when BDB user authenticationis used. This requires the user to provide an encryption key before calling any of theauthentication functions. If the database is encrypted, sqlite3 key v2() must be called4/13/2017Getting Started with the BDB SQL APIsPage 6
Berkeley DB SQL: The Absolute BasicsLibrary Version 12.1.6.2first, with the correct decryption key, prior to invoking sqlite3 user authenticate()/sqlite3 user add().To open an existing, encrypted, authentication-required database, the call sequence is:sqlite3 open v2();sqlite3 key v2();sqlite3 user authenticate();/* Database is now usable */To create a new, encrypted, authentication-required database, the call sequence is:sqlite3 open v2();sqlite3 key v2();sqlite3 user add();BDB SQL Key-store Based User AuthenticationBDB SQL provides the key-store based user authentication to allow the user to work easilywith encryption and user authentication together. In the key-store based user authentication,encryption becomes mandatory if user authentication is enabled, and user could just workwith user authentication API only and without the knowledge of the encryption key.You do this by storing the encryption
use the SQL interface to Berkeley DB 12c Release 1. This manual also describes common administrative tasks, such as backup and restore, database dump and load, and data migration . The BDB SQL interface comes as a part of the Oracle Berkeley DB download. This can be downloaded from the Oracle Berkeley DB download page.
