SQLite DB 테이블 조회할 때 한글과 영어가 섞인 컬럼에 대해서 정렬을 하게되면, SQLite는 기본적으로 기타문자(특수문자,숫자), 영어, 한글 순서로 정렬을 합니다. 보통 ORDER BY 절에 COLLATE LOCALIZED 키워드를 사용하는데, (사실 이게 뭔지는 저도 잘 모르지만) 사용자 로케일이 ko_KR이더라도 우리가 원하는대로 한글 - 영어 - 기타 순으로 정렬되지는 않더군요. 

ORDER BY name COLLATE LOCALIZED ASC; // 기타 - 영어 - 한글 순으로 정렬됨

1. 구분 컬럼을 이용한 방법
우리나라 사용자에게 좀더 자연스러운, 한글 -> 영어 -> 기타 순서로 정렬하기 위해 고민하다가 검색해보니 역시 유용한 팁이 있네요.


해당 컬럼 값이 한글인지, 영어인지, 기타인지 구분해주는 구분컬럼을 두어서 1, 2, 3 식으로 할당을 합니다. 그리고 ORDER BY 절에 이 구분컬럼으로 먼저 정렬을 하면 조회목록에서 한글이 영어보다 먼저 나오게 됩니다. 위 링크에 나오는 것처럼 구분컬럼을 TAG라고 하면,

ORDER BY tag, name COLLATE LOCALIZED ASC; // TAG 컬럼으로 먼저 정렬하므로 한글 - 영어 - 기타 순으로 정렬됨

그러나 TAG와 같은 구분컬럼을 이용하는 경우에는 복잡성이 증가하고 유연성이 떨어집니다. 
우선 테이블 컬럼이 늘어나게 되고, NAME 컬럼에 값이 추가/수정될 때마다 이게 한글인지 영어인지 기타 인지 판단하여 TAG 컬럼 값 역시 적절히 추가/수정해줘야 합니다(이런 일에는 트리거가 유용하겠죠). 우리에게 테이블에 컬럼을 추가할 수 있는 권한이 없을 수도 있습니다. 만약 언어 로케일 설정 또는 화면별로 정렬 순서를 다르게 가져가야 한다면 TAG 컬럼을 활용하기 어려울 수도 있습니다.

2. ORDER BY 절에서 CASE 문을 이용하는 방법
이런 구분컬럼을 추가하지 않아도 우리가 원하는대로 정렬할 수 있습니다. ORDER BY 절에서 CASE 문을 사용해서 동적으로 한글/영어/기타로 구분하면 됩니다.

ORDER BY (CASE WHEN substr(name ,0, 1) BETWEEN 'ㄱ' AND '힣' THEN 1 
                           WHEN substr(name, 0, 1) BETWEEN 'A' AND 'Z' THEN 2
                           WHEN substr(name, 0, 1) BETWEEN 'a' AND 'z' THEN 2
                           ELSE 3 
                 END), 
                 name COLLATE LOCALIZED ASC

제가 SQL 전문가가 아니기 때문에 더 좋은 쿼리가 있는지는 모르겠습니다. 이 방법은 테이블을 고칠 필요가 없으며, 언어 로케일이나 화면 등 상황에 따라서 ORDER BY 절에서 정렬 순서를 수정하면 되므로 유연합니다. 동일한 CASE 구문이 여러 곳에서 쓰인다면 SQL 함수로 만들거나 자바 메소드로 만들어두면 됩니다. 다만 구분컬럼을 두는 1번 방법보다는 성능면에서 안 좋습니다. 그러나 테스트해 본 바로는 생각보다 그리 느려지지 않았습니다.

ps. 위 2가지 방법 모두 완벽한 정렬은 아닙니다. 첫글자 이후로는 기타 - 영어 - 한글 순으로 소팅됩니다. 즉 아래와 같이 정렬됩니다.
 - 가나다123
 - 가나다abc
 - 가나다라마바

