Well, it took some figuring out, but I have succeeded in getting Redmine to run with an Oracle database. I did have a little help from this post, but there have been a few changes since then to deal with. Once I got all figured out, it really isn’t all that difficult to repeat (it just took a while to get there). (I’m not going to discuss setting up the Oracle adapter here, only Redmine.)
Here goes.
1. Oracle sees ” and “null” as the same thing.
The biggest issue is the fact that Oracle see ” and ‘null’ as the same thing. That said, there are a few setup scripts that will need modified. I went through db/migrate/001_setup.rb and looked for everything that sets a default value of ” and told it to allow nulls for any such column.
For example, the users table is created like this:
create_table “users”, :force => true do |t|
t.column “login”, :string, :limit => 30, :default => “”, :null => true
t.column “hashed_password”, :string, :limit => 40, :default => “”, :null => true
t.column “firstname”, :string, :limit => 30, :default => “”, :null => true
t.column “lastname”, :string, :limit => 30, :default => “”, :null => true
t.column “mail”, :string, :limit => 60, :default => “”, :null => true
t.column “mail_notification”, :boolean, :default => true, :null => false
t.column “admin”, :boolean, :default => false, :null => false
t.column “status”, :integer, :default => 1, :null => false
t.column “last_login_on”, :datetime
t.column “language”, :string, :limit => 2, :default => “”, :null => true
t.column “auth_source_id”, :integer
t.column “created_on”, :timestamp
t.column “updated_on”, :timestamp
end
…and versions:
create_table “versions”, :force => true do |t|
t.column “project_id”, :integer, :default => 0, :null => true
t.column “name”, :string, :limit => 30, :default => “”, :null => true
t.column “description”, :string, :default => “”
t.column “effective_date”, :date, :null => true
t.column “created_on”, :timestamp
t.column “updated_on”, :timestamp
end
I’m simply allowing nulls where before null was no longer accepted. This shouldn’t be a problem, its just a lacking database constraint. I’ll definitely follow up if it does become a problem.
There are a few other places where you’ll have to make similar changes:
- 074_add_auth_sources_tls.rb
- 091_change_changesets_revision_to_string.rb
- 108_add_identity_url_to_users.rb
- 20091017214336_add_missing_indexes_to_users.rb
2. Version Effective Date:
Oracle won’t like the syntax of 048_allow_null_version_effective_date.rb. I simply removed this file (it appears that this was a later change in Redmine) and made the version effective data column nullable in 001_setup.rb like this:
create_table “versions”, :force => true do |t|
t.column “project_id”, :integer, :default => 0, :null => true
t.column “name”, :string, :limit => 30, :default => “”, :null => true
t.column “description”, :string, :default => “”
t.column “effective_date”, :date, :null => true
t.column “created_on”, :timestamp
t.column “updated_on”, :timestamp
end
3. The UTF-8 Problem
Assuming your Oracle database uses AL32UTF8, you’ll want to do something like this in environment.rb:
ENV['NLS_LANG']=’american_america.AL32UTF8′
4. Oracle 30-character limitation on table names
Oracle limits table names to 30 characters in length. This is a problem in one particular Redmine db migration script: 107_add_open_id_authentication_tables.rb
I changed this file to use smaller table names:
class AddOpenIdAuthenticationTables < ActiveRecord::Migration
def self.up
create_table :open_id_auth_associations, :force => true do |t|
t.integer :issued, :lifetime
t.string :handle, :assoc_type
t.binary :server_url, :secret
end
create_table :open_id_auth_nonces, :force => true do |t|
t.integer :timestamp, :null => false
t.string :server_url, :null => true
t.string :salt, :null => false
end
end
def self.down
drop_table :open_id_authentication_associations
drop_table :open_id_authentication_nonces
end
end
5. A problem with the Activity Tab
There a problem with Oracle CLOB String comparison (as in, you can’t compare a CLOB to a String). This is documented here: http://www.redmine.org/issues/3699
Unfortunately, the Redmine response is always “Oracle is not supported.” Hey, I don’t care for being stuck with Oracle either, but some of us are. Anyway, you can take care of this problem with a simple change to the comparison. Change redmine/app/models/journal.rb:
acts_as_activity_provider :type => ‘issues’,
:permission => :view_issues,
:author_key => :user_id,
:find_options => {:include => [{:issue => :project}, :details, :user],
:conditions => “#{Journal.table_name}.journalized_type = ‘Issue’ AND” +
” (#{JournalDetail.table_name}.prop_key = ‘status_id’ OR length(#{Journal.table_name}.notes) > 0)”}
8. Change the sequences
Before you get moving, you’re probably going to want to fix the table sequences, setting them to start at 1. This is just a matter of preference, but at least for the ISSUES_SEQ, since the unique ID is used to identify the ticket, it makes sense to start with lower numbers. For some reason that I don’t understand (yet), Oracle 11 starts sequences at 10,000. I recommend doing this:
DROP SEQUENCE REDMINEUSER.ISSUES_SEQ;
CREATE SEQUENCE ISSUES_SEQ
START WITH 1
MAXVALUE 9999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;Now your tickets will start with lower numbers. As far as other sequences go, it doesn’t matter too much, since we generally don’t view the id columns.
9. Annoyingly Short VARCHAR2 Defaults
The default VARCHAR2 setting is going to be VARCHAR2(255). When it comes to certain fields, such as the project description, this is a little on the short side. I went ahead and modified the column width myself:
alter table PROJECTS modify description VARCHAR2(4000);
commit;
10. Database Trigger Needed to Copy Workflows
When creating a Tracker, Redmine allows a user to “Copy from existing workflow.” (I.e., You can copy the Tracker workflow from an existing Tracker, making the process of adding a new Tracker much more quick.) This creates a possible error when inserting the new workflow into the Workflows table because for some reason (and I’m not yet sure why), no trigger was created on the Oracle database. Without this trigger you will see a “Cannot insert null” error in your production log file if you attempt to copy a workflow. To get around this problem it is easy enough to create a trigger on the Workflows table:
CREATE OR REPLACE TRIGGER workflows_before_insert
BEFORE INSERT
ON WORKFLOWS
FOR EACH ROW
BEGIN
if :new.id is null then
SELECT workflows_seq.nextval INTO :new.ID FROM dual;
end if;
END;
/
11. Database connection
Finally, database.yml will end up looking something like this:
production:
adapter: oracle_enhanced
database:
host:
port: username: redmine_db_user
password: redmine_db_pass
These changes are very important to note should you ever have to upgrade Redmine.
Like this:
Be the first to like this post.
