sql store for ruby-openid

[Andrew Arrow]

I've been running openid in production for a few weeks now and the
db/cstore/nonce directory has WAY too many files in it.  I looked for
the SQL Store class but couldn't find it so I wrote this one.  I tried
to get really good indexes on the two tables so not only would lookups
be fast, but all selecting all the expired ones.  I used datamapper
with these models:

class OpenAssociation < DataMapper::Base
  property :proto,    :string, :size => 255
  property :domain,   :string, :size => 255
  property :url,      :string, :size => 255
  property :handle,   :string, :size => 255
  property :data,     :text

  property :valid_til, :datetime, :index => true

  index([:proto, :domain, :url, :handle], :unique)
  index([:proto, :domain, :url, :valid_til])
end

class OpenNonce < DataMapper::Base
  property :proto,    :string, :size => 255
  property :domain,   :string, :size => 255
  property :url,      :string, :size => 255
  property :salt,     :string, :size => 255

  property :valid_til, :datetime, :index => true

  index([:valid_til, :proto, :domain, :url, :salt], :unique)

end

which make tables like:

CREATE TABLE `open_associations` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `proto` varchar(255) collate utf8_unicode_ci default NULL,
  `domain` varchar(255) collate utf8_unicode_ci default NULL,
  `url` varchar(255) collate utf8_unicode_ci default NULL,
  `handle` varchar(255) collate utf8_unicode_ci default NULL,
  `data` text collate utf8_unicode_ci,
  `valid_til` datetime default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `open_associations_proto_domain_url_handle_index`
(`proto`,`domain`,`url`,`handle`),
  KEY `open_associations_valid_til_index` (`valid_til`),
  KEY `open_associations_proto_domain_url_valid_til_index`
(`proto`,`domain`,`url`,`valid_til`)
) ENGINE=InnoDB

CREATE TABLE `open_nonces` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `proto` varchar(255) collate utf8_unicode_ci default NULL,
  `domain` varchar(255) collate utf8_unicode_ci default NULL,
  `url` varchar(255) collate utf8_unicode_ci default NULL,
  `salt` varchar(255) collate utf8_unicode_ci default NULL,
  `valid_til` datetime default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `open_nonces_valid_til_proto_domain_url_salt_index`
(`valid_til`,`proto`,`domain`,`url`,`salt`),
  KEY `open_nonces_valid_til_index` (`valid_til`)
) ENGINE=InnoDB

Then I modified the filesystem store like so:

require 'openid/util'
require 'openid/store/interface'
require 'openid/store/nonce'
require 'openid/association'

class SqlStore < OpenID::Store::Interface
  @@FILENAME_ALLOWED =
"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ.-".split("")

  # store_association(server_url, association)
  # get_association(server_url, handle=nil)
  # remove_association(server_url, handle)
  # use_nonce(server_url, timestamp, salt)
  # cleanup_nonces
  # cleanup_associations
  # cleanup

  def get_new_association(server_url, handle)
    unless server_url.index('://')
      raise ArgumentError, "Bad server URL: #{server_url}"
    end

    oa = OpenAssociation.new
    oa.proto, rest = server_url.split('://', 2)
    oa.domain = filename_escape(rest.split('/',2)[0])
    oa.url = safe64(server_url)
    if handle
     oa.handle = safe64(handle)
    else
      oa.handle = ''
    end
    oa
  end

  def store_association(server_url, association)
    oa = get_new_association(server_url, association.handle)
    oa.data = association.serialize
    oa.valid_til = Time.at(association.issued.to_i + association.lifetime)
    oa.save
  end

  def get_association(server_url, handle=nil)
    oa = get_new_association(server_url, handle)
    if handle
      return _get_association(oa)
    end
    sql = %{ SELECT id FROM open_associations WHERE
             proto='#{oa.proto}' and
             domain='#{oa.domain}' and
             url='#{oa.url}' ORDER BY valid_til }
    last = OpenAssociation.find_by_sql(sql).last
    return _get_association(nil, last) if last
  end

  def _get_association(oa, id=nil)

    if not id
      sql = %{ SELECT id FROM open_associations WHERE
               proto='#{oa.proto}' and
               domain='#{oa.domain}' and
               url='#{oa.url}' and
               handle='#{oa.handle}' }
      id = OpenAssociation.find_by_sql(sql).first
      return if not id
    end

    sql = %{ SELECT data FROM open_associations WHERE
             id=#{id} }
    data = OpenAssociation.find_by_sql(sql).first

    begin
      association = OpenID::Association.deserialize(data)
    rescue
      database.execute("delete from open_associations where id=#{id}")
      return nil
    end

    if association.expires_in == 0
      database.execute("delete from open_associations where id=#{id}")
      return nil
    else
      return association
    end
  end

  def remove_association(server_url, handle)
    assoc = get_association(server_url, handle)

    if assoc.nil?
      return false
    else
      oa = get_new_association(server_url, handle)
      sql = %{ delete FROM open_associations WHERE
               proto='#{oa.proto}' and
               domain='#{oa.domain}' and
               url='#{oa.url}' and
               handle='#{oa.handle}' }
      database.execute(sql)
      return true
    end
  end

  # Return whether the nonce is valid
  def use_nonce(server_url, timestamp, salt)
    return false if (timestamp - Time.now.to_i).abs > OpenID::Nonce.skew

    on = OpenNonce.new
    if server_url and !server_url.empty?
      on.proto, rest = server_url.split('://',2)
    else
      on.proto, rest = '',''
    end
    raise "Bad server URL" unless on.proto && rest

    on.domain = filename_escape(rest.split('/',2)[0])
    on.url = safe64(server_url)
    on.salt = safe64(salt)
    on.valid_til = Time.at(timestamp + OpenID::Nonce.skew)

    on.save
  end

  # Remove expired entries from the database. This is potentially expensive,
  # so only run when it is acceptable to take time.
  def cleanup
    cleanup_associations
    cleanup_nonces
  end

  def cleanup_associations
    sql = %{ delete FROM open_associations WHERE
             valid_til < now() }
    database.execute(sql).affected_rows
  end

  def cleanup_nonces
    sql = %{ delete FROM open_nonces WHERE
             valid_til < now() }
    database.execute(sql).affected_rows
  end

  protected

  # Create a temporary file and return the File object and filename.
  #
  # create a safe filename from a url
  def filename_escape(s)
    s = '' if s.nil?
    filename_chunks = []
    s.split('').each do |c|
      if @@FILENAME_ALLOWED.index(c)
        filename_chunks << c
      else
        filename_chunks << sprintf("_%02X", c[0])
      end
    end
    filename_chunks.join("")
  end

  def safe64(s)
    s = OpenID::CryptUtil.sha1(s)
    s = OpenID::Util.to_base64(s)
    s.gsub!('+', '_')
    s.gsub!('/', '.')
    s.gsub!('=', '')
    return s
  end

end