All posts by bcarter

Execute PL/SQL calls with Python and cx_Oracle

After you’ve got the hang of performing Basic CRUD operations with cx_Oracle you’re ready to start tapping into some of the real power of the Oracle Database.

Why use PL/SQL?

Python is an excellent language for most things you want your application to do, but when you’re processing data it just goes faster if you do the work where the data is.

This post will cover how to execute Oracle PL/SQL functions and procedures using Python and cx_Oracle.  I’m assuming you’re already familiar with PL/SQL if not, you can get some help from Steven Feuerstein and Bryn Llewellyn.  (Additional resources at the end.)

Prerequisites

  • Python 3
  • Oracle Database version 12+
  • Basic Oracle PL/SQL and SQL knowledge.

Setup

If you’d like to follow along with the examples you’ll need to create the following objects in a database schema that is safe to experiment in.  Make sure you have permissions to create the following objects.

To keep everything clean, I’ll be putting my PL/SQL code into a package called pet_manager.

Cleanup

To clean up the database when you are finished with the series, you need to drop the two tables and the package.  Please make sure you’re connected to the correct schema where you created the tables.

Boilerplate template

The template we will be using is:

  1. Install cx_Oracle.
  2. Import the cx_Oracle driver.
  3. Import os module used to read the environment variable.
  4. Get the connection string from the environment variable.
  5. Create the connection object.
  6. Create the cursor object.
I will include this code section with all Python examples and use the connection object “con” and the cursor object “cur” throughout the series.

For each exercise, replace the “# Your code here” line with your code.

Anonymous PL/SQL Block

I’m going to start off with the most basic process and simply execute an anonymous block of PL/SQL code to reset the database tables.

You can execute any DDL or DML statement like this, but if you’re going to run PL/SQL it’s usually best to compile it to the database.

Execute a PL/SQL Procedure

Using the code from the anonymous block I created a procedure in the PL/SQL package called reset_data.

To call this procedure from Python we use the cursor.callproc method and pass in the package.procedure name to execute.

Assuming everything works, there will not be any response.  So this works as a ‘fire and forget’ way to call database procedures.

Pass Parameters

I have a procedure in my PL/SQL package that we can use to create a new pet in the lcs_pets table.  It accepts the pet_name, owner_id and pet_type.  Using these values it will insert a new entry into the lcs_pets table.

Now on the Python side.

I prefer to set my values with variables so that my code is easier to read, so I’ll create and set pet_name, owner_id and pet_type.

Next, I’ll call the cursor.callproc method and add an array containing the values to pass in the order they are defined in the database.

Once again, if everything works there will not be any response.

Get PL/SQL Function Return Values

When a row is added to the lcs_pets table a new id is automatically generated.  Having this id can be useful so I created a function in my PL/SQL package that will create a new pet in the lcs_pets table, just like in the previous function, but it will return the new id.

Using Python to call a function in the database and get the return value I’ll use the cursor.callfunc method.

  1. I set the variables that I’ll use as arguments to the function.
  2. Define a new_pet_id variable and assign it the value returned from callfunc.
  3. The second argument of the callfunc method is used to define the type of the data being returned.  I’ll set it to int.  (cx_Oracle will handle the NUMBER to int conversion.)
  4. I pass in the array of values just like I did when I used callproc.
  5. Print the returned value for new_pet_id.

Out Parameters

Out parameters can be very handy when you need to pass back more than one piece of information.  I have an add_pet function in the PL/SQL package that will check to see if the pet type you’re adding needs a license or not.  The function will return the new id like before, and a ‘yes’ or ‘no’ through the out parameter.

To work with the out parameter in Python I’ll add a string variable called ‘need_license’.  It can be defined using ‘cursor.var(str)‘. Then we just add the new variable to the values array in the correct position.  This works the same when using out parameters with the callproc method.

To get the value from ‘need_license’ we call it’s getvalue() function.

Accept Argument Values

