Thursday, January 19, 2017

Install MS SQL Server on Debian Jessie (through Docker)

Recently Microsoft released some previews of the Linux version of SQL Server.

At present the supported Linux distributions are only Red Hat Enterprise Linux 7.2, Ubuntu 16.04 and SUSE Linux Enterprise Server v12 SP2.

Installing it in other distributions (I'm using Debian Jessie) can be successful, but surely more difficult.

An alternative in these cases is using a Docker container.

Microsoft provides a Docker image for the server part, so running the server is as simple as typing the following command:
docker run -e 'ACCEPT_EULA=Y' \
    -e 'SA_PASSWORD=<yourstrong Passw0rd>' \
    -p 1433:1433 -v <host directory>:/var/opt/mssql \
    -d microsoft/mssql-server-linux
More difficult is the installation of SQL Server Tools, needed for connecting and querying the server. In this case Microsoft doesn't provide a Docker image, so you can only install them on the supported platforms.

Fortunately we can build a docker image by ourself.

Using the following Dockerfile:
FROM ubuntu:16.04
ENV DEBIAN_FRONTEND noninteractive
RUN apt-get update && apt-get install -y curl apt-transport-https debconf-utils apt-utils
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/msprod.list
RUN echo mssql-tools mssql-tools/accept_eula boolean Y|debconf-set-selections
RUN echo msodbcsql msodbcsql/accept_eula boolean true|debconf-set-selections
RUN apt-get update && ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev-utf16
RUN ln -sfn /opt/mssql-tools/bin/sqlcmd-13.0.1.0 /usr/bin/sqlcmd 
RUN ln -sfn /opt/mssql-tools/bin/bcp-13.0.1.0 /usr/bin/bcp
RUN locale-gen en_US en_US.UTF-8
RUN locale-gen it_IT it_IT.UTF-8
RUN dpkg-reconfigure locales
Build the docker image:
docker build -t mssql-tools:latest .
And run it typing:
docker run -it mssql-tools:latest /bin/bash
Now in this container you can user the MS SQL Tools for connecting to the running server:
sqlcmd -S <ms sql server container IP> -U SA -P '<yourstrong Passw0rd>'
For finding the IP of the container running the SQL Server, you can type the following command on your host:
docker inspect mssql-server|grep IPAddress

Monday, February 4, 2013

Using PostgreSQL in Java without JDBC

JDBC is the Java starndard API for accessing relational databases. There is any good reason for not using it, directly or through a higher-level API/framework (Hibernate, JPA, etc.)? Usually not, but if you feel you need, probably it's a good reason.

My own reason is related to pgpool-II and its parallel mode. As far as I know (please write to me if I'm wrong), pgpool's parallel mode doesn't support the PostgreSQL extended query sub-protocol. Unfortunately the PostgreSQL JDBC Driver can use only that protocol. Even setting the JDBC Driver for using the V2 protocol doesn't solve the problem because other issues in the communication with pgpool.

The only solution I found is not using JDBC, but accessing PostgreSQL through the libpq C library. Of course I need to do it in Java, so I wrapped the libpq library in Java classes, using the SWIG tool.

You'll find the code of my experiments in this Github repository.

Let's go through the full process.

  1. Install SWIG, downloading from here, or using the correct package of your OS. I use Debian, so I just needed to install the swig package:
    apt-get install swig
    
  2. Prepare a SWIG interface file, describing the C functions you want to wrap. If you want to wrap a whole library, this task could be very simple, as you can simply include the C header files:
    pqswig.i:
    %module pq
    %{
    #include "/usr/include/postgresql/libpq-fe.h"
    %}
    
    %include "/usr/include/postgresql/libpq-fe.h"
    
  3. Use the SWIG command for generating the Java wrapper classes and the C SWIG wrapper functions:
    swig -java \
        -package com.benfante.experiments.libpqwrapper.swig \
        -outdir \
        ../../../generated/java/com/benfante/experiments/libpgwrapper/swig \
        pqswig.i
    In the same directory you'll find the pqswig_wrapper.c file. In the indicated path (../../../generated/java/com/benfante/experiments/libpgwrapper/swig, change it following your needs) you'll find the Java wrapper classes.
  4. Compile the pqswig_wrapper.c file:
    gcc -c pqswig_wrap.c -I /usr/lib/jvm/java-6-sun/include -I /usr/lib/jvm/java-6-sun/include/linux
  5. Build the dynamic library:
    ld -G pqswig_wrap.o -o libpqswig.so -lpq
  6. Copy (or link) the libpqswig.o file into a directory of the java.library.path. For example, copy it into the /usr/lib directory:
    cp libpqswig.so /usr/lib/
    That's all. Of course the previous steps are for a Linux OS. If you are using a different OS, read the SWIG documentation and FAQs (in particular the SharedLibraries section).
Now you can execute code like the following:
package com.benfante.experiments.libpgwrapper;

import com.benfante.experiments.libpqwrapper.swig.ConnStatusType;
import com.benfante.experiments.libpqwrapper.swig.ExecStatusType;
import com.benfante.experiments.libpqwrapper.swig.SWIGTYPE_p_pg_conn;
import com.benfante.experiments.libpqwrapper.swig.SWIGTYPE_p_pg_result;
import com.benfante.experiments.libpqwrapper.swig.pq;

public class App {

    public static void main(String[] args) {
        System.out.println(System.getProperty("java.library.path"));
        System.loadLibrary("pqswig");
        System.out.println("PQ Library Version: " + pq.PQlibVersion());
        SWIGTYPE_p_pg_conn connection = connectToPostgres("localhost", "5432", "test", "test", "test");

        SWIGTYPE_p_pg_result res = selectAllPerson(connection);

        printTuples(res);

        pq.PQclear(res);

        pq.PQfinish(connection);
    }

    private static SWIGTYPE_p_pg_conn connectToPostgres(String host, String port, String db, String username, String password) {
        SWIGTYPE_p_pg_conn connection = pq.PQconnectdb("postgresql://" + username + ":" + password + "@" + host + ":" + port + "/" + db);
        if (pq.PQstatus(connection) != ConnStatusType.CONNECTION_OK) {
            System.out.println("Connection to database failed: " + pq.PQerrorMessage(connection));
            exitNicely(connection);
        }
        return connection;
    }

    private static SWIGTYPE_p_pg_result selectAllPerson(SWIGTYPE_p_pg_conn connection) {
        SWIGTYPE_p_pg_result res = pq.PQexec(connection, "SELECT * FROM person");
        if (pq.PQresultStatus(res) != ExecStatusType.PGRES_TUPLES_OK) {
            System.out.println("SELECT failed: " + pq.PQerrorMessage(connection));
            pq.PQclear(res);
            exitNicely(connection);
        }
        return res;
    }

    private static void exitNicely(SWIGTYPE_p_pg_conn connection) {
        pq.PQfinish(connection);
        System.exit(0);
    }

    private static void printTuples(SWIGTYPE_p_pg_result res) {
        int nTuples = pq.PQntuples(res);
        int nFields = pq.PQnfields(res);
        for (int i = 0; i < nFields; i++) {
            System.out.printf("%-15s|", pq.PQfname(res, i));
        }
        System.out.println("");
        for (int i = 0; i < nTuples; i++) {
            for (int j = 0; j < nFields; j++) {
                System.out.printf("%-15s|", pq.PQgetvalue(res, i, j));
            }
            System.out.println("");
        }
    }
}

Saturday, November 14, 2009

My Devoxx 2009

Devoxx banner
The following is my schedule for the Devoxx conference. In red the presentations I will attend. If you would like to suggest me other presentations you would like to hear about from me, send me a line and I will try to attend them.

Sunday, August 9, 2009

Build or simply compile?

With Parancoe I already have a very productive environment for the development of my applications. I can have unit tests on most of the code, so I rarely need a full build and redeploy.

But sometimes it would be nice (and necessary) to just code and try the result in the browser. With JavaRebel you can do this simply compiling the new code, without the need of a full build and redeploy of your application.

For example, in my current parancoe-based project, I modified a method in the the HomeController, and some code (even adding a new method) in the UserProfileBo class.

Then I just recompiled the project, in 3 seconds:


NetBeans: Executing 'mvn -Dnetbeans.execution=true compiler:compile'
NetBeans: JAVA_HOME =/usr/lib/jvm/java-6-sun
Scanning for projects...
Searching repository for plugin with prefix: 'compiler'.
------------------------------------------------------------------------
Building minimark Web Application
task-segment: [compiler:compile]
------------------------------------------------------------------------
[compiler:compile]
Compiling 2 source files to /home/lucio/MyWorks/minimark/minimark/target/classes
------------------------------------------------------------------------
BUILD SUCCESSFUL
------------------------------------------------------------------------
Total time: 3 seconds
Finished at: Sun Aug 09 08:14:56 CEST 2009
Final Memory: 18M/144M
------------------------------------------------------------------------


When I refreshed the page in the browser, the log of my Tomcat showed:


JavaRebel: Reloading class 'com.benfante.minimark.controllers.HomeController'.
JavaRebel-Spring: Reconfiguring bean 'homeController' [com.benfante.minimark.controllers.HomeController]
JavaRebel: Reloading class 'com.benfante.minimark.blo.UserProfileBo'.
JavaRebel-Spring: Reconfiguring bean 'userProfileBo' [com.benfante.minimark.blo.UserProfileBo]


Notice the classes not only have been reloaded, but also the spring-managed annotation-configured beans have been reconfigured.

The configuration of JavaRebel (in this case) is very easy.

In my Maven pom.xml:



org.zeroturnaround
javarebel-maven-plugin


generate-rebel-xml
process-resources

generate






The Tomcat configuration is:


CATALINA_OPTS="-XX:PermSize=32m -XX:MaxPermSize=200m -Xmx256m -Dfile.encoding=UTF-8 -noverify -javaagent:/home/lucio/local/javarebel-2.0/javarebel.jar -Drebel.spring_plugin=true"

Monday, May 25, 2009

Gregorio


Gregorio
Inserito originariamente da Lucio Benfante
Un benvenuto a Gregorio, fratellino di Carlo e Silvia, che gli fanno tanti auguri e non vedono l'ora che arrivi a casa.

Sì, al momento è tranquillo come appare, speriamo continui così

Tuesday, May 5, 2009

Mutable authorities with Spring Security and CAS

Recently I worked on an application with the following requisite:

The logged user must select its current role among the roles for which he's authorized.


A simple requisite, and (apparently) it's easy to implement it with Spring Security: write an UserDetails class in which you can select the returned authority(ies). For example:


public class LoggedUserWithSelectableRole extends User {
private GrantedAuthority currentAuthority;

public LoggedUserWithSelectableRole(String username, String password,
boolean enabled, GrantedAuthority[] authorities) throws IllegalArgumentException {
super(username, password, enabled, authorities);
}

public void setCurrentAuthority(GrantedAuthority currentAuthority) {
this.currentAuthority = currentAuthority;
}

@Override
public GrantedAuthority[] getAuthorities() {
if (Arrays.asList(super.getAuthorities()).contains(currentAuthority)) {
return new GrantedAuthority[] {currentAuthority};
} else {
return new GrantedAuthority[0];
}
}

public GrantedAuthority[] getAllAuthorities() {
return super.getAuthorities();
}
}


Now you can select an authority for the logged user (for example, in a controller):


@RequestMapping
public String selectRole(@RequestParam(value = "role") int role) {
LoggedUserWithSelectableRole user =
(LoggedUserWithSelectableRole) SecurityContextHolder.getContext().
getAuthentication().getPrincipal();
user.setCurrentAuthority(user.getAllAuthorities()[role]);
return "redirect:/";
}


Unfortunately this is not sufficient, as the authorities used by Spring Security for checking the user authorization are not (usually) stored in the principal object, but it the Authentication object.

It would be nice to write something like:


/* WARNING: The method setAuthorities doesn't exist */
SecurityContextHolder.getContext().getAuthentication().
setAuthorities(user.getAuthorities());


But the Authentication token is mostly immutable, so the setAuthorities doesn't exist. Worst, in the AbtractAuthenticationToken class, the base class of most of the token implementations, the authorities attribute is private, so you can't easily implement by yourself an alternative token implementation extending the original token class.

In our application we are using CAS. The only solution I found (as far as I know...please send me a line if you see a better solution) was to extend the CasAuthenticationToken, provinding a costructor for coping an existing token (of course of the same type):


public class UpdatableCasAuthenticationToken extends CasAuthenticationToken {

private final int keyHash;

public UpdatableCasAuthenticationToken(CasAuthenticationToken token, GrantedAuthority[] authorities) {
super("BOH", token.getPrincipal(), token.getCredentials(), authorities, token.getUserDetails(), token.getAssertion());
this.keyHash = token.getKeyHash();
}

@Override
public int getKeyHash() {
return this.keyHash;
}

}


As you can see, we also need to hide attributes and override methods not modifiable through the constructor of the base class.

Now I can substitute the original token with the modified one:


@RequestMapping
public String selectRole(@RequestParam(value = "role") int role) {
LoggedUserWithSelectableRole user =
(LoggedUserWithSelectableRole) SecurityContextHolder.getContext().
getAuthentication().getPrincipal();
user.setCurrentAuthority(user.getAllAuthorities()[role]);
SecurityContextHolder.getContext().setAuthentication(
new UpdatableCasAuthenticationToken(
(CasAuthenticationToken) SecurityContextHolder.getContext().getAuthentication(),
user.getAuthorities()));
return "redirect:/";
}


As CAS ha no concerns with the user roles, I think CasAuthenticationToken should provide a way for updating authorities, and maybe the authorites attribute of AbstractAuthenticationToken should be declared as protected.

Sunday, March 8, 2009

Cantucci

Oggi ho provato un nuovo "algoritmo" culinario: i cantucci. Ok, siamo un po' fuori stagione...ma non si può mica sempre aspettare Natale, no? :)

La ricetta originale l'ho presa da sito Misya.info, con gli opportuni adattamenti.

Partiamo dagli ingredienti:Ingredienti per i cantucci
  • 270g di farina "0"

  • 200g di zucchero

  • un cucchiaino abbondante di lievito in polvere

  • un bacello di vaniglia

  • due uova intere

  • 150g di mandorle

Sbattere le uova.Uova da sbattereI "diti" dei cantucci
I "diti" dei cantucciAggiungere la farina, lo zucchero, il lievito, il contenuto del bacello di vaniglia e un pugno di mandorle.Impastare fino ad ottenere un impasto abbastanza sodo, eventualmente aggiungendo un po' di farina se dovesse risultare troppo appiccicoso.

Aggiungere la scorza dell'arancia e le mandorle. Formare due "diti" su una teglia ricoperta da carta da forno.


Tagliare i "diti"Cuocere in forno per circa 30 minuti a 180°C.

Tagliare i diti diagonalmente per formare i cantucci.

Infornare per un'altra quindicina di minuti, per far asciugare bene anche l'interno.
Mangiare. :P
I cantucci sono prontiI miei cantucci