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.

Advertisement

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

The first one is the most important. It reduced the time for a join from more then 10 hours to a couple of minutes.

MySQL backup

Just for the record: How to combine mysqldump and zip to archive all MySQL databases on the host. I am using a simple MySQL database server on localhost, to organise research tables before analysis.

mysqldump --all-databases | zip -9 allDB_backup_110415.sql.zip -

mysqldump --all-databases writes the content of all databases into the pipe and
zip -9 Filename - compresses the standard input (note the dash ‘-‘ at the end!) to ‘Filename’ (-9 gives maximum compression).

The reverse following the man page of ‘mysqldump’:

You can load the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

Mathematical expressions in R plot

Mathematical anotations to R plots can be formated LaTeX style with the expression function. The expression() can be included in

• plot titles

  title( expression(...))

• axis anotations

  plot( ... , xlab = expression(...))

  or

• the plot panel itself

  text( x , y , expression(...))

An example:

plot( 0 , 0 , type = "n" , xlab = expression( "Nothing" * (mu*mol/l)) )
text( 0 , 0 , expression(beta>=0.2))
title( expression( "Only an expression() demo " * theta^(2*pi)))


It is even possible to update the plot anotation from a variable (found on the R forum)

[R] Expression in plot text

Roger Koenker roger at ysidro.econ.uiuc.edu
Wed Dec 6 20:22:05 CET 2000

expression(paste(hat(theta),'= ',that)))

We’ve been here before. To get an expression either use parse on your
pasted character string or substitute on an expression. There are worked
examples in the list achives. The neatest is

title(substitute(hat(theta) == that, list(that=that)))

(note it is == not =)

Violinplots with R

Just for the record a nice alternative to boxplot() from the R introduction on MOAC@University of Warwick:

Violin plots are essentially pretty versions of box plots, where the width is set by the local density. For skewed distributions, you get things that look a bit like “violins”, hence the name.

The catch is that we need to install and load the package UsingR to get the violin plot function. […]
library(UsingR)
simple.violinplot(Age ~ Gender, data=Santa, col = "red")

Match and replace TAB

I am using the terminal inside emacs to modify and clean textfiles (colon separated) for import into MySQL – mostly with SED, GREP, AWK.
Replacing TAB in a textfile with SED was not as straightforward as I thought. So Google came up with an excellent post on macworld.com and here especially one post suggesting perl as a solution. It turned out perl behaves inside the pipe the same way as SED/AWK.

Replacing TAB ‘\t‘ with colon ‘,‘ in a textfile ‘SomeFile.txt‘ can be done with:

perl -pi -e 's/\t/,/g' SomeFile.txt

or using the pipe, which can be combined with grep, sed, awk, tr, head, tail, etc…

cat SomeFile.txt | perl -pi -e 's/\t/,/g'

*Very* similar to the sed invocation, except for the flags. “-pi” for “*print* every line out after making the changes, while editing *inplace*. That is, no backup file is kept. In reality, a temp file is written, and if all is well (ie the operation succeeds), the temp file is shifted “onto” the original file, giving the appearance of an in-place edit.

Emacs as MySQL frontend

After working quite a time with some minor MySQL databases in the (Emacs-) Terminal I just looked up the preinstalled SQL related emacs functions. Just entered M-x sql TAB and indeed the autocompletion showed a function sql-mysql, as expected…

I gave it a try with
M-x sql-mysql
and after prompting for database, servername, username and password Emacs connected to the database and presented the MySQL shell. So I bound sql-mysql to some keyboard shortcut, BUT entering the whole connection parameters each and every time was not acceptable.

Atomized.org has a really nice post on Enhancing Emacs’ SQL Mode (you can have a look, but you cannot read the post before pasting the content to a text editor). There I found some excellent functions which would provide a solution:

(setq sql-connection-alist
'((pool-a
(sql-product 'mysql)
(sql-server "1.2.3.4")
(sql-user "me")
(sql-password "mypassword")
(sql-database "thedb")
(sql-port 3306))
(pool-b
(sql-product 'mysql)
(sql-server "1.2.3.4")
(sql-user "me")
(sql-password "mypassword")
(sql-database "thedb")
(sql-port 3307))))

(defun sql-connect-preset (name)
"Connect to a predefined SQL connection listed in `sql-connection-alist'"
(eval `(let ,(cdr (assoc name sql-connection-alist))
(flet ((sql-get-login (&rest what)))
(sql-product-interactive sql-product)))))

(defun sql-pool-a ()
(interactive)
(sql-connect-preset 'pool-a))

Now, you can just run sql-pool-a and get connected right away. Because the buffers have good names, you can easily fire up many connections.

I included it in my .emacs file and appended
(DefGlobKey "s-a" 'sql-pool-a)
and with a keystroke the database promt appears.

Thx, atomized.org, but what’s that webpage giving you an epileptic fit looking at it?!. Excellent page 🙂

MySQL command line with OS X 10.6.

After installing MySQL 5.1.54 on Mac OS X 10.6.6 (Snow Leopard) I tried to access the server in the terminal and got:

-bash: mysql: command not found

Bash could not find the path, so I had to add it. I am not that familiar with the internals of the unix terminal anyway, so I found a highly informative thread on superuser.com.

Especially the post of Jim Logan solved the issue:

Take a look at the file /etc/paths, which is used by /usr/libexec/path_helper, which is used by /etc/profile. For MacPorts, use “sudo” to append “/opt/local/bin” to the end of the /etc/paths file and reopen the terminal window.

So I opened the terminal and used nano:
sudo nano /etc/paths

and appended the line
/usr/local/mysql/bin

to the file, saved, exited, restarted the terminal …. and voila, solved.