삽질 중: SQLite and Android, 안드로이드에서의 SQLite 사용

이전 블로그에서 이전 함 (원본 글 2013/07/19 작성)

* 배운 내용 복습 차원으로 여기저기 찾아보며 정리 중

SQLite and Android

SQLite

Developer(s)	D. Richard Hipp
Initial release	August 2000
Stable release	3.7.17 / May 20, 2013; 60 days ago
Written in	C
Operating system	Cross-platform
Size	~350 KiB
Type	RDBMS (embedded)
License	Public domain^[1]
Website	sqlite.org

SQLite - Wikipedia

http://en.wikipedia.org/wiki/SQLite

[SQLite] SQLite 소개 및 특징

http://six605.tistory.com/294

[장점]

- 뛰어난 이식성 : 동적 데이터 타입 지원

- 작은 크기

- public domain license : http://www.sqlite.org/copyright.html

- 신뢰성, 편의성

[단점]

- 동시성 : read시 block 가능, 동시 writting 불가

- left outerjoing만 지원

[Architecture]

http://www.sqlite.org/arch.html

Block Diagram Of SQLite

* GIF라 안드로이드에서는 보이지 않으니 위 링크를 참고하세요~

- Backend 중 일부

. B-Tree : DB의 table과 index를 관리하기 위해 B-Tree 구조를 사용하며 DB 파일에서 페이지들을 유지 관리(정렬, 연결)

. Pager(Page Cache) : B-Tree 구조를 사용하여 저장된 DB를 구성하는 정보(chunk 단위)를 디스크로 부터 read/write

: database locking, transaction manage 제어 포함

[Data type]

http://www.sqlite.org/datatype3.html

Integer, Real(부동소수점), Text, BLOB, NULL

=> data type을 정하지 않아도 상관없고 다른 type을 집어 넣더라도 SQLite는 들어간다. 헐.

1.0 Storage Classes and Datatypes

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.

[SQL Syntax]

* Android에서 주로 사용되는 것들 위주로 기준으로 정리

http://www.sqlite.org/lang.html

* GIF라 안드로이드에서는 보이지 않으니 위 링크를 참고하세요~

- Transaction Control Language (TCL)

: Transaction

* 기본으로 auto_commit을 지원하여 다량의 데이터 입력 시 transaction을 사용하는것 추천

. begin : SQLiteDatabase.beginTransaction()

=> Listener 여부, Transaction 시 EXCLUSIVE / IMMEDIATE mode 사용 여부에 따른 기타 API 존재

Public Methods
void	beginTransaction() Begins a transaction in EXCLUSIVE mode.
void	beginTransactionNonExclusive() Begins a transaction in IMMEDIATE mode.
void	beginTransactionWithListener(SQLiteTransactionListener transactionListener) Begins a transaction in EXCLUSIVE mode.
void	beginTransactionWithListenerNonExclusive(SQLiteTransactionListener transactionListener) Begins a transaction in IMMEDIATE mode.

. commit : SQLiteDatabase.endTransaction()

. transaction 성공 시 : SQLiteDatabase.setTransactionSuccessful()

. rollback : 명시적인 API는 없고 setTransactionSuccessful()를 호출하지 않는 경우.

Here is the standard idiom for transactions:

   db.beginTransaction();
   try {
     ...
     db.setTransactionSuccessful();
   } finally {
     db.endTransaction();
   }

- Data Definition Language (DDL)

: 데이터의 구조(Table) 생성, 변경, 제거

. CREATE, DROP, ALTER : SQLiteDatabase.execSQL(), create(), deleteDatabase(),

public void execSQL (String sql)

Added in API level 1

Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.

It has no means to return any data (such as the number of affected rows). Instead, you're encouraged to useinsert(String, String, ContentValues), update(String, ContentValues, String, String[]), et al, when possible.

When using enableWriteAheadLogging(), journal_mode is automatically managed by this class. So, do not set journal_mode using "PRAGMA journal_mode'<value>" statement if your app is using enableWriteAheadLogging()</value>

Parameters

sql	the SQL statement to be executed. Multiple statements separated by semicolons are not supported.

Throws

SQLException	if the SQL string is invalid

* SQLite는 부가적으로 IF NOT EXIST 같은 구문을 지원한다.

. 예를 들어 CREATE TABLE student (); 같은 구문을 실행할 때 만약 student table이 있다면 에러가 발생되지만

CREATE TABLE IF NOT EXIST student (); 를 실행할 경우 만약 존재한다면 만들지 않음

DROP TABLE IF EXIST student; 도 같은 맥락

- SQLiteOpenHelper 구현 참고

http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/

: SQLite DB 생성 및 업그레이드 를 위한 일반적인 구현 예제