So far I’ve hard-coded the variable values in the Python code and the methods are fairly simple, so there’s a low chance of errors.  But, for most methods, we want to accept parameter values that can be passed into the Python code then on to the PL/SQL functions.  I’ll modify the Python method to accept command line arguments.

We need to import sys so that we can use sys.argv[] to grab the command line arguments and assign them to the variables.

If I run this to add a dog, I get:

Adding a fish, I get:

PL/SQL Exceptions

Now that I’m accepting outside argument values, the odds that I’ll eventually get errors with the above code is almost a certainty.  If an error happens in the Python code you can handle it as you normally would.  But, what if there’s an error thrown by the PL/SQL code?

It’s easy enough to test this.  Make the same call as before but pass in a string for the second value.

I would recommend that you handle errors as close to where they happen as you can.  In this example, you could catch the error in the PL/SQL function and either handle it or raise it.  If you don’t handle it in PL/SQL it will be passed back to cx_Oracle which will throw a cx_Oracle.DatabaseError.  At that point, you can handle it as you would when any other Error is thrown in your Python application.

Additional Resources

CI/CD for Database Developers – Export Database Objects into Version Control

With this post, I kick off a series that walks you through the process of building  database applications using a CI/CD pipeline. I will be covering:

  • Export Database Objects into Version Control (This Post)
  • Use Schema Migration
  • Unit Test PL/SQL
  • Build and Deploy
  • Automate

Links will be added for each topic as that article is published. I will use the DinoDate application (source) as my code base.

For this post, assume that I have DinoDate installed and all development to date  has been done directly in the database (source code not stored externally in files).

Since, however, it’s really not a good idea to edit and maintain code directly in the database, I’d like to switch to storing the DDL for my database schema objects in files, managed by a version control system. Let’s get going!

Export the Database Objects out to Files

I could export the code for my objects out into files by writing queries against  built-in database views like ALL_SOURCE.  I have used this method a few times, and you probably have, too. You can make it work, but it puts the onus on you to get everything right. I’d rather think about other things, so I ask myself: is there a tool that will take care of the heavy lifting on this step?

There sure is, and it’s free:  Oracle SQL Developer has a great export tool. Assuming you’ve got SQL Developer installed and you can connect to your schema, here’s what you do:

Under the Tools menu, click on Database Export.

The first step of the wizard configures the general export attributes:

  1. Choose the connection for the schema you want to export.
  2. Select the options you’d like the wizard to use when creating the export scripts.
    I like to add drop statements to my scripts.  You may also want to export grants.
  3. I want to export some of the master table data so I will leave the export data section set with defaults.
  4. Choose “Save As Separate Directories” and select the export directory.

In step 2 I left all of the default options selected.

I want all of the schema objects so I skip step 3.

I selected the tables that contain the pre-loaded master data for the application.

Everything looks good so click finish.

The export wizard also creates a master run script and opens the script in a new worksheet (I’ll come back to this later):

Now that I have everything exported, I want to get the files into version control asap.

Add the Objects to Version Control

I’ll be using Git for version control.  SQL Developer has some nice integrated tools for working with Git but for now, I’m just going to use the command line.

  1. Change to the export directory.
  2. Initialize a Git repository.
  3. Add all files to the repo.
  4. Commit the changes.
Assuming that you’re using a central Git repository, you should push the new repo up to the shared repo.  That process can differ based on which repository host you’re using so I’ll leave it up to you to consult the documentation.

With my files stored safely in Git, it’s time to

Verify / Clean up

Whenever you use any automated code generate/export tool, you should verify the results.

For example, you may want to change the generated SQL for the dinosaure_id column from

to

After you verify each file, run a code beautifier on it.  Maintaining a standard code format will make it easier to see the differences between changes.  This will make your future code reviews much easier.

Make sure you commit each file as you change it.  Don’t wait till the end to do a massive commit.

Organize

In step 1 of the wizard, I selected Save as Separate Directories so all of my object scripts have been grouped into directories by type, like this:

