Moving from SQL Server to PostgreSQL

SQL Server and PostgreSQL are both relational database systems and as such share similarities that should allow you to migrate your data structures between them. In today’s post, I’m going to go through the process of migrating SQL Server data schema objects over to PostgreSQL.

Immediate differences

Whilst both relational database systems implement a core set of the standard language, there are implementation-specific features which need special consideration. So long as you are capable of wrangling text in your favorite editor, the conversion task shouldn’t be that hard.

The batch terminator GO gets replaces by a much more familiar ;.

Tables

First thing to do for tables is to generate your create scripts. Make sure that you:

• Turn off DROP statement generation for your objects
• Turn on index and keys generation

To safely qualify the names of objects within the database, SQL Server will surround its object names with square brackets [], so you’ll see definitions like this:

-- generated from SQL Server

CREATE TABLE [dbo].[Table1] (
    [ID]          INT IDENTITY (1, 1) NOT NULL,
    [Name]        VARCHAR (50) NOT NULL
    CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([ID] ASC)
)

PostgreSQL uses double-quotes on object names and doesn’t use the owner (in the above case [dbo]) to qualify names.

In the above example, Table1 is using IDENTITY on its primary key field ID. This gives us the auto-increment functionality that’s so natural in relational database systems. There is a little extra work in PostgreSQL to emulate this behavior through the use of CREATE SEQUENCE and nextval.

The SQL Server definition above now looks like this for PostgreSQL:

-- migrated for PostgreSQL

CREATE SEQUENCE Table1Serial;

CREATE TABLE Table1 (
    ID          INT NOT NULL DEFAULT nextval('Table1Serial'),
    Name        VARCHAR (50) NOT NULL,
    CONSTRAINT PK_Table1 PRIMARY KEY (ID)
);

Stored Procedures

Stored procedures in SQL Server are considered a much more common citizen in the database world than Stored procedures in PostgreSQL. If your database design hinges on extensive use of stored procedures, you’ll be in for a bit of redevelopment.

Both stored procedures and functions are created using the same syntax in PostgreSQL. The actions that either can perform differ though:

A simple function that will square its input value looks as follows:

CREATE OR REPLACE FUNCTION SquareNum(n INT) RETURNS INT AS $$
  BEGIN
    RETURN n * n;
  END;
  $$ LANGUAGE plpgsql;

This can be invoked using SELECT.

SELECT SquareNum(5);

A more in-depth example involves returning a result set from within a stored procedure. You can do this in an unnamed fashion; you won’t control the name of the cursor coming back.

You can pull out a single record set:

CREATE OR REPLACE FUNCTION retrieve_entries() RETURNS refcursor AS $$
  DECLARE
    ref refcursor;
  BEGIN
    OPEN ref FOR SELECT id, name FROM table1;   
    RETURN ref;                                                       
  END;
  $$ LANGUAGE plpgsql;

Multiple record sets:

CREATE OR REPLACE FUNCTION show_entities_multiple() RETURNS SETOF refcursor AS $$
  DECLARE
    ref1 refcursor;           
    ref2 refcursor;                             
  BEGIN
    OPEN ref1 FOR SELECT id, name FROM table1;   
    RETURN NEXT ref1;                                                                              

    OPEN ref2 FOR SELECT id, name FROM table2;   
    RETURN NEXT ref2;
  END;
  $$ LANGUAGE plpgsql;

Invoking these stored procedures so that you can gather the information being returned, requires you to FETCH these details:

BEGIN;
SELECT retrieve_entities();
FETCH ALL IN "<unnamed portal 2>";
COMMIT;

Re-writing retrieve_entities, we can give the caller the option to name their cursor:

CREATE OR REPLACE FUNCTION retrieve_entities(ref refcursor) RETURNS refcursor AS $$
  BEGIN
    OPEN ref FOR SELECT id, name FROM table1;   
    RETURN ref;
  END;
  $$ LANGUAGE plpgsql;

The invocation of this procedure now requires a name:

BEGIN;
SELECT retrieve_entities('entities_cur');
FETCH ALL IN "entities_cur";
COMMIT;