. SQLite DB schema 변경으로 DB 재생성을 위해서는 직접 Delete & Create 코드를 실행하는 것도 방법이지만

. DB version을 올린 상태로 app을 배포하면 자동으로 DB 재생성/업데이트 함.

. WAL mode setting 가능(public void setWriteAheadLoggingEnabled (boolean enabled), 단 단말과 버전에 따라 미구현일수도 )

public class DatabaseHandler extends SQLiteOpenHelper {

    // All Static variables

    // Database Version

    private static final int DATABASE_VERSION = 1;

    // Database Name

    private static final String DATABASE_NAME = "contactsManager";

    // Contacts table name

    private static final String TABLE_CONTACTS = "contacts";

    // Contacts Table Columns names

    private static final String KEY_ID = "id";

    private static final String KEY_NAME = "name";

    private static final String KEY_PH_NO = "phone_number";

    public DatabaseHandler(Context context) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);

    }

    // Creating Tables

    @Override

    public void onCreate(SQLiteDatabase db) {

        String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("

                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"

                + KEY_PH_NO + " TEXT" + ")";

        db.execSQL(CREATE_CONTACTS_TABLE);

    }

    // Upgrading database

    @Override

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // Drop older table if existed

        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

        // Create tables again

        onCreate(db);

    }

- Data Manipulation Language (DML)

: 데이터의 삽입, 수정, 삭제

. INSERT, UPDATE, DELTE 등

. Android SQLiteDatabase에서 지원하는 API는 다양하다..

=> execSQL, insert, update, delete...

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

- Android SQLiteDataBase API들을 사용한 SQLite 처리 예제

http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/

: 기본적으로 참고할 만한 소스, 좀 이상한 부분도 있으니 댓글을 보고 해당 사항 검토 필요,

⇒All CRUD Operations (Create, Read, Update and Delete)

Now we need to write methods for handling all database read and write operations. Here we are implementing following methods for our contacts table.

// Adding new contact

public void addContact(Contact contact) {}

// Getting single contact

public Contact getContact(int id) {}

// Getting All Contacts

public List<Contact> getAllContacts() {}

// Getting contacts Count

public int getContactsCount() {}

// Updating single contact

public int updateContact(Contact contact) {}

// Deleting single contact

public void deleteContact(Contact contact) {}

⇒Inserting new Record

The addContact() method accepts Contact object as parameter. We need to build ContentValues parameters using Contact object. Once we inserted data in database we need to close the database connection.

addContact()
    // Adding new contact

public void addContact(Contact contact) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();

    values.put(KEY_NAME, contact.getName()); // Contact Name

    values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone Number

    // Inserting Row

    db.insert(TABLE_CONTACTS, null, values);

    db.close(); // Closing database connection

}

⇒Reading Row(s)

The following method getContact() will read single contact row. It accepts id as parameter and will return the matched row from the database.

getContact()
    // Getting single contact

public Contact getContact(int id) {

    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,

            KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",

            new String[] { String.valueOf(id) }, null, null, null, null);

    if (cursor != null)

        cursor.moveToFirst();

    Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),

            cursor.getString(1), cursor.getString(2));

    // return contact

    return contact;

}

getAllContacts() will return all contacts from database in array list format of Contact class type. You need to write a for loop to go through each contact.

getAllContacts()
    // Getting All Contacts

 public List<Contact> getAllContacts() {

    List<Contact> contactList = new ArrayList<Contact>();

    // Select All Query

    String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;

    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list

    if (cursor.moveToFirst()) {

        do {

            Contact contact = new Contact();

            contact.setID(Integer.parseInt(cursor.getString(0)));

            contact.setName(cursor.getString(1));

            contact.setPhoneNumber(cursor.getString(2));

            // Adding contact to list

            contactList.add(contact);

        } while (cursor.moveToNext());

    }

    // return contact list

    return contactList;

}

getContactsCount() will return total number of contacts in SQLite database.

getContactsCount()
// Getting contacts Count

    public int getContactsCount() {

        String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;

        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery(countQuery, null);

        cursor.close();

        // return count

        return cursor.getCount();

    }

⇒Updating Record

updateContact() will update single contact in database. This method accepts Contact class object as parameter.

updateContact()
    // Updating single contact

public int updateContact(Contact contact) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();

    values.put(KEY_NAME, contact.getName());

    values.put(KEY_PH_NO, contact.getPhoneNumber());

    // updating row

    return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",

            new String[] { String.valueOf(contact.getID()) });

}

⇒Deleting Record

deleteContact() will delete single contact from database.

deleteContact()
    // Deleting single contact

