데이터베이스(Database) - SQLite

SQLite의 특징

• 오픈 소스
• 최소한의 라이브러리만으로 동작
• 관리유저나 설정파일 등이 불필요
• 원자성(atomicity), 일관성(consistency), 독립성(isolation), 영속성(durabilitiy)이라는 데이터베이스에 필요한 특성을 갖추고 있습니다.
• SQL92에서 정의된 명령어의 대부분 이용가능
• 하나의 데이터베이스가 하나의 파일에 저장

   

   

데이터베이스

데이터를 영속적으로 저장할 수 있는 공간

   

테이블

• 데이터베이스에서 데이터가 모이는 집합공간
• SELECT, INSERT, UPDATE, DELETE같은 쿼리문으로 제어할 수 있습니다.

   

데이터 베이스 특성

원자성(atomicity)	하나의 트랜젝션이 하나의 단위로 처리. 어려 단계에 걸쳐 수행되더라도 트랜젝션이 실패가 되면 모든 상태가 트랜젝션 수행 상태 전으로 rolled back되어, 이전 상태를 유지하게 됩니다. 즉, 트랜젝션이 완전히 수행되거나, 아무것도 수행되지 않은 상태(All or Nothing)을 의미합니다.
일관성(consistency)	트랜잭션 수행후 완전한 데이터만 저장이 됩니다.
독립성(isolation)	격리성이라고도 합니다. 트랜젝션 수행 완료되기전까지의 변경되는 내용이 다른 트랜젝션에 영향을 주지 않습니다.
영속성(durabilitiy)	비휘발성으로 사라지지 않게 하는 것. 스마트폰 전원이 꺼져도 데이터가 계속 저장되어 있습니다.

   

   

DatabaseAdapter	데이터베이스와 데이터를 불러오는 기능 수행시 중계역할을 수행하는 클래스
DatabaseHelper	SQLiteOpenHelper클래스를 상속받다 데이터베이스에 접근하게 하는 사용자 정의 클래스
Cursor	Select쿼리문으로 데이터를 읽으면 Cursor을 이용하여 데이터의 위치를 가리키고 ArrayList에 정하는 역할을 하는 클래스
SQLiteOpenHelper	데이터베이스에 접근할 수 있도록하는 클래스

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

데이터베이스 접근 방법

   

   

ListView에 데이터베이스의 저장된 데이터를 표시하는 도식도

   

   

어플리케이션 개발시 SQLite 사용

• 애플리케이션이 작성한 SQLite의 데이터베이스는 /data/data/<패키지명>/databases 디렉토리에 생성됩니다.
• 하나의 애플리케이션에 대해서 하나의 디렉토리가 할당됩니다.
• 데이터베이스는 다른 애플리케이션에서 읽고 쓰기 할 수 없습니다. (다른 애플리케이션에서 사용 가능하게 하고 싶을 경우 Content provider 구성해야 합니다.)
• 애플리케이션이 작성한 데이터베이스를 조작하고자 할 때는 /system/xbin 디렉터리에 있는 sqlite3 명령어를 사용합니다.

   

   

SQLite 사용시 기본 사항

테이블의 작성과 삭제

 

Create [TEMP] TABLE <테이블명> (Column정의, [테이블제약]);

• TEMP(또는 TEMPORARY) 키워드를 지정하면 임시 테이블을 작성할 수 있습니다.
• 이 테이블은 데이터베이스를 close했을 때에 자동적으로 삭제됩니다.

   

 

SQLite에서 지원하는 컬럼 타입

컬럼타입	설명
text, varchar	문자열
smallint, integer	정수(2바이트 또는 4바이트)
real, float, double	부동소수(4바이트 또는 8바이트)
boolean	true 또는 false
date, time, timestamp	시간(날짜, 시간, 날짜 + 시간)
blob, binary	바이너리

   

 

테이블의 작성 예

Create table contact_list( _id integer primary key autoincrement, name text not null, tel text not null default 'unknown', unique (name, tel), check(length(tel)>=3)); );

_id: primary key라는 걸 알려주기 위해 일반적으로 _id형태로 표시

*외래키는 지원하지 않음

   

