Bruji News

Alex again here with some Doghouse stats. The Doghouse, our new data repository for the Pedia programs, is growing nicely:

We are nearing the 100,000 mark for DVDs in our database. This is a great milestone.
We have more than 140,000 books already, awesome.
61,000 CDs, guess people don’t use those too much anymore.
But only 6,000 games. Where are the gamers out there? Or maybe there just aren’t that many games to begin with?

Here’s where it gets interesting:

Directors in our database: 23,000. Wow, that’s a lot of directors.
People in movie credits: about 360,000. Ouch! This includes actors as well as other jobs that usually appear in credits at the end of a movie.
Geeky fact: we have a table that associates the credits with the movies and this table has 1,612,713 entries in it. That’s over One Million and a Half (capitals awarded for such a big number).

There have been 500,000 contributions from users like you and 200,000 have been matched to entries already contributed. The search rate is looking quite good with 80% matching on DVDs. But we are looking at raising that number every day.

Ok, right now you’re asking yourself, why is he boring me with all these numbers and stats? Is there nothing better on YouTube? The reason is simple: all this is only possible thanks to your contributions. The people who have taken the time and effort to submit good data and the moderators who are painstakingly and lovingly checking and fixing entries. And, of course, the great efforts of the wonderful people here at Bruji *self-pat on the back* and maybe a dog biscuit.

Seriously though, without user contribution we wouldn’t have a database for us to all benefit from. But, as the old adage goes, “garbage in, garbage out”. The data is only as good as the entries that are submitted. So if you’re submitting to the Doghouse, please try to have as good and reliable data as possible, this will benefit us all and make the database grow faster since our moderators will have a bit less work and can concentrate on more productive activities like watching Monty Python videos.

And if you search and can’t find something, be sure to contribute to the Doghouse after you’ve added it to your database (preferably including the UPC at the back of the item).

Interesting search facts:

One of the most failed search terms was “Pirates of the carribean”, notice the misspelling, it should be “Caribbean“. Also “Real Steal” which should be “Steel”. So if the movie you are searching for is not there, check your spelling and try again. If you’re not sure of the spelling and don’t have IMDb handy, try different spellings or just one word “Pirates”, though this might produce too many hits, in which case, try a combination of the title and a main actor (remember to set the search window limit to “None”): “pirates johnny depp”. Or title and director: “pirates gore” (for Gore Verbinski). Above all, have fun, don’t get frustrated, it’s a database not a mind-reading app (yet).

Speaking of fun, want to watch a movie with Johnny Depp in it and not sure which one? Use DVDpedia to get all the Johnny Depp movies (set the limit to Credits) and sort the search by Release. You’ll discover a bunch of movies you probably didn’t know or forgot existed. Or you’re a fan of David Cronenberg (and who isn’t)? Same deal: limit to Director, search “David Cronenberg” and start watching from your favourite source (if available).

Right, I’m off to the dungeon, have fun with the Pedias.

Hello, I am Alex. For the past year I’ve been chained in a dungeon, fed on a steady diet of MySQL and PHP. [Ed. note: We do let him out once a month to play with some CSS.] I built the Doghouse infrastructure with these technologies. Recently I started learning a wonderful and beautiful programming language called Ruby. If you have any interest whatsoever in programming languages I really recommend giving Ruby a try (more on that in a future post). I’m currently building the future Doghouse moderator section with Ruby On Rails. Working with Rails and the pedias I have had a chance to look more closely at SQLite, so I thought I’d write a short post about it.

I have a new-found respect for SQLite.

As you know, SQLite is the database ‘engine’ on which the pedias are based. It is Apple’s default database schema. Being a Mysql user for a number of years now I wasn’t really interested in this seemingly less capable new-comer. But, lately, I’ve had a lof of dealings with Ruby on Rails, whose default database engine is also SQLite and have found I really like it for certain tasks.

For those of you who are only interested in a Graphical Editor to be able to manipulate your pedia data, skip to the GUI tools below.

It’s not for every project, especially ones that require a lot of inserts or updates to your database. But it’s perfect for portable applications and web sites that rely mostly on reading and not writing data. I read somewhere that the rule of thumb is that if your database takes less than 100,000 (100K) hits a day, then you’re find going with SQLite. This is, obviously, a big simplification, but not a bad starting point.

