Executing MySQL Scripts in Batch Mode

I wrote a script that would accept a file path and traverse through the directory tree to search for files with extension *.sql and load those sql scripts into MySQL and run them.
My *.sql files are basically scripts that builds database tables. 


Here's what I've done;

################################################################################
#!/bin/sh

# Reads the specified directory and traverses for the sql script files.
# The first argument($1) is the target directory to be searched.

#Read the files in the directory and generate the table creation statements separating them using the semi-colon delimiter
# If there are sub-directories, they will be traversed and all files ending in '.sql' will be processed
source_queries=""
source_command="source "
echo "argument passed $1"

for file in $(find $1 -type f -iname '*.sql'); do source_queries="${source_queries}${source_command}${file};"
done

# Uncomment to printout generated queries/statements for debugging purposes
# echo "printing statements generated ${source_queries}"

# Connect to the database using the 'stratus' database and execute the query statements generated
# This can be run on the background as well

echo "Executing database tables creation scipt......."
mysql -u myuser --password=mypassword --database=mydatabase -e "${source_queries}"
echo "script completed...."
#################################################################################


Say if there's 1 script file with filename create_person_table.sql under the target directory /opt/db_scripts the resulting mysql command would look like this


mysql -u myuser --password=mypassword --database=mydatabase -e "source /opt/db_scripts/create_person_table.sql"


If there are multiple sql files the clause on the '-e' option would look like this


mysql -u myuser --password=mypassword --database=mydatabase -e "source /opt/db_scripts/create_person_table.sql;source /opt/db_scripts/create_employee_table.sql"

JUnit

The setup() and tearDown() methods of the TestCase class apparently behaves differently from what some people would have thought. If these methods are used in a test class, one would think that it would be only called once for the lifetime of the test class. But it is not the case.

To illustrate this further, lets consider the sample code below. It's main purpose is to show how the methods above behaves when the test case is run.

Say we have this test class:

1.public class TestPerson extends TestCase{
2.       private Person person;
3.
4.       protected void setup(){//create Greg
5.           person = new Person("Greg");
6.       }
7.
8.       protected void tearDown(){
9.           person = null;
10.      }
11.
12.       public void testCreateChild(){
13.          person.createChild("Nimfa");
14.       }
15.
16       public void testMarrySomebody(){
17.           person.getMarriedTo("Laila");
18.       }
19.}

When this test case is run, here's what's going to happen:

1. At line 2, the person variable is declared and initialized
2. At line 4, setup() method is called
3. At line 12,  testCreateChild() method is called (assuming this is the first method called)
4. At line 8,  tearDown() method is called
5. At line 2, the person variable is declared and initialized again
6. At line 4, setup() method is called is called again
7. At line 16,  testMarrySomebody() is called
8. At line 8,  tearDown() method is called

After code execution, Greg will loose a child names Nimfa then get married to Laila instead of having both the child and getting married. Sad story....

Here's the explanation. In a nutshell, the number of test methods we have, in this case two(testCreateChild() and testMarrySomebody()), is directly proportional to the calls of the setup() and tearDown() methods and to the declaration and initialization of the member attributes. The variable person will be instantiated again with a new Person object but with the same value which is 'Greg' for each method loosing the previous state of the child created. Greg will always end up getting married and not having a child.

If the intention on line 5 is to create an object which has a state consistent across all test methods, then it is a problem, since JUnit will basically create a new object per test methods. Object state maybe different from each test methods. The best approach would be to not use the setup() but instead initialize the object in the test method itself. So the object(s) initialized at the setup() method is never reused to the rest of the test methods. The tearDown() method is not necessary since it's just destroying the created object unless, a specific resource(i.e. database connection, etc.) needs to be released and should be called here.

Ten Commandments of Egoless Programming

A friend from work forwarded this information to me, which hed extracted from a TechRepublic article and I thought it was pretty interesting. I'm the Apache Software Foundation Group follows this very much, as it reflects how good their software products are and its free!!!

Here are the 10 Commandments of Egoless Programming, my friend willingly shared.

1. Understand and accept that you will make mistakes.
2. You are not your code.
3. No matter how much "karate" you know, someone else will always know more.
4. Don't rewrite code without consultation.
5. Treat people who know less than you with respect, deference, and patience.
6. The only constant in the world is change.
7. The only true authority stems from knowledge, not from position.
8. Fight for what you believe, but gracefully accept defeat.
9. Don't be "the guy in the room."
10. Critique code instead of people-be kind to the coder, not to the code.