This is a perfectly fine way to organize your scripts.  However, I like to group mine by whether or not the scripts will be changed and re-run vs run a single time and future changes will be done with new scripts.  For example:

If I were going to run everything from scripts, I would also break down the Run_Once directory into product versions with a subdirectory for ‘create from scratch’ and one for ‘updates’, like this:

In another post, I’ll show you how to use a Schema Migration tool which makes managing schema changes much easier.

Master Build Script

Now that everything is cleaned up and organized, we need to modify the master script that was generated by the export.  It should be named with a timestamp in a format similar to this:

Generated-YYYYMMDDHH24MISS.sql.

My exported script was named Generated-20180706134514.sql.

You will want to edit this file and modify the directory for the scripts to match the changes you made above.  Also, you need to verify that the scripts are being run in the order of dependencies.  Objects should be created after the objects they depend on.

If you’re not planning to use a Schema Migration tool you’ll want to create separate master scripts for each of the create and update directories.

Test

Create a new schema that’s safe to test with, and run your master scripts.  After you get the scripts running without errors, do a schema compare to check for any differences between your new schema and the schema you exported from.

When it’s all the way you want it, you are now ready and able to work from files to build your database code.

Working From  Files

From this point, you can now follow a new and improved workflow when it comes to editing the code for your database objects:

  1. Pull the latest version of your code from the shared repository. NEVER ASSUME YOU ALREADY HAVE THE LATEST.
  2. Make your changes.
  3. Compile to the database.
  4. Commit (to your source code repository, not your transaction) often.
  5. Push your changes back up to the shared repository.

Of course, that is a very simplified workflow. You should start a discussion with your team about using more advanced methods to further automate and improve your processes.

I sincerely hope you skimmed this post because you gave up long ago on editing code directly in the database. If that is not the case, I hope this article helps you make the change. Because then the next time something goes badly wrong,  you can simply recover from Git (or your repository of choice).

No tearing your hair out. No gnashing of teeth. No self-hating recriminations. Just a quick recovery and get back to work.

And then you can move on to more interesting challenges in improving the way you write and maintain your database application code.

Tips to help PL/SQL developers get started with CI/CD

In most ways, PL/SQL development is just like working with any other language but, sometimes it can be a little different.  If you’d like to create a Continuous Deployment Pipeline for your PL/SQL application, here is a short list of tips to help you get started.

Work From Files

Do not use the Database as your source code repository.  If you are making changes to your application in a live database, stop right now and go read PL/SQL 101: Save your source code to files by Steven Feuerstein.

Now that you’re working from files, those files should be checked into…

Version Control

There are a lot of version control applications out there, the most popular right now is probably Git.  One advantage of using Git is, you work in your own local copy of the repository making frequent commits that only you see.  If you’re working in your own personal database you could compile your changes to the database at this point.

If you’re working in a database shared with other people and you’re ready to compile your code,   git pull from the central repository to get any changes since your last pull.  Handle any merge conflicts, then git push your changes back up to the shared repository.  After the push, you can compile your code to the database.  This helps ensure that people don’t overwrite each other’s changes.

Making frequent small commits will help keep everything running smoothly.

What about your schema objects?

Your schema objects, such as tables and views, are as much a part of your application as any other component and changes to these objects should be version controlled as well.  The process of keeping track of schema changes is called Schema Migration and there are open source tools you can use such as Flyway and Liquibase.

Unit Testing

In any application it’s smart to have good test coverage, If your goal is Continuous Deployment it’s critical.  A Unit Test is when you test a single unit of code, in PL/SQL that may be a function or procedure.

Unit tests typically follow these steps:

  1. Setup the environment to simulate how it will be when the application is deployed.  This might include changing configuration settings and loading test data.
  2. Execute the code unit.
  3. Validate the results.
  4. Clean up the environment, resetting it to the state it was in before running the tests.

