BASH: Convert Uppercase to Lowercase letters

Vivek Gite on nixCraft suggests tr to tranform uppercase letters in the textfile input.txt to lowercase and output the transformed text to output.txt.

tr '[:upper:]' '[:lower:]' output.txt

I needed to clean up a messy old scriptfile where I lost track of my variable naming convention.

Very useful indeed 🙂

Advertisements

Add public key behind a firewall in Ubuntu Shell

In short: Use
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80/ --recv-key E084DAB9
instead of
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-key E084DAB9
This way you force port 80 which is usually clear.

I got the idea from the answer of Phil Bradley on the superuser.com forum. He claimed that this would be fixed in Natty, but it isn’t although the configuration file he mentions has the port80 specification added by default, apt-key does not use it. The above snippet solves that.

For those Ubuntu users who have no idea what I am talking about:

Installing the newest R-version in Ubuntu requires to append the CRAN repository to you /etc/apt/sources.list. One might hit Alt+F2 and enter
gksu gedit /etc/apt/sources.list

With Xubuntu you would use mousepad instead of gedit. In any distro you can use
sudo nano /etc/apt/sources.list
in a terminal.

Usually I add the line
deb http://cran.uib.no/bin/linux/ubuntu natty/
at the end of the file and update with
sudo apt-get update.

CRAN at University of Bergen is closest to me. You might want another one (check the r-project.org site for mirrors).

apt-get update answers with a warning
GPG error: http://cran.uib.no nat/ Release: The folowing signatures coldn't be verified because the public key is not abailable

That is not a problem. One can install R and packages anyway, but it is better to have the public key.

Behind a firewall (and many public and open hotspots block several ports) it is not possible to use

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-key E084DAB9

since the port through which the keyserver is contacted is blocked on most firewalls. You have to force port 80 by:
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80/ --recv-key E084DAB9

After the key is added
sudo apt-get update
sudo apt-get install R-recommended emacs ess

proceeds without warning nor error.

Sane PATH variable in Emacs on Mac OS X

On Mac OS X the system PATH variable is not recognized by emacs. This means that one can not simply type

mysql

in the emacs shell to get into the database. The emacs shell complains about “binary not found”.

Indeed

echo $PATH

reveals that emacs just looks into /bin, /usr/bin, /sbin and /usr/sbin.

To set the $PATH variable inside emacs one can append the following lines to the .emacs file (found on github, hattip Alex Payne):

; sane path
(setq path "/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:/usr/local/mysql/bin")
(setenv "PATH" path)

Next time Emacs starts one can go to the shell and

mysql

presents the database prompt.

Compressed backup of MySQL database

Wrote several posts on this topic, but none was 100% right. The following is a blockquote from webcheatsheet.com and looks much better researched then my previous tries:

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]
If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

Create an appropriately named database on the target machine
Load the file using the mysql command:
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql
To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

Remove empty lines with SED

Raw data is often delivered in Excel-sheets with a lot of noise and formating around. For analysis in R or other packages the real raw data is required. Scripting the “deformating” in plain text / csv files using shell tools like SED, AWK or Pearl to remove excess text in the datasheets makes it possible to rerun the procedure or track systematic errors.

Removing empty lines from a file containing code in plain text (like .csv, .html, .php, etc…) is very easy with SED in a UNIX/ MAC OS shell and even possible in the Windows CMD (after installing SED). The following is a blockquote from ZoneO-tips for Mandriva Linux which I found really useful and well written:

So, open up a konsole and move into the directory where your file resides (cd MyDirectory). And here we go with the two lines that’ll do the job

sed '/^$/d' myFile > tt
mv tt myFile

Here is what happens:

sed '/^$/d' myFile removes all empty lines from the file myFile and outputs the result in the console, > tt redirects the output into a temporary file called tt,
mv tt myFile moves the temporary file tt to myFile.

Now, you may have 100 html files to correct at the same time. That’s where foreach comes in… Let’s say you want to correct all files ending with .html, here is what you should do:

Open up a konsole, move into the directory where your html files reside, type the following commands:

foreach file (*html)
sed '/^$/d' $file > tt
mv tt $file
end

Finished!

MySQL tables must have indexes

A newbee to MySQL myself I had a rather odd experience: A simple inner join of three research tables took severeal hours. I posted the problem on the forum.mysql.com and the following is the result of the post.

To give the conclusion first:

  1. Always create indexes for each key-variable in each and every MySQL table, no matter how small or big.
    ALTER TABLE MyTable ADD INDEX(MyID)
    where MyTable is a Table in the database and MyID is one of the key variables.

  2. Never use brackets in consecutive joint statements! The right way is:
    select * from Table_A A inner join Table_B B on A.id = B.id inner join Table_C on A.id = C.id;
    Brackets force the creation of temporary tables and increase execution time
  3. The first one is the most important. It reduced the time for a join from more then 10 hours to a couple of minutes.