This is a general AppleScript question, not specifically SD.
I have a huge file that I work with on a weekly basis that has every movie that’s airing on TV for an entire week.
We currently breakout sublists based on information contained in the date for each movie (year it was released; critic’s ratings, 1 thru 4 stars; a list of channels).
We also do a simple lookup to breakout any movie that was a top ten box office hit the year it was released.
Now we want to add another lookup to breakout any movie that won an academy award or Emmy for best picture; actor/actress; director.
It’s a good size database that includes the winners. (About 100 years for Oscars; 50 years or so for emmys). I can easily get the data, but I’m wondering what’s the most efficient and portable way to manage the lookup.
(For box office hits the list is stored in a text file)
I use Database Events with appleScript for some things and I’m wondering if that might be a better, faster way. Basically I have to lookup each title based on the title and year and if it’s in the list of winners, extract which category and and the names of who won.
Also, twice a year I need to be able to fairly easily update the database with a new list of winners.
Is there a big difference between Data Base Events and SQL Lite Libraries? Is one better, faster, more scriptable than the other?
Ed, I’ve always been a big fan of SQL databases for data of substantial sizes. They are very fast (in fact I don’t know of anything faster), and actually easy to construct the SQL queries once you learn the language. They also provide Views, which are like stored queries, but also act like tables so you can combine them with other Views and Tables.
Since Shane has published his SQLite Script Lib, that would seem to be the way to go.
Also, there are a number of SQL apps/tools that make it very easy for a large audience to use the databases.
They former uses the latter as its storage format. Database Events is fine for simple stuff, but it can get out of its depth very quickly.
I’ve actually had decent performance using arrays and predicates with datasets in the 10s of thousands, but I suspect biting the SQLite bullet would make more sense for your situation.
Interesting. But have you ever seen it faster than using SQLite?
And, I have to say, at least for me, writing SQL queries is much, much easier than dealing with ASObjC.
I’ve never compared it. In the two cases I’m thinking about, it was the easiest approach in their circumstances, and once it was established that it was easily fast enough, there was no need to go further. Keep in mind that using arrays means everything is in memory, which helps performance — although loading time is quite significant (but largely irrelevant in these cases, because it happens once at launch time).
Having said that, I wouldn’t be at all surprised if SQLite would have been faster — it just would have been considerably more effort.
Right — the tool we’re familiar with nearly always looks the best bet for most of us. But the picture can look different for someone who has never written an SQL query before.
I went with Database events for my small projects it’s fine, but now I’m thinking I’ll try Shane’s SQL library for bigger projects that will need to go faster.