Mac OS X Unleashed

By John Ray and William C. Ray

• Table of Contents
  • Copyright
  • About the Author
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: Introduction to Mac OS X
  • Chapter 1. Mac OS X Component Architecture
  • Chapter 2. Installing Mac OS X
  • Chapter 3. Mac OS X Basics
  • Chapter 4. The Finder: Working with Files and Applications
  • Chapter 5. Running Classic Mac OS Applications
  • Part II: Inside Mac OS X
  • Chapter 6. Native Utilities and Applications
  • Chapter 7. Internet Communications
  • Chapter 8. Installing Third-Party Applications
  • Part III: User-Level OS X Configuration
  • Chapter 9. Network Setup
  • Chapter 10. Printer and Font Management
  • Chapter 11. Additional System Components
  • Part IV: Introduction to BSD Applications
  • Chapter 12. Introducing the BSD Subsystem
  • Chapter 13. Common Unix Shell Commands: File Operations
  • Part V: Advanced Command-Line Concepts
  • Chapter 14. Advanced Shell Concepts and Commands
  • Chapter 15. Command-Line Applications and Application Suites
  • Chapter 16. Command-Line Software Installation
  • Chapter 17. Troubleshooting Software Installs, and Compiling and Debugging Manually
  • Chapter 18. Advanced Unix Shell Use: Configuration and Programming (Shell Scripting)
  • Part VI: Server/Network Administration
  • Chapter 19. X Window System Applications
  • Chapter 20. Command-Line Configuration and Administration
  • Chapter 21. AppleScript
  • Chapter 22. Perl Scripting and SQL Connectivity
    • Perl
    • MySQL
    • Creating a Database
    • Perl/MySQL Integration
    • Summary
  • Chapter 23. File and Resource Sharing with NetInfo
  • Chapter 24. User Management and Machine Clustering
  • Chapter 25. FTP Serving
  • Chapter 26. Remote Access and Administration
  • Chapter 27. Web Serving
  • Part VII: Server Health
  • Chapter 28. Web Programming
  • Chapter 29. Creating a Mail Server
  • Chapter 30. Accessing and Serving a Windows Network
  • Chapter 31. Server Security and Advanced Network Configuration
  • Chapter 32. System Maintenance
  • Appendix A. Command-Line Reference
  • Appendix B. Administration Reference

Creating a Database

The key to using MySQL is an understanding of the SQL syntax itself. If you've used Oracle or another SQL-based system, you'll be right at home interacting with MySQL. For beginners, this introduction should be enough to get started, but we recommend a more complete text such as Sams Teach Yourself MySQL in 21 Days (ISBN: 0672319144).

To start MySQL, invoke the client (mysql) using mysql -u <username> -p <password> . To start, there should only be the root account available. If you didn't set the password for root, there will be no password required.

The first step when working with MySQL is to create the database itself. If you've worked with FileMaker Pro or AppleWorks, this is a very different concept. In MySQL, a database is a container that holds a collection of tables. These tables, in turn, hold actual information. The FileMaker database model has a single table in a single database. To create relationships between different collections of data requires multiple databases. In MySQL, a single database can contain multiple tables each with unique data.

To create a database, make sure that you've started MySQL and are at a command prompt:

[primal:~] jray% mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 183 to server version: 3.23.27-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql>

Next, use create database <database name> to set up an empty database. This database will be completely empty, but not for long. After creating the database, type use <database name> to work with the new database.

For example, let's start with an employee database:

mysql> create database employee;
Query OK, 1 row affected (0.07 sec)

mysql> use employee;
Database changed
mysql>

If you want to delete the database that you've defined, you can use the drop command, just like create:

drop database <database name>

After a database has been created, you need to set up the internal tables that will actually hold the data you want to store.

Tables and Data Types

When making a table, use another create command to tell the system what type of data you want to store—if any.

create table <tablename> (<columns...>)

For example, let's create some tables for a fictitious employee database:

create table tblemployee (
    employeeID  int not null,
    firstname   varchar(50),
    lastname    varchar(50),
    titleID     int,
    salary      float,
    primary key (employeeID)
);
create table tbljobclassification (
    titleID     int not null,
    title       text,
    minsalary   float,
    maxsalary   float,
    primary key (titleID)
);

