pink cylinders

More than Four

...there's an axis for that.

Using recursive Common Table Expressions to generate lists

Say we want create a generated list of values - sequential dates, for example - as a set of rows. We can do this using recursive common table expressions.

Here's an example of a common table expression to generate a set of dates:

with dates as
(
select date '2012-01-01' as DATE_VALUE from dual
union all
select date '2012-01-02' as DATE_VALUE from dual
union all
select date '2012-01-03' as DATE_VALUE from dual
)
select * from dates;

Using this technique, we could create a set of rows with sequential date values... but it is not very flexible. Ideally, we want a technique that expresses the intent algorithmically.

In 11g R2 and later, we can use a reference to the outer table recursively from inside the definition! This sounds crazy but it works:

with dates as
(
select date '2012-01-01' as DATE_VALUE from dual
union all
select d.DATE_VALUE + interval '1' day as DATE_VALUE
from dates d
where d.DATE_VALUE < date '2013-01-01'
)
select * from dates;

In this example, we are UNIONING the first row with a sub-select against itself, adding a new row that increments the date value by 1.

The first time I tried this technique, I got the following error:

ORA-32039: recursive WITH clause must have column alias list

Some research on the web gave me a page that explained what the problem was. The error message is actually very clear: we need to add a list of columns at the top of the WITH <alias> clause:

with dates ( DATE_VALUE ) as
(
select date '2012-01-01' as DATE_VALUE from dual
union all
select d.DATE_VALUE + interval '1' day as DATE_VALUE
from dates d
where d.DATE_VALUE < date '2013-01-01'
)
select * from dates;

And now, it just works:
 
SQL>
DATE_VALUE
-----------
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08
2012-01-09
2012-01-10
2012-01-11
:

Aside: Here's how to do the same query in PostgreSQL:

with recursive dates ( DATE_VALUE ) as
(
select date '2012-01-01' as DATE_VALUE
union all
select cast(d.DATE_VALUE + interval '1' day as date) as DATE_VALUE
from dates d
where d.DATE_VALUE < date '2013-01-01'
)
select * from dates;

The nice thing about this technique is that we can control the end date, and the increment size, by variables:

define _increment = '5';
define _start = date '2012-01-01';
define _end = date '2012-02-01';

with dates ( DATE_VALUE ) as
(
select &_start as DATE_VALUE from dual
union all
select d.DATE_VALUE + interval '&_increment' day as DATE_VALUE
from dates d
where d.DATE_VALUE < &_end
)
select * from dates;

This gives us a list of dates 5 days apart.

 

Using this technique in a PL/SQL block

If you're in a PL/SQL block, trying to generate a list dynamically based on parameter values, the code will need to look slightly different:

declare
p_start_time date;
p_interval_days integer;
p_end_date date;
--
l_interval interval day to second;
begin
l_interval := numtodsinterval( p_interval_days, 'day' );
insert into My_List_Of_Dates
with
dates ( DATE_VALUE ) as
(
select
p_start_time as DATE_VALUE
from dual
union all
select
DATE_VALUE + l_interval as DATE_VALUE
from dates d
where d.END_TIME < p_end_date
)
select DATE_VALUE from dates;
end;

 

 


 

New Gig, New Tooling

I've just started a new full-time gig, and it involves several technologies for which I don't have in-depth experience, including:

  • PostgreSQL & MySQL
  • Python 3

I'm not too worried. Once you've had sufficient experience with two varieties of RDBMS (e.g. SQL Server and Oracle) then becoming capable in a third one isn't too hard. You kind of know where the gaps are going to be, and how to find out the answers rapidly.

I want to give a huge shout-out to a tool called dBeaver. A colleague at the new gig turned me on to it. It's an RDBMS-agnostic, cross-platform database IDE based on the Eclipse framework.

Holy crap, how had I not heard of this before. It's awesome. It just works. I don't miss PL/SQL Developer or Management Studio at all. (At least, not yet.) The community edition is free, but it is worth supporting with a license.

