class AddUserCredentialsColumns < ActiveRecord::Migration
  def self.up
    add_column    :users, :userid,      :string, :limit => 8
    add_column    :users, :hash_passwd, :string
    add_column    :users, :salt,        :string
  end

  def self.down
    remove_column :users, :userid
    remove_column :users, :hash_passwd
    remove_column :users, :salt
  end
end

                
class CreateSubjectsSubscriptions < ActiveRecord::Migration
  def self.up
    create_table :subjects_subscriptions,  :id   => false do |t|
      t.column :subscription_id, :integer, :null => false
      t.column :subject_id,      :integer, :null => false
    end  
    remove_column :subjects, :subscription_id
    add_index     :subjects_subscriptions, :subject_id
  end

  def self.down
    drop_table    :subjects_subscriptions
    add_column    :subjects, :subscription_id, :integer
  end
end

                

  
    
      

        Nashville
        TN
        46808
      
      
        
          
            2434901
          
          
            9043272
          
        
        
          
            1915216
                    
        
      
      2007-05-12
    
  

                
class CreateXmlContents < ActiveRecord::Migration
  def self.up
    drop_table :documents
    create_table :documents do |t|
      t.column :name,         :string,   :null => false
      t.column :size,         :integer,  :null => false
      t.column :data,         :binary,   :limit => 2.megabytes
      t.column :content_type, :string,   :null => false
      t.column :created_at,   :timestamp
      t.column :updated_at,   :timestamp
      t.column :platform,     :string,   :limit =>10
      t.column :subject_id,   :integer
      t.column :user_id,      :integer
    end
    create_table :xml_contents do |t|
      t.column :name,        :string
      t.column :data,        :xml,       :null => false
      t.column :document_id, :integer
    end
  end

  def self.down
    drop_table :documents
    drop_table :xml_contents
    create_table :documents do |t|
      t.column :name,           :string,   :null => false
      t.column :size,           :integer,  :null => false
      t.column :data,           :binary,   :limit => 2.megabytes
      t.column :content_type,   :string,   :null => false
      t.column :created_at,     :timestamp
      t.column :updated_at,     :timestamp
      t.column :platform,       :string,   :limit =>10
      t.column :subject_id,     :integer
      t.column :user_id,        :integer
      t.column :xmldata,        :xml,      :null => false
    end
  end
end

                
self.name = File.basename(doc_field.original_filename).gsub(/[^\w._-]/, '')
self.content_type  = doc_field.content_type.chomp
self.size = doc_field.size
self.created_at = Time.now

                
unless self.content_type.include?('text/xml')
  self.data = doc_field.read
else
  content = XmlContent.new
  content.name = self.name
  content.data = doc_field.read
  self.xml_content = content
end

                
class Document < ActiveRecord::Base
  belongs_to  :user
  belongs_to  :subject
  has_one     :xml_content

  #           values  displayed  |  stored
  PLATFORM_TYPES = [ ['Neutral',    'Any'],
                     ['Windows',    'WinXP'],
                     ['Mac OS X',   'MacOS'],
                     ['Linux',      'Linux']]


  def uploaded_doc=(doc_field)
    self.name = File.basename(doc_field.original_filename).gsub(/[^\w._-]/, '')
    self.content_type  = doc_field.content_type.chomp
    self.size = doc_field.size
    self.created_at = Time.now
    unless self.content_type.include?('text/xml')
      self.data = doc_field.read
    else
      content = XmlContent.new
      content.name = self.name
      content.data = doc_field.read
      self.xml_content = content
    end
  end
end

                

  <% @documents.each_with_index do |document,i| %>
    <% row_class = i%2 ==0 ? "even" : "odd" %>
    
  <% if document.subject %>
    
  <% else %>
    
  <% end %>
  <% if document.user %>
    
  <% else %>
    
  <% end %>
    
  <% end %>

  

   
ID
   
Document name
   
Subject
   
Shared by
   
Size
   
Update at
   
Platform
   

   

   

  

    
<%= document.id %>
    
<%= document.name %>
<%= link_to "#{document.subject.name}", 
                     :controller => 'subjects', :action => 'list'  %>
<%= link_to "#{document.user.userid}", 
                     :controller => 'users', :action => 'list'  %>
<%= number_to_human_size( document.size ) %>
    
<%= document.updated_at.strftime("%d/%m/%Y %I:%M%p") %>
    
<%= document.platform. %>
    
<%= link_to 'Show', :action => 'show', :id => document %>
    
<%= link_to 'Edit', :action => 'edit', :id => document %>
    