The first table, tblemployee, holds information about each person in the database, such as his name and salary. The second table, tbljobclassification, contains job classification data—a general position description, and the minimum and maximum salary ranges for that position.

When defining a database table, there are numerous data types used to build the collection of information that can be stored. Table 22.5 contains a description of the available data types. This is a summarized version of the documentation supplied at www.mysql.com/.

Table 22.5. Database Tables Are Built with MySQL Data Types

Fields are defined within a table creation statement by using the syntax <fieldname> <datatype> <options> . There are two common options that are employed to force certain conditions on a database:

• not null — Forces the field to contain a value. If a user attempts to insert data into the database and a not null field is left blank, an error will occur.
• auto_increment — When used with an integer field, the value for the field will be determined automatically by MySQL and be incremented with each subsequent record.

The final line of a table creation command should define a primary key (or keys) for the table primary key ( <fieldname 1,fieldname 2,...> ). Defining keys are a necessary part of creating a normalized database structure. For more information on normalization, see www.devshed.com/Server_Side/MySQL/Normal/. We highly recommend reading through this tutorial, at the very least, before designing large-scale database models.

To remove a table that has been defined, type drop table <table name> .

Inserting Data

There are two ways to insert data into a table; both use the insert command with this structure:

insert into <table name> [(<field1, field2,...>)] values
(<'value1', 'value2',...>)

The difference between the methods comes from the optional field listing. If you want to insert into only a few fields of a table, and want to manually specify the order, you would include the field names, like this example using the tblemployee table created earlier:

insert into tblemployee (lastname,firstname,employeeID)
       values ('Ray','John','1');

In this example, only the lastname, firstname, and employeeID fields are given in the record, and they don't occur in the same order they were defined in the original table.

The second way you can use insert is to provide all the field values at once, in the table definition order. This method doesn't require the field names to be listed:

insert into tblemployee values ('1','John','Ray','1','35000.00');

It is important to note that you must obey the not null clause for a table definition at all times. In these examples, we had to include a value for the employeeID field; otherwise, the insert would have caused an error message to be generated.

To demonstrate the rest of the MySQL syntax, you'll need some data to work with. Go ahead and insert some information into the tables:

insert into tbljobclassification values ('1','Programmer/Analyst','20000','80000');
insert into tbljobclassification values ('2','Web Developer','20000','50000');
insert into tbljobclassification values ('3','CEO/President','40000','5000000000');

insert into tblemployee values ('1','John','Ray','1','25300.65');
insert into tblemployee values ('2','Will','Ray','1','32100.25');
insert into tblemployee values ('3','Joan','Ray','1','55300.75');
insert into tblemployee values ('4','Robyn','Ness','2','35000.20');
insert into tblemployee values ('5','Anne','Groves','2','35000.65');
insert into tblemployee values ('6','Julie','Vujevich','2','30300.01');
insert into tblemployee values ('7','Jack','Derifaj','1','12000.00');
insert into tblemployee values ('8','Russ','Schelby','1','24372.12');
insert into tblemployee values ('9','Bill','Gates','3','50000.01');
insert into tblemployee values ('10','Steve','Jobs','3','380000000.00');

These statements add three different job classifications (Programmer/Analyst, Web Developer, and CEO/President) to the system, as well as ten employees that fall under these classifications.

After your database has been populated, you can update or delete individual records using the commands update, delete, and replace into.

Modifying Data

Obviously, data in a database must be able to change; otherwise, it would only be useful for a short period of time or very limited applications.

Update

To change existing data, use the update command:

update <table name> SET <field name 1>=<expression 1>, <field name 2>=<expression 2>

      
   ,<field name n>=<expression n> [WHERE <search expression>]

To use update, you must supply a table name, as well as the names of the fields that need to be updated, and the new values that they should take on. This leaves one important part of the equation missing: the search expression. Without telling update which fields to modify, it will modify all the tables. For example, issuing the command

update tblemployee set salary='3000';

will modify every listed employee so that their salary field contains '3000'. If this is the desired action, great! If not, you're likely to be smacking your forehead when you discover what you've done.

