onfocus

So my "little" project of going off the Flickr grid is getting exponentially more complex. And it's definitely taking a little longer than a couple Saturdays. But that's the way these things go. Exporting everything from Flickr took a week or so, but that was only the beginning. After planning for thumbnails and resizing all of the images, my next dilemma was how to handle telling my site that I'm me.

In the past, I've kept the public site and administrative functions separate. So when I needed to create a gallery, title photos, or add captions I'd go to a completely separate web application that existed in a separate folder on the web server. With very basic HTTP-Authentication, I could be sure that I was the only one with access to that section. What I like about Flickr is that I can edit in place—which means that if I see a photo title that's a bit off, I can simply click and edit that title. And my editing interface is pretty much the same interface that anyone else sees. I'd like to be able to edit in place with my photos too, and that means combining administrative and display functions into one seamless application. Unfortunately, that means my basic HTTP-Authentication is out as an option, because it's all or nothing with that scheme. Everyone visiting the site would have to log in via a user/pass prompt, and that just doesn't make sense.

I thought about limiting administrative functions by IP Address, because I'm at the same IP 90% of the time. It would mean a hell of a lot less code to write. But I know I'm going to want to upload/edit photos on the road from random IP addresses. So that leaves one option: standard database authentication.

(Ok, another option would be authenticating with a Yahoo!, Flickr, or Google account, but the point of my project is to get away from relying on the big guys.)

So at this point my application only needs two types of users: anonymous guests who can view photos and a known administrator (me!) who can also add/edit/delete photos. That means I'm going to set up a user database with only one user in it. And I'll have to write a bunch of authentication code that only I will use. It's frustrating, but I think the work will be worth the convenience down the road. Plus, if I ever decide to have "users" with different levels of access (like, say, family members can log in to see family-only photos), the structure will all be there to make it happen fairly quickly.

Here's the users table I set up: otfg_tables_5.txt. I won't bore you with the gory details, but CookieID, LoginKey, and LoginExp will help create a persistent login system so that if I have the right cookie set I won't need to log in each time I visit the application. And because I'm doing some encryption voodoo on the password, cookieID, and everything else, I set up a script to add the administrative user to the db: addAdmin.php. If you want to try this out, add your username and password to addAdmin.php, call the script from a browser, and then remove/rename the file. The password is stored as a one-way hash, so you won't be able to get it back again. If you ever forget your password, you can always delete the existing user via MySQL and re-run this script with a new password. Once the admin user is added, remove this file from the public web.

You might notice at the top of addAdmin.php, there's a file called ini.php included with require("ini.inc");. Since I'm about to write a blizzard of files for my application, I decided to put some application settings in a separate file and include them on every page. Here's what it looks like: ini.php. It's probably a good idea to store this file outside of your public web directory if you can, because it has a bunch of private info.

With the user added, I just needed a way to identify myself within the application. That's what these four files do:

• login.html - just a simple HTML form to set a username/password.
• login-action.php - accepts the username/password, matches it against the db, and sets a cookie and session variable for administrative privileges if there's a match.
• logout.php - destroys the current session and overwrites the cookie.
• auth.inc - the function here will set the proper session variable if an administrative cookie is present, this enables persistent logins.
• login-status.php - this script just shows the current login status. I used it for testing.

I'm the only one who's going to be using these scripts (for now), so they're very bare-bones. It's not giving back friendly error messages when I can't log in, but I don't mind. With this infrastructure in place, my site will be able to pick me out of a crowd. And I can get back to thinking about how the pages will look.

Next Up: Getting new photos into the system.

The next step in going off the grid to host my own photos was resizing my images for display. My initial import script downloaded just my original photos that I uploaded to Flickr. But when you upload a photo to Flickr, the service creates four (sometimes five) copies of the original image at different sizes. This way Flickr can show various thumbnails of images in different ways, and they can use a standard size to display a photo on its detail page. You can click the "All Sizes" button above any photo at Flickr to see all of the sizes available for that particular photo.

I needed to do something similar, and I'm not sure exactly how I want to display my photos yet. So I decided to use Flickr's default image sizes (for the most part), to give me some different sizes to play around with. I went with the following sizes:

