oracle berkeley db - data storage (ds) tutorial
DESCRIPTION
A deep dive presentation into the Oracle Berkeley DB Data Storage APIs.TRANSCRIPT
![Page 1: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/1.jpg)
1
A Use-Case Based Tutorial
Oracle Berkeley DBData Store
![Page 2: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/2.jpg)
2
Part I: Data Store (DS)
• Overview• Creating and removing databases• Getting and putting data
• Case Studies• Telecom use case• Customer account management
![Page 3: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/3.jpg)
3
Berkeley DB Data Store
• Simple indexed data storage• Attributes• Blindingly fast• Easy to use• APIs in the languages of your choice
• Limitations• No concurrency• No recovery
![Page 4: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/4.jpg)
4
Blindingly Fast
• Platform Description• 2.33 GHz Intel Core 2 Duo• 2 GB 667 MHz DDR2 SDRAM• Mac OSX 10.4.10• Window manager and apps all up and running
• Insert 1,000,000 8-byte keys with 25-byte data• 180,000 inserts/second
• Retrieve same keys/data• 750,000 gets/second
• Bulk get of same key/data pairs• 2,500,000 pairs/second
![Page 5: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/5.jpg)
5
Easy to Use
• Code for benchmark program• Main loop• Create/open database• Put keys• Get keys• Close/remove database• (Omitted) Command line processing, key
generation
![Page 6: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/6.jpg)
6
Main Program1. main(argc, argv)2. int argc;3. char *argv[];4. {5. DB *dbp;6. int do_n, rm_db, skip_put;
7. do_n = DEFAULT_N;8. rm_db = 0;9. skip_put = 0;
10. do_cmdline(argc, argv, &do_n, &rm_db, &skip_put);11. create_database(&dbp);12. if (!skip_put)13. do_put(dbp, do_n);14. do_bulk_get(dbp, do_n);15. do_get(dbp);16. close_database(dbp, rm_db);17. }
![Page 7: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/7.jpg)
7
Create/Open Database
1. static void 2. create_database(dbpp)3. DB **dbpp; 4. { 5. DB *dbp;6. 7. /* Create database handle. */8. assert(db_create(&dbp, NULL, 0) == 0);9. 10. /* Open/Create btree database. */11. assert(dbp->open(dbp,12. NULL, DBNAME, NULL, DB_BTREE, DB_CREATE, 0644) == 0);13. 14. *dbpp = dbp;
15. }
![Page 8: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/8.jpg)
8
Put Data1. static void2. do_put(dbp, n)3. DB *dbp;4. int n;5. {6. char key[KEYLEN];7. DBT datadbt, keydbt;8. int i;
9. /* Initialize key DBT (repeat with Data, omitted) */10. strncpy(key, FIRST_KEY, KEYLEN);11. memset(&keydbt, 0, sizeof(keydbt));12. keydbt.data = key;13. keydbt.size = KEYLEN;14. for (i = 0; i < n; i++) {15. assert(dbp->put(dbp,16. NULL, &keydbt, &datadbt, 0) == 0);17. next_key(key);18. }19. }
![Page 9: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/9.jpg)
9
Get Data1. static void2. do_get(dbp)3. DB *dbp;4. {5. DBC *dbc;6. DBT keydbt, datadbt;7. int n, ret;
8. assert(dbp->cursor(dbp, NULL, &dbc, 0) == 0);9. memset(&keydbt, 0, sizeof(keydbt));10. memset(&datadbt, 0, sizeof(datadbt));11. n = 0;12. TIMER_START;13. while ((ret = dbc->get(dbc,14. &keydbt, &datadbt, DB_NEXT)) == 0) {15. n++;16. }17. TIMER_STOP;18. TIMER_DISPLAY(n);19. }
![Page 10: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/10.jpg)
10
Close and Remove Database
1. static void
2. close_database(dbp, remove)
3. DB *dbp;
4. int remove;
5. {
6. assert(dbp->close(dbp, 0) == 0);
7. if (remove) {
8. assert(db_create(&dbp, NULL, 0) == 0);
9. assert(dbp->remove(dbp,
10. DBNAME, NULL, 0) == 0);
11. }
12. }
![Page 11: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/11.jpg)
11
APIs in many Languages
• C/C++• Java• Python• Perl• Tcl• Ruby• Etc.
![Page 12: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/12.jpg)
12
Berkeley DB Product Family
DS CDS TDS HA
Berkeley DB
C API
C++ API Java API
Berkeley DB XML
C++ API Java API
Java Collections API
Runs on UNIX, Linux, MacOS X, Windows, VxWorks, QNX, etc.
APIs for C, C++, Java, Perl, Python, PHP, Ruby, Tcl, Eiffel, etc.
Java API
Berkeley DBJava Edition
CDS TDS
![Page 13: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/13.jpg)
13
Part I: Data Store
• Overview• Creating and removing databases• Getting and putting data
• Case Studies• Telecom Use Case
• Creating/using an environment• Filling/draining a queue• Btrees: storing different record types in a single database• Cursors
• Customer account management
![Page 14: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/14.jpg)
14
Telecommunications Use Case
• Radio access network optimization system• Vendor-independent multi-service multi-
protocol RAN aggregation plus optimization• Interoperable with all major base stations• Proven, world-wide deployment• Functional over range of operating conditions• Low TCO
![Page 15: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/15.jpg)
15
Berkeley DB in Telecom
BDB QueueBDB Btrees: One per device
Unsolicited events
Drain queueCSV export
Archive, Inventory, Event and Performance Records
![Page 16: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/16.jpg)
16
BDB Access Methods
• DB_QUEUE/DB_BTREE:• Berkeley DB supports a variety of different indexing
mechanisms.• Applications select the appropriate mechanism for the data
management task at hand.• For example:
• DB_QUEUE: FIFO order, fixed size data, numerical keys• DB_BTREE: Clustered lookup, variable length keys/data
• Other index types (access methods)• Hash: truly large unordered data• Recno: data with no natural key; numerical keys assigned
![Page 17: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/17.jpg)
17
Databases & Environments
• Each different data collection is a database.• Databases may be gathered together into environments.
• An environment is a logically related collection of databases.
• Example:• The NMS queue and per-device btrees all reside in
a single environment for a single instance of AccessGate.
![Page 18: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/18.jpg)
18
Creating Environments
• Create the handle using db_env_create()• Open using DB_ENV->open()
• Use DB_CREATE flag to create a new environment.• Use DB_INIT_MPOOL for a shared memory buffer pool• Use DB_PRIVATE to keep environment in main memory.• Use DB_THREAD to allow threaded access to the DB_ENV
handle.
![Page 19: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/19.jpg)
19
Environment Example/* Create handle. */ret = db_env_create(&dbenv, 0); … error_handling …
ret = dbenv->set_cachesize(dbenv, 1, 0, 1); … error_handling …
/* Other configuration here. */
/* Create and open the environment. */flags = DB_CREATE |DB_INIT_MPOOL;
ret = dbenv->open(dbenv, HOME, flags, 0); … error_handling …
![Page 20: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/20.jpg)
20
Filling and Draining a Queue
• Creating a queue database.• Appending into a queue.• Draining a queue• Important attributes:• Queue supports only fixed-length records• Queue “keys” are integer record numbers• Queues do support random access
![Page 21: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/21.jpg)
21
Creating a Queue Database
• Create the handle using db_create()• Open using DB->open()
• Use DB_CREATE flag to create a new database
• Close using DB->close()• Never close a database with open cursors or transactions.
![Page 22: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/22.jpg)
22
Creating a Queue
/* Create a database handle. */ret = db_create(&dbp, dbenv, 0); … error_handling …
/* Set the record length to 256 bytes. */ret = dbp->set_relen(dbp, 256); … error_handling …ret = dbp->set_repad(dbp, (int)’ ‘); … error_handling …
/* Create and open the database. */ret = dbp->open(dbp, NULL, file, database, DB_QUEUE,DB_CREATE, 0666); … error_handling …
![Page 23: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/23.jpg)
23
Enqueuing Data
• DB->put() inserts data into a database.• Appending onto the end of a queue:
DBT key, data;memset(&key, 0, sizeof(key));memset(&data, 0, sizeof(data));data->data = “fill in the record here”;data->size = DATASIZE;
ret = dbp->put(dbp, NULL, &key, &data, DB_APPEND);if (ret != 0) … error_handling …
/* Key->data contains the new record number. */
![Page 24: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/24.jpg)
24
Draining the Queue
• DB->get() retrieves records
memset(&key, 0, sizeof(key));memset(&data, 0, sizeof(data));
While ((ret = dbp->get(dbp,
NULL, &key, &data, DB_CONSUME) == 0) {
/* Do something with record. */
}
if (ret != 0) … error_handling …
![Page 25: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/25.jpg)
25
Btrees: Different record types in a single database
• One btree per device• Each btree contains four record types• Archive• Inventory• Event• Performance
• Not necessary to have N databases for N record types.
![Page 26: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/26.jpg)
26
Record Types
• Different records (with different fields/sizes):
struct archive { struct event {int32_t field1; int64_t field1;… other fields here … other fields here
}; };
struct inventory { struct perf {double field1; char field1[16];… other fields here … other fields here
};};
• Assumptions:• Every record has a timestamp• May need to encode types of different records
![Page 27: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/27.jpg)
27
Data Design 1
• Index database by timestamp• Add “type” field to every record.• Allow duplicate data records for any timestamp
• Advantages:• Groups data temporally
• Disadvantages:• Difficult to get records of a particular type
![Page 28: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/28.jpg)
28
Allowing Duplicatesint create_database (DB_ENV *dbenv, DB *dbpp, char *name){
DB *dbp;int ret;
if ((ret = db_create(&dbp, dbenv, 0)) != 0)return (ret);
if ((ret = dbp->set_flags(dbp, DB_DUP)) != 0 || ((ret = dbp->open(dbp, NULL, name, NULL, DB_BTREE, 0, 0644)) != 0)
(void)dbp->close(dbp, 0);return (ret);
*dbpp = dbp;return (ret);
}
![Page 29: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/29.jpg)
29
Inserting a Record (design 1)int insert_archive_record(DB *dbp, timestamp_t ts, char *buf){
DBT key, data;struct archive a;
memset(&key, 0, sizeof(key));memset(&data, 0, sizeof(data));
key->data = &ts;key->size = sizeof(ts);
a->type = ARCHIVE_TYPE;BUFFER_TO_ARCHIVE(buf, a);/data->data = &a;data->size = sizeof(a);
return (dbp->put(dbp, &key, &data, 0));}
![Page 30: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/30.jpg)
30
Data Design 2
• Index database by type• Add “timestamp” field to every record.• Allow duplicate data records for any type.
• Advantages:• Groups data by type
• Disadvantages:• Difficult to get records for a given time• Many records will have the same type
![Page 31: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/31.jpg)
31
Inserting a Record (design 2)int insert_archive_record(DB *dbp, timestamp_t ts, char *buf){
DBT key, data;RECTYPE type;struct archive a;
memset(&key, 0, sizeof(key));memset(&data, 0, sizeof(data));
type = ARCHIVE_TYPE;key->data = &type;key->size = sizeof(type);
a->timestamp = ts;BUFFER_TO_ARCHIVE(buf, a);data->data = &a;data->size = sizeof(a);
return (dbp->put(dbp, &key, &data, 0));}
![Page 32: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/32.jpg)
32
Data Design 3
• Index database by type and timestamp• May not need to allow duplicates
• Advantages:• Groups data by type and by timestamp within type• Fast retrieval by type• Reasonable retrieval by timestamp
• Disadvantages:• Nothing obvious
![Page 33: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/33.jpg)
33
Inserting a Record (design 3)int insert_archive_record(DB *dbp, timestamp_t ts, char *buf){
DBT key, data;struct archive a;struct akey {
timestamp_t ts;RECTYE type;
} archive_key;
memset(&key, 0, sizeof(key));memset(&data, 0, sizeof(data));
archive_key.ts = ts;archive_key.type = ARCHIVE_TYPE;key->data = &archive_key;key->size = sizeof(archive_key);BUFFER_TO_ARCHIVE(buf, a);data->data = &a;data->size = sizeof(a);return (dbp->put(dbp, &key, &data, 0));
}
![Page 34: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/34.jpg)
34
Data Design 4
• Create four databases in a single file• Key each database by timestamp
• Advantages:• Groups data by type• Fast retrieval by type• Reasonable retrieval by timestamp
• Disadvantages:• Nothing obvious
![Page 35: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/35.jpg)
35
Multiple Database in a File
int create_database (DB_ENV *dbenv, DB *db_array, char **names){
DB *dbp;int i, ret;
for (i = 0; i < 4; i++) {db_array[i] = dbp = NULL;if ((ret = db_create(&dbp, dbenv, 0)) != 0 || ((ret = dbp->open(dbp, NULL, “DBFILE”, names[i], DB_BTREE, 0, 0644)) != 0)
if (dbp != NULL)(void)dbp->close(dbp, 0);
break;
db_array[i] = dbp;}if (ret != 0)
/* Close all non-Null entries in db_array. */return (ret);
![Page 36: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/36.jpg)
36
Inserting a Record (design 4)int insert_record(DB *db_array, timestamp_t ts, RECTYPE type, char *buf, size_t buflen){
DBT key, data;memset(&key, 0, sizeof(key));memset(&data, 0, sizeof(data));key->data = &ts;key->size = sizeof(ts);data->data = buf;data->size = buflen;
if (type == ARCHIVE_TYPE)ret = put_rec(db_array[ARCHIVE_NDX], &key, &data);
else if (type == EVENT_TYPE)ret = put_rec(db_array[EVENT_NDX], &key, &data);
else if (type == INVENTORY_TYPE)ret = put_rec(db_array[INVENTORY_NDX], &key, &data);
else if (type == PERF_TYPE)ret = put_rec(db_array[PERF_NDX], &key, &data);
else /* Signal invalid record type */
return (ret);}
![Page 37: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/37.jpg)
37
Inserting a Record (cont)
int put_rec(DB *dbp, DBT *key, DBT *data)
{
return (dbp->put(dbp, NULL, &key, &data, 0));
}
![Page 38: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/38.jpg)
38
Data Design:Summary
• Berkeley DB’s schemaless design provides applications flexibility.
• Tune data organization for performance, ease of programming, or other criteria.
• Create indexes based upon query needs.
![Page 39: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/39.jpg)
39
Exporting to CSV
• Assume that we used design 4• Four databases in a single file• One database per record type
• Let’s say that we want one CSV file per database.
• Each CSV export will look very similar:• Use a cursor to iterate over the database.• For each record, output the CSV entry.
![Page 40: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/40.jpg)
40
Iteration with cursors
• A cursor represents a position in the database.
• The DB->cursor method creates a cursor.• Cursors have methods to get/put data.• DBC->del• DBC->get• DBC->put
• Close cursors when done.
![Page 41: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/41.jpg)
41
Iteration (code)
int archive_to_csv(DB *dbp){
DBC dbc;DBT key, data;struct archive *a;int ret;
if ((ret = dbp->cursor(dbp, NULL, &dbc, 0)) != 0)return (ret);
memset(&key, 0, sizeof(key));memset(&data, 0, sizeof(data));while ((ret = dbc->get(dbc, &key, &data, DB_NEXT)) == 0) {
a = (struct archive *)data->data;/* Output archive structure into CSV */
}if (ret == DB_NOTFOUND)
ret = 0;return (ret);
}
![Page 42: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/42.jpg)
42
Cleaning out the Database
• After creating the CSV, we want to remove the data from the Berkeley DB databases.
• Two options:• Delete and recreate the database.• Truncate the database (leave the database and
remove all the data).
![Page 43: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/43.jpg)
43
Removing a Database
• Removal with a DB handle (un-opened)ret = db_create(&dbp, NULL, 0);
… error_handling …ret = dbp->remove(dbp, file, database, 0);
… error handling …
• Removal with a DB_ENV (no DB handle)ret = dbenv->dbremove(dbenv, NULL,
file, database, 0); … error handling …
![Page 44: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/44.jpg)
44
Truncating a Database
• Truncate with an open DB handleret = dbp->truncate(dbp, NULL, &nrecords, 0);
… error handling …
• Nrecords returns the number of records removed from the database during the truncate.
![Page 45: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/45.jpg)
45
Part I: Data Store
• Overview• Creating and removing databases• Getting and putting data
• Case Studies• Telecom Use Case• Customer account management
• Secondary indexes• Cursor-based manipulation• Configuration and tuning
![Page 46: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/46.jpg)
46
Customer Account Management
Database
Orders
Log Call
Customer data
Create order
Customer history
Add Customer
Warehouse
SupportManager
Fulfill orders
View last week’s calls
Labels by zip
![Page 47: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/47.jpg)
47
The Schema
Customer Data Get by name
Get by ID
Update
Add new
Catalog Data Get by name
Get by ID
Call logAppend
Get by time
Get by customer
Order DataGet by ID
Get by customerUpdate
Add new
![Page 48: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/48.jpg)
48
Secondary Indexes
• Note that most of the data objects require lookup in (at least) two different ways.
• Berkeley DB keys are primary (clustered) indexes.
• Berkeley DB also supports secondary (unclustered) indexes.• Automatic maintenance in the presence of insert,
delete, update
![Page 49: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/49.jpg)
49
Selecting keys and indexesDatabase Primary Key Secondary
KeyDuplicates?
Customer Customer ID Name No
Catalog Product ID Name No
Order Order ID Customer Maybe
Calls Timestamp Customer No
![Page 50: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/50.jpg)
50
Secondaries internally
Customer Primary Database
Key Data01928374 IBM; Hawthorne, NY …
19283746 HP; Cupertino, CA …
28910283 Dell; Austin, TX …
… …
Customer Secondary
Key DataDell 28910283
IBM 01928374
HP 19283746
… …
![Page 51: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/51.jpg)
51
Secondaries Programmatically
• Create new database to contain secondary.• Define callback function that extracts
secondary key from primary key/data pair.• Associate secondary with (open) primary.
![Page 52: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/52.jpg)
52
Customer Secondary (1)
• Assume our customer data item contains a structure:
struct customer {
char name[20];
char address1 [20];
char address2 [20];
char state[2];
int zip;
};
![Page 53: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/53.jpg)
53
Customer Secondary (2)
• Need callback function to extract secondary key, given a primary key/data pair:
int customer_callback(DB *dbp, DBT *key, DBT *data, DBT *result)
{
struct customer rec;
rec = data.data;
result.data = rec.name;
result.size = sizeof(rec.name);
return (0);
}
![Page 54: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/54.jpg)
54
Customer Secondary (3)
• Associate the (open) primary with the (open) secondary:
ret = pdbp->open(pdbp, NULL, “customer.db”, NULL, DB_BTREE, 0);
if (ret != 0)
return (ret);
ret = sdbp->open(sdbp, NULL, “custname.db”, NULL, DB_BTREE, 0);
if (ret != 0){
(void)pdbp->close(pdbp, 0);
return (ret);
}
ret = pdbp->associate(pdbp, NULL, sdbp, customer_callback, 0);
if (ret != 0)
return (ret);
![Page 55: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/55.jpg)
55
Representing Orders
• What is an order?• An order number• A customer• A date/time• A collection of items
• How do we represent orders?• Key by order number with items in a single data item• Key by order number with duplicates for items• Key by order number/sequence number
![Page 56: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/56.jpg)
56
Orders: Multiple Items in Data
typedef int order_id; /* Primary key */struct order_info {
int customer_id;time_t date;float total;time_t shipdate;int nitems; /* Number of items to follow. */char[1] order_items; /* Must martial all items into here */
};
Struct order_item {int item_id;int nitems; /* per-item price stored elsewhere */float total;
};
![Page 57: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/57.jpg)
57
Orders: Multiple Items in Data -- Secondaries
• Secondary on customer_id• Just like what we did on customer• Create a callback that extracts the customer_id
field from the data field.• Associate secondary with primary.
• Secondary on items• Need to return multiple secondary keys for a single
primary record.
![Page 58: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/58.jpg)
58
Secondaries: Return multiple keys per key/data pair
int item_callback(DB *dbp, DBT *key, DBT *data, DBT *result){
struct order_info rec;DBT **dbta;int i;
order = data.data;result.flags = DB_DBT_MULTIPLE | DB_DBT_APPMALLOC;result.size = order->nitems;result.data = calloc(sizeof(DBT), order->nitems);if (result.data == NULL)
return (ENOMEM);dbta = result.data;for (i = 0; i < order->nitems; i++) {
dbta[i].size = sizeof(order_item);dbta[I].data = order->order_items[i];
}return (0);
}
![Page 59: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/59.jpg)
59
Secondaries: Example of multiple keys per key/data pair
Order Primary Database
Key Data9876543 19283746;3/17/08;138.50;3/18/09;3
0003;2;130.00;0019;1;8.50
8769887 19283877;3/16/08;1024.00;3/16/0810
0092;10;1024.00
5430987 90867654;3/16/08;564.98;;5
0003;3;195.00;0092;1;102.40;
9902;1;267.58
… …
Order/Item Secondary
Key Data0003 9876543
54390875439087
0019 9876543
0092 8769887
5430987
9902 5430987
… …
![Page 60: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/60.jpg)
60
Orders: Duplicates for Items
• Modify previous structures slightly:typedef int order_id; /* Primary key */
struct order_info {
int customer_id;
time_t date;
float total;
time_t shipdate;
int nitems; /* Number of items to follow. */
};
struct order_item {
int item_id;
int nitems; /* per-item price stored elsewhere */
float total;
};
• First duplicate is order; rest are items
![Page 61: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/61.jpg)
61
Orders: Items as duplicates Example
Order Primary Database
Key Data9876543 19283746;3/17/08;138.50;3/18/09
0003;2;130.00
0019;1;8.50
8769887 19283877;3/16/08;1024.00;3/16/0810
0092;10;1024.00
5430987 90867654;3/16/08;564.98;;5
0003;3;195.00
0092;1;102.40
9902;1;267.58
![Page 62: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/62.jpg)
62
Orders: Duplicates for ItemsInserting an order
int insert_order(int order_id, struct orderer_info *oi; struct order_items **oip){
DBT keydbt, datadbt;int I;
keydbt.data = &order_id;keydbt.size = sizeof(order_id);datadbt.data = oi;datadbt.size = sizeof(order_info);dbp->put(dbp, NULL, &keydbt, &datadbt, DB_NODUPDATA);for (i = 0; i < oi->nitems; i++) {
datadbt.data = *oip++;datadbt.size = sizeof(order_item);dbp->put(dbp, NULL, &keydbt, &datadbt, 0);
}}
![Page 63: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/63.jpg)
63
Orders: Duplicates for ItemsTrade-offs
• Must configure database for duplicates.• - No automatic secondaries (must hand-
code).• + Easy to add/remove items from order.
![Page 64: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/64.jpg)
64
Orders: Key by order_id/seqno
struct pkey { /* Primary key */int order_id;int seqno;
};struct order_info {
int customer_id;time_t date;float total;time_t shipdate;int nitems; /* Number of items to follow. */
};
struct order_item {int item_id;int nitems; /* per-item price stored elsewhere */float total;
};
![Page 65: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/65.jpg)
65
Orders: key by order/seqnoExample
Order/Item Secondary
Key Data0003 9876543/01
0003 5439087/015439087/01
0019 9876543/02
0092 8769887/01
0092 5430987/02
9902 5430987/03
… …
Order Primary Database
Key Data9876543/00 19283746;3/17/08;138.50;
3/18/09
9876543/01 0003;2;130.00
9876543/02 0019;1;8.50
8769887/00 19283877;3/16/08;1024.00;
3/16/0810
8769887/01 0092;10;1024.00
5430987/00 90867654;3/16/08;564.98;;5
5430987/01 0003;3;195.00
5430987/02 0092;1;102.40
5430987/03 9902;1;267.58
![Page 66: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/66.jpg)
66
Orders: Key by order/seqnoInserting an order
int insert_order(int order_id, struct orderer_info *oi; struct order_items **oip){
DBT keydbt, datadbt;struct pkey;int i;
pkey.order_id = order_id;pkey.seqno = 0;keydbt.data = &pkey;keydbt.size = sizeof(pkey);datadbt.data = oi;datadbt.size = sizeof(order_info);dbp->put(dbp, NULL, &keydbt, &datadbt, DB_NODUPDATA);for (i = 0; i < oi->nitems; i++) {
pkey.seqno = i + 1;datadbt.data = *oip++;datadbt.size = sizeof(order_item);dbp->put(dbp, NULL, &keydbt, &datadbt, 0);
}}
![Page 67: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/67.jpg)
67
Order Trade-offs• ID Key w/multiple items in data
• One get/put per order• No duplicates means automatic secondary support• Add/delete/update item is somewhat messier
• ID key w/duplicate data items• Easy to add/delete/update order items• Must implement secondaries manually• Where do you place per-order info (CID, data) (first dup?)
• Key is ID/sequence number• Easy to add/delete/update order items• Can support secondaries automatically• Where do you place per-order info (data item 0?)
![Page 68: Oracle Berkeley DB - Data Storage (DS) Tutorial](https://reader034.vdocuments.us/reader034/viewer/2022052208/54c689da4a7959163d8b4586/html5/thumbnails/68.jpg)
68
End of Part I