Community Project: Saberizing a Mac

by Sky Kalkman on Jun 2, 2009 9:00 PM EDT

The idea for this project came from a FanPost requesting some help getting databases and other saber-geeky tools up and running on a Mac. While we're at it, let's add the qualification "for free".

I'm no database guru, but I do own a Mac and consider myself smarter than a seventh grader, so I'll volunteer to act as captain of this adventure, and anyone else who's a Mac user and looking to learn how to use databases is free to come along for the ride. I suspect our cycle of learning will look something like:

Someone asks a question.
A bunch of people go looking for answers.
They report back.
We find something that works.
We all implement it.
We celebrate with age-appropriate beverages.
We aim higher and loop back to #1.

My only official role will be to decide on the next question, and wait to announce it until we give everyone a chance to catch up. Other than that, it's the blind leading the blind.

I will "feature" this article, meaning you can find a link to it in the Featured Stories section of the right sidebar even after it gets bumped from the front page.

First question: How do I get all the software pieces in place to work with SQL-based databases? Colin's writeup at Statistically Speaking is a great start, although I remember striking out on a free, quality graphical interface on the Mac. Ready, go!

Tweet Comment 43 comments | 2 recs |

Do you like this story?

Comments

Not an answer to this question

But thought I’d throw it out there for when it’s useful.

Has anyone tried Baseball on a Stick on a Mac?

by Dan Turkenkopf on Jun 2, 2009 9:10 PM EDT reply actions

xampp

http://www.apachefriends.org/en/xampp-macosx.html

PHPMyAdmin + ANSI-standard SQL will get you any girl at a bar.

by mrjavascript on Jun 2, 2009 9:43 PM EDT reply actions

MAMP

An alternative to XAMPP is MAMP. Personally, I haven’t used XAMPP on a Mac (have used it on Windows years ago), but I find that MAMP is as simple as dragging the application in the Applications folder and opening the controller application.

It uses MySQL and SQLlite. I’m not too sure how data is stored in this community, but these should be adequate. Postgres can also be installed (a Google search should turn up some helpful results).

How are calculations preformed? Is there any code that needs to be written? I’d be interested in doing so.

by Ryan Aghdam on Jun 2, 2009 10:31 PM EDT reply actions

Doesn't MAMP come on a Mac already?

Something like that does.

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 8:49 AM EDT up reply actions

The building blocks do, but MAMP's easier.

You can fuss with installing the latest PHP to the built-in Apache server that starts up when you enable web sharing, and installing your own copy of MySQL.

But it’s really more trouble than it’s worth when MAMP’s a pre-built, pre-configured package of the latest MySQL, PHP, and Apache you can just drop in your Applications folder and start up whenever you need it and quit whenever you don’t.

by taber on Jun 3, 2009 8:58 AM EDT up reply actions

Ok, does MAMP come with a GUI for MySQL?

Or do we need to add something like Sequel Pro, NaviCat MySQL Lite, or MySQL GUI Tools, all mentioned below?

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 9:20 AM EDT up reply actions

MAMP doesn't come with a GUI

MAMP doesn’t include a GUI for MySQL management, but any of those graphical frontends will hook up to a MAMP-based MySQL server without a fuss.

Both will have to be running at the same time — the GUI depends on the server to work.

by taber on Jun 3, 2009 10:00 AM EDT up reply actions

phpMyAdmin comes with MAMP, but some prefer a GUI client to a web application.

by Ryan Aghdam on Jun 3, 2009 10:57 AM EDT up reply actions

Navicat

If you prefer a OSX GUI to use there’s a free version of Navicat call Navicat Lite available for non-commercial use.

http://www.navicat.com/download/download.html

by BluJay on Jun 3, 2009 1:39 PM EDT up reply actions

Yes, there will be code that needs to be written. Thanks for volunteering ; )

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 9:19 AM EDT up reply actions

From kindred in the other thread:

i give you some open-source-hippie-shit. But seriously this “R” thing looks decent…just not sure if i have the computing chops for it.

Decrease runs scored?
Maybe.

Decrease winning? Never seen that proven.
-SFTU

by hazel on Jun 3, 2009 1:12 AM EDT reply actions

GNU R is a programming language all its own.

I use it from time to time to do stuff that Excel can’t handle, although I find myself gravitating more and more towards gretl, which is less powerful but also much easier to use for someone like me who isn’t a real computer programmer.

I don’t think either really solve the issues at stake here – whenever I use either of them I feed them data that comes out of an SQL database. The problem being looked at here is really that stage of things – MySQL should run fine on a Mac, and any MySQL code that I (or others!) publish should run regardless of the underlying OS.

Has anyone tried Navicat MySQL Lite? I’ve used the Windows version and didn’t like it as much as SQLyog, but I found it plenty servicable, and there’s a Mac version.

I has a blog. | Ask a sabermagician!

by cwyers on Jun 3, 2009 3:14 AM EDT up reply actions

I've also had fairly good luck with Oracle's SQLDeveloper

And it’s a Java application, so it should run on any platform (I’ve liked it better on Windows than on Linux). They have a Mac installer here.

I also use the MySQL GUI Tools which come in a Mac version as well.

by Dan Turkenkopf on Jun 3, 2009 7:36 AM EDT up reply actions

Interesting, sounds like some good places to start.

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 8:50 AM EDT up reply actions

Oh and I am a computer programmer and I have trouble with R

The documentation is shaky at best and it assumes you know a lot about statistics and matrix math to really do anything interesting.

Baseball Hacks has a good intro to some plotting/binning in R, but I’ve found the advanced stuff quite difficult.

by Dan Turkenkopf on Jun 3, 2009 8:06 AM EDT up reply actions

Sequel Pro's a fairly new MySQL GUI for 10.5

This frontend for MySQL (which works perfectly, of course, with a MAMP installation, which is what I use for my local MySQL server — works beautifully) used to be called CocoaMySQL, but it’s been updated and it’s called Sequel Pro now. it’s all Cocoa, fast, and pretty effective, though it’s still not as comprehensive as my favorite Windows open source solution, HeidiSQL.

by taber on Jun 3, 2009 8:22 AM EDT up reply actions

When I was going through Colin's tutorials last summer or whenever, I remember reading that CocoaSQL was the way to go, but it had stopped being developed.

So nice to see an new version of it.

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 8:51 AM EDT up reply actions

Yeah, I want to get to R eventually, too.

Let’s get the database software ready, and a database or two imported…

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 8:48 AM EDT up reply actions

Also, I'm thinking once we answer the current question, I'll write up a summary of what's worked for most people as a future resource.

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 9:21 AM EDT reply actions

Not Mac-specific

but phpMyAdmin is web-based, will run on anything, and does just about everything I need.

by richardhkirkando on Jun 3, 2009 9:39 AM EDT reply actions

MAMP and phpMyAdmin set up really easy

but how do you get and manipulate Retrosheet files with it? Is it possible?

by RFK on Jun 3, 2009 1:48 PM EDT up reply actions

Good question.

I know there’s a program that runs on PCs which takes in retrosheet files and spits out something more user-friendly. Hopefully someone more knowledgeable will chime in here.

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 2:26 PM EDT up reply actions

Short answer:

DOSBox.

I has a blog. | Ask a sabermagician!

by cwyers on Jun 3, 2009 2:46 PM EDT up reply actions

Longer answer:

If you follow my Retrosheet tutorial:

http://statspeak.net/2009/03/creating-a-retrosheet-database-part-i.html

You should be able to use DOSBox to run both Chadwick and the batch files I wrote to parse the Retrosheet files into CSV files. The SQL loaders I wrote should then (with a slight modification) load the CSV files. I don’t have a Mac where I can test this on, but it seems straightforward to me – you just have to pay attention to the directory structure you’re using when you mount the files on the virtual drive.

I has a blog. | Ask a sabermagician!

by cwyers on Jun 3, 2009 4:52 PM EDT up reply actions

Uh, sweet.

After “we” get databases software up and running, I think the Databank database will be goal one (which shouldn’t be hard given your tutorial) and then maybe we’ll hit retrosheet. There are size issues to deal with there, right?

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 4:55 PM EDT up reply actions

Ooh ooh

can we get Pitchf/x and Gameday too?

St. Louis Cardinals... defying win expectancy since 2008

by vivaelpujols on Jun 3, 2009 6:40 PM EDT up reply actions

I'm hoping.

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 7:05 PM EDT up reply actions

Shortest answer

Entire Retrosheet in single .sql dump

Pitch F/X is at the same site is .sql format

Link

by Jeff Zimmerman on Jun 3, 2009 5:56 PM EDT up reply actions

