DOWNLOAD Oracle Database XE Ruby on Rails Sample code

TAGS xe, opensource, rubyonrails, All

HR Schema on Rails

Learn techniques for creating a Ruby on Rails Web application that utilizes a legacy schema.

Updated November 2009

Oracle Database is a product with a distinguished past and a bright future. It boasts innovative, state-of-the-art features as well as a history that predates many modern technologies. Applications have come and gone, but the data of organizations has lived on in Oracle databases that have migrated through various versions. This has resulted in a technical challenge, due to the large number of legacy schemas that exist today. Many organizations are faced with the challenge of making this data more widely available to their user base.

Ruby on Rails (or simply "Rails") is an established application framework that developers, DBAs, and system administrators can use to quickly develop web applications. The framework is open source and growing in popularity. It allows you to prototype and develop web applications with database integration in a fraction of the time possible with other frameworks.

Most of the Rails examples and tutorials available today involve the creation of a new database schema with tables and columns that adhere to structures and naming conventions that the framework expects. These tutorials also assume a rather simple object-relational mapping that is not feasible when working with legacy schemas. However, both the framework and Oracle Database offer features that can be leveraged to allow the creation of Web applications that are integrated with legacy schemas.

This article describes the techniques for creating a Web front end to the Oracle demonstration schema HR. The sample code for this article includes a complete Ruby on Rails Web Application that was developed on the HR schema running on Oracle Database 10g Express Edition (XE). The app was developed on Windows but works on other environments that support rails and Oracle. Non-Windows users should convert all backslashes to forward slashes when performing commands described in this article.

Why Ruby on Rails?

Numerous platforms are available for Web development, including Microsoft .Net, Java 2 Enterprise Edition, and PHP. Furthermore, Oracle Application Express, a feature of Oracle Database 10g and later (also available standalone), is widely considered an ideal platform for quickly developing Web applications with Oracle Database integration. So why has there been so much interest in a new platform using a relatively unfamiliar language?

By design, Rails allows extremely rapid development of Web applications. It accomplishes this by adhering to conventions rather than forcing a developer to make all of the configuration decisions up front. Once Ruby and the various required packages are installed, you can create a minimal Web application by running less than half a dozen commands and editing one file (database.yml). Included code generators or libraries address many of the most common tasks in Web development. The Ruby language has the object-oriented capabilities of a language like Java but can also be used for creating simple scripts. The range of capabilities of the language enables you to create applications without having to resort to numerous divergent technologies to accomplish the tasks at hand.

Rails has gained an almost legendary reputation for promoting rapid application development. Part of the way this goal has been accomplished is by requiring adherence to set coding standards and naming conventions. Besides being required by the Rails framework, these standards and conventions are generally good coding practices.

• Tables are named in using the plural form of the model they represent. For example, an "employee" model maps to an "employees" table.
• All tables that contain data that will be updated contain a primary key called "id."
• In the case of Oracle Database, this primary key is incremented using a sequence with a name based on the table it increments. A table named "employees" that contains an "id" will be incremented by a sequence named "employee_seq."

There are many ways to proceed with creating Rails applications that run against legacy schema. Some of the techniques involve the use of Rails features; others involve the modification of underlying database objects. Rather than attempt to describe every possible way to proceed, the demo Web application is an attempt to leverage the power of both Oracle and Rails in such a way as to provide a reasonable solution that is clear, quickly developed, and easily maintained.

The Rails community characterizes the Ruby on Rails framework as "opinionated." This term is intended as a positive description, indicating that the design of the software promotes adherence to standards and practices. However, such a goal raises the concern that the framework will limit or constrain application development. Our application includes the implementation of a few requirements that illustrate the flexibility of the framework.

The Ruby on Rails framework provides the tools needed to rapidly construct a Web application on a new or a legacy database schema. Consider the output of rake (a Ruby build utility), which provides some idea of the effort involved in creating this project:

C:\hr_rails\hr>rake stats
(in E:/ruby_apps/hr_rails/hr)
+----------------------+-------+-------+---------+---------+-----+-------+
| Name                 | Lines |   LOC | Classes | Methods | M/C | LOC/M |
+----------------------+-------+-------+---------+---------+-----+-------+
| Controllers          |   243 |   201 |       7 |      35 |   5 |     3 |
| Helpers              |     0 |     0 |       0 |       0 |   0 |     0 |
| Models               |   167 |   144 |       6 |       8 |   1 |    16 |
| Libraries            |     0 |     0 |       0 |       0 |   0 |     0 |
| Functional tests     |   440 |   315 |      10 |      50 |   5 |     4 |
| Unit tests           |    50 |    35 |       5 |       5 |   1 |     5 |
+----------------------+-------+-------+---------+---------+-----+-------+
| Total                |   900 |   695 |      28 |      98 |   3 |     5 |
+----------------------+-------+-------+---------+---------+-----+-------+
Code LOC: 345     Test LOC: 350     Code to Test Ratio: 1:0.9

