Category Archives: NodeJS

Deploy NodeJS & Python3 applications on an Oracle Cloud Compute instance

DinoDate

DinoDate currently has both Python and NodeJS mid-tier applications and is backed by an Oracle Database.

The following instructions show how to deploy DinoDate to an Oracle Cloud Compute instance.  However, if you just need to deploy a NodeJS or Python application, the same instructions should help you install Node and/or Python 3.

If you don’t have access to an Oracle Database you can try the Oracle Cloud for free.

Database

Download/Clone DinoDate to get the database scripts you’ll need.

Create an Oracle Cloud database.

Connect to your database as sys with sysdba and run coreDatabase/dd_master_install.sql.  (Use your password and connect string)

Compute

Create an Oracle Cloud Compute instance.

Open the ports for our NodeJS and Python apps.

Download and scp the following to your new compute instance.  (Current versions as of the time of this post.)

Open an ssh connection to your compute instance.  (Use your ssh key and the public IP address for your compute instance)

  • Switch to su
  • Update your instance
  • Install some tools we’ll need

Install both Oracle Instant Client files

Install NodeJS 8

  • Install some tools we’ll need
  • Enable the config manager
  • Install Python 3.5
  • Enable Python 3.5
  • Upgrade pip
  • Install the python modules for DinoDate
    • cx_Oracle
    • bottle
  • Exit scl bash:

Exit su

Add the following to your .bash_profile:

  • Create the environment variables  for DinoDate (use the JDBC connect string for your database)
  • Enable Python 3.5.
Re-run .bash_profile
  • Clone DinoDate to your Compute instance
  • Extract the Oracle JET files
  • Run bower install
  • Install the NodeJS modules
  • Use pm2 to start the NodeJS version of DinoDate
    The –watch parameter will restart the application if the files change.
  • (We already installed the Python modules above)
  • Use pm2 to start the NodeJS version of DinoDate
    The –watch parameter will restart the application if the files change.
‘pm2 startup’ will generate the command needed to restart our applications on boot.  The following will extract and execute the command from the generated text.

Try it out

Open a browser and pull up DinoDate:

  • NodeJS
    http://YourComputePublicIP:3000
  • Python
    http://YourComputePublicIP:8088

You can log in with any of the existing users, such as:

  • Bob
    bob@example.com
  • Admin
    admin@example.com

Use any value for the password, the application doesn’t check it.

Click on the Search tab and search for ‘eat’ it should return 6 of the pre-loaded dinosaurs.

If you run into any trouble, leave a comment and I’ll be happy to help.

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 DinoDate Up and Running

You’ve downloaded DinoDate, the premier dating website for dinosaurs. Now lets get it up and running!

Prerequisites

  • Download Logger and extract it to dino-date/coreDatabase
  • Check Logger’s create_user.sql file. If you see an exit statement at the very end , remove it.
  • Install Bower.
  • Install Oracle Instant Client with the sdk modules.

Installation

Database Schemas and Objects
IMPORTANT – Please make sure you’re using a database instance in which you can safely create  schemas named DD, DD_NON_EBR and DD_LOGGER.
  1. Navigate to dino-date/coreDatabase
  2. Run dd_master_install.sql from an account connected “as sysdba”.
  3. First prompt is for the directory into which you extracted Logger, e.g. Logger_3.1.1
  4. Second prompt is what you want to name the schema that holds the Logger database objects.
  5. Third and fourth prompts are for your Tablespace and Temporary Tablespace.
  6. Fourth prompt is for the logger schema password
Common Client

Open a prompt and navigate to dino-date/commonClient

RESTful Tier

Currently there are RESTful APIs written in both NodeJS and Python.  You can choose to run one or both.

NodeJS

Open a prompt and navigate to dino-date/nodejs

Python

Open a prompt and navigate to dino-date/python

Configure your Environment

DinoDate uses environment variables for database connection and port settings.

Create the following environment variables using the correct values for your system.

  • dd_connectString=localhost:1521/orcl
  • dd_user=dd
  • dd_password=dd
  • dd_port=8888
  • dd_python_port=8080
  • dd_node_port=3000

Run DinoDate

NodeJS

Open a prompt and navigate to dino-date/nodejs

