Using MySQL on a Mac

OS X 10.4 ships with a relational database that I think has many of the pluses that Access has, and not some of the minuses of MySQL.

SQLite is a public domain (doesn't get more open source than that) relational database engine.

<http://www.sqlite.org/>

It has 2 big advantages over other implementations: a) No server process. You don't need a process running in the background to access the database. b) Entire database is in one file. Need to pass the database to a client or back it up? Everything is in one file.

The downsides are: a) no GUI administration tools. b) no GUI builder. You can't build forms like you can in Access. It would be nice if Apple integrated some of this into XCode and let you build an applescript form for working with the database.

Sqlite is also available from the Fink project for pre-10.4. <http://pdb.finkproject.org/pdb/package.php/sqlite3>

Kevin

On 03/10/2006, at 16:54, kevinv wrote:

> OS X 10.4 ships with a relational database that I think has many of
> the pluses that Access has, and not some of the minuses of MySQL.
>
> SQLite is a public domain (doesn't get more open source than that)
> relational database engine.
>
> <http://www.sqlite.org/>
>
> It has 2 big advantages over other implementations: a) No server
> process. You don't need a process running in the background to
> access the database. b) Entire database is in one file. Need to
> pass the database to a client or back it up? Everything is in one
> file.
>
> The downsides are: a) no GUI administration tools. b) no GUI
> builder. You can't build forms like you can in Access. It would be
> nice if Apple integrated some of this into XCode and let you build
> an applescript form for working with the database.

For the GUI administration use 'SQLite Database Browser' (http://
sqlitebrowser.sourceforge.net), and you can use XCode to create the
db in graphical form, also using drag&drop from the model to
Interface Builder create the 'forms' to enter/modify data without any
coding (you need to code if want more options/windows/etc...), Apple
have a tutorial called "NSPersistentDocument Core Data Tutorial" who
explain that, I have also a tutorial called 'DVD's Movies' in my web
site (http://homepage.mac.com/javier_diaz_r/eng/resources.html).

At 14:54 -0700 3/10/06, André van Toly wrote:
>I found it rather hard to believe that there was no mention of
>YourSQL <http://yoursql.ludit.it> in your article, the most

and also that there was no mention of Mark
Liyanage's site: http://www.entropy.ch/, which has
to be one of the premier sources on MySQL (and
other useful things) on OS X. He and that site
have saved my bacon often.

--
~~~~~~~~~~~~~~~~~~~~~~~~~
Dan Hinckley, Gingins, Switzerland

Two questions:

Is MySQL, with one or more of the add-ons mentioned here or by Jonathan Sousa, suitable for a user with several years of relational database experience (mostly in FileMaker) but no real grasp of Unix or desire to study same?

If so, would MySQL (+ whatever) be suitable for replacing a fairly elaborate FileMaker configuration which bogs down to a ridiculous degree with a really large dataset (>500 MB), apparently caused by failure to access available RAM and relying on an internal VM system?

TIA for all comments & suggestions!

Pierce Butler, Gainesville, FL

Good morning,

On 6/10/06 at 11:51 AM -0700, pbutler <pbutlerigc.org> wrote:

>Is MySQL, with one or more of the add-ons mentioned here or by Jonathan
>Sousa, suitable for a user with several years of relational database
>experience (mostly in FileMaker) but no real grasp of Unix or desire to
>study same?

Yes, if you are willing to learn some SQL. I would suggest
learning SQL if you are serious (or even curious) about managing
relational databases. Learning SQL doesn't really having
anything to do with learning unix. There are plenty of GUI SQL
clients (as listed in the article).

>If so, would MySQL (+ whatever) be suitable for replacing a fairly
>elaborate FileMaker configuration which bogs down to a ridiculous
>degree with a really large dataset (>500 MB), apparently caused by
>failure to access available RAM and relying on an internal VM system?

A key concept to understand is that FileMaker is both the
database engine and the frontend interface to the database,
while mySQL is just the database engine. You will still need to
use something else to interface to the database. The most common
interface is probably web apps using something like perl or php.
But there are other options closer to the FileMaker way of
working such as Servoy.

Both FileMaker and mySQL (with appropriate frontend) have their
place. For some companies, having both a FileMaker and mySQL
database makes sense. Eg. use a FileMaker solution in the office
and mySQL solution for their website. <plug>And keep the two
databases in synch using something like fmSQL Synch.</plug>

<http://www.garrison.com.au/products/fmsql_synch.html>


Charlie

--
    Charlie Garrison <garrisonzeta.org.au>
    PO Box 141, Windsor, NSW 2756, Australia

At 2006-10-06 11:51 -0700 pbutler wrote:

>Is MySQL, with one or more of the add-ons mentioned here or by
>Jonathan Sousa, suitable for a user with several years of relational
>database experience (mostly in FileMaker) but no real grasp of Unix
>or desire to study same?

Pierce - I'm a long-time FileMaker user (since before it was Pro, and
before Claris bought it). I've had to learn a bit about MySQL (and
PHP) in order to implement an online subscription payment system for
a club. I spent a while messing about with the MySQL tutorial, but
once I got hold of phpMyAdmin I got on much faster. The nice thing
about it is that I have the same interface to the development version
on my own Mac as to the online version on a *nix box.

>If so, would MySQL (+ whatever) be suitable for replacing a fairly
>elaborate FileMaker configuration which bogs down to a ridiculous
>degree with a really large dataset (>500 MB), apparently caused by
>failure to access available RAM and relying on an internal VM system?

I must admit to no experience of FMP with that size of dataset, but
the received wisdom about MySQL is that it scales to pretty much
whatever you can throw at it. Moving your data should be reasonably
straightforward if a bit time-consuming. MySQL accepts text imports -
phpMyAdmin has buttons to do it easily. The complexity of your
present system may lead to difficulties in translating concepts from
FMP to MySQL.

I've found that the following books have been useful:

Mastering MySQL 4 by Ian Gilfillan, Sybex
(got it cheap, remaindered)

PHP, MySQL and Apache by Julie C Meloni, Sams
(includes install disc, but not all needed for Mac)
This one is really good for getting started as it's got a
step-by-step cookbook section at the beginning.

By the way, have you asked any of the various FileMaker lists about
the poor performance of your FMP setup? I've always found them very
supportive, and someone there may be able to help you get more out of
your present arrangement at less effort than learning a whole new
language.

Hope this helps.

regards

Rowland
--
| Wilma & Rowland Carson http://home.clara.net/rowil/
| <rowilclara.net> ... that's Rowland with a 'w' ...

On 7/10/2006 7:51 AM, "pbutler" <pbutlerigc.org> spake thus:

> Is MySQL, with one or more of the add-ons mentioned here or by Jonathan Sousa,
> suitable for a user with several years of relational database experience
> (mostly in FileMaker) but no real grasp of Unix or desire to study same?

As long as you can find a decent front end to it, yes. If you're familiar
with Filemaker, you you should grok MySQL no problem. They're both pretty
similar in their degree un-relational-ness :)

> If so, would MySQL (+ whatever) be suitable for replacing a fairly elaborate
> FileMaker configuration which bogs down to a ridiculous degree with a really
> large dataset (>500 MB), apparently caused by failure to access available RAM
> and relying on an internal VM system?

Should be fine. Note that 500 MB is probably quite "small" as far as MySQL
is concerned. The hard work is going to be in translating the structures
across, because you'll almost certainly find that things like data types
don't match up.

--
Nigel Stanger, Dunedin, NEW ZEALAND.
http://xri.net/=nigel.stanger

I found it rather hard to believe that there was no mention of YourSQL <http://yoursql.ludit.it> in your article, the most userfriendly application for managing a MySQL database on the Mac. And yes, it won't run on an Intel Mac but most geeks know how to build their own universal copy using Xcode from the source that is included in the download (change the build target to universal, click build and presto).

André,

Thanks for pointing out YourSQL. I did test out the application (along with a number of other programs) but didn't include it since the article was limited in length and focused more on the new user who wouldn't be comfortable with building their own universal version. It is great that you bring it up, though, since the more options people have the better. One of the great things about MySQL is how many different GUIs there are to pick from.

Cheers,
Jonathan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Jonathan D. Sousa
Sousa Consulting LLC

And don't forget MacSQL <http://www.rtlabs.com/macsql/>, which has been around longer than any other mentioned applications (I started writing it in 1995 with the first PowerPCs). Relationships can be defined in the Schema Viewer and then multi-table queries can be built in the graphical query editor with no knowledge of SQL.

Our Framework product also allows querying MySQL from Interface Builder so you can build a Cocoa or AppleScript Studio project without a line of code.

Mark Lilback
Runtime Labs

I have no experience working in MySQL (or any SQL), but I have done a fair amount of work with Filemaker. I have a solution running now that is about 20 GB in total size, and over 12 million records among the various tables, that does not have any serious performance problems (1 table alone has over 10 million records, and it will return a found set in about 3 seconds). Part of that is the result of realizing that there are multiple ways to do things. Some ways can result in terrible performance, while other ways work just fine. I suppose, like anything else, you have to know how to work with it to sidestep its limitations. Having said that, I suspect that a SQL database engine with a compiled front end GUI is probably inherently faster than Filemaker's interpreted scripts and calculated fields.

Thanks to everybody for some thoughtful & well-informed tips.

Plainly, I'm going to have to look into SQL in much more depth (those book suggestions are particularly welcome!).

However, Rowland offers hope of being able to resolve my problem even more simply - given the too many demands on my time, the steady focus required to master a new language, or even a major new app, seems more than a little out of reach these days.

The only FileMaker list I've tried is fmforums.com - where else should I look?

Fritz’s experience indicates that perhaps I’m missing something vital. While it’s very probably true that my FM configuration could be streamlined, there also seems to be a problem with FM’s memory handling per se. For example, during a prolonged process on a PowerMac with 2 gigs of memory, I opened up Activity Monitor, which indicated that FM was using about 100 megs of RAM and over 300 of virtual memory - while leaving 500 megs of RAM completely idle. That doesn’t seem to be something better use of functions and scripting could address...

For those you don't want the trouble of tracking down dependencies, and re-compiling, I've made a Universal binary for YourSQL available on my web site.

You can download it from:

http://www.infused.org/2006/04/20/yoursql-for-intel-based-macs/