Re: AppleScript access to databases
Re: AppleScript access to databases
- Subject: Re: AppleScript access to databases
- From: BareFeet <email@hidden>
- Date: Tue, 23 Mar 2010 12:33:02 +1100
On 23/03/2010, at 12:52 AM, Axel Luttgens wrote:
>> Unfortunately, no, that won't work all commands. I used to do it that way, but soon found it didn't work for all. I think the problem was with those commands that run over multiple lines.
>
> By "multiple lines", you sure must be thinking about something else than this one:
> Could you remember an example of <sql statements> requiring echo in place of providing it directly as an argument to sqlite3?
I had to go searching to find out why I changed to using echo. This led me down a bit of history and nostalgia.
The problem was with multiple lines in a cell output. That is, if a column in a row contained returns or linefeeds, then the output would appear as a new row starting part way through a value. In order to identify this, I needed to use one of sqlite3's other output methods, which requires a "dot" command prefix, such as:
.mode csv
select SomeColumn from SomeTable;
But the only way to send a dot command prefix and the actual query was to use echo.
It was a long time ago and after my code worked, it just grew roots in my library.
I needed a way to manage all of my databases.
I have tested all other Mac SQLite GUI apps out there:
http://www.tandb.com.au/sqlite/compare/?ml
but found them all lacking.
So, I started building my own, using the AppleScript SQLite library I created, and built a GUI in Xcode (ie AppleScript Studio). I enhanced my SQLite AppleScript library as I went along. It soon worked better than any of the alternatives out there. But the limitations of AppleScript not providing a reliable SQLite hook weighed it down.
When AppleScript-Studio was disbanded with the release of Snow Leopard, I decided to migrate my project over to Objective-C. I found the implementation of AppleScript Obj-C not very AppleScript like and I think it makes the Objective-C syntax (eg where method parameters are beside each of their labels, in pairs) more complicated (by replacing that neat pairing with an obscure underscore delimited method name and positional parameters). I figured if I'm going to have to learn Cocoa anyway, I might as well learn its native language than be constantly reinterpreting it into syntax that was less readable.
I ported the logic of my AppleScript code across fairly easily.
So, now I use my Objective-C/Cocoa developed desktop app to manage my databases. I use PHP to publish selected data entry and browsing interfaces on the web. And I still use several AppleScript routines (based on the library I posted) to link the databases up with other functions such as emailed orders and invoices, outputting an order into web form entry etc.
As my own and customer use of my software grew, it became obvious that we also needed a portable GUI. My customers needed it for data entry and showing off their products (eg real estate). I needed it for database development while on the road, monitoring web sales etc.
Then a funny thing happened. A new device was announced a few weeks ago that would be portable enough for the mobile workplace and customer presentations. I had customers contacting me early that morning to say "This is the future of real estate <or insert industry here>! When can we get our database onto this device?" or whatever industry they are in.
Fortunately, this new device is also programmed in Objective-C and uses the same fundamental Cocoa calls. You can't program it in AppleScript.
So, due to popular demand, I paused my Mac development, moved my entire model code over with barely an alteration and set about designing a new portable interface. I even made it scale down for the iPhone (a third device that uses Objective-C, but again, not AppleScript).
Watch out for it in the App Store in a couple of weeks ;-)
My point here is four fold:
1. You can achieve a lot of SQL functionality using the basic AppleScript SQLite library I posted. I still use it today.
2. I think Apple dropped the ball in not providing AppleScript hooks into SQLite databases files. It would only require a small suite (basically executeSQL and executeSelect resulting in an AppleScript list of records or lists. I'd still really like to see an AppleScript interface for SQLite to open it up to more uses.
3. Once you start getting into very complex do shell scripts or XCode projects, it's probably time to look at jumping ship to learn Objective-C. One day I'll write a tutorial aimed at AppleScripters, since in hindsight I think existing resources make it more complicated than it needs to be.
4. Now is a great time to be an Objective-C/Cocoa programmer :-)
Thanks,
Tom
BareFeetWare
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden