The rants and ravings of a Mac developer
I Love programming, Macs, the iPhone, iPad, Apps & iOS! In my spare time I like Programming in ExtJS, PHP, MySQL / SQLite3 and Apache - in other words a Real Geek's geek. I've tinkered around with programing for iOS in the not so distant past . During the day I work for Motorola Solutions in IT as BSA for our Export Compliance system JPMorgan's TradeSphere. At night I freelance program on the Mac & Web. I'm also a techie who likes Sci-Fi and Horror. Some of my favorite authors include Stephen King, William Gibson and Frank Herbert as well as his son Brian Herbert and Kevin Anderson.
But my real passion is for Comic Book Collecting. My current freelance project is writing a web based application using ExtJS for collecting comics books. After I get the web application completed, I will be integrating some portion of the web application into a desktop client for the Mac through the use of web services for all data exchange & transaction. Eventually, the web site & Mac client would also include a social networking component as well as an iOS component.
I started collecting comic books as a kid when my grandfather passed away and we found my father's comic book collection when we were cleaning out my grandfather's basement. There were a number of early issues in that stash, such as Fantastic Four number one, The Amazing Spider-Man number one, number 22 of the Uncanny X-Men... and others. So needless to say, I was hooked.
After high school I stopped collecting - college, drinking and woman took priority (not necessarily in that order) over visiting the local comic book shop. Now that I have 2 kids of my own, I've gotten back into collecting.
Character Sets and MySQL command line client
FYI… if connecting to a MySQL database using the MySQL command line client (tool) where the database collation / character-set is set to utf8_general_ci / UTF-8, you need to issue a
SET NAMES 'utf8';
command prior to running any queries so that any special characters show up correctly.
Refer to the MySQL Reference Manual, section 9.1.4. Connection Character Sets and Collations and then search on SET NAMES.
Also if you have a bunch of MySQL statements in a file and want to execute them against a UTF-8 database you need to include the —default-characters-set option on the command line.
mysql --default-character-set utf8 -u user1 -puser1 cb_upc_db < extract.sql1 year ago
Character Sets and PHP Follow Up
Following up to my Feb 23, 2010 post on difficulties with charaster sets I’ve yet run into another example of how this can mess you up.
In PHP, I was using curl to fetch a page from a remote web server and then extract some data using regex and then inserting it into a MySQL database. The issue that I encountered was that my database was in was in UTF-8 but the text being retrieved from the website was in ISO-8859-1 (aka Latin-1 according to wikipedia). So when I selected a record that had incompatible characters between UTF-8 and ISO-885901 I either saw garbage or truncated values. For example, the value ‘Atlantic Förlags’ on the web page showed up in my table just as ‘Atlantic F’.
So to investigate I used FireFox to open up the URL I was parsing in my PHP script and then right clicked on the web page and selected ‘View Page Info’ from the popup menu.
Now all I needed to do was use PHP’s string iconv(in_charset, out_charset, str) function to convert from ISO-8859-1 to UTF-8 before inserting each string value into my database. Like such:
$trans_text = iconv("ISO-8859-1", "UTF-8", $text);
At least that’s how it goes in theory… your mileage may vary.
1 year agoAh, Language, isn’t it wonderful. If you’re not a programmer
Well I’ve spent a good part of the day reading the MySQL manual pages for Language support… and I’ve got to say, life would be so much simpler if everyone only wrote, spoke and used English… I know that’s not a very global attitude, but really, when you get into things like multilingual support (i.e., Unicode) in applications, programming languages, databases, web browsers its a wonder that anything works at all!
This basically comes into play, when importing, exporting or displaying data across different systems that use different character sets. For example reading data from database (stored in latin1 for example) and them manipulating it in PHP (defaults to cp1251, I believe) and then displaying in in a user’s web browser (using utf8). Not only do you have to worry is the character that I want available in the character set that my programming language uses, but now you have to make sure you translate it properly when reading and outputting it. Not only are there all these different character sets, but no two applications/databases/programing languages refer to them by the same name! Talk about impossible tasks!
Not only are there different characters in different languages, but there are differences how the characters in each language are sorted - this is referred to as a collating sequence. For instance in Spanish an ‘ñ’ (n-tilde) sorts between the ‘n’ and ‘0’ characters. And if using traditional spanish, there’s a ‘ch’ character that sorts in between ‘c’ and ‘d’, like wise with the character ‘ll’, its in between ‘l’ and ‘m’. And it gets worse for other languages such as Swedish/Finish.
This issue also affects the comparison of characters and doing database searches… like is ‘Ano’ equal to ‘Año’ (2nd character is a n-tilde)? Other examples (not sure these will show up correctly in this post) are ‘ß’ is ‘technically’ equal to ‘ss’, but some programming languages/database may say its equal to ‘s’ (I don’t even know what ‘ß’ is for). And look at these other ones, ‘Ä’ = ‘A’, ‘Ö’ = ‘O’ and ‘Ü’ = ‘U’!
Woe is me… and here UTF was supposed to be the holy grail. Remember, “There is no silver bullet.”
1 year agoI’ve posted some new articles lately, check out:
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 years 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.
2 years ago
