Category Archives: REST

CodeCard Sketch changes: Howto disable fingerprint check and increase REST URI size

Pre-Requisites

  • You have followed the instructions here.
  • You are comfortable using the Arduino IDE and making changes to the Sketch.

Fingerprint Check

In the comments on the post You Went to Oracle Open World 19 and got a Code Card, now what? we talked about how to update the fingerprint of the security certificate for the REST API when it changes.

I’m not a big fan of doing things multiple times and since I’m not planning on doing anything of a sensitive nature with my CodeCard I decided I would just disable the fingerprint check altogether.

How to tell if the fingerprint check is causing your badge to stop working.

If you push one of the buttons and it hangs on the “Please wait…” screen forever, check the Serial Monitor for a message similar to this.

Warning, If you’re planning to use your CodeCard for anything you’d consider sensitive, do not make this change!

Upgrade the driver

  1. Clone or download the CodeCard repository
  2. In the Arduino IDE, open the file codecard/arduino/codecard/codecard.ino
  3. Open the Board Manager.  Tools/Board/Boards Manager
  4. Search for esp
  5. Select version 2.5.2
  6. Click Install
  7. Close the Boards Manager
Code Changes
  • Click on the httpClient.h tab
  • Replace line 74 with BearSSL::WiFiClientSecure client;

  • On line 102 add client.setInsecure();

  • Comment out the fingerprint check starting on line 112

  • Uncomment line 152 and remove line 153

  • On line 176 add secureClient.setInsecure();

  • Comment out the fingerprint check starting on line 185

Increase arrayToStore variable size

If you’re planning to change the REST calls to use a different and longer URI you will want to increase the size of the arrayToStore variables.  Currently they are set to 100, I changed mine to 200, but you can use whatever you’d like.

  • Click on the memory.h tab
  • On lines 18 and 46, change the arrayToStore variable to the new size
  • Click on the wifi.h tab
  • On line 7, change the arrayToStore variable to the new size

Troubleshooting

Sketch uses too much program storage space

I have seen this error off and on when I compile the sketch.  If you get this error, the easiest way to reduce the size of the sketch is to remove some of the larger icons in icons.h.

  • Click on icons.h
  • Find a 128x128px image that you don’t plan to use and either comment it or remove it.
  • Click on templates.h
  • Find the drawIcon128(int x, int y, String icon, long color) method and comment or remove the reference to the image you removed.
  • You may have to remove more than one image.
“Connection failed” or you receive an empty response “{}”

This one can be tricky.  The way I’ve solved this is to erase the ESP8266 flash memory.

Usually this works:

  • Connect with the Serial Monitor
  • Press and release the A and B buttons
  • Wait for the Serial Monitor to display all of the data
  • Enter “reset” and send
  • Enter “ls” and update any settings as needed

If that doesn’t work you may need to search for another way to reset the memory.  Please leave a comment if you have a better way.

 

Oracle REST Data Services on Autonomous Transaction Processing Database

Whenever I give a presentation, write a blog post or make a video I like to run all of my examples and demos locally and on an Oracle Cloud instance.  I keep a Virtual Box machine or Docker Container ready on my laptop, just in case I can’t get to the internet, but for speed and dependability, you just can’t beat a cloud instance.

I like to have some automation scripts handy to create and destroy my environments and depending on what I’m doing that can sometimes take a while.

So when Oracle’s Autonomous Transaction Processing Database was released I was excited by how fast I could spin up a new instance.  It takes a few minutes instead of around an hour for other options.  Oracle wanted to make sure ATP was solid, so they initially launched with a limited feature set and new features have been making their way into Oracle’s Autonomous Transaction Processing Database almost every month.

At KScope19 it was announced that Oracle REST Data Services is now available on ATP.

I was scheduled to give my ‘Creating REST APIs Is Fast, Easy, and Secure with Oracle REST Data Services‘ talk on Wednesday and I like to live dangerously, so I decide (Tuesday afternoon) to convert my demos over to ORDS on ATP.  I figured worst case, if I couldn’t figure it out I’d just use my Docker container.

It’s going to be hard… right?

I already had a couple of ATP instances that I use for stuff and things.  Since I wanted to do a live demo, I decided to spin up a new instance just in case I expose anything that could be used to get in.  It took under 5 minutes to fully create and start up a brand new ATP database.  After that, I downloaded my Client Credentials (Wallet) and connected with SQL Developer.  There are lots of examples for this part so I’ll move on to the new stuff.

New User

I try to do as little as possible as the Admin user so I created a rest_demo user with a couple of tables and a view.

