Using Oracle and Ruby on Rails

From your terminal window, execute the connect.rb script by executing the following commands:

export NLS_LANG=American_America.UTF8
ruby connect.rb

You should see the above message if the connection is successful, or an error if it was not.

The contents of the connect.rb file is as follows:

# connect.rb: Create connections to Oracle
require 'config.rb'

begin
	# login as normal user
	conn1 = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)
	puts "login as normal user succeeded."
	conn1.logoff
	puts "logoff succeeded."	
	puts

	# login as DBA
	conn2 = OCI8.new('sys', 'oracle', DB_SERVER, :SYSDBA)
	puts "login with DBA privilege succeeded."
	conn2.logoff
	puts "logoff succeeded."

rescue OCIError
	# display the error message
	puts "OCIError occured!"
	puts "Code: " + $!.code.to_s
	puts "Desc: " + $!.message

end

puts '-'*80

The commands that will be included in the rest of your scripts throughout this tutorial are as follows:

require 'config.rb'
 
 #create connection
 conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# operations on conn goes here


 #log out
 conn.logoff

Create a cursor, prepare to execute SQL statement and return the instance of OCI8::Cursor.

Execute the SQL statement. The type of return value depends on the type of SQL statement. When bindvars are specified, they are bound as bind variables before execution.

From your terminal window, execute the fetch.rb script by executing the following command:

ruby fetch.rb

The output is shown in the screenshot.

The contents of the fetch.rb file is as follows:

#fetch.rb: Fetch data from database
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# parse and exec the statement
cursor = conn.parse("select * from regions")
cursor.exec

# output column names
puts cursor.getColNames.join(",")

# output rows
while r = cursor.fetch 
	puts r.join(",")
end

# close the cursor and logoff
cursor.close
conn.logoff
puts '-'*80

There is another way to write the same code that is more specific to ruby. From your terminal window, execute the fetch_r.rb script by executing the following command:

ruby fetch_r.rb

The output is shown in the screenshot. 

The contents of the fetch_r.rb file is as follows:

# fetch_r.rb: Fetch in a more ruby-like way
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# Fetch and display the rows in a block
nrow = conn.exec("select * from regions") do |r|
	puts r.join(",")
end

# Display count of rows
puts ' '*30 + nrow.to_s + " rows were fetched."

conn.logoff
puts '-'*80

Prefeching helps performance when many rows are fetched. From your terminal window, execute the prefetch.rb script by executing the following command:

ruby prefetch.rb

The output is shown in the screenshot. 

The contents of the prefetch.rb file is as follows:

# prefetch.rb: Prefetch data
require 'config.rb'

SELECT_STATEMENT = "select a.first_name, a.last_name, b.first_name, b.last_name,
   a.salary - b.salary salarydiff 
from employees a, employees b 
where b.id > a.id"

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# Execute statement with different prefetch sizes
[nil,100].each do |prefetchrows|

  cursor = conn.parse(SELECT_STATEMENT)

  if prefetchrows
    cursor.prefetch_rows = prefetchrows
  else
    prefetchrows = "default"
  end

  puts "Executing with prefetch rows = #{prefetchrows}"

  time1 = Time.now

  cursor.exec

  while r = cursor.fetch()
    # puts r.join(",")
  end

  # Display count of rows
  puts ' '*30 + cursor.row_count.to_s + " rows were fetched."

  time2 = Time.now
  puts "Time cost: " + (time2-time1).to_s
  puts

end

conn.logoff
puts '-'*80

Bind variables explicitly. When key is number, it binds by position, which starts from 1. When key is string, it binds by the name of placeholder.

Can also bind variables via bindvars.

From your terminal window, execute the bind.rb script by executing the following command:

ruby bind.rb

The output is shown in the screenshot.

The contents of the bind.rb file is as follows:

# bind.rb: How to bind variables
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# Prepare the data, also display what's hard-coded statement
conn.exec("DELETE FROM test_bind")
conn.exec("INSERT INTO test_bind VALUES(1, 'Unknown')")
conn.exec("INSERT INTO test_bind VALUES(2, 'Unknown')")
conn.exec("INSERT INTO test_bind VALUES(3, 'Unknown')")