Speaking of "community editions", PyCharm CE is a very nice Python IDE with Git integration.

Windows Live Writer, Code Snippet, and embedded styles

I'm a long-time fan of Windows Live Writer, which I can't link you to because it is no longer available from Microsoft. (Search for Windows Live Essentials 2012 and maybe you'll find something.)

Thankfully the project was moved into open-source and a variant of it is available today as Open Live Writer. That's cool - but as of this writing, it does not support plug-ins.

Alas, I rely on a plug-in called "Code Snippet" by Leo Vildosola. So I'm sticking with the original Windows Live Writer for now.

Embittered Styles

Code Snippet is great for posting source code and similar monospace text blocks into the blog page. It also has the default option to emit embedded styles into the generated HTML code. This makes for a kind of nasty lowest-common denominator format, like this:

<pre id="codeSnippet" style="border-top-style: none; font-size: 8pt; overflow: visible; 
border-left-style: none; font-family: 'Courier New', courier, monospace; width: 100%;
border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr;
text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px;
margin: 0em; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4">

If you're not paying attention, you can end up with a lot of blog posts with these "embittered" styles and end up with serviceable but not necessarily attractive text blocks. (raises hand).

This option can be switched off when authoring new blog posts. See that "Embed Styles" check-button in the UI?

imageIf you un-select that option, you get this generated instead:

<pre id="codeSnippet" class="csharpcode">This is a test without embedded styling</pre>

That's quite a difference.

Now we get a CLASS attribute instead of the embedded styles, so if we want to improve upon the basic default <pre> tag style, we'll obviously need to add a style to our CSS.

Note: no matter what language/syntax you choose in Code Snippet's Language dropdown list, the emitted CLASS value will always be "csharpcode". This could be confusing because you might not be pasting C# code into the snippet, but the CSS class doesn't appear to be related.

image

OK, we can definitely live with this, and add a style directive to our CSS. But what about all those previous blog posts with text blocks with embedded styles?

This is !important

Up until today I always thought that embedded styles trumped CSS, but it turns out there is a qualifier you can add to CSS that forces modern web browsers to use the CSS styles preferentially, even for embedded styles.

That qualifier is "!important".

Now I can beautify my historical blog posts by adding the following to my blog theme's style.css:

/* Windows Live Writer - Code Snippet - Fix for emitted embedded styles */
#codeSnippet {
background-color: aliceblue !important;
font-family: "Droid Sans Mono", Consolas, courier, monospace !important;
}

Awesome.

 
References

Changing Source Control providers

For the last few years we've used Assembla to host source control (Subversion) for one of our projects. We signed up for a free hosting plan, and later on, when Assembla switched to pay-only plans, they graciously allowed us to continue using them for no charge. We knew we were on borrowed time, however.

This week I received a nicely-worded email from them indicating that it was time for us to upgrade to a paid plan, or transition to a different provider. After reviewing the paid options, we decided to find someone else to host our project. We have very modest requirements, and the offerings from Assembla were somewhat more than we needed.

Apart from that, we have no complaints about Assembla - they're awesome, and if one of their plans fits your needs, I say go for it.

However, for our little project, RiouxSVN was looking like a better fit. I decided to give them a try.

How much?

For no cost, you get 50 MB of storage for up to 4 repositories. Well, we had one repository, but it was (according to Assembla) 75 MB in size. RiouxSVN gives you the tools to upgrade various aspects of your account, through redemption of credits. What is a credit? It's what you get if you donate to RiouxSVN (via PayPal). $1 gets you 1 credit.

If you want 20 MB of additional storage, that uses 1 credit. Want another repository slot? That's 2 credits. If you delete a repository, you get back the  credits used to augment it, allowing you to re-allocate them. Nice.

I used 4 credits to pump up the available storage to 130 MB. That's permanent - it's one-time, not an annual donation. That's nice.

Migration