Create a test API

Then I used the SQL Developer ‘Auto REST’ feature to REST enable the schema and a table.   (PL/SQL exported below in case you want to try it.)

Get the URI

Now I just needed the URI to test my new service.  Here are the steps:

  1. Log into your Oracle Cloud Dashboard.
  2. From the menu, select ‘Autonomous Transaction Processing’.
  3. From the instance menu, select ‘Service Console’.

    Or, Click on your Instance Name then Click the ‘Service Console’ button.
  4. Click ‘Development’.
  5. In the Rest Data Services box, click the “Copy URL” button.
Test it

For a simple fast test, I just used an internet browser with the following URI:
https://someIdstuff-blaineords.adb.my-region-1.oraclecloudapps.com/ords/api/cool_data

Well, that was easy.

To be honest I expected this to be a lot harder and that I’d have problems.  I ran through all of the demos for my presentation and they just worked, first try.

The thing I spent the most time on was re-checking everything.  Since it was so easy, I thought I had connected to one of my pre-existing cloud databases.

Go try it!

As always, don’t take my word for it, go try it yourself.

Here are some links to help get you going:

Quick and Easy Setup – Oracle Xe and ORDS Using Docker

In this post I’ll show you how to stand up a couple of Docker containers running Oracle Database Xe and Oracle REST Data Services.

I will assume you are familiar with using Docker.  If not checkout this getting started guide.

Required Downloads

Oracle docker-images repository

Oracle provides a GitHub repository with a log of great examples.  Clone repository into the directory where you want to work.

Oracle Xe

You can find more information about Oracle Xe at https://www.oracle.com/rest.

Download the installer and place it in the docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 directory.

I’ll be using oracle-database-xe-18c-1.0-1.x86_64.rpm .

Oracle Rest Data Services (ORDS)

You can find more information about ORDS at https://www.oracle.com/rest.

Download the installer and place it in the docker-images/OracleRestDataServices/dockerfiles directory.

I’ll be using ords-19.1.0.092.1545.zip .

ORDS runs in Java, so you’ll need a Java JRE.  Download the Server JRE and place it in the docker-images/OracleJava/java-8 directory.

I’ll be using server-jre-8u211-linux-x64.tar.gz .

Docker Network

We’ll be running the database and ORDS in separate docker containers.  Define a docker network that they will use to communicate.

Oracle Database Xe

Change into the docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 directory.  Make sure the Oracle Xe install file (oracle-database-xe-18c-1.0-1.x86_64.rpm)  is in this directory.

This directory contains a Dockerfile.xe ready for us to use.  Take a minute and read through the file to see what it is going to do.

Build the Oracle Database Docker Image

The repository includes a shell script (buildDockerImage.sh) that you can use to build the docker image for Xe and other Oracle Databases.

It can be found in the docker-images/OracleDatabase/SingleInstance/dockerfiles directory.  Instructions for using this script can be found here.

I won’t be using the shell script.

The shell script will generate and run something similar to the following command that I will use:
*Notice the dot at the end, this is not a typo.

You can find an explanation of the Docker Build parameters here.

If this command doesn’t work for you, try using the shell script.

Verify that your image was created:

You should see something like this:

Once you have created the docker image it’s time to

Create a Docker Container

The following command is similar to the command you’ll find on the GitHub repository, but I’ve added a ‘-d’ to run the container in detached mode and I’m not defining any data volumes so my data will not persist when I delete the container.

You can find more information on the docker run command parameters here.

This will take a few minutes.

You can watch the progress and troubleshoot any issues with with this command:
(You can use the container name or id.)

When the last line of the log looks like this, you are ready to proceed.

Verify that your container was created and is up:

You should see something like this:

Now try to connect to your new Oracle Xe Database.

To connect with SQLcl:

Oracle REST Data Services

Before we create the ORDS container, we need to create a Docker image with Oracle Linux 7 and a Java JRE installed.  We will use this as a base image to build on top of.

Oracle Linux 7 and Java JRE Base Image

Change into the docker-images/OracleJava/java-8 directory.  In there you will find a Dockerfile and the server-jre-8uXXX-linux-x64.tar.gz file you downloaded earlier.

Take a minute and read through the Dockerfile to see what it is going to do.

Run the following to build the image:
*Notice the dot at the end, this is not a typo.

We do not need to create a Docker container from this image.  We will only need the image as a base for the ORDS image.

Verify that your image was created:

You should see something like this:

Now we can create the ORDS image.

ORDS

