Ruby File Uploads Using PostgreSQL's Large Objects

• Tweet
• 

        provided by <a href="http://robbyonrails.com/">Robby</a>, with a Rails tutorial coming soon (hopefully).

DB structure:

CREATE DATABASE filedb;
 
\c filedb
 
CREATE TABLE files (
  id SERIAL PRIMARY KEY NOT NULL,
  file_oid OID,
  name TEXT,
  size INT,
  type TEXT,
  created_at TIMESTAMP DEFAULT now()
);
 
COMMENT ON TABLE files IS 'table for storing cool files'

ruby code: 

#!/usr/bin/env ruby
 
file = ARGV[0]
 
require "postgres"
require "readbytes"
 
READ_SIZE = 8192
 
conn = PGconn.connect("localhost", 5432, "","", "filedb", "user", "pass")
 
conn.exec("BEGIN")
lo = conn.lo_import(file)
conn.exec("COMMIT")
 
### error checking before you get here
### quick and ugly sql!
size = File.stat(file).size
sql = "INSERT INTO files (file_oid, name, size, type) VALUES (#{lo.oid},'#{file}','#{size}','image/jpeg');"
conn.exec(sql)
 
puts "large object id: #{lo.oid}"
 
--------------------
 
$ ./lo_import.rb 6.jpg
large object id: 323476

Tags:
• fileupload
• PostgreSQL
• Ruby