<%= link_to 'Remove', { :action => 'destroy', :id => document }, 
                     :confirm => 'Are you sure?', :method => :post %>
    

                
def show
  @document = Document.find(params[:id])
  doc_type = @document.content_type
  unless doc_type.include?('text/xml')
    doc_content = @document.data
  else
    doc_content = @document.xml_content.data
  end
  send_data(doc_content,
            :filename => @document.name,
            :type => doc_type,
            :disposition => "inline")
end

                
def upload
  if params[:document][:uploaded_doc].to_s.empty?
    flash[:notice] = "Please provide a file for upload"
    redirect_to(:action => "new" )
  else
    @document = Document.new(params[:document])
    @subject = params[:subject_name] && params[:subject_name].empty? ? 
               Subject.new :
               Subject.find_by_name(params[:subject_name])

    Document.transaction do
      User.find(session[:user_id]).documents << @document
      @subject.documents << @document
      @subject.size = @subject.documents.size
      if @subject.new_record?
        @subject.name = params[:subject][:name]
        @subject.tag = params[:subject][:tag]
        @subject.description = params[:subject][:description]
        @subject.save
      end

      if @document.save
        flash[:notice] = "Document #{@document.name} successfully created."
        if @document.subject.subscriptions
          SubscriptionMailer.deliver_notify(@document)
        end
        redirect_to :action => 'list'
      else
        render :action => 'new'
      end
    end
  end

                
XQUERY

   declare default element namespace "http://www.ibm.com/developerworks";
   { for $c in fn:distinct-values(
         db2-fn:xmlcolumn(
        'XML_CONTENTS.DATA')/marketinfo/sales/customer/address/city)
         order by $c
      return {$c}
   }

                

  Atlanta
  Augusta
  Austin
  Baton Rouge
   ... 

                
class DocumentsController < ApplicationController
  [...]
  def upload
  [...]
      @document = Document.new(params[:document])
      @subject = params[:subject_name] && params[:subject_name].empty? ? 
                 Subject.new :
                 Subject.find_by_name(params[:subject_name])

      Document.transaction do
        User.find(session[:user_id]).documents << @document
        @subject.documents << @document
        @subject.size = @subject.documents.size
        if @subject.new_record?
          @subject.name        = params[:subject][:name]
          @subject.tag         = params[:subject][:tag]
          @subject.description = params[:subject][:description]
          @subject.save
        end
  
        if @document.save
          flash[:notice] = "Document #{@document.name} successfully created."
  [...]
end

                
class DocumentsController < ApplicationController
  [...]
  def update
    @document = Document.find(params[:document][:id])
    if @document.update_attributes(params[:document])
      flash[:notice] = 'Document was successfully updated.'
      redirect_to :action => 'show', :id => @document
    else
      render :action => 'edit'
    end
  end
  [...]
end

                
class DocumentsController < ApplicationController
  [...]
  def destroy
    Document.find(params[:id]).destroy
    redirect_to :action => 'list'
  end
end

                
XQUERY
declare default element namespace "http://www.ibm.com/developerworks";

  { let $categories := fn:distinct-values( 
      for $c in db2-fn:xmlcolumn( "XML_CONTENTS.DATA")/marketinfo/sales/customer
          where xs:date($c/last_purchase) >  xs:date("2007-04-15")
            and xs:date($c/last_purchase) <= xs:date("2007-04-30")
         return $c/categories/category/@type)
      for $c in $categories 
         return {$c}
  }

                

  Home
  Electronics
  Apparel
  Gifts & Flowers
  Baby

                
select name, xmlquery( 
               'declare default element namespace "http://www.ibm.com/developerworks";
                 let $total := sum (
                                    for $i in $t//category 
                                       let $sum := count($i/item)
                                       where $i/@type = "Jewelry"
                                    return $sum
                                    )
                return {$total}'
                   passing data as "t"
                      ) as data 
      from teamroom.documents
      where xmlexists( 
                'declare default element namespace "http://www.ibm.com/developerworks";
                 $t/marketinfo/sales/customer/address[zip = "79081"]'
                    passing data as "t"
                      )

                
SELECT X.CITY, X.STATE, X.ZIP, X.LAST_PURCHASE FROM 
TEAMROOM.XML_CONTENTS, 
    XMLTABLE (XMLNAMESPACES (DEFAULT 'http://www.ibm.com/developerworks'), 
       'db2-fn:xmlcolumn("XML_CONTENTS.DATA")//customer' 
       COLUMNS 
       "CITY"          CHAR(16) PATH './address/city', 
       "STATE"         CHAR(16) PATH './address/state', 
       "ZIP"           CHAR(6)  PATH './address/zip', 
       "LAST_PURCHASE" DATE     PATH './last_purchase') as X

                