It’s extremely easy to set up, lightweight and very portable since the whole database consists of a single file and there are no server connections to worry about. It offers all the simplicity of a file-driven database (Excel, CSV file, Access, etc) with none of the shortcomings, namely it allows for relational databases as well as constraints and triggers.

You can find lots of good articles on SQLite usability and Mysql vs SQLite comparisons so I won’t bother, instead I’ll write about the Graphical Tools you can use to manipulate these databases.

GUI Tools

So, on to the most important thing, the GUIs or Graphical Tools for manipulating SQLite databases. Sure, you can use the terminal and issue all sorts of weird commands to manipulate your databases, but c’mon, who doesn’t love a GUI?

Sequel Pro
http://www.sequelpro.com/
This is the best tool for Mysql databases on the planet, no exceptions. And it’s free. If you work with Mysql databases, do yourself a favour and get it now. And do us all a favour and donate something to the developers to keep them upgrading this wonderful product.
It doesn’t yet have support for SQLite but it’s in the works. I just had to include it here because it’s so great and I work with it everyday monitoring and tweaking Doghouse performance.

Navicat Premium
http://itunes.apple.com/us/app/navicat-premium-essentials/id466416967?mt=12
This is a capable but very pricey option. Though there is a sale of the Premium Essentials http://itunes.apple.com/us/app/navicat-premium-essentials/id466416967?mt=12 right now in the App Store.

It’s not that great-looking but will do pretty much anything you need and has the advantage of also tackling Mysql databases so if you use both Mysql and SQLite like we do, you only need one tool.

I don’t very much like the interface or the way the tables are laid out or the fact that each table opens up a new window, making for a very cluttered workspace.

Not my tool of choice, but capable.

Base
http://itunes.apple.com/us/app/base/id402383384?mt=12
This is a nice-looking SQLite manager. Much nicer-looking than Navicat, albeit with less features.

The interface is simple and very nice when reading database contents.
However, when editing the database such as adding or altering tables and fields I find the interface strange and confusing. Things are hidden away in weirdly-placed pop-up menus. Almost as if it was initially intended only for reading databases, then the editing features were bolted on as an afterthought and the developer wasn’t quite sure where to put them.

I like it a lot more than Navicat Premium Essentials but, unfortunately, it’s more expensive, almost three times the price (now that Navicat Premium Essentials is on sale).

I think this is a nice app, especially for reading data. If it were a little cheaper I’d buy it, but I’m not paying $30 for something I don’t fully like.

SQLVue
http://itunes.apple.com/us/app/sqlvue/id426397771?mt=12
They don’t have a demo version on the App Store (this really pisses me off) but if you hunt around the Internet you can find a 7-day demo on their site:

http://www.logicalvue.com//Download/SQLVueTrial.dmg

I think this tool is ok, capable and simple for SQLite databases. The problem is I tried their tool to convert a Mysql database to SQLite called SQLite Migrator and could not get it to work. The SQLite Migrator relies on Apple’s ODBC Administrator app (no longer supplied with OS X, but you can download it separately) and ODBC Administrator in itself is not simple to configure. You first have to find an ODBC driver (from where?) for the connection you want to use, then get that working inside ODBC Administrator, create a new connection with that driver and to the database of your choice, then go back to SQLite Migrator and try to connect to this new connection you’ve created in ODBC Administrator. If that works, then you can (I assume) start dealing with your migration issues.

I’m sorry, but for a tool that costs $50 I expect it to be a little easier to create a connection just to get started. The days of ODBC connectors on the desktop are long gone (there’s a reason Apple doesn’t include this tool anymore, because it was just gathering dust and taking up space in your hard drive) and this is a step in the wrong direction.

So this experience left a really bitter taste in my mouth so I can’t quite like their SQLVue product.

All these tools (except Sequel Pro) seem to suffer from opening a new window for every task. If you are looking at a table structure and want to edit it you get a new window just to add or modify a field. This makes it confusing and cluttered.
SQLite Database Browser
http://sourceforge.net/projects/sqlitebrowser/
Lastly, there is this tool. It’s free so that’s great news, but it’s buggy, crashy, hideous and hasn’t been update since 2008 as far as I can tell. It is a port of a Windows app and it shows.

For very ocassional use this tool might be OK. But if you want to deal with SQLite databases and keep your sanity, I’d recommend using one of the other alternatives.

Conclusion

Well, it’s tough to say who the winner really is. Until Sequel Pro brings out support for SQLite I think you’re better off going with Navicat Premium Essentials. It’s not great, but I think that for $10 (on sale) it’s probably your best option at this point.