OK, so the next  step is to migrate our repository from Assembla to RiouxSVN.

After some research, I determined that the best option for us was a two-step process: to export to a local dump file, and then import the dump file. As a long-time, but not very deep TortoiseSVN user, I needed to install the latest command-line tools for the export/import task. I selected SlikSVN for this purpose.

The export, using svnrdump,  is simple:

cd c:\temp
mkdir svn_dump
cd svn_dump
svnrdump dump https://subversion.assembla.com/svn/MyProject/trunk > MyProject.svn_dump
* Dumped revision 0.
* Dumped revision 1.
* Dumped revision 2.
:

After about 2-3 minutes, I had a 105 MB dump file.

Importing was basically the same process, only this time I was prompted for my RiouxSVN account credentials:

svnrdump load https://svn.riouxsvn.com/MyProject < MyProject.svn_dump
Authentication realm: <https://svn.riouxsvn.com:443> RiouxSVN
Username: xxxxxx
Password for 'xxxxxx': ************
* Loaded revision 0.
* Loaded revision 1.
* Loaded revision 2.
:

This took longer, a couple of seconds per revision. After about 10 minutes, it was completed. And I verified I could check out, check in, and commit a simple change to the new repository.

SQL Server 2017 on Linux Mint

I followed the instructions as laid out here:

My target was a VM instance of Linux Mint 18.3, called "golem". Everything just works... SQL Server is running automatically after I boot the VM.

Some other links I need to review:

 

Interestingly, the database properties don't seem to know about the Linux host:

image

Fart-ing forward slashes

I have a home-grown database build script that allows me to build multiple instances of my database.

The build scripts allow me to target a number of different environments (test, dev, etc) and perform upgrade or replace operations.

I use the wonderful FART utility to perform text substitution into templated script files, using placeholders delimited with @@.  (FART is a command-line utility developed by Lionello Lunesu. The name is an acronym for "Find-And-Replace-Text". )

For example, consider the following template script, build.tpl:

create database @@DB_NAME@@
containment = partial
on primary
( name = '@@DB_NAME@@',
filename = '@@DATA_PATH@@@@DB_NAME@@.mdf',
size = 5120KB,
filegrowth = 1024KB
)
log on
( name = '@@DB_NAME@@_log',
filename = '@@LOG_PATH@@@@DB_NAME@@_log.ldf',
size = 1024KB,
filegrowth = 10%
)
collate Latin1_General_CS_AS;
go

This template can be written out as a targeted script using a sequence of command-line instructions:

copy build.tpl build.sql

set SB_DB_NAME=MY_NEW_DB
set SB_DATA_PATH=c:\SQL\Data\
set SB_LOG_PATH=C:\SQL\Log\

fart *.sql @@DB_NAME@@ %SB_DB_NAME%
fart *.sql @@DATA_PATH@@ %SB_DATA_PATH%
fart *.sql @@LOG_PATH@@ %SB_LOG_PATH%

Running this yields the following output:

build.sql
Replaced 5 occurence(s) in 1 file(s).

build.sql
Replaced 1 occurence(s) in 1 file(s).

build.sql
Replaced 1 occurence(s) in 1 file(s).

And the contents of build.sql is now:

create database MY_NEW_DB
containment = partial
on primary
( name = 'MY_NEW_DB',
filename = 'C:\SQL\Data\MY_NEW_DB.mdf',
size = 5120KB,
filegrowth = 1024KB
)
log on
( name = 'MY_NEW_DB_log',
filename = 'C:\SQL\Log\MY_NEW_DB_log.ldf',
size = 1024KB,
filegrowth = 10%
)
collate Latin1_General_CS_AS;
go

This is now ready to execute as part of the larger automated build process (This is obviously a simplified example.)

This is all fine and dandy until we try to target a brand new instance of SQL Server 2017 running on Linux. (Distribution of choice: Mint). The paths need to be changed to the unix-style with forward slashes:

set SB_DB_NAME=MY_NEW_DB
set SB_DATA_PATH=/var/opt/mssql/data/
set SB_LOG_PATH=/var/opt/mssql/data/

You'd think this would Just Work, but unfortunately we get an error:

build.sql
Replaced 5 occurence(s) in 1 file(s).

> fart *.sql @@DATA_PATH@@ %SB_DATA_PATH%
Error: invalid option -/
Error: invalid option -o
Error: invalid option -t
Error: invalid option -/
Error: invalid option -m
Error: invalid option -l
Error: invalid option -/
Error: invalid option -d
Error: invalid option -t
Error: invalid option -/

We have to escape the slashes, and also tell FART to use c-style extended characters, with the -C switch:

set SB_DB_NAME=MY_NEW_DB
set SB_DATA_PATH=\/var\/opt\/mssql\/data\/
set SB_LOG_PATH=\/var\/opt\/mssql\/data\/

fart *.sql @@DB_NAME@@ %SB_DB_NAME%
fart -C *.sql @@DATA_PATH@@ %SB_DATA_PATH%
fart -C *.sql @@LOG_PATH@@ %SB_LOG_PATH%

This looks a bit odd, and the output to console includes a warning message:
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
build.sql
Replaced 1 occurence(s) in 1 file(s).

Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
build.sql
Replaced 1 occurence(s) in 1 file(s).

However, the substitution did take place, and the result is correct:

create database MY_NEW_DB
containment = partial
on primary
( name = 'MY_NEW_DB',
filename = '/var/opt/mssql/data/MY_NEW_DB.mdf',
size = 5120KB,
filegrowth = 1024KB
)

etc.

This  console warning message is probably a bug in FART.

UPDATE: And someone else has already reported it: #12 Warning: unrecognized character escape sequence: \/.

Virtuabox Host Networking revisited

My current work environment consists of a Windows 10 host running Virtualbox 5.2 with a Windows 7 guest VM,in which I run my client development tools.

I had Virtualbox Host Networking set up on the default 192.168.56.x subnet, but the second adapter in the guest wasn’t configured because at the time I had no need to “see” the host resources from the guest.

Until now…   I find that I needed to point some client tools on the guest VM at the SQL Server instance running on the host.

Okay, no problem, just set up the second adaptor on the guest as 192.168.56.56, set the gateway as 56.1, and it should all work, right?

image

Yeah, well, for some reason it did not work. I could PING the guest from the host side, but the host was not responding to pings from the guest.

Temporarily disabling the Windows Firewall on the host allowed the guest to “see” the host. So it was a Windows 10 host firewall configuration issue.

I found this post that explains how best to address this problem:

https://superuser.com/questions/936581/how-to-config-windows-firewall-so-vm-host-only-can-ping-windows-7

Essentially:

  • Open Windows Firewall
  • Scroll the right-hand pane down until you see “Windows Firewall Properties”
  • Click on it
  • Select Public Profile
  • Click on “Customize”
  • un-check the “Virtualbox Host Only Network”

 

image

This will disable Windows Firewall from getting in the way of networking between the host and guest.

Thanks, Andrew Joe!

Upgrading site from BlogEngine 2.5 to 3.3

I though this was going to be a nightmare, but getting the content moved from 2.5 to 3.3 was the easiest part, using the BlogML export and import. The tricky part was adapting the Standard theme to match my previously customized one. A straight copy of the theme folder did not work.

This will do for now.

Flyway DB

For years I've used a database build script framework written in-house to recompile all schema objects and run unit tests. However, the actual DDL code to migrate a schema from one version to the next was always left as a hand-rolled script that is executed up-front before the normal build process.

A colleague introduced me to Flyway DB and I am now a fan. The introduction, documentation, and tutorial material is top-notch.

Installing Flyway on Linux

Flyway DB can be found here:

Although it has an API for integration, I specifically like the command-line utility version:  https://flywaydb.org/getstarted/firststeps/commandline