utPLSQL is a great tool for unit testing your PL/SQL applications.  You will write a package of tests for each ‘unit’ of your application which should test all of the required functionality and potential errors.  utPLSQL is an active open source project with an impressive set of features.  Check out the docs to get started.

Build

Building a database application usually consists of running the change scripts in a specific order.  It’s common to create a master script that executes the others in order of how the objects depend on each other.  However, if the master script simply executes the other scripts, you will need to create additional scripts to track and verify changes, and more scripts to give you the ability to rollback the changes if/when there’s a problem.

There are build tools such as Gradle and Maven that can easily execute your scripts.  But you’ll still need to create the additional control scripts.  If you use a Schema Migration tool it should include a lot of these additional functions without having to write extra scripts.  For an example, check out Dino Date which has a Liquibase migration included.

How to handle the PL/SQL code

You could include your PL/SQL code in a Schema Migration changeset but adding schema migration notation to your PL/SQL introduces another layer of complexity and potential errors.

In the Dino Date runOnChange directory, you will find examples of setting up Liquibase changesets that watch for changes in the files of objects that you would rather keep ‘pure’.  When you run a migration, if the file has changed Liquibase will run the new version.

In a shared database environment, you should execute a schema migration after you pull/merge/push your changes into your version control system.

Automate!

All of these pieces can be tied together and automated with an automation server such as Hudson or Jenkins (both are open source) to create a build pipeline.

Original by Jez Humble

A simple (maybe too simple) build pipeline using the above tools could follow these steps:

  1. Developer makes a change and pushes it to the shared Git repository.
  2. Hudson notices the repository has changed and triggers the build pipeline.
  3. The project is pulled from Git.
  4. Liquibase deploys the changes to a test database.
  5. utPLSQL is trigged to run the unit tests.
  6. Liquibase deploys the changes to the production database.

Other Useful Tools

  • Edition Based Redefinition[pdf] can be used to deploy applications with little to no downtime.
  • Oracle Developer Cloud Service comes with a ton of pre-configured tools to help with almost every aspect of your development process.
  • Gitora can help you version control your application if you are not able to move out to files.

Quick and Easy Raspberry Pi Setup

So, you have a shiny new Raspberry Pi and you’d like to install Linux on it.

This is a short guide to help you get Raspbian installed and configured for Wifi and SSH access.  You should be able to follow this guide even if you don’t connect a keyboard or monitor to your Pi.

NOTE: In order to complete all of the steps in this guide, you will need to be able to access and edit files in an ext4 filesystem.  I will mark these steps with [ext4].  This may require additional steps and/or software for Windows systems.

First up, you will need to download a couple files.

  • Raspbian
    I usually download the image from raspberrypi.org.  There are two images available.  The desktop image comes with a GUI front end and a few pre-installed applications.  The lite image is a ‘headless’ install, meaning you will boot to a command prompt and there is no GUI desktop installed.  This guide will work for either version, pick whichever version you want.
  • Etcher
    Using Etcher makes it easy to flash an image to your sd card.

Put the SD Card in Your Computer

For the next few steps, keep the SD Card in your computer.  You will not put the card in your Pi until we get to the “Boot your Pi” section below.

Flash Raspbian to the SD Card

Run Etcher:

  1. Chose the Raspbian zip file you downloaded above.
  2. Select your SD Card from the list.  WARNING: Etcher does it’s best to make sure it only shows SD Cards in the list, but you should always make sure it’s the card you want to install to, the selected drive will be formatted!
  3. Click Flash.

If you open your favorite file explorer you should now see two new drives listed.

  • boot
  • rootfs [ext4]
Enable SSH Access
  1. Change into boot.  (Modify the ‘cd’ command below to match the path to boot on your system.)
  2. Create an empty file called ‘ssh’.
That’s it.  The first time you boot your Pi ssh will automatically be enabled.  (Don’t boot it up yet.)

Connect Without a Password

Having to type a password everytime you SSH or SCP to your Pi gets old after a while.  If you don’t mind typing the password every time you can skip this step.

