Database Access in Corona with SQLite - 2 -

오늘도 어제에 이어서 코로나에서 SQLite 다루는 방법에 대한 튜토리얼을 정리하겠습니다.

===== o ===== o ==== o ====

Inserting new rows into a table

새로운 row를 만드는 것은 INSERT 문을 통해서 할 수 있습니다. 우선 아주 기초적인 사용법을 보여 드리겠습니다. 그리고 나서 좀 더 다이나믹한 예제를 보여 드릴께요.

local insertQuery = [[INSERT INTO test VALUES (NULL, 'John Doe','This is an unknown person.', 'http://www.example.com/'); ]]
db:exec( insertQuery )

그냥 딱 봐도 알겠죠? 그래도 설명을 드리자면, 이전 에제에서 test라는 테이블을 생성했습니다. 그리고 이 예제에서는 그 test라는 테이블에 row를 생성한 겁니다.

주의하셔야 할 것은 컬럼의 순서에 맞춰서 value를 넣어 주셔야 합니다. 그 컬럼 순서는 테이블을 만들 때 사용했던 순서 입니다.

    id → NULL
    name → John Doe
    description → This is an unknown person.
    website → http://www.example.com/

이전 예제에서 테이블을 만들 때 id는 auto-increment로 설정 했습니다. 자동적으로 row 번호를 매기도록 하기 위해서 입니다. 그래서 id 값은 NULL 로 넣었습니다. 그러면 NULL이 들어가는 것이 아니라 자동적으로 숫자가 들어갈 겁니다. SQLite가 알아서 하는 부분이니까 별로 신경 안 쓰셔도 됩니다.

이제 좀 더 다이나믹한 예제를 볼까요? 좀 더 창의력을 발휘해 보겠습니다. 아래 예제는 루아 테이블로부터 value들을 가져와서 SQL 테이블에 row들을 insert 합니다.

local people =
{
    {
        name = "John Doe",
        description = "This is an unknown person.",
        website = "http://www.example.com/"
    },
    {
        name = "Jane Doe",
        description = "The wife of an unknown person.",
        website = "http://www.example2.com/"
    },
    {
        name = "Oscar",
        description = "Green guy lives in trash can.",
        website = "http://www.example3.com/"
    }
}

for i=1,#people do
    local q = [[INSERT INTO test VALUES (NULL, ']] .. people[i].name .. [[',']] .. people[i].description .. [[', ']] .. people[i].website .. [['); ]]
    db:exec( q )
end

NOTE : 여러분들이 입력하고자 하는 값에 따옴표가 있으면 그 따옴표 옆에 역슬래시를 넣어 주어야 합니다. 그렇지 않으면 에러가 발생할 겁니다.

 Updating existing rows

항상 새 row를 만드는 일만 하는 것은 아니죠? 사실 실전에서는 기존에 있던 row의 데이터를 업데이트 하는 일이 더 자주 있습니다. 아래 예제는 테이블에 이미 3개의 row들이 있다고 가정하고 만든 예제입니다. (위의 예제에서 만들었던 3개의 row들이라고 가정합시다.)

local q = [[UPDATE test SET name='Big Bird' WHERE id=3;]]
db:exec( q )

위 예제의 쿼리가 하는 일은 id가 3인 row를 찾아서 그 중의 name 컬럼의 값을 Big Bird 로 바꾸는 일을 합니다. 이전 예제에서 만들었던 세번째 row의 name인 Oscar 가 Big Bird가 되겠죠. 꼭 id로 특정 row를 찾아야 되는 것은 아니지만 대개 이렇게 하는게 간편합니다. (그 값은 unique 해서 항상 딱 1개의 row만 해당 되거든요.)

Deleting a row from an SQL table

delete는 update와 비슷합니다. 다른점은 UPDATE 대신 DELETE를 사용한다는 것이죠. 아래 예제에서는 id가 1인 row를 delete 합니다. John Doe가 있는 row가 delete 되겠네요.

local q = [[DELETE FROM test WHERE id=1;]]
db:exec( q )

Retrieving data with “SELECT”

SQL 데이터베이스에서 데이터를 검색할 때 다양한 필요에 의해 행해지게 됩니다. 어떤 때는 단 1줄만 필요할 때도 있고 특정 값을 가진 (예를 들어 서울에 사는 사람들 등) row들을 모두 검색해야 할 때도 있습니다.

이 모든 경우를 다루지는 않겠습니다. 다만 아래 예제를 통해 기존에 있던 데이터베이스에서 SELECT 쿼리를 통해 얻은 값을 루아 배열에 담는 과정을 보여드리겠습니다.

require "sqlite3"

local path = system.pathForFile( "data.db", system.DocumentsDirectory )
local db = sqlite3.open( path )

local people = {}  -- starts off emtpy

for row in db:nrows("SELECT * FROM test") do
    print( "Row " .. row.id )

    -- create table at next available array index
    people[#people+1] =
    {
        name = row.name,
        description = row.description,
        website = row.website
    }
end

여기서 가장 중요한 부분은 8번째 줄인 SELECT 구문이 있는 곳입니다. nrows() 함수를 이용해서 for 루프를 돌리도록 합니다. 이렇게 해서 루아 배열에 값을 넣고 그 값을 가지고 앱 내에서 이런 저런 프로그래밍을 할 수 있습니다.

SELECT 와 관련되서 좀 더 자세히 알고 싶으시면 이 글을 읽어 보세요.

Closing the database

데이터베이스와 관련된 작업이 끝났다면 데이터베이스와의 연결을 끊어 줘야 합니다. 이것은 굉장히 중요한 부분 입니다. close() 메소드를 사용해서 연결을 끊는데요. 필요할 때는 언제든지 사용하실 수 있습니다. 하지만 되도록이면 applicationExit 시스템 이벤트가 발생할 때 사용할 것을 권장 합니다. 이렇게 되면 앱이 종료할 때 데이터베이스 연결이 끊어지게 됩니다.

local function onSystemEvent( event )
    if event.type == "applicationExit" then
        if db and db:isopen() then
            db:close()
        end
    end
end
Runtime:addEventListener( "system", onSystemEvent )

위 코드는 여러분의 앱내 어느 부분에 있어도 됩니다. 단지 데이터베이스를 오픈한 이후가 되도록 하는게 좋겠죠. 위의 경우는 db 객체가 생성된 이후가 좋겠죠. 그리고 데이터베이스를 닫아야 하는 시점과 관련된 위치가 좋겠구요.

Further Reading

이것이 코로나에서 데이터베이스를 이용하는 대략적인 Tutorial 입니다. 코로나에서 SQLite으로 어떤 일을 할 수 있는지 살짝 맛만 보인겁니다. 더 많은 정보들은 여러분 스스로 찾으셔야 합니다. 인터넷에는 수 많은 SQLite + Lua 관련된 자료들이 있습니다. 그러니까 앱을 만들 때 데이터베이스를 좀 더 효과적으로 이용하시려면 더 많은 공부가 필요할 겁니다.

우선 SQL의 다양한 쿼리 사용법에 익숙해 지는것이 좋습니다. 이것만 해도 수 많은 Tutorial들을 만들 수 있습니다. 이 글에서 다룬 내용은 단지 처음 시작할 수 있도록 기본적인 내용만 다룬겁니다.

SQLite에 대해 좀 더 깊은 정보를 원하신다면 zetcode.com 의 SQLite tutorial을 보실것을 권장합니다.