More than Four

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

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

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.

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:

1. The Host Virtualbox need host-only networking enabled
2. The Guest OS needs a second network adaptor enabled, for host-only networking
3. The Guest OS needs MANUAL IP configuration
4. 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
        ...

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.

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;

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;

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.

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;
}

• http://openlivewriter.org/
• http://lvildosola.blogspot.com/2009/03/code-snippet-plugin-for-windows-live.html
• http://www.hardcodet.net/2009/09/live-writer-plugins-to-format-content-through-css-classes
• https://www.w3schools.com/cssref/sel_id.asp

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.

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:

• Install SQL Server Integration Services (SSIS) on Linux
• Extract, transform, and load data on Linux with SSIS

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

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%

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

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?

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”

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

Thanks, Andrew Joe!