SQLite, PostgreSQL 차이 : Like / iLike

해당 글은 https://blog.naver.com/kbs4674/221682881216 로 부터 게시글이 이전되었습니다.

 

http://www.damda.info

 

" Damda 홈페이지를 운영하면서 겪었던 이슈 중 하나인 SQL 검색어 문법으로 쓰이는 like 문법에 대해 소개하는 글입니다. "

 

Damda 홈페이지 메인에는 대학교를 검색하는 창이 존재합니다.

그런데 홈페이지를 운영하게 되면서 해당 검색어 창에 대해 이슈를 겪게 되었습니다.

 

 문제 찾아내기 

1. Rails 내 검색 문법

일단, Rails 내에서 검색 문법에 쓰인 코드는 다음과 같습니다.

@searches = Univ.where("univ_name_eng like ?", "%#{params[:search_univ]}%")

 

2. 데이터 애트리뷰트 설정

 

위의 목록의 DB 중 univ_name_eng 애트리뷰트에 해당되는 자료들을 검색해볼 겁니다.

 

3. 검색

 

검색창에 national 문구를 넣고 검색을 합니다.

 SQLite 기반 검색결과 

"National" 단어 검색결과 (좌 : SQLite3 / 우 : PostgreSQL)

검색 결과, SQLite 에서는 원하는 결과가 나온 반면, PostgreSQL에서는 검색결과가 하나도 안나온게 확인됩니다.

 

처음에는 본래의 운영 사이트에서 검색이 안되었을 때 '아예 검색이 안되나?' 하는 의문을 품고 이번에는 national이 아닌 National 로 첫 단어를 대문자로 바꿔서 다시 검색해봤습니다.

 

PostgreSQL 기반 검색결과 (National 단어  검색)

이번에는 올바른 검색결과가 나온게 확인되었습니다.

저는 이 문제를 1주일 전에서야 발견을 하게되고, 부랴부랴 원인을 찾기 시작했습니다.

 

그리고, 답을 찾아냈습니다.

 

 

 문제 해결 

원인은 SQL 쿼리 중 like 문법에 있었습니다.

SQLite는 like를 써도 대/소문자 구분에 대해서는 상관을 안했으나, PostgreSQL 기반에서는 대/소문자 구분이 엄격했었습니다.

# @searches = Univ.where("univ_name_eng like ?", "%#{params[:search_univ]}%")
=> @searches = Univ.where("univ_name_eng ilike ?", "%#{params[:search_univ]}%")

 

PostgreSQL에서는 like를 ilike 라고 고쳐주면 간단히 해결되었었습니다.

뭔가 허무하게 해결되었네요..ㅎㅎ

 

PostgreSQL 기반 검색결과 ( national  단어 검색)

위와같이 검색결과가 제대로 잘 보이는게 확인됩니다.

하지만 또다른 문제가 발생하게 됩니다.

 

SQLite 기반에서 ilike 문법 사용 시 발생되는 오류

테스트 서버는 SQLite 기반인데, SQLite에서는  ilike  문법이 존재하지 않다보니 위와같은 오류를 내뱉게 됩니다..

저는 DB에 따라 문법이 상이하게 작동되도록 다음과 같이 구분을 두었습니다.

## app/controllers/searches_controller.rb
 
def univ_search    
    if ActiveRecord::Base.connection.adapter_name == 'SQLite'
        @searches = Univ.where("univ_name_eng like ?", "%#{params[:search_univ]}%")
    elsif ActiveRecord::Base.connection.adapter_name == 'PostgreSQL'
        @searches = Univ.where("univ_name_eng ilike ?", "%#{params[:search_univ]}%")
    end
end

 

저같은 경우는 DB를 쓸 때 있어서 테스트 용으로서는 SQLite, 배포용으로서는 PostgreSQL을 쓰다보니 2개의 DB를 사용함에 있어 오류가 일어나면 안됩니다.

ActiveRecord::Base.connection.adapter_name
=> PostgreSQL / SQLite / ...

 

위 문법을 통해 내가 어떤 DB를 쓰는지 알아낸 후, 사용되는 DB에 따라 위와같이 서로 다른 코드가 쓰이도록 했습니다.