PostgreSQL crosstab query - Rotate a table about a pivot

An interesting feature of relational databases(postgres in this case) is the ability to rotate the table about a pivot. So if you have data like this-
 id | rowid | key | value
---+------+----+-------
  1 | test1 | key1      | val1
  2 | test1 | key2      | val2
  3 | test1 | key3      | val3
  4 | test1 | key4      | val4
  5 | test2 | key1      | val5
  6 | test2 | key2      | val6
  7 | test2 | key3      | val7
  8 | test2 | key4      | val8

And want to have a result set like this -

rowid | key1 | key2 | key3 | key4
------+------+-----+-----+------
 test1  | val1  | val2  | val3  | val4
 test2  | val5  | val6  | val7  | val8


It can be achieved by a "crosstab" query in a postgres database -

	SELECT * FROM crosstab(
	'SELECT rowid, key, value
	FROM test WHERE key= ''key1'' OR key = ''key2''
	OR key = ''key3'' OR key = ''key4''
	ORDER BY 1,2') AS
	ct(rowid text, key1 text, key2 text,
	key3 text, key4 text);

view raw crosstab.sql hosted with ❤ by GitHub

Update: 02/14/2013
The following can be achieved by a crosstab and also by an interesting SQL which I came across here - http://stackoverflow.com/questions/14863985/postgres-crosstab-maybe

	date | type | value
	_________________________
	2012-01-01 | 1 | 100
	2012-01-01 | 2 | 200
	2012-01-02 | 1 | 200
	2012-01-02 | 2 | 300
	2012-01-03 | 1 | 500
	2012-01-03 | 2 | 10

view raw gistfile1.txt hosted with ❤ by GitHub

	METHOD 1(faster):
	select date,
	sum(case when type = 1 then value end) Type1,
	sum(case when type = 2 then value end) Type2
	from yourtable
	group by date
	METHOD 2:
	select * from crosstab('select date::text, type::text,
	value::numeric(16,2) from tableA where
	type in (1,2)
	and date between ''2012-02-06'' and ''2013-02-13''
	ORDER by 1,2' )
	AS ct(date text,type1 numeric(16,2), type2 numeric(16,2))

view raw Crosstab.sql hosted with ❤ by GitHub

Virtualbox-Windows 8 developer preview installation error

Downloaded the Windows 8 developers preview yesterday but only to find I could not get it installed on a Oracle Virtualbox. I tried both 32-bit and 64 bit version on a Win 7 64 bit machine(HP EliteBook 8440p). The 32-bit got stuck at the following screen. I need to look for help on this error 0x81B8C63B

EDIT: From "Smiley"'s comments below, after I turned the Virtualization option ON from the BIOS menu at startup, I was able to install windows 8. Thanks.
Surprisingly, I have a Ubuntu virtual machine and it runs fine without turning on the Vitualization option.

Wxpython snippet - A date time control

Make sure you have installed wxwidgets.



# Create date control
self.dateCtrl = wx.DatePickerCtrl(panel, -1, pos=(130, 70))

#create time control
self.timeCtrl = wx.lib.masked.timectrl.TimeCtrl(panel,display_seconds=False,
                                               fmt24hr=False, id=-1, name='timeCtrl',
                                               style=0,useFixedWidthFont=True,
                                               value=datetime.now().strftime('%X'), pos = (250,70))

To get values -

self.timeCtrl.GetValue()

self.dateCtrl.GetValue())

C++ - Virtual functions

I came across an interesting question in an interview today. It was about virtual functions. I wanted to improve my understanding of virtual functions, so here are some interesting findings, some new and some already known to me.

• Destructors are called in the order - derived to base(if base destructor is virtual)
• A virtual function is member function of a class , whose functionality can be overridden by its derived class.
• The virtual function call is resolved at run time. Unlike non-virtual member functions which are resolved at compile time.(static binding/dynamic binding)
• If a same 'name' function is implemented in both base class and derived class, the base class function would be called.
• A class with one or more pure virtual functions becomes an abstract base class. This means that it has to be inherited.
• An interface class has no members variables, and all of the functions are pure virtual.

Yii web service and php soap client

A web service is a method of communication between two machines. The web methods are exposed for world wide access. Yii has support for implementing web services. It uses SOAP as its foundation layer.

To create a web service-

1. Create a web app using Yii console application.
2. Inside the controllers folder, create a file lets say 'ServiceController.php'.
3. Add the following code, which implements two functions. One adds a session variable and the other returns it back.
<?php
class ServiceController extends CController
{
   public function actions()
    {
       return array(
        'wreader' => array(
          'class' => 'CWebServiceAction',
        ),
      );
    }

    /**
     * @param string username
     * @return float
     * @soap
     */
     public function getauth($uname)
     {
              
            $session = Yii::app()->session;
            $session['u_id'] = 1111;
            return 1;
     }

     /**
     * @param string username
     * @return float
     * @soap
     */
     public function getemp($uname)
     {
        $session = Yii::app()->session;
        return isset($session['u_id'])?$session['u_id']:999;
     }
}

* Remember to mark the web methods with the tag @soap in its doc comment. Yii relies on doc comment to specify the data type of the web method's input parameters and return value.

Create a php file and enter the following code which consumes the web service.
<?php
$client=new SoapClient('http://127.0.0.1/s/index.php/service/wreader');
echo "\n".$client->getauth('harpreet');
echo "\n".$client->getemp('harpreet');

?>

Execute the php script in a terminal to check out your web service.

Open to suggests/improvements.

Read from Magtek card swipe reader in HID mode using libusb-win

This post deals with reading card swipe information from a Magtek USB Swipe Reader when set in HID mode. I used Visual Studio C++ Express Edition for a windows development environment. I installed libusb-win32 from http://www.libusb.org/wiki/libusb-win32
Create a C++ project in Visual studio, include the usb.h file and link the project with libusb.lib. Both of these are included in the libusb package.

The following can be used to read the card information-

// magtekusbhidcardswipe.cpp : Defines the entry point for the console application.
//
#include
#include

#include "stdafx.h"
#include "usb.h"

int _tmain(int argc, _TCHAR* argv[])
{
    struct usb_bus *busses;
    struct usb_bus *bus;
    struct usb_device *dev;
    int c, i, res;
    char data[337];
    char idata[10];

    usb_init();
    usb_find_busses();
    usb_find_devices();
    busses = usb_get_busses();
    for (bus = busses; bus; bus = bus->next) {

        for (dev = bus->devices; dev; dev = dev->next) {           
        if(dev->descriptor.idProduct == 2 && dev->descriptor.idVendor == 0x801) {
        printf("Product id: %04hx" ,dev->descriptor.idProduct);
        printf("Vendor id: %04hx \n" ,dev->descriptor.idVendor);
               
        usb_dev_handle *l_Handle = usb_open( dev);
        if( NULL == l_Handle ){
            printf( "usb_open(): no handle to device\n" );
        }
        res = usb_claim_interface(l_Handle, 0);
        if(res  < -EBUSY) {
            printf("Device interface not available to be claimed! \n");
            exit(0);
        }
        if(res < -ENOMEM) {
            printf("Insufficient Memory! \n");
            exit(0);
        }
        printf( "\nPlease swipe your card\n",res );
       
        res = usb_interrupt_read(l_Handle, 0x81, data, 337, -1);
        printf( "\nusb_interrupt_read %d \n",res ); 
       
        c=-1;
                //I am interested in only 10 characters in this range
        for(i=1;i<10;i++) {
            idata[++c] = data[i];
        }
        c=atoi(idata);
        printf( "\nMy data : %d\n",c);
       
        usb_release_interface(l_Handle, 0);            
        usb_close(l_Handle);
        }
       }
    }
    getchar();
    return 0;
}
 

Suggestions/improvements are welcome as always.

Iphone 4 does not mount on Ubuntu after iOS 4.2 upgrade - DBus Error

After I upgraded my iphone to iOS 4.2, I was not longer able to mount it on Ubuntu 10.10. Whenever I got plugged it in, I would get an error message -
DBus error org.freedesktop.DBus.Error.NoReply: Message did not receive a reply (timeout by message bus)


I looked it up and found a workaround, seems like I need to update to a newer version of libimobiledevice.

Here is how I did it-

Unplug the iPhone and execute the following in the terminal window.

sudo add-apt-repository ppa:pmcenery/ppa
sudo apt-get update
sudo apt-get upgrade

sudo apt-get install libimobiledevice-utils

After the above install, plug in the iphone and execute -

idevicepair unpair
idevicepair pair
idevicepair validate

That was it and we are back with Iphone on Ubuntu.