Friday, January 21, 2011

Parse and Transform Text File by Using PowerShell (3)

In my previous blog, I described who I parsed the input text file from my debug result to generate the first report table. Now Let's continue to the second report.

Generate the Second Report

The second report is another summary report, based on the first report. It displays a list of methods and counts of their calls. In other words, it will list distinct method names in the first report, and take the max counter for each method as method call count.

I find out that to get the second report, it is not easy to use just one pipeline with a long chain of segment codes. Still, I'll continue to use pipelines with each one to generate temporary results. I'll use pipelines to get temporary results, and dynamically add properties to objects.

First, initialize some variables:

# second report
# initialize variables
$totalCount = 0
$i = 0
$ht=New-Object Collections.Hashtable

The first pipeline is used to pass the result of the above collection of objects ($results) as input. There will be no result out of the pipeline. Instead, I use the pipeline to update a hash table variable $ht, with the property of method name as its key.

The input objects are piped into the second segment which is a where clause to filter any object with its count property larger than zero.

# Filter out rows with count less or equals 0
$result | where { $_.$propertyNameForCount -gt 0 } `

Then the filtered object is passed to a block of codes, where the hash table variable $ht is updated with the object with the max count value:

| Select-Object -Property 'MethodName', $propertyNameForCount `
| %{
if ( $_ -ne $null ) {
$key1 = $_.'MethodName';
if ( $ht.ContainsKey($key1) -eq $false ) {
$ht.Add($key1, $_)
}
elseif ($_.$propertyNameForCount -gt $ht[$key1].$propertyNameForCount) {
$ht.Set_Item($key1, $_)
}
}
}

The values of the hash table $ht contain objects we need for the report. In addition to those objects, I need a total count of each method call count. This is done by a pipeline to update total count into the variable $totalCount:

$ht.Values `
| %{ `
if ( $_ -ne $null ) {
$totalCount += $_.$propertyNameForCount
}
}

After we get the total count, a new object is created in the same structure of properties as ones in the hash table, then add the new object to the hash table $ht:

# add total count to $ht1 table
$objValue = New-Object PSCustomObject
$objValue | Add-Member -type NoteProperty -Name 'MethodName' -Value '[Total count]'; # use XXX so that sorting to the last
$objValue | Add-Member -type NoteProperty -Name $propertyNameForCount -Value $totalCount;
$ht.Add("XXXX dummy key", $objValue);

Finally, the objects in the hash table are ready for the second report. The report is generated by the last pipeline: sorting by property 'MethodName', adding a sequence number as object property, and appending the table layout report to the output file:

# generate report
$ht.Values `
| Sort-Object -Property 'MethodName' `
| %{ # Add sequence column
$obj = $_;
$obj | Add-Member -type NoteProperty -Name 'No.' -Value $i;
$i++;
$obj;
} `
| ft -AutoSize -Property 'No.', 'MethodName', $propertyNameForCount >> $outputFile; # Format the result and out put to file

In summary, pipeline in PS is a nice-to-have feature. You may achieve the same result without using pipeline. I like PS pipeline's simple, fluent flow and powerful feature. As you can see, PS is also dynamic data type script language. Objects can be created on-fly and properties can be added or removed during the run-time. My parse script codes take the advantage of those two great features.

Here is the complete package(ParseDebugMsgs.zip) of the script codes.

Read More...

Friday, January 07, 2011

Parse and Transform Text File by Using PowerShell (2)

With the previous blog on the basic concepts and the project goals on your belt, now it is time to jump into the codes from 6000 feet height.

Define Input Variables
I could set some input parameters for my script module for easy use from command line. However, since my script is only for my own use and I have to update some values frequently when I use it, then I decided to just declare some variables with initial settings as a simple start:

$inputFile = 'C:\Tmp\test\fst_PO20100505.txt';
$outputFile = '{0}.txt' -f $inputFile;
[decimal]$durationLimit = -0.01;

Then some counter variables and a hash table variable $ht for later use are initialized:
$i = 0
$j = 1
$identityExPattern = "*duration: *" # expression pattern as a filter
$exPatternForCount = "*DAO::*" # epxression pattern for count
$identityPropertyName = "Duration"
$propertyNameForCount = "DAO count"
$ht = New-Object Collections.Hashtable;

Next I output a line a header to my output file:
# Output duration limit to the result file
'==== Result of "Duration > {0}" ====' -f $durationLimit >> $outputFile;

Those codes are straightforward. The duration limit is a filter, which is used to list only calls with duration larger than the filter value.

Generate the First Report


The first report is generated by one statement with a long list of chained segments of codes as a pipeline. The result is saved to variable $result.

The first segment in the chain is to get lines from input file:

$result = Get-Content $inputFile `

Then the second segment is a block of codes %{...}. This block takes the input to process and generate empty or a collection of objects as a result. The result can be piped to the next segment. The codes in the block is very simple, it updates the line number in a varable $i, and then takes the input object as it is. $_ is a special variable notation for the input object:

| %{ $i = $i + 1;
$_;
} `

Then each line is piped to the next where constrain statement:
| where {$_.trim().length -gt 0 -and ($_.trim().SubString(0,1) -eq "[") -and ($_.trim() -like $identityExPattern)} `

This constrain clause is like a filter to remove un-expected string lines. The result of this filter will be a line which is not empty, the first none-empty char is "[" and the content of the string contains a substring of "duration: ". The interested lines are then piped into the next code block %{...}.

This block contains a lot of codes. They can be divided in to two parts. The first part is to split a line into an array variable $row, and to acuminate count of each method:

$row = (-split $_ ); # split a line into array by space
$bCount = $false;
$method = $row[7]; # example: [ 5/5/2010 9:55:03 AM duration: 0.19 ] AppUserDAO::loginUser
if ( $method -like $exPatternForCount )
{
$c = 1;
# update method in hash table with count value
if ( $ht.ContainsKey($method) )
{
$c = $ht.Get_Item($method) + 1;
$ht.Set_Item($method, $c);
}
else
{
$ht.Add($method, $c);
}
$bCount = $true;
}