* 외래키(Foreign Key ≒ 참조키): 테이블과 테이블이 관계를 갖고 연결할때 참조하는 속성으로 다른 테이블의 기본키가 됩니다.

   

   

테이블 삭제 Drop 명령어 사용

drop table 테이블명;

   

 

테이블명의 변경과 컬럼 추가

테이블 명의 변경에는 ALTER 명령어의 RENAME TO를 사용

alter table 테이블명 rename to 새로운 테이블명;

 

테이블의 column추가에는 alter 명령어의 add column을 사용

alter table 테이블명 add column 컬럼 정의;

 

   

 

SQLite의 내장 함수(Built-in Function)

 

내장함수(Built-in Function)

함수	개요
min(X,Y,…,)	최소값을 반환합니다.
max(X,Y,…,)	최대값을 반환합니다.
typeof(X)	데이터의 형을 반환합니다.
length(X)	문자열의 길이를 반환합니다.
substr(X,Y,Z)	X의 문자열의 Y번째에서 Z문자의 길이의 문자열을 반환합니다.
abs(X)	절대값을 반환합니다.
round(X,Y)	X의 수치를 소수점 이하 Y에서 반올림합니다.
upper(X)	대문자로 변환합니다.
lower(X)	소문자로 변환합니다.
coalesce(X,Y,…,)	최초의 NULL이 아닌 값을 반환합니다.
hex(X)	문자열을 16진수 표기로 반환합니다.
Ifnull00,Y,…(X)	최초의 NULL이 아닌 값을 반환합니다.
random()	Radom한 값을 반환합니다.
randomblob(X)	X바이트의 random한 값의 열을 반환합니다.
nullif(X,Y)	2개의 값이 다르면 최초의 값을 반환합니다. 같다면 NULL을 반환합니다.
sqlite_version()	SQLite의 버전을 반환합니다.
quote(X)	문자열을 SQL의 문자열로 삽입할 수 있도록 escape처리를 실행합니다.
last_insert_rowid()	마지막에 삽입한 레코드의 ID를 반환합니다.
sum(X,Y,…,)	NULL이 아닌 행의 값의 합계를 반환합니다. 행이 없는 경우 NULL을 반환합니다.
total(X,Y,…,)	NULL이 아닌 행의 값의 합계를 반환합니다. 행이 없는 경우 0를 반환합니다.
avg(X,Y,…,)	NULL이 아닌 행의 값의 평균을 반환합니다. 행이 없는 경우 NULL을 반환합니다.
count(X)	그륩 안에서 X가 NULL이 아닌 행의 행수를 반환합니다.
count(*)	그룹 별의 행수를 반환합니다.

 

   

SQLite 데이터베이스 이용하기

 

SQLiteOpenHelper

• 데이터베이스를 생성하고 오픈하거나 닫기를 하려면 SQLiteOpenHelper 객체를 사용해야 합니다.(데이터베이스에 접근할 수 있게 해주는 클래스)
• SQLiteOpenHelper 클래스는 애플리케이션에서 요구하는 내용에 따라 데이터베이스를 생성하거나 업그레이드하는 기능 제공합니다.
• DatabaseHelper클래스는 SQLiteOpenHelper클래스를 상속받아 데이터베이스에 접근하게 하는 사용자 정의 클래스

   