• Medium Thumbnail - 240 pixels max width or height. Flickr uses this size for their photostream pages.
• Tiny Thumbnail - 100 pixels max width or height. Flickr uses this size for JavaScript syndication (Flickr Badges).
• Square Thumbnail - 85 pixels square, cropped from the center of the original image. Flickr uses a 75 pixel square thumbnail on the member home page and in back/next links on photo detail pages.

Flickr has a nice naming convention for these different sizes. They use [FlickrID]_[Thumbnail code].jpg to denote the various sizes. So a square thumbnail in their system will have a name like, 359119647_4874f02815_s.jpg, and the same photo in a bit larger size would be 359119647_4874f02815_m.jpg. I went a similar route, but decided to separate the thumbnails from the original photos. By placing the thumbnails in a different root directory, I can stop search engines from indexing the copies with a well-crafted robots.txt file. That means any photo that is syndicated out through Google Images, Yahoo! Images, etc. will be the original photo I want to share.

And because the thumbnail URLs won't be thrown around in the wild, I decided to use a naming system that doesn't give any information about the file itself. I thought that if I could assemble a thumbnail name from limited information (just the PhotoID) that could minimize the amount of stuff I have to pull out of the db. But I don't want to expose PhotoIDs through the system—that could let someone look at photos that aren't meant for them by guessing its ID in a series. So I went with an MD5 Hash of the PhotoID, plus a string that's unique to my application. That should obscure my IDs to all but the most determined cryptographers.

Beyond the thumbnails, I also wanted to set a maximum photo size for the original photo. I'm not sure what the design will look like yet, but I know that I want a standard size to work with in designing the pages. That means I could a.) make sure to resize all photos to the maximum size before I upload it into the system, or b.) automatically resize any original that's larger than my max. I went with b to make my uploading life easier, and to scale down any large photos I might have uploaded to Flickr. (I tried to go with smaller sizes at Flickr, for the most part.) In this script, if an original photo is too large the original file is copied to [name]_o.jpg in the /photos directory, and then the resized photo is saved to the "original" filename: [name].jpg. I know this system isn't perfect, and I have a feeling this is going to cause problems down the road, but hey, what can you do? I think it'll work.

Here's the script I used to resize all of my images: resize-all-photos.php. And here's the unique information that you'll need to set at the top of the resizing script if you've been following along:

• A PHOTO_MAX_WIDTH and PHOTO_MAX_HEIGHT for the maximum file size you want to display. (I went with 850 x 640.)
• A PHOTO_QUALITY that will be used in PHP image functions. (I went with 95, but you can play with this up or down to change the filesizes and image quality.)
• A SALT that's used for uniquely naming thumbnails on your system. (I used—wait a minute, you almost got me. This should be a string of 8-x characters that only you know about.)
• The full path of the /photo directory set in the original import script, along with a new /thumbs directory at the root path of the site.
• And, of course, your MySQL details.
• The thumb sizes are hard-coded, but it should be fairly clear where to change them if you'd like different sizes.

With all of this set, I ran the script and generated a bunch of thumbnails. To get a sense of the sizes available and the file names/locations, check out this page. As I mentioned, this is very close to Flickr's standard sizes and I can always re-run this script with new sizes if I need something different for the final design.

And to help keep my eye on the goal, I set up a couple different pages with some ideas for displaying photos. Here's one that's very Flickrish, with the latest photo large, followed by smaller photos: onfocus photos preview. And here's another page with the latest photo up-front, and older photos as square thumbnails: onfocus photos preview 2. Getting closer!

Last night I was working on the "Resizing Images" code I hoped to post today, and realized that I missed an important bit of data all the way back in Step 4 in my original import photos script. I forgot to get Rotation information about each photo. Flickr lets you rotate a photo after you've uploaded it, which is especially handy for cell phone images that aren't easy to rotate before you upload. Because I grabbed all of my original photos, I got the non-rotated versions. Luckily, the Flickr API lets you know how many degrees you rotated a photo so I just needed to whip up a script to grab that info and actually rotate my non-rotated originals.

The first thing I did was add a Rotation field to the photos table. That looks something like this:

mysql> ALTER TABLE photos ADD COLUMN Rotation INT NOT NULL;

