I’ve posted some new articles lately, check out:
The rants and ravings of a Mac developer
As some of you may know or may not know I’m a IT guy that works for Motorola and I like to code for fun in my free time. And some of you may also know (or not) I’m a comic book nut… so my latest hobby is thinking up cool things I could code up to help comic book junkies like myself.
Well the other day I had an idea for a new web site that hopefully will help collectors get their comics into their favorite comic book collection software program easier. I’ve used a few programs in the past and they have all had their pros and cons. One of the ones that I’ve really like is ComicBase it comes with this huge database with every comic imaginable with tons of information already loaded into it. The only issues I have with it are: 1) its PC only [remember I’m a Mac guy] and 2) the interface was a little rough and not exactly intuitive 3) you have to pay each year to continue getting data updates.
The program I currently use on my Mac is called IntelliScanner Comic Edition (its cross platform for you PC guys out there). This program comes with a scanner. Cool I thought I can just scan the Comic Book’s UPC code and add it into the program… NOT!
So here’s my idea… create a web site that where users contribute the UPC and comic book data. This way the data will be free to use. The web site will then provide a web service [fancy words for a internet database that other programs can look up information in] to look up a UPC code and return all the title and issue information that is needed by the comic book collection software. This way all you have to do is scan in the UPC Codes the comic book collection software calls the web service passing the UPC codes, for each UPC code the web service sends back all the info on the comic book, and the comic book collection software adds the issue right to your collection with out you doing any data entry. Fast, easy, accurate and consistent.
I’ve just started working on the web site, which is called the Comic Book UPC Database. I hope to have a beta up soon. I also have set up the Comic Book UPC Database blog to get news and updates on my progress.
So my gripes about the IntelliScanner Comic Edition program… the major thing that I don’t like about it is that adding a comic book is a hit or miss thing. The way its supposed to work is you add the title and then you add the issues. The program uses the Grand Comic Book Database (GCD) and the Comic Book DB (CBDB) to ‘automate’ the data entry process. Problem is the data between these 2 internet database is often different and inconsistent between one another. Some times you get the issue’s cover scan, sometimes you don’t. Often one database will have information for the comic but not cover scan, or vice versa. Also the program sometimes gets confused and doesn’t pick up the right information. The program does not handle what is called “variant” comic books very well (actually not at all). Variant are comics books that have multiple covers of the same issue. Often done by a different artist or is a sketch of the original cover. Additionally some times there are multiple printing of the same issue, the program doesn’t handle that either. Lastly, there’s no way to group your titles or issues logically, say if you had some comics that you wanted to sell you could put them into a special folder. Navigation is also sometimes a pain.
The only place where the Bar Code scanner comes into play is AFTER you’ve entered all your comic books. Then you can assign them asset tag number (just a unique number for each comic book). You can use the UPC code as long as you don’t have 2 of the same comic book, otherwise you have to come up with your own unique asset id. When you want to search your collection in the software, you can just scan in the asset tag and the software will pull it up. A little disappointing if you ask me. They have other programs that let you scan in the bar code from a book or a movie or even a video game and it will look up the bar code and return all the information about that item. The only problem for comic books is that no one has created the data base - until now…
That’s what the Comic Book UPC database will provide. Additionally, if many of the comic book collection software programs start using the same database to get their title and issue information from, it will lead to standardization on Title names and issues numbers. Currently every program out there has their own naming schema for issues, so what called the Fantastic Four (1961) in one program may be called the Fantastic Four Vol. 1 in another. Also when publishers change the numbering schema for a title, like Marvel did with some of its titles, some programs and database split the titles differently. For example after issue 416 of the Fantastic Four, the title restarted at issue number 1 (essentially volume 2) in 1996. There were 13 issues and then they restarted at issue number 1 again in 1998 (aka volume 3). This time the issues went all the way up to number 70. But after the 70th issue of volume 3, in 2003 the issue number jumped to 500. This is the issue number that the Fantastic Four would have been if they had not restarted the numbering from 1 twice (i.e., 416 + 13 + 70 = 499, the next issue is 500). So The GCD considers this to be the Fantastic Four, the 2003 series [essentially volume 4], but the CBDB considers this to be a continuation of the 1961 series [i.e., the continuation of volume 1].
Inconstancies like the above can cause much confusion among comic book collectors, especially newbies. Additionally the GCD, CBDB and ComicBase all handle variants differently (some programs ignore variants altogether). I actually favor how ComicBase does it, they track an issue number, the printing (first, second, third, etc.) and then assign an alphabetic character to track the variant. Having one database that supplies all the information on Titles and issues would solve both of the above issues.
Finding the original photo file in the iPhoto Library database
Using iPhoto’s SQLite3 database you can get the relative path to the original photo in the iPhoto library for any image in the sqPhotoInfo table. To do so you must join from the sqPhotoInfo table to the sqFileImage table and finally to the sqFileInfo table.
select pi.primaryKey, pi.archiveFilename, fl.relativepath from SqPhotoInfo as pi join SqFileImage as fi on pi.primaryKey = fi.photoKey join sqFileInfo as fl on fl.primaryKey = fi.sqFileInfo where fl.relativePath like 'Originals%'2 months ago
Date Format used in iPhotoMain.db database
Not sure how many of you know that iPhoto uses SQLite3 as its database engine. You can access the database files by right clicking on ~/username/Pictures/iPhoto Library and selecting Show Package Contents. Once you have displayed the package, the SQLite3 datbase files are:
- iPhotoMain.db
- iPhotoAux.db
- face.db
- face_blob.db
The main database file, as you might have guessed, is the iPhotoMain.db file. In Terminal, you can open the file by changing to the directory of iPhoto Library package, e.g.:
cd /Users/gskluzacek/Pictures/iPhoto\ Library
Then execute the following
sqlite3 iPhotoMain.db
One of the initial problems that I faced was when reading date fields, like the photoDate field on the sqPhotoInfo table. After some experimenting, I determined that the date is stored as a FLOAT which represents the number of days since midnight Jan 1st 2000. To convert this to a Julian Day value, you need to add the Julian Day of 1/1/2000 - then you can use the datetime() function to convert this to a more human readable format. For example:
select primaryKey,
datetime(photoDate + julianday('2000-01-01 00:00:00')) as photoDate,
archiveFilename from SqPhotoInfo order by archiveFilename desc;
Hopes this saves someone a couple of hours, I googled all over and couldn’t find this info.
3 months ago