Fewer than 30 classes and 350 lines of code were required to develop this application!

Installing and Running the Web Application

Installation of Oracle Database XE, Ruby, Rails, and the OCI8 packages are prerequisites for installing this application. Because describing each process would require a series of articles. I will simply refer you to installation resources:

• Installing Oracle Database XE
• Installing Ruby and Rails
• Installing OCI8

Figure 1: Directory Structure

The directory structure is worth noting as it is a standard structure for all Rails applications. The name of the application (hr, in this case) appears as the root directory. The models, views, and controllers directories within the app directory contain the bulk of the application code. The config directory contains the database.yml file (used to configure database connections) and the routes.rb file (used to define the page that will serve as the index to the application). The public folder contains directories with Web resources, including scripts, images, and stylesheets. Although it's not discussed in this article, a test directory contains resources related to unit-testing the application.

With the software installed, the schema created, and the Rails application configured, the application is ready to run. From the hr directory, run:

ruby script\server

Sample pages as they appear in the application are listed below:

Figure 2: Main Page

Figure 3: Regional Compensation Report

Figure 4: Employee Listing

Figure 5: Employee Edit

Next, let's take a look at database and schema configuration for the application.

Database and HR Schema Configuration

The SQL*Plus Client (or Oracle Instant Client Software) must be installed on the server where Rails is installed. A TNSNAMES.ora entry referencing the database containing the HR schema to be used must be created. For those new to Oracle, the TNSNAMES.ora file contains the connection information used to connect to an oracle database. The database could be on the same machine or on a remote server. Connection information includes the server name, database name and database port in use. This information is used by the Ruby OCI8 package to allow Rails to communicate with the database. The OCI8 layer is a layer written in Ruby that utilizes the underlying Oracle database configuration (specified in TNSNAMES.ora) to make the database connection.

To create the schema objects required by the Web application, perform the following tasks first:

• Create a new user (designated as HR_RAILS here) Note: The included script will drop the user if it exists and recreate it.
• Grant the new user the appropriate resources and privileges to create its own schema and access the HR schema.
• Modify HR schema in a few areas:
  • Create sequences to populate primary keys.
  • One trigger needs to be modified and another created. The triggers are required due to the job_history table and related constraints. The modified trigger checks to determine if an employee's job_id or department_id has changed before inserting a history record. The new trigger deletes an employee's job history record if the employee is deleted. The trigger could be modified to archive this data if required.
• Create synonyms as well as views for the HR_RAILS schema that adhere to Rails naming conventions.

C:\hr_rails\hr>sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 8 12:31:01 2006
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
SQL> @create_rails_db_user_and_privs
Enter the DBA user:
system
Enter the DBA password:
notmanager
Enter the Database Name ( Oracle SID):
xe
Enter the (new) rails user (user will be DROPPED and created):
hr_rails
Enter the rails user's password:
hr_rails
Enter the HR user (to grant the rails user privs):
hr
Enter the HR user's password:
hr
Connected.
.
.
.

Integration and Application Databases. Object-oriented programming expert Martin Fowler makes a useful distinction between application and integration databases. In general terms, an application database is a database that is used by a single application, while an integration database is accessed and utilized by a number of applications. Rails development is oriented toward the development of applications using an application database. The current project shows an approach to utilizing Rails with an integration database.