The second part is to create an object based on the result of the first part: $row. The object is created by using "Select ... -InputObject ... -Property" statement. The -InputObject take the array of $row as input, and -Property defines a list of properties:

# create an object with properties: sequence, datetime, duration, DAO count, and methodName
$obj = select-object -input $row -prop `
@{Name='No.'; expression={$i;}}, `
@{Name='DateTime'; expression={[DateTime]($row[1] + ' ' + $row[2] + ' ' + $row[3]);};} , `
@{Name=$identityPropertyName; expression={([decimal]$row[5]);} }, `
@{Name=$propertyNameForCount; expression={ `
if ($bCount) { `
$ht.Get_Item($method); `
} `
else { `
0; `
} `
}
}, `
@{Name='MethodName'; expression={($method);} };
$obj; #output object

The last line of $obj will pass the object to the next segment of the pipeline.

The segment is a simple where clause, which filters out any duration smaller than the expected value:

| where { $_.Duration -gt $durationLimit } `

The final pipe segment is to create another object based on input object. The purpose of this new object is for the first report, each property for a column in the report:

%{
$obj1 = Select-Object -Input $_ -Property `
@{Name='No.'; expression={$j;}}, `
@{Name='Org No.'; expression={$_.'No.';}}, `
@{Name='DateTime'; expression={$_.'DateTime';};} , `
@{Name=$identityPropertyName; expression={$_.$identityPropertyName;} }, `
@{Name=$propertyNameForCount; expression={$_.=$propertyNameForCount;} }, `
@{Name='MethodName'; expression={$_.'MethodName';} };
$j++;
$obj1; # output obj1
};

The result of the above chained segments is a collection of objects for the first report. The result is assigned to variable $result, and then is output to a file:

# get the reults to output file as formatted table
$result | ft -AutoSize >> $outputFile;

Read More...

Saturday, January 01, 2011

Parse and Transform Text File by Using PowerShell (1)

I have used my DebugLog class to investigate issues in Visual Studio projects. The debug messages are pushed to Visual Studio's output consol. The generated messages may be very extensive huge. For example, I had a case of a Windows application with performance issues of DAO calls. I got about 8233 lines of debug messages just from the start to the main window displayed. I copied the messages to a text file. It is 732K in size. I like to keep the raw messages there; however, it was hard to investigate issues with the extensive raw messages.

What I would like is to generate concise summary reports, for example, a list of method calls with durations in an order and a list of DAO calls with their counts. This is similar to the case to use XPath to parse and to transform an XML content to another format, such as a HTML table list.

PS came to my mind first. PS is a script based language; therefore, it is easy to give it a try. I am not an expert in PS. I just use it and learn it as I need. I spent some time to write codes and finally I completed a script module to get my expected result. Here is my review of the codes.

Basic Concepts

Before I jump deep into my PS codes, I would like to list brief explanations for some basic concepts.

Single value variables are dynamically declared in PS with prefix $. The data type can also be static in the format of [type]var.

Hashtable is a dictionary data type with a key and an associated value. The constant definition is @{[key1=value1,...]} or @{}.

# is used for comments.

Statements can be either separated by line break or terminated by ';' character. ` character is used as a continuing indicator.

Piping or pipeline is a very powerful feature in PS. By using |, twp segments of codes can be chained together, the output or results of the first segment being piped into the next segment of codes as an input. Not only strings can be piped, but objects can also be passed through the pipeline. You can write similar codes without pipelines, but by using it appropriately, your scripts may look much simple and easy to read, and you may like this unique and powerful feature of PS.

There are many great resources on web. For example, the first part of this blog tutorial on PS variables, arrays, and hashtables provides nice hands-on examples on PS basics.

The Goal of my Project

I call it as a project because I want to write script codes to reach my goal. Basically, I copy my debug messages from VS output console and save them to a text file. The goal of the project is to read the text file as input, parse each line and generate a list of reports, actually two reports in this project.

The first report is a table view of methods and their corresponding duration time. The second report is a table view of interested methods and their call counts.

Here is a partial section of the raw data:

[ 5/5/2010 3:12:58 PM ] MainSchedulingTool::posMenuItem_Click
[ 5/5/2010 3:12:58 PM ] POSelectionViewForm::POSelectionViewForm
[ 5/5/2010 3:12:58 PM ] POSelectionViewForm::poStartDateTimeFilterPicker_ValueChanged
[ 5/5/2010 3:12:58 PM duration: 0.00 ] POSelectionViewForm::poStartDateTimeFilterPicker_ValueChanged
[ 5/5/2010 3:12:58 PM ] POSelectionViewForm::poEndDateTimeFilterPicker_ValueChanged
[ 5/5/2010 3:12:58 PM duration: 0.00 ] POSelectionViewForm::poEndDateTimeFilterPicker_ValueChanged
[ 5/5/2010 3:12:58 PM ] PODAO::GetAllPOsByStatusAndOrderByEndDate
[ 5/5/2010 3:12:58 PM ] PODAO::GetPOs
[ 5/5/2010 3:12:59 PM ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM duration: 0.00 ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM duration: 0.00 ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM duration: 0.00 ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM ] PODAO::ReadPOData
[ 5/5/2010 3:12:59 PM duration: 0.00 ] PODAO::ReadPOData
...

Here an example of the first report:
No. Org No. DateTime            Duration DAO count MethodName
--- ------- -------- -------- --------- ----------
1 161 5/5/2010 3:12:59 PM 0.38 1 PODAO::GetPOs
2 162 5/5/2010 3:12:59 PM 0.38 1 PODAO::GetAllPOsByStatusAndOrderByEndDate
3 164 5/5/2010 3:12:59 PM 0.41 0 POSelectionViewForm::POSelection...
4 807 5/5/2010 3:13:00 PM 1.56 1 VendorDAO::GetVendorData
5 808 5/5/2010 3:13:00 PM 1.56 1 VendorDAO::GetVendors
6 1450 5/5/2010 3:13:02 PM 1.83 2 VendorDAO::GetVendorData
7 1451 5/5/2010 3:13:02 PM 1.84 2 VendorDAO::GetVendors
8 1452 5/5/2010 3:13:02 PM 1.84 0 POSelectionViewForm::setupSortedPOList
9 2575 5/5/2010 3:13:13 PM 14.13 0 POSelectionViewForm::PopulatePOData...
10 2576 5/5/2010 3:13:13 PM 14.13 0 POSelectionViewForm::POSelection...
11 2729 5/5/2010 3:13:14 PM 0.16 0 MainSchedulingTool::setupC......


The first column is a sequence number. It is a sequence line number in the report. The second is similar to the line number in the raw text file. The remaining columns are the information about each method such date time, duration value, count of DAO method calls, and method names.

The following is an example of the second report:
No. MethodName                                DAO count
--- ---------- ---------
0 [Total count] 6
1 PODAO::GetAllPOsByStatusAndOrderByEndDate 1
2 PODAO::GetPOs 1
3 VendorDAO::GetVendorData 2
4 VendorDAO::GetVendors 2
...

Read More...

Thursday, December 23, 2010

Great Year 2010 and Embracing the New Year 2011

Year 2010 is coming to the end. I have a great year 2010. I have been working in one company as an IT consultant for the whole year. Even though the job requirement are not very challenge, I have been always set up new heights for me and continue to learn and explore new stuff in this year. This is very productive year for me. Not only I gained so much in a wide range of areas, but I also pick some of my old skills and knowledge back, such as OPC and COM in Windows. I feel very applaud of myself when I see my accomplishments.

In addition to my work, I have spent much my after-work time on personal persuasions. I think I have made great progress in my iOS development. My application is close to the finish stage. During the development, I have gained great skills and knowledge of Objective-C, Cocoa framework, and iOS. I enjoy my journey in the year of 2010. At the same time, as always, I have been keeping up with the evolution of iOS in past two years. I watched all the technical videos of WWDC 2010, and some wonderful podcasts such as CTN, and app review shows.

The most important thing I have to say about my year in 2010 is the web or internet. In a sense, it really extend my life. Without it I would spend more time and energy to struggle. I have taken so much from the web, the open community: learning, enjoying and sharing. It has enriched my life so much. I am so grateful to live in such a wonderful time and word.

I like two phrases. One is "stay hungry and stay foolish", from Steve Jobs' 2005 Stanford Commencement Address. Another one is "You Can't Take Money to Eternity", same applies to knowledge. I want to stay as I am and to share what I have. This is the way to extend human's life.

Now it is time to embrace the New Year 2011!

Read More...

Sunday, December 19, 2010

NULL Issues in ABContact Open Source Project

I found a nice open source project for Mac/iOS Address Book data source. The project contains several key wrapper classes: ABContact, ABGroup, and ABContactsHeler. Mac OS/iOS has extensive APIs for accessing and editing AB records, but they are all C libraries. The project provides nice Objective-C wrapper classes for those libraries.

Today, I found several bugs with multi value properties. Some NULL issues in the wrapper class ABContact.m methods have not been handled. As a result, I got EXC_BAD_ACCESS exception. Basically, if a record has no multi-value property defined, for example address property, the CFTypeRef value will be NULL. The fix is very easy: checking NULL before using CFTypeRef value. Here are my updated codes:

#pragma mark -
#pragma mark Getting MultiValue Elements
- (NSArray *) arrayForProperty: (ABPropertyID) anID
{
NSArray *items = [NSArray array];
CFTypeRef theProperty = ABRecordCopyValue(record, anID);
// the return value is NULL if no multi property is defined for the record.
// therefore, check its NULL first before getting values
// Updated by David Chu, same apply to the following methods
if (theProperty != NULL ) {
items = (NSArray *)ABMultiValueCopyArrayOfAllValues(theProperty);
CFRelease(theProperty);
[items autorelease];
}
return items;
}

- (NSArray *) labelsForProperty: (ABPropertyID) anID
{
NSMutableArray *labels = [NSMutableArray array];
CFTypeRef theProperty = ABRecordCopyValue(record, anID);
if ( theProperty != NULL ) {
for (int i = 0; i < ABMultiValueGetCount(theProperty); i++)
{
NSString *label = (NSString *)ABMultiValueCopyLabelAtIndex(theProperty, i);
[labels addObject:label];
[label release];
}
CFRelease(theProperty);
}
return labels;
}

+ (NSArray *) arrayForProperty: (ABPropertyID) anID inRecord: (ABRecordRef) record
{
NSArray *items = [NSArray array];
// Recover the property for a given record
CFTypeRef theProperty = ABRecordCopyValue(record, anID);
if (theProperty != NULL) {
items = (NSArray *)ABMultiValueCopyArrayOfAllValues(theProperty);
CFRelease(theProperty);
[items autorelease];
}
return items;
}

The original codes do not check NULL cases. With those updates, my codes resume normal. In addition to NULL checking, I also make sure there is no memory leak, as the same way as the original codes do. All the copied NSArray result are set with autorelease.

Read More...

Wednesday, December 15, 2010

XCode Splash Screen

Just finished watch a short podcast show by CTN (Cocoa Touch Netcast). It is about 2 minutes show on how to enable settings for XCode Splash screen. For example, this is my XCode splash screen:


The way offered by Robert is actually to edit the plist file in /Library.... I don't like this way to modify plist file, since it may mess up the file. Instead, I prefer to use console tool or command defaults to modify it.

First, check the default setting. Open Terminal and type the command:

defaults read com.apple.xcode XCShowSplashScreen 1

or use the pipe and grep command to search for "Splash"

defaults read com.apple.xcode |grep Splash

I did not find the setting for XCShowSplashScreen. I have never disabled my splash screen. It looks like that the default setting is to show the splash screen if the setting is not defined in plist. Then I disabled my splash screen and use the defaults read command to read it again. After that, I saw it was set to 0.

To enable it, use the defaults write command:

defaults write com.apple.xcode XCShowSplashScreen 1

You may check it again by defaults read command.

Read More...

Saturday, December 04, 2010

PowerShell Tip: Dynamic Data Type

I have used PowerShell(PS) for quite a while. I really enjoy its power and great features. I did not spent time to learn PS thoroughly or systematically(such as C/C++, .Net C# or VB, or Objective-C). I just learn it by examples and by demand. The main reason is that PS covers a wide range of areas, from DOS command to .Net and other scripts. I just don't have time to learn it in a long time span.

One of PS great features is its dynamic data type. All the variables are defined by $ prefix. You can define a variable with strong data type. However, sometimes you just need to take the advantage of its dynamic data type. For example, the following code is to get files. The result may be null, one file or a collection of files:

$fs = Get-Item -Path "*.txt"

In order to find out if the result contain any file, you have to check the cases of empty, one object or a collection of objects.

$fs = Get-Item -Path "*.txt"
if ($fs -eq $null) {
Echo "empty files"
}
if ($fs.Count -eq $null) {
Echo ("one file: {0}" -f $fs)
}
else {
Echo ("collection of files. Count: {0}" -f $fs.Count)
$fs
}

Read More...

XCode 3.2.5 and iOS SDK 4.2

Xcode 3.2.5 and iOS SDK 4.2 was available on November 22, 2010. I downloaded the whole package (52GB) couple weeks ago. It took me about 2hours to get the package(not sure why my Internet or browser was so slow).


I had a little trouble to compile my app after the installation, as same I did last update (3.2.5 on September 30, 2010). I had to refresh my base frameworks. This time it was much better than the previous time. I only spent about a few minutes to make my app codes working in XCode.

Read More...

Sunday, November 21, 2010

iPhone Dev: Build + Analyze

There are many ways to detect iPhone project's code potential issues such as memory leak and performance issues. One very simple way is to build with Analyze. There is shortcut key for it: Shift+Command+A.

My current project is targeted to Simulator - 4.1. When I first time tried to use this Analyze feature, I got several messages saying "Analyzer skipped this file due to parse errors" for a lots of files. There were no analyze result for those files. Soon I found a solution to resolve most skips. SO has several posts on this issue. One of solution is to add a line to project settings (from project Get Info):


However, I still got this skip warning for MyLogger class. Eventually, I found is a C code issue. Here is a definition of enum type:

typedef enum {
LogLevelDebug = 1,
LogLevelWarning = 2,
LogLevelError = 3,
LogLevelInfo = 4,
LogLevelNone = 100
} UIUInteger, MyLoggerLevel;

After I removed UIUInteger, I got no skip warnings.

Read More...

Saturday, November 20, 2010

Powershell Scripts and Batch File

Normally I create a PS script project and then run it as a job in a batch file. Occasionally, I need to execute a batch job from my PS script. Here are two tips.

First to execute a PS script in batch file, run it from PS with script name and additional arguments:

Powershell myscript.ps1 argument1, 'argument two'

Notice that if there is a space in an argument, use single quote instead of double quotes.

Second tip is about calling a batch file from PS script, use the cmd /c to execute a batch file. For example, the following case is to use PS script to format the current date time asa string and then run a bat with the string as its argument:

# get the current date time
$date = Get-Date
# format the date time as a string
$argDateTime = "{0:d4}{1:d2}{2:d2}_{3}{4}{5}" `
-f $date.Year, $date.Month, $date.Day, $date.Hour, $date.Minute, $date.Second
# build a command line: $args[0] is a bat file
$cmdApp = ("{0} {1}" -f $args[0], $argDateTime)
# run the bat with a formatted date time string as argument
cmd /c $cmdApp

Read More...

Saturday, November 13, 2010

NANT: Exec a Target with Arguments

Recently I encountered an minor issue when I tried to use NANT to build a Visual Studio solution. Finally I narrowed down to the problem. It was the Exec target failure when one of its parameter argument contains a space. I thought that I could use a function or tool to convert the argument (a path) from a long name to DOS short name. Unfortunately, there is not such function in NANT for Windows build.

I posted a question to SO. I soon got a answer. In addition to that, I also figured out a way to resolve the issue. See my answer for detail explanation with a simple test case.

By the way, I used NANT extensively long time ago when I was a system analyst in a company's RTD, about 10 years ago. I created a NANT project to build an open source project by using Microsoft .Net Framework SDK (1.0) without using Visual Studio. Later I also used NANT in an Agile environment as a way to test UNIT tests, and to deploy the project. I really enjoy this great tool.

Read More...

Saturday, November 06, 2010

Using Excel to Compare SQL Database Tables

It is a very common task to compare data content between two SQL database tables. If two tables are within a SQL database, I normally use TSQL EXCEPT and UNION to get difference, detail in my previous blog: Comparing Two Tables By SQL Stored Procedure.

I could add linked server in SQL database so that the same method could be used to compare two tables in two difference SQL databases. However, it would cause too much trouble, such as change SQL settings, or caching millions rows of data from linked server.

The alternative way is very simple. I just run a SQL query to get data in SQL server management studio. Then copy the result to excel, either copy or export. Do the same thing against another SQL server again. By using Excel, it is very easy to compare cell by cell to get any difference.

The following are steps I use in Excel.

Excel Worksheets

The excel file contains four worksheets. The first one is comparison, which will be discussed in detail latter. The second one an third one are data result from two SQL database tables, for example, source_table1 and test_table1. SQL worksheet contains SQL query used to get data.


In the worksheet of comparison, the comparison is done by the following formula. For example, for cell A1 column:

=IF(source_table1!A1=test_table1!A1, source_table1!A1, "Diff (" & source_table1!A1 & " : " & test_table1!A1 & ")")

For the data cell A2:

=IF(source_table1!A2=test_table1!A2, "YES", "Diff (" & source_table1!A2 & " : " & test_table1!A2 & ")")

The formula of A2 comparison applies to all other data cells. Just copy the formula and paste to all the cells you want to compare. Excel is smart enough to adjust cell coordinates to get the comparison results. The following is an example of comparison result:


If you have thousands of raws of data in Excel, you can use Excel's Filter feature to narrow down the differences easily. From menu Data|Filter|AutoFilter to turn on the filter:


On the header filters, you can configure filter to whatever levels you want. Here are two examples:




As you can see, by using Excel it makes much easy to compare two tables and find out the differences.

Import Data Issues

The above method works fine for most of cases. I had a problem to get data from SQL to Excel last week when I was working on one SQL database table. I had spent for 2 hours+ finally I found the problem. Some rows contain double quotes in their raw data. For example, there was " being used in a table as inch unit. This unbalanced quote caused the Excel to import rows from either clipboard or csv file.

Excel treats " as string data type mark. In order to make importing to work, it has to be escaped. I used VIM to resolve the issue. Just simply search for " by typing /" in VIM, it will mark and find those quotes. Then I used VIM's Replace feature to escape all the single quote by the command(replacing " with ""):

:%s/"/""/g

After the normalization of data, I got the expected data from SQL query result to my Excel.

Excel Being too Smart

This issue is beyond the main topic of comparing data between SQL database tables. However, it is worth to leave a note here.

Here is the story of what I do to import or copy data from SQL query result to Excel. For the first data source, I copy the result of SQL query into clipboard. I think that by default, the delimiter used to separate columns is a tab char. I use Paste Special feature from Excel to paste pure text. Then Excel will prompt me to select what delimiter is and preview data in a grid. I like it very much. However, when I repeat the same steps to copy the data result from the second data source, the Excel does not prompt me, nor provide preview. It just copy data to columns. It is too smart to remember the delimiter.

However, I would like to preview data before pasting. I could not find a way to disable this smart feature. Finally I found an alterative way. I pasted the data to VIM first. Then replace the tab to semi-command (make sure it is not used in the raw data):

:%s/\t/;/g

Then copy the whole text to Excel. This change will fool the Excel to prompt the preview as expected.

Read More...

Saturday, October 23, 2010

.Net Debug Class (3)

Implementation Class: DianosticsDurationHelper

To calculation duration between two debug calls, I created an implementation class: DianosticsDurationHelper. In addition to two public properties in the base class, the implementation class provides a few public methods.


As you can see, the duration helper class has very simple APIs. One method Indent() is used for indentation or un-indentation, and one method for debug a message. The duration calculation is done within the class.

As discussed above, the calculation is done through delegates. Two private methods are defined here. One is for obtaining the current date time value, and another for calculating the duration value. Those methods are used as delegates to the base class calls.

private DateTime GetDateTime()
{
return DateTime.Now;
}

private string CalculateDuration(DateTime dt, DateTime poppedValue)
{
float duration = (float)((new TimeSpan(dt.Ticks - poppedValue.Ticks)).Hours) * 3600.0f +
(float)((new TimeSpan(dt.Ticks - poppedValue.Ticks)).Minutes) * 60.0f +
(float)((new TimeSpan(dt.Ticks - poppedValue.Ticks)).Seconds) +
(float)((new TimeSpan(dt.Ticks - poppedValue.Ticks)).Milliseconds) / 1000.0f;
return string.Format(" duration: {0:N2}", duration);
}

The remaining part of the duration helper class is as followings:

internal class DianosticsDurationHelper : DianosticsHelperBase<DateTime>
{
public DianosticsDurationHelper(string className) : base(className)
{
}

public DianosticsDurationHelper DebugMessage(string message)
{
base.debugMessageBase(message);
return this;
}

public DianosticsDurationHelper Indent()
{
return DebugIndent(true);
}

public DianosticsDurationHelper UnIndent()
{
return DebugIndent(false);
}

private DianosticsDurationHelper DebugIndent(bool indent)
{
if (indent)
{
base.debugIndent(GetDateTime);
}
else
{
base.debugUnindent(GetDateTime, CalculateDuration);
}
return this;
}
...// private methods used as delegates, see above.
}

Usage Examples

In many of projects, I used this duration class to get duration time between the begging and end of each call. Here are some example codes.

[STAThread]
static void Main() {
// customize indent string to --
DiagnosticsDurationHelper.IndentString = "--";
MyForm mainForm = new MyForm();
Application.Run(mainForm);
// ...
}

// MyForm.cs
class MyForm : Form {
// some helper methods using duration helper class
private DianosticsDurationHelper _debugHelper =
new DianosticsDurationHelper("MyForm");
// other data members
// helper methods to use the debug instance
private void DebugMessage(string message) {
_debugHelper.DebugMessage(message);
}

private void DebugMessage(string message, bool indent) {
if (indent) {
_debugHelper.DebugMessage(message).Indent();
}
else {
_debugHelper.UnIndent().DebugMessage(message);
}
}
// use debug helper methods to log duration...
private void MyForm_Load(object sender, EventArgs e) {
DebugMessage("MyForm_Load", true);
// ...
DebugMessage("Some information...");
// ...
DebugMessage("MyForm_Load", false);
}
// ...
}


By the way, in my helper class and base class, I tried to use fluent interface pattern for APIs. As you can see, the usage of those APIs is much fluent and clean.

To get the debug result in my Visual Studio's output console, I run my application in debug mode. Alle debug strings will be in the output console. Here are some results:

--[ 10/18/2010 11:21:58 AM ] MyForm::MyForm_Load
----[ 10/18/2010 11:23:58 AM ] DBGateway::DBGateway
...
----[ 10/18/2010 11:23:58 AM duration: 0.02 ] DBGateway::DBGateway
--[ 10/18/2010 11:22:58 AM ] MyForm::Some information ...
...
--[ 10/18/2010 11:21:58 AM duration: 0.22 ] MyForm:: MyForm_Load

Here is the source codes with an example.

Read More...

Sunday, October 17, 2010

.Net Debug Class (2)

My debug class contains one base class. Let's see the structure of the base class.

DianosticsHelperBase Class

My debug class is a simple base class, DianosticsHelperBase:


The above class picture shows all the public properties and protected helper methods for derived classes. Let's discuss the design of the base class.

Layout Format of Debug Message

The main purpose of the debug class is to print or log messages. The message consists two key parts: timestamp and message, with additional optional parts: context, indent, and calculated value.

The context is used as a convenience to identify message's context, for example, a class or module name could be used to mark debugged messages within the context.

Indentation is a nice feature to layout debug messages. The indentation and un-indentation are done through two method calls. DebugIndent() and DebugUnindent(). The indentation string can be set by the static property InentString. Null or empty string can be set to IndentString to disable indentation, or other strings to customize an indentation string, such as "—". The default indent string is a string of two spaces:

private static string _indentStr = "  ";

The base class provides another nice feature to calculate a value between two points. This is done by providing delegates to method calls: DebugIndent() and DebugUnindent(). If null delegates are passed to the calls (parameters), no calculation will be donem, thus disable the calculated value feature.

The layout format of debug messages is set by the static property Format of the base class. The default layout is:

//indent, timestamp, calculatedValue, context, message
private static string _format = "{0}[ {1}{2} ] {3}::{4}";

Constructor

Typically, an instance of the debug class is a member of a class where debug messages will be logged by the instance within its internal codes. A context string for the instance is set through the base class CTOR. The context of the debug instance is set in its creation time.

private string _context;
private string _calculatedValue;

public DiagnosticsHelperBase(string context) {
if (context != null) {
_context = context;
}
else {
_context = "";
}
_calculatedValue = "";
}

Helper Methods

A message is logged or output by the protected method debugMessage. In this method, a message is formatted as a string and output to a media. In the following codes, the output is passed to Output console in the Microsoft Visual Studio.

private string _context;
private string _calculatedValue;
private static bool _logMessage = System.Diagnostics.Debugger.IsAttached;

protected DiagnosticsHelperBase<T> debugMessage(string message) {
if (_logMessage)
{
System.Diagnostics.Debug.Print(
string.Format(_format, getIndent(), DateTime.Now,
_calculatedValue, _context, message));
_calculatedValue = "";
}
return this;
}

private string getIndent() {
string sVal = "";
if (_indent > 0)
{
int i = 0;
do
{
sVal = string.Format("{0}{1}", sVal, _indentStr);
i++;
} while (i < _indent);
}
return sVal;
}

The method checks _logMessage before further logging messages. This flag is set by Syste.Diagnostics.Debugger.IsAttached. If the build is in release mode, this flag will be False, as a result, no calls to log messages.

The concept of a calculated value is based on the consideration of calculating a value between two debug points, for example, a duration time value between the beginning and the end of a method call. This is done within the base class by two method calls: debugIndent() and debugUnindent(). How a value is obtained and how the calculated value is done are provided by passing delegates as parameters to those method calls. This provides a flexible way to generate calculated values such as duration, memory usage, disk free spaces and so on.

protected DiagnosticsHelperBase<T> debugIndent(GetValue<T> pushedValueDelegate) {
if (_logMessage)
{
if (pushedValueDelegate != null)
{
T value = pushedValueDelegate();
_debugDts.Push(value);
}
_calculatedValue = "";
_indent++;
}
return this;
}

protected DiagnosticsHelperBase<T> debugUnindent(GetValue<T> fromValueDelegate, GetCalculatedValue<T> calculatedValueDelegate) {
if (_logMessage)
{
if (calculatedValueDelegate != null && fromValueDelegate != null)
{
T value2 = _debugDts.Pop();
T value1 = fromValueDelegate();
_calculatedValue = calculatedValueDelegate(value1, value2);
}
if (_indent > 0)
{
_indent--;
}
}
return this;
}

All the methods in the base class are protected. They are only available for a derived class to implement specific debug usages. For example, the following duration helper class is an example for logging messages with duration information.

Read More...

Monday, October 11, 2010

.Net Debug Class (1)

I wrote a blog on debug or logger class in Objective-C about couple months ago. Actually, that class was based on my prevous .net debug class. It has a feature to print debug information in a nice indention layout, which was extended to my Objective-C class. I think my .Net is very useful, at least for me, and it is more generic, with an additional feature to calculate a value between paired messages(indent and un-indent).

Background Story

I was assigned to a task to resolve a painful slow performance issue of a .Net Windows application project. I did not spend much time to understand the complete business logic of the application. I went to the issue directly. What I did was to create a debug class to print out time and duration of each method or event call. Based on that information, I then focused on the longest duration calls to find out ways to improve the performance issue. The task was finished in very short period of time and the performance was greatly improved. My debug class is a very generic and useful tool. I have used this in my other projects with great success. With this experience and design, I created a similar logger class in Objective-C when I started iPhone development.

The intension to create this debug class is mainly based on three considerations. First is to generate debug message with duration information for each method or event call. This is the main reason and it is very easy to do. This could be done without a class. However, I would like to have debug message in a consistent format or indention. A class can encapsulate the implementation and provide unified interface. This second consideration actually was trigged by System.Diagnostics.Debug class, which has a nice property IndentLevel and a method Indent.

The third consideration is the maintenance. Since the debug calls are spread out all over the places in a project, it will be really hard to clean up them when the project is finally released. I would like to keep the debug feature or codes in my project without removing them, but also have least impact on its performance. As you will see, I have introduced delegates and early checks to avoid unnecessary calls if debug is disabled.

Delegates Used in the class

I like to use delegates as a way to pass custom implementation to my class. The first advantage is that it follows Open/closed-principle. It opens for extension or plug-in, but close my class for any modifications. The second advantage is to improve performance. Delegates normally used as parameters. They are pointers to functions or methods. If a delegate is not called for execution, its internal codes will never be evaluated. The following delegates are defined:

public delegate T GetValue<T>();
public delegate string GetCalculatedValue<T>(T value1, T value2);

The first one is a delegate to get a value. The delegate provides a way to let client to define his/her implementation to get a value. For example, for duration case, a delegate can be defined just returning a value of DateTime.Now.

The second delegate is for calculate a value based two parameter values, from value1 to value2.

Read More...

Friday, October 01, 2010

XCode 3.2.4 and iOS 4.1

Today I checked the Apple Development web site to see if there is any new Xcode and iOS available. Then I found that Xcode 3.2.4 and iOS 4.1 are available for download. I remember that when I just finished watching WWDC10 videos, I could not get the new iOS 4.1. It was only available for registered developers. I am very glad that now it is open for all developers.

My current XCode is 3.2.1 and iPhone SDK is 3.1.3. The new package is in download progress. The size of the package is 2.94GB. Like I have recorded the space compassion my iMac updates, here is the difference of free space between before and after.

Command: df -lakUsed(Kilobytes in 1024-blocks)
Before ...1,609,459
After...2,428,476
Difference (A-B)819,017


Issues After the Update

After the update, I tried to open the project I am working on. The first issue is that I lost my Framework references. For example, my UIKit.framework item in Frameworks became red. iPhone 3.1.3 frameworks were all gone.

That issue actually was not hard to fix. I opened the project Get Info window, and changed the Base SDK for All the configurations to iOS 3.2. Then select a format for my project.


The second issue was not so straightforward. I saw an error when I tried to build the project. It is code signing issue with key chains.


After I googled the web for solutions, I found this answer from SO. Basically, default device for my build was set to iPhone, instead of Simulator. For device build, I do need to be a registered developer.

After I fixed those two issues, I am now back to my progress of development. XCode and iOS updates are very well back-compatible. The updates do not like Visual Studio update with a lots of changes or conversions. Maybe my project is just too simple?

Read More...

Friday, September 24, 2010

Use iOS Address Book

Recently I have encountered an issue about using iOS Contacts or Apple's Address Book as data source for my application. The AB provides nice detail data structure for contacts, as well as views and APIs for adding, editing and deleting contacts. The issue is how I build a bridge between the AB contacts and my application.

Initially, I thought that the contacts in my app are mainly company contacts. In the AB framework, a contact record can be a person with names such first and last name, or an organization with a name, or both. Actually, when I stand back, my app's contact can be a company or a person. There should be no clear line between them. Therefore, I should not limit my data source to organization or company only.

Then I was puzzled by the issue who to present contacts from AB to my app. I don't mind to present all the contacts, but I would like to provide a convenient way to present a sub-set of contacts. I don't feel that it is good way to divide contacts into persons and companies.

Further exploration on AB framework and studying some example codes, I realized that contacts can be categorized into groups. Groups are defined by user. A group in AB is also record which has only one property kABGroupNameProperty. That opened my eyes. Why not let users to define their contact collection in group? Then my app will use that group as data source. Now I think that it's the best solution.

With this discovery, I find out a very useful and simple application case. By searching for the case in iTunes, I found a couple of apps. The app is very simple. Its main feature is for a user to manage or organize contacts into groups. I could work out a different and simple app. It is not hard to do it. I may get it done in a very short period of time. Sit back and rethink about this struggle period, my past pause and hard time are not a waste. You never know that a bright, wide and smooth pathway will suddenly present to your front when you think you are driving into a no-exit road.

Read More...

Thursday, September 23, 2010

Wrapper Helper Classes for ABAddressBook

I have been looking for ways to integrate Address Book - Contracts to my application. Although Address Book Programming Guide for iOS provides information, APIs and example codes, all those resources are far away from what I need.

Then I found this ABContactHelper project on github. The project contains several wrapper ObjC classes to wrap CF classes: ABContact for contact, ABGroup for group and ABContractHelper for CF functions. I like those helper classes. The only thing I found so far I don't like is the contactName property. It formats a name in western way, not localized. Instead, the compositeName is an alternative choice, which just uses iPhone's AddressBook C function to return a composite name.

Those helper ObjC classes provide a good overview of ABAddress Book framework.

Read More...

Monday, September 13, 2010

Backup SQL Server by Using PowerShell Scripts

There are many ways to back up SQL server databases. Normally it is done through DBA to create a scheduled job on SQL Server. However, this requires a full version of SQL server. For Microsoft SQL Server 2005/2008 Express version, one of its limitation is that the free version does not provide job scheduling. During my past working experience, I fount several ways to the backup.

The basic requirements for the database backup job are:

  • The job can be scheduled as an automation job without user interaction
  • The job is preferred in script for each maintenance, for example, database, user/pwd, and backup location changes.
  • The backup job is centralized on one place so that several SQL databases are backed up on one central location.

SQL Server Utility

The first tool I found is to use SQL management tool SQLMaint.exe. It comes with SQL Server Management Studio 2005. Here is the technical information of this tool. For example, I use the following batch commands to do a database backup for MyDatabase on SQL server PC001\sqlexpress:

@echo off
REM
REM This path is SQL binary folder for sql maintanance app
REM
pushd "C:\MSSQL2005\MSSQL.2\MSSQL\Binn"
sqlmaint.exe -S PC001\sqlexpress -U dbbackup -P pwd
-D myDatabase -CkDB -BkUpOnlyIfClean
-Rpt C:\MSSQL_Backup\Log\PC001MyDatabase_backup_log.txt
-VrfyBackup -BkUpMedia DISK
-BkUpDB C:\MSSQL_Backup\DB
-DelBkUps 3days -DelTxtRpt 3weeks
-HtmlRpt C:\MSSQL_Backup\HTMLRpt\PC001MyDatabase_backup_report.html
-DelHtmlRpt -3weeks
popd
@echo on

The batch commands work fine with only SQL Server 2005 database; however, it does not work for SQL Server 2008 or Expression databases.

PowerShell Solution One

By googling web, quickly I found an alternative way to do the job. Those scripts are based on Microsoft.SqlServer.xxx classes. That's very cool! Based on those scripts and my requirement, I created a function. This function does database backup by either Windows log-on user credential, or SQL Server user credential. In either case, the credential user should be configured in the SQL server with db_backupoperator permission.

The function takes following parameters:

  • SQL Server Name, for example, PC001\SQLEXPRESS
  • database name, for example, myDatabase
  • folder: a path on SQL server where the backup file will be saved
  • SQL user name. This is optional. If it is not supplied, the current Windows log-on user's credential will be used
  • password for the above SQL server user. Optional


Here is the script:

function BackupSQLDb (
[string]$p_sqlServerName = ${throw "Missing sql server name "},
[string]$p_db = ${throw "Missing parameter database name"},
[string]$p_DestFolder = ${throw "Missing parameter destination folder"},
[string]$p_userName,
[string]$p_password
)
{
#load assemblies
#note need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008
#otherwise may get this error
#Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure
#the assembly containing this type is loaded.

[System.Reflection.Assembly]::
LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::
LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::
LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::
LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$sqlServername = $p_sqlServerName

$sqlUserName = $p_userName
$sqlPWD = $p_password
#create a new server object
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")
-ArgumentList $sqlServername # "PC001\sqlexpress"
$backupDirectory = $p_DestFolder
#display default backup directory
Write-Debug ("Default Backup Directory: {0}" -f $backupDirectory)
if ( $sqlUserName -ne $null -and $sqlUserName.length -gt 0 ) {
$server.ConnectionContext.LoginSecure=$false;
$server.ConnectionContext.set_Login($sqlUserName)
$securePassword = ConvertTo-SecureString $sqlPWD -AsPlainText -Force
$server.ConnectionContext.set_SecurePassword($securePassword)
}

$db = $server.Databases[$p_db]
$dbName = $db.Name
if ( $dbName.length -gt 0 )
{
$timestamp = Get-Date -format yyyyMMdd_HHmmss
$backupFile = $backupDirectory + $dbName + "_" + $timestamp + ".bak"
Write-Output ("Start backup database ""{0}"" to ""{1}"""
-f $dbName, $backupFile)

$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
#BackupActionType specifies the type of backup.
#Options are Database, Files, Log
#This belongs in Microsoft.SqlServer.SmoExtended assembly
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($backupFile, "File")
$smoBackup.SqlBackup($server)
Write-Output ("Finished backup database ""{0}"" to ""{1}"""
-f $dbName, $backupFile)
}
else {
Write-Output ("ERROR: invalid database name or database does not exist: {0}"
-f $p_db)
}
}

I only tested this function on Windows XP and Windows 2008 Server with SQL Server 2008 Express installed.

PowerShell Solution Two

The above script function works fine with SQL Server 2005 and 2008 and Express versions. However, it does not work for SQL Server 2000! I realized one day that there is option to obtain the backup TSQL scripts from SQL Server Management studio. I verified that in the TSQL command stays same in all SQL Server versions: 2000, 2005 and 2008. How about to make a connection to SQL server and run the TSQL command? Quickly I come to the solution two.

function BackupSQLDb (
[string]$p_sqlServerName = ${throw "Missing sql server name "},
[string]$p_db = ${throw "Missing parameter database name"},
[string]$p_DestFolder = ${throw "Missing parameter destination folder"},
[string]$p_userName,
[string]$p_password
)
{
$timestamp = Get-Date -format yyyyMMdd_HHmmss
$backupFile = $p_DestFolder + $p_db + "_" + $timestamp + ".bak"
$backupDescription = "Full backup of {0}" -f $p_db
Write-Output ("Start backup database ""{0}"" on SQL Server({2}) to ""{1}"""
-f $p_db, $backupFile, $p_sqlServerName)
# TSQL command for backup
$tsqlCmd = "BACKUP DATABASE {2} TO DISK = N'{0}' WITH NOFORMAT, NOINIT, NAME = N'{1}', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
-f $backupFile, $backupDescription, $p_db
$con = $null
if ( $p_userName -ne $null -and $p_userName.length -gt 0 ) {
# Use SQL user/password
$con = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}"
-f $p_sqlServerName, $p_db, $p_userName, $p_password
}
else {
# Use Windows log on credential
$con = "Data Source={0};Integrated Security=SSPI;Persist Security Info=True;Initial Catalog={1}"
-f $p_sqlServerName, $p_db
}
Write-Output ("Connecting to {0} ..." -f $con)
$cn = new-object System.Data.SqlClient.SqlConnection ($con)
$cn.Open()
$cmd2 = new-object "System.Data.SqlClient.SqlCommand" ($tsqlCmd, $cn)
$result = $cmd2.ExecuteNonQuery()
$cn.Close()
Write-Output ("Backup database is done with result {0}" -f $result)
}


I did similar tests for this function. It seems that it works for all SQL Servers, 2000, 2005, and 2008.

References

SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell

SQL Server PowerShell : Basics – Connecting to SQL Server

JBs Powershell blog: SQL Queries.

Read More...

Saturday, September 11, 2010

Old Windows Tool: XCOPY

XCOPY is an old Windows Tool. I remember that I first used this tool to copy files with sub-directiories when my computer was DOS based one. It is a nice tool to copy files. At my current work, this tool is also used to copy only most recently changed files as a way to back files. The option is /D/Y.

XCOPY source [destination] /D /Y


In the past week, I was working on a project to migrate services and scheduled jobs to a new Windows Server 2008 box. One simple task was to move a XCOPY job, copying files from a mapped Windows box to an imported UNIX based network drive. To my surprise, the same command does not work any more. It always copy whole files again, about several GBs. Somehow, XCOPY does not work for CIFS type network drive.

It is possible that the file date time stamp retrieved from the network drive might be truncated after seconds, while the full date time stamp are available in Windows file system. As a result, this may lead to always copy new files to the destination. I got this explaining from my SF question.

I also tried to use ROBOCOPY. It did not work neither. Finally, one of my network team member suggested me back to the old tool XCOPY with different options. It works again! Here is the command:

XCOPY source [destination] /M /Y /C

/M for copying only archived files and clear archive flag after the copy. I added /C option to let copy process to continue regardless of any failure.

The reason I like to use XCOPY is that it is more tolerance or not so aggressive. Even a file is hold by another process, it still does the copy. I tried SyncToy tool and it does not work with files being accessed by another process. My backup job is doing realtime file backup and those files are constantly updated by realtime process. Therefore, /C option lets me to continue the backup process and try it again on the next day.

I am happy with the alternative solution.

Read More...