Add your public key to your Pi [ext4]:

  1. Locate the ssh public key you want to use (example ~/.ssh/id_rsa.pub). If you don’t already have one, you can follow the steps in this guide.
  2. Change into the pi home on rootfs.  (Modify the ‘cd’ command below to match the path to rootfs on your system.)
  3. Create the .ssh directory.
  4. Append your public key to the authorized_keys file.  (You can repeat this step to append as many keys as you need for the different systems you intend to use.)
If you’re not able to access the ext4 partition from your operating system you can follow this guide on raspberrypi.org after you boot up the Pi.

Enable Wifi [ext4]
  1. Change into rootfs/etc/wpa_supplicant.
  2. Edit wpa_supplicant.conf.  (You may need sudo to edit this file.)
  3. Append the following to the end of the file.  Replace “Your SSID” and “Your WPA Password” with the values to connect to your Wifi.
  4. Save the file.

If you’re not able to access the ext4 partition from your operating system you can follow these instructions after you boot up the Pi with the following changes.

  1. Connect a keyboard and monitor to your Pi.
  2. Change into /etc/wpa_supplicant.
  3. Continue with step 2 above.

Now it’s time to

Boot your Pi

Put the SD Card in your Pi and boot it up.

If you’re not connecting your Pi to a display, you should be able to get its IP address from your Wifi Router admin page.

  1. Connect with ssh.
  2. Change the default pi user password. (default password: raspberry).  Even though we set up access using a public key, the Pi can still be accessed with a password so it’s a good idea to set a new password.
  3. Run an update.

Do Something Fun

At this point, you have a little Linux machine all set up and ready to use for your projects.  Go do something fun with it.

If there are other configurations you’d like me to add to the guide please leave a comment.

Oracle Developer Cloud Service – Use Maven Repository Artifacts in Build Jobs

Maven Repository

Oracle Developer Cloud Service (DevCs) includes a lot of great tools to help you build applications.  The Maven repository is useful for storing build dependencies and artifacts.

Maven artifacts can be uploaded to the repository after they are created by your build.  Dependencies can be manually uploaded ahead of time and pulled into the build jobs that need them.  Let’s take a look at how to work with dependency files in the Maven repository.

Upload Files

We’ll need to upload one or more files first.

  1. Click the Upload button.
  2. Drag and drop files into the box or click “select artifact files” and select the files.

My project uses Oracle JET version 4.1.0 so I’ll add it to my Maven repository.

Populate the file data.

  • GroupId: DinoDate
  • ArtifactId: oracleJet
  • Version 4.1.0
  • Packaging: zip

Click the Start Upload button.

Note: the file may be renamed in the repository.  For example, the oraclejet_4.1.0.zip file will be renamed to oraclejet-4.1.0.zip using the above data.

Now that we have a file in the repository let’s see use it in our build.

Use a Build Tool

If you’re using a build tool such as Maven or Gradle, you can get the dependency declaration for the Maven repository by clicking on the root of the project then copying it from the dependency declaration tab for your tool.

To get the file information:

  • Drill down into the folder.
  • Click on the file you need.
  • Copy the dependency declaration for the file.

At this point, you can run your build tool in a build job as normal.

But if you’re not using a build tool there is another way.

cURL

To use a tool such as cURL you need to get the URL for the file your after.

First, we’ll need the DevCs Maven repository URL for the project.

  1. Click /
  2. Copy URL from the dependency declaration panel

The repository URL will be similar to this:

Add file information to the URL:

This is the same data (plus the file name) we entered when we uploaded the file.

If you need to find the data for a file:

  • Drill down into the folder.
  • Click on the file you need.
  • Copy the information from the dependency declaration for the file.

Now fill out the rest of the URL:

Use cURL in the Build Job

The different components in DevCs already have the permissions to access each other in place so the build job can simply cURL from the Maven repository.

Add an “Execute Shell” build step and add the following (with your repository URL):

You can use any of the cURL features as you normally would, for example, to get multiple files in one call:

Why Store Dependencies in the Project?

Using the DevCs Maven Repository to store dependencies has many benefits, including:

  • It will be faster than pulling dependencies across the internet.
  • You won’t have to worry about a remote repository being offline or files being removed.
  •  Helps protect you from security problems if a remote repository is compromised.

Please leave a comment if you have questions or need additional help.

Deploy a Python application to Oracle Application Container Cloud Service

About Application Container Cloud Service

ACCS provides a pre-configured platform (Platform as a Service or PAAS) where you can quickly deploy and host your applications.  For many of today’s applications, the hosting server is just that, a place to host the application.  Most of the time the only thing an application needs from the server is to have it support the application’s programming language and to provide in and out connections through ports.  Using a PAAS such as ACCS frees you from all of the extra work of configuring and maintaining a server and allows you to focus on perfecting your application.

ACCS supports multiple languages but for this post, I’ll focus on Python.

DinoDate

For the examples, I will be deploying the DinoDate application. DinoDate was written as an open source learning application that can be used to demonstrate database concepts with multiple programming languages.  It currently has both Python and NodeJS mid-tier applications and is backed by an Oracle Database.

The following instructions show how to deploy the Python version of DinoDate to an Oracle ACCS instance.

If you don’t have access to Oracle Cloud services, you can try the Oracle Cloud with $300 of free credit.

Download/Clone the DinoDate application.

Database

First, you’ll need a database.

Create an Oracle Cloud database or if you already have an Oracle Database, make sure that you can safely create and destroy the DD and DD_NON_EBR schema.

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

Prepare the DinoDate Application

Download oraclejet.zip (version 4.1.0).  (Current versions as of the time of this post.)

  • Extract the Oracle JET files
  • Run bower install

Download necessary files

The Docker container for Python used by ACCS comes with Python installed.  We’ll need to include the rest of the dependencies.

Package the Files to Deploy

  • Create a deploy directory with a lib subdirectory.
  • Copy the front end client into the deploy directory.
  • Copy the python application into the deploy directory.
  • Extract the Oracle instant client files into the deploy/lib directory.  (Change the command to point to where your files are located.)
  • Change to the deploy directory.
Create a shell script, launchPython.sh,  to install the dependencies and launch the application.
Create the manifest file: manifest.json

This file declares that we will use Python version 3.6.0 and provides the command that will be used to start the application.

Create the deployment file: deployment.json

This file includes the environment variables DinoDate needs and sets the ACCS deployment to use 1G of memory and only install 1 instance.  PYTHONPATH is the directory we will install the Python modules into and LD_LIBRARY_PATH is used by cx_Oracle to locate the Oracle client files.

Replace “YourJdbcConnecString” with the JDBC connect string for your database.

Important Note

ACCS is pre-configured to listen on $PORT so we set our application to listen on that port.  Do not attempt to change $PORT.  When ACCS performs its post-deploy check it will open the application using $PORT, if the application is not listening on that port and returns a 404 the deployment will fail and be removed.

Create a zip file with the required DinoDate deploy files.

Deploy to ACCS

In your browser navigate to the Oracle Application Container Cloud Service Console.

Push the Create Application button to open the platform selection panel.

Push the Python button to open the application definition panel and expand the ‘More Options’ section.

  • Populate [Name] with DinoDatePython.
  • Click ‘Choose File’ for Archive and select the DinoDatePythonACCS.zip file.
  • Click ‘Choose File’ for Manifest and select the manifest.json file.
  • Click ‘Choose File’ for Deployment Configuration and select the deployment.json file.

You can change the values in the other fields as you’d like, but notice that since we defined “memory”: “1G” and “instances”: “1” in the deployment.json file those values will change automatically.

It’s also possible to include the manifest.json file in the DinoDatePythonACCS.zip file instead of uploading it separately.

Click Create.

It may take several minutes for ACCS to setup the environment and deploy the application.  Once it’s done click on the URL: link to open the application.

