MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘basic programming’ Category

Run X11 Apps on Mac

without comments

It’s possible folks didn’t notice but Mac OS X no longer includes XQuartz by default from Maverick forward. You need to download XQuartz and install it. I’d recommend after you install Xcode.

Launch XQuartz and then either use the bash shell it opens or open a Terminal bash shell session. Inside the shell, you might start Secure Shell (ssh) like this:

Mac-Pro-3:~ michaelmclaughlin$ ssh student@192.168.2.170
student@192.168.2.170's password: 
Last login: Thu Jun  4 14:33:37 2015
[student@localhost ~]$ xclock &
[1] 10422
[student@localhost ~]$ Error: Can't open display:

Granted that’s a trivial error and running the xclock X11 applications isn’t crucial, an error that makes it more important is the following from Oracle’s old Designer/2000 application:

FRM-91111: Internal Error: window system startup failure.
FRM-10039: Unable to start up the Form Builder.

This is the desired behavior. Secure shell (ssh) can’t run it unless you make the connection with the -Y flag. You should use the following syntax:

Mac-Pro-3:~ michaelmclaughlin$ ssh -Y student@192.168.2.170
student@192.168.2.170's password: 
Last login: Tue Jun  9 14:56:55 2015 from 192.168.2.1
/usr/bin/xauth:  file /home/student/.Xauthority does not exist
[student@localhost ~]$ xclock &
[1] 10760

You can safely ignore the .Xauthority does not exist warning message because it’ll create a .Xauthority file and store the magic cookie after the warning message. You should see the xclock program running in the upper left hand corner of your console, like:

X11MacXclock

It’s terrific that you don’t get a font warning like you typically would using UTF-8 on Linux. Nice that the Mac OS fonts are so well done that there isn’t a raised exception.

Using xclock or xeyes isn’t very useful as a rule, but this method also lets you run any of the Linux GUI applications. For example, the following gedit command lets you run the gedit utility from a Mac OS console. If you’ve installed the gedit plug-ins, you also can use the Terminal console on the remote system.

X11GeditTerminal

The process sequence for the command-line is shown below:

1030     1  /usr/sbin/sshd -D     - The root process launches the ssh daemon
3145  1030  sshd: student [priv]  - The sshd launches a ssh session to manage a student ssh session
3152  3145  sshd: student@pts/1   - The ssh session launched to manage the ssh session
3166  3152  -bash                 - The bash shell launched by connecting through the ssh session
3240  3166  gedit                 - The gedit command issued inside a ssh session
3166  3240  gnome-pty-helper      - Launching the gedit session across X11 
3169  3240  /bin/bash             - Launching the Terminal session inside the gedit session across X11
3269  3884  ps -ef                - Command run inside the gedit Terminal session

Hope that helps those who want to use X11 applications on the Mac OS.

Written by maclochlainn

June 9th, 2015 at 4:51 pm

Bash Arrays & MySQL

with 2 comments

Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure ones.

LittleERDModel

It seems a quick refresher on how to use arrays in bash shell may be helpful. While it’s essential in a Linux environment, it’s seems not everyone masters the bash shell.

Especially, since I checked my Learning the Bash Shell (2nd Edition) and found a typo on how you handle arrays in the bash shell, and it’s a mistake that could hang newbies up (on page 161). Perhaps I should update my copy because I bought it in 1998. 😉 It was good then, and the new edition is probably better. The error is probably corrected in the current Learning the Bash Shell, but if not, the following examples show you how to use arrays in loops.

Naturally, these do presume some knowledge of working with bash shell, like the first line always is the same in any bash shell script. That you open an if-statement with an if and close it with a fi, and that you else-if is elif; and that a semicolon between a for-statement and the do statement is required when they’re on the same line because they’re two statements.

If you’re new to bash shell arrays, click on the link below to expand a brief tutorial. It takes you through three progressive examples of working with bash arrays.

Only one more trick needs to be qualified before our main MySQL examples. That trick is how you pass parameters to a bash shell script. For reference, this is the part that’s insecure because user command histories are available inside the Linux OS.

Here’s a hello_whom.sh script to demonstrates the concept of parameter passing:

1
2
3
4
5
6
7
8
9
10
#!/usr/bin/bash
 
