Introduction to Couchbase for Oracle Developers and Experts: Part 3: Data Types
In this blog series, we are exploring various topics to compare Oracle and Couchbase from a developer perspective. Today, we are going to talk about data types.
CORE
·
Join the DZone community and get the full member experience.
Join For Free
ReModeling: From Relational to Document Model
| Oracle | Couchbase | |
| Model | Relational, Object-relational | JSON model with N1QL (SQL for JSON) |
| Data Types | A long list of data types | https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datatypes.html |
| String Data types | CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR, LOONGVARCHAR | String, up to 20MB in length. All of the string data in JSON is Unicode. |
| Date and Time Data Types | DATE, TIME, TIMESTAMP, all these with TIMEZONE, INTERVAL | Date, Time, Timestamp, all these with timezones should be represented in ISO 8601 format and saved as strings. N1QL has extensive functions for formatting, extraction, conversion, and arithmetic. This article covers conversion and arithmetic on them in detail. |
| Boolean | BOOLEAN | Boolean true and false are automatically interpreted as a boolean value. |
| Numerical | NUMBER DECIMAL BIGINT SMALLINT INTEGER FLOAT REAL DOUBLE |
Numeric data: can be integer, fraction or an exponent. Here are the ranges: https://docs.couchbase.com/server/current/analytics/9_data_type.html |
| Binary Data Types | BINARY VARBINARY LONGVARBINARY |
You can store the whole document as binary or encode the binary as base64 values. |
| Large Object Data Types | ||
| BLOB, RAW, LONG_RAW | Each document can be up to 20 MB. Binary data can be encoded via BASE64 | |
| CLOB | Each document can be up to 20 MB. | |
| ABSTRACT Types, NESTED TABLES | Builtin support for objects, arrays, arrays of objects, objects of arrays. No support for user-defined opaque data types. | |
| XML | Does anyone still use XML in databases? ;-) | |
| ANY TYPE | ANY TYPE was invented to make the Oracle routines flexiblle — in terms of types they handle as parameters and return types. In JSON model, |
|
| Objects | CREATE the object types and the columns associated with it explicitly: CREATETYPE person_typ ASOBJECT |
OBJECTs are built into a JSON model with any number of nested levels with any data type, objects, or arrays. |
| Arrays | VARRAY can create an array of a specific type and then use that type as the type for a column. URL: https://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#i468598 |
An array is a first-class citizen in the JSON data model and can be used for any value. Each array can be of scalars of any type: scalars, objects, arrays, etc. “A”: [1, “X”, [3, 4]] “B”: [{“x”:1}, {“x”:2}] |
Additional Notes
Date and Datetime Types
Oracle and other RDBMS have extensive data types for handling time-related data and manipulating them. JSON does not have a date or date-time type. We’ve chosen ISO 8601. The idea is to store the date and time-related data in a string form, conforming to ISO 8601 and then manipulating it in a consistent way. See this blog with a detailed comparison to Oracle types, conversion, and formatting functions you can use in N1QL to extract and manipulate the date and time data.
The primary Key to Document Key Conversion.
Couchbase document key is always less than 255 bytes and is usually a string. RDBMS can have a single column or multiple columns (composite) primary key for a table. One common way to convert is to simply have a separator between the individual parts after converting each part to a string. The document key should be unique to a bucket and hence it’s typically prefixed with the table (collection) type. See the blog for examples of how to do this correctly.
Published at DZone with permission of Keshav Murthy. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments