Best Database Solution?

[ralph.lindenfeld]

Hi Folks-

My project will require a database for access, and reporting. Several users on networked macs will be reading/writing to the database on the server. It looks like it will consist of 3 tables. Right now I have them set up with Microsoft Excel, but I’m thinking this won’t scale up well.

What do folks recommend for a solution that can be easily leveraged from within AppleScript?

Thanks,

Ralph

[sixlead]

Hi Ralph,

Your right excel is not your best option. On the mac you have several choices, FileMaker, MySql, PostgreSQL, FrontBase, and 4D something, to name a few. Depending on your level of UI/DB programming skills, time available and funding you should be able to narrow down your options fairly quickly.

The following comments are my opinion only and come from personal experience only.

4D - Commercial, Expensive, big learning curve, supports large user base, likes a separate DB server

FileMaker - Commercial, Expensive, Easy to use very much like MS Access, intended for workgroups, can be served from a work group MAC

MySql - Open Source but commercial support available, Supports standard SQL makes it easy to use, User Interface can be just about anything you want. I think the majority of MySql DB’s are supporting web front ends such as PHP or PHP/Flash, ColdFusion, HTML5 etc…, Very scalable a separate DB server/web server are good for larger user bases, but can be hosted on a work group MAC

PostgreSQL - pretty much the same as MySql but seems to be less popular.

good luck

Here is a link to Apple Developer that has some good info on it.

http://developer.apple.com/internet/opensource/osdb.html

[DJ_Bazzie_Wazzie]

MySQL is very reliable and easy to setup.

Another much better solution I use is using XML-RPC to access the data. The XML-RPC can be easily written in PHP in a very short time, if you’re familiar with http, php and sql. Advantage of XML-RPC compared with a direct connection

1. You define the protocol so changes to server database or choosing other database software doesn’t affect you protocol. Which means the client software doesn’t needed to be changed.
2. you don’t have to parse the mysql data into applescript objects because the built-in xmlrpc client does that for you
3. much faster than using the shell (default mysql client).
4. Every programming language with http/xml-rpc support can access the data.

[ralph.lindenfeld]

Thanks for the replies sixlead and DJ!

I’m familiar with http, and moderately familiar with sql queries.

Where would you recommend going for more info on XML-RPC? It sounds interesting. Do you have any examples DJ?

Thanks,

Ralph

[DJ_Bazzie_Wazzie]

What I did was using the book programming web applications with xml-rpc as my source and builded my own xml-rpc server in php. It’s quite easy because what you do is posting an http page with xml content to the webserver. Then with http_raw_post_data and simple xml (for example) you can easily parse the data into php objects. Then read out the data and return an xml-rpc response back. The objects that can be returned back are strings, integers, double (real), booleans, date, arrays (list) and structs (records). The only object that doesn’t work is binary (base64) but I’ll send them as string objects without problems.

for example when sending something like this:

tell application "[url=http://www.mywebserver.com]www.mywebserver.com[/url]"
call xmlrpc {method name:"xmlrpc.test", parameters:{param1:"hello"}}
end

Something like this is send to the webserver and there you can parse it with simple xml into php object

<?xml version="1.0"?> xmlrpc.test param1hello

Once you have parsed it

//$xmlrpc is the parsed object
$methodCall = $xmlrpc->methodCall->methodName;
if ($methodCall == “xmlrpc.test”){
//this method is called, so do something with it
}

[DJ_Bazzie_Wazzie]

Sorry I forget something to say.

Like AppleScript, PHP has a built in XML-RPC client too. But PHP still reports the following message about XML-RPC:

This extension is EXPERIMENTAL. The behaviour of this extension including the names of its functions and any other documentation surrounding this extension may change without notice in a future release of PHP. This extension should be used at your own risk.

This was for enough reason for me not using the PHP XML-RPC client.

[kerflooey]

Hi, I use FileMaker Pro for everything. It’s not free, but it offers high productivity and ease of development. Networking and security are built in, it can contain graphics, PDFs, and arbitrary binary files, all data is easily visualized and lots of interfaces can be created, it can read and write files, and reporting is quite easy. It’s extensible thru plug-ins and the developer version can create standalone applications. And it’s all Applescriptable as well as internally scriptable. Very good for less technical types like myself.

[druido]

Hi,
I’d add Sqlite3, which is free, shipped with OS X, basically no-setup, robust, and implements (a good part of) the SQL standard. According to the man page, it “can also be used within shell scripts and other applications to provide batch processing features.”, so you can easily use it via ‘do shell script’.

Its main defect is that performance may suffer in highly concurrent systems. But since you’re using Excel (and it seems that it works), your system probably does not fall into such category Besides, Sqlite3 is not based on a client-server architecture, so you will have to provide that yourself. Migrating to other SQL-based DBMSs is also relatively easy.

For a “real” and free DBMS, I would recommend PostgreSQL 9.x, which is superior to MySQL 5.x in terms of authentication, access control, query optimization, standard-compliance to SQL and documentation (not sure about indexing). Replication and concurrency control are very good in both systems. Ah, and OS X 10.7 (Lion) comes with psql (a command-line PostgreSQL client) by default (maybe OS X Server has a PostgreSQL server by default?)

[DJ_Bazzie_Wazzie]

@druido

Hi,
I’d add Sqlite3, which is free, shipped with OS X, basically no-setup, robust, and implements (a good part of) the SQL standard. According to the man page, it “can also be used within shell scripts and other applications to provide batch processing features.”, so you can easily use it via ‘do shell script’.

The topic starter asked for a server database and not a local database. So I don’t think it’s suitable, at least SQLLite is not suitable for multi user because it’s simple and brute force locking mechanism.

@druido

For a “real” and free DBMS, I would recommend PostgreSQL 9.x, which is superior to MySQL 5.x in terms of authentication, access control, query optimization, standard-compliance to SQL and documentation (not sure about indexing). Replication and concurrency control are very good in both systems. Ah, and OS X 10.7 (Lion) comes with psql (a command-line PostgreSQL client) by default (maybe OS X Server has a PostgreSQL server by default?)

1. How about authentication? Where do you need security for in the first place in teh situation of topic starter?
2. Access control is quite easy but there are tons of free tools who makes it even easier
3. query optimization? MySQL is very well documented included the query optimizing
4. standard-compliance to SQL because you can run MySQL in Ansi mode
5. documentation is huge and very specific, maybe for dummies a little hard in the beginning but all the pro and cons are very well described and you’ll know exactly what to do and not to do after the 1,600 pages.
6. indexing, depends in MySQL on type of engine. myisam is a simple light weight engine with brute solutions. Therefore it’s the fastest engine but also unreliable with minimum features (still in 10 year none of my 7 MySQL servers had problems with myisam engines).

I’m not saying postgre is bad, it’s pretty good software but it is definitly not superior to MySQL.

[druido]

@DJ Bazzie Wazzie: Sorry, I didn’t mean to start a flame war on DBMSs, especially in an AS forum You’re right. In the OP’s context, probably any of the mentioned client-server DBMSs would do the job equally well.

[DJ_Bazzie_Wazzie]

Druido: It’s oke and I’m sorry if my post seems a bit harsh. For the record, I was not trying to say that one is better than the other. They just have both their advantages and disadvantages.