The following instructions are written to get a single-table database (e.g., a spreadsheet) up and running online very quickly. We’ll look at adding a second and third table at another time. These instructions provide absolutely no detail about what’s going on under the hood, and also assume that you have the necessary software installed on your computer. If you’re using a Mac and have developer tools installed, then you are probably ready to go. If you don’t use a Mac, hit a snag, or just feel unsure, then go here for details: http://guides.rubyonrails.org/getting_started.html

1. Open the Terminal application, use cd to navigate to the place where you’d like to setup your database, and create the rails project and scaffolding by typing:

rails new coral_traits

Substitute a different name if you want. Open the project in a text editor. If you’re using Textmate type:

mate coral_traits

2. Move into the project directory and create the project database (which will use the default sqlite3):

cd coral_traits
rake db:create

3. We’ll make a welcome page, rather than the index leading straight to the database:

rails generate controller welcome index

Navigate to: “app/views/welcome/index.html.erb” and enter something like:

<h1>Hello, database!</h1>
<p>Welcome to the coral trait collaboration. Here are some details about the project and collaborators.</p>
<%= link_to "Show me the database", traits_path %>

Remove the default public index:

rm public/index.html

Go to “config/routes.rb” and uncomment the line:

root :to => 'welcome#index'

4. Make a data table and all the necessary scaffolding to support the table:

rails generate scaffold Traits species:string family:string growth_form:string polyp_diameter:decimal notes:text
rake db:migrate

If you are entering you’re own data, then you’ll diverge somewhat at this point. Just list each of your header names, use underscores for spaces, and append the data type with a colon in between.

5. Start the web server:

rails server

Go to your new database and add some data:

localhost:3000

This database will be available to anyone on your network. Go to “Sharing” in your “System Preferences” to find your computer network address. My laptop is robusta.local (so http://robusta.local:3000). But now I’ve put tutorial and database on the acropora server at http://acropora.bio.mq.edu.au:3002, and so available to anyone in the world. Except it’s password protected (see point 7 below).

6. But you already have the data in an Excel spreadsheet… how do you get it in the database? Open your spreadsheet and add a column to the left. Fill this column with IDs (ascending integers), making sure that none of the IDs are already used in your rails database or you won’t be able to do the import. If you haven’t added any data yet, then just start at 1. Otherwise, click the “Show” link for the last entry in your rails database, and then look at the number at the end of the web address. I have a number 3. Therefore, I’ll start my new spreadsheet column at 4. We also need two blank columns to the right of the “notes” column (because sqlite needs these for “date created” and “date modified”). If the dimensions of the spreadsheet data don’t match those of the data table, then you won’t be able to import. To do this in Excel we need a hack: go to the bottom row and enter the letter “a” (or something) in the cell two to the right of the notes cell. Delete the header row because this information is already in the database table you made above), and “save as” a .csv file into the “db/” folder in your rails project. Open the .csv file in your text editor and delete the “a” (you’ll see that you got the extra columns you needed!). “Save as” in your text editor and make sure you have unix-style line endings like LF. Here’s my file: corals.csv

In the terminal, navigate to: “db/” and type sqlite3 development.sqlite3

On the sqlite prompt type:

.mode csv
.import corals.txt traits
.quit

(Note that this is the hardest step to get correct, and the reason it’s hard is because of Excel’s idiosyncrasies.)

7. Maybe you only want some people to have access to viewing and entering data? Navigate to: app/controllers/application_controller.rb and insert the following code just below the first line:

http_basic_authenticate_with :name => "sesame", :password => "sesame9"

8. Now your collaborators have added a bunch of new data to your database and you want to be able to download the data to run some R analyses and make a figure. Navigate again to: app/controllers/application_controller.rb and insert the following code just below the protect_from_forgery line:

require 'csv'
def export_data
    @all = Trait.find_by_sql("SELECT * FROM traits")  
    csv_string = CSV.generate do |csv|
        csv << ["database_id", "species", "family", "growth_form", "polyp_diameter", "notes"]      
        @all.each do |tra|
            csv << [tra.id, tra.species, tra.family, tra.growth_form, tra.polyp_diameter, tra.notes]         end      end      send_data csv_string,          :type => 'text/csv; charset=iso-8859-1; header=present', :stream => true,
        :disposition => "attachment; filename=traits_#{Date.today.strftime('%Y%m%d')}.csv" 
end

(The details in the above code need to reflect your database table that was generated in step 4.)

Navigate to: app/config/routes.rb and somewhere near the top, but below the first line, insert:

match 'export_data' => 'welcome#export_data'

Finally, you need the download link. Let’s make it appear at the top of every page along with a link back to the welcome page. Navigate to: app/views/layouts/application.html.erb and after the body tag insert:

 

<%= link_to "Export data (.csv)", "export_data" %> | <%= link_to "Welcome page", "/" %>

 

9. If you have a trait, you must have to have a species, and people are sometimes forgetting to enter the species name. Also, someone keeps entering text data into the polyp diameter field! Let’s stop that too. Navigate to: app/models/trait.rb and after the first line insert:

validates :species, :presence => true
validates :polyp_diameter, :numericality => true

10. All corals fall under three simple growth forms! Why is your R code suggesting that there are 16! Probably spelling mistakes and incompetence. Let’s make this easier by providing the choices. Navigate to: app/views/traits/_form.html.erb and swap the line <%= f.text_field :growth_form %> with:

<%= f.select :growth_form, ['', 'branching', 'tabular', 'massive'] %>

 

11. I’ve got a lot of data, what about a search? Navigate to: “app/models/trait.rb” and insert the following:

def self.search(search)
    if search
        where('species LIKE ? OR family LIKE ? OR growth_form LIKE ?', "%#{search}%", "%#{search}%", "%#{search}%")
    else
        all
    end
end

Then navigate to: “app/controller/traits_controller.rb” and change @traits = Trait.all to:

@traits = Trait.search(params[:search])

Finally, add the search box to the traits index page. Navigate to: “app/views/traits/index.html.erb” and insert the following near the top of the page:

<%= form_tag traits_path, :method => 'get' do %>
    &lt;p&gt;Search species, families and growth forms: <%= text_field_tag :search, params[:search] %>
    <%= submit_tag "Search", :name => nil %>&lt;/p&gt;
<% end %>

*You now have a fairly handy online database for collaboration.