코로나에서 SQLITE DB 이용하기

오늘은 코로나에서 데이타베이스를 이용하는 방법을 보겠습니다.
안드로이드나 아이폰에서 SQLite라는 DB를 쓰니까 코로나에서도 당연히 SQLite 연동 기능을 제공 하겠죠?

기본적으로 코로나는 아이폰에 맞는 SQLite built-in을 실행합니다. 그리고이것을 안드로이드의 SQLite 버전으로 컴파일 하게 되는데요. 이것은 안드로이드 바이너리 파일(apk 파일)의 사이즈를 약 300k 정도 커지게 만듭니다.

SQLite 은 코로나 시뮬레이터에서도 제공 되므로 시뮬을 통한 테스트도 가능합니다.
(지금 Spin the Bottle 을 코로나 버전으로 옮기고 있는데요. text field, text box 같은 것들은 시뮬에서 제공이 안 되더라구요. 그래서 소스 고친 다음에 빌드하고 디바이스에 인스톨 해서 테스트 해야 하니 많이 번거롭습니다.)

아래 샘플을 보세요.

require "sqlite3"  -- SQLite 3 를 import 한다.
local db = sqlite3.open_memory() -- 메모리에 디비를 만든다.
 
db:exec[[  -- 테이블을 생성한다.
  CREATE TABLE test (id INTEGER PRIMARY KEY, content);
  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]
 
print( "version " .. sqlite3.version() ) -- SQLite의 버전을 터미널에 프린트한다.
 
for row in db:nrows("SELECT * FROM test") do  -- 모든 데이터를 화면에 출력한다.
  local t = display.newText(row.content, 20, 30 * row.id, null, 16)
  t:setTextColor(255,0,255)
end

소스 설명은 옆에 주석으로달았습니다.

두번째 줄에서 보시듯이 이 소스는  디비를 메모리에서 만들도록 했습니다.
그래서 재 실행하면 디비가 없어지고 다시 새로 만들기 때문에 항상 저 위에 3개의 문자만 나옵니다.

아래 샘플 코드는 메모리가 아니라 디바이스에 디비를 만듭니다.

--Include sqlite
require "sqlite3"
--Open data.db.  If the file doesn't exist it will be created
local path = system.pathForFile("data.db", system.DocumentsDirectory)
db = sqlite3.open( path )  
 
--Handle the applicationExit event to close the db
local function onSystemEvent( event )
        if( event.type == "applicationExit" ) then             
            db:close()
        end
end
 
 
--Setup the table if it doesn't exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]]
print(tablesetup)
db:exec( tablesetup )
 
--Add rows with a auto index in 'id'. You don't need to specify a set of values because we're populating all of them
local testvalue = {}
testvalue[1] = 'Hello'
testvalue[2] = 'World'
testvalue[3] = 'Lua'
local tablefill =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[2]..[['); ]]
local tablefill2 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[2]..[[',']]..testvalue[1]..[['); ]]
local tablefill3 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[3]..[['); ]]
db:exec( tablefill )
db:exec( tablefill2 )
db:exec( tablefill3 )
 
--print the sqlite version to the terminal
print( "version " .. sqlite3.version() )
 
--print all the table contents
for row in db:nrows("SELECT * FROM test") do
  local text = row.content.." "..row.content2
  local t = display.newText(text, 20, 30 * row.id, null, 16)
  t:setTextColor(255,0,255)
end
 
--setup the system listener to catch applicationExit
Runtime:addEventListener( "system", onSystemEvent )

이렇게 디바이스에 디비를 만들면 맨 마지막 줄처럼 앱이 끝날 때 디비를 close 시키기 위해 리스너를 달고 onSystemEvent( event ) 함수처럼 db를 close 시킵니다.

코로나에서는 이 SQLite 관련해서 자세하게 사용법을 알려주지 않더라구요.
그리고 SQLite의 여러 신택스들도 사용하면서 잘 실행이 안되는 것 같구요.

그래서 저는 데이터를 다룰 때 select * 을 해서 모든 데이터를 가져오고 이 데이터들을 코로나의 배열(테이블)에 넣어서 사용했습니다.

function fetchAll()
    local r = {}
    local i = 1;
   
    for row in db:nrows("SELECT * FROM table") do
        --print("id = " .. row.id .. " content = " .. row.content );
        local id = row.id;
        local contnt = row.content;

        r[i] = {}
        r[i].id = id
        r[i].content = contnt
        i = i+1;
    end

return r; -- 모든 데이터와 count(*) 값을 리턴한다.   
end

이렇게 데이터를 r이라는 테이블(배열) 에 담아서 리턴하는 함수 하나 만들어 놓고 사용합니다.
코로나는 간편하게 테이블(배열)을 control 할 수 있어서 이게 더 편하더라구요.

물론 SQLite를 통해서 제공하는 기능을 잘 알면 그걸 쓰면 더 편할 수도 있겠지만요.

신택스는 제가 보니까 아래 처럼 두가지가 있더라구요.
    db:exec[[
        INSERT INTO table VALUES (NULL, 'contents contents contents');
    ]]
이렇게 직접 쿼리를 실행하는 것 하구 아래처럼 변수에 쿼리를 담아서 실행하는 것도 있습니다.

    local insertRule =[[INSERT INTO table VALUES (NULL, ']]..contents..[[');']]
    db:exec( insertRule )

    local updateRule =[[update table SET content = ']] ..content .. [['WHERE id = ']].. id .. [[';']]
    db:exec( updateRule )

자 이렇게 DB control에 필요한 connect, select,insert,update,delete,drop, db close 기능에 대해서 배웠구요.
이 데이터들을 코로나 배열(테이블)에 넣어서 사용하는 법도 배웠습니다.

다음 시간에는 file control에 대해서 살펴 볼께요.