Spacemacs as database client

One of the first thing I did in my journey of trying to learn some Spacemacs was to make it a database client. After some work with the author of ejc-sql we got it to work with SQL Server and instances by using full connection URI. Should work great with any db, but as we all know SQL Server is a bit special. So I wrote this post to make it easier for others to find a way to work with SQL Server.

Thanks to kostafey and his work on ejc-sql and for helping me out to get it to work with SQL Server and instances. https://github.com/kostafey/ejc-sql

I’m in no meaning any expert in Spacemacs/Emacs and it’s configuration, so there might be way better options for this.

You should probably install Leiningen as described at ejc-sql first. The documentation is good, all of what I write is explained there.

So first of we need to add the package:

lisp
dotspacemacs-additional-packages '(ejc-sql)

Then it’s time to add a connection for your database under dotspacemacs/user-config, that is important. And don’t forget the require, mistake I made since I’m not very familiar with the syntax.

lisp
(require 'ejc-sql)
(ejc-create-connection
"SQL Server instance 01"
:classpath (concat
"/home/mynick/.m2/repository/com/microsoft"
"/sqlserver/sqljdbc4/4.2/sqljdbc4-4.2.jar")
:classname "com.microsoft.sqlserver.jdbc.SQLServerDriver"
:connection-uri (concat
"jdbc:sqlserver://mydomain.se\\instance01:4001;"
"databaseName=db_name;"
"user=sa;"
"password=S3cr3t"))

Don’t forget to double check your TCP port, in my case it’s 4001. You can see how to find it out here: https://msdn.microsoft.com/en-us/library/ms177440.aspx.

If your more familiar with JTDS, it’s fine to use that as well:

lisp
(require 'ejc-sql)
(ejc-create-connection
"SQL Server instance 01"
:classpath (concat
"/home/mynick/.m2/repository/"
"net/sourceforge/jtds/jtds/1.3.1/jtds-1.3.1.jar")
:classname "net.sourceforge.jtds.jdbc.Driver"
:connection-uri (concat
"jdbc:jtds:sqlserver://mydomain.se:1433/db_name;"
"instance=instance01;"
"user=sa;"
"password=S3cr3t"))

The configuration isn’t harder than that.

Here’s some commands, running Evil mode, hence the capital V.

First you need to connect to a configured database:
SPC-SPC ejc-sql-mode
SPC-SPC ejc-connect

I often has multiple SQL queries in the same buffer, so when I run a sql-statement I do:
V C-x S

That’s it.

Array with promises

First up, the problem: I needed to build an array of items based on multiple roles for a user in an Angular application. And the returning value had to be a promise. So first attempt was to build up the array with if-statements and Array.push, not very beautiful nor immutable, and then return it with $q.when(array).

So I figured that this could be done with multiple promises instead. So here’s what I came up with, fun with trolls and ogres.

Pretty self explaining, ey?

Sometimes I see trolls when in the forest chopping firewood. Please come with an even more elegant way! πŸ™‚

Strings behind the scene

What’s really happening behind the scenes with the strings you create?

I will try to show what’s happening and what to think of when generating a string. And to visualize it I will use log messages.

Often I see log messages where the whole message is created as it should be logged. This is a pretty bad idea in performance perspective, since it always have to generate the string no matter what, even if the log message is discarded. So I will show you what the Java compiler actually does with the string messages you create.

But this will not be another performance comparison blog post. I will just try to show what’s happening behind the scenes with some byte code, then you will have to make your own assumptions. Of course I will tell you my point of view πŸ™‚

Let’s start with a more or less common examples.

The interesting part of the byte code is what’s happening within the loop:

It creates a new StringBuilder in each iteration and appends the logStr (ALOAD 2) and then appends the variable value. ASTORE 2 simply means that it stores the toString value to logStr. You don’t have to understand the byte code, the important thing is that it creates a StringBuilder in each iteration and makes an extra append-call, simply unnecessary and might be a performance issue in larger systems.

Let’s take another example with the string + operator.

This is a very common approach of creating log messages. The compiler simply creates a StringBuilder and each plus-operator is basically translated to an append-call.

Pretty neat, huh? The Java compiler is not that stupid after all. You might have heard someone say “don’t use + when working with strings“, that was true, back in Java 1.4, but now the compiler is a bit smarter πŸ™‚

So lets see what happens if we use a StringBuilder directly, instead.

And the byte code:

Not much to say. Almost identical to logWithConcat. Just some more line number changes.

So lets look at something else, a log message using parameters instead.

So this is compiled to creation of an array and then just a method invocation. In other words, this is probably the fastest solution in my examples.

So what do I want to tell you with this? As you can see the Java compiler always uses the StringBuilder (stop using StringBuffer!!) when it creates the strings for you.

Of course it’s not totally free to generate the string with a StringBuilder. That’s why all (good) logging frameworks also has the solution with the string format pattern. That will not produce anything else than one method call and possibly an array creation, but that’s pretty fast. Some frameworks also defines methods that actually takes two arguments instead of an array to speed up those logging messages that only takes two arguments, then no array is created. Some might even have more than that.

And then, of course, some logging frameworks has been adding support for Java 8 suppliers and lambdas, that is probably the sweetest solution πŸ™‚

Well, you get the point πŸ™‚ Happy string creation.

You may as well see this post at http://teknikbloggenhrm.blogspot.se/2016/04/string-behind-scenes.html

Observables with SQLiteOpenHelper

Lets look at some code on how to use Observables with SQLiteOpenHelper.

I suppose you know how to work with SQLite in Android. Otherwise, take a look at http://developer.android.com/training/basics/data-storage/databases.html

So lets assume we got this class Database with whatever needed to create any tables needed.

The code below shows how to make a query and an insert. As you can see the query and insert is just like always, the only different is the creation of an observable and notification to the subscriber when query/insert is done.

So those methods can then be used asynchronous and the subscription is notified when done. The subscribeOn is taking a scheduler where the above methods will be executed, and observeOn where the subscription is executed (the code in .subscribe). AndroidSchedulers.mainThread() must be used if updating the UI.

Now I will show you a pretty neat feature that I often use when chaining observables, the flatMap. The code below writes the name to the database, the returned id is then passed to the get-method.

So that’s a way of using observables with Android and SQLlite.

Dependencies used:
me.tatarka:gradle-retrolambda:3.2.3
io.reactivex:rxandroid:1.1.0