And here's the script I threw together to rotate any images that needed it: rotate-any-photos.php. One thing to note is that Flickr's rotation is clockwise, and PHP's imagerotate() function rotates counterclockwise. So I needed to make the Flickr degrees negative to compensate. I also copied the original file (using Flickr's _o naming convention), and then saved the rotated image to the original file name. Managing filenames is turning into a pain, but I think this will work ok.

This script rotated 66 photos for me. Now, up next (hopefully): resizing images.

Welcome back to This Old Blog. This week we're going off the Flickr grid by setting up a custom photo sharing application at a private web site. (this one!)

Flickr has a fantastic feature called notes that lets people add a layer of information on top of a photo. Here's one of my photos with notes so you can see it in action: moon 8/31. As you hover over the image, you can see boxes I've drawn, with notes underneath. It's great for pointing out some little detail in a photo that might otherwise be missed. Matt's memory maps idea is another great example of notes in action. Check out the memory maps tag at Flickr to see hundreds more.

I don't plan on adding notes to my photos here. To me, notes is one of those magical features that just happens—I have no idea how it works. I don't even know where to begin building it. But that doesn't mean I won't want to build it someday, so I figured I might as well throw my existing notes on photos into a database in case I want to recreate them down the road.

To accomplish this, I took a look at the notes data available through the Flickr API and mapped everything to a local table, notes. Here's the structure: otfg_tables_4.txt. And I set up another PHP script to do the work: import-flickr-notes.php. If you've been following along with the previous scripts, you know the drill on this one: runs in the browser, authenticates at Flickr, add your details to the top, might set your house on fire, etc.

This script pulled in 48 notes for me across 19 photos.

Ok, after importing notes I have every bit of information I could possibly want from Flickr residing on my server. That means it's time to start thinking about how I want to show my photos.

Next Up: Drink Me (Resizing Photos)

The next question I asked myself in my Flickr move: should I take comments with me? Flickr is built for having conversations around photos. Every photo entered into Flickr is also a place for discussion, and those places never close. As you add more photos into Flickr, there are more and more opportunities for discussion to monitor. I'm always surprised when a photo I uploaded in 2004 has a new comment on it, and I frequently miss comments on older photos because I don't check my "recent activity" page enough. But casual conversation about photos is what makes Flickr so much fun. Sometimes the comments are better than the photo people are discussing, and often photos are posted just because they'll generate comments.

There was no question in my mind that photos, titles, captions, and tags are mine, and I have no trouble taking them away from Flickr and sharing them somewhere else. But comments exist in gray area for me. The people commenting on my photos expected their comments to be at Flickr and nowhere else. At the same time, the comment wouldn't exist without my photo prompting it. Another complication is that Flickr doesn't expose comments via their API. I'm not sure why, but it could be for the very reason that people expect their comments to be at Flickr and nowhere else. (Though photos seem far more personal to me and they're available through the API unless someone specifically requests that they're not.)

I decided to compromise by downloading the comments but not displaying them on my site. That might seem odd, but I'd like to be able to go back in a few years time and see comments that people I know made on my photos. (Even those silly "please add this photo to the chickens being used as phones group" might be fun to read again someday.) If my Flickr Pro account lapses, that won't be possible. Or if I move off the grid, that won't be possible. So I downloaded the comments on my photos via screen scraping, and I'll keep them in a database for my private use. When I start my photoblog here at onfocus.com, I'm going to want comments, so I'll use the same table but flag the Flickr comments as unique (not for display).

Here's the table I set up for comments: otfg_tables_3.txt. The FlickrID field notes the internal Flickr ID of comments I scraped from the site. There won't be any entries in the IP field for Flickr comments, but this will eventually hold the IP Address of people who add comments here. (I figured I might as well include that now.)

And here's the script I used to import comments: import-flickr-comments.php. This is how it works:

• Grabs the FlickrIDs of photos in the photos table, and starts looping through.
• Logs in at Flickr (old skool only) and sets a local cookie for subsequent requests.
• Grabs the photo detail page of that particular photo.
• Picks through the HTML to find the comments.
• Calculates the approximate date of the comment.
• Throws the comment, username, Flickr profile URL, and date into the comments table.
• Rests for one second.

Figuring out a comment date isn't an exact science. Flickr doesn't include an exact date/time for comments, instead they use a friendly format such as "30 days ago" or "2 hours ago". This script takes the current time the script is running, and then uses the PHP date_modify function to come up with an approximate date/time. It won't be close, but it should come up with the right month and year for that particular comment.

