Start/Stop MySQL on OS X Leopard

/Library/StartupItems/MySQLCOM/MySQLCOM [start|stop|restart]

search and replace text in files on the command line

find . -name "*.php" -print | xargs sed -i 's/foo/bar/g'

use '-maxdepth 1' before -name if you want to restrict to current directory

run X programs on your mac (say, on a parallels linux host or remotely)

Run this on the mac side:

ssh -X user@host

Find and delete duplicate records in MYSQL

DELETE t2 from table AS t1, table AS t2 WHERE t1.field = t2.field AND t2.id < t1.id

encode mp3s on the fly

I use this to record audio programs (voice quality). This gives fairly
small files, which I like because it makes it possible to save a LOT of
sound on a cd, and I find that the quality is satisfactory for me.



Here is the command:

sox -t ossdsp -w -s -r 44100 -c 1 /dev/dsp -t raw - | lame --nores -b 16
-x -m m - $(date +"/home/david/test--%a%d%b%Y_%I-%M%p.mp3") &

Change the number 16 to something else, like 32 to get better quality.
Like this. (These examples produce mono.)

sox -t ossdsp -w -s -r 44100 -c 1 /dev/dsp -t raw - | lame -b 32 -x -m m -
$(date +"/home/david/audio-temp/--ChangeMyName--%a%d%b%Y_%I-%M%p.mp3") &

This part of the command:
$(date+"/home/david/test--%a%d%b%Y_%I-%M%p.mp3")
produces a file name with a date and time extension as part of it.



For stereo, try this:

sox -t ossdsp -w -s -r 44100 /dev/dsp -t raw - | lame -t -r -x -h -b 160
-s 22.05 -q 5 --nores --strictly-enforce-iso - $(date
+"/home/david/test_%a%d%b%Y_%I-%M%p.mp3")

or

sox -t ossdsp -w -s -r 44100 /dev/dsp -t raw - | lame -t -r -x -h -b 128
-s 22.05 -q 5 --nores --strictly-enforce-iso - $(date
+"/home/david/test_%a%d%b%Y_%I-%M%p.mp3") 

New way to get all This American Life episodes

Do this in the directory you want to download them into. Adjust numbers.

counter=318;  while [  $counter -lt 366 ]; do echo "http://audio.thisamericanlife.org/jomamashouse/ismymamashouse/$counter.mp3" | xargs wget; let counter=counter+1; done;

MySQL Select the maximum row in each group

SELECT f.id AS id, f.client AS client, f.customer_id AS customer_id, f.bill_email AS bill_email
FROM (
   SELECT client, max(id) AS top_id
   FROM activity GROUP BY client
) AS x INNER JOIN activity AS f ON f.client = x.client and f.id = x.top_id;

Another way to find installed perl modules

find `perl -e 'print "@INC"'` -name '*.pm' -print

Regex to match a price with or without the decimal

\d+\.?\d{0,2}?

command to kill a process by name

kill `ps -axwwwww | grep MyAPP | grep -v grep | awk '{print $1}'`

Applescript to close all open applications

tell application "System Events" to set the visible of every process to true

tell application "System Events" to set theApps to (name of every process whose visible is true and name is not "Finder" and name is not (my name as text))

repeat with theApp in theApps
    tell application theApp to quit
end repeat



Applescript to unmount disk image


tell application "Finder"
    if exists "MyDisk" then
        do shell script "hdiutil detach \"/Volumes/MyDisk\""
    end if
end tell



Another way:


tell application "Finder"
    if (exists the disk "local_home_backup") then
        eject "local_home_backup"
        delay 2
    end if
end tell

MySQL use CONCAT and UPDATE together in mysql to add to an existing string

UPDATE products SET SHIPPING = CONCAT('+', SHIPPING) WHERE SHIPPING != "" AND SHIPPING REGEXP '^[^\+]'

install perl module at the command line with CPAN

perl -MCPAN -e 'install Bundle::CPAN'

Group records by month in mysql

