Building a simple Node.js application with Oracle

Anyone who knows me will tell you, my expertise is the Oracle Database and not web applications or JSON. But my new role is all about pushing myself out of my comfort zone, so I wanted to get some hands on experience with both application development and JSON in the Oracle Database.

My colleague, Mark Drake, the PM for JSON in the Oracle Database, suggested I get my feet wet by trying the new Movie Ticketing Application tutorial available on the JSON page on Oracle.com.

The Movie Ticketing Application is written in Node.js and connects to the Oracle Database via a REST Data Service. Since I recently signed up for the Oracle Database Exadata Express cloud service, I had the perfect Database setup for a web application.

So, armed with the application code and a detailed set of instructions, I began my journey.

The first thing I had to do was install Node.js. Since my main environment is an Apple Mac, installing Node.js was very simple: download the installer followed by a couple of clicks. To check I had done it correctly my husband taught me the equivalent of “Hello World” in JavaScript.

MCOLGAN-mac:~ mcolgan-mac$ node

> a='Hello World'

'Hello World'

> console.log(a)

Hello World

Mark supplies all of the application code; you just need to install it. The installation was a little more complicated for me as I had to navigate around the Oracle firewall but I followed Mark’s instructions carefully and Voila, the application is now installed.

The application creates and retrieves JSON documents, so the next step is to create a document store, which is a schema-less environment where JSON documents can be stored and accessed.

Starting in 12c (12.1.0.2), JSON documents can be stored and accessed directly in the Oracle Database. Unlike XML, there is no new JSON data type in 12c. Instead JSON is stored as text, in any table column, using a VARCHAR2, CLOB or BLOB data type. Using existing data types ensures that JSON data is automatically supported with all of the existing database functionality, including Oracle Text and Database In-Memory.

That’s great but how do we get the JSON documents in and out of the database with a Node.js application?

12c also offers new APIs known as Simple Oracle Document Access (SODA) that provides a documents and collections interface to the Oracle Database to support schema-less applications. SODA comes in two flavors, Java and REST. Mark’s application uses the REST API to store and access JSON documents in the Oracle Database via HTTP calls.

So, what do I have to do to set up SODA?

Since I’m using the Oracle Database Exadata Express cloud service as my backend, I get to cheat on this step. Right on the front page of the Exadata Express console is a button that will automatically set up a document store.

Clicking the button opens a dialog window that enables you to create a new database schema and enable SODA for REST for that schema.

Don’t panic if you don’t have access to a cloud account. You can setup SODA very easily on any Oracle 12c database. Beda Hammerschmidt (the developer responsible for JSON support in the Oracle Database) does a great job of explain how to manually setup SODA in an on-premises database on his blog. Note SODA for REST requires Oracle Database patch 20885778 to be installed.

But I digress. Let’s get back to the tutorial. If I look in the MOVIES schema that I just setup on my Exadata Express account, I see that its currently empty.

SELECT TABLE_NAME FROM user_tables;
 
no ROWS selected

Since this is a schema-less application, there are no tables or indexes required upfront. Once the application starts loading data, SODA will automatically create tables based on the collections or documents inserted. More on this in a moment, as we haven’t quite finished the setup.

The final part of the setup is the editing of the Node.js application configuration files. These files tell the application which database to connect to and what schema to use etc. Editing these files gave me my first taste of a real-world JSON document, which wasn’t as scary as I had originally thought. After all it’s a text file that contains name / value pairs. All I had to do was edit the values to match my environment.

Once configured, start the Node.js application server by executing the command:

node INDEX.js

Now the application server is running, we can launch the application in a web browser.

Before we can use the application to find what movies are currently playing in our local theaters, we need to load the theater, movie and showtime information into the database. The information comes from theMovieDB.org and Mark explains how to get your own account with them in the tutorial. To begin the population of theater and movie information, clicking on the load button.

After the theater information is populated, we can now see objects in the MOVIES schema, including a table called THEATER that contains the JSON documents with all of the information we need about each movie theater.

SELECT TABLE_NAME FROM user_tables;
 
TABLE_NAME
------------------------
DR$THEATER_SEARCH$I
THEATER
DR$THEATER_SEARCH$K
DR$THEATER_SEARCH$R
DR$THEATER_SEARCH$N
DR$THEATER_SEARCH$U
 
6 ROWS selected.
 
