Month: January 2014

I was given a flat file that had the date in this format

Date,Hour,Min,Seconds,Temperature,Unused field
11/25/2013,14,0,0,59.69,
11/25/2013,14,15,0,60.38,
11/25/2013,14,30,0,61.85,
11/25/2013,14,45,0,62.57,

but the table that I need to put it into is formatted as

(64349,'2014-01-06 20:45:00',44.258,NULL),
(64350,'2014-01-06 21:00:00',44.978,NULL),

In BBEdit I used this grep to convert the date and separate out the temp into a separate field.

Find
([0-9]+)/([0-9]+)/([0-9]+),([0-9]+),([0-9]+),([0-9]+),([0-9.]+),
Replace
'\3-\2-\1 \4:\5:\6',\7,NULL),

What I’ve done is separate out the different parts of the date with parentheses. Then I rearranged the month, day, year with \3-\2-\1 and added the dash separator. The time is in the correct order so all I need to do is put in the right separator. I want an SQL file when I’m done so I added the ‘ field separators and the NULL field and line terminator.

I’ll use the numbering feature of BBEdit to add the row numbers and then add a paren at the beginning of the line. I also need to remember to change the last comma to a semi-colon. Then I can import the data to the MySQL table.

After I rearranged the date, I did need to fix months and days that were only one digit—they need a 0 before the digit. I did this manually with a find and replace. Same with the time. If you did this a lot, you could write a grep that it looked for records with only one digit and prefix it with a zero. A ? will look for exactly one occurrence of a digit. Then do a second pass to get the rest. Or you could use the # to look for one or two digits.

I’m playing around with bash and trying to understand how it thinks. I manage a bunch of servers so I wrote some aliases and functions to help me manage them. In my .profile or my .bash_profile I added a line to run the shell script that helps me do things. The last line is:

    . ~/.bash_config

If you make changes to this file, you can reload your profile and see the changes by running:

    source ~/.profile

I found a bunch of .bash_profile’s online and used some of the ideas from them. The best one is from Nathaniel Landau. I kept his basic structure and lots of his aliases. I also added a bunch of stuff people might be interested in. I left in a lot of stuff that I later rewrote so people learning bash can see my thought process as I developed the script.

#  ---------------------------------------------------------------------------
#  .bash_config
#  Description:  BASH configurations and aliases
#  Called from .profile or .bash_profile with . ~/.bash_config
#  Sections:
#  1.   Environment Configuration
#  2.   Make Terminal Better (remapping defaults and adding functionality)
#  3.   File and Folder Management
#  4.   Searching
#  5.   Process Management
#  6.   Networking
#  7.   System Operations & Information
#  8.   Web Development
#  9.   Reminders & Notes
#
# Source: http://natelandau.com/my-mac-osx-bash_profile/
# Modified extensively - January 2014
#  ---------------------------------------------------------------------------

#   -------------------------------
#   1.  ENVIRONMENT CONFIGURATION
#   -------------------------------

#   Change Prompt
#   ------------------------------------------------------------
#   export PS1="________________________________________________________________________________
     \n| \w @ \h (\u) \n| => "
#   export PS2="| => "
#  \e[ - Indicates the beginning of color prompt
#  x;ym - Indicates color code. Use the color code values mentioned below.
#  \e[m - indicates the end of color prompt
#   \e works on Ubuntu and in the OSX prompt, but not in echo commands on OSX use \033 instead
# Normal Colors
BLACK='\033[0;30m'        # Black
RED='\033[0;31m'          # Red
GREEN='\033[0;32m'        # Green
YELLOW='\033[0;33m'       # Yellow
BLUE='\033[0;34m'         # Blue
PURPLE='\033[0;35m'       # Purple
CYAN='\033[0;36m'         # Cyan
WHITE='\033[0;37m'        # White

# Bold - Change 0; to 1;
B_BLACK='\033[1;30m'      # Black
B_RED='\033[1;31m'        # Red
B_GREEN='\033[1;32m'      # Green
B_YELLOW='\033[1;33m'     # Yellow
B_BLUE='\033[1;34m'       # Blue
B_PURPLE='\033[1;35m'     # Purple
B_CYAN='\033[1;36m'       # Cyan
B_WHITE='\033[1;37m'      # White