To be a bit more selective about the update, you must define the WHERE search expression. This will select only the records that you want to update. For example, assume that we'd like to set the salary for employeeID 1 to equal 30000.99. The update statement would look like this:

update tblemployee set salary='30000.99' where employeeID='1';

This update statement will search the database for a field where employeeID is equal to 1, and then update the value in that record's salary field.

In addition to =, there are a number of common ways to select a record based on comparing a field to a value; that is, you can select records by creating an expression that evaluates to true or false. Table 22.6 shows some of the most common expression operators and syntax.

Table 22.6. Some of the Common Expression Operators and Syntax

These basic expressions can be combined to form more complex searches:

• NOT <expression> — Evaluates to true if the expression evaluates to false.
• <expression> OR <expression> — Evaluates to true if either of the expressions is true.
• <expression> AND <expression> — Evaluates to true if both of the expressions are true.
• ( <expression> ) — Use parentheses to combine expressions to force an order of evaluation.

Check the MySQL documentation for further information on available mathematical expressions, string comparisons, and other operators that can be used in expression syntax.

Delete

To delete data from a MySQL system, you use a command similar to update, but without supplying new field values:

delete from <table name> [WHERE <search expression>]

As with the update command, you can leave out the WHERE portion of the statement entirely. Unfortunately, the result would be the elimination of all data from the named table. Again, if this is your intention, by all means, use it! For example, to delete employees who make more than $50,000 from the database, you would enter

delete from tblemployee where salary>'50000';

Replace

There is one final way to conveniently replace existing records with new data. Using the INSERT command to try to save a record more than one that already exists will result in an error. This happens because only one record with a given primary key can exist at a time. For example, assuming that we've filled the database with the following employee record:

insert into tblemployee values ('1','John','Ray','1','25300.65');

Attempting to insert another record using the same employee ID (1) will cause an error:

mysql> insert into tblemployee values ('1','Maddy','Green','1','41000.00');
ERROR 1062: Duplicate entry '1' for key 1

To circumvent this, you could update the existing record; or delete the record and then re-run the insert; or use the replace into command.

replace replaces an existing record with new data or, if no record exists, simply inserts a record. Think of replace as a more powerful version of the basic i n sert command. It can be used to add new records to a table, or replace existing records with new data. The syntax is identical to insert. For example, let's retry the insert into the tblemployee table—this time using replace:

mysql> replace into tblemployee values ('1','Maddy','Green','1','41000.00');
Query OK, 2 rows affected (0.00 sec)

Success!

Querying MySQL

After you add data to the tables in a database, you would obviously want to display it. Querying a MySQL database is performed with the select statement. The power of relational databases comes from the capability to relate data in one table to that of another, and select can do just that:

select <field name1>,<field name2>,... from <table name 1>,<table name 2>,... [where

      
    <search expression>] [ORDER BY <expression> ASC|DESC]

If this isn't confusing for you, fantastic. If you're like the rest of us, however, some explanation is necessary.

The simplest query that select can perform is to pull all the data out of a single table (select * from <table name> ). For example:

mysql> select * from tbljobclassification;
+---------+--------------------+-----------+-----------+
| titleID | title              | minsalary | maxsalary |
+---------+--------------------+-----------+-----------+
|       1 | Programmer/Analyst |     20000 |     80000 |
|       2 | Web Developer      |     20000 |     50000 |
|       3 | CEO/President      |     40000 |     5e+09 |
+---------+--------------------+-----------+-----------+
3 rows in set (0.00 sec)

Ordering Information

To sort the information based on one of the fields, use order by with an expression (often one or more comma separated field names), and asc for ascending order or desc for descending order:

mysql> select * from tbljobclassification order by maxsalary desc;
+---------+--------------------+-----------+-----------+
| titleID | title              | minsalary | maxsalary |
+---------+--------------------+-----------+-----------+
|       3 | CEO/President      |     40000 |     5e+09 |
|       1 | Programmer/Analyst |     20000 |     80000 |
|       2 | Web Developer      |     20000 |     50000 |
+---------+--------------------+-----------+-----------+