Installing Solaris 10 Software Companion CD from ISO Image

To install the software companion from an iso image on a solaris machine follow this steps:

1. Type the command lofiadm -a /export/temp/software.iso. This command creates a block device from a file which can be mounted. The block device will look something like this /dev/lofi/1.

2. Mount the block device by typing this command mount -F hsfs -o ro /dev/lofi/1 /mnt. We are mounting a file system of type hsfs(High Sierra File System) which is basically a representation of our iso image (compact disc). We can also use the cdfs(Compact Disc File System) if the file is being mounted from a cdrom. The ro option basically means its a read-only.

3. You can install one package at a time, but for the purpose of this demo, I'm gonna install all of the packages. Next step is to create an admin file say on this path /var/tmp/admin. The admin file should be created by root user. It contains the following:

mail=
conflict=nocheck
setuid=nocheck
action=nocheck
partial=nocheck
instance=overwrite
idepend=nocheck
rdepend=nocheck
space=check

Now this will install the packages without having the user interact with the package installation.

4. Change directory to the location of the software companion packages relevant to your architecture. In my case I'll be installing it on a Solaris 10 OS. After changing to that directory it should look something like this /mnt/Solaris_Software_Companion/Solaris_sparc/Packages.

5. As a root, execute this command pkgadd -a /var/tmp/admin -d /mnt/Solaris_Software_Companion/Solaris_sparc/Packages. The -a option overrides the default admin file with the one we just created. 

6. Once it executes, it will display all the available packages to be installed and will asks you to install a package or all of the packages. Choose 'all'. Installation will proceed to run.

Installing CouchDB Version 0.10.1 On Fedora 4

I've heard about the hype on CouchDB from this link NoSQL and the design behind it is pretty interesting. It's a different kind of database that doesn't use schemas at all. Built on Erlang which is a well known language solid enough to handle a grand scale of processes without sacrificing performance. Its data structure is a B-tree and uses MapReduce to traverse it at an incredible speed. CouchDB is document based and provides the option of
working online and offline. With its interesting characteristics, I decided to install it on my Fedora machine.

Here are the dependencies I've installed (assuming everything was a downloaded source):
    1. curl-7.19
    2. SpiderMonkey - js-1.7.0
    3. ICU - icu4c-4_2_1
    4. Erlang OTP - otp_src_R13B03
    5. autoconf-2.13

Somebody had provided a script to properly and easily wire SpiderMonkey with CouchDB as can be seen from http://74.125.47.132/search?q=cache:http://dt.in.th/2008-03-03.spidermonkey-linux.html. This is our script:

#!/bin/bash
if test "$USER" = root; then
    wget http://ftp.mozilla.org/pub/mozilla.org/js/js-1.7.0.tar.gz -O- | tar xvz
    cd js/src
    make -f Makefile.ref
    mkdir -p /usr/include/smjs/ -v
    cp *.{h,tbl} /usr/include/smjs/ -v
    cd Linux_All_DBG.OBJ
    cp *.h /usr/include/smjs/ -v
    mkdir -p /usr/local/{bin,lib}/ -v
    cp js /usr/local/bin/ -v
    cp libjs.so /usr/local/lib/ -v
else
    echo "You must be root. Try sudo $0"
fi

To ensure that CouchDB will compile properly, use the options --with-js-lib and --with-js-include when installing it. Type the command couchdb to run it. Then try to run this command curl http://127.0.0.1:5984/. You should see something like this {"couchdb":"Welcome","version":"0.10.1"} as the response. This means that the database was successfully installed.

Futon is a great web interface to the database. Be sure to run all tests from Futon. If the URL used to access Futon is say http://localhost:5984/_utils, there are 3 tests that will fail - oauth, replication and security_validation. This is a known issue for some common network configurations. My setup is basically a Windows XP accessing thru Futton the CouchDB server instance  which is installed on a Fedora machine. Both machines are connected via a router without any DNS configurations. Whatever the network configuration is, this URL will always work and all tests passes - http://127.0.0.1:5984/_utils.

I prefer a different ip address used to access Futon so I went ahead and updated two configuration files which are default.ini and local.ini. The settings from local.ini will override the default.ini. Depending on your installation of CouchDB, these files can be found at /usr/local/etc/couchdb directory. Change the binding address to suit your needs.