If you want to try this out you'll need to add your photostream URL and your Flickr username (most likely your email address) and password. The script logs in as you so it can grab comments on photos that are marked as friends/family only. But it's important to note that this login only works for pre-Yahoo!-acquisition Flickr members. I have a so-called old skool ID rather than a Yahoo! ID, so I didn't need to log in via Yahoo! If you want to run this under a Yahoo! ID, you'll need to fiddle around with the login stuff around line 17. As with the other scripts I've posted, this runs in your browser.

After this script ran, I had 260 comments in a local table. Even though they might never see the light of day again, at least I have a record of what friends and random Flickr folks said about my photos.

Coming up: A note about notes.

Grabbing all of the photos and their extended info is only the first step to going off the Flickr grid. I also have some information about how photos are related to each other that I need to grab. Flickr calls groups of photos sets, and they're simply a number of photos from your pool of photos that are displayed together. Here's one of my sets at Flickr: New Zealand 2006.

On this site (pre-Flickr) I grouped photos into two distinct areas: galleries and my photoblog. Like a Flickr set, a gallery is a group of photos that are related in some way, and my photoblog was for everything else. So the photoblog was a single, large gallery of photos that were only related by the fact that they weren't part of an existing gallery. (Don't worry, this isn't a paradox. Yet.)

Instead of two distinct areas, Flickr says you have one big set of all your photos—your Flickr photostream (much like my photoblog)—and from there you can have sub-sets, or simply sets. I think this is a much more intuitive approach for photographers. I can look at the entire pool of photos I've taken, and then create associations from that pool at will. Even though this makes sense for the photographer, I'm not sure this is the best approach for viewing photos. (I'll discuss this more later when I'm building the Web side of this project.)

Anyway, I want to save these photo associations I've already put together at Flickr. I set up two tables for this task: sets and setphotos. Every set has a title, description, and FlickrID (just like photos). The Flickr API doesn't provide the date a set was created, but I added a DateCreated field anyway because this is something I'll want to track eventually. I can either go back and guess on the dates my Flickr sets were created or base the DateCreated on the photos within the set somehow. The setphotos table is simply a list of local photoIDs associated with a local SetID. The IsPrimary field lets you flag one photo to represent the whole set—required when you create a Flickr set. And I added Order and DateAdded fields for later use. Flickr lets you order the photos arbitrarily within a set, but doesn't expose the ordering system via the API. I'll probably have to do that by hand later.

You can grab the SQL required to create the set tables here: otfg_tables_2.txt. And you can add it to a MySQL database like this:

shell> mysql -u [username] -p [password] [database name] < otfg_tables_2.txt

Ok, with the data set to be structured, I just needed to reach out and grab it. Once again, here's the covered wagon script I threw together for this: import-flickr-sets.php.

Here's what the script does:

