Redmine(레드마인) 에서 Oracle(오라클) 사용하기 I.lib()/I.lib(Oracle)2013. 11. 21. 19:21
Redmine 에서 Back-End 로 사용하는 DB중에는 Oracle 이 공식적으로는 없다.
하지만 블로그 링크를 통해 아래 경로를 추천하였으며 그에 대한 정리를 한다.
블로그 경로 : [http://matthewrupert.net/2011/03/11/running-redmine-with-an-oracle-backend/]
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
-- 오라클에서는 " 라는 값도 null로 인식한다는 뜻 같은데.. 정확하게는 알수가 없어 위에 시키는대로 따라하였다.
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
-- 오라클에서는 필요 없는 기능이라는 의미 같은데.. 일단 삭제하라는 파일은 삭제하였고 versions 부분은 1번에서
동일하게 변경하였기 때문에 수행하지 않았다.
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′
-- UTF-8 을 사용할때의 옵션같다. 저는 한글을 쓰기 때문이 이 기능에 대해서 적용하지 않았습니다
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
endcreate_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
enddef 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;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.
CREATE SEQUENCE ISSUES_SEQ
START WITH 1
MAXVALUE 9999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
-- 시키는대로 따라한다.. (사실 난 안해도 잘돌아가서 안했지만;;)
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;
//2013.12.10 추가. 아래 테이블의 컬럼값도 변경되야 한다.
alter table journal_details modify VALUE VARCHAR2(4000);
alter table journal_details modify OLD_VALUE VARCHAR2(4000);
-- 이미 버그로 인식해서 수정했던 부분이었기 때문에 적용하진 않았지만 꼭 해둘필요가 있다.
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.
--위같이 설정하면 끝!!!!!!! 인줄 알았지만 버젼 업은 되었지만 블로그 글은 업이 되지 않았다는것.
추가적으로 아래 내용도 적용한다.
12. app/views/issues/_attributes.html.erb 수정
|
|
|
|
|
-- return 줄을 추가한다.
이상입니다.
댓글 및 질문 받지만 도와드릴수 있을지는 미정입니다^^;
'I.lib() > I.lib(Oracle)' 카테고리의 다른 글
| [펌] [ORACLE] 프로시저 오렌지에서 실행 (0) | 2014.12.23 |
|---|---|
| CentOS 에 Oracle 설치하기 (0) | 2014.01.06 |
| Oracle LAG 사용 방법 (2) | 2013.09.27 |
| Merge 사용중 오류 사항 (ORA-30926,ORA-00001) (1) | 2013.08.07 |
| 오라클 Sequence( 시퀀스) 권한 부여 (0) | 2013.04.30 |