public void deleteContact(Contact contact) {

    SQLiteDatabase db = this.getWritableDatabase();

    db.delete(TABLE_CONTACTS, KEY_ID + " = ?",

            new String[] { String.valueOf(contact.getID()) });

    db.close();

}

* Conflict Resolution Algorithms

http://www.sqlite.org/conflict.html

: 데이터 입력,수정 시 Table의 제약조건(primary key, unique 속성 등)과 충돌 시 처리하도록 설정할 수 있음.

. Abort : 제약 조건의 위반 시 Transaction내 DML 구문 전체를 취소하고 Transaction은 유지 함.

. Rollback : 모든 Transaction 취소

. Fail : Transaction내에서 위반하기 전까지 결과는 유지되고 Transaction 유지

. Ignore : 위반한 구문만 무시하고 모두 진행하고 트랜잭션 유지

. Replace : 위반 시 기존 레코드에 덮어 씀.

Constants
int	CONFLICT_ABORT	When a constraint violation occurs,no ROLLBACK is executed so changes from prior commands within the same transaction are preserved.
int	CONFLICT_FAIL	When a constraint violation occurs, the command aborts with a return code SQLITE_CONSTRAINT.
int	CONFLICT_IGNORE	When a constraint violation occurs, the one row that contains the constraint violation is not inserted or changed.
int	CONFLICT_NONE	Use the following when no conflict action is specified.
int	CONFLICT_REPLACE	When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row.
int	CONFLICT_ROLLBACK	When a constraint violation occurs, an immediate ROLLBACK occurs, thus ending the current transaction, and the command aborts with a return code of SQLITE_CONSTRAINT.

long insertWithOnConflict(String table, String nullColumnHack, ContentValues initialValues,int conflictAlgorithm)

General method for inserting a row into the database.

int updateWithOnConflict(String table, ContentValues values, String whereClause, String[] whereArgs, int conflictAlgorithm)

Convenience method for updating rows in the database.

- Query

: 데이터의 검색 시 사용, SELECT

Cursor	query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) Query the given table, returning a `Cursor` over the result set.
Cursor	query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit, CancellationSignal cancellationSignal) Query the given URL, returning a `Cursor` over the result set.
Cursor	query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) Query the given table, returning a `Cursor` over the result set.
Cursor	query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) Query the given URL, returning a `Cursor` over the result set.
Cursor	queryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit, CancellationSignal cancellationSignal) Query the given URL, returning a `Cursor` over the result set.
Cursor	queryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) Query the given URL, returning a `Cursor` over the result set.
Cursor	rawQuery(String sql, String[] selectionArgs, CancellationSignal cancellationSignal) Runs the provided SQL and returns a `Cursor` over the result set.
Cursor	rawQuery(String sql, String[] selectionArgs) Runs the provided SQL and returns a `Cursor` over the result set.
Cursor	rawQueryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable) Runs the provided SQL and returns a cursor over the result set.
Cursor	rawQueryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable, CancellationSignal cancellationSignal) Runs the provided SQL and returns a cursor over the result set.

[SQLite 내장함수]

- Core Functions

http://www.sqlite.org/lang_corefunc.html

- Date and Time Functions

http://www.sqlite.org/lang_datefunc.html

- Aggregate Functions

: Performance로 인해 DISTINCT를 명시한 field에 대해서만 사용 필요,

non-null values를 대상으로 수행.

http://www.sqlite.org/lang_aggfunc.html

[Trigger]

나중에..

http://www.sqlite.org/lang_createtrigger.html

[SQLite를 사용한 트리거 (Trigger) 의 이해 그리고 사용 방법]

http://jinhokwon.blogspot.kr/2013/03/sqlite-trigger.html

[Index]

나중에..

http://www.sqlite.org/lang_createindex.html

[Transaction]

Atomic Commit In SQLite

http://www.sqlite.org/atomiccommit.html

: 이거 다 읽어보면 될것 같지만.. 귀찮음. 배운 내용+ 다른 블로그 보며 정리함..

http://www.sqlite.org/lockingv3.html

http://flylib.com/books/en/1.91.1.17/1/

- Lock States

http://blog.csdn.net/cctt_1/article/details/5505831

* GIF라 안드로이드에서는 보이지 않으니 위 링크를 참고하세요~

: UNLOCKED

. 트랜잭션을 시작한 상태, 다른 프로세스 r,w 가능

: SHARED

.트랜잭션을 시작 후 r을 실행한 Lock 상태

. 다른 프로세스가 Pending Lock > Exclusive Lock 할 경우 Shared Lock의 트랜잭션이 끝나길 기다림

: RESERVED

. w하려는 상태의 Lock 상태

. 하나의 프로세스가 Reserved Lock을 획득 가능하고 다른 트랜잭션는 r만 가능

