| Developer: Open Source
HR Schema on Rails
by Casimir Saternos
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.Conventions that relate to legacy schema integration with Oracle and Rails include the following:
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:hr_rails.zip file, which contains all the code resources associated with this article. The extracted files have the following 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\serverThis command stars WEBBrick, a Ruby Web Server that is included with the installation. In a Web browser, enter the URL of the Web application that will be running on port 3000—for instance, http://localhost:3000.
Sample pages as they appear in the application are listed below:
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:
To accomplish these tasks, run the create_rails_db_user_and_privs.sql script and answer the prompts appropriately. For example:
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. . . .
A number of commands will be run and objects created. Review the results in the create_db_objects.log file, which is created in your working directory.
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."
A general outline of the development process used to create the sample application is as follows:
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.Employee Model validation ensures that the required fields are included, string data fields do not exceed the maximum length, emails are unique, and salaries are numeric. (The views implicitly validate the departments and managers by forcing the user to select data from a drop-down).
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 => trueIn addition, there is custom validation code in the Employee model to ensure that the salary and commission are set within a valid range:
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.The query to construct the corporate hierarchy is as follows:
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.
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.
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.
Casimir Saternos is an Oracle-certified DBA, an IBM-certified enterprise developer, and a Sun-certified Java programmer, based in Allentown, Pennsylvania.