What about that "Baseball on a stick" thing?

St. Louis Cardinals... defying win expectancy since 2008

by vivaelpujols on Jun 3, 2009 6:53 PM EDT up reply actions

It SHOULD work.

It just should take a little additional work to get it working.

I has a blog. | Ask a sabermagician!

by cwyers on Jun 3, 2009 9:13 PM EDT up reply actions

wow

I was just going to take look at the Retrosheet db… 4.59 gigs. That’s a lot of baseball.

Marlins Stadium: When It's Raining, The Roof Will Happen!

by dan 2.0 on Jun 3, 2009 9:34 PM EDT up reply actions

I Can Host a Database

I pay for a web host that gives me database hosting where we could host a database for all to view (and possibly edit). Any interest in using it?

by Ryan Aghdam on Jun 3, 2009 10:59 AM EDT reply actions

How would that work?

Would we all create our own queries and build them off of others’ queries? We’d have to prevent people from removing others’ queries or deleting information, right?

What if we made that databases publicly available, shared how they were set up, helped people set up their own local database, then shared, via the public sample database, all the code snippets (and queries and whatever other technical things I’m unaware of) that would do cool things people would want to do on their own computer?

Ryan, mind sending me an email? This is definitely something worth looking into.

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 11:52 AM EDT up reply actions

This is great, Sky

I’m a new Mac owner and a complete database virgin, and coupling this post with Colin’s first from Statistically Speaking, I’ve been able to start working with the Baseball Databank in fairly short order. I used MAMP and Sequel Pro; I tried Navicat but liked Sequel Pro better.

I’ll be following this closely. I’m eager to learn.

"I didn't realize his velocity was that high," said Macha, noting that radar readings aren't flashed during exhibition games.

by battlekow on Jun 3, 2009 3:24 PM EDT reply actions

Cool, there's already a lot of information here.

I’m going to go try MAMP and SequelPro and see how I do…

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 4:36 PM EDT up reply actions

jumping on board

I had tried this in 07 after reading Baseball Hacks, but the lack of a quality (free) GUI then left me floundering with Terminal commands.

Decided to follow battlekow’s lead and shoot for Databank using MAMP/Sequel Pro. The only issue I ran into was on running Sequel Pro, the initial connection would fail due to some socket, uh, issue. But a quick search brought up this Sequel Pro kinda-sorta FAQ, where the solution was found. Namely:

In the connection setup dialogue, tell Sequel Pro to connect to the Socket “/Applications/MAMP/tmp/mysql/mysql.sock” instead and everything works just fine.

Did that, connected fine. Followed the gist of the StatSpeak post and was running simple queries in short order.

Marlins Stadium: When It's Raining, The Roof Will Happen!

by dan 2.0 on Jun 3, 2009 6:01 PM EDT reply actions

Yeppers!

I downloaded MAMP and downloaded Sequel Pro. Dragged them both into Applications. Opened them both. On the web page MAMP automatically opened up, there were login details (user, password, port, host) including the /Applications/MAMP/tmp/mysql/mysql.sock socket link. I typed those all into the Sequel Pro login screen and it connected. Next step, a database, but I’m going to wait just a bit to move on to give others a chance to try this themselves and ask questions.

Anyone know of any limitations of Sequel Pro? What are the chances we regret using this down the road. It sure does look nice and keeps the spirit of doing things on a Mac alive, at least visually…

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 6:08 PM EDT up reply actions

my initial observation is

that Sequel Pro does work fine IF you know SQL syntax/language/commands/whatever (unless I’m missing something).

On the other hand, phpMyAdmin has a Query tab that helps you build the proper command. Of course, you need to learn how to use THAT.

by RFK on Jun 3, 2009 7:27 PM EDT up reply actions

Is it like Access' tool? That's pretty handy.

I could see us getting into some SQL challenges and sharing results for useful queries that work.

Beyond the Boxscore // Calling BJ Upton lazy is lazy.

by Sky Kalkman on Jun 3, 2009 11:18 PM EDT up reply actions

I've never used Access

but I searched around and some site said the phpMyAdmin Query tab was a "MSAccess-like query editor (Query-By-Example) which allows users to compose an SQL query using several GUI elements", so, yeah, probably.

(Sorry for having to double-post this comment.)

by RFK on Jun 4, 2009 1:30 AM EDT up reply actions