Unzip the command-line tool version into your preferred program location. In my Linux VM, I decided on ~/bin:

image

and created a simlink to put flyway into the path:

$ ln -s ~/bin/flyway-4.2.0/flyway ~/bin/flyway

Now I can run it from any directory.

How Flyway works

Flyway scans the contents of a “migration scripts” folder using a file naming convention to understand which scripts belong to which specific version.

It then uses a special table in the target database called “schema_version” that contains metadata about the current version of the database.

Using Flyway

The basic syntax is

> flyway VERB

where VERB is one of:

  • migrate
  • clean
  • info
  • validate
  • baseline
  • repair

In our case, we need to:

  • "baseline" our database so that Flyway understands the current starting "version";
  • write a DDL script to move the DB to the next version;
  • "migrate" our database instance to that version.

Setting up a working project directory

  • Create a working folder
  • copy the {install}/conf/flyway.conf file into the working directory, for project-specific overrides;
  • create a sub-folder called sql

The working directory I set up was:

/home/colin/Projects/flyway
/home/colin/Projects/flyway/sql

I copied the flyway.conf file, ready for local configuration.

Selecting the target DB

I've been working through the "MySQL Cookbook" tutorial, so I have a user and a database installed in the VM locally, called "cookbook", with one table "limbs" already created.

Editing the .conf file:

flyway.url=jdbc:mysql://localhost:3306/cookbook
flyway.user=root
flyway.locations=filesystem:./sql

We could supply the password as well, but for interactive use it is safer to have flyway prompt for it. For automated processes, you'd want to put the password in the .conf file.

Note the syntax of specifying the location of the SQL migration scripts, with a "filesystem:" prefix.

Just for kicks, let's run the "migrate" command:

$ flyway migrate
Flyway 4.2.0 by Boxfuse

Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Successfully validated 0 migrations (execution time 00:00.003s)
ERROR: Found non-empty schema(s) `cookbook` without metadata table! Use baseline() or set baselineOnMigrate to true to initialize the
$

We see a couple of issues:

  • the target DB is missing the metadata table, and therefore Flyway doesn't know what version it is;
  • we don't have any upgrade scripts to process.

Baselining our target DB

We can update the .conf file to include information about the database. Let's assume we are starting at version 5:

flyway.baselineVersion=5
flyway.baselineDescription=Baseline prior to Flyway

Now we can use the "baseline" command to create the metadata:

$ flyway baseline
Flyway 4.2.0 by Boxfuse

Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Creating Metadata table: `cookbook`.`schema_version`
Successfully baselined schema with version: 5
$

Querying MySQL shows the table now exists:

mysql> show tables;
+--------------------+
| Tables_in_cookbook |
+--------------------+
| limbs |
| schema_version |
+--------------------+
2 rows in set (0.00 sec)

And the contents:

mysql> select * from schema_version;
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
| installed_rank | version | description | type | script | checksum | installed_by | installed_on
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
| 1 | 5 | Baseline prior to Flyway | BASELINE | Baseline prior to Flyway | NULL | root | 2017-06-12 14
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
1 row in set (0.00 sec)

We can use the "info" verb on the command-line to review the current version according to Flyway:

$ flyway info
Flyway 4.2.0 by Boxfuse

Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Baselin |
+---------+--------------------------+---------------------+---------+
$
 
Both schema and table name can be changed in the .conf, but it is probably better to leave it at default. For testing migrations on a freshly-built test database, there is a setting in the .conf that allows automatic default baselining if the schema table is not detected:
 
flyway.baselineOnMigrate=true

This can be very handy, along with specifying the DB user password, if security is not a priority.

Migrating the DB to a new version

Our current DB version is 5, but I'm going to create scripts for the versions either side of that, in order to see what Flyway does.

The script filenaming convention is documented here:

There are a number of possibilities including versioning scripts; repeatable scripts; and SQL callback scripts for invoking at various points during the process. For now, we're just looking at versioning scripts.