# This says hello to the argument while managing no argument.
if [[ ${#} = 1 ]]; then
  echo 'The '${0}' program says: "Hello '${1}'!"'
elif [[ ${#} > 1 ]]; then
  echo 'The '${0}' program wants to know if you have more than one name?'
else
  echo 'The '${0}' program wants to know if you have a name?'
fi

If you need more on how parameters are passed and managed, you can check a prior blob post on Handling bash Parameters, or check the bash help pages. The following leverages bash arrays to run scripts and query the MySQL database from the command line.

You will need the three batch SQL files first, so here they are:

The following list_mysql.sh shell script expects to receive the username, password, database and fully qualified path in that specific order. The script names are entered manually because this should be a unit test script. Naturally, you can extend the script to manage those parameters but as mentioned I see this type of solution as a developer machine only script to simplify unit testing. Anything beyond that is risky!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#!/usr/bin/bash
 
# Assign user and password
username="${1}"
password="${2}"
database="${3}"
directory="${4}"
 
# List the parameter values passed.
echo "Username:  " ${username}
echo "Password:  " ${password}
echo "Database:  " ${database}
echo "Directory: " ${directory}
echo ""
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="actor.sql"
cmd[1]="film.sql"
cmd[2]="movie.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  mysql -s -u${username} -p${password} -D${database} < ${directory}/${i} > /dev/null 2>/dev/null
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p${password} -D${database} <<<'show tables' 2>/dev/null |
 
# Read through the piped result until it's empty but format the title.
while IFS='\n' read list; do
  if [[ ${list} = "Tables_in_sampledb" ]]; then
    echo $list
    echo "----------------------------------------"
  else
    echo $list
  fi
done
echo ""
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p${password} -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null |
 
# Read through the piped result until it's empty but format the title.
while IFS='\n' read actor_name; do
  if [[ ${actor_name} = "Actors in Films" ]]; then
    echo $actor_name
    echo "----------------------------------------"
  else
    echo $actor_name
  fi
done

The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 33 and 47 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.

You can run this script with the following input parameters from the local directory where you deploy it. The a parameters are: (1) username, (2) password, (3) database, and (4) a fully qualified path to the SQL setup files.

./list_mysql.sh student student sampledb "/home/student/Code/bash/mysql"

With valid input values, the list_mysql.sh bash script generates the following output, which confirms inputs and verifies actions taken by the scripts with queries:

Username:   student
Password:   student
Database:   sampledb
Directory:  /home/student/Code/bash/mysql
 
Tables_in_sampledb
----------------------------------------
actor
film
movie
 
Actors in Films
----------------------------------------
Chris Hemsworth in Thor
Chris Hemsworth in Thor: The Dark World
Chris Pine in Star Trek
Chris Pine in Star Trek into Darkness
Chris Pine in Guardians of the Galaxy

If you forgot to provide the required inputs to the list_mysql.sh bash script, it alternatively returns the following output:

Username:  
Password:  
Database:  
Directory: 
 
./list_mysql.sh: line 25: /actor.sql: No such file or directory
./list_mysql.sh: line 25: /film.sql: No such file or directory
./list_mysql.sh: line 25: /movie.sql: No such file or directory

The secure way removes the password at a minimum! The refactored program will require you to manually enter the password for all elements of the array (three in this sample), and twice for the two queries. Here’s the refactored code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#!/usr/bin/bash
 
# Assign user and password
username="${1}"
database="${2}"
directory="${3}"
 
# List the parameter values passed.
echo "Username:  " ${username}
echo "Database:  " ${database}
echo "Directory: " ${directory}
echo ""
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="actor.sql"
cmd[1]="film.sql"
cmd[2]="movie.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  mysql -s -u${username} -p -D${database} < ${directory}/${i} > /dev/null 2>/dev/null
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p -D${database} <<<'show tables' 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read list; do
  if [[ ${list} = "Tables_in_sampledb" ]]; then
    echo $list
    echo "----------------------------------------"
  else
    echo $list
  fi
done
echo ""
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read actor_name; do
  if [[ ${actor_name} = "Actors in Films" ]]; then
    echo $actor_name
    echo "----------------------------------------"
  else
    echo $actor_name
  fi
done

Please let me know if you think there should be any more scaffolding for newbies in this post. As always, I hope this helps those looking for this type of solution.

Written by maclochlainn

May 17th, 2015 at 12:01 pm

Find a string in files

with 2 comments

From time to time, folks ask questions about how to solve common problems in Linux or Unix. Today, the question is: “How do I find a list of files that contain a specific string?” There are two alternatives with the find command, and the following sample searches look for files that contain a sqlite3 string literal.

  • Search for only the file names:
find . -type f | xargs grep -li sqlite3

Or, the more verbose:

find . -type f -exec grep -li sqlite3 /dev/null {} +
  • Search for the file names and text line:
find . -type f | xargs grep -i sqlite3

Or, the more verbose:

find . -type f -exec grep -i sqlite3 /dev/null {} +

Don’t exclude the /dev/null from the verbose syntax or you’ll get the things you lack permissions to inspect or that raise other errors. I don’t post a lot of Linux or Unix tips and techniques, and you may find this site more useful to answer these types of questions:

Unix & Linux Stack Exchange web site

As always, I hope this helps those you land on the blog page.

Written by maclochlainn

April 18th, 2015 at 2:39 pm

Excel date conversion

with 24 comments

I put together a post on how to upload to MySQL from a CSV file with dates. It was more or less for my students but one of them was curious how the mega formula worked. As commented, the easier solution is to create a custom format. Oddly, Open Office does support the MySQL default format natively.

Excel doesn’t support the native MySQL date format as a default format mask, which is YYYY-MM-DD, or 2009-06-02 for June 2, 2009. That means you have to convert it from a scalar date to a string or create a custom format mask (see Dmitri’s comment below). If you just shook your head at the term scalar date, maybe a custom format mask is best. However, if you want a programming solution let me explain that Excel supports only three data types. They’re a string literal, a numeric literal, and a formula. Dates in Excel are merely formatted numbers. When the numbers are integers, the date is a date, but when the number has a fractional component, the date is really a timestamp.

Here’s a brief description of the process required to convert a date in Excel into a MySQL date format string literal in a CSV file. You need the following Excel functions:

Date Functions

  • The DAY(date) function returns a 1 or 2 digit numeric value for the day of the month, with ranges of 1 to 28, 1 to 29, 1 to 30, or 1 to 31 dependent on the month and year.
  • The MONTH(date) function returns a 1 or 2 digit numeric value for the month of the year.
  • The YEAR(date) function returns a 4 digit numeric value for the year.

Logical Functions

  • The IF(logical_expression,truth_action,false_action) function returns the truth action when the expression is true, and the false action when the expression isn’t true.

MySQL Server

  • CONCATENATE(string_1, string_2, ...) glues strings together.
  • LEN(numeric_value) function returns the length of a string or number.

MySQL requires that you return an eight character string of numbers. The first four numbers must be a valid year, the fifth and sixth numbers a valid month, and the seventh and eigth numbers a valid day in the context of the year and month provided. Unfortunately, the DAY() and MONTH() functions may return a 1 or 2 digit value. That can’t happen in the CSV file’s string for a date, so you use the IF() and LEN() functions to guarantee a 2 digit return value.

Here are the examples that guarantee 2 digit day and month values, assuming that the base date is in the A1 cell. The concatenation of a "0" (zero between two double quotes) or the "" (two double quotes or a string null) ensures the number data types become strings.

=IF(LEN(DAY(A1))=1,CONCATENATE("0",DAY(A1)),DAY(A1))
=IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1))

A zero is placed before the day or month when the logical condition is met, which means the day or month value is a single digit string. A null is place before the day or month when the logical condition isn’t met, which means the day or month value is a two digit string. There’s only one problem with these mega functions. They return a number.

The year calculation doesn’t require the explicit casting when you concatenate it with the other strings because it is implicitly cast as a string. However, it’s a better practice to include it for clarity (most folks don’t know about the implicit casting behaviors in Excel).

=CONCATENATE(YEAR(A1),"-",IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1)),"-",IF(LEN(DAY(A1)) = 1,CONCATENATE("0",DAY(A1)),DAY(A1)))

As Goodwin reported in a comment, there’s an easier way that I missed. You can simply use the TEXT function when the source column is a valid serialized date value.

=TEXT(A1,"YYYYMMDD")

You can see the full MySQL import from CSV in the previous post. Naturally, you may want to copy and paste special the value before creating the CSV file. Also, don’t forget to delete any unused columns to the right or rows beneath because if you don’t your file won’t map to your table definition.

Written by maclochlainn

June 16th, 2009 at 8:15 pm

Excel string parsing

with 14 comments

Parsing strings isn’t the easiest thing to do in Excel 2007 or Excel 2008 but it is an important thing to know how to do. You’ll learn how to parse a set names into first, middle and last names. You can find the data set for these examples at the bottom of the blog post.

Written by maclochlainn

February 27th, 2009 at 10:15 pm