colin – More Than Four

Migrating to WordPress 5.4 from BlogEngine.NET

Lisa and I are probably the last people on the planet to still be using BlogEngine.NET for our blogs. Hey, if it works… don’t upgrade it.

Many other respected folks out there have described their process of moving their blog to WordPress, and I’m indebted to them. In this post, I’ll add my notes on the whole experience. Maybe someone out there will find it interesting and helpful.

Overview

Here’s the basic process:

Export your posts from BlogEngine.NET admin as BlogML
Save your image and attachment files
Create a new (empty) WordPress site
upload the image and attachment files to the /wp-content/upload folder
Obtain and install the BlogML Importer plugin for WordPress
Perform some simple text find-and-replace on the BlogML file
Tools->Import the BlogML file

In Practice

I hit some potholes… The image link replacements were all straight-forward but required some planning on how the sites were going to be switched. I definitely wanted to keep the same name of the blog site directory (I think it is no secret that it was and is “morethanfour”.)

So the change is minimal – replacing the BlogEngine.NET-specific “/image.axd?picture=” hrefs with “/wp-content/uploads/” path.

It was more complicated than that due to my first importing into a local web root and reviewing the format of the imported posts, and fixing a few strange glitches (due to non-breaking spaces).

Once I was comfortable with the replacements and the final appearance of the imported posts, it was time to deploy to production. For me, this involved the following steps:

remove the ASP.NET application on the existing BlogEngine.NET site
rename the blog root from “morethanfour” to “old-blog-backup”
re-create the “morethanfour” directory
upload the WordPress 5.4 install files
create the associated blog-specific MySQL database and user
ensure that the server was running PHP 5.6 (or later, was not the default!)
run the WordPress install process
copy the image files (previously saved) into /wp-content/upload
copy the blogml-importer plugin into /wp-content/plugins (see below)
install/activate the plugin inside the WordPress Administration
Go to Tools > Import and run the BlogML Import (which is now available)
Follow the prompts

And that is basically it. The final results look great.

Concerning that blogml-importer plugin

The original site with the BlogML Import plugin is no longer available, although other locations have hosted it, including the one I eventually downloaded it from: http://pluginsroom.com/plugins/blogml-importer

The zip archive contains two files, blogml-importer.php and Xpath.class.php, and the second one is not compatible with PHP 5.6 and later. I hacked the Xpath.class.php until I could install the plugin into WordPress without errors.

I have attached my 5.6/7.2 compatible version, zipped, here:

blogml-importer_2020 Download

Most of the other potholes aren’t really worth discussing because they were peculiar to differences in PHP version between my hosting provider and my own local (laptop) development site.

Detecting overlapping date ranges

Consider an Event table, with columns A and B defining the start and end of the period of time over which the event occurred.
Consider also a random period of time defined from S to E.
Now imagine that you want to select all event rows that overlap that specific period of time.

By overlap, we mean that case 1,2,4, and 6 should be included, but 3 and 5 should not:

The first solution that might occur to us is this one:

select * from Event
where B between S and E or A between S and E

At first glance it looks clear and elegant, but unfortunately it doesn’t handle case #6.

Somewhat less intuitive but still elegant and also complete is:

select * from Event
where B >= S and A <= E

AES Encryption between Informatica and PostgreSQL

We encountered a situation where encrypted data exported from Informatica could not be decrypted by PostgreSQL, once we’d loaded it into a table.

If you found this page looking for a solution, save your time. I don’t have a solution, I just wanted to make some notes.

How does Informatica encrypt data?

According to this knowledgebase article:

AES_DECRYPT needs binary data as input. Hence when we first encrypt the data using the AES_ENCRYPT function, the data transformed by this function has to be encoded in base64 format. This transformed data can then be used as an input to the AES_DECRYPT function.

In other words:

CIPHERTEXT = Base64_Encode( AES_Encrypt( PLAINTTEXT ))

and

PLAINTEXT = AES_Decrypt( Base64_Decode( CIPHERTEXT ))

According to this thread:

[informatica’s] AES_ENCRYPT function uses the combination as follows which is expressed in terms of functions in DBMS_CRYPTO package of Oracle: DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_ZERO

From Oracle’s documentation, we get:

DBMS_CRYPTO.ENCRYPT_AES128 Advanced Encryption Standard. Block cipher. Uses 128-bit key size.
DBMS_CRYPTO.CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext block before it is encrypted.
DBMS_CRYPTO.PAD_ZERO Provides padding consisting of zeroes

So far, so good.

Ensure the pgcrypto extension is installed in PostgreSQL:

In psql:

=> \dx
 hstore  | 1.4     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 
=> create extension if not exists pgcrypto;
CREATE EXTENSION
 
