Useful PostgreSQL Queries Compared with MySQL

Recently while creating an SQL script for PostgreSQL, I found that some of the queries where not straightforward, specially if you are used to MySQL. I'm listing some of them here in case someone finds it useful.

Auto Increment

In MySQL you usually use AUTO_INCREMENT attribute as follows.

CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT,
Name varchar(255)
)

In PostgreSQL a similar result can be obtained by using SERIAL data type.

CREATE TABLE users (
id SERIAL
);

SERIAL is not an actual type, it is just a keyword used for convenience which is equivalent to specifying:

CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
id integer DEFAULT nextval('users_id_seq') NOT NULL
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL

Running Multiple Instances of ActiveMQ on the same server

To run multiple instances of Apache ActiveMQ on the same server, it is required to do some configuration changes.

First, go to ActiveMQ installation directory.

cd ~/apache-activemq-5.8.0

Then create instance1 and instance2 by running the following commands.

./bin/activemq create instance1
./bin/activemq setup ~/.activemqrc-instance-instance1
ln -s activemq bin/activemq-instance-instance1

./bin/activemq create instance2
./bin/activemq setup ~/.activemqrc-instance-instance2
ln -s activemq bin/activemq-instance-instance2

Using PostgreSQL with WSO2 BAM

WSO2 Business Activity Monitor (BAM) can be used to address monitoring requirements in business activities and processes. It supports aggregating, analyzing and presenting information about business activities.

WSO2 BAM architecture is designed to handle these steps through its different modules.  Data Receivers in WSO2 BAM receive data and store them in a Cassandra data store. The Analyzer Engine will run analytics according to defined Hive queries and stores the result in a RDBMS data store. Finally the analyzed data will be fetched from the data store and shown in dashboards or reports.

This post will show how to integrate PostgreSQL into WSO2 BAM, which could be used to store analyzed data.

Using Admin Services in WSO2 Carbon

Products based on WSO2 Carbon platform provide the Management Console user interface to handle administrative tasks. These Admin Services are implemented as SOAP web services. So you can invoke these web services directly to perform certain tasks. This is useful when you do not want to go through the UI or when you need to automate such tasks.

First, you need to find out the list of available Admin Services. This can be done through the OSGi console of Carbon products. For that start the server with -DosgiConsole option.

./wso2server.sh -DosgiConsole

After startup, enter listAdminServices command in the osgi prompt. Then it will list a set of Admin Services available to that server as follows.

Add a Separator Between Terminal Commands

After running many commands in the terminal, it sometime gets difficult to read the outputs. I've seen many people hitting 'Enter' repeatedly just to separate those outputs. This neat trick would add a nice separator between each command and also makes the command bold. In addition to that it also adds a time-stamp at the end. The time-stamp is useful in knowing when a particular long-running command completed.

So the final result would look like this.

Easily Enable/Disable SVN Proxy

To configure SVN proxy settings you need to edit the servers file in svn. In Linux, this can be found at ~/.subversion/servers.

Open this file in a text editor and change the settings as following by including the proxy host and port under the [global] section. Uncomment those two lines and remove any leading spaces.

[global]
# http-proxy-exceptions = *.exception.com, www.internal-site.org
http-proxy-host = proxy.host.com
http-proxy-port = 1234
# http-proxy-username = defaultusername
# http-proxy-password = defaultpassword
# http-compression = no
# http-auth-types = basic;digest;negotiate
# No http-timeout, so just use the builtin default.
# No neon-debug-mask, so neon debugging is disabled.
# ssl-authority-files = /path/to/CAcert.pem;/path/to/CAcert2.pem

If you get any errors, make sure that there are no spaces at the start of those lines.

For most of us, proxy is used only at school/university/work. Everywhere else you don't use proxy. So Most of the time you need configure the settings once. But you will have to enable/disable proxy every time. That means opening this file in an editor and commenting/uncommenting manually every single time.

After getting fed up with doing this every time I move to and from university, I came up with a better way to do this.

Custom Launcher for IntelliJ IDEA in Ubuntu Unity

IntelliJ IDEA does not install a program launcher during installation. You only get a start-up script. So you need to create a custom launcher to quickly and easily run the program without going to the terminal.

In previous versions of Ubuntu we had this to quickly create a launcher.

But in Unity it is not so easy.