# Now update the data using bind variables.
cursor = conn.parse("UPDATE test_bind SET name = :name WHERE id = :id")
cursor.bind_param(1,nil,String,100) #  Or: cursor.bind_param(1, ' '*100)
cursor.bind_param(2,Fixnum)					#  Or: cursor.bind_param(2, id) 

id = 1
['China', 'Korea', 'Japan'].each { |country|
	cursor[1] = country
	cursor[2] = id
	cursor.exec
	id = id + 1
}

# Fetch back the updated data
conn.exec("SELECT * FROM test_bind").fetch do |row|
	puts row.join(',')
end

conn.logoff

puts '-'*80

To test the performance improvement by using bind variables, execute the bind_perf_test.rb script by executing the following command:

ruby bind_perf_test.rb

The output is shown in the screenshot. 

The contents of the bind_perf_test.rb file is as follows:

# bind_perf_test.rb: Performance test for binding variables
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# Not using bind varables.
puts "Fetching result, not using bind variables:"
time1 = Time.now
(100..200).each { |id|
	cursor = conn.parse("SELECT first_name FROM employees WHERE id = #{id}")
  cursor.exec
	#puts id.to_s + " --> " + cursor.fetch[0]
}
time2 = Time.now
puts "Time cost: " + (time2-time1).to_s
puts

# Using bind varables.
puts "Fetching result, using bind variables:"

time1 = Time.now
cursor = conn.parse("SELECT first_name FROM employees WHERE id = :id")
cursor.bind_param(1, Fixnum)

(100..200).each { |id|    
	cursor[1] = id
	cursor.exec
	#puts id.to_s + " --> " + cursor.fetch[0]
}
time2 = Time.now
puts "Time cost: " + (time2-time1).to_s

# End of the test
conn.logoff

puts '-'*80

Use this method within parse and exec. pos starts from 1. length is used when type is String.

From your terminal window, execute the define.rb script by executing the following command:

ruby define.rb

The output is shown in the screenshot.

You can see the difference between the two blocks of output.

The contents of the define.rb file is as follows:

# define.rb: Define output columns
require 'config.rb'


# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)


# Prepare the data
conn.exec("DELETE FROM test_define");
conn.exec("INSERT INTO test_define VALUES(1,'Scott Tiger', SYSDATE, SYSTIMESTAMP)")

# Define to fetch Date and Time
cursor = conn.parse("SELECT name,birthdate,lastvisit FROM test_define WHERE id = :id")
cursor.bind_param(1, 1)
cursor.define(1, String, 100)
cursor.define(2, Date)
cursor.define(3, Time)
cursor.exec

while r = cursor.fetch
	puts r.join("\n")
end

puts 

# Define to fetch Date and Time as String
#conn.exec("ALTER SESSION SET nls_territory='TAIWAN' nls_language='TRADITIONAL CHINESE'")
cursor = conn.parse("SELECT name,birthdate,lastvisit FROM test_define WHERE id = :id")
cursor.bind_param(1, 1)
cursor.define(1, String, 100)
cursor.define(2, String, 100)
cursor.define(3, String, 100)
cursor.exec

while r = cursor.fetch
	puts r.join("\n")
end

conn.logoff
puts '-'*80

Get/Set the state of the autocommit mode. The default value is false (remember: not committed transactions are rollbacked when logoff). If true, the transaction is committed automatically whenever executing insert/update/delete statements.

From your terminal window, execute the transaction.rb script by executing the following command:

ruby transaction.rb

This script updates a row using connection conn1. In Oracle, new data is only visible in the original database session until it is committed. The output is shown in the screenshot.

In this case, connection conn2 doesn’t know what happened in conn1’s not-committed transactions.

The contents of the transaction.rb file is as follows:

# transaction.rb: How to use transactions
require 'config.rb'

# Create connections to Oracle
conn1 = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)
conn2 = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

conn1.exec("DELETE FROM test_transaction")
conn1.exec("INSERT INTO test_transaction VALUES(1, 'old value')")
conn1.commit

#conn1.autocommit = true
puts "OCI8.autocommit = " + conn1.autocommit.to_s
puts "conn1 updated the name to 'something new'";
conn1.exec("UPDATE test_transaction SET name = 'something new' WHERE id = 1");
#conn1.commit