ps2. SQLite가 COLLATE LOCALIZED 키워드를 어떻게 해석하는지 low level의 소스를 분석해보면 좀더 근본적인 해결 방법이 있을것 같습니다.

Posted by 에코지오
,

애플리케이션에서 사용하는 SQLite DB의 table 구조나 data를 확인하는 3가지 방법을 정리해봅니다.

1. sqlite 프롬프트 이용
명령어 날리는게 귀찮지만 sqlite 활용의 기본이니 알아두면 좋습니다. 이 방법은 직접 디바이스 내부 DB에 접속하여 작업 가능하므로 DB 파일을 로컬로 복사할 필요가 없습니다.

자세한 이용 방법은 커니님의 글 참조 : http://androidhuman.tistory.com/entry/테이터베이스-그-속이-궁금하다

2. SQLite 클라이언트 툴 이용
SQLite Database Browser 같은 SQLite DB 전용 클라이언트 소프트웨어를 이용하여 DB 내용을 볼 수 있습니다.

(1) 클라이언트 SW 설치
- SQLite Database Browser : UI가 수수함. 주로 데이터 조회 기능 제공.
- SQLite Developer Lite : UI가 세련됨. 테이블/인덱스 외에 뷰/트리거도 조회. 테이블 관리 등 여러가지 기능 제공.

(2) DB 파일 꺼내기
디바이스 or 에뮬레이터의 내부 DB 파일(*.db)을 로컬로 꺼냅니다. 예를들어 Contacts Provider가 사용하는 DB파일을 C:\로 복사해보죠.

$ adb pull /data/data/com.android.providers.contacts/databases/contacts2.db C:/contacts2.db

(3) 꺼낸 DB 파일을 클라이언트 툴로 오픈
SQLite 클라이언트 툴로 오픈하여 DB 구조와 데이터를 확인합니다.





* 타겟 시스템의 바뀐 DB 내용을 확인하고자 한다면, DB 파일 꺼내는 작업을 반복해서 해주어야 하므로 DDMS의 File Explorer보다는 파일 꺼내는 명령을 배치파일로 만들어두고 실행하는 것이 편리합니다. 보통은 DB 파일을 꺼내고 툴로 오픈하는 명령을 하나의 배치파일로 만들어두고 활용합니다.

3. 이클립스 DB 클라이언트 플러그인 이용
SQLite 클라이언트 툴을 설치하는게 귀찮거나 이클립스 외에 다른 프로그램을 띄우는게 싫다면, 이클립스 안에서 SQLite DB를 볼 수 있게 세팅해주면 되겠습니다. 개인적으로 이 방법을 선호합니다.(그러나 이런 이클립스 플러그인들은 JDBC를 이용하기 때문에 전용 SW보다는 DB 관리 기능이 부족합니다.)

(1) DB 클라이언트 플러그인 설치
SQL Explorer나 Quantum DB 같은 DB 클라이언트 플러그인을 설치합니다.

(2) JDBC 연결 세팅
JDBC 드라이버 : http://www.zentus.com/sqlitejdbc/ 에서 구할 수 있습니다.
JDBC URL 형식 : jdbc:sqlite:/[로컬 DB파일 경로] (예를들어, jdbc:sqlite:/C:/contacts.db )

(3) DB 플러그인에서 DB 연결하여 내용조회
DB 파일을 JDBC URL에 설정된 고정된 경로로 꺼내고, DB 플러그인에서 DB에 연결하여 내용을 확인합니다.



* 2번 방법과 마찬가지로 내부 DB 변경시마다 로컬로 DB파일 꺼내는 작업을 반복해야 하므로, 파일 꺼내는 배치파일 or 명령을 이클립스 외부프로그램(External Tools)으로 등록시켜서 실행하면 편리합니다.



참고 : SQLite 데이터베이스의 DDL 쿼리 덤프 뜨기
sqlite에서 제공하는 .dump 명령어를 사용합니다. (table/index/trigger CREATE 문과 data INSERT 문이 출력됨)

$ sqlite3  C:/contacts.db  .dump > C:/contacts.ddl
Posted by 에코지오
,