• Authenticates the person running the script at Flickr via the browser. (I don't think this is technically required for this step because sets are public, but I had the auth code from the other script so why not?)
• Requests all of the account's sets, and adds the FlickrID, title, and description of each to the sets table.
• Then the script grabs the list of photos in the set, finds the local photoID for that photo, and associates it with the set in the photosets table.
• Finally, a 1 second pause for good API behavior.

Shew! Don't forget to add your local details to the top of the script if you're going to try this out, and then run the script from your browser.

It only took a few seconds to grab all of my set info from Flickr. I had six sets with a total of 88 photos.

One caveat: if you're going to try this out and you have sets with huge numbers of photos in them, say, 100+, this script probably won't work for you. There's no paging going on through the results that come back from the API. You'll need to dig into paging a bit to get it to work. And anyway, who can look through a single set with hundreds of photos in it?

That's almost all of the information I need to start displaying my photos. But first, the trouble with comments up next, ugh.

At long last here's the import script I used: import-flickr-photos.php.

If you want to try it out, be sure to add all of your personalized information I've mentioned in the previous steps (1,2,3) to the top of the script.

Here's what the script does:

• Authenticates the person running the script at Flickr via the browser. (You'll have to give your script permission to read all of your photos.)
• Requests the total number of photos for the authenticated account from the Flickr API (to help with looping).
• Requests all of the account's photos, asking for standard info (title, FlickrID, whether or not it's public) and some extra details (relevant dates, and the longitude and latitude).
• Loops through every photo, adding the photo information to the database if it isn't already there.
• Downloads the original photo file from Flickr and saves it locally if it isn't already there.
• Requests the description (caption) and tags for the particular photo from the Flickr API. (Requires a separate API call, unfortunately.)
• Adds the file location, description, and tags to the db.
• Finally, the script sleeps for one second before doing anything else. (Seemed like the polite thing to do so the script doesn't hammer the API.)

In my last post I mentioned that Flickr Backup was clunky to use, but this script is a thousand times clunkier. If Flickr Backup is a Ford Taurus, then running this script is like taking your covered wagon out on the Oregon trail: no shocks, no rubber tires, no paved roads, and you'll likely die of cholera before it's finished. I'm kidding on that last part, but the script probably will die before all of your photos are saved locally. Not to worry, you can run the script multiple times without duplicating files or data. The script checks for existing records and files before taking any action. I have 513 photos at Flickr—which isn't too many in the scheme of things—and I still needed to run the script a couple times to get all of them. (I set a ridiculously high timeout at the top of the script, but the script seemed to die anyway.)

To run the script, open it in your browser. The URL should be something like:

http://example.com/import-flickr-photos.php

The script will redirect you to Flickr where you'll need to log in and/or authorize the script. From there, the magic starts. The script will try to give you some info about what's happening, but if you don't see anything but a blank page, don't worry, it's probably working. If you can, log into your server and check out the photos directory you set up. You should see folders and files appearing. Another way to check progress is by firing up MySQL and running some counts on your table. Something like this:

SELECT Count(PhotoID) FROM photos

If the script is working the count will be higher than zero.

It's important to note that the import script is grabbing every photo you uploaded to Flickr, even those marked as friends and family only. This is exactly what I wanted to happen, but if you want something different, check out the documentation for the flickr.photos.search method and tweak line 65 of the script. You can set a privacy_filter argument in the call to get a list of only photos that are public, for example.

So, once this script finished, I had a bunch of local directories filled with photos that I'd uploaded to Flickr over the past three years. I also had 513 records in the photos table and 1,646 records in the tags table describing those photos. That means I (and some others) added about 3.2 tags per photo. huh. So I can't really look at my photos through the Web yet, but at least they're ready for the next phase. Not too shabby for a few hours on a Saturday afternoon.

Disclaimer: As I mentioned before, OTFG is an off-the-top-of-my-head project. So if you try any of this stuff out, please don't hold me responsible for your toaster catching on fire. I'm sharing this project publicly to show how I'm going off the Flickr grid, and to hopefully get some feedback in the process.

Next Up: Set Theory

One feature that put Flickr ahead of the existing photo-sharing pack a few years ago was the fantastic Flickr API. The API gives developers the chance to tap into the Flickr photo database and create stunning visualizations, fun toys, and productivity hacks that extend the service. Hosting my own images means I won't be able to take advantage of these Flickr-specific tools built (for the most part) by fans of the service. Loosing access to these tools is one of the biggest drawbacks to going off the grid. Even my ability to leave Flickr with my photos hinges on the existence of the API. People can use the API to export their photos if they're not happy with the service, or if they want to share their photos in different way.

But exporting your photos isn't just a matter of pushing an "export" button. You have to have a certain amount of technical expertise to be able to export your photos from Flickr. There is one existing tool I know of that can grab all of your photos—Flickr Backup—but in my experience it's a bit clunky to use, and doesn't snag all of my data like descriptions and tags. (Sounds like it's getting better since I tried it, though, based on posts in the FlickrBackup Open Discussion.) So while it's possible to get your photos out of Flickr, it's not easy for most of the world. I consider myself familiar with Flickr and its API, but it still took a few hours to write a script to grab my photos, titles, captions, and tags.

The key to gathering my photos was logging in as myself through the Flickr API. (Which is about as easy as it sounds.) Luckily most of the heavy lifting is handled through phpFlickr—a swiss-army-knife for working with the Flickr API in PHP. If you want to try this at home you'll need to download and install phpFlickr on your server. Make sure the main phpFlickr file is in your public working directory, along with the file auth.php. This little file helps handle Flickr authentication.

Note the URL of auth.php on your server, it should be something like:

http://example.com/auth.php

Flickr controls access to their API through keys, and I needed one for my import script. With my auth URL in hand, I headed over to the Flickr API and applied for a key. I quickly described the app, noted that it was for non-commercial use, and agreed to the Terms of Use. In exchange, I got a couple of alphanumeric strings that let me use the browser to log in via the Flickr API.

I was instantaneously approved for the key, and I clicked on the Your API Keys link and found the key I just made on the list. I clicked Edit key details, gave the import script a quick title and description, and placed my auth.php URL in the Callback URL field. Then I clicked "Save Changes" to finish the setup.

I found my new key once again on the list of keys and copied both my Key, and my Secret—which is a smaller alphanumeric string that shouldn't be shared with others (as the name implies). You'll need to jump through these hoops to register your import script as well if you're following along.

To recap the progress so far, these items were in my magic bag of holding before any files were transferred:

• A MySQL username, password, and database name, with empty photos and tags tables.
• A local filesystem directory where photos will be stored.
• A Flickr API Application Key and Secret.

It takes a bit of work to put the pieces in place, but once this groundwork is done, importing the photos from Flickr can begin. That's up next.

My next step in moving my photos from Flickr to my own server was thinking about where I would store the photo files. Flickr assigns every photo a numeric ID which is available in every photo URL. For example, here's the URL of one of my photos hosted on Flickr:

http://farm1.static.flickr.com/124/359119647_4874f02815_o.jpg

The URL doesn't give much information about the photo. We know that the photo is at a flickr.com server, and that the photo is a user's original photo (note the _o at the end of the filename). Other than that, pretty anonymous.

Since I'm hosting my own photos, I thought I'd put a bit more information into the photo URLs. I decided to go with this format for original, unresized images:

http://example.com/[year]/[month]/[photo title].jpg

This means the same photo on my server will have a URL like this:

http://example.com/2007/01/beach-dogs.jpg

Though I don't expect my photo URLs to be exposed in the wild very much, I like this structure because it provides a bit of context. And because I'll be using actual directories in the filesystem named /2007 and /01, for example, the filesystem should scale well. I won't have hundreds and hundreds of photos in one folder. On the other hand, it will make running batch operations on all of the photos a bit tougher because I'll have to recurse through the directories—but that shouldn't be a big deal. (Especially since all of the file locations will be stored in the db.)

The Flickr API provides the date and time a photo was added to their system in Unix time, and the PHP date() function converts that to any format. So as my import script grabs photos from the Flickr server, it puts the image in the local filesystem based on the time it was added to Flickr originally.

I simply set a starting directory in my import script that's available through the web server, say, /www/photos/ or c:\\www\\photos\\ in Windows, and it will create the necessary local directories as it pulls in photos from Flickr.

Using the title of a photo as the file title is a bit tricky, because the titles are meant to be read by humans, not used in the filesystem. Photo titles contain punctuation and spaces, so I just strip all of that out with some regular expressions. I'm sure this could be improved, but I'm using:

$photoTitle_f = preg_replace('/\s+/', '-', $photoTitle_f);
$photoTitle_f = preg_replace('/[^-\w]/', '', $photoTitle_f);

Basically this bit of code says replace any whitespace in the title with a dash, and then remove any character that isn't a dash or isn't standard letters and numbers. A bit rough, but it should handle most standard English titles.

With the photo-URL planning out of the way, it was time to set up Flickr API access for my import script. I'll show how that works in Step 3.

This weekend I took my first step toward going off the Flickr grid (aka OTFG). I set up a database to store information about my photos, I downloaded all of my original photos from the Flickr servers, and used the Flickr API to gather information about those photos. This first step is key for me because I don't want to loose the years of work I've already put in to adding titles, captions, and tags to my photos at Flickr. Luckily, Flickr has a fantastic API that lets you tap into their photo database.

If you want to follow along at home, my setup includes PHP 5 and MySQL 5.

I started by whipping up a quick database structure to hold info about photos. I'm sure this will change over time, but this is what I felt was the bare minimum I needed to get back out of Flickr. I made two tables: one for photos and one for tags. The table photos includes fields for a PhotoID, title, description, date the photo was added, date the photo was taken, longitude and latitude of the location (if available), whether or not the photo is public, and the location of the photo on the local filesystem. (I decided to store the files in the local filesystem instead of in the database because that seemed more intuitive to me.) The table tags will store all of the tags associated with the photos.

You can grab the SQL required to create the tables here: otfg_tables_1.txt.

And you can set them up in a new database like this:

shell> mysql -u [username] -p [password] [database name] < otfg_tables_1.txt

Next you'll want to set up a user to access this database. Fire up MySQL and run something like this:

mysql> grant all on *.* to [username]@localhost identified by '[password]';

Remember the MySQL username/password you set, you'll need them in a bit.

And with that, the stage is set and ready to be filled with photos! Coming up: How I grabbed my photos and put stuff in the db.

Disclaimer: Please be aware that I'm building this as I go. That means the code I'm sharing hasn't even been tested in the real world. I'm merely showing the steps I'm taking as a guide (and hopefully for some input). In other words, don't try this at home unless you're comfortable with what's going on here.

When I started this site in 1998, one of the first things I posted was a set of pictures I took on a walk through Downtown Lincoln, Nebraska. The gallery is a bit clunky (both the photos and the design), but I put it together by hand and it worked. Over the years I've posted numerous galleries here, as you can see on my photos page. In 2003 I set up a way to automatically publish pictures from my cell phone to the web: Mophos Moblog. (It still has my old design.) In 2004 I set up a separate photoblog (2004 archive, 2005 archive) where I could easily post any photo without interrupting my text blog or without having enough photos for a full gallery of related pictures.

Even though I have all of these home-grown tools for posting photos, almost all of my photo activity here at onfocus.com stopped in 2006. (The last photo on my photoblog is from July 3rd, 2006. And the last gallery I posted is from February 27th, 2006.) Part of the problem is that I'm simply not taking as many photos these days. And the other problem is that I'm using the fantastic photo-sharing application Flickr (my Flickr photostream). Every photo that I want to share online goes directly to Flickr where I know it will be seen by my Flickr pals. And if someone isn't yet a Flickr pal and I'd like them to see a photo or two, I just send them to my Flickr photostream. I love Flickr so much that I even wrote half of a book about all the cool stuff you can do with it called Flickr Hacks. My online photo life has moved entirely over to Flickr.

Unfortunately, my inner geek isn't completely thrilled with my move to Flickr. As much as I believe Flickr is a revolutionary application, a part of me is sad to see onfocus.com go without photos. And another part of me thinks that all of the awesome stuff that Flickr enables (community, conversation, collaboration, cataloging, aggregation, and so much more) should be done in a distributed way across the Web. The Web geek in me feels that photo sharing shouldn't be owned by any one company, and photos themselves should ultimately be under the control of individual photographers.

I know this vision of distributed photo-sharing doesn't seem realistic right now, but it is happening. Photobloggers post across servers and domains with widely varying software and somehow aggregators are able to pull their photos together in unique ways thanks to standard feed formats. (I still use blogging software I wrote myself, yet I can join in the larger blogosphere because any news reader can pick up my feeds.) Of course enabling ad-hoc groups is impossible without a centralized application—and identity management/access control (photos for friends/family only) is next to impossible in a distributed fashion. But I believe the tools will get there. And I'd like to start living in my distributed-photo-utopia once again.

I realize that not everyone has the means and ability to manage their own server space. But as a do-it-yourself Web guy I have both, and I'd like to get back ultimate control over my photos. Over the next few weeks (months?) I'm going to re-write my personal photoblogging software from scratch. My first task will be to gather the 500+ photos I've already uploaded to Flickr, because their API makes it possible to export my photos. I'm hoping to document my progress along the way, in case my steps can help anyone else out there who wants to go the DIY route. Going off the grid (so to speak) won't be easy, but I just need to remember that I've been there before.

Progress so far:

• OTFG Step 1: Setting the Stage
• OTFG Step 2: Thinking about Photo URLs
• OTFG Step 3: Registering the Import Script
• OTFG Step 4: Running the Import Script
• OTFG Step 5: Setting Up Sets
• OTFG Step 6: The Trouble With Comments
• OTFG Step 7: Importing Notes
• OTFG: Woops, Rotating Images
• OTFG Step 8: Resizing Images
• OTFG Step 9: Authentication
• OTFG Step 10: Adding Photos
• OTFG Step 11: Displaying and Editing Photos