puts "conn2 got the name as '" + 
		conn2.exec('SELECT name FROM test_transaction WHERE id = 1').fetch[0] + "'"

conn1.logoff
conn2.logoff

puts '-'*80

Update the transaction.rb script and uncomment conn1.autocommit = true and save the file.

Execute the script again using the following command:

ruby transaction.rb

Now conn2 knows there is something new. The output is shown in the screenshot.

Committing each row individually causes extra load on the server. You can compare the performance difference between committing each row individually versus at the end of the transaction. To test the difference, execute the trans_perf_test.rb script using the following command:

ruby trans_perf_test.rb

Notice that the second number is smaller than the first one. The output is shown in the screenshot.

The contents of the trans_perf_test.rb file is as follows:

# trans_perf_test.rb: Performance test for transactions
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)
conn.exec("DELETE FROM test_transaction")
conn.commit

# Commit on each time
conn.autocommit = true
time1 = Time.now
300.times{
	conn.exec("INSERT INTO test_transaction VALUES(1, 'something')")
}
time2 = Time.now

# Commit on the last step
conn.autocommit = false	# It's the default
time3 = Time.now
300.times{
	conn.exec("INSERT INTO test_transaction VALUES(1, 'something')")
}
conn.commit
time4 = Time.now

# Output the comparation
puts "Time cost of each-time commit(sec): " + (time2-time1).to_s
puts "Time cost of one-time commit(sec) : " + (time4-time3).to_s

conn.logoff

puts '-'*80

Oracle stored packages. TO_CHAR is a built-in function.

From your terminal window, execute the plsql.rb script by executing the following command:

ruby plsql.rb

The output is shown in the screenshot.

The contents of the plsql.rb file is as follows:

# plsql.rb: Call PL/SQL procedures and functions
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

puts 'Get version information from Oracle:'
cursor = conn.parse("BEGIN DBMS_UTILITY.db_version(:ver, :comp); END;")
cursor.exec(' '*50,' '*50)
puts "Oracle DB Version: " + cursor[1]
puts "Oracle DB Compatibility: " + cursor[2]
puts

puts 'Call TO_CHAR function:'
cursor = conn.parse("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;")
cursor.exec('ABCD', 123)
puts "TO_CHAR input: " + cursor[2].to_s
puts "TO_CHAR output: " + cursor[1]
puts

puts 'Get DBMS_OUTPUT:'
conn.exec("BEGIN DBMS_OUTPUT.ENABLE(NULL); END;")
conn.exec("BEGIN DBMS_OUTPUT.put_line('Hello world!'); END;")
conn.exec("BEGIN DBMS_OUTPUT.put_line('Can you see me?'); END;")

cursor = conn.parse("BEGIN DBMS_OUTPUT.get_line(:line, :status); END;")
cursor.bind_param(':line', nil, String, 255)
cursor.bind_param(':status',Fixnum)

while true
	cursor.exec
	break if cursor[':status'] == 1
	puts cursor[':line']
end

puts '-'*80

Check whether BLOB is available or not. To use BLOB you need to insert EMPTY_BLOB() at first.

A PNG file is in the same folder with the script file. Create a folder called download. From your terminal window, execute the blob.rb script by executing the following command:

mkdir download
ruby blob.rb

The output is shown in the screenshot.

The contents of the blob.rb file is as follows:

#blob.rb: Save and Load BLOB
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

conn.exec("DELETE FROM test_blob")

# Must insert a EMPTY_BLOB before save real data
cursor = conn.parse("INSERT INTO test_blob VALUES(:name, EMPTY_BLOB())")
Dir["*.png"].each do |fname|
	cursor.exec(fname)
end

# Save BLOB into Oracle
conn.exec("SELECT name,image FROM test_blob") do |name, image|
	puts "uploading file: " + name
	File.open(name, 'r') do |f|
		image.write(f.read)
		image.size = f.pos
	end
end

# Load BLOB from Oracle
conn.exec("SELECT name,image FROM test_blob") do |name, image|
	puts "downloading file: " + name
	File.open("download/"+name, 'w') do |f|
		f.write(image.read)
	end
end

# End
conn.logoff
puts '-'*80