Try it out

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.

Quick Review

  1. Download the dependencies.
  2. Create a launch script that will install the dependencies and launch the application.
  3. Collect the required deployment artifacts and dependencies into a .zip file.
  4. Create a manifest.json file that contains at least the required Python version and the command used to start your application.
  5. Create a deployment.json file that contains any needed environment variable definitions.  Optionally you can include ACCS environment definitions such as required memory and number of instances.  (This file is optional.  You could include the environment variables in your launch script.)
    Reminder: ACCS will use the pre-defined environment variable $PORT.  Make sure your application listens on $PORT.
  6. Use the ACCS service console to upload your 3 files and create your new application.

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

Deploy a Node.js application to Oracle Application Container Cloud Service

About Application Container Cloud Service

ACCS provides a pre-configured platform (Platform as a Service or PAAS) where you can quickly deploy and host your applications.  For many of today’s applications, the hosting server is just that, a place to host the application.  Most of the time the only thing an application needs from the server is to have it support the application’s programming language and to provide in and out connections through ports.  Using a PAAS such as ACCS frees you from all of the extra work of configuring and maintaining a server and allows you to focus on perfecting your application.

ACCS supports multiple languages but for this post, I’ll focus on Node.js.

DinoDate

For the examples, I will be deploying the DinoDate application. DinoDate was written as an open source learning application that can be used to demonstrate database concepts with multiple programming languages.  It currently has both Python and NodeJS mid-tier applications and is backed by an Oracle Database.

The following instructions show how to deploy the Node.js version of DinoDate to an Oracle ACCS instance.

If you don’t have access to Oracle Cloud services, you can try the Oracle Cloud with $300 of free credit.

Download/Clone the DinoDate application.

Database

First, you’ll need a database.

Create an Oracle Cloud database or if you already have an Oracle Database, make sure that you can safely create and destroy the DD and DD_NON_EBR schema.

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

Prepare the DinoDate Application

Download oraclejet.zip (version 4.1.0).  (Current versions as of the time of this post.)

  • Extract the Oracle JET files
  • Run bower install
  • Install the NodeJS modules.  ACCS assumes your deploy package will include all necessary modules.
  • Create a deploy directory.
  • Copy the front end client into the deploy directory.
  • Copy the nodejs application into the deploy directory.
  • Change to the deploy/nodejs directory.
Create the environment variables.  (Replace YourJdbcConnecString with your JDBC connect string.)
Test the application to make sure everything is working.
  • Open your browser to localhost:3000
  • Log in as bob@example.com (any password will work)
  • Open the search tab and execute a search.  I typically search for ‘eat’ it will return several members.

Ctrl-c to stop the node server then switch back to the deploy directory.

Package the Files to Deploy

Create the manifest file: manifest.json

This file declares that we will use Node.js version 8 and provides the command that will be used to start the application.

Create the deployment file: deployment.json

This file includes the environment variables DinoDate needs and sets the ACCS deployment to use 1G of memory and only install 1 instance.

Replace “YourJdbcConnecString” with the JDBC connect string for your database.

Important Note

ACCS is pre-configured to listen on $PORT so we set our application to listen on that port.  Do not attempt to change $PORT.  When ACCS performs its post-deploy check it will open the application using $PORT, if the application is not listening on that port and returns a 404 the deployment will fail and be removed.

Create a zip file with the required DinoDate deploy files.

Important Note

The Node.js platform in ACCS has the oracledb module pre-installed.  If we were to upload the module we just installed it would cause a conflict that would cause the deployment to fail and be removed, so we exclude it from the deployment .zip file.

Deploy to ACCS

In your browser navigate to the Oracle Application Container Cloud Service Console.

Push the Create Application button to open the platform selection panel.

 

Push the Node button to open the application definition panel.

  • Populate [Name] with DinoDate.
  • Click ‘Choose File’ for Archive and select the DinoDateNodeACCS.zip file.
  • Click ‘Choose File’ for Manifest and select the manifest.json file.
  • Click ‘Choose File’ for Deployment Configuration and select the deployment.json file.