. DB의 변경이 아닌 Page cache에 변경된 내용 저장

: PENDING(Reserved Lock 후)

. Commit, Rollback시 Exclusive Lock을 진입하기 위한 전단계, Shared Lock 트랜잭션이 종료되길 기다림

. 하나의 프로세스가 Pending Lock 상태가 되면 다른 트랜젹선들은 Unlocked > Shared Lock으로 변경 불가 (다른 트랜잭션들 r 불가)

=> 이런 골때린 현상을 피하고자 WAL 모두 사용을 추천

: EXCLUSIVE

. Pager가 가지고 있는 변경된 page를 쓰는 Lock 상태

. SQLiteDatabase.beginTrasanction()은 기본적으로 EXCLUSIVE lock을 걸고 시작

* Android SQLiteDatabase에서 제공하는 transaction API들이고 EXCLUSIVE/IMMEDIATE mode로 시작함.

- void beginTransaction() : Begins a transaction in EXCLUSIVE mode.

- void beginTransactionNonExclusive() : Begins a transaction in IMMEDIATE mode.

* 두건의 Transaction이 시작되어 Unlock상태에서 둘다 read를 하여 Shared Lock을 획득한 뒤

한건이 write를 하고자 하여 Reserved Lock을 획득하고 commit하여 Exclusive Lock을 획득하고 하면

다른 Shared Lock 상태의 Transaction을 기다리기 위해 Pending Lock 상태에 머무르게 된다.

=> 이때는 DB를 read 조차 할 수 없는 상태임..

[Journal mode & WAL mode]

Transaction시 Atomic commit과 Rollback 기능을 지원하기 위해 DB이름-journal 이란 파일을 생성하여 사용한다.

- Journal mode

http://www.sqlite.org/pragma.html#pragma_journal_mode

. DELETE : 기본 모드이며 transaction 종료 시 파일이 삭제된다.

. TRUNCATE : transaction 종료 시 파일을 두고 size를 0으로 만든다. 지우는 것 보다는 빠를 듯

. PERSIST : 파일을 지우지 않고 transaction마다 overwrite하여 사용. 별도 file operation이 없음

. MEMORY : 파일을 생성하지 않고 메모리에 기록. 단 process가 죽어 버리면 DB corrupt 가능

. WAL : Rollback Journal 대신 write-ahead log 방법 사용. 3.7.0 이후 부터 지원

. OFF : Rollback journal을 사용하지 않으므로 Rollback이 동작하지 않을 것이고 transaction시 crash나면 DB corrupt 가능

- Write-Ahead Logging

http://www.sqlite.org/wal.html

. 장단점

: 주요 장점으로는 상대적으로 적은 file operation으로 인해 일반적으로 기존 rollback journal 보다 빠르고 r/w가 동시에 가능함.

: 주요 단점으로는 NFS 같은 것 지원 못하고 r를 주로 사용하는 시나리오에서 느리며 heavy한 transaction(100M이상)의 시나리오에서는 상대적으로 느리고 disk 남은 용량에 영향을 받음.

There are advantages and disadvantages to using WAL instead of a rollback journal.

Advantages include:

WAL is significantly faster in most scenarios.
WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
Disk I/O operations tends to be more sequential using WAL.
WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.

But there are also disadvantages:

WAL normally requires that the VFS support shared-memory primitives. (Exception: WAL without shared memory) The built-in unix and windows VFSes support this but third-party extension VFSes for custom operating systems might not.
All processes using a database must be on the same host computer; WAL does not work over a network filesystem.
Transactions that involve changes against multiple ATTACHed databases are atomic for each individual database, but are not atomic across all databases as a set.
It is not possible to change the database page size after entering WAL mode, either on an empty database or by using VACUUM or by restoring from a backup using the backup API. You must be in a rollback journal mode to change the page size.
It is not possible to open read-only WAL databases. The opening process must have write privileges for "-shm" wal-index shared memory file associated with the database, if that file exists, or else write access on the directory containing the database file if the "-shm" file does not exist.
WAL might be very slightly slower (perhaps 1% or 2% slower) than the traditional rollback-journal approach in applications that do mostly reads and seldom write.
There is an additional quasi-persistent "-wal" file and "-shm shared memory file associated with each database, which can make SQLite less appealing for use as an application file-format.
There is the extra operation of checkpointing which, though automatic by default, is still something that application developers need to be mindful of.
WAL works best with smaller transactions. WAL does not work well for very large transactions. For transactions larger than about 100 megabytes, traditional rollback journal modes will likely be faster. For transactions in excess of a gigabyte, WAL mode may fail with an I/O or disk-full error. It is recommended that one of the rollback journal modes be used for transactions larger than a few dozen megabytes.