NC='\033[m'               # No Color - Reset color prompt

#   Make it clear in the prompt which system I’m on
#   localName=$(echo $HOSTNAME | cut -d.  -f 2); # Pull out the local part of the HOSTNAME
if [ "$HOSTNAME" = "server" ]; then
    hostColor=$B_RED;
elif [ "$HOSTNAME" = "dave" ]; then
    hostColor=$B_PURPLE;
elif [ "$HOSTNAME" = "don" ]; then
    hostColor=$B_CYAN;
# Mac $HOSTNAME has .local appended, cut it out
elif [ $(echo $HOSTNAME | cut -d.  -f 2) = "local" ]; then
    hostColor=$B_BLACK;
else
    hostColor=$B_GREEN;
fi
    # Colors are in {} just to be clear, even though \ is not a valid variable character.
    # NC doesn’t need to be in {} since it is followed by a space.
    export PS1="${B_BLUE}\u@${hostColor}\h: ${B_GREEN}\w$NC $ "

#   Set Paths
#   ------------------------------------------------------------
#    export PATH="$PATH:/usr/local/bin/"
#    export PATH="$PATH:/usr/local/git/bin:/sw/bin/:/usr/local/bin:/usr/local/:/usr/local/sbin:/usr/local/mysql/bin"
    
#   Set Default Editor (change 'Nano' to the editor of your choice)
#   ------------------------------------------------------------
    export EDITOR=/usr/bin/vim
    if [ "$HOSTNAME" = "Dave" ]
    then
        export EDITOR=/usr/bin/nano
    fi

#   Keep the history file around forever
    export HISTSIZE=10000
    export HISTFILESIZE=1000000000

#   Set default blocksize for ls, df, du
#   from this: http://hints.macworld.com/comment.php?mode=view&cid=24491
#   ------------------------------------------------------------
    export BLOCKSIZE=1k

#   Add color to terminal
#   from http://osxdaily.com/2012/02/21/add-color-to-the-terminal-in-mac-os-x/
#   ------------------------------------------------------------
    export CLICOLOR=1
#   export LSCOLORS=ExFxBxDxCxegedabagacad

    export TZ=America/Los_Angeles
    
#   -----------------------------
#   2.  MAKE TERMINAL BETTER
#   -----------------------------
#   Override an alias with command e.g. command cp file1 file2
#   -i is the interactive flag, -v is the verbose flag
alias cp='cp -iv'                           # -i warns before overwriting
alias mv='mv -iv'                           # -i warns before overwriting
alias mkdir='mkdir -pv'                     # -p Create intermediate directories as required
mcd () { mkdir -p "$1" && cd "$1"; }        # Makes new Dir and jumps inside

alias ll='ls -FGlAhp'                       # 
alias less='less -FSRXc'                    # Preferred 'less' implementation

cd() { builtin cd "$@"; ll; }               # Always list directory contents upon 'cd'
alias cd..='cd ../'                         # Go back 1 directory level (for fast typers)
alias ..='cd ../'                           # Go back 1 directory level
alias ...='cd ../../'                       # Go back 2 directory levels
alias .3='cd ../../../'                     # Go back 3 directory levels
alias .4='cd ../../../../'                  # Go back 4 directory levels
alias .5='cd ../../../../../'               # Go back 5 directory levels
alias .6='cd ../../../../../../'            # Go back 6 directory levels
alias ~="cd ~"                              # Go Home

alias which='type -all'                     # Find executables
alias path='echo -e ${PATH//:/\\n}'         # Echo all executable Paths

#
#   OSX specific aliases and commands
#
alias f='open -a Finder ./'                 # Opens current directory in OSX Finder

trash () { command mv "$@" ~/.Trash ; }     # Moves a file to the OSX trash
ql () { qlmanage -p "$*" >& /dev/null; }    # Opens any file in OSX Quicklook Preview
alias DT='tee ~/Desktop/terminalOut.txt'    # Pipe content to file on OSX Desktop

#   lr:  Full Recursive Directory Listing
#   ------------------------------------------
alias lr='ls -R | grep ":$" | sed -e '\''s/:$//'\'' -e '\''s/[^-][^\/]*\//--/g'\'' -e '\''s/^/   /'\'' -e '\''s/-/|/'\'' | less'