I would also like to add that you can use these tools on the Pedias by pointing them to the database at ~/Library/Application Support/Xpedia/Database.Xpd (where X is book, cd, dvd or game). Sqlite is also the backend of choice for any large Core Data program (XML and binary are also options with Core Data) so it might be useful for any number of Mac programs out there based on Core Data.

Some of the Pedias’ printing templates add an automatic page break after a certain number of entries. Starting with version 5.0.2 of the programs, this page break can be used via CSS to style in a page number at the footer of the page.

To add a page number add the following CSS code to the template inside the <style></style> section at the top of the HTML code.

body {
counter-reset: page;
}
.pageBreak {
width: 100%;
text-align: right;
counter-increment: page;
}
.pageBreak:before {
content: counter(page) " ";
}

Of course you can change the formatting or font in the pageBreak property to fit the template that you’re modifying.

When you download information for an entry and no cover image is included (or you don’t like the one that came along), this is how you can search for alternatives.

Ctrl-click (right-click) anywhere in the cover image well at the bottom of the main window to bring up the contextual menu like this:

Then choose either “Download from” and select a site to search or choose “Open Google Images”. (In DVDpedia you will also see the option to “Open MoviePosterDB”.)

When you choose “Download from” and select a site, the program will search for a cover image on that particular site and download the first cover image returned.

When you choose “Open Google Images”, the web view will open and start a search on Google Images for the selected title. To make sure you get the correct image the search will also include the format or platform depending on the Pedia you’re using.

When the window with your search results appears, click on the image you want and then click on the ‘See Full Size Image’ link at the top of the window to see a larger version of the image.

Now ctrl-click (right-click) anywhere on that larger image and select ‘Make Cover’ from the contextual menu that appears to make it the cover of your selected entry. (Or if you’re impatient like Conor, just click and drag the image over into the image well.)

To finish, click the “Done” button at the top right of the web view.

The new statistics in 5.0 use a monochrome pallet of blues to blend in with the muted tones of Lion. A few have asked for the bright colors of before. Since our programs are based on a number of open technologies, in this case HTML and CSS to render the statistics, the colors can be customized with a little CSS knowledge.

Open the program’s package: ctrl-click the program icon for a contextual menu to find “Show Package Contents”, then navigate to the /Contents/Frameworks/Pediabase.framework/Resources/statistics.html. This file contains the base for building the final statistics view. Open the file in a text editor to change the colors (TextEdit requires the use of the ‘Open’ command to select the “Ignore Rich Text” option, otherwise it opens looking like a web page).

At the top of the file there is a section of CSS that defines the colors:

.vertgraph ul li {

     background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#A1B0CF), color-stop(100%,#788AB0));

}

.vertgraph ul .red {

     background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#788AB0), color-stop(100%,#4f648c));

}

.vertgraph ul .green {

    background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#4f648c), color-stop(100%,#263e6d));

}

.vertgraph ul .blue {

    background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#263e6d), color-stop(100%,#00184e));

}

The color classes are still named after the old color scheme. What you want to change here are the odd looking hex values, “#263e6d”. The first one on each line represents the top color and the bottom color of the bar. To help select new colors and create a gradient you can use ColorZilla’s gradient editor a handy website that will let you pick a color from a palette and produce the bit of code that you need to copy.

Click on one of the stops on the bottom of the color bar, then click on the color well to change the color. Once you have established the color you would like to use for a bar, copy the -webkit-gradient declaration from the code on the left, the highlighted section and replace the above color:

Repeat four times for each color bar and save the file. You are all set now with new eye-popping colors for your statistics.

For simplicity here are similar colors to what we used to have with version 3 and 4 of the Pedias, so you can copy paste them directly into the template.

.vertgraph ul li {

    background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#fcd83a), color-stop(100%,#febf04));

}

.vertgraph ul .red {

     background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#ef4747), color-stop(100%,#c91414));

}

.vertgraph ul .green {

     background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#139e0c), color-stop(100%,#075e0a));

}

.vertgraph ul .blue {

    background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#4c90ff), color-stop(100%,#1938ff));

}

If you do change any of the colors be sure to make a copy of the statistics.html file outside of the program bundle. Since the file resides within the program it gets updated when you download a new version. With a copy of the file, you can drop it in as a replacement to set your custom colors again.

