Blog
SQL fun: order before group with a subquery
I have came across a problem within a sql statement. Say we have 2 tables which are in a 1:n relationship. So we want to join them together normally the sql engine will just use the first match based on the auto increment key within the table. This isn't what i needed so i searched a way to actually sort the data before the grouping happened. The solution seems to be a subquery which isn't ideal performance wise but worked for my use case. So i ended up with something like this:
SELECT * FROM (
SELECT lastname, firstname, address_addition FROM address
LEFT JOIN othertable ON address.somefield = othertable.somefield
ORDER BY whatever
) as addresses GROUP BY addresses.address_id
Which brought me to the next problem, the sql standard doesn't allow duplicate column names within a subquery and mysql does actually implement this part of the standard -- surprise ;)
So i had to "retag" a few fields in the selected clause with AS. Lesson learned: always use a short table prefix.
Marc
UUIDs for partitions in Ubuntu Edgy Eft 6.10
I recently updated a Ubuntu Dapper Drake (6.06) system to Edgy Eft (6.10). Other than i had to reinstall/reconfigure my xserver everything went ok. However by the next reboot i noticed a strange root=[long alphanumeric value] kernel parameter in my grub prompt. After some investigation i found out that my /etc/fstab contained the same strange ids.
# /etc/fstab: static file system information.
#
# <file system> <mount point> <type> <options> <dump> <pass>
proc /proc proc defaults 0 0
# /dev/hdc5 -- converted during upgrade to edgy
UUID=048b4608-5241-4dc4-8671-f8290cb55af5 / ext3 defaults,errors=remount-ro 0 1
# /dev/hdc1 -- converted during upgrade to edgy
UUID=05c63290-f5b8-4afb-97c1-fb7e15ad6ed8 /boot ext2 defaults 0 2
# /dev/hdc10 -- converted during upgrade to edgy
UUID=06ee7e3e-768b-4a51-a31f-0e70ec314e81 /data ext3 defaults 0 2
# /dev/hdc7 -- converted during upgrade to edgy
UUID=56ef33df-9f30-4577-b1de-83b540fd3519 /home ext3 defaults 0 2
# /dev/hdc11 -- converted during upgrade to edgy
UUID=9628b57a-cd57-4ffb-a0e7-6d2bc349e46b /media/hdc11 ext3 defaults 0 2
# /dev/hdc12 -- converted during upgrade to edgy
UUID=caa74b76-416b-46aa-8c0a-f6348f6afbcd /media/hdc12 ext3 defaults 0 2
# /dev/hdc13 -- converted during upgrade to edgy
UUID=fe873ec7-3176-4aad-b0b1-9164f938f213 /media/hdc13 ext3 defaults 0 2
# /dev/hdd1 -- converted during upgrade to edgy
UUID=9E745EF6745ED11F /media/hdd1 ntfs defaults,nls=utf8,umask=007,gid=46 0 1
# /dev/hdd5 -- converted during upgrade to edgy
UUID=78DB-0025 /media/hdd5 vfat defaults,utf8,umask=007,gid=46 0 1
# /dev/hdc9 -- converted during upgrade to edgy
UUID=b43c2193-3c00-48d0-8c29-c2fa7212c457 /usr ext3 defaults 0 2
# /dev/hdc8 -- converted during upgrade to edgy
UUID=7cec1fb3-c2f2-46dc-9782-7228d57b9f2b /var ext3 defaults 0 2
# /dev/hdc6 -- converted during upgrade to edgy
UUID=6996378e-a37b-49b8-8e91-65164bef7626 none swap sw 0 0
/dev/hda /media/cdrom0 udf,iso9660 user,noauto 0 0
/dev/fd0 /media/floppy0 auto rw,user,noauto 0 0
My first thought: what a mess! Luckily they left some commends in there.
So what is the purpose of those numbers which are not so human friendly? They should uniquely identify a given partition. Which means you could swap your disks/memorykeys/whatever and the system will still mount the correct partition on the right place. Another thing this mechanism tries to work around is, that the device names depend on module load order. With the development in recent kernels which try to scan the hardware and detect devices in parallel, to speed up boot time, it will be entirely random which devices turns up as /dev/sda.
Listing UUIDs
You may ask how can i check the mappings between UUIDs and devices? Good question -- there are a few different ways,
/sbin/blkid will happily print a list of device names, filesystem labels and UUIDs for you even if you aren't root. While sudo vol_id -u <partion> will give you only the exact UUID for the requested partiton.
After i have informed myself about those UUIDs i am no longer that scared but i still think this is a suboptimal solution they are just way too human unfriendly. A solution based on diks LABELS is probably the better approach.
Marc
Playing Age of Empires 2 with Wine 0.9.30
I am absolutely not a gamer, but today i was sick at home and wondered what i could do, so i decided to try if i could run Age of Empires 2 + the Conquerors expansion with Wine.
According to the Wine APP DB [1,2] this should be possible -- and it was. Installation was straight forward and worked perfectly, just mount the cdrom and execute the setup with wine. Because wine doesn't yet support the required copyprotection (safedisc2?) the game needs a no cd crack in order to run. This isn't a problem either since all required information can be found on the APP DB site. At first performance was really bad but then i disabled the sound (via winecfg) set the game resolution to 800x600 and reduced graphic detail. With this configuration the game was playable on my P4 2.8 GHz, however a DIB engine wouldn't hurt either ;)
There is a strange thing which i noticed, if i start the game then switch back to the terminal, kill wineserver and restart the game it is quite a bit faster. Haven't yet investigated further in what's actually going on.
Thanks to all Wine developers for their work, you made my day :)
Marc
QEMU 0.9.0 is out and KQEMU is released under the GPL
QEMU 0.9.0 brings lots of improvements and KQEMU is finally released under the GPL -- thanks Fabrice.
Other interesting things happen in the current KVM development with the recent work from Ingo Molnar on KVM paravirtualization for Linux which gives pretty impressive performance.
Unfortunately i currently don't have time to work on qemu-gui and there doesn't seem to be much interest anyway.
So long, Marc.
Accessing the Line Scanner in IBM Anyplace Kiosk
The last week i was busy searching a way to access the integrated Line Scanner as found in the IBM Anyplace Kiosks. The biggest hurdle was actually to get the damn thing to work, after reading through various documentation (JavaPOS,OPOS,UnifiedPOS), installing different devices drivers, i finally succeeded with a OPOS based Windows solution. I still try to convince Management to deploy a Linuxbased solution, but do not yet know if there are the needed device drivers available, IBM seems to support it with IRES, we will see.
So the next step was to actually program the scanner in order to receive data. Since our "application" runs whitin a browser i had to use ActiveX and JScript to access the scanner. During coding this i learned that in order to capture ActiveX Events one has to use a slightly different event handling technique. I eventually got it to work with the code below.
<html>
<head>
<title>Scanner</title>
</head>
<body>
<object classid="clsid:CCB90182-B81E-11D2-AB74-0040054C3719" id="Scanner" width="39"
height="38">
</object>
<script type="text/jscript" language="JScript">
onload = function(){
MyScanner = function(){
var scanner = Scanner;
var dc = ec = function(){};
/* see the following msdn-link for further
* information on why this strange syntax actually works.
*
* http://msdn2.microsoft.com/en-us/library/ms974564.aspx
*/
function Scanner::DataEvent(){
dc(scanner.ScanDataLabel);
scanner.DataEventEnabled = true;
}
function Scanner::ErrorEvent(ResultCode, ResultCodeExtended,
ErrorLocus, ErrorResponse){
ec(ResultCode, ResultCodeExtended, ErrorLocus,
ErrorResponse);
}
var open = function(devname){
if(scanner.Open(devname))
return false;
return true;
}
var enable = function(devname){
if(!open(devname))
return false;
if(scanner.Claim(1000))
return false;
scanner.DeviceEnabled = true;
scanner.DataEventEnabled = true;
scanner.DecodeData = true;
return true;
}
return {
init : function(devname,datacb,errorcb){
if(!enable(devname))
return false;
if(typeof datacb == "function")
dc = datacb;
if(typeof errorcb == "function")
ec = datacb;
return true;
},
close : function(){
return !scanner.Close();
}
};
}();
var status = MyScanner.init("LineScanner",function(v){
alert("scanner " + v)
},
function(){
alert("error");
}
);
if(!status)
MyScanner.close();
else
alert("could init");
}
onunload = function(){
MyScanner.close();
}
</script>
</body>
</html>
An important point i would like to note is that only one application can access the scanner at the same time. Which means we must _always_ call close to release the internal handle.
Hope this is useful for somebody.
Marc