#   mans:   Search manpage given in agument '1' for term given in argument '2' (case insensitive)
#           displays paginated result with colored search terms and two lines surrounding each hit. 
#           Example: mans mplayer codec
#   --------------------------------------------------------------------
    mans () {
        man $1 | grep -iC2 --color=always $2 | less
    }
alias mansearch='mans'
#   showa: to remind yourself of an alias (given some part of it)
#   ------------------------------------------------------------
    showa () { /usr/bin/grep --color=always -i -a1 $@ ~/Library/init/bash/aliases.bash | grep -v '^\s*$' | less -FSRXc ; }

#   -------------------------------
#   3.  FILE AND FOLDER MANAGEMENT
#   -------------------------------

zipf () { zip -r "$1".zip "$1" ; }          # Create a ZIP archive of a folder
alias numFiles='echo $(ls -1 | wc -l)'      # Count of non-hidden files in current dir
alias make1mb='mkfile 1m ./1MB.dat'         # Creates a file of 1mb size (all zeros)
alias make5mb='mkfile 5m ./5MB.dat'         # Creates a file of 5mb size (all zeros)
alias make10mb='mkfile 10m ./10MB.dat'      # Creates a file of 10mb size (all zeros)

#   extract:  Extract most known archives with one command
#   ---------------------------------------------------------
    extract () {
        if [ -f $1 ] ; then
          case $1 in
            *.tar.bz2)   tar xjf $1     ;;
            *.tar.gz)    tar xzf $1     ;;
            *.bz2)       bunzip2 $1     ;;
            *.rar)       unrar e $1     ;;
            *.gz)        gunzip $1      ;;
            *.tar)       tar xf $1      ;;
            *.tbz2)      tar xjf $1     ;;
            *.tgz)       tar xzf $1     ;;
            *.zip)       unzip $1       ;;
            *.Z)         uncompress $1  ;;
            *.7z)        7z x $1        ;;
            *)     echo "'$1' cannot be extracted via extract()" ;;
             esac
         else
             echo "'$1' is not a valid file"
         fi
    }

#   ---------------------------
#   4.  SEARCHING
#   ---------------------------

alias qfind="find . -name "                 # qfind:    Quickly search for file
ff () { /usr/bin/find . -name "$@" ; }      # ff:       Find file under the current directory
ffs () { /usr/bin/find . -name "$@"'*' ; }  # ffs:      Find file whose name starts with a given string
ffe () { /usr/bin/find . -name '*'"$@" ; }  # ffe:      Find file whose name ends with a given string

#   spotlight: Search for a file using MacOS Spotlight's metadata
#   -----------------------------------------------------------
    spotlight () { mdfind "kMDItemDisplayName == '$@'wc"; }

#   
alias h='history'                           # Shortcut for history
hist() { history | grep $1; }               # Find a command in the history, e.g hist man

#   ---------------------------
#   5.  PROCESS MANAGEMENT
#   ---------------------------

#   findPid: find out the pid of a specified process
#   -----------------------------------------------------
#       Note that the command name can be specified via a regex
#       E.g. findPid '/d$/' finds pids of all processes with names ending in 'd'
#       Without the 'sudo' it will only find processes of the current user
#   -----------------------------------------------------
    findPid () { lsof -t -c "$@" ; }

#   memHogsTop, memHogsPs:  Find memory hogs
#   -----------------------------------------------------
    alias memHogsTop='top -l 1 -o rsize | head -20'
    alias memHogsPs='ps wwaxm -o pid,stat,vsize,rss,time,command | head -10'

#   cpuHogs:  Find CPU hogs
#   -----------------------------------------------------
    alias cpu_hogs='ps wwaxr -o pid,stat,%cpu,time,command | head -10'

#   topForever:  Continual 'top' listing (every 10 seconds)
#   -----------------------------------------------------
    alias topForever='top -l 9999999 -s 10 -o cpu'

#   ttop:  Recommended 'top' invocation to minimize resources
#   ------------------------------------------------------------
#       Taken from this macosxhints article
#       http://www.macosxhints.com/article.php?story=20060816123853639
#   ------------------------------------------------------------
    alias ttop="top -R -F -s 10 -o rsize"