SELECT
count(*) as REQUESTS,
DATE_FORMAT(date,"%a %b %e, %Y") as DATE,
date as DATE2,
MONTH( date ) as "MONTH OF THE YEAR"
FROM requests
WHERE date BETWEEN '2007-10-01' AND '2008-06-17'
GROUP BY "MONTH OF THE YEAR"
ORDER BY DATE2 ASC

Perl subroutine to validate e-mail addresses

    sub is_valid_email ($) {
       my ($addr) = @_;
       my $atext = qr/[A-Za-z0-9\!\#\$\%\&\'\*\+\-\/\=\?\^\_\`\{\|\+\~]/;
       my $dot_atom_text = qr/$atext+(\.$atext+)*/;

       my $no_ws_ctl_char = qr/[\x01-\x08\x0b\x0c\x0e-\x1f\x7f]/;
       my $qtext_char = qr/([\x21\x23-\x5b\x5d-\x7e]|$no_ws_ctl_char)/;
       my $text = qr/[\x01-\x09\x0b\x0c\x0e-\x7f]/;
       my $qtext = qr/($qtext_char|\\$text)*/;
       my $quoted_string = qr/"$qtext"/;

       my $quotedpair = qr/\\$text/;
       my $dtext = qr/[\x21-\x5a\x5e-\x7e\x01-\x08\x0b\x0c\x0e-\x1f\x7f]/;
       my $dcontent = qr/($dtext|$quotedpair)/;       
       my $domain_literal = qr/\[(${dcontent})*\]/;

       if ( $addr =~ /^($dot_atom_text|$quoted_string)\@($dot_atom_text|$domain_literal)$/ ) {
               return 1;
       } else {
               return 0;
       }
   }

Sort a directory by filesize, ascending

ls -alSr

Force install a perl CPAN module

perl -MCPAN -e "CPAN::Shell->force(qw(install IO::Socket::SSL));"

Use perl on the command line with modules

perl -e 'use POSIX qw(strftime ceil floor); my $ts = strftime( "%Y%m%d%H%M%S",localtime(time)); my $t = substr($ts,0,12); print "TIME: $ts $t\n"';

Use mod_rewrite to create "fake sites" (with subdomains) on a single apache vhost

Note: the subdomains need to be server aliases on the vhost

RewriteCond %{HTTP_HOST} ^(subdomain1|subdomain2)\.yoursite\.com [NC]
RewriteRule ^(.*) http://www.yoursite.com/%1/%{REQUEST_URI} [L,P]

Apparantly this also works:

RewriteCond %{HTTP_HOST} ^(sub1|sub2)\.yoursite\.com [NC]
RewriteRule (.*) http://www.yoursite.com/%1/$1 [R=301,L]

MySQL Select random integer between 0 and 82

SELECT FLOOR(0 + (RAND() * 82))

Change case on file extensions in a directory

for f in *.JPG; do mv $f `basename $f .JPG`.jpg; done;

Count files in subdirectories

find . -type f | wc -l

MySQL search and replace

UPDATE table_name SET table_field = REPLACE(table_field,'replace_that','with_this');

shell script to copy all MySQL databases from one server to another

#!/bin/bash

# destination for the backups
# mysqlbackup.lst needs to be in this dir,
# and list all the db names you want to transfer
dest="/home/user/downloads";
user="user";
pass="passwd";
host1="host1.net";
host2="host2.net";

for db in `cat $dest/mysqlbackup.lst`
do

    echo "dumping $db..."
    /usr/bin/mysqldump -u $user -h $host1 -p$pass $db > $dest/$db.sql
    echo "creating $db locally..."
    /usr/bin/mysqladmin -u $user -p$pass create $db
    echo "importing $db.sql to $db..."
    /usr/bin/mysql -u $user -h $host2 -p$pass $db < $dest/$db.sql
    echo "removing $dest/$db.sql..."
    rm $dest/$db.sql

done

Create an SSH tunnel to connect to remote VNC server

ssh -c 3des -N -L5901:255.255.255.211:5901 -2 -l rory 255.255.255.211 -p 22

start a vnc server

vncserver :1 -depth 8 -geometry 800x600