=> \dx
 hstore   | 1.4     | public     | data type for storing sets of (key, value) pairs
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language

Looking at the docs for the raw base functions in pgcrypto, it suggests that the default settings for encrypt/descrypt will use MODE=CBC and PADDING=PKCS (any length) which suggests zero padding but that’s a guess. So I think it should match Informatica, at least according to the docs.
See https://www.postgresql.org/docs/9.5/pgcrypto.html

And yet, using the following test code yields an error:

=> select pgp_sym_decrypt( decode('BI830ZHd2kimJTfMD8E07mSTa8HDUbU/lbKFUDl73FrqI6uwAH7oCwPRGjxUkUZw','base64'), 
                           'Abc123Abc123Abc123', 
                           'cipher-algo=aes128' );

ERROR:  Wrong key or corrupt data

In this example, I’ve replaced the key text with a random string value – we didn’t actually use that value.

I am pretty confident that the encryption key is correct; less confident that the cipher text is not corrupt, but in simple tests where I DO have confidence, we still get the same error. Where does this leave us? With data we can’t decrypt, unless we can figure out the difference.

Of course, we can encrypt and decrypt values successfully if we stay in the DB domain and use only the PostgreSQL pgcrypto API to perform the encryption. It’s only data coming from Informatica that is giving us trouble.

Assuming the process that is extracting data from Informatica is not corrupting the data, it has to be some difference between the two encryption algorithms.

I’ll continue to research this and if we find a solution, update this post, of course.

Bi-directional Host-only networking with Virtualbox

I thought I already had a post about this, but I can’t find it, so where we go again.

It has been a while since I set up a guest OS and configured it for networking between Host and Guest. Of course, we also want the Guest to be able to see the Internet…

Environment:

Host OS : Windows 10
Guest OS: Linux Mint

Important Configuration Steps:

The Host Virtualbox need host-only networking enabled
The Guest OS needs a second network adaptor enabled, for host-only networking
The Guest OS needs MANUAL IP configuration
The Host OS needs a Firewall adjustment

1. Enable Host-Only networking in Virtualbox

Under Global Tools, or File > Host Networking Manager, or Ctrl-W:

I use the default base address range of 192.168.56.x and the host IP of .1.

I also set up the DHCP server to dispatch addresses in the 101-254 range.

2. Add a second adaptor to the Guest OS

By default the network adaptor in the Guest is NAT. We want to leave this one unchanged, and click on the Adapter 2 tab:

Enable the adapter and select “Host-only adapter”.

3. Manually configure the Guest OS IP settings on the new adaptor

At this point we should be able to ping the host:

colin@golem:~$ ping 192.168.56.1
PING 192.168.56.1 (192.168.56.1) 56(84) bytes of data.
64 bytes from 192.168.56.1: icmp_seq=1 ttl=128 time=0.254 ms
64 bytes from 192.168.56.1: icmp_seq=2 ttl=128 time=0.762 ms
64 bytes from 192.168.56.1: icmp_seq=3 ttl=128 time=0.655 ms
^C
--- 192.168.56.1 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2047ms
rtt min/avg/max/mdev = 0.254/0.557/0.762/0.218 ms

And, indeed we can. Even though the network will be available, the adaptor will probably have obtained an IP from the DHCP server, e.g. 192.168.56.101.

Go to the Network Connections dialog and edit the settings on the second network connection. (I usually rename my Ethernet connections so as to clearly identify which is WAN and which is LAN):

For the “Host Network” adaptor, under IPV4 Settings, configure a manual static IP address.

I use 192.168.56.56 because it is easy to remember.

At this point, you may need to restart the networking subsystem to respect your changes. I usually just log out and re-login to the Guest desktop at this point.

Check the IP settings using ifconfig :

colin@golem:~$ ifconfig
enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 10.0.2.15 netmask 255.255.255.0 broadcast 10.0.2.255
...

enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.56.56 netmask 255.255.255.0 broadcast 192.168.56.255
...

Good, looks like it is respecting the static IP we want.

At this point, in the Host OS, try pinging 192.168.56.56. It might work fine… in which case you can stop here. But if you encounter problems communicating with the Guest, you may need to relax the Host firewall:

4. Adjust the Host’s firewall

Seeing as this network only exists virtually on my computer, I want to take the simple way out and just tell my host firewall to get out of the way of this specific network.

In Windows 10, bring up the Windows Defender Firewall and select “Advanced Settings” from the left panel.

Select “Windows Defender Firewall Properties”

Select Private Profile and Public Profile tabs, and select “Customize”

Uncheck “VirtualBox Host-Only Network”

At this point, you should be able to communicate between Host and Guest in either direction, using any protocol you want.

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?

If 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.

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:

Install SQL Server and create a database on Ubuntu

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:

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: \/.