Checksum App for Windows is a Worm!

Do not download the Checksum App from this site http://corz.org/windows/software/checksum/ . Once downloaded and run, a worm is loaded! Please see the image below. I'm glad I had my Lavasoft Ad-Aware running. Somehow my McAfee didn't detect it.

Internationalizing Numbers and Dates in Java

Locale

It's easy to be tempted to find a tool that can do a specific job. Consider formatting Dates and Numbers for different countries. If you do find a tool, great, but that's just making your application dependent on another tool and redundant since Java already provides you an API that does the job. There's a ton of language support that Java provides. If Klingon would become a language in the future, I'm sure Java will support it, hopefully here on earth.

A Locale in Java is a representation of the Language and the Country. It's also a class. Their's two properties of it that is a must - language id and country code each of which consists of 2 characters. Say if you have the German language, the locale is "de". If the language is still German but of the country Austria then the locale is "de_at". Locale for United States would be "en_US". There's a lot of websites that explains the different locales in different countries so I'm gonna leave it out.

When instantiating a Locale take its important to note what arguments are being passed to its constructor. I've come across a bug in one of the applications I've worked at where a line of code was passing a language id of this form languageId_countryCode where in fact it should only be languageId. If you need to pass both to the constructor, separate those two values out. See example below.

Locale germanLanguageId = new Locale("de_at"); // wrong assignment of locale value.

Locale germanLanguageId = new Locale("de"); // correcet assignment.

If both the language id and country code is needed then it should look like this.

Locale germanLanguageIdAndCountryCode = new Locale("de", "at");

Localized Or Internationalized Date

This is pretty straight forward. Just use the DateFormat class and pass in the right locale to get the right date display as shown below. When you actually want to get an instance of that class, use the factory method getDateInstance(int style, Locale locale).

Date date = new Date(); // Now the date can be of any type as long as its an object.

DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.SHORT, locale);

String internationalizedDateString = dateFormat.format(date);

Now depending on your current Locale, let's say we are using the german language id de, their date would actually be in this format. This is due to the fact that we chose the format type SHORT.

dd.mm.yyyy // so for todays date in german it would be 14.11.2009, thank goodness I decided not to write

                   // this yesterday!

Now for locale en_US it would be 11/14/2009. Of course the code will take care of doing the formatting for whatever locale the application is supporting.

Localized or Internationalized Number

First thing to do is to always define the pattern of the number that we would like to show in different locales. When I say pattern, its how many zeros do we want to show and if we don't want to show anything is the value of the number is equal to zero, etc. The DecimalFormat class has a method applyPattern(String pattern) where we can plugin the pattern we want. We can get an instance of this class by using the factory method of NumberFormat and cast it to DecimalFormat as shown below.

float aNumber = 1,500.98;

DecimalFormat decimalFormat = (DecimalFormat)NumberFormat.getInstance(locale); //factory method

decimalFormat.applyPattern("#,##0.00"); //our cool pattern

String internationalizedAndFormattedNumber = decimalFormat.format(aNumber );

Now the pattern #,##0.00 means that the grouping of the number is in thousand. The hash sign is any digit, where if its zero it won't display that number. The zero in the pattern is a digit, where if its zero, it would display as 0. We are also restricting the number of digits after the decimal to two digits. Say if we have a number 1,450.50 in english locale (en_US), upon formatting this to german (de) it should show up as

1.450,50. The comma becomes as period and the period becomes a comma. Of course, if the number we are trying to format is in millions, then our pattern should look something like this #,###,##0.00.

If the pattern is missing, some numbers get displayed in some other locales and some would display just fine. If our number is 0 to be formatted in german it might appear like this 0.0E3 which just confuses our business people looking at our localized spreadsheet. A pattern must be supplied always to display the right number in the right format. If a number in our localized csv file displays as 12.61 and then opened in excel, that number will be translated into a date equal to December 1967. This is the side effect of not supplying a pattern.

One last tip, if an application is processing data and converting it into CSV for excel viewing, remember to replace all carriage line feeds or new lines embedded in a String value with an empty String (" "). Say we have the String "The value\n is supposed to be\n entered here". This value should be in one row on a cell right? But with the new line character \n excel will actually break those strings into 3 rows. It would be

                Cell A

Row 1     The value

Row 2     is supposed to be

Row 3     entered here

Have fun with I18N !