-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathpaperclip-database-storage.html
66 lines (66 loc) · 9.28 KB
/
paperclip-database-storage.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
<!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Database storage for Paperclip: rewritten to use a single table - Pat Shaughnessy</title><meta name="description" content=""><meta name="author" content=""><meta name="viewport" content="width=device-width, initial-scale=1"><link rel="stylesheet" href="/assets/css/normalize.css"><link rel="stylesheet" href="/assets/css/skeleton.css"><link rel="alternate" type="application/atom+xml" title="Pat Shaughnessy - Feed" href="http://feeds2.feedburner.com/patshaughnessy"><link rel="icon" type="image/png" href="images/favicon.png"></head><body><div id="banner"><a href="/"><span id="title">Pat Shaughnessy</span><span id="tagline"> blogger, rubyist, aspiring author</span></a></div><div style="margin-top: 35px"><div class="ten columns"><div class="container"><div class="row"><article class="post"><header><h1>Database storage for Paperclip: rewritten to use a single table</h1><div class="metadata">April 14th 2009 — <a href="#disqus_thread" data-disqus-identifier="https://patshaughnessy.net/paperclip-database-storage"> Comments and Reactions</a></div></header><section class="content"><p>Back in February I wrote an <a href="https://patshaughnessy.net/2009/2/19/database-storage-for-paperclip">implementation of a new storage module for Paperclip</a> that supports saving file attachments in a database table. My original implementation saved the file attachments in a separate database table, which was internally managed using a “has_many” relationship from the target model.</p>
<p>This month I decided to rewrite the code to use a simpler, single table approach. Instead of saving the files in a separate table, each file is saved in a BLOB column in the same table as the target model. This makes the database storage module easier and more intuitive to use, and moves it closer to the original intent of the Paperclip design.</p>
<p>Code: <a href="http://github.com/patshaughnessy/paperclip">http://github.com/patshaughnessy/paperclip</a></p>
<p>New usage:</p>
<ol>
<li>Install and use my version of the plugin:
<pre>script/plugin install git://github.com/patshaughnessy/paperclip.git</pre>
</li>
<li>In your model specify the "database" storage option; for example:
<pre>has_attached_file :avatar, :storage => :database</pre>
The file will be stored in a column called [attachment name]_file (e.g. "avatar_file") by default. To specify a different BLOB column name, use :column, like this:
<pre>has_attached_file :avatar,
:storage => :database,
:column => 'avatar_data'</pre>
</li>
<li>If you have defined different styles, these files will be stored in additional columns called [attachment name]_[style name]_file (e.g. "avatar_thumb_file") by default. To specify different column names for each style, use :column in the style definition, like this:</li>
<pre>has_attached_file :avatar,
:storage => :database,
:styles => {
:medium => {
:geometry => "300x300>",
:column => 'medium_file'
},
:thumb => {
:geometry => "100x100>",
:column => 'thumb_file'
}
}</pre>
<li>You need to create these new columns in your migrations or you'll get an exception. Example:
<pre>add_column :users, :avatar_file, :binary
add_column :users, :avatar_medium_file, :binary
add_column :users, :avatar_thumb_file, :binary</pre>
Note the migration for the "binary" column type will not work for LONGBLOBs in MySQL. Here's an example migration for MySQL:
<pre>execute 'ALTER TABLE users ADD COLUMN avatar_file LONGBLOB'
execute 'ALTER TABLE users ADD COLUMN avatar_medium_file LONGBLOB'
execute 'ALTER TABLE users ADD COLUMN avatar_thumb_file LONGBLOB'</pre>
</li>
<li>
To avoid performance problems loading all of the BLOB columns every time you access your ActiveRecord object, a class method is provided on your model called “select_without_file_columns_for.” This is set to a hash that will instruct ActiveRecord::Base.find to load all of the columns <u>except</u> the BLOB/file data columns, for example:
<pre>{:select=>"id,name,avatar_file_name,avatar_content_type,..."}</pre>
If you’re using Rails 2.3, you can specify this as a default scope:
<pre>default_scope select_without_file_columns_for(:avatar)</pre>
Or if you’re using Rails 2.1 or 2.2 you can use it to create a named scope:
<pre>named_scope :without_file_data, select_without_file_columns_for(:avatar)</pre>
</li>
<li>By default, URLs will be set to this pattern:
<pre>/:relative_root/:class/:attachment/:id?style=:style</pre>
Example:
<pre>/app-root-url/users/avatars/23?style=original</pre>
The idea here is that to retrieve a file from the database storage, you will need some controller's code to be executed. Once you pick a controller to use for downloading, you can add this line to generate the download action for the default URL/action (the plural attachment name), "avatars" in this example:
<pre>downloads_files_for :user, :avatar</pre>
Or you can write a download method manually if there are security, logging or other requirements. If you prefer a different URL for downloading files you can specify that in the model; e.g.:
<pre>has_attached_file :avatar,
:storage => :database,
:url =>'/users/show_avatar/:id/:style'</pre>
</li>
<li>Add a route for the download to the controller which will handle downloads, if necessary. The default URL, /:relative_root/:class/:attachment/:id?style=:style, will be matched by the default route: :controller/:action/:id</li>
</ol>
<p>For now, I’ve overwritten my code from February. I believe this implementation is cleaner and easier to use; if anyone did download and use my code from February let me know and I can help you migrate the file data from the separate table back to columns in primary table. I will be writing a script to do this for my own application.</p>
<p>When I have time in the next few days or weeks, I’ll post a sample application that illustrates all of these steps and shows exactly how to do all of this. If anyone has any questions or suggestions please let me know.</p>
</section><section class="comments"><div id="disqus_thread"><script type="text/javascript">var disqus_identifier = 'https://patshaughnessy.net/paperclip-database-storage'; var disqus_shortname = 'patshaughnessy'; var disqus_title = 'Database storage for Paperclip: rewritten to use a single table';</script></div><script type="text/javascript" src="https://disqus.com/forums/patshaughnessy/embed.js"></script><noscript><a href="https://patshaughnessy.disqus.com/?url=ref">View the discussion thread.</a></noscript></section></article></div></div></div><div class="two columns"><div id="sidebar"><img src="/assets/images/pat.jpg"></img><div class="header">Subscribe</div><div class="links"><ul><li><a id="feed" href="http://feeds.feedburner.com/patshaughnessy"><img src="/assets/images/feed-icon16x16B.png"></img></a><a href="http://twitter.com/pat_shaughnessy"><img width="20" height="20" src="/assets/images/twitter.svg"></img></a></li></ul></div><div class="header">Buy My Book</div><div class="links"><ul><li><a href="/ruby-under-a-microscope"><img src="/assets/images/book-cover.png"></img></a></li><li id="eBook"><a href="/ruby-under-a-microscope">Ruby Under a Microscope</a></li></ul></div><div class="header">More on Paperclip</div><div class="links"><ul><li><a href="/2010/5/23/how-to-install-paperclip-in-a-rails-3-app">How to install Paperclip in a Rails 3 app</a></li><li><a href="/2009/5/29/paperclip-sample-app-part-3-saving-file-attachments-in-a-database-blob-column">Paperclip sample app part 3: saving file attachments in a database BLOB column</a></li><li><a href="/2009/5/16/paperclip-sample-app-part-2-downloading-files-through-a-controller">Paperclip sample app part 2: downloading files through a controller</a></li><li><a href="/2009/4/30/paperclip-sample-app">Paperclip sample app</a></li></ul></div><div class="header">Popular</div><div class="links"><ul><li><a href="/2016/11/26/learning-to-read-x86-assembly-language">Learning to Read x86 Assembly Language</a></li><li><a href="/2012/1/4/never-create-ruby-strings-longer-than-23-characters">Never create Ruby strings longer than 23 characters</a></li><li><a href="/2012/3/23/why-you-should-be-excited-about-garbage-collection-in-ruby-2-0">Why You Should Be Excited About Garbage Collection in Ruby 2.0</a></li><li><a href="/2013/4/3/ruby-2-0-works-hard-so-you-can-be-lazy">Ruby 2.0 Works Hard So You Can Be Lazy</a></li></ul></div><div class="header"><a href="/">More...</a></div></div></div><script type="text/javascript">(function () {
var s = document.createElement('script'); s.async = true;
s.type = 'text/javascript';
s.src = 'https://' + disqus_shortname + '.disqus.com/count.js';
(document.getElementsByTagName('HEAD')[0] || document.getElementsByTagName('BODY')[0]).appendChild(s);
}());</script></div><div id="footer" class="ten columns"><p>Content and UI design © 2008-2022 Pat Shaughnessy</p></div></body></html>