This version mostly addresses smaller bug fixes that slipped past us with the initial 5.0 release (localization errors, fields not included in autofill) but also includes an important change to the new Amazon search sign up as you no longer need to enter an Amazon associate ID in version 5.0.1. If none is provided the program will enter a default Bruji ID.

For a full list of what’s new in this version, please refer to the release notes that appear when you use the ‘Check for Updates’ feature or go to the What’s New? page for each program: DVDpedia, Bookpedia, CDpedia and Gamepedia.

The new search options in version 5 of the Pedia programs now increasingly favour sites that provide media content instead of goods for sale. The advantage of this is that the data is largely user-contributed and open for sharing. However it also means that information like UPC and price is not necessarily included. We’re hoping Doghouse will bridge that gap in the future and provide the best of both worlds for all Pedia users.

Maybe you’ve already had a chance to check out Doghouse, the Pedias’ very own user-assisted online data repository which we’ve been building for the past year. Since the database is built exclusively with contributions from Pedia program users it is obviously still in its infant phase since we only just launched version 5 and even though our hard-working beta testers have been contributing data for months, there is still a lot of room to grow for Doghouse.

One of the major advantages of Doghouse is its international potential. Since users from all countries can contribute their data in their own language, this will create a truly global database with information from around the world for other users to search.

Doghouse returns results in the language your computer is set to, it would be overwhelming to receive multiple results in different languages. However if you are looking for an entry not in your language, Doghouse is smart enough to get you those results when the title matches nothing in your language. At the moment, non-English users will see results in their own language first, as well as matching results in other languages. Depending on your feedback and as the non-English contributions grow we will change that in the future. (That’s the beauty of it – we have full control over the Doghouse and its searches so we can set things to work in the best possible way for Pedia users. We’ve already tweaked our code in the last week in response to feedback and improved exact match returns for title and UPC searches.)

If you’d like to start sharing your own database with other Pedia users, begin by signing up for a username and password in the Doghouse menu > Settings. Contributions to the Doghouse appear anonymously but we do require a username/password combo in case someone starts uploading inappropriate or unrelated data it makes it easier for us cleanup.

Since your contributed data will be shared with other Pedia users we do ask that you only contribute those collections that would be useful to others, i.e. if you have a collection of home movies, please don’t send that to Doghouse since no one else will have any use for it. (Personal fields such as custom fields, location, comments, borrowed by, etc. are never included in the contributed data. Most fields found in the extras tab by default will fall in the personal category.)

If you’re interested in becoming a moderator for Doghouse to help us check entries for accuracy and quality, sign up here. We’ve also put together a little style guide for moderators which might also make an interesting read for anyone contributing data to get an idea what we’re looking for in Doghouse entries. Being a moderator lets you fix Doghouse entries when you spot a mistake; there is no commitment.

A big thank you to all the early adopters of version 5 who have already shared their data and especially those who’ve taken the extra two minutes to contribute an entry if they couldn’t find it on Doghouse. The next user looking for that entry will be pleased!

The search window is one of the features that has received a major make over with this new version, both in form and function. Let’s start by talking about the new UI a bit.

The biggest change for most users will probably be the way the search sites are accessed now. Instead of a drop-down menu at the bottom of the window, the search options are now integrated into the search field itself via the little magnifying glass. Click on it and you’ll be presented with the list of search sites.

From the screen shot you’ll also notice there are two new features connected to the search options: the option for recent searches (so you don’t have to retype titles or keywords) and the fact that only those sites you select in the Preferences/Sites show up in your list now so you can turn off any sites you’re not interested in seeing.
Previously, unchecked sites in the Preferences/Sites would not be included in the ‘Cascade’ search – which is now called ‘All’ – but they would still appear in the search site list.

The other big change is that the limiter menus at the bottom – Limit to, etc. – only show up when they are actually applicable now. So you always know what your search options are for a specific site. (This is also why when you choose the ‘All’ search no limiters appear since every site has different options. We might try and make the programs smarter about that in the future so that if one or more menu options match for the selected sites, they will appear for the ‘All’ search.)

The number of search sites has gone up considerably with this version. Each program now has access to at least 8 international search sites. This includes the Pedias’ own new database, Doghouse (we will write a separate and more in-depth blog post about that soon) as well as great open databases: Wikipedia and Freebase. There are also new program-specific sites: MusicBrainz and Discogs for CDpedia, BoardGameGeek for Gamepedia, OpenLibrary for Bookpedia, TMDb and the TVDB for DVDpedia. (And of course Amazon is still an option as well. Learn more about that in this post .)