In this application, rather than directly accessing the schema using the HR user, a new user is created (identified as hr_rails in this article). Having a unique user dedicated to the Rails application provides visibility to the DBA who needs to differentiate between the applications accessing the database schema in question. In addition, Rails encourages the use of table and column names that conform to standard naming conventions. The new schema will contain views that correspond to the underlying HR tables. These views are inherently updatable rather than query-only and so are used for all insert, update, and delete operations as well. In essence, the implementation strives to create an application database schema that is somewhat independent of the integration database schema (to use Fowler's terminology).

The use of Oracle's "Inherently Updatable" views provides a useful interface for taking advantage of ActiveRecord's object-relational capabilities. Again, these views serve to format the table structure and data in a form that is most useful for Rails access. They also isolate the rails application so that there is increased auditing visibility for the Rails database user and other application users that access the HR schema. All the power of Oracle SQL, including Oracle's extensions to the language and functions, can be leveraged using find_by_sql.

For reference within the application, a portion of an Entity Relationship Diagram can be found as an image on the About This Site page. There is also a link from this page (as well as from the main page) to a page that queries the Oracle Data Dictionary to display the view names along with column names and types.

Changes to the HR Schema

Although the HR schema remains fundamentally unchanged, the application did require some adjustments. I added several sequences that Rails uses to populate primary keys of tables. Sequences of this kind might need to be accessed by other applications and so are included in the HR schema itself.

I modified one trigger that already exists in the HR schema (UPDATE_JOB_HISTORY) to call the add_job_history procedure only if the job or department associated with the employee changes. I made this change to limit the situations when this trigger is called to fields that are actually related to the emplyee's job history. I added a second trigger (DELETE_JOB_HISTORY) that deletes the employee's job history records when an employee is deleted. This trigger is required to allow the employee to be deleted because of the way the database constraints are set up. For example, in a production application, the JOB_HISTORY might be archived, or an Employee record might have the status "inactive."

Development Process

A general outline of the development process used to create the sample application is as follows:

• Create and update the HR schema.
• Create the HR_RAILS user, schema, and database objects.
• Create a Rails application by running the following command:

            rails hr -database oracle
  

• Configure the database.yml file so that the development, test, and production connections would connect to the correct database with a valid username and password. The database.yml file included with the application download (found in the \hr\config directory) references a database called XE accessed by the HR_RAILS user using HR_RAILS for the password. These values need to be modified to values that you used when creating the schema. This file is used by the Ruby ActiveRecord class to make database connections. The --database oracle argument is not required, but it results in a database.yml that includes some comments that are specific to the use of Oracle databases.
• Generate "scaffolds" for the tables. These scaffolds—Ruby models, views, and controllers—provide the ability to view, create, update, and delete records on the underlying tables. These scaffolds were created by running scripts that had been created as part of the Rails application.

  ruby script\generate scaffold <name>
  

• What's New with Scaffolds? When generating scaffolds, you are now required to list the field names and parameters; e.g.: ruby script\generate scaffold region name:string There are a number of different data types available (but only a few are usually required such as :datetime, :float, :integer and :string). Depending on the versions in use, you might get an error on the generated view page's link_to tag. These can be fixed by explicitly specifying the id as String as follows: <td><%= link_to 'Show', region %></td> <%= link_to 'Show', region.id.to_i.to_s %> This problem can also be addressed by using the enhanced Oracle ActiveRecord Adapter mentioned previously. The configuration steps include: Downloading/installing the gem Modifying your environment.rb to include the gem: config.gem 'activerecord-oracle_enhanced-adapter', :lib => 'active_record/connection_adapters/oracle_enhanced_adapter' Modifying your database.yml to use the oracle_enhanced adapter rather than oci8: adapter: oracle_enhanced Creating a file called oracle_advanced.rb in the config/initializers directory. This file contains whichever options you would like to include. To address the error mentioned above, specify the following: self.emulate_integers_by_column_name = true

• Create a layout for the site. This provides the header, navigations on the left, and the footer portions that you see on each page of the site.
• Modify the routes.rb configuration so that the first page of the site was mapped (rather than having to enter an extended URL).
• Perform multiple iterations involving modifications of models (if additional data is needed), views (trying to make the interface user-friendly and functional), and controllers (to get data to the views). This is the actual manual coding that was completed to change the style of the site and create functionality specific to the application at hand.

Scaffolds. During the development process, a complete Model, View, and Controller for each table was generated. Although the full scaffold was generated for all of the models in use, the final version does not include the full functionality available. A user can view only Region and Country listings. Location, Department, and Employee listings can be viewed as well as inserted, updated, and deleted.

Models. There are similar features to each of the Model classes. Representative examples are presented below. You can consult the code to see the others in detail.

Input Field Validation. The Rails framework seeks to centralize validation code in the Model class. There are a number of benefits to this approach, most notably the fact that all insert and update processing that utilizes the Model class will include the constraints defined and will return a consistent message to the user should the validation rule fail. However, the database itself can (and should) also be used to constrain data. This has the advantage of restricting the data entered into the table by any application or SQL statement. In addition, the View layer can be used to enforce validation by its very design. A well designed GUI will restrict the user in such a way that will prevent him from choosing invalid options. A text field with a set length will prevent strings that exceed the maximum allowable length. A drop down box will prevent a user from entering unacceptable entries in a field that is required to contain foreign key entries.

validates_presence_of :email, :hire_date, :last_name, :first_name, :phone_number, :salary
validates_uniqueness_of :email
validates_length_of :first_name, :maximum => 20
validates_length_of :last_name, :maximum => 25
validates_length_of :email, :maximum => 25
validates_length_of :phone_number, :maximum => 20
validates_numericality_of :salary, :only_integer => true

def validate  
     if salary != nil   
          if salary < 1
                  errors.add(:salary, "must be positive.")
          end
          
          if salary > 999999
                  errors.add(:salary,"must be less than 999999")
          end     
     end

     if commission !=nil
          unless commission >= 0 && commission < 1
                          errors.add(:commission, "must be greater than 0 and less than 1.")
          end            
     end
end

Queries. There is no SQL visible in the code related to the INSERT, UPDATE, DELETE, and SELECT * queries that are used by each of the Model classes. This code is generated behind the scenes. However, a common characteristic of legacy schemas is the requirement of complex SQL to retrieve or summarize data. Furthermore, Oracle Database provides powerful functionality not available in other RDBMSs. For example, it is possible to produce a tree-like representation of data using a single hierarchical query. This type of functionality would require many queries and complex coding in other database systems. Rails provides a means to execute Oracle specific SQL statements by defining a find_by_sql function in the Model class.

SELECT tree.*,  
           LEAD (hierarchy_level) OVER (ORDER BY seq) next_level 
FROM (  
         SELECT CONNECT_BY_ROOT last_name top_node_name, 
                        (last_name || ', ' || first_name ||' ('||job_title||')') employee_name,  
                        emp.id employee_id,
                        SYS_CONNECT_BY_PATH (last_name, '->') node_path,
                        LEVEL hierarchy_level,
                        ROWNUM seq
         FROM(   
                 SELECT e.*, j.job_title
                 FROM employees e, jobs j
                 WHERE e.job_id = j.id
                 ) emp                                    
         START WITH emp.job_title= 'President'
         CONNECT BY PRIOR emp.id = manager_id                                 
         ORDER SIBLINGS BY emp.id                                 
         ) tree                                 
ORDER BY seq

Figure 6: Employee tree view produced by an Oracle Hierarchical Query

A number of relationships are defined in relation to the Employee model. An employee belongs to a department and reports to a manager. At the database level, foreign key constraints exist that reflect these relationships. The Rails framework also specifies that these relationships are one-to-many. Manager is a field within the Employees table itself.

belongs_to :department                                                                   
belongs_to      :manager,
                :class_name => "Employee",
                :foreign_key => "manager_id"
belongs_to :job                                                                          
has_many :department                                                        
has_many :managed_employee,
             :class_name => "Employee",
             :foreign_key => "manager_id"

These relationships set up a number of convenience methods that allow the programmer easy access to related data through the model objects.

Controller. There are relatively few modifications to the generated controller objects. The main controller has methods added for the main and about views. The employee view includes a couple of additional calls to models used to populate manager and department drop-downs. The remaining code is simply a portion of the automatically generated scaffold.

The config\routes.rb file was modified so that http://localhost:3000 maps to the main page for the application.

View. All of the pages in the site have a common header, left navigation bar, and footer. These are consolidated into a single layout file. By default, Rails looks for a layout called <controller_name>_layout.<xml or rhtml> in the /app/views/layout directory. However if you create a layout called application in the layouts directory, it will be used by controllers that do not have a layout based on the controller name.

The views take advantage of a number of Rails classes designed to minimize the amount of code that appears in a view. The numeric data was formatted as currency using the number_to_currency helper or the number_with_precision helper.

Rails also includes some JavaScript libraries that provide a number of services related to AJAX support and DOM manipulation. Although these are not used extensively, there are visual effects that are used to make the images on most of the pages disappear if they are clicked. A few additional JavaScript functions are included, which allow the mouseover button effects and the corporate hierarchy drill-down.

Advanced Oracle Functionality

The sample application takes advantage of the Rails ability to automatically generate the trivial SQL statements that are required to insert, update, and delete by primary key as well as to select * from a table. However, several queries that use Oracle SQL are included as well. The region.rb model contains SQL used to create the regional compensation report (GROUP BY ROLLUP) as well as the schema report that lists the views that the application uses (LAG OVER). The employee.rb model includes the query used to construct the corporate hierarchy. This query uses CONNECT BY to perform a recursive search as well as LEAD OVER.

Conclusion

HR will continue to provide a popular schema for the demonstration of SQL functionality. As demonstrated with this application, tt also serves as an example of how Ruby on Rails can be used to create Web applications that utilize legacy schemas.