Introduction to Couchbase for Oracle Developers & Experts: Part 2: Database Objects

Oracle DBAs work with physical clusters, machines, instances, storage systems, disks, etc. All of Oracles users, developers and their applications work with logical entities: databases, tables, rows, columns, partitions, users, data types.  There are a lot of similarities and some differences.  Let’s compare and contrast how this is done on Couchbase.

Here’s the home page for the series: http://blog.planetnosql.com/2022/01/introduction-to-couchbase-for-oracle.html

ORACLE Organization

Full list of Oracle schema objects are listed here. A database instance can have many databases; A databases many schema; A schema many tables, indexes, functions, etc.

COUCHBASE Organization

A Couchbase instance can have many buckets; A bucket can have many scopes; A Scope can have many collections, indexes, functions, search indexes, analytic collections, eventing functions. Each bucket comes with a _default scope and _default collection mainly for backward compatibility. You can create new scopes, collections, indexes and functions using the respective CREATE statements.

	ORACLE	COUCHBASE
	DATABASE	BUCKET Within a Couchbase instance (single node or multi-node cluster), you can create one or more buckets.  Within each bucket, you can have one ore more scopes and within each scope one ore more collections, indexes, functions, search indexes, analytic collections, and eventing functions (similar to AFTER TRIGGERS).  See detailed explanation in this blog. CREATE BUCKET: You create bucket either via Couchbase web console or via REST API. In the web console, you provide the information below.                     User provides the following: Name of a bucket. Location of an existing data storage. Couchbase creates a directory with the bucket name underneath the specified directly. In this case, CUSTOMER directory is created under : /my/data.  This directory /my/data should exist on every node of the Couchbase cluster with data (key-value) service.  This is fixed and unchangeable. Memory used in megabytes. This is the fixed amount of memory used to cache the data as you insert/load the data.  The actual data in memory depends on your application access pattern.  Usual LRU, MRU algorithms are used to determine which documents are kept in memory and which ones are evicted. For additional information on key eviction, see the link: http://bit.ly/2ngKUZk Bucket Type Couchbase: JSON and key-value databases Memcached: Memcache Ephemeral: just like Couchbase bucket, except all the data, index are only in memory. Replica: By default there is one copy of the data in the cluster. You can have up to three copies of the data within the cluster. Under the CAP theorem rules. There are plenty of papers and talks on CAP theorem and their application to NoSQL databases in public domain.Couchbase Bucket is a CP system. That means, Couchbase chooses consistency over availability (C over A).  Supporting partition tolerance is a requirement for these multi-node scale out systems. See Couchbase documentation for full details on all the parameters and examples. http://bit.ly/2GrbMOw
	TABLE	COLLECTION Hierarchy: Bucket->Scope->Collections In RDBMS, a table is a set of rows. In Couchbase, a collection is a set of JSON documents.  While buckets and scopes provide namespaces, collections provide a mechanism to store and manipulate a set of JSON documents.  Since JSON is self describing, you don't need to define the schema before inserting or loading data into the collection.   Example document INSERT via N1QL: CREATE SCOPE mybucket.myscope; CREATE COLLECTION mybucket.myscope.mycustomers INSERT INTO mybucket.myscope.mycustomers VALUES("CX:3424", {     "Name": "Jane Smith",     "DOB": "1990-01-30",    "Billing": [ { "type": "visa", "cardnum": "5827-2842-2847-3909", "expiry": "2019-03" }, { "type": "master", "cardnum": "6274-2842-2847-3909", "expiry": "2019-03" } ], "Connections": [ { "CustId": "XYZ987", "Name": "Joe Smith" }, { "CustId": "PQR823", "Name": "Dylan Smith" }, { "CustId": "PQR823", "Name": "Dylan Smith" } ], "Purchases": [ { "id": 12, "item": "mac", "amt": 2823.52 }, { "id": 19, "item": "ipad2", "amt": 623.52 } ] }); The INSERT statement look similar to Oracle's INSERT statement except you specify the data in a slightly different way: You specify the document key and give the whole JSON document to insert.  The table is automatically sharded (uses consistent hash partitioning) -- nothing for the user to do.  Couchbase SDKs also provide a simpler way to INSERT, UPDATE, UPSERT individual documents directly in each of the SDKs.  Here's the Java SDK example.
	ROW	JSON DOCUMENT or a binary object JSON Document, with its documentkey.  Each document can have varying number of fields, data types and structures. Since each JSON document is self describing, the field name (column name) is derived from the document and the type of the data is interpreted according to rules of JSON spec. Document key (user generated): "CX:3424" { "type" : "CUSTOMER", "Name" : "Jane Smith", "DOB" : "1990-01-30", "balance": 48259.12, "premium": true, "overdraft": null } Considerations for document key design: http://bit.ly/2GnRwwV
	COLUMN	Key-Value pairs (or Field) JSON is made up of key-value pairs.  The key name in individual fields are similar to column names. In relational world, you declare the column name upfront where as in JSON, each document describes the column name. And therefore, each document in a collection can have arbitrary fields with any valid JSON typed values. Example: {“fullname”: “Joe Smith”} {“name: { “fname”:”Joe”, “lname”:”Smith”} {“hobbies”: [“lego”,”robotics”, “ski”]} In these documents, “name” is a key, also known as an attribute. Its value can be scalar (fullname) or an object (name) or array (hobbies). In Oracle, when you create the table, you specify column names and their data types. In Couchbase, you simply insert JSON documents.  Each document self-describes the attribute (column) names. This gives you the flexibility to evolve the schema without having to lockdown the table. The data types are simply interpreted from the value itself: String, number, null, true, false, object or an array.
	Views can be created with CREATE VIEW statement. Once created, these are simply relations that can be used anywhere a table (set of relations) can be used. There are some additional requirements for updates on views (e.g. instead of triggers for insert, updates on complex views).	Couchbase does not have dynamic SQL based view like Oracle. Couchbase does have a technology we call, “Couchbase Views”, based on map reduce framework. These are similar to materialized views and not the SQL View and cannot be used with N1QL.
	Materialized Views	Couchbase VIEW provides a flexible map-reduce framework for you to create pre-aggregations, indexes, anything else you want to create.  See more details at: http://bit.ly/2EhIFfF
	Sequences	Unavailable
	TRIGGERS	EVENTING FUNCTIONS Couchbase Eventing provides a easy way to program down the stream actions.  The big difference between database triggers and eventing actions are the eventing action is done asynchronously and outside the transaction that's updating the data.  See detailed use cases and docs here.
	Constraints: primary key, unique, Check, referential, not null,	Couchbase requires and enforces unique constraint on the document key within a bucket. Documents can have reference to other document’s key. It’s simply storing the data and can be used in JOIN operations. The reference itself isn’t checked or enforced.
	INDEXES	INDEXES  Indexes in Couchbase comes in two varieties: In memory and standard secondary. Standard secondary indexes are similar to the Oracle indexes. You can have large number of indexes with large number of keys. Required portions of the indexes will be paged in as required. In memory indexes are completely kept in memory and therefore will require proportional memory allocation.  Indexes in Couchbase are also referred to as GSI - Global Secondary Indexes.   A collection is hash partitioned automatically, but the index indexes the whole (global) data of a collection.   Both types of the indexes are eventually consistent.  Remember, Couchbase is a distributed system.  The changes to the documents are available immediately (Couchbase is a CP system in the CAP regime).  For each type of index, you can use variety of indexes using multiple features: Primary Index Named primary index Secondary index Composite Secondary Index Functional index Array Index ALL array ALL DISTINCT array Partial Index Adaptive Index Duplicate Indices Covering Index Flex indexes More details in the Index section. For now, you can see the detailed blog: http://bit.ly/2DI1nAa All these indexes shouldn't worry you.  Couchbase has an indexer advisor as a service and the feature is built into the product query workbench as well (enterprise version).
	Functions  (PL/SQL functions and stored procedures)	User Defined Functions (UDFs) Couchbase query service has two types of functions: - Simple SQL based functions which can return expressions or run a SELECT statement to return values.  - Javascript functions can be elaborate programs with complex logic and compute. Starting in Couchbase 7.1, the Javascript functions can also issue all of N1QL statements, can be part of transactions and can run transactions themselves -- just like Oracle PL/SQL and SQL Server T-SQL. And the developers agree Javascript is an easier to use language to program!