In this example, the tbljobclassification table is displayed and the records are sorted by the maximum salary in descending order (most to least). Obviously, this is great for getting data out of a single table and manipulating its order, but it still doesn't draw on the relational power of MySQL.

Joining Tables

To fully exploit MySQL's capabilities, relationships must be created and used. A relationship links two or more tables based on a common attribute. For example, the tblemployee and tbljobclassification tables share a titleID field. Each employee record has a titleID field that can be used to relate to the tbljobclassification table. The process of relating tables together is called a join.

To see a join in action, let's take a look at how you would display a list of each employee's name, along with his or her job title. The select statement looks like this:

select firstname,lastname,title from tblemployee,tbljobclassification WHERE tblemployee

      
   .titleID=tbljobclassification.titleID;

Translating this query into English is simple: Select the firstname, lastname, and title fields (select firstname,lastname,title) from the tblemployee and tbljobclassification database tables (from tble m ployee,tbljobclassification). Relate the two tables by matching the titleID field in tblemployee to the titleID field in tbljobclassification (WHERE tblemployee.titleID=tbljobclassification.titleID).

The result is a neat display of the employees and their corresponding job titles:

mysql> select firstname,lastname,title from tblemployee,tbljobclassification WHERE

      
    tblemployee.titleID=tbljobclassification.titleID;
+-----------+----------+--------------------+
| firstname | lastname | title              |
+-----------+----------+--------------------+
| Maddy     | Green    | Programmer/Analyst |
| Will      | Ray      | Programmer/Analyst |
| Joan      | Ray      | Programmer/Analyst |
| Jack      | Derifaj  | Programmer/Analyst |
| Russ      | Schelby  | Programmer/Analyst |
| Robyn     | Ness     | Web Developer      |
| Anne      | Groves   | Web Developer      |
| Julie     | Vujevich | Web Developer      |
| Bill      | Gates    | CEO/President      |
| Steve     | Jobs     | CEO/President      |
+-----------+----------+--------------------+
10 rows in set (0.03 sec)

A select statement can be combined with the WHERE search expressions that you've already seen in this chapter. For example, the last query can be modified to show only the employees who are making more than $50,000:

select firstname,lastname,title,salary from tblemployee,tbljobclassification WHERE

      
    tblemployee.titleID=tbljobclassification.titleID AND tblemployee.salary>'50000';

For example:

mysql> select firstname,lastname,title,salary from tblemployee,tbljobclassification WHERE

      
    tblemployee.titleID=tbljobclassification.titleID AND tblemployee.salary>'50000';
+-----------+----------+--------------------+---------+
| firstname | lastname | title              | salary  |
+-----------+----------+--------------------+---------+
| Joan      | Ray      | Programmer/Analyst | 55300.8 |
| Bill      | Gates    | CEO/President      |   50000 |
| Steve     | Jobs     | CEO/President      | 3.8e+08 |
+-----------+----------+--------------------+---------+
3 rows in set (0.00 sec)

Of course, expressions can be combined with other expressions to create truly complex queries.

Calculations

Using built-in MySQL functions, you can create virtual fields that contain data that is calculated as the query is performed. The syntax for an inline calculation is

   <expression> as '<variable name>'

For example, the expression required to calculate the percentage of the maximum salary that each person makes could be represented by

tblemployee.salary/tbljobclassification.maxsalary*100 as 'percent'

Adding this code into a query of all the employees names and salaries results in

mysql> select firstname,lastname,salary,tblemployee. salary/tbljobclassification

      
   .maxsalary*100 as 'percent' from tblemployee,tbljobclassification where tblemployee

      
   .titleID=tbljobclassification.titleID;
+-----------+----------+---------+-------------------+
| firstname | lastname | salary  | percent           |
+-----------+----------+---------+-------------------+
| Maddy     | Green    |   41000 |             51.25 |
| Will      | Ray      | 32100.2 |        40.1253125 |
| Joan      | Ray      | 55300.8 |        69.1259375 |
| Jack      | Derifaj  |   12000 |                15 |
| Russ      | Schelby  | 24372.1 |   30.465148925781 |
| Robyn     | Ness     | 35000.2 |     70.0003984375 |
| Anne      | Groves   | 35000.6 |      70.001296875 |
| Julie     | Vujevich |   30300 |    60.60001953125 |
| Bill      | Gates    |   50000 | 0.001000000234375 |
| Steve     | Jobs     | 3.8e+08 |               7.6 |
+-----------+----------+---------+-------------------+
10 rows in set (0.01 sec)