Baton Rouge      LA               77888  03/10/2007
Baton Rouge      LA               14257  01/07/2007
Richmond         VA               78045  01/26/2007
Oklahoma City    OK               71107  04/13/2007
Tallahassee      FL               41720  04/25/2007
Richmond         VA               39591  03/25/2007
Richmond         VA               36522  03/23/2007
Richmond         VA               32230  02/12/2007
Charleston       WV               33015  02/12/2007
Columbia         SC               72647  01/11/2007
Raleigh          NC               11238  04/02/2007
Nashville        TN               21245  01/06/2007
Fankfort         KY               53793  04/18/2007
Austin           TX               35462  03/13/2007
Columbia         SC               68359  01/01/2007
Jackson          MS               25770  01/20/2007
Little Rock      AR               46342  03/10/2007
Tallahassee      FL               54306  01/20/2007
Charleston       WV               44339  02/20/2007
Frankfort        KY               92403  02/27/2007

                
INSERT INTO TEAMROOM.CUSTOMER_INFOS
SELECT X.CITY, X.STATE, X.ZIP, X.LAST_PURCHASE FROM 
TEAMROOM.XML_CONTENTS, 
    XMLTABLE (XMLNAMESPACES (DEFAULT 'http://www.ibm.com/developerworks'), 
       'db2-fn:xmlcolumn("XML_CONTENTS.DATA")//customer' 
       COLUMNS 
       "CITY" VARCHAR(16)    PATH './address/city', 
       "STATE" CHAR(16)      PATH './address/state', 
       "ZIP" CHAR(6)         PATH './address/zip', 
       "LAST_PURCHASE" DATE  PATH './last_purchase') as X

                
CREATE PROCEDURE GET_ATOM_FEED (  )
  DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure 
------------------------------------------------------------------------
P1: BEGIN
  -- Declare cursor
  DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT XMLSERIALIZE(
      XMLDOCUMENT(
        XMLELEMENT (NAME "feed",
          XMLNAMESPACES(DEFAULT ''),
          XMLCONCAT (
            XMLELEMENT (NAME "id", 'http://localhost:3000/documents'),
            XMLELEMENT (NAME "title", 'Teamroom Documents'),
            XMLELEMENT (NAME "updated", CURRENT TIMESTAMP),
            XMLELEMENT (NAME "link", 
                        XMLATTRIBUTES('http://localhost:3000/documents/atom_feed' 
                        as "href", 'self' as "rel")),
            XMLELEMENT (NAME "author", 
              XMLCONCAT(
                XMLELEMENT (NAME "name", 'TeamRoom'),
                XMLELEMENT (NAME "email", 'teamroom@developerWorks.ibm.com')
              )
            ),
            XMLAGG (
              XMLELEMENT (NAME "entry",
                XMLCONCAT (
                    XMLELEMENT (NAME "title", name),
                    XMLELEMENT (NAME "id", 'http://localhost:3000/documents/show/' 
                               || CHAR(id)),
                    XMLELEMENT (NAME "updated", updated_at),
                    XMLELEMENT (NAME "link", 'http://localhost:3000/documents/show/' 
                               || CHAR(id)),
                    XMLELEMENT (NAME "category", category),
                    XMLELEMENT (NAME "summary", content_type),
                    XMLELEMENT (NAME "content", XMLATTRIBUTES('text' as "type"), content)
                )
              )
            )
          )
        )
      ) 
      AS CLOB INCLUDING XMLDECLARATION
    )
      FROM (SELECT d.id as id, d.name as name, d.content_type as content_type, 
            d.updated_at as updated_at, s.name as category, s.description as content
          FROM DOCUMENTS d, SUBJECTS s 
          WHERE d.subject_id = s.id
          ORDER BY d.updated_at DESC 
          FETCH FIRST 10 ROWS ONLY) 
      AS doc_list;
      
  -- Cursor left open for client application
  OPEN cursor1;
END P1

                
class Document < ActiveRecord::Base
  [...]
  def atom_feed
    feed = Document.find_by_sql("call teamroom.get_atom_feed()")
    content = feed[0].attributes["1"]
    send_data(content,
              :filename => 'TeamRoomFeed.atom',
              :type => 'text/xml',
              :disposition => "inline")
  end
  [...]
end