Tuesday, November 29, 2011

SQL Clustered Index

A primary key is usually a unique clustered index. A primary key cannot be null.

A unique clustered index, if not a primary key, can be null.

A clustered index determines the physical order of rows in a table.

There can be at most one clustered index on a table. Non-clustered index is built upon clustered index, and can be created only when a clustered index already exists on the table.

A unique key is non-clustered by default, unless you specify it to be clustered.

[1] Creating Clustered Indexes
[2] Using Clustered Indexes
[3] CREATE INDEX (Transact-SQL)

MSSQL - Change table design not-allowed error

Sometimes, for example, when you add a column to a table, or when you want to create a primary key field on a table containing millions of rows, you may encounter the dialog message: "The changes you have made require the following tables to be dropped and re-created".

The solution is: From top menu select Tools -> Options -> Designers -> Table and Database Designers, uncheck the box "Prevent saving changes that require table re-creation". When the table is big, you may also need to increase the value of "Transaction Time-out after" textbox.

However, it is recommended not

Query Plan

When a query goes complicated and data involved is huge, query plan reveals the query execution sequence and helps to optimize the query [1].

In MSSQL Server Management Studio, after opening a query window, one can choose "Query -> Display Estimated Execution Plan" to see the estimated query plan, and select "Include Actual Execution Plan" to show actual query plan in the result. [2] is a very good introduction.

[1] Query plan
[2] SQL Server Query Execution Plan Analysis

Saturday, November 26, 2011

MacBook Pro with Mac OS X Lion 10.7

I got a MacBook Pro 13" for the Thanksgiving sales Friday. It's $101 off the regular price -- $1098, plus tax it's $1150. I didn't get an Air because an Air lacks wired ethernet jacket and CD/DVD drive. I'll have to buy extra accessories. Air has 128GB super fast SSD hard drive. Pro has ATA drive but it's 500GB. For 13.3" size, Pro is 2.04kg, Air is 1.35kg, which does not create a big difference for me since I don't carry laptop around often.

The current OS is called Lion, it is called Mac OS X Lion 10.7.

So far the feeling is: the 13.3" screen is indeed a little bit small, can't easily display the different windows without interfering each other. Besides a bigger screen, I also prefer a mouse instead of the touchpad. I may buy these separately later, but now let me just setup the environment first.

So now I'm installing softwares to make it a development machine. It already has php, perl, python, ruby, vi, emacs, svn, git, cvs, lex/yacc. I need to have apache, mysql, postgresql, c/c++, java, rails, gcc, make, Xcode, so that I can develop LAMP web applications, iOS applications, and c/c++/java applications.

First I need to enable root account, since many operations will need root permission. See How to Enable the "root" Account on Mac OS X. I used the "2.2 Directory Utility - (Mac OS X 10.4 or later)" method.

To setup LAMP, I referred to Setting up PHP, MySQL, and Apache in Mac OSX Leopard, and Installation of MySQL server on Apple Mac OS X 10.7 aka Lion.

To setup pear, I followed How to setup PHP Pear in Mac OS X Lion. To restore a LAMP application I wrote in the past, I need pear::DB, so I use "pear install DB" command to install it, quite easy.

When trying to make php work with mysql, I had a problem "Warning: mysql_connect(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock)". The solution is found in the above link.

From the above link, you may have a socket file as /tmp/mysql.sock or /var/mysql/mysql.sock but php is looking in the wrong place. Instead of moving the socket or editing config files one can just create a symbolic link so mac finds the required socket. In my case I have /tmp/mysql.sock but not /var/mysql/mysql.sock, so I used this:
cd /var
mkdir mysql
cd mysql
ln -s /tmp/mysql.sock mysql.sock

To install Xcode, I visited Downloads for Apple Developers (needs apple ID). Early versions of Xcode are also available here. The most recent version Xcode 4.2.1 was just released on 11/17/2011. Its major new features are support to iOS 5 and iCloud. But I'm a little conservative. After checking some user reviews on Xcode 4.2.1, which seem to complain about some backward incompatibility issues, I decided to go with the last one, Xcode 4.1, which was released in July. After Xcode is installed, gcc/g++/make all work.

When type "java" or "javac", Lion pops up a dialog box on installing java runtime environment. This installation seems to actually install the sdk as well, since now I can also compile java programs.

Installing rails is also by following hints of Lion. I don't know why the first time it didn't work, complaining about the missing of a ruby header file. But a second try works.

So far I don't have postgresql yet. But that's ok since I won't use it in a short time. My development environment is nearly setup after one day.

Tuesday, November 22, 2011

OS Tools

KDirStat is a linux/unix disk usage statistics viewer and cleanup tool. Its Windows version is WinDirStat and Mac version is Disk Inventory X.

Escape special char

When transferring data, special characters may be used as separators of records, e.g., row or record separators. In that case you need to escape the special characters from the data, and recover them at the data receiving side. Here is PHP code for this purpose:

test("\.");

function test($s) {
$t = encode($s);
$o = decode($t);
p("s = [$s], encode(s) = [$t], decode(encode(s)) = [$o]. " . (($o == $s) ? "passed" : "failed !!!"));
}

//
// Escape row separator "\n" in a string.
// Encoding scheme:
// \ -> \\
// \n -> \.
//
function encode($s) {
return str_replace(",", "\;", str_replace("\\", "\\\\", $s));
}

//
// Decoding scheme:
// \. -> \n
// \\ -> \
// Note: can't use the following:
// return str_replace("\\\\", "\\", str_replace("\.", "\n", $s));
// because it fails for the below case:
// s = "\.". encode(s) = "\\.", decode(encode(s)) = "\n".
//
function decode($s) {
// Use "===", since "==" treats 0 as false. See http://www.php.net/manual/en/function.strpos.php
if (strpos($s, "\\") === false) return $s;

$t = "";
$len = strlen($s);
for ($i = 0; $i < $len; $i ++) {
$c = $s[$i];
if ($c == "\\") {
$d = $s[$i + 1];
if ($d == "\\") { $t .= "\\"; $i ++; }
else if ($d == ".") { $t .= "\n"; $i ++; }
else { $t .= "(error)"; } // This shouldn't happen.
}
else { $t .= $c; }
}
return $t;
}

function p($s) {
print str_replace("\n", "<br>", $s) . "<br>";
}

Thursday, November 10, 2011

iPhone/iPad hover function

The hover event (can be provided by css or javascript) is not supported in iPad/iPhone.

The workaround is use javascript: "onclick='javascript: f1();'". f1() is defined as:

function f1(o) {
if ( navigator.userAgent.match(/iPhone/i) ||
navigator.userAgent.match(/iPad/i) ) {
// do the hover work. for example:
var x = document.getElementById('elemId');
x.style.left = u.parentNode.offsetLeft + 'px'; // needed for positioning.
x.style.visibility = 'visible';
}
return true;
}

Some sources say one can also use ontouchstart() and ontouchend(). But they didn't work in my situation.

Blog Archive

Followers