Suddenly, the database has provided information that didn't even exist previously! Using these methods, you can use the MySQL database engine to perform much of the mathematical work of database applications, leaving the logic to other programming languages.

Summarization and Grouping

Summarizing data is another very useful part of any query. Using the summarization functions, you can easily find totals for numeric columns, or count the number of records of a particular type. Here are a few summarization functions that can be used in a query:

• max() — The maximum of a given field. Used to match the highest value. For example, if you use max on the salary field of the employee table, it should return the highest salary in the group.
• min() — The minimum of a given field. Has the exact opposite of the max function.
• sum() — The sum of the values in a given field. For example, to find the total amount paid in salaries.
• count() — Provides a count of the number of occurrences of a given field.

For example, you could find the minimum salary of all the employees by typing

mysql> select min(salary) from tblemployee;
+-------------+
| min(salary) |
+-------------+
|       12000 |
+-------------+
1 row in set (0.01 sec)

Or a count of the occurrences of the titleID field:

mysql> select count(titleID) from tblemployee;
+----------------+
| count(titleID) |
+----------------+
|             10 |
+----------------+
1 row in set (0.00 sec)

This second example obviously isn't very useful—all it did was return the number of times the titleID field was used—that is, ten times, once in each record. Displaying the count of each of the types of titleIDs would make more sense. This can be accomplished with one last construct—the group by clause.

group by organizes the data based on a field name, and then makes it available to the summarization function. For example, the previous query could be modified like this:

select titleID,count(titleID) from tblemployee group by (titleID);

Instead of simply counting the field occurrences and reporting a result, the query groups the r e cords by the titleID field, and then counts the occurrences within each group. The output looks like this:

mysql> select titleID,count(titleID) from tblemployee group by (titleID);
+---------+----------------+
| titleID | count(titleID) |
+---------+----------------+
|       1 |              5 |
|       2 |              3 |
|       3 |              2 |
+---------+----------------+
3 rows in set (0.00 sec)

As with all queries, this could be turned into a join to provide information from more than one table. To show the actual job titles rather than just ID numbers, you could modify the query like this:

mysql> select title,count(tblemployee.titleID) from tblemployee,tbljobclassification where

      
    tblemployee.titleID=tbljobclassification.titleID group by (tblemployee.titleID);
+--------------------+----------------------------+
| title              | count(tblemployee.titleID) |
+--------------------+----------------------------+
| Programmer/Analyst |                          5 |
| Web Developer      |                          3 |
| CEO/President      |                          2 |
+--------------------+----------------------------+

This output should be a bit more presentable. Note that in the modified query, the extended name (table name and field name) was used to refer to the titleID field. Failure to do this would result in an ambiguity error.

Helpers and Alternatives

There are several helper applications that are available for graphically controlling MySQL and its databases. Personally, I prefer the command-line interface, but your mileage may vary. In addition, Mac OS X boasts several industrial-strength commercial SQL database solutions. If you're interested in pointing and clicking, take a look these products:

• EstellaSQL— Provides a Cocoa interface to MySQL databases, as well as a Service menu item to perform searches from within any other Cocoa application. http://homepage.mac.com/mxcantor/filesharing.html.
• SQL4x Manager— Manage MySQL users and databases from this Cocoa application. This is, by far, the most elegant front end available for MySQL on any platform. www.macosguru.de/download.html.
• FrontBase— A Mac OS X–native SQL database system that features a fully graphical administration and RealBASIC integration. www.frontbase.com/cgi-bin/WebObjects/FrontBase.
• OpenBase— Another commercial SQL system for Mac OS X. OpenBase features GUI tools for designing database schema, as well as application development using either RealBASIC or RADStudio. www.frontbase.com/cgi-bin/WebObjects/FrontBase.

Share This