Consider the following script files:

$ ls -l sql/*.sql
-rw-rw-r-- 1 colin colin 64 Jun 12 14:35 sql/V4__other_objects.sql
-rw-rw-r-- 1 colin colin 80 Jun 12 14:35 sql/V5__limbs.sql
-rw-rw-r-- 1 colin colin 62 Jun 12 14:36 sql/V6__test_table2.sql
$

Note: it may be tricky to remember that the name convention requires a double-underscore ("dunder") between the version number and the file name!

Now, if we run the "info" verb:

Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)

+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 4 | other objects | | <Baseln |
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6 | test table2 | | Pending |
+---------+--------------------------+---------------------+---------+
$

Flyway detects this information from a) the script file naming convention, and b) the contents of the schema_version table in the target DB.

Questions:

  • Can we have more than one script belonging to a specific version?
  • If so, what is the execution sequence?
  • Can we have sub-versions (e.g. 5.1, 5.2 etc)?

The answer is that Flyway will throw an error if it detects two scripts associated with the same version number, so in fact you MUST use sub-version numbers for multiple scripts, like this:

V6_0__test_table2.sql
V6_1_1_test_table1.sql
V6_1_2__test_table3.sql

Of course, this now dictates the execution sequence:

$ flyway info
Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 4 | other objects | | <Baseln |
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6.0 | test table2 | | Pending |
| 6.1.1 | test table1 | | Pending |
| 6.1.2 | test table3 | | Pending |
+---------+--------------------------+---------------------+---------+

Now, let's migrate!

$ flyway migrate
Flyway 4.2.0 by Boxfuse

Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Successfully validated 5 migrations (execution time 00:00.018s)
Current version of schema `cookbook`: 5
Migrating schema `cookbook` to version 6.0 - test table2
Migrating schema `cookbook` to version 6.1.1 - test table1
Migrating schema `cookbook` to version 6.1.2 - test table3
Successfully applied 3 migrations to schema `cookbook` (execution time 00:00.115s).
$

mysql> show tables;
+--------------------+
| Tables_in_cookbook |
+--------------------+
| limbs |
| schema_version |
| test1 |
| test2 |
| test3 |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from schema_version;
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
| installed_rank | version | description | type | script | checksum | install
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
| 1 | 5 | Baseline prior to Flyway | BASELINE | Baseline prior to Flyway | NULL | root
| 2 | 6.0 | test table2 | SQL | V6_0__test_table2.sql | 224077121 | root
| 3 | 6.1.1 | test table1 | SQL | V6_1_1__test_table1.sql | 669488585 | root
| 4 | 6.1.2 | test table3 | SQL | V6_1_2__test_table3.sql | -1574979834 | root
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
4 rows in set (0.00 sec)

And now:
 
$ flyway info
Flyway 4.2.0 by Boxfuse

Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 4 | other objects | | <Baseln |
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6.0 | test table2 | 2017-06-12 15:01:22 | Success |
| 6.1.1 | test table1 | 2017-06-12 15:01:22 | Success |
| 6.1.2 | test table3 | 2017-06-12 15:01:22 | Success |
+---------+--------------------------+---------------------+---------+

It's pretty cool!

Installing Oracle's JDK in Ubuntu Linux

Just for my own notes, because I can never remember this stuff.

1. Check the architecture of your host:

colin@Mongpy:~$ uname -m
x86_64

2. Grab the appropriate download from Oracle's web site:

image

My web browser puts it in the Downloads folder under home.

colin@Mongpy:~$ ls -l ~/Downloads
total 393356
drwxr-sr-x 7 colin colin 4096 Mar 31 12:21 eclipse-installer
-rw-rw-r-- 1 colin colin 185540433 May 25 13:19 jdk-8u131-linux-x64.tar.gz
-rw-rw-r-- 1 colin colin 21944320 Mar 29 14:18 mysql-workbench-community-6.3.9-1ubuntu16.10-amd64.deb
-rw-rw-r-- 1 colin colin 195297254 May 25 12:47 pycharm-community-2017.1.3.tar.gz

3. Open a terminal and get superuser access:

colin@Mongpy:~$ sudo su
[sudo] password for colin:
root@Mongpy:/home/colin#

4. Make a directory and expand the archive:

root@Mongpy:/home/colin# mkdir /opt/jdk
root@Mongpy:/home/colin# tar -zxf ./Downloads/jdk-8u131-linux-x64.tar.gz -C /opt/jdk
root@Mongpy:/home/colin# ls -l /opt/jdk
total 4
drwxr-xr-x 8 uucp 143 4096 Mar 15 01:35 jdk1.8.0_131

5. Review the current default JDK:

root@Mongpy:~# update-alternatives --display java
java - auto mode
link best version is /usr/lib/jvm/java-8-openjdk-amd64/jre/bin/java
link currently points to /usr/lib/jvm/java-8-openjdk-amd64/jre/bin/java
link java is /usr/bin/java
slave java.1.gz is /usr/share/man/man1/java.1.gz
/usr/lib/jvm/java-8-openjdk-amd64/jre/bin/java - priority 1081
slave java.1.gz: /usr/lib/jvm/java-8-openjdk-amd64/jre/man/man1/java.1.gz

root@Mongpy:~# update-alternatives --display javac
javac - auto mode
link best version is /usr/lib/jvm/java-8-openjdk-amd64/bin/javac
link currently points to /usr/lib/jvm/java-8-openjdk-amd64/bin/javac
link javac is /usr/bin/javac
slave javac.1.gz is /usr/share/man/man1/javac.1.gz
/usr/lib/jvm/java-8-openjdk-amd64/bin/javac - priority 1081
slave javac.1.gz: /usr/lib/jvm/java-8-openjdk-amd64/man/man1/javac.1.gz

Note the current "priority" is 1081.

6. Set the Oracle version as the default JDK, using a higher priority, say 1090:

root@Mongpy:/home/colin# update-alternatives --install /usr/bin/java java /opt/jdk/jdk1.8.0_131/bin/java 1090

update-alternatives: using /opt/jdk/jdk1.8.0_131/bin/java to provide /usr/bin/java (java) in auto mode

root@Mongpy:/home/colin# update-alternatives --install /usr/bin/javac javac /opt/jdk/jdk1.8.0_131/bin/javac 1090

update-alternatives: using /opt/jdk/jdk1.8.0_131/bin/javac to provide /usr/bin/javac (javac) in auto mode

root@Mongpy:/home/colin# update-alternatives --display java
java - auto mode
link best version is /opt/jdk/jdk1.8.0_131/bin/java
link currently points to /opt/jdk/jdk1.8.0_131/bin/java
link java is /usr/bin/java
slave java.1.gz is /usr/share/man/man1/java.1.gz
/opt/jdk/jdk1.8.0_131/bin/java - priority 1090
/usr/lib/jvm/java-8-openjdk-amd64/jre/bin/java - priority 1081
slave java.1.gz: /usr/lib/jvm/java-8-openjdk-amd64/jre/man/man1/java.1.gz

root@Mongpy:/home/colin# update-alternatives --display javac
javac - auto mode
link best version is /opt/jdk/jdk1.8.0_131/bin/javac
link currently points to /opt/jdk/jdk1.8.0_131/bin/javac
link javac is /usr/bin/javac
slave javac.1.gz is /usr/share/man/man1/javac.1.gz
/opt/jdk/jdk1.8.0_131/bin/javac - priority 1090
/usr/lib/jvm/java-8-openjdk-amd64/bin/javac - priority 1081
slave javac.1.gz: /usr/lib/jvm/java-8-openjdk-amd64/man/man1/javac.1.gz

7. Finally, testing:

colin@Mongpy:~$ java -version
java version "1.8.0_131"
Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)

Cool.