Archive

Archive for December, 2011

MySQL: select data whose certain column contains the specified values only

December 23, 2011 2 comments

This problem could be best described by an example as below:

select * from mytbl;
+------+------+
| k    | v    |
+------+------+
| A    | 1    |
| A    | 2    |
| A    | 3    |
| B    | 1    |
| B    | 3    |
| C    | 1    |
| C    | 3    |
| C    | 4    |
| E    | 2    |
| E    | 1    |
| F    | 5    |
+------+------+

k, v are both char(10), not null.

I want to find out records whose v column is either 1 or 3 [and only 1 or 3, therefore in the above example the only qualified records are (‘B’, ‘1’) and (‘B’, ‘3’)]. This is just a simplified version of the problem I faced at work recently and I did find someone posting the similar question on Internet but I forgot to bookmark the url and I didn’t find its solutions interesting hence I pulled my hair a bit and came up with the following solution using group_concat and regexp:

select k, group_concat(distinct v order by v) as g from mytbl group by k having g regexp '^(1,?)?(3)?$';
+------+------+
| k    | g    |
+------+------+
| B    | 1,3  |
+------+------+

If the requirement becomes v is either 1, 3 or 4 (again, 1, 3 or 4 only),

select k, group_concat(distinct v order by v) as g from mytbl group by k having g regexp '^(1,?)?(3,?)?(4)?$';
+------+-------+
| k    | g     |
+------+-------+
| B    | 1,3   |
| C    | 1,3,4 |
+------+-------+
Categories: mysql, Programming

git branching by example

December 11, 2011 3 comments

In this post I am going to demonstrate how to use git to apply a bug fix from the master branch to a feature branch that has already committed a new feature.

Example: fork a branch, add some new features to it. In the meanwhile add a bug fix to the master branch. We want to apply the bug fix to the feature branch while working on it but we decide it’s not time yet to add the new feature to the master branch. The simplest way I found (so far) is to create a temporary branch (and switch to it) on top of the master branch with the bug-fix commit, merge from feature branch, then switch to feature branch, merge changes from combined.

mkdir demo_proj
cd demo_proj
vi func.php

Put the following code into func.php and save.

<?php
    function do_something() {
        echo "hello\n";
        return "wrong value";
    }
git add .
git commit -a -m "added function do_something in func.php"
git checkout -b feature
vi func.php

Add a new function new_feature() to func.php

<?php
    function do_something() {
        echo "hello\n";
        return "wrong value";
    }


    function new_feature() {
        return 999;
    }
git checkout master
vi func.php

Change word “wrong” to “right”:

<?php
	function do_something() {
		echo "hello\n";
		return "right value";
	}
git commit -a -m "fixed a bug in do_something()"
git checkout -b combined
git merge feature
git checkout feature
git merge combined
git branch -d combined

After “git merge combined” the feature branch has the bug-fix from master branch while maintaining its own new_feature() function. Branch combined has accomplished its goal hence it’s OK to remove it.

Categories: git

Mac Lion 10.7.2 on HP 4530S

December 10, 2011 2 comments

Followed instruction from http://www.tonymacx86.com/viewtopic.php?f=259&t=41546 to install Lion 10.7.2 on my newly bought HP 4530S (i3, 4GRAM, 320GB 7200RPM).

Notes:

  • Got a kernel panic upon reboot after installing hp installer, reset BIOS to factory default fixed that.
  • Alt key is the Command key.
  • Had a hard time to exit from full screen mode, found solution from here.
  • [01/07/2012] Installed VirtualBox 4.1.8 and tried to start Ubuntu 11.10 64bit server and it failed with the following error, downgraded to 4.1.6 solved the problem.
    VT-x features locked or unavailable in MSR. (VERR_VMX_MSR_LOCKED_OR_DISABLED).
Categories: lion, mac osx, tony mac osx

Play with randomness in MySQL

December 2, 2011 1 comment

Some of you might know about the rand() function in MySQL — it randomly generates float point number between 0 and 1. Most of the time we use it to generate results that we want to be in random order. For example,

We have a people table that has the following records:

select * from people;
+-----------+
| name      |
+-----------+
| Bob       |
| Alice     |
| Kim       |
| Tom       |
| Jerry     |
| Linda     |
| Fransisco |
| Zack      |
| Peter     |
+-----------+

We are going to pick 3 persons out of the list randomly, so we do a

mysql> select * from people order by rand() limit 3;
+------+
| name |
+------+
| Zack |
| Bob  |
| Kim  |
+------+

The above query should return (most likely) different result every time. Next I am going to make it a little more interesting. Let’s say I have another table named prize which contains a list of prizes:

select * from prize;
+-----------------+
| name            |
+-----------------+
| Pencil          |
| Coffee grinder  |
| iPad            |
| GPS watch       |
| Yoga mat        |
| 2 movie tickets |
+-----------------+

What we want to do is to assign a randomly picked prize to each person in the people table (assuming the same prize can be assigned to more than one person as there are fewer number of prizes than number of people), here’s the query

select o.*, (select name from prize order by rand() limit 1) as prize from people o;

This would return something like the following (again the result will be most like different if you try it):

+-----------+-----------------+
| name      | prize           |
+-----------+-----------------+
| Bob       | iPad            |
| Alice     | Yoga mat        |
| Kim       | GPS watch       |
| Tom       | GPS watch       |
| Jerry     | iPad            |
| Linda     | GPS watch       |
| Fransisco | Pencil          |
| Zack      | 2 movie tickets |
| Peter     | GPS watch       |
+-----------+-----------------+

For your convenience you can use the following sql statements to generate the tables with data populated:

CREATE TABLE `people` (
  `name` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `people` VALUES ('Bob'),('Alice'),('Kim'),('Tom'),('Jerry'),('Linda'),('Fransisco'),('Zack'),('Peter');

CREATE TABLE `prize` (
  `name` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `prize` VALUES ('Pencil'),('Coffee grinder'),('iPad'),('GPS watch'),('Yoga mat'),('2 movie tickets');
Categories: mysql, Programming