Change into the docker-images/OracleRestDataServices/dockerfiles directory.  In there you will find a Dockerfile and the ords-19.1.0.092.1545.zip file you downloaded earlier.

Take a minute and read through the Dockerfile to see what it is going to do.

Pre-Checks

Verify that the docker network we created earlier is ready:

You should see something like this.

Make sure your Xe database is running with the docker ps  command above.

If the Xe database container is not started, start it with the following command:

Build the ORDS Docker Image

The repository includes a shell script (buildDockerImage.sh) that you can use to build the ORDS docker image.

It can be found in the docker-images/OracleRestDataServices/dockerfiles directory.  Instructions for using this script can be found here.

If you choose to run the shell script instead of the below command, and there is not a Checksum file included for the version of ORDS you are installing, use the -i 1 command line argument to ignore the check.

The shell script will generate and run something similar to the following:
*Notice the dot at the end, this is not a typo.

Verify that your image was created:

You should see something like this:

Now we can create the ORDS container.

Create an ORDS Docker Container

The following command is similar to the command you’ll find on the GitHub repository, but I’ve added a ‘-d’ to run the container in detached mode and I’m not defining any data volumes so my data will not persist when I delete the container.

Make sure that you set  -e ORACLE_HOST=oracleXe to the name you used for the Oracle Xe Database container you created above.  ORDS will not find your database if you use localhost.  The containers are basically separate servers running on the same network --network=OrdsXeNet .

The parameter -e ORACLE_PWD="Password1_One" is the SYS/SYSTEM password you used when you created the Oracle Xe Database above.

The parameter -e ORDS_PWD="ORDS_Password1"  will be used as the ORDS admin password.  It does not need to be the same as the database password.

Verify that your container was created and is up:

You should see something like this:

You should now have an Oracle Xe database up and running in one container and an ORDS instance running in the other.

Connect to your database and test that ORDS is properly installed and configured.  A quick way to tell using Oracle SQL Developer is:

  1. Create a new database user.
  2. Connect as that user.
  3. Right click on the connection.
  4. Look towards the bottom of the menu for “REST Services”.

Have fun creating your new REST modules!

How to build an Oracle Database Developer Toolbox with Vagrant

I like to experiment with a lot of different things and of course, a significant number of my projects require an Oracle database and Oracle Rest Data Services (ORDS).

I often use a VirtualBox VM for my project in order to keep it’s environment “clean”.

This guide will walk through the steps to set up a VM with Oracle Xe and ORDS installed and ready to go.

Vagrant

Vagrant automates the creation of a new VM from scratch.  I can use a Vagrantfile and some shell scripts to spin up a VM quickly, saving me time and hard drive space compared to keeping a bunch of VMs laying around that I would need to maintain.  You can learn more on the Introduction to Vagrant page.

Prerequisites

Before we get started, install VirtualBox and Vagrant.  After you’ve finished, download the following.

Downloads

Versions

As of the date that I’m writing this post, I’m using the following versions of the above downloads.  This walkthrough should work if you use these versions, if you use different versions you may have to make adjustments.

  • Oracle Database 18c Express Edition 18.4.0.0.0 (18c)
  • Java JRE – jre-8u201-linux-x64.rpm
  • ORDS – ords-18.4.0.354.1002.zip
  • https://github.com/oracle/vagrant-boxes – Latest commit eb4983d on Dec 7, 2018
Working in the host and virtual machine environments

Some of the following steps will be run inside the VM others in the Host environment.  Check the title of the code blocks to make sure you’re in the correct environment.

To open an SSH connection to your new VM run the following command in the project directory created in the next step.

Create the Toolbox

Make a copy of the 18.4.0-XE directory.

When you are working in the virtual machine, the /vagrant/ directory will be mapped to the dbDevToolbox directory on the host machine.

Edit dbDevToolbox/Vagrantfile

Change the name value

Open port 8080 for ORDS to use.

Create the VM and install Oracle Database XE

Run the following command to start the vagrant build process.

This may take a few minutes to run.  When it’s finished it will generate and display the system password, you’ll want to remember it.

At this point you have a new virtual machine with Oracle Xe installed and running.

Using your preferred SQL tool, test a database connection. (localhost:1521/xepdb1)

Optional: If you would prefer to set your own password, enter the following commands in a shell.  Replace ‘newPassword’ with the password you want to use.

Install the Jave Runtime Environment (JRE)
  • Switch to the root user.
  • Use the .rpm file to install java.
  • Check the java version.
  • Exit the root user.

You should see the following output.