Review the privileges for the ruby.png file. Execute the following command:

ls -l ruby.png download/ruby.png

Generate XML from relational data based on a SELECT statement. It returns CLOB.

From your terminal window, execute the xml.rb script by executing the following command:

ruby xml.rb

The output is shown in the screenshot.

The contents of the xml.rb file is as follows:

# xml.rb: Generate a xml document based on relational data
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

sql = "SELECT DBMS_XMLGEN.getxml('
		SELECT dept.name,dept.id,
		CURSOR(
			SELECT emp.first_name,emp.last_name,emp.phone_number
			FROM employees emp
			WHERE emp.department_id=dept.id
		) AS employees
		FROM departments dept
		WHERE dept.id in (20,110)'
	) AS xml
	FROM dual"

# Fetch as CLOB
puts conn.exec(sql).fetch[0].read

conn.logoff
puts '-'*80

From your terminal window, execute the activerecord.rb script by executing the following command:

ruby activerecord.rb

The output is shown in the screenshot.

The contents of the activerecord.rb file is as follows:

# activerecord.rb: Using ActiveRecord, the ORM module
require 'config.rb'
require 'rubygems'
require 'active_record'

# Establish a connection to Oracle
ActiveRecord::Base.establish_connection(
	:adapter => "oracle_enhanced",
	:database => DB_SERVER,
	:username => DB_USER,
	:password => DB_PASSWORD )

# Define the Classes, they are mapped to table countries and regions
class Country < ActiveRecord::Base
	belongs_to :region
end
class Region < ActiveRecord::Base
	has_many :countries
end

# Enjoy the automatic Object-Relation Mapping
cty = Country.find('CN')
puts 'CN refers to [' + cty.name + ']'
puts '[' + cty.name + '] is in [' + cty.region.name + ']'
puts

rgn = Region.find(cty.region.id)
puts 'Countries in the same region with [' + cty.name + ']'
rgn.countries.each { |country| puts ' - ' + country.name }

puts '-'*80

You will create a most basic Rails application named holapp. From your terminal window, execute the following commands:

rails holapp
cd holapp
gedit config/database.yml

Replace the development section in the file config/database.yml with the following content and then save and close the file.

development:
    adapter: oracle_enhanced
    username: rubyhol
    password: welcome
    database: localhost/orcl

You can now generate the comic model. From your terminal window, execute the following command:

ruby script/generate model comic

You want to edit the script that was generated. From your terminal window, enter the following command:

gedit db/migrate/*_create_comics.rb

Replace the self.up section with the following content and then save and close the file.

  def self.up
    create_table :comics do |t|
          t.column :name,:string
    t.column :description, :string
    t.column :price, :float
    end
  end

You can now rake the database migration to generate target tables. From your terminal window, enter the following command:

rake db:migrate

You can generate the scaffold for the comic model via the following command

ruby script/generate scaffold --skip-migration comic name:string description:string price:float

Webrick is the HTTP server library written in Ruby that uses servlets to extend its capabilities. You can start the Webrick server using the following command:

ruby script/server

You can view the application. Open a browser window and enter the following URL. Create a record by clicking New Comic to ensure that the application works correctly.

http://localhost:3000/comics

Enter comic information and click Create.

Your comic was created successfully. Click Back.

Create another comic. Select New comic.

Enter comic information and click Create.

Another comic was created. Click Back.

Both comics are listed. You can also perform an edit by selecting Edit for the first comic.

Change any of the information and click Update.

That comic was updated. Click Back.

You can also destroy a comic. Select Destroy for either comic.

Click OK to confirm.

That comic was updated. Click Back.

Stop the script server. Control+c in the window where it is running.

Open another terminal window and execute the following commands:

cd /home/rubyhol/holapp
source /etc/bashrc

Generate the scaffold with model for author. From your terminal window, execute the following command:

ruby script/generate scaffold author name:string vocation:string

Generate the scaffold with model for article. From your terminal window, execute the following command:

ruby script/generate scaffold article title:string author_id:integer abstract:text

Now you need to tell Rails about the relationship between the Article and Author models. To do so, you need to edit the model scripts that were generated. From your terminal window, enter the following command:

gedit app/models/author.rb

Replace the contents of the file with the following and then save and close the file.

class Author < ActiveRecord::Base
   has_one :article
end

You want to edit the script that was generated. From your terminal window, enter the following command:

gedit app/models/article.rb

Replace the contents of the file with the following and then save and close the file.

class Article < ActiveRecord::Base
   belongs_to :author
end

You can now rake the database migration to generate target tables. From your terminal window, enter the following command:

rake db:migrate

From your terminal window, start the script server again by executing the following command:

script/server &

You can view the application. Open a browser window and enter the following URL and then select New author.

http://localhost:3000/authors

Enter a new author and click Create.

Your author was created successfully. Click Back.

You want to create one more author. Select New author.

Enter another author and click Create.

The author was created successfully. Click Back.

The list of authors you added are displayed.

Now you want to create some articles. Enter the following URL and select New articles.

http://localhost:3000/articles

Enter a Title, specify Julia Child for the Author and any abstract. Then click Create.

The article was created successfully.

Notice that Author: 0. This is because we have an invalid value for author_id, changing "Julia Child" to 0. In the next section, you will improve the new and edit forms to allow you to pick an author by name using a popup list that show name but returns author_id in the code.

Enter the following commands in a new terminal window (since your previous window is running script/server).

cd holapp
gedit app/views/articles/new.html.erb

Replace the following line:

<%= f.text_field :author_id %>

with

<%= f.collection_select(:author_id, Author.all, :id, :name) %>

The file should now look as follows.

Do the same thing with the edit.html.erb file in the articles directory. Enter the following commands in your terminal window :

gedit app/views/articles/edit.html.erb

Replace the following line:

<%= f.text_field :author_id %>

with

<%= f.collection_select(:author_id, Author.all, :id, :name) %>

The file should now look as follows.

Enter the following URL again. Notice that the article you created previously is in the list. Select Edit.

http://localhost:3000/articles

Notice that now you can select the Author from a select list. Select Julia Child. Then click Update.

The article was updated successfully.

Notice that Author: 1. This is the value for author_id. You can change this to the name.

You will change the index.html.erb file in the articles directory. Enter the following commands in your terminal window :

gedit app/views/articles/index.html.erb

Replace the following line:

<td><%=h article.author_id %></td>

with

<td><%=h article.author.name %></td>

The file should now look as follows.

Do the same thing with the edit.html.erb file in the articles directory. Enter the following commands in your terminal window :

gedit app/views/articles/show.html.erb

Replace the following line:

<%=h article.author_id %>

with

<%=h article.author.name %>

The file should now look as follows.

Enter the following URL again. Notice that the author now appears in the list. Select Show.

http://localhost:3000/articles

You now see all the information in Show mode.

In order to show this, you need to make a change to the author.rb file. From your terminal window, enter the following command:

gedit app/models/author.rb

Replace the following:

 has_one : article 

with

 has_many : articles 

Enter the following URL again and select New article.

http://localhost:3000/articles

Create a new article. Select Julia Child as the Author and click Create.

The article was created successfully. Click Back.

Both articles are displayed.

You need to change the relationship again in the authors.rb file. From your terminal window, enter the following command:

gedit app/models/author.rb

Replace the following:

 has_many :articles 

with

 has_and_belongs_to_many :articles 

You also need to change the article.rb file. From your terminal window, enter the following command:

gedit app/models/article.rb

Replace the following:

 belongs_to :author 

with

 has_and_belongs_to_many :authors 

You now need to create a join table between Authors and Articles. From your terminal window, enter the following command:

script/generate migration CreateArticlesAuthors

You now need to update the file that was just created to create a table to store the authors and articles. From your terminal window, enter the following command:

gedit db/migrate/*_create_articles_authors.rb

Replace the following:

def self.up
end

def self.down
end

with

def self.up
  create_table :articles_authors, :id => false do |t|
    t.integer :author_id
    t.integer :article_id
    t.timestamps
  end
end
 
def self.down
  drop_table :articles_authors
end

You can now rake the database migration to generate target tables. From your terminal window, enter the following command:

rake db:migrate

Enter the following URL again. Notice that you receive an error.

http://localhost:3000/articles

Now that you have a many to many relationship, the Article model now has methods named authors and author_ids instead of author and author_id. To correct the error, you need to create and edit the popup lists and display fields you previously created to handle multiple authors in the new.html.erb file. Enter the following commands in your terminal window.

gedit app/views/articles/new.html.erb

Replace the following line:

<%= f.collection_select(:author_id, Author.all, :id, :name) %>

with

<%= f.collection_select(:author_ids, Author.all, :id, :name, {},
                        {:multiple => :multiple} ) %>

The file should now look as follows.

You need to also change the edit.html.erb file in the articles directory. Enter the following commands in your terminal window :

gedit app/views/articles/edit.html.erb

Replace the following line:

<%= f.collection_select(:author_id, Author.all, :id, :name) %>

with

<%= f.collection_select(:author_ids, Author.all, :id, :name, {},
                        {:multiple => :multiple} ) %>

The file should now look as follows.

You need to also change the index.html.erb file in the articles directory. Enter the following commands in your terminal window :

gedit app/views/articles/index.html.erb

Replace the following line:

<th>Author</th>

with

<th>Authors</th>

You need to get the names of all authors and join (concatenate) them. Replace the following line:

<td><%=h article.author.name %></td>

with

<td><%=h article.authors.map {|auth| auth.name}.join(", ") %></td>

The file should now look as follows.

And finally change the show.html.erb file in the articles directory. Enter the following commands in your terminal window :

gedit app/views/articles/show.html.erb

Replace the following lines:

<b>Author: </b>
<%=h article.author.name %>

with

<b>Authors: </b>
<%=h article.authors.map {|auth| auth.name}.join(", ") %>

The file should now look as follows.

Enter the following URL again. Notice that you see no authors in the list. That is because you switched your representation. You could have fixed this with a migration to pull data from the articles table into the articles_authors table. Instead, you will add them back using the application. Select the Edit link for the Boning a Duck title..

http://localhost:3000/articles

Select Julia Child as the Author and click Update. Notice that this select list allows you to select multiple authors instead of just one.

The article was updated successfully. Click Back.

Select the Edit link for The Importance of Butter title.

Control-Select both Authors and click Update.

Your article was updated successfully. Click Back.

Your article now shows more than one Author for an article.

Add a boolean column is_finished. From your terminal window, execute the following command:

script/generate migration AddIsFinishedToArticle is_finished:boolean

Then you need to mark the existing articles as unfinished. From your terminal window, execute the following command:

gedit db/migrate/*_add_is_finished_to_article.rb

Change the following line:

  add_column :articles, :is_finished, :boolean

to

  add_column :articles, :is_finished, :boolean, :default => false
  Article.reset_column_information
  Article.find(:all).each do |a|
    a.is_finished = false
  end

Now you will update the database. From your terminal window, enter the following command:

rake db:migrate

Next, you want to add a "Mark Finished" button which will call a new mark_finished action.. From your terminal window, enter the following command:

gedit app/views/articles/show.html.erb

Add the following line after <%= link_to 'Back', articles_path %>.

<%= button_to "Mark Finished", :action => :mark_finished, :id  => @article.id %>

You need to update the layout to show links to unfinished articles. You should keep this code separate from the main layout. You will make a new "partial template" (which starts with "_" (underscore) character to show it's not a full template). It will look up articles with is_finished false and link to them. From your terminal window, execute the following command:

gedit app/views/articles/_unfinished.html.erb

Add the following code to the file:

  <div class="unfinished-title">Unfinished Articles</div>
  <table>
    <% for article in Article.all(:conditions => {:is_finished  => false}) %>
     <tr> <%= link_to article.title, :action => :show, :id  => article.id %> </tr>
    <% end %>
  </table>

Next, you need to refer to the new "partial template" in your layout so all the pages will show it. From your terminal window, execute the following command:

gedit app/views/layouts/articles.html.erb

Put the following code right after <p style="color: green"><%= flash[:notice] %></p>

 <div id="unfinished">
  <%= render(:partial => "unfinished") %>
 </div>

Next you need to code the mark_finished action that the new button will use. From your terminal window, execute the following command:

gedit app/controllers/articles_controller.rb

Scroll down to the bottom of the file. Right before the last 'end', add the following:

    # POST /articles/mark_finished/1
    # POST /articles/mark_finished/1.xml
    def mark_finished
      @article = Article.find(params[:id])
      @article.update_attribute(:is_finished, true)
  
      respond_to do |format|
        format.html { redirect_to(:action => :show) }
        format.xml  { head :ok }
      end
    end
  
          

You have updated the database, added your button and action and added your list to the layout. You can test this functionality now. Enter the following URL in your browser:

    http://localhost:3000/articles

Notice that the Unfinished articles are listed at the top of the page. Select the Boning a Duck link.

Click the Mark Finished button.

Notice that the Boning a Duck has been removed from the list of Unfinished Articles. Select Back.

At this point you are ready to try AJAX! You want change the application so that when you hit the button, your window changes automatically, without repainting the screen. To do that, you want the "Mark Finished" button to send an AJAX command to do the mark_finished action, then send back a command to update the "Finshed articles" header. You will first change the button to a remote (AJAX) form. From your terminal window, execute the following:

  gedit app/views/articles/show.html.erb

Change the following statement from:

  <%= button_to "Mark Finished", :action => :mark_finished, :id  => @article.id %>

to

  <% form_remote_tag :url => { :action => :mark_finished, :id  => @article } do %>
   <%= submit_tag "Mark Finished" %>
  <% end %>

When you hit the button, the application will still do it's update, but instead of sending a whole page, you need to send back some JavaScript to update just the unfinished list.  With a remote javascript template, Rails can generate that JavaScript for you. From your terminal window, execute the following to create a new file:

  gedit app/views/articles/mark_finished.js.rjs

Enter the following line:

  page.replace_html("unfinished" , :partial => "unfinished")

For this to work, there has to be some default JavaScript subroutines in all pages. You need to edit your layout. From your terminal window, execute the following to create a new file:

gedit app/views/layouts/articles.html.erb

After  <%= stylesheet_link_tag 'scaffold' %> add the following line:

<%= javascript_include_tag :defaults %>

Next, you need to update the mark_finished action to use your new remote template, which it knows has same name as the action. From your terminal window, execute the following to create a new file:

gedit app/controllers/articles_controller.rb

Change the following from:

    # POST /articles/mark_finished/1
    # POST /articles/mark_finished/1.xml
    def mark_finished
      @article = Article.find(params[:id])
      @article.update_attribute(:is_finished, true)
  
      respond_to do |format|
        format.html { redirect_to(:action => :show) }
        format.xml  { head :ok }
      end
    end

          

to

    # *AJAX* /articles/mark_finished/1
    def mark_finished
      @article = Article.find(params[:id])
      @article.update_attribute(:is_finished, true)
  
      respond_to do |format|
        format.js
      end
    end

You have switched to using an AJAX request form and to send Javascript back which will update the list. Enter the following URL in your browser:

    http://localhost:3000/articles

Create a new article. Select New article.

Enter Gravity for Title, select Julia Child for Author, enter Gravity makes cakes fall for Abstract and click Create.

Notice that Gravity is listed in the list of Unfinished Articles. Select the Gravity link.

Click Mark Finished.

Gravity disappears from the list immediately. The page is not redrawn. This is the power of AJAX. On a simple screen like this the difference may not be very obvious, since the page your web server is sending is small. But on more complicated screens, with images, flash, and/or lots of data, it can make a huge difference that, with AJAX, the server sends only the changes. Then you'll really appreciate Rails' AJAX support!

You can now generate the comic model. From your terminal window, execute the following command:

ruby script/generate migration add_columns

You want to edit the script that was generated. From your terminal window, enter the following command:

gedit db/migrate/008_add_columns.rb

Replace the self.up section with the following content and then save and close the file.

  def self.up
 add_column :comics, :author, :string
  end

You can now rake the database migration to generate target tables. From your terminal window, enter the following command:

rake db:migrate

Note: To remove a column we can add a line like "remove_column :comics, :price" in the self.up method; To rename a column we can add a line like "rename_column :comics, :price, :new_price" in the self.up method; To change a column we can add a line like "change_column :comics, :price, :integer" in the self.up method.
Moreover we can rollback the Migration operation by issuing the command "rake db:migrate VERSION=xx", where the xx is the version you expect to rollback to.