DESC THEATER 
 Name					   NULL?    TYPE
 ----------------------------------------- -------- ----------------
 ID					   NOT NULL VARCHAR2(255)
 CREATED_ON				   NOT NULL TIMESTAMP(6)
 LAST_MODIFIED				   NOT NULL TIMESTAMP(6)
 VERSION				   NOT NULL VARCHAR2(255)
 JSON_DOCUMENT					    BLOB

Since the JSON_DOCUMENT is stored as a BLOB in the database, we can query it just as we would any BLOB.

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(JSON_DOCUMENT)) FROM theater WHERE rownum < 2;
 
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(JSON_DOCUMENT))
------------------------------------------------------------------------------------------------------
{"id":1,"name":"Regal Jack London Stadium 9","location":{"street":"100 Washington St","city":"OAKLAND"
,"zipCode":"94607","state":"CA","phoneNumber":NULL,"geoCoding":{}},"screens":[{"id":1,"capacity":68,"f
eatures":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9.95,"
seniorPrice":9.95}},{"id":2,"capacity":77,"features":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPric
ing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":9.95}},{"id":3,"capacity":104,"features":{"th
reeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":
9.95}},{"id":4,"capacity":103,"features":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adult
Price":14.95,"childPrice":9.95,"seniorPrice":9.95}},{"id":5,"capacity":77,"features":{"threeD":FALSE,"
reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":9.95}},{"id":
6,"capacity":69,"features":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"
childPrice":9.95,"seniorPrice":9.95}},{"id":7,"capacity":126,"features":{"threeD":FALSE,"reserveSeats"
:FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":9.95}},{"id":8,"capacity":
95,"features":{"threeD":FALSE,"reserveSeats":FALSE},"ticketPricing":{"adultPrice":14.95,"childPrice":9
.95,"seniorPrice":9.95}},{"id":9,"capacity":110,"features":{"threeD":FALSE,"reserveSeats":FALSE},"tick
etPricing":{"adultPrice":14.95,"childPrice":9.95,"seniorPrice":9.95}}]}

A far more elegant way to query JSON data is to take advantage of our simplified syntax, which uses the same dot notation we use in SQL.

TABLE_NAME.COLUMN_NAME.JSON_STRING

So in our case we could see the first 10 movie theaters loaded into the database using the following query.

SELECT t.json_document.name FROM theater t WHERE rownum < 11;
 
NAME
----------------------------------------------
Regal Jack London Stadium 9
AMC Bay Street 16
UA Berkeley 7
AMC Metreon 16
Century San Francisco Centre 9 AND XD
CineArts @ Empire
UA Stonestown Twin
Century 20 Daly City AND XD
CineLux Chabot Cinema
Tiburon Playhouse 3 Theatre
 
10 ROWS selected.

The other tables visible in the MOVIES schema are for the Oracle Text index that is automatically built on top of the JSON documents by the application. The indexing strategy is defined in the collections.json file that Mark supplies with the application. I’ll blog in more detail about using Oracle Text to search JSON document in the future.

After the theaters are load, you need to load the movies, posters and screens. After which the following tables are in the MOVIES schema.

SELECT TABLE_NAME FROM user_tables;
 
TABLE_NAME
----------------------------------------------------------------------------------------------------
THEATER
DR$THEATER_SEARCH$I
DR$THEATER_SEARCH$K
DR$THEATER_SEARCH$R
DR$THEATER_SEARCH$N
DR$THEATER_SEARCH$U
MovieTicketLog
TicketSale
Movie
DR$MOVIE_SEARCH$I
DR$MOVIE_SEARCH$K
DR$MOVIE_SEARCH$R
DR$MOVIE_SEARCH$N
DR$MOVIE_SEARCH$U
Poster
Screening
 
16 ROWS selected.

So we have 6 tables automatically created, one for each collect and two Oracle Text indexes, one on the JSON_DOCUMENT column in THEATERS and the other on the
The application is now ready to use and the screen should automatically switch to list all of the theaters. If you click on a MOVIES link next to one of the theaters, it will show you what movies are playing in that theater and their showtimes.

If you like me, want to  get your feet wet with JSON, I recommend trying the new Movie Ticketing Application tutorial available on the JSON page!

Posted in Oracle Database 12c New Features | Tagged , , | Leave a comment

Excited to be the newest member of the AskTom Team

A little over a year ago Connor McDonald and Chris Saxon took over answering questions posted on AskTom, which covers many topics related to building applications on top of the Oracle Database. Connor and Chris have done an amazing job of keeping up with the constant influx of questions, with nearly 16,000 answered to date.

Last week, I was lucky enough to be invited to join Connor and Chris in answering questions submitted AskTom. So, if you have any burning questions regarding the Oracle Database or building applications on top of the Oracle Database, you can submit them at AskTom and Connor, Chris or I will get back to you!

Posted in AskTom | Tagged , | 3 Comments

Online Statistics Gathering

Although there have been a number of significant changes to the Oracle Database in 12c, some of my favorite new features are actually the small enhancements, designed to make your life a little easier. So, I decided to spend some time this year sharing the details on these small but extremely useful features.

One such enhancement is Online Statistics Gathering.

Whenever an index is created, Oracle automatically gathers optimizer statistics for that index. The database piggybacks the statistics gather on the full data scan and sort operation necessary for the index creation. This approach has worked so well since it was introduced in 9i, few people even realize it’s happening.

SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') WALL_CLOCK_TIME 
FROM dual;
 
WALL_CLOCK_TIME
-----------------------------
03-JAN-2017 21:29:44 
 
SELECT index_name, 
       TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS') 
       last_analyzed,
       leaf_blocks,
       distinct_keys,
       clustering_factor
FROM   user_indexes; 
 
no ROWS selected
 
CREATE INDEX t_s_idx ON items (s);
 
INDEX created.
 
SELECT index_name, 
       TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS') 
       last_analyzed,
       leaf_blocks,
       distinct_keys,
       clustering_factor
FROM   user_indexes; 
 
INDEX_NAME LAST_ANALYZED	 LEAF_BLOCKS DISTINCT_KEYS  CLUS_FACT
---------- -------------------- ----------- ------------- ----------
T_S_IDX    03-JAN-2017 21:29:45    6135	      1000000        999839

In Oracle Database 12c, the same technique is now applied for direct path operations such as, Create Table As Select (CTAS) and Insert /+APPEND */ As Select (IAS) operations into an empty table or partition.

Piggybacking the statistics gather as part of the data loading operation, means no additional full data scan is required to have statistics available immediately after the data is loaded. The additional time spent on gathering statistics is small compared to a separate statistics collection process, and it guarantees accurate statistics are readily available from the get-go.

SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') WALL_CLOCK_TIME 
FROM   dual;
 
WALL_CLOCK_TIME
-----------------------------
03-JAN-2017 21:40:07
 
CREATE TABLE items2 AS SELECT * FROM items; 
 
TABLE created.
 
SELECT TABLE_NAME, 
       num_rows,
       TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS') last_analyzed
FROM   user_tables;
 
TABLE_NAME   NUM_ROWS  LAST_ANALYZED
---------- ----------  ---------------------
ITEMS	      1000000  20-OCT-2016 05:58:26
ITEMS2	      1000000  03-JAN-2017 21:40:09
 
SELECT column_name, 
       num_distinct, 
       num_nulls, 
       histogram, 
       notes
FROM   user_tab_col_statistics
WHERE  TABLE_NAME='ITEMS2';
 
COLUMN_NAME NUM_DISTINCT  NUM_NULLS HISTOGRAM	     NOTES
----------- ------------ ---------- --------------- --------------
N		  624640	  0 NONE	    STATS_ON_LOAD
S		  988416	  0 NONE	    STATS_ON_LOAD

Be aware that online statistics gathering will not occur if any data exists in the table or partition, as the overhead of reading the existing data could greatly increase the time it takes to complete the data load. Only base table and column statistics are gathered. Histograms are not gathered automatically, as they require a second pass of the data, which again could adversely affect the elapse time for the database load.

Histograms can be gathered manually without re-gathering base column statistics by using new GATHER AUTO option in the DBMS_STATS package.

BEGIN
dbms_stats.Gather_table_stats('SH','ITEMS2',options=>'GATHER AUTO');
END;
/ 

If for some reason you wish to turn off this new automatic online statistics gathering, you can do so by setting the underscore parameter _optimizer_gather_stats_on_load to FALSE.

Posted in Optimizer, Oracle Database 12c New Features | Tagged , , | 5 Comments

Coming Soon!

I recently switched my role in the Oracle Database Development team from the product management team for Database In-Memory and Advance Compression to be a database evangelist.

With this new role comes a new blog and an opportunity for me to broaden the topics I blog about. I’ll also have an opportunity to share my own opinion and approach to optimizing your Oracle investment.

I hope you will join me on this new journey and will learn a little something along the way!

Posted in Uncategorized | 4 Comments