java version “1.8.0_201”
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)

Create the file dbDevToolbox/ords_params.properties

This will be used to configure ORDS.  Feel free to modify the settings for your environment.  If you change the port, remember to also change it in the Vagrantfile in the above step.

For an explanation of these and other settings, you can find the documentation here.

Install ORDS
  • Switch to the oracle user.
  • Unzip ORDS into the Oracle product directory.
  • Copy the above properties file into the ORDS directory.
  • Set the LD_LIBRARY_PATH environment variable.  This is needed in order to install ORDS with a bequeath connection.
  • Run a silent installation of ORDS using the above parameter file.
  • Exit the oracle user.

Autorun ORDS on boot

Create file dbDevToolbox/oracle-ords-18-4 using the following example.

If you decide to write your own, make sure ORDS does not attempt to start until after the database is fully running.  For example, in the below file I included ‘# Required-Start: oracle-xe-18c’.

  • Switch to root user.
  • Copy the above file to /etc/init.d/
  • Set the file permissions.
  • Add the configuration.
  • Set the configuration run levels.
  • Start ORDS.
  • Exit the root user.

Test

At this point, your VM should have both Oracle Database XE and ORDS installed and running.  If you’re already familiar with ORDS you should create a test module to make sure everything is working.

If you’re new to ORDS you can create the file dbDevToolbox/setupHr.sql.  This script will enable the HR user and create an ORDS module.

  • Switch to the oracle user.
  • Use SQL*Plus to run the above script.  Replace <YourPassword> with the sys password from the beginning of the post.
  • Exit the oracle user.
  • Use curl to test the REST service.

You should see the following output.

Automate

Creating a new VM with Oracle XE installed is already fully automated.  Now, I’m going to show you how to roll the ORDS steps into the Vagrantfile.

The below file collects all of the above steps into a single shell script.

Create the file dbDevToolbox/scripts/installOrds.sh with the following.

Edit dbDevToolbox/Vagrantfile

Provision the new script

Destroy the current VM and create a new one.

WARNING:  This will completely destroy the VM we created above and create a new one from scratch.  If you have made any changes you’d like to keep, make sure to back them up first.

  • Destroy the VM.
  • Confirm that you want to destroy the VM.
  • Create a new VM.
Test the new machine.

Future project virtual machines

For future projects you’ll only need to:

  • Make a copy of the dbDevToolbox directory with a new name.
  • Edit the Vagrantfile and modify the NAME entry.
  • Run the vagrant up command.

When you’re finished with the project use vagrant destroy to clean up.

Resources

How to secure your ORDS service

Securing your Oracle Rest Data Services module only takes a couple of minutes.

As you can see in the video, this will be a fairly short how-to.

Assumptions

For this post, I’m going to assume:

  • You’re able to use SQL Developer.
  • You already have an ORDS module created that you want to secure.
  • You have at least a basic understanding of Roles, Privileges, Authentication and Authorization.

Test the Existing Service

Before we begin, I’ll test the current service with a curl command.

This returns a response with a status of “HTTP/1.1 200 OK” and a JSON object with an array of items.

 

Secure Your Service

First up I will create a Role and a Privilege that I’ll use to secure my service.

Create a Role
  1. Expand the schema connection and REST Data Services, if they are not already expanded.
  2. Right click on Roles.
  3. Click New Role…

  1. Enter a name for the role.  I’m using bc-role.
  2. Click Apply.

Create a Privilege
  1. Expand the schema connection and REST Data Services, if they are not already expanded.
  2. Right click on Privileges.
  3. Click New Privilege…

  1. Enter a name for the privilege.  I’m using bc-priv.
  2. Select the new role ‘bc-role’.
  3. Select the ORDS Module.  I’m using my Beacon module.
  4. Click Apply.

Be aware, this will only add security to the specific modules you select in the bottom section.  If you were to add a new module later and you want it to be covered with this privilege, you need to remember to edit this privilege and select it in the ‘Protect Modules’ selector.

Alternatively, you can use the ‘Protect Resources’ tab to apply security based on a URI pattern.

For my examples, I’m working from a VirtualBox VM and I have used ‘beacon’ as my schema alias.  So the base for all of my REST services in this schema would be

I entered the URI pattern

This will protect all ORDS services for this schema.  If I wanted to use this method to protect just an admin URI template in my beacon module (the full URI is https://localhost:8080/ords/beacon/beacon/admin), I would use a URI pattern like this

There are many more complex patterns you could use for your services, but for this post, I am only protecting the Beacon module so I am using the first example.

Test
This now returns a response with a status of “HTTP/1.1 401 Unauthorized”.
 

Access Your Service

I want to set up my ORDS module so it can be accessed by a third party application so I’ll set up OAuth 2.

Create an OAuth 2 Client

Using the OAUTH PL/SQL Package, I will create an OAuth2 client using the privilege I created above.

Then I will grant the new OAUTH2 client the role I created above.

Test

In order to generate an access token, I need to get my client id and secret from the user_ords_clients table.

When you create an OAuth2 client, a REST service ‘/oauth/token’ is created for you that your applications can use to generate a bearer token using the above id and secret.

Notice that this token expires in 3600 seconds.  Once it expires, you would repeat the above call to generate a new token.

Now that I have an access_token I can include it in the Authorization header to access my secure ORDS service.  Notice that since this is a Bearer token I set the Authorization value to ‘Bearer <<my token>>’.

Example Usage

The following is an example of how to make secure REST calls from an external application after you’ve followed the above steps.

On the application server

Store the client_id and client_secret in environment variables.

In your application

Summary

Setting up security on your ORDS modules is quick and fairly easy.

OAuth2 is just one way to access your secured ORDS modules.  If you’d like to setup Basic Auth checkout this post by Jeff Smith.

Console the ORDS documentation for other examples and a much more in-depth explanation.

My ODTUG GeekAThon 2017 Entry

The rules and other information can be found at ODTUG GeekAThon 2017.

Problem

My son Alex attends a school where the students have some ‘bonus features’, or as the school puts it: “Educating Exceptional People”.  There are some students at Alex’s school who sometimes try to wander away.  Obviously, this could be a problem but the school staff is extremely well trained and they keep a close watchful eye on all of the students.  Still, I’d like to try and make their lives a little easier, and the students a little safer.

There are commercial systems available that could notify the administration and/or lock doors when a beacon worn by a student is detected in a hazardous zone, such as leaving the school.  That sounds perfect. There’s just one problem: those systems can be very expensive.

Proposal

Implement a student tracking and door lock automation system that can operate on inexpensive components and open source the software.  I will set up a test environment at my house and my son will test it with me.

Desired Features

  • Central to the whole system is a way to detect a beacon when it enters a specific area such as near an exit door or a faculty-only area.
  • Ability to send notifications.
  • Ability to trigger a physical event such as a door lock or audible alert.
  • Log beacon detection events in a database.
    • Beacon Id.
    • Distance from the scanner.
    • Timestamp.
  • Affordable components.

Initial Idea

After browsing the web for a while I decided I would set up multiple scanners with overlapping zones then use trilateration (I like saying that word) to determine the position of the beacon.

I would set up multiple scanners, measure the distances between them and plug that data into my database.  When a scanner detects a beacon it would use my ORDS service to POST its own id, the beacon id and the calculated distance to the beacon.  On the database, I would use Oracle Spatial queries to determine the location of the beacon.  Finally, I would compare the beacon location to defined zones in my house and trigger the alerts/actions for the zones.

I have a tendency to over-engineer my projects.  I once built a doghouse that weighed close to 200 lbs.  (It was awesome.)

After getting most of this working, I realized that I could achieve the project goals by simply placing a single scanner near each zone and let that scanner initiate the alert actions for its zone.  Sometimes less is more.

Hardware

I already had a bunch of Raspberry Pi so I decided to use a couple of my Pi 3s.  Since I’m always looking for an excuse to buy more toys, I decided to get a Pi Zero W.

I have a z-wave enabled deadbolt and a Z-Stick USB hub that I can control using Home-Assistant.io.  For the audio notification, I’ll push a ‘text to speech’ action to my Sonos speaker.  I can make the Sonos say anything I want, this entertains me a lot, my family… not so much.

Software

  • Raspbian Linux
    • Linux modules
      • bluetooth
      • bluez
      • libbluetooth-dev
      • libudev-dev
  • NodeJS
    • NodeJS Modules
      • bleacon
      • request
  • IFTTT.com
  • Home-Assistant.io
  • Oracle Database
  • Oracle Rest Data Services (ORDS)

The installation instructions are in the GitHub repo.

Database

The beacons are set to transmit every two seconds and can be detected by multiple sensors.  I always like to keep track of my data so of course, I’m pushing it to a database.  I’m using an Oracle Cloud Database with an ORDS (Oracle Rest Data Services) front end to collect the data.  When a Raspberry Pi detects a beacon, it will calculate the distance then POST the data to the database.  The database will automatically record a time-stamp when the record is inserted.

This is included in the current code and it’s what I need to collect the data for the “Initial Idea” section above.

If I decide to implement the feature to track the beacon’s position throughout my house.  I just need to determine the fixed position of each scanner relative to a point in my house and using the data I’m already collecting, run an Oracle Spatial query that defines a circle from each scanner with a radius of the distance to the beacon.  Where the circles overlap is the approximate location of the beacon.  The official term (linked above) is Trilateration, but you can think of it as a Venn Diagram.

How I Deployed the System

If you’d rather, you can watch the video and skip this section.

I configured and positioned three Raspberry Pi through my house.  I put a Pi 3 in the hallway outside of the bedrooms, a Pi 3 near the front door and a Pi Zero W outside on the front porch.

  • The first Raspberry Pi 3 in the hallway is set to trigger an alert when the beacon is approximately 2 meters away. This alert will send a notification through IFTTT* to the app on my phone.
  • The Raspberry Pi 3 near the front door fires an alert when the beacon is approximately 1 meter away.
    This alert has three actions:

    • Send the ‘lock’ command to the deadbolt through the REST interface of Home-Assistant.io using Z-Wave.  Home-Assistant.io and the Z-Wave USB dongle are also installed on this Pi.
    • Set the Sonos volume to max and send ‘Locking the front door’ to the Sonos speaker using the text to speech function in Home-Assistant.io.
    • Send a notification through IFTTT to my cell phone.
  • The Raspberry Pi Zero W outside near the front door will trigger an alert when the beacon is approximately 1 meter away.
    This alert has three actions:

    • Send the ‘unlock’ command to the deadbolt through the REST interface of Home-Assistant.io using Z-Wave.
      (If Alex makes it outside, I want the door unlocked so he can come back in.)
    • Set the Sonos volume to max and send ‘Unlocking the front door’ to the Sonos speaker using the text to speech function in Home-Assistant.io.
    • Send a notification through IFTTT to my cell phone.

*IFTTT can also send a text message but the free tier only allows a limited number of texts to be sent each month. I chose to use notifications through their Android app since they are unlimited and I would have burned through the text quota the first time I forgot to limit how often I send a notification. In a live situation, it could send out multiple texts.

Challenges

I had intended to use OpenHab for the home automation features of the project, but when I built the project there was a bug in the Z-Wave addon that made interacting with the deadbolt more difficult.  I tried out Home-Assistant.io and so far I really like it.  Each application has its own strengths and weaknesses, but they both run on a Raspberry Pi so I may use both for future projects.  I’d like to mention they are both open source which is an added bonus.

The beacon distance tracking is not as accurate as I hoped, but it’s fine for this project.  The signal can be degraded by walls, bodies or other objects being between the beacon and scanner.  To improve the accuracy, I implemented a weighted rolling average function as part of the distance calculation to smooth out some of the spikes.  Deploying more scanners would also greatly improve the accuracy if I implement the position tracking.

Future Improvements

  • Add an Oracle JET front end for configuration and control of the system.
  • Add a map display that can show the beacons live.
  • Change the Alert/Action code to be more generic and provide a mechanism to define them in the front end.
  • Find a small inexpensive wearable BLE beacon or design one with a small rechargeable battery and a 3D printed enclosure.

Final Thoughts

If you decided not to participate in the GeekAThon this year please join in next year.  It is a great way to learn some new skills and have fun at the same time.  I am sure parts of what I described above sound intimidating. But if you’d like to try your hand at this or similar projects, don’t hesitate to contact me for help. And while I can’t speak for the other GeekAThon participants, this year or past years, I am certain they will be eager to help you, too.

This project has been a lot of fun, I learned a lot.  I’m looking forward to next year!

Getting started with Oracle Rest Data Services

Most applications today store data of some type, most likely that data is stored in a database.  There are many ways to get data from the application to the database and back, but one of the most popular methods is using RESTful services.  If you’re not familiar with REST think of it as an easy way to let 2 computers talk to each other.  For a more detailed explanation check out this Wikipedia page.

If you are familiar with REST you’re probably used to standing up a server and building a server side application that connects to your database and provides a REST API.

Oracle provides a simpler solution called Oracle REST Data Services or ORDS for short.  ORDS is a quick way to build a REST API directly to your database.  If you’d like a more thorough explanation, check out the ORDS site.

A Short Tutorial

Setup a VM

I’ll be using the Developer Days vm on Virtual Box for the tutorial.  This vm has the Oracle 12c Database and ORDS already installed and ready to go.

  1. Download the Database App Development VM.  I’m using the one from June 13, 2017.
  2. Create a new appliance and start it.
  3. Inside the appliance, open a terminal and enter the following commands.  Provide a password when prompted.
Now we have the VM running and we’ve created an ORDS user “ords_dev”.

SQL Developer

For these examples, I’ll be using SQL Developer version 4.2.0.

If you don’t already have SQL Developer installed you can download it here.

Connect to the HR schema

Open SQL Developer and create a connection to the HR schema.

  • Connection Name:  Anything you’d like.  I’m using Hr – VM
  • Username: hr
  • Password: oracle
  • Hostname: localhost
  • Port: 1521
  • Service name: oracle
    (Make sure you select the Service name radio button.)

Test the connection and connect.

Rest Enable The Schema
  1. Right click on the HR connection.
  2. Click REST Services.
  3. Click Enable REST Services…

  • Enable schema: checked
  • Schema alias: personnel
    (Remember this for later.)
  • Authorization required: un-checked
    For production applications, you will want to use authorization but I’m not going to cover it here.

You can click Finish or if you’d like to see the summary page you can click Next then Finish.

REST Data Services Wizard

From here SQL Developer offers a couple different ways to run the REST Data Services wizard.

One way you can work with the wizard is through the database connection.

This method does not require you to have an ORDS user, but the full ORDS URI won’t be automatically provided in the wizard so you’ll need to get that from the ORDS admin.  I’ll cover the URI below.

For this tutorial, I’ll be using the…

REST Development Panel
  1. Click the View menu item.
  2. Click REST Data Services.
  3. Click Development.

The REST Development panel (on the right) should now be in the left panel bar.

         

Connect to ORDS
  1. Click the Connect icon.
  2. Create a new connection.
  3. Populate the ORDS connection data.

This is an ORDS connection using the ORDS user we created in the VM earlier NOT the HR schema user.

Connection Name: HR-VM
Username: ords_dev
(The username is case sensitive.)
Select: http
Hostname: localhost
Port: 8080
Server Path: /ords
Schema/Workspace: /personnel
(If you used a different value when you rest enabled the schema use that value here ‘/your_alias’)

  1. Click OK in the New RESTful Services Connection panel.
  2. Select your new connection and click OK.
  3. Enter the password we created earlier: oracle
  4. Click OK.

New Module

A module is a collection of related REST services.  How the services are related is up to your imagination.  I usually think of a module like a package and the services as functions inside the package.

To create a new module:

  1. Right click on Modules.
  2. Click New Module…

The wizard will open and we can populate the data.  The purpose of my module is to manage the personnel so I’m going to name my module Manage.

Module Name: Manage
URI Prefix: manage
Check the Publish check box.

Notice that when you enter the URI Prefix the Example URI is expanded to include that value.  This is the URI I mentioned above.  If you run the wizard through the database connection the URI will include a generic value for the first part that refers to the ORDS server.  (http://localhost:8080/ords/personnel/)

Click Next.

Template URI

The template URI identifies a specific REST service endpoint.  In this case employees.  Notice that when you enter the URI Pattern the Example URI is expanded to also include that value.

Let’s break apart the URI.  First, we have the schema alias ‘personnel’ that gives us access to the HR schema.  Next, we created a module to ‘manage’ the HR schema records. Finally, we created a specific URI to handle transactions for ’employees’.

Method Handlers

Now that we’ve created the service endpoint to work with employees, we need to ‘Handle’ the different HTTP ‘Methods’ we intend to use.

A quick web search for ‘http rest methods’ will return pages of discussions on the available methods and how to “properly use them” but the short version is:

GET: Retrieve records with or without search criteria.
POST: Create records without providing the primary key.
PUT: Replace a record with a given primary key.  This can also be used to create a record if you’ve pre-assigned it a primary key.
DELETE: Remove a record with a given primary key.

We’ll start by creating a simple GET all handler.

  • Method: GET
  • Source Type: Query
  • Data Format: JSON
  • Pagination Size: 25
    We’ll leave this at the default value of 25.  It’s a good idea to define a pagination size, we don’t want to accidentally return a billion records in one call.  More on this later.

Click Next, review the summary and click Finish.

Get Query

Our GET method will return the Employee id, Hire Date, First and Last name for all employees.

If the GET employees SQL Worksheet did not automatically open, expand Manage, employees and click on GET.

Enter this query into the SQL Worksheet.

Push the new module to ORDS
  1. Right click on the Manage module.
  2. Click on Upload.

Post

To create new records we’ll want a handler for the POST method.

  1. Right click the employees URI template.
  2. Click Add Handler.
  3. Click Post.

Notice that GET is grayed out since you can only have one method handler of each type per URI template.

We use the MIME Types to define the data format that we’ll accept.  Click the green plus to add a new MIME Type and enter application/json.  Click Apply.

If the POST employees SQL Worksheet did not automatically open, expand Manage, employees and click on POST.

ORDS uses PL/SQL for methods that change data, POST, PUT and DELETE.  PL/SQL gives us a greater amount of control which in turn provides better security.

Enter this PL/SQL into the SQL Worksheet.

Notice the use of bind variables in the PL/SQL.  If the data keys coming into our REST service match our bind variables, ORDS will auto-map the values.  However, if the keys do not match or we have additional use cases, we will need to map the bind variables using the Parameters tab. For this service, we will be passing in data values with keys that match the bind variables.

Since we are creating a new record and the primary key is auto-generated, it will be useful to the end user if we return the new id.  Above, we’ve defined a new bind variable :newid to pass this value back.  There is also another bind variable :status that we’ll use to change the response status from 200 (success) to 201 (success and I created a new record).

Parameters

Click on the Parameters tab and enter the following values.

Colum definitions:

  • Name – Used by ORDS.
    • newid will be the key in the JSON object that returns the id to the user.
    • X-APEX-STATUS-CODE is a built in ORDS parameter used to set the status of the response object.
  • Bind Parameter – The bind variable used in our PL/SQL.
  • Access Method – Defines the direction in the transaction we intend to use the parameters; IN, OUT or IN/OUT.
  • Source Type is where the parameter will be used.
    • newid will be in the response body.
    • X-APEX-STATUS-CODE will be in the response header.
  • Data Type – Data type for the returned value.  When all else fails, choose STRING.
Push the modified module to ORDS
  1. Right click on the Manage module.
  2. Click on Upload.

At this point, we have created and deployed a fully functional REST API with the ability to GET all employees and POST a new employee.

It’s time to….

Test the Service

Switch to the Details tab for either the GET or the POST method handler.  At the bottom, you can copy the URI for the new REST service.

URI: http://localhost:8080/ords/personnel/manage/employees

GET

To test the GET method you could simply enter the URI into a web browser and it will return the records.  Using my test tool, I enter the URI and hit send.

I receive back a JSON object with an “items” array that has 25 employee entries in it.  Below, I’ve trimmed a few out of the middle to keep it short.

Remember, I set the Pagination Size to 25 in the GET method, so ORDS returns the first 25 records.  Notice at the bottom of the JSON object after the array there is a “first” object.  The “$ref” value will take you to the first page of records.  This is automatically added to the response by ORDS when pagination is enabled.

There is also a “next” object added by ORDS to indicate that there are more records on the server.  When you write your client side application, you would process the returned records and check to see if there is a “next” object.  If there is, you could use URI in the “$ref” object to fetch the next set of records.  You would loop through this process until the last set of records.  When you reach the last set there will not be a “next” object.

After the first page, you would start to see a “prev” object containing a “$ref” object that you can use to reverse through the records.

If you set Pagination Size to 0 the service will return every record at once and the navigation objects will not be included.

POST

In your REST testing tool:

  • Change the method to POST.
  • Add a header.
    Content-Type: application/json
  • Enter the following as the payload.
  • Send the request.

You should receive a response with a status of “201 Created” and the response body should contain the newly generated id.

Our service is deployed and the tests return the data we expect.

The wizards are a great way to quickly define REST services for your database, but you won’t want to use them when you deploy your application.  Instead, we can…

Export SQL

For mass deployment (or for people who just prefer to type everything) a SQL script is a better option.

Another difference between the REST Development panel and REST Data Services in the database connection is that you can export the SQL using the database connection tool.

Open the HR database connection and expand the REST Data Services item.  If you do not see your new service, click on the REST Data Services item and click the refresh arrows at the top of the panel.

  1. Expand Modules.
  2. Right click on Manage.
  3. Select Export…

In the window that pops up:

  1. Check the Enable Schema check box if you want to include the statement.
  2. Un-Check Privileges.
  3. Enter a filename and location.
  4. Click Apply.
  5. Open the file.

You can now include this SQL script in your application build process to deploy the REST services right alongside the rest of your database objects.

When you need a REST API to work with your database, ORDS and the SQL Developer wizards will save you a ton of time and help you create very robust and elegant solutions.

Please leave me a comment if you have trouble or find any bugs.