1. 2017-07-21 - Temporary Tables; Tags: Temporary Tables

    Temporary Tables

    Temporary tables exist in a special schema. If specified in the create table statement, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction. .. Any indexes created on a temporary table are automatically temporary as well.


    • Parsing street name with number and separate them in different columns.
    • Parsing with regexp_matches for street names in the German language.
      --drop table tempstreet;
      create temporary table tempstreet(id integer, data text[2]);
      insert into tempstreet 
           select customer_id, 
                  regexp_matches(street, (^[A-ZÄÖÜa-zäöüß[A-ZÄÖÜa-zäöüß(-{}| )]+[.| |-])[ ]?([1-9][0-9]{0,3}[ ]?[a-z]?)')
             from crm.addresses;

      Update the data from the temporary table to update the table crm.addresses

      update crm.addresses set street=data[1] from tempstreet where tempstreet.id = customer_id;
      update crm.addresses set hausnr=data[2] from tempstreet where tempstreet.id = customer_id;
    • 2016-09-27 - Run Sonarqube with Docker and PostgreSQL; Tags: Run Sonarqube with Docker and PostgreSQL

      Run Sonarqube with Docker and PostgreSQL

      A long time ago (seems like ages to me) I have programming in Java and let my projects analyze with Sonarqube. I always remembered that every Sonarqube upgrade wasn’t quick to made. Since Docker I now have the possibility to run the latest stable Sonarqube version. No manual upgrades anymore. Sounds wonderful. Following installation was made on my Linux Box running Ubuntu 16.04.01 LTS with Docker 1.11.2 and PostgreSQL 9.5.

      PostgreSQL Installation

      First I need PostgreSQL for Sonar to store its data.

      >sudo apt-get install postgresql

      Initial setup

      Login as postgres user and alter the password for the database user

      sudo -u postgres psql template1 
      ALTER USER postgres WITH PASSWORD 'fancypassword';

      Move data directory

      As default the data directory is defined in /var/lib/postgresql/9.5/main. This is my SSD. Data can be stored on my regular HDD. Therefore move it to /home/postgresql.

      This step is not necessary :smirk:.

      Create database

      We need a user for Sonarqube. I just named it sonar.

      sudo -u postgres createuser -D -P sonar

      The options explained:

      * `-D` → The new user will not be allowed to create databases
      * `-P` → Password prompt

      Now we create the database for sonar and assign the encoding and user. I just choose the innovative name sonar :smile:.

      sudo -u postgres createdb sonar --encoding=UTF-8 --owner=sonar

      Sonarqube with Docker

      Now we need to pull the Docker image from https://hub.docker.com/_/sonarqube/. I chose the image variant lts-alpine. This image is based on the popular Alpine Linux project, available in the alpine official image. Alpine Linux is much smaller than most distribution base images (~5MB), and thus leads to much slimmer images in general.

      sudo docker pull sonarqube:lts-alpine

      Testing and troubleshooting

      If we run it interactively with the settings for PostgreSQL:

      $ docker run -it --name sonarqube \
          -p 9000:9000 -p 9092:9092 \
          -e SONARQUBE_JDBC_USERNAME=sonar \
          -e SONARQUBE_JDBC_PASSWORD=sonar \
          -e SONARQUBE_JDBC_URL=jdbc:postgresql://localhost/sonar \

      We get an error :-o

      2016.09.26 20:19:45 INFO  web[o.sonar.db.Database] Create JDBC data source for jdbc:postgresql://localhost:5432/sonar
      Caused by: org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

      Fixing connection problem

      The connection between Docker and PostgreSQL doesn’t work. Docker has its own interface with IP assigned.

      tan@omega:~$ ifconfig 
      docker0   Link encap:Ethernet  HWaddr 02:42:3d:4c:f3:00  
                inet addr:  Bcast:  Mask:
                inet6 addr: fe80::42:3dff:fe4c:f300/64 Scope:Link
                UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
                RX packets:18707 errors:0 dropped:0 overruns:0 frame:0
                TX packets:18753 errors:0 dropped:0 overruns:0 carrier:0
                collisions:0 txqueuelen:0 
                RX bytes:8669821 (8.6 MB)  TX bytes:9622716 (9.6 MB)

      Therefore PostgreSQL has to allow this IP range. Add the line for 172.17.x.x in /etc/postgresql/9.5/main/pg_hba.conf:

      # IPv4 local connections:
      host    all             all               md5
      host    all             all              md5

      Now we need to apply the changes, either with a simple reload

      sudo -u postgres /usr/lib/postgresql/9.5/bin/pg_ctl -D /home/postgresql/9.5/main reload

      or just simply restart the service. Basically it isn’t necessary. But since it is a local non-shared installation:

      /etc/init.d/postgresql restart

      Tuning docker run

      The official run command has some flaws. For portability you shouldn’t use localhost. You can add the option --add-host=database to expose your database host IP to docker.

      Furthermore we need to map the declared volumes for data and extensions. Otherwise all changes or installed plug-ins are gone after the docker container stops. I decided to put everything in home directory.

      mkdir -p sonar/data sonar/extensions
      sudo docker rm sonarqube && sudo docker run -d --name sonarqube \
       -p 9000:9000 -p 9092:9092 \
       -v /home/tan/sonar/data:/opt/sonarqube/data \
       -v /home/tan/sonar/extensions:/opt/sonarqube/extensions \
       -e SONARQUBE_JDBC_URL=jdbc:postgresql:// \
       --add-host=database: \

      If we run it interactively again, we see that the connection is ok and sonar creates some tables.

      2016.09.26 20:22:49 INFO  web[o.sonar.db.Database] Create JDBC data source for jdbc:postgresql://
      2016.09.26 20:22:57 INFO  web[DbMigration] ==  InitialSchema: migrating ==================================================
      2016.09.26 20:22:57 INFO  web[DbMigration] -- create_table(:projects, {})

      Some tables shown in DataGrip. postgresql-sonarqube

      Check Docker container

      If the container is running, docker ps shows you the information you need.

      tan@omega:~$ sudo docker ps
      CONTAINER ID        IMAGE                  COMMAND             CREATED             STATUS              PORTS                                            NAMES
      44a2292b59a6        sonarqube:lts-alpine   "./bin/run.sh"      17 minutes ago      Up 17 minutes>9000/tcp,>9092/tcp   sonarqube

      The wide output isn’t much readable, therefore we can format it more human readable.

      tan@omega:~$ sudo docker ps --format 'CONTAINER ID: {{.ID}}\nIMAGE: {{.Image}}\nCOMMAND: {{.Command}}\nCREATED: {{.CreatedAt}}\nSTATUS: {{.Status}}\nPORTS: {{.Ports}}\nNAMES: {{.Names}}'
      CONTAINER ID: 44a2292b59a6
      IMAGE: sonarqube:lts-alpine
      COMMAND: "./bin/run.sh"
      CREATED: 2016-09-26 22:30:14 +0200 CEST
      STATUS: Up 23 minutes
      NAMES: sonarqube

      Check if the docker container listens on the Sonar ports.

      tan@omega:~$ netstat -na | grep  ':9000\|:9092'
      tcp6       0      0 :::9092                 :::*                    LISTEN     
      tcp6       0      0 :::9000                 :::*                    LISTEN

      Setup Sonar

      Now we a running sonar instance, we need some plugins for code inspection. Go to http://localhost:9000 with your browser and login with the defaults admin/admin.

      Go to Administration → Update Center and install all the plugins you need, in my case:

      * Java
      * Checkstyle
      * Findbugs
          * PMD

      You need to restart the Sonar server, which can be done within the Administration web GUI.

      Analyze Maven Project

      If you use maven for a java project, you add the maven sonar plugin to your build section:


      Run code inspection

      mvn sonar:sonar

      It defaults to localhost:9000. At the bottom of the Maven output you will see some INFO messages.

      [INFO] Analysis report generated in 69ms, dir size=46 KB
      [INFO] Analysis reports compressed in 31ms, zip size=23 KB
      [INFO] Analysis report uploaded in 385ms
      [INFO] ANALYSIS SUCCESSFUL, you can browse http://localhost:9000/dashboard/index/net.cinhtau:ssh-demo

      Shutdown Sonar

      If you don’t need sonar simply stop your sonar container:

      sudo docker stop sonarqube