Some of these new sites offer special search options geared towards their strength. We will dive into details for each site in subsequent posts, but for example MusicBrainz in CDpedia offers a great search by album title but no search by artist yet; we hope to include that in the future. OpenLibrary in Bookpedia lets you search using ISBNs (as the limiter says) but secretly if you enter the exact title of a book, including correct capitalization and punctuation, it can also perform a title search. Freebase has a very broad range of information for all media and offers search limiters by title as well as director/actor and you can distinguish between searching movies or TV shows.

Searching for TV episode information on TVRage or TVDB, you can open an ‘add manual’ window (Command-Shift-F), enter information into the Series, Season and Episode fields and use the window’s gear button to get a specific episode from either one of those sites. Alternatively you can also search for TV episodes by entering the series followed by the season and episode into the search field, like this:

This search will return: "Dexter" Season 2, Episode 3

Spend some time with these new sites to get to know their strengths and match which ones work best for you and your collection. Many of these new sites work better with keyword searches than UPCs so give that a try as well.

A few users have reported trouble with the new Amazon sign up process. It’s explained in the Help files but maybe we didn’t include enough details. While we remedy that, here’s an illustrated walk-through for getting your Access and Secret key as well as an Associate ID. (For a larger version of each image below, click on it.)

Start by going to the AWS (Amazon Web Services) website.

If you already have an Amazon account (the email and password you use to purchase goods on Amazon), use that same account to sign in here. If you don’t have an Amazon account yet, sign up for one by selecting the ‘I’m a new user’ option. You’ll be taken to another page where you have to enter a few personal details and choose a password.

The next page you’ll see should be the AWS homepage. (If you land on a payment page instead, please take a look at this section before proceeding.)

Scrolling down the page a little, you’ll see an area called “Access Credentials”, about half-way down the page. That is where you get your Access key and Secret key. The Access Key is listed under the ‘Access Key ID’ header. To see your Secret Key, click on the ‘Show’ link.

Now armed with your two keys, open the Amazon Settings window in your Pedia program (click on the Amazon Settings button in the Preferences/Sites). Copy paste the two keys into the appropriate fields of the Amazon Settings window like this:

For the third field, the Associate Tag, either follow the steps below to sign up for your own associate ID or use the default Bruji one provided by the program when you leave the field blank.

(Whenever you click on a link in the Pedias that takes you back to Amazon it has that associate ID attached to it. The commission for any sales generated during that ‘session’ on the Amazon website gets paid to the ID attached. This can happen through direct links in the program or if you export your database to the web and someone clicks on your link to Amazon.)

To sign up for your own free associate account, open the Amazon Affiliate Program website, which looks like this:

Click on the ‘Join now for free’ button on the right hand side to create an associate account. Sign up with an email address and password and you’ll be taken to the next page where you enter payment information so Amazon can send you your commission.

On the next page, Amazon will ask a few questions about your website since this service is mainly used by people advertising Amazon links on their site. If you don’t have a personal website a Twitter account will do too.

Enter the information as needed and press the ‘Finish’ button. On the next page you’ll be presented with your Associate ID and if you choose you may also enter your payment method at that point (how Amazon should pay you the commission).

Now return to your Pedia program and copy paste that Associate ID into the Amazon Settings window of the Preferences, click the Sign Up button and the Amazon sites will appear in your search sites of the Preferences for you to arrange and turn on or off as you like.

—-

AWS provides many different services, including the free search that the Pedias let you access. Some other AWS services are paid. The AWS search feature is 100% FREE, regardless of how many searches a day you do, but since it is part of the greater AWS feature, Amazon may ask for your credit card information. In that case you will see the following page asking for credit card information during the sign up process:

The response to the new version 5 has been incredible awesome. We are thrilled that users are sharing our excitement and showing it in the Mac App Store to boot. Three of our apps have taken 1, 2, and 4 top paid app spot under reference category:

For the all encompassing global category we are at 64 (DVDpedia) and 78 (Bookpedia). There is no competing with Angry Birds, not even on the Mac. The screenshot below is 5 minutes old and we are still gaining

Some users noticed that the About Box from the Mac App Store version number is 4.9.3, just a display glitch (It’s actually the 5.0 you have) and will be fixed in version 5.0.1 that we are preparing right now with a few tiny fixes.

Don’t let me distract you though. Continue your shopping spree, let’s see if we can we break the top 50 paid apps!