#   my_ps: List processes owned by my user:
#   ------------------------------------------------------------
    my_ps() { ps $@ -u $USER -o pid,%cpu,%mem,start,time,bsdtime,command ; }

#   ---------------------------
#   6.  NETWORKING
#   ---------------------------

#   Log in to other systems

id=$(who am i | cut -d\  -f 1)
server='192.168.194.220'
dave='192.168.201.11'
don='192.168.102.113'
purple='purple.aserver.com'

# Update the list of servers if you add one. It is used on connect and scp functions
servers=(server dave don purple)

#   The commented out aliases and functions are experiments with different ways to use aliases and functions

#   With the id placed in the alias, not generalizable
#   alias server='ssh myloginid@192.168.194.220'

#   Find the userID from login info
#   alias server='ssh $(who am i | cut -d\  -f 1)@192.168.194.220'

#   Make the userID an alias
#   alias myID='who am i | cut -d\  -f 1'
#   alias server='ssh "echo $(myID)"@192.168.194.220'
#   alias server='ssh $(myID)@192.168.194.220'

#   Make the userID a variable and use it later in the function
#   server() { id=$(who am i | cut -d\  -f 1); ssh $id@192.168.194.220; }

#   Make the userID a variable in the shell script and use it in an alias or function
#   $id can’t be local since isn’t called until the alias or function is invoked.
#   id=$(who am i | cut -d\  -f 1)
#   alias server='ssh $id@192.168.194.220'
#   server()  { ssh $(myID)@192.168.194.220 ; }