A much more comprehensive run down of stored procedures/functions can be found here and here.

Views

Views fall into the same category as Tables. The syntax remains very much the same with functions that change between the database platforms.

Detaching running processes in bash

There are quite a few times where I’ve run a command on a remote machine and needed to get out of that machine but leave my command running.

I’ll normally start a job that I know is going to take a while using an ampersand like so:

$ long-running-prog &

Really, the nohup command should also be put on the command line so that the command that you execute will ignore the signal SIGHUP.

$ nohup long-running-prog &
$ exit

If you’re already part-way through a running process, you can get it to continue running in the background (while you make your getaway) by doing the following

$ long-running-prog
CTRL-Z
$ bg
$ disown pid

You use CTRL-Z to suspend the running process. The bg command then gets the program running in the background. You can confirm that it is running in the background with the jobs command. Lastly, using disown detatches the process running in the background from your terminal, so that when you exit your session the process will continue.

The LDP has a great article on job control.

Using wcc386 and tasm together

It’s possible to use the Watcom compiler to mix your code with modules compiled (or in this article’s case, assembled) with other tools. In today’s post, I’ll take you through the simple process of creating a module using Borland’s Turbo Assembler and linking it with a simple C program.

Creating a test

First thing to do, is to create an assembly module that we can integrate with. For this module, we’re going to take two numbers; add them together and send out the result.

; adder.asm
;
; Assembly module to add two numbers

.386p
.MODEL  FLAT

_TEXT SEGMENT DWORD PUBLIC 'CODE'
    ASSUME CS:_TEXT

    PUBLIC add_numbers

    add_numbers PROC NEAR
        push    ebp
        mov     ebp, esp

        ARG     A:DWORD, B:DWORD

        mov     eax, [A]
        mov     ecx, [B]
        add     eax, ecx

        mov     esp, ebp
        pop     ebp

        ret
    add_numbers ENDP

_TEXT ENDS
END

This is a basic module, with most of the stack-balancing work being handled for us by the ARG directive. From the documentation:

ARG is used inside a PROC/ENDP pair to help you link to high level languages. Using ARG, you can access arguments pushed onto the stack.

Also from the documentation:

In the code that follows, you can now refer to PAR1 or PAR2, and the correct [BP + n] expression will be substituted automatically by the assembler.

Of course, we could have just as easily used the following without needing the ARG directive:

mov    eax, [ebp + 12]
mov    ecx, [ebp + 8]

In accordance with the 32bit ABI, we put the result in EAX at the end of execution. Producing an object file from this assembly source is relatively easy:

C:\SRC> tasm /mx /zi /os adder.asm adder.obj

Integrating with the module

Now that we’ve got an object file with our function in it, we’ll create a very small, simple C program that will use this function. In order to do so though, we need to declare the function as an extern; as it is implemented externally to our C code:

/* test.c
 *
 * Assembly module usage
 */
#include <stdio.h>

/* Here's our externally implemented function */
int add_numbers(int a, int b);

int main(int argc, char *argv[]) {
    printf("2 + 3 = %d\n", add_numbers(2, 3));
    return 0;
}

Because we’re using C, there’s no need to really decorate the function prototype of add_numbers. Had we been compiling a C++ module, this declaration would need to change slightly to attribute the calling convention:

extern "C" {
    int add_numbers(int a, int b);
}

This module is now ready to be compiled itself and linked to the assembly implementation. We can achieve this with wcc386 and wlink to tie it all together for us.

C:\SRC> wcc386 /d2 /3s test.c
C:\SRC> wlink system dos4g file test,adder name test

From there, we have a linked and read to run executable test.exe.

Docker, Chrome and Ubuntu

Docker provides a very convenient way of packaging your applications and their dependencies so that they can be moved around without too much effort. Another great side-effect of this type of system design is the isolation that you’re given between containers. In today’s post, I’ll walk through the setup of Google Chrome running in an isolated sandbox within Docker and so that it’s nicely integrated into Ubuntu.

Not starting from zero

