I gotta have my orange juice.

Jesu, Juva

Archive for December 2008

PostgreSQL foreign keys and indexes

with one comment

[PostgreSQL]If you’re a frequent user of MySQL, you may be familiar with the fact that all MySQL table constraints automatically create indexes for you.  This is true of the InnoDB foreign key constraints, for which “an index is created on the referencing table automatically if it does not exist.”

If you’re switching or considering a switch to PostgreSQL, you should be aware that not all PostgreSQL table constraints will automatically create indexes for for you.  In PostgreSQL, a UNIQUE or PRIMARY KEY constraint on one or more fields will implicitly create an index for you.  However, in PostgreSQL a FOREIGN KEY constraint will not automatically create an index for you.

For each of your foreign key constraints, you should evaluate whether you want to create an index.  You may want to do this for optimizing your own queries, but be aware that it can also help to speed up DELETE queries on the referenced table and UPDATE queries on the referenced field.  This is because any foreign key reference must be located to enforce whatever ON DELETE and ON UPDATE behavior is in effect for the constraint.

Written by Scott Moonen

December 19, 2008 at 11:15 am

SmugMug uploader

with 8 comments

[SmugMug]I’ve written a small Python script to upload pictures to a SmugMug gallery. I love SmugMug and use it extensively for family photos. I’m using this script for my personal use because it’s much simpler and much less of a resource hog than a browser-based uploader, and also because it was a fun exercise to try out the SmugMug API. You can run this script as follows to upload one or more files:

python upload.py gallery-name picture-file-name . . .

On Windows I’ve set up a desktop shortcut pointing to the script, and I can drag and drop a pile of picture files onto the icon and it will upload away. I’ve tested it using both Python 2.5 using simplejson, and also using Python 2.6 which has simplejson built in. Earlier versions of Python may require you to change the import of hashlib to md5, and change the hashlib.md5() invocation to a md5.new() invocation. You’ll also need to modify the script to contain your email address and SmugMug password, and obtain a SmugMug API key for your own development use, but this is a very painless process. Here is the script:

#!/usr/bin/python

##########
# Requirements: Python 2.6 or
#               simplejson from http://pypi.python.org/pypi/simplejson
##########

EMAIL='...'
PASSWORD='...'

##########
APIKEY='...'
API_VERSION='1.2.2'
API_URL='https://api.smugmug.com/services/api/json/1.2.2/'
UPLOAD_URL='http://upload.smugmug.com/photos/xmlrawadd.mg'

import sys, re, urllib, urllib2, urlparse, hashlib, traceback, os.path
try    : import json
except : import simplejson as json

if len(sys.argv) < 3 :
  print 'Usage:'
  print '  upload.py  album  picture1  [picture2  [...]]'
  print
  sys.exit(0)

album_name = sys.argv[1]
su_cookie  = None

def safe_geturl(request) :
  global su_cookie

  # Try up to three times
  for x in range(5) :
    try :
      response_obj = urllib2.urlopen(request)
      response = response_obj.read()
      result = json.loads(response)

      # Test for presence of _su cookie and consume it
      meta_info = response_obj.info()
      if meta_info.has_key('set-cookie') :
        match = re.search('(_su=\S+);', meta_info['set-cookie'])
        if match and match.group(1) != "_su=deleted" :
          su_cookie = match.group(1)
      if result['stat'] != 'ok' : raise Exception('Bad result code')
      return result
    except :
      if x < 4 :
        print "  ... failed, retrying"
      else :
        print "  ... failed, giving up"
        print "  Request was:"
        print "  " + request.get_full_url()
        try :
          print "  Response was:"
          print response
        except :
          pass
        traceback.print_exc()
        #sys.stdin.readline()
        #sys.exit(1)
        return result

def smugmug_request(method, params) :
  global su_cookie

  paramstrings = [urllib.quote(key)+'='+urllib.quote(params[key]) for key in params]
  paramstrings += ['method=' + method]
  url = urlparse.urljoin(API_URL, '?' + '&'.join(paramstrings))
  request = urllib2.Request(url)
  if su_cookie :
    request.add_header('Cookie', su_cookie)
  return safe_geturl(request)

result = smugmug_request('smugmug.login.withPassword',
                         {'APIKey'       : APIKEY,
                          'EmailAddress' : EMAIL,
                          'Password'     : PASSWORD})
session = result['Login']['Session']['id']

result = smugmug_request('smugmug.albums.get', {'SessionID' : session})
album_id = None
for album in result['Albums'] :
  if album['Title'] == album_name :
    album_id = album['id']
    break
if album_id is None :
  print 'That album does not exist'
  sys.exit(1)

for filename in sys.argv[2:] :
  data = open(filename, 'rb').read()
  print 'Uploading ' + filename
  upload_request = urllib2.Request(UPLOAD_URL,
                                   data,
                                   {'Content-Length'  : len(data),
                                    'Content-MD5'     : hashlib.md5(data).hexdigest(),
                                    'Content-Type'    : 'none',
                                    'X-Smug-SessionID': session,
                                    'X-Smug-Version'  : API_VERSION,
                                    'X-Smug-ResponseType' : 'JSON',
                                    'X-Smug-AlbumID'  : album_id,
                                    'X-Smug-FileName' : os.path.basename(filename) })
  result = safe_geturl(upload_request)
  if result['stat'] == 'ok' :
    print "  ... successful"

print 'Done'
# sys.stdin.readline()

I am donating this script to the public domain. You are welcome to use and modify it as you please without conditions. I’d appreciate hearing about your experience with this script or any changes and improvements you’ve made; please leave a comment. Thanks!

Update 2010-07-20

Since I first posted this, I’ve updated it as follows:

  1. Add a Content-Type header of ‘none’. This is to workaround a bug in the SmugMug API.
  2. Use basename() to send only the file’s basename for X-Smug-FileName.
  3. Rewrite safe_geturl() to loop up to five times if the upload attempt fails. I’ve found that uploading is surprisingly unreliable, and re-attempting the upload generally works fine.
  4. Add a commented call to readline() at the end of the script. In my case, I run my script by dragging files onto an icon on my Windows desktop, which causes it to run in a DOS window and vanish when done. If you uncomment this line, it will wait for you to press Enter when it is done uploading. You’ll be able to see any files that weren’t uploaded successfully.

Update 2010-11-28

SmugMug made a recent change to their API’s login behavior which broke this script. While the new login behavior is not documented in the API docs, the fix is apparently to use a session cookie along with the session ID. While it’s a bit of a kludge, I’ve updated the script above to save this cookie in a global variable and submit it on subsequent requests.

Update 2011-06-24

I’ve fixed a bug in the script causing it to wrongly report a failure for certain requests that don’t send back the session cookie. The fix involves testing whether a set-cookie header was returned before accessing the header.

Update 2013-10-01

Version 1.2.0 of the SmugMug API has stopped working, so I have updated the script to use version 1.2.2 of the API.

Written by Scott Moonen

December 1, 2008 at 3:33 pm

Follow

Get every new post delivered to your Inbox.

Join 128 other followers