# Using the select conditional to pick a server
# This example shows how a case statement and $REPLY can be used
# It can be generalized so that answers aren’t hard coded
whichserver() {
    # We don’t need the list outside this function. Making it local assures that we don’t clobber a previous alias with the same name
    local serverlist=(server dave don purple)              
    PS3='Select a server. '
    
    select srv in ${serverlist[@]} "Quit"; do     # the @ expands all the elements of the array into separate words

        case "$REPLY" in
            # It would be nice if you could do this, but you can’t put anything in a case statement that isn’t one of the cases
            # arrayitem=$(( ${#serverlist[@]}+1 ))   
            1 ) 
                echo "Connecting to ${serverlist[$REPLY-1]}"; ssh "$id@$server" ;;
            2 ) 
                echo "Connecting to ${serverlist[$REPLY-1]}"; ssh "$id@$dane" ;;
            3 ) 
                echo "Connecting to ${serverlist[$REPLY-1]}"; ssh "$id@$dane" ;;
            
            # The last option is quit. (( )) is arithmetic evaluation. # counts the number of items in the array. 
            $(( ${#serverlist[@]}+1 )) )       
                echo "No server selected.";
        esac
        
        break

    done
}

#   Here's the same code using an eval to run the alias

connect() {
    # Requires an array called servers that has IP addresses or domain names.
    # It is defined when aliases are defined at the beginning of this script.
    if [ -z "$1" ]; then
        
        PS3='Select a server. '
        select srv in ${servers[@]} "Quit"; do     # the @ expands all the elements of the array into separate words

            if [ "$REPLY" -lt $(( ${#servers[@]}+1 )) ]; then
                    echo "Connecting to ${servers[$REPLY-1]}"
                    ssh ${!servers[$REPLY-1]}
             else
                    echo "No server selected.";
            fi
        
            break

        done
    else
        echo "Connecting to $1"
        ssh ${!1}
    fi
}

# Share .bash_config. Requires . ~/.bash_config in .bash_profile
share() {
    
    # This works if you want to list out each server
    # scp .bash_config ${id}@${server}:.bash_config
    # scp .bash_config ${id}@${dane}:.bash_config
    # scp .bash_config ${id}@${dan}:.bash_config
    
    for serv in "${servers[@]}"; do
        echo "Connecting to $serv"
        scp .bash_config ${id}@${!serv}:.bash_config
    done
}

alias myip='curl ip.appspot.com'                    # Use Google’s AppSpot site to find this machine’s public facing IP Address
alias netCons='lsof -i'                             # Show all open TCP/IP sockets
alias flushDNS='dscacheutil -flushcache'            # Flush out the DNS Cache
alias lsock='sudo /usr/sbin/lsof -i -P'             # Display open sockets
alias lsockU='sudo /usr/sbin/lsof -nP | grep UDP'   # Display only open UDP sockets
alias lsockT='sudo /usr/sbin/lsof -nP | grep TCP'   # Display only open TCP sockets
alias ipInfo0='ipconfig getpacket en0'              # Get info on connections for en0
alias ipInfo1='ipconfig getpacket en1'              # Get info on connections for en1
alias openPorts='sudo lsof -i | grep LISTEN'        # All listening connections
alias showBlocked='sudo ipfw list'                  # All ipfw rules inc/ blocked IPs

#   ii:  display useful host related informaton
#   -------------------------------------------------------------------
#   -e     enable interpretation of backslash escapes

    ii() {
        ## \e works on Ubuntu but not recognized on OSX, must use \033
        ## Didn’t work since colors weren’t defined. Now they are.
        ## local RED='\033[0;31m'          # Red
        ## local NC="\033[0m"              # Color Reset
        echo -e "\nYou are logged on to $HOST"
        echo -e "\nAdditional information: " ; uname -a
        echo -e "\n${RED}Users logged on:${NC} " ; w -h
        echo -e "\n${RED}Current date :${NC} " ; date
        echo -e "\n${RED}Machine stats :${NC} " ; uptime
        echo -e "\n${RED}Current network location :${NC} " ; scselect
        echo -e "\n${RED}Public facing IP Address :${NC} " ;myip
        #echo -e "\n${RED}DNS Configuration:${NC} " ; scutil --dns
        echo
    }

#   ---------------------------------------
#   7.  SYSTEMS OPERATIONS & INFORMATION
#   ---------------------------------------

alias mountReadWrite='/sbin/mount -uw /'    # mountReadWrite:   For use when booted into single-user

#   cleanupDS:  Recursively delete .DS_Store files
#   -------------------------------------------------------------------
    alias cleanupDS="find . -type f -name '*.DS_Store' -ls -delete"

#   finderShowHidden:   Show hidden files in Finder
#   finderHideHidden:   Hide hidden files in Finder
#   -------------------------------------------------------------------
    alias finderShowHidden='defaults write com.apple.finder ShowAllFiles TRUE'
    alias finderHideHidden='defaults write com.apple.finder ShowAllFiles FALSE'

#   cleanupLS:  Clean up LaunchServices to remove duplicates in the "Open With" menu
#   -----------------------------------------------------------------------------------
    alias cleanupLS="/System/Library/Frameworks/CoreServices.framework/Frameworks/LaunchServices.framework/Support/lsregister -kill -r -domain local -domain system -domain user && killall Finder"

#    screensaverDesktop: Run a screensaver on the Desktop
#   -----------------------------------------------------------------------------------
    alias screensaverDesktop='/System/Library/Frameworks/ScreenSaver.framework/Resources/ScreenSaverEngine.app/Contents/MacOS/ScreenSaverEngine -background'

#   ---------------------------------------
#   8.  WEB DEVELOPMENT
#   ---------------------------------------

alias apacheEdit='sudo $EDITOR /etc/httpd/httpd.conf'   # Edit httpd.conf
alias apacheRestart='sudo service apache2 restart'      # Restart Apache
alias mySQLRestart='sudo service mySQL restart'         # Restart mySQL
alias editHosts='sudo $EDITOR /etc/hosts'               # Edit /etc/hosts file
alias taile='tail -f /var/log/php_error.log'            # Tails PHP error logs
alias apacheLogs="less +F /var/log/apache2/error_log"   # Shows apache error logs
httpHeaders () { /usr/bin/curl -I -L $@ ; }             # httpHeaders:      Grabs headers from web page

#   httpDebug:  Download a web page and show info on what took time
#   -------------------------------------------------------------------
    httpDebug () { /usr/bin/curl $@ -o /dev/null -w "dns: %{time_namelookup} connect: %{time_connect} pretransfer: %{time_pretransfer} starttransfer: %{time_starttransfer} total: %{time_total}\n" ; }

#   ---------------------------------------
#   9.  REMINDERS & NOTES
#   ---------------------------------------

# Things I Can’t Remember
ticr() {
    echo -e "\n${B_PURPLE}Things I Can’t Remember$NC"
#   Ubuntu update commands
    echo -e "\n${B_BLACK}safe-upgrade is preferred$NC"
    echo -e "sudo aptitude safe-upgrade"

    echo -e "\n${B_BLACK}If safe-upgrade doesn’t work you can do it manually$NC"
    echo -e "sudo apt-get update"
    echo -e "sudo apt-get upgrade"

    echo -e "\n${B_BLACK}Sometimes a reboot is required after an update$NC"
    echo -e "sudo reboot"
    
    echo -e "\n${B_BLACK}scp a file e.g public key$NC"
    echo -e "scp ./.ssh/RSA_KEY.pub myloginid@198.199.102.113:/home/myloginid/.ssh/authorized_keys"
}

I’ve been helping set up some VPSs (Virtual Private Server) and at the moment they don’t have domain names assigned. I can use the history command to find the right IP address to ssh in but that can be cumbersome. So I decided to write an alias for each server. These lines are in my extended bash config file that is called when my .profile runs.

My login is the same on each server so I could hard code this, but I made it general so I can share it with other users. I don’t like to hard code things so I thought I’d let bash find my user id and then use it in the ssh command. My first thought was to just create an alias for my login and use it in an alias for each server. But I couldn’t get it to work. I tried using just the alias since examples of creating aliases seem to work this way with builtin commands. Then I thought that maybe I need to escape the alias so that bash knows that it should process it.

alias myID='who am i | cut -d\  -f 1'
alias myserver='ssh myID@192.245.184.221'      #Doesn’t work
alias myserver='ssh $(myID)@192.245.184.221'   #Works fine

It turns out that the second line works, but I did had a problem with aliases being set and it didn’t work for me. So I started playing around with things that did work and making small adjustments. FYI, One thing you need to do if you redefine aliases and functions is to make sure that you clear out the old ones. If you don’t then you may not be running the function you thought you were or you could be running an alias when you thought you were running a function.

You can use unset to remove them from memory. Use the -f flag for functions.

unset myID
unset -f dave

For myserver, I put the alias command in parens and prefixed it with a $. Bash executes this first and then uses it in the ssh command. If you don’t do this, bash thinks that the first argument for ssh is ‘who’ and fails. That worked, but I don’t like the idea of having duplicate code in login alias. So I thought I’d try a function. In the first function, I define a variable and then use it. The third function looks an awful lot like a failed alias in the example above, but since it is a function, it works. I think what is happening is that when bash sees $(myID) it thinks, I should process whatever is inside the parens. So it finds the alias and runs it.

#   Log in to other systems
alias myID='who am i | cut -d\  -f 1'

alias myserver='ssh $(who am i | cut -d\  -f 1)@192.245.184.221'
dave() { id=$(who am i | cut -d\  -f 1); ssh $id@192.233.221.11; }
dana()  { ssh $(myID)@192.149.142.163 ; }

alias bigserver='ssh "echo $(myID)"@192.255.174.220'

It finally occurred to me that the .profile script is a full blown shell script. So that means I can use variables in it. I define an id variable that is the result of the shell executing the commands that are in it. When the .profile is run, bash assigns id to my login. Then I can use it anywhere I want with $id. I don’t need to use ${id} since my login is one word and since the @ cannot be in a variable name, bash knows to stop processing when it hits the @. This is what I wanted to do with aliases. I thought about making it a local variable since I don’t need it to hang around outside of this script. But the alias isn’t resolved until it is used, so it needs to resolve the variable when it is called.

#   Log in to other systems
id=$(who am i | cut -d\  -f 1)
alias myserver='ssh $id@192.245.184.221'
alias dave='ssh $id@192.233.221.11'
alias dana='ssh $id@192.149.142.163'

I started playing around with aliases again and got strictly aliases to work as well.

#   Log in to other systems
alias myID='who am i | cut -d\  -f 1'
alias myserver='ssh $(myID)@192.245.184.221'
alias dave='ssh $(myID)@192.233.221.11'
alias dana='ssh $(myID)@192.149.142.163'

Update: I was playing with printenv and it turns out that one of the environment variables is LOGNAME. So you can replace the $id with $LOGNAME in the code above.