I have to admit, most of the hard work had already been done for me in Jessie Frazelle’s post about hosting desktop applications in docker containers. The Chrome Dockerfile that I have hosted in my github repository is a pretty good rip, directly from Jessie’s post.

Getting started

Putting together a run script that you can repeatedly call from the operating system shouldn’t be too hard. It only needed to do three things:

• Create a container when one didn’t exist
• Start the container if it already existed
• Open a new window if the container was already started

This is a relatively simple bash script to do this:

CHROME_RUNNING=$(docker inspect --format="" chrome 2> /dev/null)

We want to check if the container is running, first up. I’ve standardised by calling the container chrome. Really creative. Upon successful return from the docker inspect command, the $CHROME_RUNNING variable should either be true or false. If the inspect call didn’t go to plan, it’s most likely because the container doesn’t exist and we need to use run to kick it into gear:

docker run \
    -it \
    --net host \
    --cpuset-cpus 0 \
    --memory 512mb \
    -v /tmp/.X11-unix:/tmp/.X11-unix \
    -e DISPLAY=unix$DISPLAY \
    -v $HOME/Downloads:/root/Downloads \
    -v $HOME/.config/google-chrome/:/data \
    --device /dev/snd \
    -v /dev/shm:/dev/shm \
    --name chrome \
    tuttlem/chrome

This gets the container up and running and the browser under our noses.

In cases where the container already exists, but isn’t running we’ll use run. When the container exists and it is running, the only reason why someone could be invoking this script is to get another browser window running; so we’ll use exec to get chrome to open up a new window for us:

if [ "$CHROME_RUNNING" == "false" ]; then
    docker start chrome
else
    docker exec chrome sh -c "/usr/bin/google-chrome '$@' --user-data-dir=/data"
fi

By using the $@ variable in the exec script, we can take in any web address that’s passed into this script. This is what will allow us to integrate this container into our operating system.

Integration

We’ve done just about everything now with the run script. I’ve created myself a menu item with a chrome icon that just points to this run script:

The main key binding of Super + W, but the most important is changing the preferred browser so that it invokes the script. %s passes the desired web site through for a seamless finish.

SSH key setup for remote login

Setting up passphrase-less login to your SSH servers is a convenient way of logging into your servers without being annoyed for a passphrase. In today’s post, I’ll take you through generating a key, distributing your identity and logging on.

Generating your key

If you haven’t done so already, you’ll need to generate some authentication keys for yourself. You can do this with ssh-keygen.

ssh-keygen -t rsa

The output of which will look like this:

root@64ed9b1beed9:~# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
b6:ff:5f:1b:88:12:7e:3d:5f:28:c9:66:fb:a7:21:6a root@64ed9b1beed9
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
|                 |
|                 |
|                 |
|        S.       |
|       .....o... |
|        .o oB+o.o|
|         .E+ +oo=|
|         .o.oo+= |
+-----------------+

Now that this process has completed, you’re given a public key and private secret key in your .ssh/ folder.

Distribute your identity

To deploy your public key to other servers so that you can authenticate using your private key, you can use ssh-copy-id.

$ ssh-copy-id remote-user@remote-host

You’ll want to swap out remote-user for the user that you’re associating your key to and remote-host with the machine that you want to connect to.

Another way that you can establish your key into the remote machine’s authorized set is as follows:

cat ~/.ssh/id_rsa.pub | ssh example@123.123.123.123 "mkdir -p ~/.ssh && cat >>  ~/.ssh/authorized_keys"

You’ll then be taken through the verification process, which is just supplying your remote password:

The authenticity of host '123.123.123.123 (123.123.123.123)' can't be established.
ECDSA key fingerprint is ae:2d:33:79:e9:d8:03:16:6c:17:d3:f2:7e:c4:05:60.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
example@123.123.123.123's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'example@123.123.123.123'"
and check to make sure that only the key(s) you wanted were added.

Conclusion

You’re free to login. Of course, if you don’t set a pass phrase for your keys you won’t be hassled all the time to unlock them. If you do set a pass phrase, your overall solution will be just a bit more secure.