public class DatabaseHelper extends SQLiteOpenHelper{   // 생성자 public DatabaseHelper (Context context){ super(context, "dbtest", null, 1); //context: Activity등의 Context 인스턴스 //dbtest: 데이터베이스의 이름 //null: 커서 팩토리(보통 null지정) //1: 데이터베이스 스키마 버전 } public void onCreate(SQLiteDatabase db){ //테이블을 생성하고 초기 데이터를 추가 String table_sql = "create Table test( _id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL);"; db.execSQL(table_sql); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){ // 버전 번호를 확인해서 새로운 스키마로 적절하게 업그레이드 함 db.execSQL("DROP TABLE IF EXISTS test"); onCreate(db); //onCreate 메소드를 호출해서 새로 table 셋팅 } }

   

   

INSERT - 데이터 추가

데이터베이스에 데이터를 저장하는 명령어

ContentValues cv = new ContentValues(); cv.put("title", "해피 데이"); cv.put("name", "홍길동"); db.insert("member", "title", cv);

   

SELECT - 데이터 가져오기

db.query(TABLE_NAME,        //테이블병 String[] {"name", "member"},        //컬럼 명세 null,                                 //WHERE 절 null,                                 //WHERE절에 전달될 데이터 null,                                 //groupBy절 null,                                 //having절 MEMO_ID + " DESC"         //ORDER BY );

   

UPDATE - 데이터 업데이트

데이터베이스에 저장된 데이터를 수정하는 명령어

ContentValues cv = new ContentValues(); cv.put("name", "홍길동");

 

String[] params = new String[] {"dragon"}; db.update("member", cv, "userid=?", params);

 

   

DELETE - 데이터 삭제

데이터베이스에 저장된 데이터를 삭제하는 명령어

String[] params = new String[]{userid}; db.delete("member", "userid=?", params);

 

   

 

데이터 불러오기

rawQuery() 메소드를 사용해 SELECT 구문을 직접 실행

Cursor c = db.rawQuery("SELECT name FROM sqlite_master where type='table' AND name='constants'", null);

   

query() 메소드를 인자로 각 부분의 값을 넘겨 실행

public Cursor query(String table, String[] columns, String selection, String[] selcetionArgs, String groupBy, String having, String orderBy, String limit)

 

값	설명
table	대상 테이블 이름
columns	값을 가져올 컬럼 이름의 배열 (null : 모든 열)
selection	WHERE 구문. 물음표를 사용해 인자의 위치를 지정할 수 있음 (null : 모든 레코드)
selectionArgs	WHERE 구문에 들어가는 인자값
groupBy	GROUP BY 구문 (null : 미사용)
orderBy	ORDER BY 구문 (null : 미사용)
limit	레코드수 지정 (null : 미사용)

 

SQLiteQueryBuilder 클래스의 query() 메소드 이용

   

 

   

   

커서(Cursor)

• 데이터베이스나 내용 제공자(Content Provider)의 데이터를 쿼리하여 가져올 때 사용됩니다.
• 결과값을 가지고 있는 포인터입니다.
• 즉, Select쿼리문으로 데이터를 읽으면 Cursor을 이용하여 데이터의 위치를 가리키고 ArrayList 같은 배열에 데이터베이스에서 읽은 데이터를 저장해주는 역할을 하는 클래스입니다.

   

커서의 활용

moveToFirst	가장 처음에 위치한 레코드로 커서를 이동
moveToNext	커서를 다음 행으로 옮김
moveToLast	가장 마지막에 위치한 레코드로 커서를 이동
moveToPrevious	이전 레코드로 커서를 이동
moveToPosition	특정 레코드로 커서를 이동
getPosition	커서가 현재 가리키고 있는 위치를 반환
getCount	전체 결과 건수가 몇 개인지 확인
getColumnCount	컬럼들의 전체 개수를 반환
getColumnIndex	특정 컬럼 번호 조회
getColumnName	특정 인덱스 값에 해당하는 컬럼 이름을 반환
getColumnNames	결과에 포함된 전체 컬럼 이름
isFirst	커서가 첫번째 레코드에 위치하는지를 반환
isLast	커서가 마지막 레코드에 위치하는지를 반환
isBeforeFirst	커서가 첫번째 레코드의 앞에 위치하는지를 반환
isAfterLast	커서가 마지막 레코드의 뒤에 위치하는지를 반환
getString() getInt()	컬럼값 반환
requery()	쿼리를 재실행
close()	커서가 확보한 자원을 모두 해제

 

 

트랜잭션(Transaction) 다루기

여러 데이터베이스 연산이 반드시 모두 성공해야 하는 경우가 있습니다.

이런 경우, 연산들 중 하나라도 실패하면 연산 모두를 없던 일로 철회해야 합니다.

모든 연산이 성공한다면, 비로소 연산 결과가 데이터베이스에 실제로 반영됩니다. 이와 같이 데이터베이스의 무결성이 보장되는 상태에서 요청된 연산들을 완수하기 위한 기본 작업 단위를 "트랜잭션(Transaction)"이라고 한다.

 

mDatabase.beginTransaction(); try{   mDatabase.setTransactionSuccessful(); }catch(Exception e){ }finally{ mDatabase.endTransaction(); }