You can change the values in the other fields as you’d like, but notice that since we defined “memory”: “1G” and “instances”: “1” in the deployment.json file those values will change automatically.

It’s also possible to include the manifest.json file in the DinoDateNodeACCS.zip file instead of uploading it separately.

Click Create.

It may take several minutes for ACCS to setup the environment and deploy the application.  Once it’s done click on the URL: link to open the application.

Try it out

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.

Quick Review

  1. Build your application and test it.
  2. Collect the required deployment artifacts and dependencies into a .zip file.
    Reminder: do not include the oracledb module.
  3. Create a manifest.json file that contains at least the required Node.js version and the command used to start your application.
  4. Create a deployment.json file that contains any needed environment variable definitions.  Optionally you can include ACCS environment definitions such as required memory and number of instances.  (This file is optional)
    Reminder: ACCS will use the pre-defined environment variable $PORT.  Make sure your application listens on $PORT.
  5. Use the ACCS service console to upload your 3 files and create your new application.

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

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!

Becoming a DevOps “expert”

I’ve decided to learn more about DevOps.

I’ve always been a believer in automating repetitive tasks and letting machines do as much of “my” work as they can.  The way I learn best is (as you can tell by the name of my blog) I learn about the topic, I build something from what I’ve learned and I share my experience.

Given that DevOps is a very big topic, it will take more than one or two posts to do it justice.  This post is the first in a series of blog posts, videos and presentations that I plan to create as I learn more.  I think the best place to start is with what I “think” I know now.

What is DevOps?

A couple years ago I was using Jenkins to create a continuous delivery pipeline for a project I was working on.  I was the only one working on the project and after seeing a CD demonstration at a conference I figured I’d give it a try.  I had everything working and I was quite pleased with myself.  Then I started hearing the term DevOps and assumed it was just a term for what I was already doing.  I was partially right.

DevOps is more than just automating the software delivery process, it’s also a cultural mindset.  It’s developers and operations working together throughout the full lifecycle of a project instead of in separate silos.  Since I was working solo on that project I missed out on this aspect.  Currently, I’m not working on any project where I can experience the full cultural aspect so I plan to mentally assume different roles as I work through the learning process.

If you’d like a better definition, there is plenty of material available on the web from real experts.  I only wanted to document what DevOps means to me as I start to learn more.

My current plan.

I have been working on an open source application used for demonstrations and learning called DinoDate.  I am going to build a DevOps process around this application.  My focus will be a bit more on the database aspects of DevOps since the database isn’t always used to its full potential and sometimes even treated like a bucket of data.  I will be building this process using the Oracle Developer Cloud Service against an Oracle Cloud Database and other Oracle Cloud services, as well as other tools such as Jenkins against an Oracle Database on a VM.

Plan Steps:
  1. Define the steps to manually deploy DinoDate as is.
    1. Automate the build and deploy process which currently is, run some scripts and scp the code to an Oracle Compute instance where I have already setup Python and NodeJS.
    2. Deploy the NodeJS and Python apps to an Oracle Application Container Cloud instance.
  2. Add some open source tools to improve the process.
    • Build script using Gradle.
    • Schema object version control using Liquibase.
    • Unit tests for the PL/SQL using UTPLSQL.
  3. Automate creating the infrastructure (DB, Compute instance) from scratch then deploy, test and destroy.
  4. Reproduce the entire CD pipeline using Jenkins (or another tool) against a VM.

Once I’m satisfied with my understanding of the tools and workflow, I’ll find a project that would benefit from a DevOps environment and pester encourage them to switch to a DevOps process with an offer to act as the DevOps “expert”.

More to come.

Keep an eye out here and on my YouTube channel for how-to and ‘lessons learned’ posts that I’ll make as I go.  Feel free to post a comment if you see that I’ve already got something wrong or if you have a specific interest you’d like me to focus on as I go.