Databases are all alike, aren't they? Well, no, and this week Adam shares his real-world experiences with Panorama V, an unusual database that makes it easy to add new capabilities as you discover you need them. In other items, Jeff Carlson covers three new ebook initiatives, Glenn Fleishman opens his Mac mini to add RAM on the cheap, we link to several Take Control excerpts, and we note a court decision permitting Apple to subpoena confidential information about trade secret leaks.
Topics: MailBITS/14-Mar-05 When It Bends, It's Funny. When It Breaks... A Rising Tide of Ebooks When You Need a Panoramic View Take Control News/14-Mar-05 Hot Topics in TidBITS Talk/14-Mar-05
This issue of TidBITS sponsored in part by: * READERS LIKE YOU! Help keep TidBITS great via our voluntary <------ NEW! contribution program. Special thanks this week to David Illig, Will McClintock, and Scott Barnham for their generous support! <http://www.tidbits.com/about/support/contributors.html>
* SMALL DOG ELECTRONICS: iWork, iLife, .Mac - $229 <----------------- NEW! 12-inch iBook with AirPort Network & Case - $949 Apple 20-inch Cinema Display (old style) - $795 Visit: <http://www.smalldog.com/tb/> 800-511-MACS
* FETCH SOFTWORKS: Want to copy files between FTP servers? <--------- NEW! Use Fetch, the FTP client for Mac OS 9 and X, and you can drag and drop files for fast and easy file copying. Download your free trial version! <http://fetchsoftworks.com/>
* Dr. Bott, LLC: Self-powered Bluetooth adapter for iPod. <--------- NEW! Head for the hills, tuck your iPod safely in your backpack, and revel in your untethered freedom. Bluetooth remote included, look for more accessories soon. <http://www.drbott.com/>
* Web Crossing, Inc: Site Crossing brings Web Crossing power to <---- NEW! your small biz, family or club. Build a core site or bolt onto a current one for discussions, blogs, chat, polls, calendars, podcasts, and more. <http://www.sitecrossing.com/tb-305>
* Circus Ponies NoteBook: Never lose anything again. NoteBook <------ NEW! lets you take notes, clip content, and share information. Find anything instantly with automatic index pages. One-step Web publishing. Free 30-day demo! <http://www.circusponies.com>
* Awarded 4 mice by Macworld, Nisus Writer Express 2.1 is the writer's word processor that is both powerful and easy to use. Use coupon code "TidBits" and receive a $10.00 discount! <http://www.nisus.com/in/tidbits/> ---------------------------------------------------------------
MailBITS/14-Mar-05 ------------------
**Apple Wins Subpoena Request** -- In the latest update in Apple's quest to squelch information leaks, Judge James Kleinberg of Santa Clara County Superior Court ruled last Friday that the news site O'Grady's PowerPage must divulge its confidential sources, describing the information as "stolen property." The judge was careful to note that his ruling should not be construed more broadly than Apple's right to subpoena information from PowerPage's ISP Nfox. The judge also made a distinction between "the public interest" (served by whistleblowers disclosing health, safety, or welfare hazards) and "the interested public" (served by news and enthusiast Web sites). The full text of the decision can be downloaded from The Mac Observer site. Jason O'Grady has said he will appeal the decision. [ACE]
When It Bends, It's Funny. When It Breaks... -------------------------------------------- by Glenn Fleishman <glenn@tidbits.com>
I had beads - nay, rivulets - of sweat on my brow. Why wouldn't it open?
You guessed it: I was installing RAM in my new Mac mini. I avoided Apple's $425 price tag for a 1 GB upgrade (from the meager built- in 256 MB of RAM), and bought generic RAM for $200. I also watched Other World Computing's very clear video of how to use a putty knife to crack open the mini case.
Perhaps I was too sanguine. I'd slipped the knife in, and starting cracking. But the noise was terrifying. I watched the plastic bow and thought, "This just can't be right, regardless of what I'd heard."
I persisted. I nicked the bottom of the case a bit, but after wiggling it this way and that and hearing awful ship's-rigging- collapsing, nightmare-tree-falling sounds, the case released its burden and allowed me access to the beautiful innards. I'd wrestled with the giant clam and snatched its pearl.
Well, I snatched a 256 MB memory module. I put the new 1 GB module in, sealed her back up - just a little worse than re-sealing a Tupperware-brand container - and fired the puppy up.
Happy as a clam, it booted.
A Rising Tide of Ebooks ----------------------- by Jeff Carlson <jeffc@tidbits.com>
Adam is fond of talking about the "Macintosh ecosystem" and how the companies and products that make up that system generally work together for a common good. When a shortcoming is found in Mac OS X, for example, software developers create utilities to address it. In many cases, you get several programs that do the same thing, but with their own unique approaches. Look at the plethora of snippet-keepers (many of which Matt Neuburg has reviewed). A couple years ago, I didn't put much thought into whether such note-taking and -organizing software existed, and now I look for which one offers the features that suit my style of working.
The same effect is starting to apply to electronic books, as well. Since TidBITS Electronic Publishing started the Take Control series of ebooks (of which I've edited three titles), I now pay more attention to other publishers who are releasing Mac how-to books using the electronic publishing route. As more titles appear, the credibility and usefulness of the entire category improves - "a rising tide floats all bits," if you will.
<http://www.tidbits.com/takecontrol/>
If you're looking for more resources to expand your Mac how-to book library (and since I make part of my living publishing printed books, I include traditional publishers in that classification), here are a few other ebook publishers that have recently released titles.
**MyMac.com's Scroll Down Books** -- I often wonder how the folks at MyMac.com have time to review all the books and products that they do. Now they're adding to their plate by launching Scroll Down Books. So far they've released two $5 titles in PDF format: "Buying Used Macs," by Neale Monks (176 pages), and "iMovie - On the Cheap," by Chris Seibold (95 pages). (Seibold's title covers iMovie 4, not iMovie HD, but because the two versions don't differ significantly - aside from the capability to edit HD footage - all of the information still applies. It wouldn't surprise me if Seibold is working on an update, as are the rest of us who have published iMovie books.) Although both titles are inexpensive, I was hoping to find a free downloadable sample of each book to let potential buyers preview the content and style of writing.
<http://www.mymac.com/ebooks.php>
**Fix a Troubled Mac** -- One of the advantages of electronic publishing is the speed and ease of being able to update an ebook as software and hardware advances. Fix a Troubled Mac, a $15, 222-page PDF written by "dirtymouse" (would a real name be so hard to include?), certainly benefits from that type of flexibility. The title includes troubleshooting information for Mac OS 9 and Mac OS X, with lots of photos and diagrams to help navigate the often headache-inducing process of nailing down and fixing problems. A 4.1 MB sample can be downloaded from Apple's Web site.
**SpiderWorks** -- Another advantage of electronic publishing is its economies of scale. When a print publisher creates a title, the company pays for the printing of a minimum of a few thousand copies up front, with the expectation that it will recoup its investment from sales of those copies. But what if a publisher doesn't think a title will sell its minimum print requirement, or what if the publisher doesn't have the money on hand for the initial print run? The history of publishing is strewn with good titles that haven't been released or updated because publishers, for whatever reason, chose not to invest in them.
SpiderWorks, a new ebook publisher, is filling that gap by publishing updated electronic editions of books that are no longer in print (as well as brand new titles). The last edition of Danny Goodman's AppleScript Handbook was published in 1995, but now SpiderWorks is offering the third edition of the title as a $15 PDF (388 pages), now updated for Mac OS X. Also coming back for more is Dave Mark's Learn C on the Macintosh ($15, PDF, 292 pages), updated for Mac OS X. New titles include David Hill's Cocoa Game Programming ($10, PDF, 152 pages) and Ben Waldie's AppleScripting the Finder ($10, PDF, 107 pages). You can download samples of each ebook, which include the full table of contents and some content.
<http://www.spiderworks.com/>
It's great to see other publishers releasing ebooks that go well beyond simply printing a traditional book to a PDF file by adding features like links, bookmarks, and layouts designed for onscreen reading. For instance, the SpiderWorks ebooks all feature a two- column layout that seems to be a good fit with the horizontal screens of most Macs and that prints horizontally on an 8.5 by 11-inch sheet of paper.
When You Need a Panoramic View ------------------------------ by Adam C. Engst <ace@tidbits.com>
When you're setting up a database for the first time, do you know exactly what you'll want out of it, for the rest of the life of the database? Heck, do you know what you're going want from it next month? You probably have a general idea, and if you've created databases in the past or are working with a consultant, you'll probably spend some time mapping out your data structures, reports, and more. That work is usually highly worthwhile, since it can be difficult to rework overall architecture in many database programs.
Until 2003, I hadn't created a custom database by myself in years. Geoff Duncan set up all the databases related to TidBITS, and all the rest of my database tasks were ably performed by dedicated programs such as Now Contact (for our shared address book) and Eudora (for archived email) that did an excellent job at what they were designed for, but didn't venture past those confines. But at the end of 2003, we started Take Control, and I knew we were going to need a database to track orders and generate royalty statements for our authors. It's conceivable that there is a dedicated royalty database program available, but I couldn't imagine it would be able to handle the raw data we received from eSellerate, and I didn't want to pay a consultant to create something until I knew exactly what I wanted the database to do.
**Deciding on the View** -- After some thought, I decided to create my royalty database in ProVUE Development's Panorama, a powerful and quirky database program that was one of the first Macintosh databases ever. I had used Panorama last back in 1992, so I'd forgotten anything I once knew about programming it. What I did remember was that Panorama's RAM-based architecture made it lightning fast when you were performing queries, something that's often not true of other databases unless you've indexed the fields upon which you're querying and you're performing a relatively basic query (Panorama queries can search for text in the middle of words, search for text phonetically, search for fields compared to other fields, and search using an arbitrary formula). Also key in my decision was Panorama's Data Sheet, a spreadsheet-like view of your data that matches the way many (if not most) people who are not database professionals like to visualize their data. That tabular visualization is the reason so many simple databases are created in Microsoft Excel, even though Excel isn't particularly accomplished at database work.
<http://www.provue.com/>
The reasons these two facts - fast queries on any field and the Data Sheet's tabular view - were important to me is that I knew going into my project that I didn't have a clue what the database would look like in a year. And not only could I not predict what I was going to want, I didn't have time either to guess at what might be important or to spend a lot of time creating a database that performed tasks I didn't need. I knew I needed to calculate monthly royalty statements for a couple of books based on the data in tab-delimited text files that I received daily from eSellerate. At that point, though, I could barely imagine that some authors would be writing multiple books, that we'd also be paying editors and translators (some of whom could also be authors), that we might end up with multiple authors sharing royalties on a book, and so on. Plus, when I started writing the database, I knew relatively little about my incoming data. I couldn't predict that we'd want coupons for individual books, for bundles of books, and for entire orders, and until some appropriate orders came through, I had no idea how affiliate adjustments had to be made. In short, I was flying low, fast, and blind, and I needed a highly maneuverable airplane to skim through my data. In a word: Panorama.
**Zooming In** -- I'm not going to attempt to tell you everything about how Panorama works; it's both powerful and deep, and I'm sure I've merely scratched the surface of its capabilities. What I will tell you, however, is a bit about some of the features that enabled me to understand my incoming data and extract the results I needed. Each month I have enhanced the database a bit more as I learned more about what I wanted.
As I noted earlier, Panorama's spreadsheet-like Data Sheet is a wondrous thing. You don't have to create any layouts to enter or view data in Panorama, for the simple reason that in the Data Sheet, you can see and change every field of every record. Of course, you can create layouts that help you focus on specific fields or that are used in generating printed reports, and I did create them eventually. But even still, I spend most of my time in the Data Sheet, since it's such an excellent tabular overview of my data. So, every month I would use the Unix cat program to lump together all the daily reports I received from eSellerate in email, then I'd strip out the column header records on each using BBEdit, and then I'd import the resulting text file into Panorama. Seems clumsy, doesn't it? I mentioned this while chatting with Jim Rea, Panorama's creator, and he sent me the bones of a procedure - which is what Panorama calls the little programs you write to automate tasks beyond what's possible from the interface - that looked in the appropriate folder and imported all the files in it, automatically stripping the column header records. Life was already a little easier (and there's also a mailing list for Panorama users to share similar pointers).
<http://www.provue.com/support.html>
The Data Sheet not only looks like a spreadsheet, it works like one as well. For those first few months, before we had coupons or affiliates to complicate matters, I relied on the commands in Panorama's interface to generate raw numbers that Tonya then further massaged in Excel to generate an actual royalty statement. Within the Data Sheet, you can sort on any column (columns are fields, rows are records), show just the records that match single or multi-field queries, and, most interestingly, group records by field. Grouping is tremendously helpful because it lets you collect records into what Panorama calls "summary records." Summary records are real records that can contain the same types of data as normal records, but they're temporary; you can use them to hold the results of mathematical equations as subtotals, and when you're done with a particular grouping, remove them. Summaries are essentially a hierarchical outline, with an outline level for each grouped field, and you can view any level of the outline independently. This feature proves to be useful because it enables you to - for any ad hoc query - group and subtotal the records found by the query, view the subtotals, and then expand either the entire set of found records or any individual group to see how you arrived at particular subtotals.
An example will make this more clear. We now have a lot of books to track, and every now and then when I'm running royalties, I notice that my database and eSellerate's payment report disagree about how many copies of each were sold in a month. So I do a search to limit the displayed records to just books sold in the appropriate month. Then I group the database on the Title field to make a summary record for each book. Then, while in the Quantity field, I use the Total command in the Math menu to count the number of each book sold in that month. Lastly, I change the outline level so only my subtotals and the grand total (created automatically when I used Total) are showing. With these steps, I've gone from a list of several thousand records to about 20, and I can easily compare my numbers with eSellerate's. When I find a subtotal discrepancy, I expand the summary record for that book, and glance at the raw data, which usually reveals the reason right away. This is a simple, though real example, and I use this basic technique often to understand my data and the result of any calculations I'm performing on it.
**Automation** -- Of course, just because you can perform all these commands directly from Panorama's interface doesn't mean you should. Panorama has a full-fledged programming language, complete with local and global variables, looping commands, and so on. Any time you find yourself repeating the same actions over and over again, it's time for a procedure. My first procedures were quite simple; they just selected data, grouped it, and performed calculations on the records in the group.
The most important procedure I wrote calculates the amount of money different people earn for each sale of each individual book, storing those numbers in new fields I've created. In concept it's simple: multiply the unit price by the quantity, subtract the transaction fee, calculate any coupon discount and subtract that and any affiliate charge, and then divide the resulting subtotal between the author, editor, publisher, and any translators. In reality it's simple too, or it was until we ended up with oodles of specific coupon codes, some of which apply only to specific books (but which still appear in the records for other books purchased in the same order), others of which apply to the entire order, and all of which can have different percentage adjustments or fixed discounts.
All of the complicated "if a record has this coupon, perform this calculation" code for coupons has caused my earnings calculation procedure to get ugly, and adding new coupons to it each month has become a painstaking and error-prone process; that in turn was the hint I needed to move to the next level in Panorama, using its relational capabilities.
**Linking Databases** -- In the beginning, Panorama was a flat- file database, but at some point in its long evolution, ProVUE added the capability to link databases by lookups, statements in a procedure that go into another database, find appropriate records, and extract the relevant data from those records. Once you have the data, you can do with it whatever you want, such as using it in calculations, inserting it into the database and so on.
My first effort at linking databases came when I wanted a way of connecting book titles to authors, since eSellerate didn't know the author name to include it anywhere in our raw data. I could have written a procedure that found all instances of "Take Control of Upgrading to Panther" and filled in an Author field with "Joe Kissell", but that would have required constant modification of the procedure to account for new titles. The right way to do this is to create a Books database that contains fields for Title, Author, Editor, and Translator, and then, whenever I needed to know the author (or editor, or translator) of a book, to look that up in the Books database. Adding data for new books to the Books database is much easier and less error-prone than modifying a procedure each month.
That's especially true of the enhancement I'm working on this month, which is a new Coupons database that tracks all the different coupon codes, the percentage or amount of discounts they embody, and information about when and why they were generated. I haven't quite finished the code yet, but the idea is that for any record in my Orders database that has a coupon, I can look up the coupon in the Coupons database, determine if a discount should be applied, and do the math based on the information reported back for that coupon. Since we've used about 50 coupons so far, this is proving already to be a more coherent and accurate approach. One use for the Coupons database has already arisen - a minute's work with Panorama's Text Export Wizard and I was able to export an HTML table of the coupon codes and descriptions to share with our authors on our internal wiki. The Text Export Wizard is only one of many useful canned utilities that ProVUE has written and bundled with Panorama.
Yet another advantage of breaking the system down into multiple databases is that it leaves room for growth. At the moment, the percentages we use to calculate author, editor, and translator royalties are the same for everyone. But I could imagine a situation where they weren't the same, and if that comes up, it should be relatively easy to add fields to the Books database that specify what percentage each contributor to the book should earn. Luckily, with Panorama, there's no reason to cross that bridge until I come to it.
**Layouts and Reports** -- Unlike many databases, there is almost no manual data entry in our system, and what there is (mostly direct sales from organizational purchase orders and the like) could be done directly in the Data Sheet in our separate Special Sales database. However, when we published the "Take Control of Panther" print book with Peachpit Press, we created a situation where we would not only have to enter the data from the statements they sent us, but we'd have to break apart the entered record to be able to share the proceeds appropriately with each of the authors whose book was included in the print collection. For that, I created a layout - basically a form containing fields and labels - into which the data could be entered. Then I wrote the procedure that split up the entered data so it matched the format of other special sales and attached that procedure to a clickable button. Now, when it comes time to enter any bundles, such as our print books, I can type in the numbers, select the appropriate bundle from a menu, and click the Split Bundle button to break it apart. In essence, I'm using the layout both as an interface with which to enter data and as way of transforming the entered data into the form I need. Layouts can thus protect the data from me (preventing me from modifying bits I shouldn't accidentally) while at the same time protecting me from the data (by hiding irrelevant fields). And if I'm ever concerned about something I've done, I can easily check in the Data Sheet to make sure all the underlying data is correct.
The most obvious use for layouts, of course, is in generating printed reports, which are one of the more powerful and tricky parts of Panorama, because they rely heavily on the summary records created by grouping (since that's how you get subtotals, and you usually want to include subtotals in your reports). Panorama handles all this with the concept of report "tiles," onto which you put boxes that display information from specific fields. Tiles are associated with different summary levels (since you probably want a variety of subtotals and totals on many reports), and there are a variety of options for how they float on the page. Panorama also offers a full graphic editing environment for adding boxes and lines and text and images to your report. It feels like MacDraw of yesteryear, and my main irritation is that getting a report to look just right is often quite time-consuming, especially since you can't necessarily predict the length of any given field. Nevertheless, using it I was able to create reports showing both historical sales over time for each book and monthly royalties for each author, editor, and translator.
**Interface and Usage Nits** -- I won't pretend that Panorama is perfect by any means. Its interface, having evolved across many years and many incarnations of the Mac OS, is quirky, bordering on weird in places. For instance, you can't close Panorama's Preview window (for previewing reports) with Command-W, and moving to the next page (you can't move the previous page) requires clicking a tiny page button in the upper left corner; a set of modified scroll arrows would be more obvious. Opening a procedure or layout in a new window (rather than taking over the frontmost window) requires holding down a modifier key. Little things trip me up too, like trying to close dialogs by pressing the Escape key; it works in system-level dialogs like Print and Save As, but not in any of Panorama's native dialogs, and worse, if a Panorama dialog contains a text field, pressing Escape types a character in that field. And as cool as the Data Sheet is, you have to be a little careful in it, since it's easy to add a new record accidentally by pressing Return or by scrolling past the last visible record. Luckily, Panorama prompts for confirmation if you accidentally try to delete a record by pressing the Delete key when you mistakenly thought you were editing the contents of a field.
**The Grand Total** -- Usage nits aside, the way Panorama allows you to take advantage of its features over time, as you discover what you need and learn more about how to use Panorama itself, proves to be the most unusual and attractive aspect of the program. I'm no database expert, but other databases I've used over the years haven't been nearly as flexible or forgiving if you decide that you want to change the way the database works in some fairly radical fashion. If you are looking for a database that can grow with you, Panorama is worth taking for a test drive. The free evaluation version is fully functional, but once your database has more than 250 records in it, you'll be prompted to play a little game every time you print or save.
<http://www.provue.com/panoramatestdrive.html>
Panorama V runs natively in both Mac OS 9 and Mac OS X, includes over 3,000 PDF pages of documentation, and costs $300 for a full development version (you can also get runtime-only versions for $130).
Take Control News/14-Mar-05 --------------------------- by Adam C. Engst <ace@tidbits.com>
As a way of spreading the word about Take Control ebooks, we've started working with some of our friends at other publications to publish excerpts from a few of our books. You can already download free samples of all our ebooks to see what's covered and what the reading experience would be like, but the excerpts contain the full text and screenshots of particular sections.
**Take Control of Sharing Files in Panther excerpt** -- If you're interested in a more secure, more configurable FTP server in Mac OS X and you haven't already purchased Glenn Fleishman's "Take Control of Sharing Files in Panther" ebook, head over to O'Reilly's MacDevCenter, where we've published an excerpt from the latest version of Glenn's popular ebook. In the excerpt, Glenn explains how you can install and configure PureFTPd to replace Mac OS X's built-in FTP server, turn on anonymous FTP access, create FTP users that don't have associated Mac OS X login accounts on the machine, and more.
**Take Control of Mac OS X Backups excerpt** -- Joe Kissell's "Take Control of Mac OS X Backups" has been our best-selling ebook of recent months, and it's great to see so many people protecting their data with good backups. If you haven't yet bought Joe's ebook, you can read a two-part excerpt on Macworld's Web site. In part one, Joe helps you start developing a backup strategy: he explains the difference between duplicates and archives (and why you want both), and discusses why synchronization utilities aren't sufficient for backups. Then, in part two, he talks about how often you should back up, looks at what's different about backing up a small network, and provides his overall recommendations.
Hot Topics in TidBITS Talk/14-Mar-05 ------------------------------------ by TidBITS Staff <editors@tidbits.com>
The second URL below each thread description points to the discussion on our Web Crossing server, which will be faster.
**Good streetmap software for Macs?** Do any companies make mapping software comparable to Microsoft Streets & Trips for Windows? Route 66 could be the answer, or online mapping sources. (2 messages)
**Apple, FireWire, and USB** -- Apple stopped bundling a FireWire cable with its new revision of iPods in favor of a USB 2.0 cable, leading to a spirited discussion of which format reigns supreme, including a long post from Michael Teener, who was the tech lead for FireWire at Apple in the 1990s. (22 messages)
**Two-Fingered Blackout PowerBook Dropping** -- Glenn Fleishman's article last week about the new features of the latest PowerBook models helps reveal not only the motion sensor hack mentioned in the article, but also a game based on the technology. (2 messages)
**Auto scroll utility** -- Is there a way for fast readers to scroll automatically through long Web pages (or other documents)? A few suggestions are tossed into the ring. (3 messages)
**Mac OS X Window Behavior** -- Jeff Carlson's article on the default behavior of Mac OS X windows spurs debate on which methods are "right" or "wrong," while alternative methods for bringing windows to the front are offered. (29 messages)
**Domain Name Hoarding** -- What's to be done about companies that buy blocks of domain names and sit on them without developing them? And is this really a problem? (24 messages)
**About DRM and copying** -- Prices of many products in France and elsewhere, such as iPods, include markups that in effect assume you're going to use the product to steal copyrighted material. Is this type of front-loading tax at all effective? (9 messages)
**Terminology surrounding shareware** -- With large commercial developers offering trial versions of their products and then providing online payment and download (such as Adobe Photoshop), does the term "shareware" still retain its original meaning? (2 messages)
Non-profit, non-commercial publications may reprint articles if full credit is given. Others please contact us. We don't guarantee accuracy of articles. Caveat lector. Publication, product, and company names may be registered trademarks of their companies.
For information: how to subscribe, where to find back issues, and more, see <http://www.tidbits.com/>. TidBITS ISSN 1090-7017. Send comments and editorial submissions to: <editors@tidbits.com> Back issues available at: <http://www.tidbits.com/tb-issues/> And: <ftp://ftp.tidbits.com/issues/> Full text searching available at: <http://www.tidbits.com/search/> -------------------------------------------------------------------