DinoDate (NodeJS) will be listening on the dd_node_port port you defined above.

Python

Open a prompt and navigate to dino-date/python

DinoDate (Python) will be listening on the dd_python_port port you defined above.

Running Both

You can run both versions at the same time, provided you specified different ports.

This will allow you to switch between languages by changing the ports and view the code examples specifically for each language.

Please leave a comment if you run into trouble.

WORKAROUND FOR DATABASE BIND ISSUES

Update: Thanks to Chris Jones for letting me know there has been an update to node-oracledb that handles the original problem.

I had originally written this post in November 2015, back then everything ‘didn’t work’ the way I had described it. But the node-oracledb project is very active and shortly after that, they added a feature which allows you to bind out to single value arrays. If you have tried my example code since then you would have noticed that the problem I described actually does work if you follow the comment from Chris Jones.

The point of the post is to demonstrate ways to work around problems that may come up when binding to PL/SQL programs. It could be bind issues like below, or maybe you have a very complex query in your code and you want to use PL/SQL to handle the complex part but still use it as a query.

I’ve updated this post to use a different example that will throw the same error. If/when my new example becomes obsolete due to new features in node-oracledb, just pretend the problem is real and try the solutions.

Using node-oracledb version: 1.9.3

I’ve been working on DinoDate, a demonstration application that shows how to implement the same functionality in different languages, all on top of an Oracle Database foundation. I had the Python code working and it was time to work with JavaScript and NodeJS, using the node-oracledb driver for the first time.

How I learn

When I’m learning something new, I prefer learning from code examples over reading documentation.  I pick up little extra nuggets of knowledge this way since I tend to break things in the examples, which I then have to sort out and fix.

However, I will only bang my head against the wall so many times before I read the docs. At last count, that was 57 times.

The Problem

One of the application examples demonstrates how to use a PL/SQL stored procedure to perform some different searches.  The procedure uses OUT parameters to pass the data back.

Here’s the PL/SQL code (defined in a package):

In Node, I attempted to bind to the out parameters like this:

This had worked several times before in other functions when I returned a single value from an out parameter.  This time, I couldn’t find a single node-oracledb type that would work. Most of them resulted in this error:

So I read the doc, fired up my trusty old search engine and searched, and then did what I should have done right from the start: lean on another member of my Oracle Developer Advocates team:  Dan McGhan.  Who immediately told me “The driver only support scalar types right now.”  Update: The driver also supports arrays of a single string or number.

When you learn by experimenting on code rather than reading the docs, sometimes you miss out on the ‘real words.’  I assumed ‘scalar types’ means a variable that holds a single value opposed to an array.  Just to be sure, I found a decent explanation on Stack Overflow.

Well, game over, right?

Ha! The game is only over when you quit. It was time to explore some options.

Change the PL/SQL procedure to a function
Return the results using JSON.

I considered running the query, building a JSON object from the results and returning them as a string in a clob.

But this doesn’t fit with the way the other application functions work. The database calls return unformatted results which are returned to the API functions and then those functions convert the results to JSON (or other formats if required) and sent back in the response.

Use a ref cursor

This could also be a viable solution and here’s a good example of how to use a ref cursor.

One goal of this demonstration application is that all functionality will be reproduced in multiple languages; Python, Ruby, PHP and others. I am trying to keep the functions as simple as possible. Other languages may not be able to work with a ref cursor.

Pipelined function

I decided to use a pipelined table function.  Table functions are very cool. In combination with the TABLE operator, I can return a collection to the FROM clause of a SELECT, and then have it treated as if it were a table, full of rows and columns. I can apply all the usual SQL SELECT operations on this dataset. Plus, the results are returned unformatted, so a developer should be able to use just about any language  to run a simple query built around this feature.

PL/SQL:

JavaScript:

This works great but what if you can’t change the PL/SQL procedure to a function because it’s used elsewhere?

Add a PL/SQL wrapper function
Now call text_only_wrapper in the JavaScript function above.

More options

Of course, there are other ways to work around the original issue and I’m sure there are better ones than what I’ve chosen.  If you have a better solution, please share it.

Share

Speaking of sharing.  The node-oracledb project is on GitHub under the Apache License, Version 2.0.  If you are able to add support for compound types such as arrays, please submit a pull request.