'SQLite'에 해당되는 글 11건

  1. 2010.09.01 [강좌A11] 안드로이드 실전 개발 - 데이터베이스 : Part3 (Motodev database) (7)
  2. 2010.09.01 [강좌A10] 안드로이드 실전 개발 - 데이터베이스 : Part2 (6)
  3. 2010.09.01 [강좌A09] 안드로이드 실전 개발 - 데이터베이스 : Part1 (2)
  4. 2010.08.27 [강좌A08] 안드로이드 실전 개발 - SQLite (3)
  5. 2010.08.27 SQLite User Guide for Android, iPhone - Trigger
  6. 2010.08.27 SQLite User Guide for Android, iPhone - DateTime 함수, DateTime Formatting (1)
  7. 2010.08.27 SQLite User Guide for Android, iPhone - 함수. Function (Core, Aggregation) (1)
  8. 2010.08.26 SQLite User Guide for Android, iPhone - DML (SELECT/INSERT/UPDATE/DELETE) 등
  9. 2010.08.26 SQLite User Guide for Android, iPhone - DDL(CREATE, DROP 등) (1)
  10. 2010.08.26 SQLite User Guide for Android, iPhone - PRAGMA, Data Type 등.
  11. 2010.08.26 SQLite User Guide for Android, iPhone - 소개, GUI Tools 등 (5)
2010.09.01 13:41

[강좌A11] 안드로이드 실전 개발 - 데이터베이스 : Part3 (Motodev database)




이번의 강좌는 잠깐 쉬어가는 편입니다. Database 개발 관련하여 글을 쓰다가 Motodev의 Database 기능을 사용해 봤는데, 생각보다 너무 좋습니다. 유용한 기능이 많아서 소개할까 합니다. 대부분 그림이니 맘 편하게 보시고 한 5분 정도만 투자하셔서 해 보시면 될 것 같습니다.

전체 강좌 목차

[강좌A01] Moteodev Studio를 이용한 안드로이드 개발 환경 구축 가이드
[강좌A02] 안드로이드 개발 참고 서적 소개
[강좌A03] Android 실전 개발 - 아이디어 / 기획 / Wireframe
[강좌A04] 안드로이드 실전 개발 - 아이콘 제작
[강좌A05] 안드로이드 실전 개발 - 레이아웃 및 리소스 : Part1
[강좌A06] 안드로이드 실전 개발 - 레이아웃 및 리소스 : Part2
[강좌A07] 안드로이드 실전 개발 - 리소스 해킹
[강좌A08] 안드로이드 실전 개발 - SQLite

[강좌A09] 안드로이드  실전 개발 - 데이터베이스 : Part1
[강좌A10] 안드로이드  실전 개발 - 데이터베이스 : Part2
[강좌A11] 안드로이드  실전 개발 - 데이터베이서 : Part3 (Motodev database)

1. Database 생성하기. 테이블 생성하기.

먼저, Motodev를 실행하고, Window > Open Perspective > Motodev Database 퍼스펙티브를 실행합니다.



왼쪽의 Motodev Database Explorer에서 우리 프로젝트인 Hangul2English를 선택한 후 우측마우스 > Create database.. 를 선택합니다.



데이터베이스명을 hangul2english.db라고 입력한 후 OK. 데이터베이스가 생성됩니다.
실제 데이터베이스는 프로젝트 디렉토리의 assets\ 하위에 생성됩니다. 테이블 생성을 위해서 생성한 DB를 아래 그림 처럼 선택하고 Connect 를 선택하여 DB에 연결합니다.



데이터베이스가 연결되면 파란색 아이콘이 생성됩니다. 우측마우스 > Create Table .. 선택합니다.



테이블명 및 컬럼 정보를 입력합니다.



테이블 정보 입력 완료된 화면입니다. Finish를 누르면 테이블이 만들어집니다.

2. DDL문 Generate 하기.

Motodev Database Explorer에서 생성한 db를 선택한 후 우측마우스 클릭 > Generate DDL을 선택하여 DDL문을 만들 수 있습니다.

3. DML 실행 및 테스트

Motodev Database에서는 SQL문도 처리가 가능합니다. Motodev Database Explorer에서 아래 그림과 같이 Open SQL Scrapbook을 선택하여 실행합니다.



열린 SQL Scrapbook 에디터에서 SQL문을 실행하면 하단 Grid에 결과가 나타납니다.<아래그림>


4. Create Database Management Classes..

정말 좋은 기능은 이 기능입니다. Database 관련 class를 자동으로 만들어 줍니다.
아래 그림처럼Motodev Database Explorer에서 Create Database Management Classess.. 를 실행합니다.



필요한 정보를 수정한 후 Finish 누르면……짜자잔~


2개의 class가 만들어 졌습니다. Generate 된 소스는 길어서  캡처하지 않았습니다. 한 번 해보시기 바랍니다.

일반적으로 Android database 개발은 다음과 같이 구분할 수 있습니다.

데이터베이스 생성 방식에 따라
1. Code.에서 DDL문 실행 – 일반적인 방식. 간단하고 초기 적재해야 될 데이터가 적은 경우.
2. Assets 폴더에 DB를 생성한 후 실행시에 DB 파일을 복사해서 처리하는 방식 – 초기 데이터가 많거나 DB구조가 복잡할 때 주로 사용함.

데이터베이스 관련 코드 개발 방식에 따라
1. Contents Provider – DB를 다른 어플과 공유해야 할 때, 혹은, 여러명이 개발할 때 주로 사용하는 방식으로 DB 처리를 별도의 Provider로 제공하는 방법
2. 직접 DB Access – 직접 DB에 query를 실행하여 처리하는 방식

위에서 준 옵션으로 Class를 Generate 했을 때는 테이블명 + ContentProvider.java라는 이름의 Content Provider용 자바 코드가 만들어지며, 데이터베이스는 생성했기 때문에 asets\하위에 생성된 db 파일을 복사해서 db를 생성하도록 코드가 만들어졌습니다.

필요한 코드를 Motodev에서 다 만들어주니 일이 한결 수월해 지는 군요.
그리고 앞서 제가 개발한 DB소스에서 다루지 않은 두 부분으로만 코드가 만들어 졌으니 정말 딱입니다.

생성된 클래스 파일을 지우고, Generate Content Providers for each table 체크를 끄고 다시 코드를 Generate 해보니, Content Provider Code만 생성이 안되고 나머지 처리는 동일하게 진행이 되었습니다.

끝으로 Motodev Database를 이용하면 애뮬레이터나 폰에서 테스트 할 때 DB에 붙어서 Query를 실행하고 DDL문을 Generate하는 식의 작업이 가능합니다.

다음 화면은 제 애물레이터의 telephony provider의 mmssms.db의 테이블 스키마를 Motodev Database Explorer에서 확인한 화면입니다.



이로서 안드로이드 실전강좌 데이터베이스 편을 마칩니다. 곧 이어 Main UI 소스 부분도 오픈 됩니다.


Trackback 3 Comment 7
  1. 이유진 2010.09.25 18:18 신고 address edit & del reply

    안드로이드를 공부하면서 어플을 만들고 있는 학생입니다.질문이 하나 있는데요. 따로 디비를 만든것은 SQLite용으로 변환하여 사용 할려고 하는데 그방법을 모르겠습니다. 관련 자료나 소스 있으시면 kodog1022@naver.com으로 메일하나 날려주시면 고맙겠습니다 ^^;

  2. kenny 2010.11.19 08:15 신고 address edit & del reply

    보고픈 글쓴이님 Eclipse에도 똑같은 기능을 쓸수있을까요? motodev만된다면, 갈아타야겠네요. 은근히 좋은 옵션인거같아보여요.

    • 보고픈 2010.11.20 10:09 신고 address edit & del

      Motodev를 이클립스 플러그인으로 설치하셔서 사용하시면 됩니다. 다운은 http://developer.motorola.com/docstools/motodevstudio/download/ 요기서 하시면 됩니다.

  3. kenny 2010.11.23 10:06 신고 address edit & del reply

    보고픈님 감사합니다.저도 강좌를 보다 마음이 급해서 플러그인을 설치하고야말았어요.
    꽤 gui가 많이 변하거같으면서도 변한거는없이 플러그인설치로인해 옵션이 더많이늘어났네요. ^^
    자주얘기해요 보고픈님.

  4. ugg boots kids 2010.11.25 17:48 신고 address edit & del reply

    Nothing is impossible to a willing heart。

  5. ugg boots kids 2010.11.25 17:50 신고 address edit & del reply

    Nothing is impossible to a willing heart。

2010.09.01 12:53

[강좌A10] 안드로이드 실전 개발 - 데이터베이스 : Part2




안드로이드 실전 개발 데이터베이스편 파트2 입니다. 전편에 이어 바로 시작하도록 하겠습니다.

전체 강좌 목차

[강좌A01] Moteodev Studio를 이용한 안드로이드 개발 환경 구축 가이드
[강좌A02] 안드로이드 개발 참고 서적 소개
[강좌A03] Android 실전 개발 - 아이디어 / 기획 / Wireframe
[강좌A04] 안드로이드 실전 개발 - 아이콘 제작
[강좌A05] 안드로이드 실전 개발 - 레이아웃 및 리소스 : Part1
[강좌A06] 안드로이드 실전 개발 - 레이아웃 및 리소스 : Part2
[강좌A07] 안드로이드 실전 개발 - 리소스 해킹
[강좌A08] 안드로이드 실전 개발 - SQLite

[강좌A09] 안드로이드  실전 개발 - 데이터베이스 : Part1
[강좌A10] 안드로이드  실전 개발 - 데이터베이스 : Part2

다음으로 살펴볼 클래스는 General DatabaseHelper Class 입니다. 이 클래서는 SQLiteOpenHelper를 상속받아서 Database 생성 및 업그레이드, 연결 등의 작업을 담당하고 있으며, 외부 파일에서 SQLiteDatabase instance를 직접 핸들링 하지 않게 하기 위해서 database C/R/U/D 작업을 Wrapping 하고 있습니다. 또한 여러 Activity에서 DB Connection 관련 문제를 해결하기 위해서 Singleton으로 만들었습니다.

//DatabaseHelper.java


package com.overoid.hangul2english.data;

 

import com.overoid.hangul2english.Constants;

import com.overoid.hangul2english.data.H2eDatabaseCreator;

 

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteException;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

 

public class DatabaseHelper extends SQLiteOpenHelper {

 

    private static final String CLASSNAME = DatabaseHelper.class.getSimpleName();

    private static final String KEY_COLUMN = "_id";

   

    private static DatabaseHelper mInstance;

    private static SQLiteDatabase db; 

   

    /***

     * 생성자

     *

     * @param context   : app context

     * @param name      : database name

     * @param factory   : cursor Factory

     * @param version   : DB version

     */

    private DatabaseHelper(Context context, String name, CursorFactory factory, int version) { 

        super(context, name, factory, version); 

        Log.v(Constants.LOG_TAG,  DatabaseHelper.CLASSNAME + "Create or Open database : "+name);

    }

   

    /***

     * 생성자

     *

     * @param context   : app context

     */

    private DatabaseHelper(final Context context) {

        super(context, DatabaseCreator.DB_NAME , null, DatabaseCreator.DB_VERSION);

        Log.v(Constants.LOG_TAG,  DatabaseHelper.CLASSNAME + "Create or Open database : "+ DatabaseCreator.DB_NAME);

    }

   

    /***

     * Initialize method

     *

     * @param context       : application context

     */

    private static void initialize(Context context) { 

        if(mInstance == null) { 

 

            Log.i(Constants.LOG_TAG, DatabaseHelper.CLASSNAME + "Try to create instance of database (" + DatabaseCreator.DB_NAME + ")");

            mInstance = new DatabaseHelper(context);

           

            try {              

                Log.i(Constants.LOG_TAG, "Creating or opening the database ( " + DatabaseCreator.DB_NAME + " ).");               

                db = mInstance.getWritableDatabase();          

            } catch (SQLiteException se) {               

                Log.e(Constants.LOG_TAG, "Cound not create and/or open the database ( " + DatabaseCreator.DB_NAME + " ) that will be used for reading and writing.", se);      

            }

            Log.i(Constants.LOG_TAG,  DatabaseHelper.CLASSNAME + "instance of database (" + DatabaseCreator.DB_NAME + ") created !");

        } 

    }

   

    /***

     * Static method for getting singleton instance

     *

     * @param context       : application context

     * @return              : singleton instance

     */

    public static final DatabaseHelper getInstance(Context context) { 

        initialize(context); 

        return mInstance; 

    } 

   

    /***

     * Method to close database & instance null

     */

    public void close() {       

        if(mInstance != null) {           

            Log.i(Constants.LOG_TAG, DatabaseHelper.CLASSNAME + "Closing the database [ " + DatabaseCreator.DB_NAME + " ].");           

            db.close();           

            mInstance = null;       

        }   

    }

   

    /***

     * Method for select table

     * db.query wrapper 

     * @param table     : table name

     * @param columns   : column name array

     * @return          : cursor

     */

    public Cursor get(String table, String[] columns){       

        return db.query(table, columns, null, null, null, null, null);   

    }       

   

    /***

     * Method for select table

     * @param table     : table name

     * @param columns   : column name array

     * @param id        : record id (pk 컬러명은 "_id" 가능함)

     * @return          : cursor

     */

    public Cursor get(String table, String[] columns, long id){       

        Cursor cursor = db.query(true, table, columns, KEY_COLUMN + "=" + id, null, null, null, null, null);       

        if (cursor != null) {          

            cursor.moveToFirst();       

        }       

        return cursor;   

    }

   

    /****

     * Method for select statements

     * @param sql       : sql statements

     * @return          : cursor

     */

    public Cursor get(String sql) {

        return db.rawQuery(sql, null);

    }

   

    /***

     * Method to insert record

     * @param table     : table name

     * @param values    : ContentValues instance

     * @return          : long (rowid)

     */

    public long insert(String table, ContentValues values) {

        return db.insert(table, null, values);

    }

   

    /***

     * Method to update record

     * @param table     : table name

     * @param values    : ContentValues instance

     * @param id        : record id

     * @return          : int

     */

    public int update(String table, ContentValues values, long id) {       

        return db.update(table, values, KEY_COLUMN + "=" + id, null);   

    }

   

    /***

     * Method to update record

     * @param table         : table name

     * @param values        : ContentValues instance

     * @param whereClause   : Where Clause

     * @return              ; int

     */

    public int update(String table, ContentValues values, String whereClause) {

        return db.update(table, values, whereClause, null);

    }

   

    /***

     * Method to delete record

     * @param table         : table name

     * @param whereClause   : Where Clause

     * @return              : int

     */

    public int delete(String table, String whereClause) {

        return db.delete(table, whereClause, null);

    }

   

    /***

     * Method to delete record

     * @param table         : table name

     * @param id            : record id

     * @return              : int

     */

    public int delete(String table, long id) {

        return db.delete(table, KEY_COLUMN + "=" + id, null);

    }

   

    /***

     * Method to run sql

     * @param sql

     */

    public void exec(String sql) {

        db.execSQL(sql);

    }

   

    /****

     * logCursorInfo    : Cursor 리턴받는 Result 로깅하는 메소드

     * @param c

     */

    public void logCursorInfo(Cursor c) {

        Log.i(Constants.LOG_TAG, "*** Cursor Begin *** " + "Results:" +

                c.getCount() + " Colmns: " + c.getColumnCount());

       

        // Column Name print

        String rowHeaders = "|| ";

        for(int i=0; i<c.getColumnCount(); i++) {

            rowHeaders = rowHeaders.concat(c.getColumnName(i) + " || ");

        }

       

        Log.i(Constants.LOG_TAG, "COLUMNS " + rowHeaders);

        // Record Print

        c.moveToFirst();

        while(c.isAfterLast() == false) {

            String rowResults = "|| ";

            for(int i=0; i < c.getColumnCount(); i++) {

                rowResults = rowResults.concat(c.getString(i) + " || ");

            }

           

            Log.i(Constants.LOG_TAG, "Row " + c.getPosition() + ": " + rowResults);

           

            c.moveToNext();

        }

        Log.i(Constants.LOG_TAG, "*** Cursor End ***");

    }

   

    @Override

    /***

     * Method to create database

     * 데이터베이스 생성. 최초 한번만 실행됨.

     * @param db        :SQLiteDatabase instance

     */

    public void onCreate(SQLiteDatabase db) {

        DatabaseCreator mCreator = new H2eDatabaseCreator();

        String[] tableCreateStmt = mCreator.getCreateTablesStmt();

        String[] indexCreateStmt = mCreator.getCreateIndexStmt();

        String[] initDataDml = mCreator.getInitDataInsertStmt();

       

        try {

            if(tableCreateStmt != null && tableCreateStmt.length > 0) {

                Log.v(Constants.LOG_TAG, DatabaseHelper.CLASSNAME + " - onCreate() : Table Creation");

                for(int i = 0; i < tableCreateStmt.length; i++) {

                    db.execSQL(tableCreateStmt[i]);

                }

            }

           

            if(indexCreateStmt != null && indexCreateStmt.length > 0) {

                Log.v(Constants.LOG_TAG, DatabaseHelper.CLASSNAME + " - onCreate() : Index Creation");

                for(int i = 0; i < indexCreateStmt.length; i++) {

                    db.execSQL(indexCreateStmt[i]);

                }

            }              

           

            if(initDataDml != null && initDataDml.length > 0) {

                for(int i = 0; i < initDataDml.length; i++) {

                    Log.v(Constants.LOG_TAG, DatabaseHelper.CLASSNAME + " - onCreate() : Data Load" + initDataDml[i]);

                    db.execSQL(initDataDml[i]);

                }

                Log.v(Constants.LOG_TAG, DatabaseHelper.CLASSNAME + " - onCreate() : Init Data Load");

            }

           

        } catch(SQLException e) {

            Log.e(Constants.LOG_TAG, DatabaseHelper.CLASSNAME + " - onCreate() : Table Creation Error", e);

        }

 

    }

 

    @Override

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

        Log.v(Constants.LOG_TAG, DatabaseHelper.CLASSNAME + " - onUpgrade() : Table Upgrade Action");

 

    }

 

}

 


소스는 보시면 내용은 많으나 찬찬히 보시면 크게 복잡하거나 어려운 부분은 없는 것 같습니다.
SQLiteOpenHelper에서 상속을 받아 구현한 DatabaseHelper 클래스는 onCreate(), onUpgrade() 같은 SQLiteOpenHelper 콜백 메소드를 구현하고 있습니다. 데이터베이스가 Open 될 때 데이터베이스가 존재하지 않으면 onCreate() 메소드 부분이 실행됩니다. onCreate()내에서는 H2eDatabaseCreator Class에서 정의한 DDDL문을 가져와 실행합니다.
onUpgrade() 메소드는 현재 설치된 DB버전과 코드의 버전을 비교해서 서로 다르면 실행되는 메소드입니다. 저희는 초기 버전이라 별 다른 액션없이 로그만 기록했습니다.

DatabaseHelper의 private 생성자 및 getInstance() 메소드는 싱글톤 패턴의 전형적인 모습입니다.
멀티스레드 환경에서는 싱글톤을 만들 때 synchronized 키워드로 동기화를 하긴 하지만, 스마트폰 앱 환경에서는 그럴 필요가 없어서 부하를 주는 synchronized 키워드 없이 심플하게 구성하였습니다.

싱글톤 관련 메서드 하위의 메서드들은 SQLiteDatabase의 인스턴스가 직접 DB에 레코드 추가,삭제,수정,조회 등의 메서드를 코드 외부에서 SQLiteDatabase의 인스턴스를 직접 핸들링하지 않도록 캡슐화(일종의 wrapper method) 하고 있습니다.
.
logCursorInfo() 메소드는 Cursor로 리턴받은 Result를 log에 기록하는 유틸성 메소드 입니다.
우리코드에서 별 사용할 일은 없겠지만, Contents Provider에서 제공하는 기능을 이용하여 개발할 때, 디버깅시에 편리하게 사용할 수 있습니다.

이제 우리 App의 데이터를 핸들링하는 Dao 클래스를 보겠습니다.

//DataDao.java

package com.overoid.hangul2english.data;

 

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

 

 

import com.overoid.hangul2english.Constants;

import com.overoid.hangul2english.data.H2eDatabase.DataTable;

 

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.util.Log;

 

 

 

public class DataDao {

    private static final String CLASSNAME = DataDao.class.getSimpleName();

    private DatabaseHelper db;

   

    public DataDao(Context context) {

        db = DatabaseHelper.getInstance(context);

       

    }

   

    public void close() {

        db.close();

    }

    /***

     * Inner Class - TO Objects

     * @author jinook.lee

     *

     */

    public static class DataTo {

        private int id;

        private String korText;

        private String engText;

       

        public DataTo() {}

 

        public DataTo(int id, String korText, String engText) {

            this.id = id;

            this.korText = korText;

            this.engText = engText;

        }

 

        @Override

        public String toString() {

            return "DataTo [id=" + String.valueOf(id) + ", korText=" + korText + ", engText=" + engText + "]";

        }

 

        public int getId() {

            return id;

        }

 

        public void setId(int id) {

            this.id = id;

        }

 

        public String getKorText() {

            return korText;

        }

 

        public void setKorText(String korText) {

            this.korText = korText;

        }

 

        public String getEngText() {

            return engText;

        }

 

        public void setEngText(String engText) {

            this.engText = engText;

        }

    }

 

    /****

     * insert       : table 추가하기

     * @param to    : DataTo object

     */

    public void insert(final DataTo to) {

        ContentValues values = new ContentValues();

 

        values.put(DataTable.COLUMN_KOR_TEXT, to.getKorText());

        values.put(DataTable.COLUMN_ENG_TEXT, to.getEngText());

 

        Log.v(Constants.LOG_TAG, DataDao.CLASSNAME + " insert - korText:" + to.getKorText());

        long rowId = db.insert(DataTable.TABLE_NAME, values);

        if(rowId < 0) {

            throw new SQLException("Fail At Insert");

        }

    }

  

    /****

     * update       : UI 사용될 일은 없을듯.

     * @param to    : DataTo object

     */

    public void update(final DataTo to) {

       ContentValues values = new ContentValues();

 

       values.put(DataTable.COLUMN_KOR_TEXT, to.getKorText());

       values.put(DataTable.COLUMN_ENG_TEXT, to.getEngText());

      

       Log.v(Constants.LOG_TAG, DataDao.CLASSNAME + " update - _id:" + String.valueOf(to.getId()));

       db.update(DataTable.TABLE_NAME, values, to.getId());

      

   }

   

    /****

     * delete       : record delete

     * @param id    : record id

     */

    public void delete(final int id) {

        Log.v(Constants.LOG_TAG, DataDao.CLASSNAME + " delete - _id:" + String.valueOf(id));

        db.delete(DataTable.TABLE_NAME, id);

    }

   

    /****

     * get          : select * from table

     * Cursor ArrayList 담아서 리턴한다.

     *

     * @return      : ArrayList DataTo

     */

    public List<DataTo> get() {

        Cursor c = null;

        ArrayList<DataTo> ret = null;

        

        String sql = "SELECT * FROM " + DataTable.TABLE_NAME + " ORDER BY 1";

       

        try {

            Log.v(Constants.LOG_TAG, DataDao.CLASSNAME + " get - All");

            c = db.get(sql);

           

            //db.logCursorInfo(c);

           

            ret = setBindCursor(c);

        } catch (SQLException e) {

            Log.e(Constants.LOG_TAG, DataDao.CLASSNAME + " getList ", e);

        } finally {

            if (c != null && !c.isClosed()) {

                c.close();

            }

        }

       

        return ret;

    }

   

   

    /****

     * SQLite Result Cursor 데이터를 Array List 넣고 리턴하는 메서드.

     * @param c     : cursor

     * @return      : ArrayList<DataTo>

     */

    private ArrayList<DataTo> setBindCursor(final Cursor c) {

        ArrayList<DataTo> ret = new ArrayList<DataTo>();

       

        int numRows = c.getCount();

       

        c.moveToFirst();

       

        // SQL문에서 Join 사용시 테이블명. 사용하면 컬럼명이 틀려지므로 getColumnIndex

        // Exception 낸다. 반드시 alias 사용해 컬럼명을 동일하게 맞춰야 한다.

        // 값이 null 경우 getInt() 0 반환할까? - 반환함.

       

        for(int i=0; i < numRows; i++) {

            DataTo to = new DataTo();

            to.setId(c.getInt(c.getColumnIndex(DataTable.COLUMN_ID)));

            to.setKorText(c.getString(c.getColumnIndex(DataTable.COLUMN_KOR_TEXT)));

            to.setEngText(c.getString(c.getColumnIndex(DataTable.COLUMN_ENG_TEXT)));

 

            ret.add(to);

            c.moveToNext();

        }

       

        return ret;

    }

   

    /****

     * List<DataTo> 내용을 로깅하는 메소드

     * @param to

     */

    public void logListInfo(List<DataTo> to) {

        Log.i(Constants.LOG_TAG,"*** List Begin *** " + "Results:" + to.size());

       

        Iterator<DataTo> itr = to.iterator();

        while (itr.hasNext()) {

            String msg = ((DataTo)itr.next()).toString();

            Log.i(Constants.LOG_TAG, "DATAS: " + msg);

        }

        Log.i(Constants.LOG_TAG,"*** List End ***");

    }

}

 



DataDao는 Activity에서 이용하는 클래스입니다.
테이블마다 혹은 비즈니스 단위마다 하나씩 Dao클래스를 만들어 사용하면 됩니다. 클래스명은 <테이블식별자> + “Dao”로 이루어져 있습니다.
역시 소스는 크게 어려운 코드는 없습니다.

DataDao 클래스는 내부에 중첩클래스로 Transfer Object로 사용할 클래스(Bean)를 담고 있습니다. TO 클래스는 Dao 클래스의 메소드 인자로 사용될 클래스입니다. <테이블 식별자> + “To”로 클래스명이 만들어 졌으며, 테이블의 컬럼에 해당되는 필드와 getter/setter로 이루어져 있습니다.

나머지 DataDao 클래스 소스 부분은 생성자에서 DatabaseHelper 클래스의 인스턴스를 얻어서 멤버변수에 저장하는 로직과, 실제 데이터 핸들링을 위한 get/insert/update/delete 메소드들이 존재합니다. 저희 App에서는 Cursor를 직접 핸들링 하지 않고 자바 개발자들이 많이 사용하는 ArrayList에 결과를 담아서 처리하도록 하겠습니다. 이렇게 작성하면 UI용 Adapter 작성시에도 ArrayAdapter를 사용할 수 있습니다. 실은 제가 익숙해서 사용하는 것입니다. 직접 작성하실 때는 Cursor를 직접 리턴하도록 코드를 작성하셔도 무방합니다.

setBindCursor() 메소드에서는 커서를 ArrayList로 변환하는 기능을 담당하고 있습니다.

logListInfo() 메소드는 ArrayList에 담긴 데이터를 log에 기록해주는 util method 입니다. 디버깅할 때 편리하게 사용할 수 있습니다.

Insert/update 시에는 ContentValues 객체를 사용했습니다. ContentValues 객체는 처리할 컬럼의 이름과 값의 쌍을 담는 객체입니다. 필요한 자료를 ContentValues 객체에 설정한 후 insert/update DatabaseHelper 인스턴스의 insert/update 메소드의 인자로 넘겨주면 됩니다.

위의 DataDao 및 DatabaseHelper에서는 단순한 쿼리에 대해서만 처리가 가능하도록 클래스가 구성되었습니다. 복잡한 쿼리는 SQLiteQueryBuilder Class를 이용해서 구성은 가능합니다만, 저는 그냥 Raw SQL문을 직접 실행하는 것이 쿼리 작성도 편리하고 좋은 것 같아서 SQL문을 실행할 수 있는 메소드만(get(sql), exec(sql) DatabaseHelper에 메소드로 제공하고 있습니다.

이로서 강좌에서 개발하는 Hangul2English App의 DB 부분 소스를 모두 살펴보았습니다.

이 DB 관련 클래스 사용법에 대해서 정리하자면,

H2eDatabase.java 
개발하려는 App의 테이블 구조를 static inner class로 정의(변경) 하시고 클래스명 및 파일명을 변경하시면 됩니다.

DatabaseCreator.java
인터페이스이므로 그대로 소스 복사하시면 됩니다. 패키지 구조만 수정하시면 될 것 같네요.

H2eDatabaseCreator.java
개발하려는 App의 DDL문을 상수로 정의하시고, 관련 메소드내에 포함시키시면 됩니다.

DatabaseHelper.java 
수정할 내용은 거의 없으며, onCreate() 메소드 내에서 인스턴스를 생성하는 클래스명 부분(DatabaseCreator mCreator = new H2eDatabaseCreator();) 만 자신의 DatabaseCreator클래스명으로 수정하시면 됩니다.

DataDao.java 
이 부분은 실제 안쪽 코드를 자신의 앱에 맞게 모두 수정하셔야 합니다.

처음에 제가 계획했던, Copy & Paste로 최소 노력으로 다른 App 개발에 적용하려는 목적은 대략 달성한 것 같습니다. 물론 별도의 library로 만들 수도 있지만, 그러려면 좀 더 다양한 케이스를 지원하도록 많은 코드도 추가되어야 괜챦은 library가 나올 것 같아서.. 그 부분은 차차 해보도록 하겠습니다.

이제 이 DB코드를 이용하는 UI 코드 부분을 살펴 보도록 하겠습니다.

UI코드를 살펴보기 전에 MotoDev의 Database 툴 기능을 편하게 리뷰하고 가도록 하겠습니다.
이번에 포스트 쓰면서 사용해본 기능인데, 소스 코드 Generation 기능이 상당히 맘에 들어 소개합니다.
그럼, Part 3에서 뵙겠습니다.


Trackback 4 Comment 6
  1. 이유식 2010.09.05 14:52 신고 address edit & del reply

    많은걸 배우고 갑니다.

  2. 선지헌 2010.09.29 20:12 신고 address edit & del reply

    많이 배우고 있습니다. 좋은 강좌 감사합니다. 질문이 있어서 글 남기는데요 DatabaseHelper 클래스를 싱글턴으로 만드셨는데요 여러 Activity에서 DB에 접근하는 문제때문이라고 하셨는데 조금 더 자세한 설명을 부탁드리고 싶습니다. 현재 제가 ContentProvider를 이용해 DB접근을 하는 앱을 만들고 있는데 이럴경우에도 싱글턴을 써야 하는 것인지요?

    • 보고픈 2010.09.30 15:48 신고 address edit & del

      제가 직접 해보지는 않아서 정확히는 모르겠으나, ContentProvider로 구현할때는 크게 문제가 되지는 않을듯 싶긴 합니다. 기존 구글쪽 소스를 보더라도 큰 문제는 안되는것 같습니다.

      여러 Activity에서 db 사용할때는 다른 화면에서 db 사용할때 이전 화면에서 db를 close 하지 않으면 에러가 발생합니다.
      싱글톤을 사용하지 않더라도 db를 close() 후 새 activity에서 open()하면 문제가 되지 않습니다. 저는 왠지 그 작업이 부하가 걸릴듯 싶어 싱글톤으로 구현한 것입니다.

  3. 리칼 2010.12.10 17:48 신고 address edit & del reply

    정말 볼수록 감탄합니다.. 감사합니다..

  4. jordan retro 12 2012.03.05 17:58 신고 address edit & del reply

    총괄 은 미국 의 인기 는 패션 여성복 브랜드 나인 의 창시자 토 리 버 치 (총괄) 여사.

  5. 맹순이 2014.10.28 00:34 신고 address edit & del reply

    좋은 코드 리뷰 잘 했습니다. 한 가지 궁금한 점이 있습니다. close 부분에서 helper 인스턴스 까지 close 해버리면 문제가 생기지 않을까 해서요. 만일 멀티 스레드 환경에서 A 액티비티에서 백그라운드 작업을 하는중에 B 액티비티로 넘어간 후 A가 끝나지 않은 상황에서 B에서 close를 해버리는 상황같이요. 한참 전 글인데 보실려나 모르겠네요 ^^

2010.09.01 12:39

[강좌A09] 안드로이드 실전 개발 - 데이터베이스 : Part1




안녕하십니까? 오늘 강좌에서부터는 저희가 개발하고 있는 Hangul2English App에 Database 코드를 붙이도록 하겠습니다.

전체 강좌 목차

[강좌A01] Moteodev Studio를 이용한 안드로이드 개발 환경 구축 가이드
[강좌A02] 안드로이드 개발 참고 서적 소개
[강좌A03] Android 실전 개발 - 아이디어 / 기획 / Wireframe
[강좌A04] 안드로이드 실전 개발 - 아이콘 제작
[강좌A05] 안드로이드 실전 개발 - 레이아웃 및 리소스 : Part1
[강좌A06] 안드로이드 실전 개발 - 레이아웃 및 리소스 : Part2
[강좌A07] 안드로이드 실전 개발 - 리소스 해킹
[강좌A08] 안드로이드 실전 개발 - SQLite

[강좌A09] 안드로이드  실전 개발 - 데이터베이스 : Part1

일반적으로 Android database 개발은 다음과 같이 구분할 수 있습니다.

데이터베이스 생성 방식에 따라
1.Code에서 DDL문 실행 – 일반적인 방식. 간단하고 초기 적재해야 될 데이터가 적은 경우.
2.Assets 폴더에 DB를 생성한 후 실행시에 DB 파일을 복사해서 처리하는 방식 – 초기 데이터가 많거나 DB구조가 복잡할 때 주로 사용함.

데이터베이스 관련 소스 처리 방식에 따라
1.Contents Provider – DB를 다른 어플과 공유해야 할 때, 혹은, 여러명이 개발할 때 주로 사용하는 방식으로 DB 처리를 별도의 Provider로 제공하는 방법
2.직접 DB Access – 직접 DB에 query를 실행하여 처리하는 방식

저희가 개발하는 App은 단 하나의 테이블만 필요하고 초기 데이터로는 샘플로 2~3개의 레코드만 등록한 채 개발할 것이므로 Code에서 직접 DDL을 실행하는 방식으로 개발하도록 하겠습니다. 또한, 혼자서 개발하며, 타 App과 DB를 공유해야 할 필요가 없어서 Content Provider로 개발하지 않고, 개발하기 편리한 직접 Access 방식으로 개발 하도록 하겠습니다.

먼저, 저희가 생성할 데이터베이스 스키마 정보는 다음과 같습니다.

Database Name : han_to_eng_db

/* table ddl */
CREATE TABLE TB_DATA (
_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, /*시퀀스, 자동채번 */
C_KOR_TEXT TEXT, /*한글 저장용*/
C_ENG_TEXT TEXT /*영문 저장용*/
);

/* unique index */
CREATE UNIQUE INDEX TB_DATA_PK ON TB_DATA (_ID);

한글/영문을 저장할 수 있는 테이블 하나와 유니크 인덱스를 하나 만들겁니다. _id 컬럼명은 안드로이드에서 BaseColumn Interface에 정의되어 있는 컬럼명으로 대부분의 코드에서 integer key column name으로 많이 사용합니다. 그래서 저도 _id를 사용하긴 했으나, 맘에 드시는 다른 컬럼명을 사용하셔도 좋습니다. 컬럼명의 “C_”는 컬럼을 구분하기 위한 prefix인데, 없어도 무방합니다. 암튼 저는 위의 DDL문을 기준으로 테이블 및 인덱스를 생성하겠습니다.

먼저, src/ 디렉토리에 다음과 같은 Constants.java 파일을 생성합니다. 이 Constants 클래스는 모든 로그에 들어갈 공통 상수를 정의하겠습니다.

//Constants.java

package com.overoid.hangul2english;

 

public class Constants {

 // Log Class 인자로 넘겨줄 Tag 정의

    public static final String LOG_TAG = "Hangul2English";

}



다음은 데이터베이스쪽 코드입니다.
일반적으로 대부분의 책 및 Android Source Sample에 나타나는 SQL 관련 코드 구조는 다음과 같습니다. 많이들 보셨을 겁니다.


public class DBHelper {

 

/* public static final DB Name, Table Name, Column Name 등을 정의하는 부분 */

 

    private SQLiteDatabase db;

    private final DBOpenHelper dbOpenHelper;

 

   

    public static class <To클래스> {

    /* Transfer Object 사용될 Bean Class 정의*/

    }

   

    /* 데이터베이스 생성. 업데이트, 연결 등의 기능을 제공하는 SQLiteOpenHelper 상속 클래스 정의*/

    private static class DBOpenHelper extends SQLiteOpenHelper {

 

        public DBOpenHelper(final Context context) {

            super(context, DBHelper.DB_NAME, null, DBHelper.DB_VERSION);

        }

 

        @Override

        public void onCreate(final SQLiteDatabase db) {

        /* 테이블 생성, 인덱스 생성, 초기 데이터 적재 코드 */

        }

 

        @Override

        public void onOpen(final SQLiteDatabase db) {

            super.onOpen(db);

        }

 

        @Override

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

        /* 업그레이드시에 처리해야 DDL 실행 */

        }

    }

 

    public DBHelper(final Context context) {

        this.dbOpenHelper = new DBOpenHelper(context);

        establishDb();

    }

 

    private void establishDb() {

        if (this.db == null) {

            this.db = this.dbOpenHelper.getWritableDatabase();

        }

    }

 

    /* 데이터 조회/등록/수정/삭제 관련 코드 왕창 추가*/

}

 


위 코드는 대부분의 안드로이드 샘플이나 책에서 소개하는 데이터베이스 코드 스타일입니다. DBHelper Class 안에 Transfer Object로 사용할 Bean Class와 데이터베이스 생성, 업그레이드, 연결등을 담당할 SQLiteOpenHelper 클래스를 상속하는 클래스 정의, 실제 사용자 DB에서 조회하고 등록,수정, 삭제 등을 처리할 메소드를 모두 DBHelper 클래스 안에 두어 이 파일 하나만 만들면 DB 관련 모든 작업을 할 수 있습니다.

제가 처음 안드로이드 개발할 때 이 방식을 사용했었는데, 단점은 한 파일의 소스코드가 무지 길어진다는 점과 여러 개의 Activity 에서 DB 처리를 해야 할 경우 DB가 Close 되지 않은 상태에서 다른 액티비티에서 Open할 때 에러가 나서 Activity 상태 변화 이벤트 처리 부분이나 select/insert/update/delete 등의 메소드 처리 부분에서 db를 close/open을 해 줘야 에러를 피할 수 있다는 점입니다.

그래서 저는 이 소스 패턴을 조금 변경하도록 하겠습니다.

소스는 향후 재사용성(Copy & Paste)을 높이는데 초점을 두겠습니다. 또한, 여러 Acticity에서 DB Handling시에 Connection 관련 에러를 막기 위해서 SQLiteDatabase 객체를 Singleton으로 만들겠습니다.
또한, DDL문과 Table, Column 정의 부분을 별도의 클래스로 작성하도록 하겠습니다.

먼저 데이터베이스 스키마를 정의하는 클래스 소스입니다. 클래스명은 데이터베이스명(Hangul2English = H2e) + “Database”라고 명명하겠습니다.

//H2eDatabase.java

package com.overoid.hangul2english.data;

 

public final class H2eDatabase {

   

    H2eDatabase() {}

   

    /* Table이나 View마다 중첩 클래스를 만듭니다.

     * 테이블명, 컬럼명 정보를 상수로 정의합니다.

     */

    public static final class DataTable {

        private DataTable() {}

       

        public static final String TABLE_NAME = "TB_DATA";

       

        public static final String COLUMN_ID = "_id";

        public static final String COLUMN_KOR_TEXT = "c_kor_text";

        public static final String COLUMN_ENG_TEXT = "c_eng_text";

       

        public String[] getColumnNames() {

            String[] columnNames = {COLUMN_ID,COLUMN_KOR_TEXT,COLUMN_ENG_TEXT};

            return columnNames;

        }

    }

}



H2eDatabase Class 내에 중첩 클래스로 각 테이블마다 클래스를 만듭니다. 저희는 하나의 테이블이라서 DataTable Class 하나만 내부에 존재하면 됩니다.
클래스명은 <테이블명 식별자> + “Table” 이라고 짓습니다. 그리고 TABLE_NAME 상수와 각 컬럼명을 “COLUMN “ + “_” + <컬럼명 식별자> 로 기술하며, SELECT문에서 사용하기 편리하도록 컬럼명 배열을 리턴하는 getColumnNames 메소드를 추가합니다.

다음은 DDL문 생성을 위한 interface를 하나 만들겠습니다. 구지 interfaces는 없어도 상관없지만, 상속 받는 클래스에서 코드도 편리하게 할 수 있고, 일관성을 유지할 수 있기 때문에 interface를 사용했습니다. Interface의 내용은 별거 없습니다. 생성할 DB명과 DB버전 상수정보와, 각각의 DDL문 배열을 리턴하는 메소드가 선언되어 있습니다.

//DatabaseCreator.java

package com.overoid.hangul2english.data;

 

public interface DatabaseCreator {

   

    public static final String DB_NAME = "han_to_eng_db";

    public static final int DB_VERSION = 1;

   

    /* Table Definition Statement */

    public String[] getCreateTablesStmt();

   

    /* Index Definition Statement */

    public String[] getCreateIndexStmt();

   

    /* View Definition Statement */

    public String[] getCreateViewStmt();

   

    /* Trigger Definition Statement */

    public String[] getCreateTriggerStmt();

   

    /* Initial Data Insert Statement */

    public String[] getInitDataInsertStmt();

}



다음은 실제 DDL문을 리턴하는 DatabaseCreator 인터페이스를 구현하는 클래스입니다.

//H2eDatabaseCreator.java

package com.overoid.hangul2english.data;

 

import com.overoid.hangul2english.data.H2eDatabase.DataTable;

 

public class H2eDatabaseCreator implements DatabaseCreator {

 

   

    /* Table Creation DDL */

    private final String TABLE_CREATE_DATATABLE = "CREATE TABLE "

        + DataTable.TABLE_NAME + " ( "

        + DataTable.COLUMN_ID + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "

        + DataTable.COLUMN_KOR_TEXT + " INTEGER, "

        + DataTable.COLUMN_ENG_TEXT + " TEXT); ";

       

   

    /* Index Create DDL */

    private final String INDEX_CREATE_DATATABLE = "CREATE UNIQUE INDEX "

        + DataTable.TABLE_NAME + "_pk ON "

        + DataTable.TABLE_NAME + " (" +  DataTable.COLUMN_ID + " );";

   

    @Override

    public String[] getCreateTablesStmt() {

        String[] tableStmt = {TABLE_CREATE_DATATABLE};

        return tableStmt;

    }

 

    @Override

    public String[] getCreateIndexStmt() {

        String[] indexStmt = {INDEX_CREATE_DATATABLE};

        return indexStmt;

    }

 

    @Override

    public String[] getCreateViewStmt() {

        // TODO Auto-generated method stub

        return null;

    }

 

    @Override

    public String[] getCreateTriggerStmt() {

        // TODO Auto-generated method stub

        return null;

    }

 

    @Override

    public String[] getInitDataInsertStmt() {

        String[] initData = new String[2];

        String[][] initValue = {{"사랑","tkfkd"},

                                {"깍쟁이","Rkrwoddl"}};

 

        for(int i=0; i<2; i++) {

            initData[i] = "INSERT INTO " + DataTable.TABLE_NAME + "("

                         + DataTable.COLUMN_KOR_TEXT + ","

                         + DataTable.COLUMN_ENG_TEXT + " ) "

                         + " VALUES( "

                         + "'" + initValue[i][0] + "','" + initValue[i][1] + "');";

        }

        return initData;

    }

 

}

 

각각의 DDL문을 상수로 정의한 다음 DatabaseCreator 인터페이스 구현 메소드에서 각각의 DDL 문장 배열을 리턴하도록 작성합니다.

나머지 소스들이 내용이 많아서 다음 파트에서 나머지 내용을 계속 진행하도록 하겠습니다.

Trackback 1 Comment 2
  1. jordan retro 13 2012.03.05 17:58 신고 address edit & del reply

    총괄 은 미국 의 인기 는 패션 여성복 브랜드 나인 의 창시자 토 리 버 치 (총괄) 여사.

  2. jordan retro 12 2012.03.05 17:59 신고 address edit & del reply

    총괄 은 미국 의 인기 는 패션 여성복 브랜드 나인 의 창시자 토 리 버 치 (총괄) 여사.

2010.08.27 12:48

[강좌A08] 안드로이드 실전 개발 - SQLite



안녕하십니까? 8번째 강좌가 시작되었습니다. 이번 강좌에서부터 데이터베이스 관련한 강좌를 다룰 예정입니다. 본격적인 데이터베이스 개발에 앞서서 SQLite DB에 대해서 먼저 알아보도록 하겠습니다.

여담이지만, 제 강좌에 대한 호응도를 보니 일반 안드로이드 소스 개발 보다는 개발에 관련된 주변 정보(아이콘, 도서 소개, 리소스 해킹 등)에 대한 강좌가 더 인기가 있는 것 같습니다. 소스 부분이야 다른 곳에도 많이 있고 그래서 그런가요? 그래도 별 실력도 없는 제 강좌를 좋아해 주시는 분들이 계시니 맘은 뿌듯합니다.

각설하고.. SQLite 다들 잘 아시는지요? 저는 안드로이드 하면서 처음 만졌습니다. 맨날 오라클만 만지다가(하하 제가 얘기했던가요? 저는 모바일 개발자도 아니고, 자바 개발자도 아니고 Oracle SQL, PL/SQL, 모델링만 주로 만지고 있습니다.) 가벼운 SQLite를 다루려고 보니, DDL 문법도 다른 점도 많고, SQL도 그렇고.. 그렇다고 자료도 별로 없고..

SQLite에 대해서 강좌를 쓰다보니, 내용이 너무 많아져서.. SQLite User Guide란 글들을 별도로 작성해서 제 블로그에 올려두었습니다. 본 강좌는 SQLite User Guide 제글의 일부분을 요약한 내용과 일부 Android 관련된 정보를 추가한 문서입니다. 강좌를 보시고 보다 SQLite에 대해서 상세히 알고싶으시면 다음의 링크나 제 블로그의 데이터베이스/SQLite 디렉토리를 클릭하셔서 살펴보시기 바랍니다.
(http://overoid.tistory.com/category/데이터베이스/SQLite)

전체 강좌 목차

[강좌A01] Moteodev Studio를 이용한 안드로이드 개발 환경 구축 가이드
[강좌A02] 안드로이드 개발 참고 서적 소개
[강좌A03] Android 실전 개발 - 아이디어 / 기획 / Wireframe
[강좌A04] 안드로이드 실전 개발 - 아이콘 제작
[강좌A05] 안드로이드 실전 개발 - 레이아웃 및 리소스 : Part1
[강좌A06] 안드로이드 실전 개발 - 레이아웃 및 리소스 : Part2
[강좌A07] 안드로이드 실전 개발 - 리소스 해킹
[강좌A08] 안드로이드 실전 개발 - SQLite


1. SQLite 소개

SQLite는 아시다시피 iphone이나 android에서 사용하는 파일 베이스 RDB입니다. 주요 특징으로는 Zero Configuration, Portabiliy, Compactness, Simplicity, Flexibility, Liberal Licensing, Reliability 라고 www.sqlite.org 사이트에 나와 있습니다. 좋은 말은 다 있는것 같습니다.

저희가 알아야 될 세부 특징은 다음과 같습니다. 중요합니다.

SQLite는 ANSI92의 기능을 대부분 지원하지만, 아래의 사항은 지원하지 않습니다.

1. RIGHT and FULL OUTER JOIN : LEFT OUTER JOIN만 지원합니다.
2. Complete ALTER TABLE Support : RENAME TABLE과 ADD COLUMN만 지원합니다. DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT 등 다른 기능은 지원하지 않습니다.
3. Writing to VIEWs : SQLite에서 View는 read-only입니다.
4, GRANT and REVOKE : Sqlite에서 읽기/쓰기 권한은 OS 파일 시스템 권한을 사용합니다. 별도로 권한 부여 기능이 없습니다.

2. GUI Tools

SQLite 개발을 효과적으로 하기 위해서는 좋은 툴은 필수겠죠?

SQLite를 지원하는 GUI Tools 리스트는 아래 링크에 나와있습니다.
http://sb2.info/commercial-and-freeware-sqlite-tools-list-2/

무지 많습니다. 그 중 몇개를 골라서 사용해 봤는데.. 제가 추천해 드리고 싶은 툴은 다음과 같습니다.

SQLite Expert (http://www.sqliteexpert.com/)



여러 제품 중에서 가장 무난하게 사용할 수 있는 제품인 것 같습니다. 필요한 기능은 다 있고, UI도 그럭저럭 쓸만합니다. Personal Edition과 Professional Edition으로 나누어지며, Personal Edition은 무료입니다.
(향후 SQLite관련 포스트에서 저는 이 제품으로 테스트 하겠습니다.) Pro Edition의 Crack도 그리 어렵지 않게 구할 수 있습니다.

SQLite Maestro (http://www.sqlmaestro.com/products/sqlite/maestro/)


아마도 제가 본 Sqlite 관련 툴 중에서 가장 강력한 제품인 것 같습니다. 다른 기능은 몰라도 Reverse로 ERD를 만들어 주는 기능은 이 제품만이 가능합니다.

여담이지만 제가 Touch Call(터치콜) App 개발할 당시 Android SDK2.0 기준의 전화번호부 DB를 이용해야 하는데, 구글의 document만으로는 이해하기가 상당히 어려웠습니다. 출판되어 있는 책들도 다 이전 데이터베이스를 기준으로 작성된 책들이고.. 그때 이 제품을 평가판으로 설치해서 애뮬레이터에 있는 contact2 데이터베이스를 가지고 ERD를 만들어 보니… 훨씬 빠르게 이해를 할 수 있었습니다. 

다음 그림은 그 당시 사용했던 contact2 db의 ERD입니다.



좋은 제품이긴 하지만 애석하게도 free 버전은 없습니다. 모두 상용버전 밖에 없으며, 한달 Trial 버전만 사용할 수 있습니다. 현재 버전의 crack은 거의 구하기 힘들며, 이전 버전은 구하실 수는 있을 겁니다.

DeZign (http://www.datanamic.com/dezign/index.html)
현재 SQLite를 Forward/Backward Engineering를 완벽하게 지원하는 모델링 툴은 datanamic의 dezign이란 모델링 툴 밖에 없습니다. 근데, 막상 평가판 설치해보니..저희 정서와 맞지 않는 툴이더군요. 저희는 한글로 논리모델을 만들고 영문으로 물리모델을 만드는데, 이 툴은 그런 개념없이 논리/물리가 함께 처리되는 툴이라 바로 지워 버렸습니다. 조금 불편하더라도 DA#이나 ERWin으로 모델링을 하고, Forward Generation한 스크립트를 수정해서 사용하는 게 더 편리할 듯 합니다.

끝으로 SQLite 강좌에서 사용할 demo db에 대해서 소개를 하겠습니다. 위에서 얘기했듯이 저는 SQLlite Expert Personal 버전을 이용해서 테스트를 하도록 하겠습니다.

SQLite Expert Personal를 실행한 후 File > Open Demo Database를 실행합니다.
Dbdemos란 데이터베이스가 스키마브라우저에 나타납니다.


좌측 스키마 브라우저에 많은 테이블 리스트가 나옵니다. 저는 그중에서 아래 ERD에 나오는 몇 개의 테이블을 주로 사용해서 DML 테스트 등을 진행하도록 하겠습니다.


고객, 직원, 주문, 주문내역 등 간단한 ERD 구조입니다.
참고로 이 ERD는 Dbdemos SQLlite 데이터베이스를 SQLite Maestro를 이용하여 일부 테이블만 designer에 띄운 모습입니다.)

3. System Catalog

시스템 카탈로그를 조회할 수 있는 방법은
sqlite_master 테이블을 직접 조회하시면 됩니다. SQLITE_MASTER 테이블은 READ-ONLY 테이블입니다.

SELECT * FROM SQLITE_MASTER;

SQLITE_MASTER 테이블을 조회하시면 테이블, 인덱스, 트리거등 모든 정보와 DDL문까지 알 수가 있습니다.

SQLITE_MASTER 테이블 조회 외에 PRAGMA 명령어를 통해서도 필요한 정보를 알 수 있습니다.

PRAGMA table_info(table-name);
테이블 정보를 조회하는 명령어입니다.

PRAGMA table_info(COUNTRY);

cid name type notnull dflt_value pk
0 Name CHAR(24) 0  1
1 Capital CHAR(24) 0  0
2 Continent CHAR(24) 0  0
3 Area FLOAT 0  0
4 Population FLOAT 0  0


PRAGMA index_list(table-name); 인덱스 리스트를 볼 수 있습니다.
PRAGMA index_info(index-name); 인덱스 정보를 조회할 수 있습니다.
PRAGMA foreign_key_list(table-name); fk 리스틀 볼 수 있습니다.

4. DATATYPE

Sqlite가 지원하는 데이터 타입은 다음과 같습니다.

1. Null
2. Integer – 부호있는 정수, 실제 값에 따라 1byte에서 8byte까지 가변적으로 저장됨.
3. Real – 실수
4. Text – 문자열
5. BLOB – blob 데이터

실제적으로 저희가 테이블 생성시 DDL상에 VARCHAR(10)이라고 컬럼 사이즈를 정의해도 SQLITE는 TEXT 타입으로 만들어집니다. 그렇기 때문에 10자 이상의 데이터도 삽입이 가능합니다. 그러니, DDL 문 만들 때 구지 다른 데이터 타입을 외울 필요없이 위 타입만 알고 있으면 될 것 같습니다.

재미있는 것은 Data and Time 즉, 날짜 관련 데이터 타입이 따로 없다는 것입니다. DATETIME은 입력되는 값에 따라서 TEXT, REAL, INTEGER 타입으로 저장됩니다. http://www.sqlite.org/datatype3.html 보시면 어떤 데이터 타입이 어떤 식으로 변경되는지 알수가 있습니다.

5. CREATE TABLE


예제1)
CREATE TABLE TEST2 (
     _ID INTEGER NOT NULL,    
     CLASS TEXT NOT NULL,    
     VALUE TEXT,
     CONSTRAINT TEST2_PK PRIMARY KEY (_ID, CLASS)
);

SQLite에서는 ALTER TABLE 문에서 ADD CONSTRAINT 구문이 지원되지 않기 때문에 PRIMARY KEY, UNIQUE, CHECK등의 TABLE LEVEL의 CONSTRAINT는 위 문장처럼 CREATE TABLE 문 제일 하단에 기술해야 합니다.


예제2)
CREATE TABLE TEST1 (
     _ID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
     TYPE TEXT NOT NULL DEFAULT 'HIGH',
     NAME TEX);

일련번호 채번은 위와 같이 AUTOINCREMENT 구문을 CRATE TABLE 의 해당 컬럼에 기술하면 자동 증가되는 일련번호를 사용할 수 있습니다.
AUTOINCREMENT 문을 가진 DDL이 최초 실행되면 SQLITE는 내부적으로 SQLITE_SEQUENCE 테이블을 생성합니다. 이 테이블은 NAME, SEQ 컬럼을 가진 테이블로 테이블마다 자동 증가되는 최종값을 가집니다. 재미있게도 SQLITE_SEQUENCE 테이블값을 직접 핸들링도 가능합니다.

6. SELECT

SELECT 는 워낙 잘 들 아시는 부분이라  한 두가지만 테스트 해 보도록 하겠습니다.
먼저, 오라클의 dual 테이블 같이 처리되는 구문은 MS-SQL 처럼 FROM 절 없이 사용하면 될 것 같습니다.

예)
SELECT 'A' a


페이징 처리 (ROWNUM, TOP과 유사한 기능)

페이징 처리시 오라클에서는 ROWNUM을 사용하고 MS-SQL에서는 TOP을 주로 사용들 합니다. 물론 요즘은 ROW_NUMBER() 함수를 더 많이 사용하신다구요?
SQLITE에서는 페이징 처리하기가 더 편리합니다. LIMIT와 OFFSET이 그것입니다.

SELECT CUSTNO, COMPANY, CITY
  FROM CUSTOMER 
 ORDER BY CUSTNO
 LIMIT 5 OFFSET 5;


ORDER BY와 상관없이 사용이 가능합니다. LIMIT에 값을 지정하면 화면에 출력할 레코드 개수를 지정할 수 있습니다. OFFSET은 건너뛸 레코드 수를 나타냅니다. 즉, 위의 쿼리는 CUSTNO로 정렬해서 나온 순서에서 6번째부터 5개의 레코드를 가져오는 SELECT문입니다. OFFSET은 생략도 가능합니다.

페이징은 편리하나 오라클의 ROWNUM 처럼 결과에 수치값을 나타낼 방법이 있다면 여러모로 편리한데 그런 기능은 아쉽습니다. 필요시에는 별도의 NUMBER를 가진 테이블을 하나 만들어서 조인해서 사용해야 할 듯 합니다.

7. UPDATE (JOIN)

UPDATE 구문도 워낙 잘 아시니 여기서는 JOIN UPDATE에 대해서만 확인해 보도록 하겠습니다.
테스트를 위해 CUSTOMER 테이블에 컬럼을 2개 추가했습니다.

/* JOIN UPDATE TEST */
-- ALTER TABLE에서 한번여 여러 컬럼 추가도 안됩니다.

ALTER TABLE CUSTOMER
ADD CAPITAL TEXT;

ALTER TABLE CUSTOMER
ADD COLUMN CONTINENT TEXT;

-- 오라클 스타일 JOIN UPDATE TEST => ERROR
UPDATE CUSTOMER C
   SET (CAPITAL, CONTINENT) = (SELECT CAPTIAL, CONTINENT  
                                WHERE COUNTRY K                               
                                  AND K.[Name] = C.COUNTRY)                                 
 WHERE COUNTRY IN (SELECT NAME FROM COUNTRY);

-- MS-SQL 스타일 JOIN UPDATE => ERROR
UPDATE CUSTOMER
   SET CAPITAL = K.CAPITAL,  
       CONTINENT = K.CONTINENT      
FROM CUSTOMER C JOIN COUNTRY K ON C.Country = K.NAME;


찾아보니 JOIN UPDATE 자체를 지원하지 않는답니다. 에구, 이게 안되는 건 좀 치명적인듯. 업데이트시에 어플에서 처리하는 방식밖에 안될 것 같습니다.

8. DELETE

DELETE시에도 LIMIT와 OFFSET 구문을 함께 사용할 수 있습니다. 이 부분은 편리한 듯~ , 참 UPDATE 구문에도 LIMIT와 OFFSET 구문을 함께 사용할 수 있습니다.

DELETE문도 단순 스타일은 워낙 잘 들 아시니 중복 레코드 제거 쿼리 테스트만 해보도록 하겠습니다.


/* 중복 제거 DELETE 문 테스트 */

-- 임시 테이블 생성.
create table t1 (
id integer,
name varchar(10)
);

-- 테스트 데이터 삽입.
insert into t1 values(1,'a');
insert into t1 values(2,'a');
insert into t1 values(2,'b');
insert into t1 values(1,'b');
insert into t1 values(1,'C');

-- 오라클에서 주로 사용하는 방식으로 테스트.
-- ANY 키워드를 지원하지 않아서 에러가 발생함.
DELETE FROM t1 A
WHERE ROWID > ANY (SELECT ROWID
                                     FROM t1 B
                                    WHERE A.id = B.id);

-- DELETE문에서 서브쿼리로 조인을 지원안해서 이것도 에러가 발생함.
DELETE FROM t1 A
WHERE ROWID > (SELECT MIN(ROWID)
                              FROM    t1 B
                             WHERE A.id = B.id);

-- 조금은 부하가 있지만, GROUP BY절을 이용한 NOT IN 서브쿼리로 중복 제거 가능.
DELETE FROM t1
WHERE ROWID NOT IN (SELECT MIN(ROWID)
                      FROM T1                     
                     GROUP BY ID);

9.Core Function

 coalesce(X,Y,...) coalesce() 함수는 Argument 중에서 첫번째로 Not Null인 Argument값을 리턴하는 함수입니다. 만일 모든 인자가 null이면 null을 리턴합니다.
 ifnull(X,Y) ifnull() 함수는 두 인자중에서 첫번째로 Not Null인 인자값을 리턴합니다. 만일, 둘다 null이면 null을 리턴합니다. Ifnull() 함수는 인자가 두개인 coalesce() 함수와 동일합니다.
 length(X) 길이값을 리턴하는 함수입니다. 만일 X 인자가 null이면 null을 리턴합니다.
 like(X,Y)
 like(X,Y,Z)
Like 함수는 “Y LIKE X [ESCAPE Z]”구문과 동일합니다.
 lower(x) 소문자로 변환 합니다.
 upper(X) 대문자로 변환 합니다.
 ltrimX)
 ltrim(X,Y)
ltrim(X)는 X 값 중 왼쪽편의 공백을 제거하는 함수입니다.
ltrim(X,Y)는 X 문자열중에서 Y에 나타난 값을 제일 좌측부터 제거하는 함수입니다.
 
select ltrim(" ZZZZabcZZ ", " aZ")
=> 좌측문자열에서 부터 공백,a,Z 문자열이 있으면 제거하고, 처음으로 공백,a,Z가 아닌 문자열부터 출력합니다. 즉, “bcZZ”가 출력됩니다.
ltrim(" ZZZZabcZZ ", " ") 는 ltrim(" ZZZZabcZZ ")과 동일합니다.
 rtrim(X)
 rtrim(X,Y)
rtrim(X)는 우측편 공백 제거
rtrim(X,Y)는 ltrim(X,Y)와 동일한 방식이지만 우측편부터 매칭되는 글자를 제거합니다. 예를들어 select rtrim(" ZZZZabcZZ ", " Z") 문장은 우측편부터 공백과 Z를 빼고 처음으로 공백과 Z가 아닌 글자, 즉 c 까지 글자가 나타납니다. 결과값: “ ZZZZabc”
 trim(X)
 trim(X,Y)
trim(X)는 양쪽 공백 제거
trim(X,Y) 는 Y에 해당되는 글자를 양쪽 끝에서 부터 제거하고 나머지 글자만 리턴함. 예) trim(" ZZZZabcZZ ", " Z") => “abc” trim은 ltrim과 rtrim을 각각 적용한것과 동일한 결과가 나타납니다.
 max(X,Y,..) 인자값들 중 최대값을 리턴합니다.

create table t1 (coll integer, col2 integer, col3 integer);

insert into t1 values(1,2,3);
insert into t1 values(5,3,1);

select max(col1, col2, col3) from t1;
 min(X,Y,...) 인자값들 중 최소값을 리턴합니다.
 nullif(X,Y) 두 인자가 서로 같으면 null을 리턴, 서로 다르면 X값을 리턴합니다.
nullif('x','y') => ‘x’ , nullif('x','x') => null 리턴
 quote(X) Quote()함수는 single quotation을 escape 해줍니다. ‘값을 ‘’ 로 변경합니다.
Insert나 update 시에 사용하면 유용할 듯 합니다.
select quote("girl's mouse") => 'girl''s mouse'
 random(*) -9223372036854775808 부터 +9223372036854775807 숫자 사이의 임의의 수를 리턴합니다.
 randomblob(N) N으로 지정된 bytes의 랜덤 바이너리 데이터를 생성합니다.
 hex(X) 바이너리 값을 hex 값으로 변경합니다.
select hex(randomblob(16))
 replace(X,Y,Z) X 문자열 중에서 Y문자열을 Z로 변경합니다.
select replace('1/12/2009','1','x') => "x/x2/2009"
 round(X)
 round(X,Y)
반올림 함수. Y는 소수점 자리. Y가 없으면 0으로 처리합니다.
Round(3.5) => 4, round(2.555, 2) => 2.56
 substr(X,Y)
 substr(X,Y,Z)
substr()함수는 X문자열 중에서 Y번째부터 시작해서 Z개수만큼 문자열을 가져오는 함수입니다. Z가 생략되면 Y번째 문자열부터 문자열 끝까지 리턴합니다. Y의 최소값은 1입니다. 자바에서는 0으로 시작하지만 SQLite에서는 1부터 인덱스가 시작합니다. 만일 Y가 –(마이너스)값이면 문자열 우측끝부터 카운팅을 시작합니다.
select substr("string", 1, 3) => str
select substr("string", 0, 3) => st
select substr("string", -1, 3) => g
select substr("string", -3, 3) => ing
select substr("string", 2) => tring
 typeof(X)  X 표현식에 대한 데이터 타입을 리턴합니다. 리턴값은 “null”, “integer”, “real”, “text”, “blob” 중의 하나입니다.

10. Aggregation Function (집합 함수)

avg(X) 그룹내의 Not Null값의 평균값을 리턴합니다. X 컬럼값이 문자열이나 BLOB이면 0으로 간주하고 처리합니다.
count(*)
count(X)
count(X)는 X가 Not Null값을 가진 레코드의 개수 리턴합니다.
count(*)는 그룹내의 모든 rows의 수를 리턴합니다.
group_concat(X)
group_concat(X,Y)
X가 not null인 경우 그룹내의 모든 문자열을 콤마(,)를 구분자로 해서 문자열을 합쳐서 리턴합니다. Y가 주어지면 Y값이 구분자로 처리됩니다.
max(X) 그룹내의 값들중 최대값을 리턴합니다.
min(X) 그룹내의 값들중 최소값을 리턴합니다. 모든값이 Null이면 Null을 리턴합니다.
sum(X)
total(X)
Sum과 total은 그룹내의 Not Null값의 합계를 리턴합니다. X의 모든 값이 Null인 경우 Sum()은 Null을 리턴하고 Total()은 0.0을 리턴합니다.

11. DateTime 관련 Keyword

먼저 SQLite에서는 DateTime관련해서 다음과 같은 세가지 키워드가 있습니다.
CURRENT_TIME : 현재 시간 (형식: 03:22:56) 다만, UTC 기준입니다.
CURRENT_DATE: 현재 날짜 (형식: 2010-08-25) UTC 기준.
CURRENT_DATETIME : 현재 날자 및 시간 (형식: 2010-08-25 03:23:37) UTC 기준.

UTC 기준 날짜 및 시간이라서 사용할때는 로컬시간으로 다시 변환작업이 필요할 듯 보입니다. DATETIME 관련 함수들이 있어 크게 유용해 보이지는 않습니다.

12. DateTime 관련 함수.

DateTime 관련 함수는 다음의 5가지가 있습니다.

date(timestring, modifier, modifier, ...)  : 날짜 함수
time(timestring, modifier, modifier, ...)  : 시간 함수
datetime(timestring, modifier, modifier, ...)  :날짜/시간 함수
julianday(timestring, modifier, modifier, ...)  : 율리우스력 함수
strftime(format, timestring, modifier, modifier, ...)  : 날짜 포맷팅 함수


위 다섯개의 함수 인자 중 timestring 파라미터에로 들어갈 수 있는 날짜 형식은 아래와 같습니다.

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

strftime 함수에 사용되는 format에 사용할 수 있는 값들은 다음과 같습니다.

%d    day of month: 00
%f     fractional seconds: SS.SSS
%H    hour: 00-24
%j     day of year: 001-366
%J     Julian day number
%m    month: 01-12
%M    minute: 00-59
%s     seconds since 1970-01-01
%S     seconds: 00-59
%w     day of week 0-6 with sunday==0
%W     week of year: 00-53
%Y     year: 0000-9999
%%    %

함수의 modifer에 들어갈 수 있는 값들은 다음과 같습니다.

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc


잘 안 와 닿는듯 합니다. 아래 샘플 코드를 보면 이해가 가실 겁니다.

--UTC 기준의 현재 날짜/시간
select datetime('now');
2010-08-25 04:01:46

-- 로컬 기준의 현재 날짜/시간
select datetime('now','localtime');
2010-08-25 13:02:30

--현재 로컬 기준 시간에서 10분 3.5초를 더한 시간.
select datetime('now','localtime','+3.5 seconds','+10 minutes');
2010-08-25 13:14:15

--현재 로컬 시간에 3.5초를 더하고 날짜는 돌아오는 화요일 (weekday == 0 이 일요일입니다.)
select datetime('now','localtime','+3.5 seconds','weekday 2');
2010-08-31 13:05:39

--현재 달의 마지막 날짜
SELECT date('now','start of month','+1 month','-1 day','localtime');
2010-08-31

--2004-01-01 02:34:56초부터 현재까지의 총 초
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
209785028

--현재날짜/시간 기준에서 올해 9번째달의 첫번째 화요일
SELECT date('now','start of year','+9 months','weekday 2');
2010-10-05

-- 날짜 포맷 스타일 변경
select strftime("%Y/%m/%d %H:%M:%S",'now','localtime');
2010/08/27 09:17:22



이것으로서 간략하게 나마 SQLite에 대해서 알아보았습니다.
보다 상세히 SQLite의 기능에 대해서 알고싶으시면 제 블로그의 SQLite User Guide를 참고하시기 바랍니다.

SQLite User Guide 블로그 포스트

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger

Trackback 4 Comment 3
  1. 이유식 2010.08.30 14:20 신고 address edit & del reply

    안드로이드 2.1버전에서 foreign key가 적용되나요?

    • 보고픈 2010.08.30 17:23 신고 address edit & del

      제가 알기로 현재 SQLite 버전은 3.7.0.1. 입니다.
      Android 1.5, 1.6, 2.1에는 SQLite 3.5.9 버전이 탑재되어 있습니다.
      Android 2.2에는 SQLite 3.6.22가 탑재되어 있구요.
      FK는 SQLite 3.6.19에 추가되었습니다.

      그러니 Android 2.1에는 FK가 지원되지 않을겁니다.

  2. 2012.12.10 14:37 address edit & del reply

    비밀댓글입니다

2010.08.27 10:45

SQLite User Guide for Android, iPhone - Trigger



SQLite Databae에 대한 일곱번째 포스트입니다. 이번 포스트에서는 SQLite의 Trigger에 대해서 다뤄보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

CREATE TRIGGER

CREATE TRIGGER 문으로 트리거를 생성할 수 있습니다. 다들 아시겠지만, 트리거는 INSERT, UPDATE, DELETE 문장이 실행될 때 자동으로 실행되는 코드입니다. 현재 SQLite는 FOR EACH ROW (각 레코드 마다 트리거 코드가 실행됨) 만 지원합니다. 즉, SQL 문장 단위의 트리거는 지원하지 않습니다.

트리거 코드에서는 각 레코드가 INSERT/UPDATE/DELETE 될때 OLD 및 NEW 키워드를 사용하여 변경전 데이터 혹은 변경되는 데이터를 참조할 수 있습니다. 물론 INSERT에서는 NEW 키워드만, DELETE에서는 OLD 키워드만 유효합니다.

특이하게도 SQLite에서는 View에도 트리거를 설정할 수 있는데, INSTEAD OF 트리거로 만들 수 있습니다. SQLite에서는 View가 Read Only이므로 뷰에 대한 I/U/D 작업이 불가능 하지만,  View에 INSTEAD OF 트리거를 만들어서 트리거에서 I/U/D 작업을 수행하는 것은 가능합니다.

SQLite에서는 트리거를 I/U/D에 대해 각각 만들어야 합니다. 오라클 같은 경우는 하나의 트리거에서 모든 것을 처리할 수 있지만 SQLite에서는 그렇지 않아서 조금 귀챦기는 할 것 같습니다.

샘플 코드를 보면서 트리거에 대해서 확인하도록 하겠습니다.

/* SQLite Trigger Test Code */

-- Trigger test를 위해서 user table과 user_log  table을 생성합니다.
create table user (
  id integer primary key autoincrement,
  name varchar(10),
  password varchar(10)
);

create table user_log (
  log_no integer primary key autoincrement,
  id integer,
  name_before varchar(10),
  name_after varchar(10),
  password_before varchar(10),
  password_after varchar(10),
  change_date text, 
  flag varchar(1)
); 

-- update에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_update BEFORE UPDATE ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(old.id, old.name, new.name, old.password, new.password, datetime('now','localtime'),'U');
END;

-- insert에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_insert BEFORE INSERT ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(new.id, null, new.name, null, new.password, datetime('now','localtime'),'I');
END;

-- delete에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_delete BEFORE DELETE ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(old.id, old.name, null, old.password, null, datetime('now','localtime'),'D');
END;

-- test를 위한 데이터 insert
INSERT INTO USER(name, password) VALUES('overoid','1234');
INSERT INTO USER(name, password) VALUES('test','12345');
UPDATE USER SET NAME = '보고픈'  WHERE ID = 2;
UPDATE USER SET password = 'abcd' WHERE ID =1;
DELETE FROM USER WHERE id = 2;

-- 데이터 확인 (null 데이터를 명확하게 표기하기 위해서..ifnull 함수를 사용함)
SELECT log_no, id, ifnull(name_before,'<null>') name_before, ifnull(name_after,'<null>') name_after,
       ifnull(password_before,'<null>') password_before, ifnull(password_after,'<null>') password_after, change_date, flag
  FROM user_log;

-- result
log_no id name_before name_after password_before password_after change_date flag
1 -1 <null> overoid <null> 1234 2010-08-25 17:02:00 I
2 -1 <null> test <null> 12345 2010-08-25 17:02:02 I
3 2 test 보고픈  12345 12345 2010-08-25 17:02:04 U
4 1 overoid overoid 1234 abcd 2010-08-25 17:02:05 U
5 2 보고픈 <null> 12345 <null> 2010-08-25 17:02:07 D

-- 데이터는 정상적으로 나옵니다.
-- 다만, autoincrement로 생성한 컬럼에 대해서 insert 작업시 실행되는 before trigger에서 NEW.ID 값을
-- 제대로 가져오지 못하고 -1로 입력되는 것을 볼 수 있습니다.

-- 위 문제를 해결하기 위해서 INSERT에 대해서 AFTER 트리거를 생성합니다.
CREATE TRIGGER user_trigger_before_after AFTER INSERT ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(new.id, null, new.name, null, new.password, datetime('now','localtime'),'I');
END;

-- 데이터를 삽입합니다.
INSERT INTO USER(name, password) VALUES('after','1234');

SELECT log_no, id, ifnull(name_before,'<null>') name_before, ifnull(name_after,'<null>') name_after,
       ifnull(password_before,'<null>') password_before, ifnull(password_after,'<null>') password_after, change_date, flag
  FROM user_log;

-- result
log_no id name_before name_after password_before password_after change_date flag
1 -1 <null> overoid <null> 1234 2010-08-25 17:02:00 I
2 -1 <null> test <null> 12345 2010-08-25 17:02:02 I
3 2 test 보고픈  12345 12345 2010-08-25 17:02:04 U
4 1 overoid overoid 1234 abcd 2010-08-25 17:02:05 U
5 2 보고픈 <null> 12345 <null> 2010-08-25 17:02:07 D
6 -1 <null> after <null> 1234 2010-08-25 17:03:26 I
<null> after <null> 1234 2010-08-25 17:03:26 I

-- after trigger로 변경했더니, autoincrement 컬럼에 대해서 값을 제대로 가져오는 군요.

이것으로 SQLite Trigger에 대한 일곱번째 포스트를 마칩니다. 다음번 포스트에서는 SQLite의 Tranaction 및 Lock에 기능에 대해서 살펴보도록 하겠습니다.

 

Trackback 0 Comment 0
2010.08.27 09:23

SQLite User Guide for Android, iPhone - DateTime 함수, DateTime Formatting



SQLite Databae에 대한 여섯번째 포스트입니다. 이번 포스트에서는 SQLite의 DateTime 관련 함수 및 Formatting 에 대해서 다뤄보도록 하겠습니다. 이 부분은 다른 DBMS와 많이 다르고 자주 사용하는 함수들이라 몇가지 표현법은 꼭 익혀야 될 점인것 같습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)


1. DateTime 관련 Keyword

먼저 SQLite에서는 DateTime관련해서 다음과 같은 세가지 키워드가 있습니다.
CURRENT_TIME : 현재 시간 (형식: 03:22:56) 다만, UTC 기준입니다.
CURRENT_DATE: 현재 날짜 (형식: 2010-08-25) UTC 기준.
CURRENT_DATETIME : 현재 날자 및 시간 (형식: 2010-08-25 03:23:37) UTC 기준.

UTC 기준 날짜 및 시간이라서 사용할때는 로컬시간으로 다시 변환작업이 필요할 듯 보입니다. DATETIME 관련 함수들이 있어 크게 유용해 보이지는 않습니다.

2. DateTime 관련 함수.

DateTime 관련 함수는 다음의 5가지가 있습니다.

date(timestring, modifier, modifier, ...)  : 날짜 함수
time(timestring, modifier, modifier, ...)  : 시간 함수
datetime(timestring, modifier, modifier, ...)  :날짜/시간 함수
julianday(timestring, modifier, modifier, ...)  : 율리우스력 함수
strftime(format, timestring, modifier, modifier, ...)  : 날짜 포맷팅 함수


위 다섯개의 함수 인자 중 timestring 파라미터에로 들어갈 수 있는 날짜 형식은 아래와 같습니다.

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

strftime 함수에 사용되는 format에 사용할 수 있는 값들은 다음과 같습니다.

%d    day of month: 00
%f     fractional seconds: SS.SSS
%H    hour: 00-24
%j     day of year: 001-366
%J     Julian day number
%m    month: 01-12
%M    minute: 00-59
%s     seconds since 1970-01-01
%S     seconds: 00-59
%w     day of week 0-6 with sunday==0
%W     week of year: 00-53
%Y     year: 0000-9999
%%    %

함수의 modifer에 들어갈 수 있는 값들은 다음과 같습니다.

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc


잘 안 와 닿는듯 합니다. 아래 샘플 코드를 보면 이해가 가실 겁니다.

--UTC 기준의 현재 날짜/시간
select datetime('now');
2010-08-25 04:01:46

-- 로컬 기준의 현재 날짜/시간
select datetime('now','localtime');
2010-08-25 13:02:30

--현재 로컬 기준 시간에서 10분 3.5초를 더한 시간.
select datetime('now','localtime','+3.5 seconds','+10 minutes');
2010-08-25 13:14:15

--현재 로컬 시간에 3.5초를 더하고 날짜는 돌아오는 화요일 (weekday == 0 이 일요일입니다.)
select datetime('now','localtime','+3.5 seconds','weekday 2');
2010-08-31 13:05:39

--현재 달의 마지막 날짜
SELECT date('now','start of month','+1 month','-1 day','localtime');
2010-08-31

--2004-01-01 02:34:56초부터 현재까지의 총 초
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
209785028

--현재날짜/시간 기준에서 올해 9번째달의 첫번째 화요일
SELECT date('now','start of year','+9 months','weekday 2');
2010-10-05

-- 날짜 포맷 스타일 변경
select strftime("%Y/%m/%d %H:%M:%S",'now','localtime');
2010/08/27 09:17:22




 

Trackback 0 Comment 1
  1. 김승배 2011.03.19 15:18 신고 address edit & del reply

    DateTime 쿼리 관련해서..
    Select date(birthday) From TableA 와 같이 테이블의 DateTime column을 넣어주면 값이 안나오던데요..
    혹시 해결 방법을 아시나요?

2010.08.27 01:24

SQLite User Guide for Android, iPhone - 함수. Function (Core, Aggregation)




SQLite Databae에 대한 다섯번째 포스트입니다. 이번 포스트에서는 SQLite의 Core 함수에 대해서 다뤄보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

1.Core Function

 coalesce(X,Y,...) coalesce() 함수는 Argument 중에서 첫번째로 Not Null인 Argument값을 리턴하는 함수입니다. 만일 모든 인자가 null이면 null을 리턴합니다.
 ifnull(X,Y) ifnull() 함수는 두 인자중에서 첫번째로 Not Null인 인자값을 리턴합니다. 만일, 둘다 null이면 null을 리턴합니다. Ifnull() 함수는 인자가 두개인 coalesce() 함수와 동일합니다.
 length(X) 길이값을 리턴하는 함수입니다. 만일 X 인자가 null이면 null을 리턴합니다.
 like(X,Y)
 like(X,Y,Z)
Like 함수는 “Y LIKE X [ESCAPE Z]”구문과 동일합니다.
 lower(x) 소문자로 변환 합니다.
 upper(X) 대문자로 변환 합니다.
 ltrimX)
 ltrim(X,Y)
ltrim(X)는 X 값 중 왼쪽편의 공백을 제거하는 함수입니다.
ltrim(X,Y)는 X 문자열중에서 Y에 나타난 값을 제일 좌측부터 제거하는 함수입니다.
 
select ltrim(" ZZZZabcZZ ", " aZ")
=> 좌측문자열에서 부터 공백,a,Z 문자열이 있으면 제거하고, 처음으로 공백,a,Z가 아닌 문자열부터 출력합니다. 즉, “bcZZ”가 출력됩니다.
ltrim(" ZZZZabcZZ ", " ") 는 ltrim(" ZZZZabcZZ ")과 동일합니다.
 rtrim(X)
 rtrim(X,Y)
rtrim(X)는 우측편 공백 제거
rtrim(X,Y)는 ltrim(X,Y)와 동일한 방식이지만 우측편부터 매칭되는 글자를 제거합니다. 예를들어 select rtrim(" ZZZZabcZZ ", " Z") 문장은 우측편부터 공백과 Z를 빼고 처음으로 공백과 Z가 아닌 글자, 즉 c 까지 글자가 나타납니다. 결과값: “ ZZZZabc”
 trim(X)
 trim(X,Y)
trim(X)는 양쪽 공백 제거
trim(X,Y) 는 Y에 해당되는 글자를 양쪽 끝에서 부터 제거하고 나머지 글자만 리턴함. 예) trim(" ZZZZabcZZ ", " Z") => “abc” trim은 ltrim과 rtrim을 각각 적용한것과 동일한 결과가 나타납니다.
 max(X,Y,..) 인자값들 중 최대값을 리턴합니다.

create table t1 (coll integer, col2 integer, col3 integer);

insert into t1 values(1,2,3);
insert into t1 values(5,3,1);

select max(col1, col2, col3) from t1;
 min(X,Y,...) 인자값들 중 최소값을 리턴합니다.
 nullif(X,Y) 두 인자가 서로 같으면 null을 리턴, 서로 다르면 X값을 리턴합니다.
nullif('x','y') => ‘x’ , nullif('x','x') => null 리턴
 quote(X) Quote()함수는 single quotation을 escape 해줍니다. ‘값을 ‘’ 로 변경합니다.
Insert나 update 시에 사용하면 유용할 듯 합니다.
select quote("girl's mouse") => 'girl''s mouse'
 random(*) -9223372036854775808 부터 +9223372036854775807 숫자 사이의 임의의 수를 리턴합니다.
 randomblob(N) N으로 지정된 bytes의 랜덤 바이너리 데이터를 생성합니다.
 hex(X) 바이너리 값을 hex 값으로 변경합니다.
select hex(randomblob(16))
 replace(X,Y,Z) X 문자열 중에서 Y문자열을 Z로 변경합니다.
select replace('1/12/2009','1','x') => "x/x2/2009"
 round(X)
 round(X,Y)
반올림 함수. Y는 소수점 자리. Y가 없으면 0으로 처리합니다.
Round(3.5) => 4, round(2.555, 2) => 2.56
 substr(X,Y)
 substr(X,Y,Z)
substr()함수는 X문자열 중에서 Y번째부터 시작해서 Z개수만큼 문자열을 가져오는 함수입니다. Z가 생략되면 Y번째 문자열부터 문자열 끝까지 리턴합니다. Y의 최소값은 1입니다. 자바에서는 0으로 시작하지만 SQLite에서는 1부터 인덱스가 시작합니다. 만일 Y가 –(마이너스)값이면 문자열 우측끝부터 카운팅을 시작합니다.
select substr("string", 1, 3) => str
select substr("string", 0, 3) => st
select substr("string", -1, 3) => g
select substr("string", -3, 3) => ing
select substr("string", 2) => tring
 typeof(X)  X 표현식에 대한 데이터 타입을 리턴합니다. 리턴값은 “null”, “integer”, “real”, “text”, “blob” 중의 하나입니다.

2. Aggregation Function (집합 함수)

avg(X) 그룹내의 Not Null값의 평균값을 리턴합니다. X 컬럼값이 문자열이나 BLOB이면 0으로 간주하고 처리합니다.
count(*)
count(X)
count(X)는 X가 Not Null값을 가진 레코드의 개수 리턴합니다.
count(*)는 그룹내의 모든 rows의 수를 리턴합니다.
group_concat(X)
group_concat(X,Y)
X가 not null인 경우 그룹내의 모든 문자열을 콤마(,)를 구분자로 해서 문자열을 합쳐서 리턴합니다. Y가 주어지면 Y값이 구분자로 처리됩니다.
max(X) 그룹내의 값들중 최대값을 리턴합니다.
min(X) 그룹내의 값들중 최소값을 리턴합니다. 모든값이 Null이면 Null을 리턴합니다.
sum(X)
total(X)
Sum과 total은 그룹내의 Not Null값의 합계를 리턴합니다. X의 모든 값이 Null인 경우 Sum()은 Null을 리턴하고 Total()은 0.0을 리턴합니다.

/* Aggregation Function Test */

create table t1 (
type integer,
id integer,
name varchar(10),
value real,
data text
);

insert into t1 values(1,1,'a',0.0,'1');
insert into t1 values(1,2,'b',3.5,NULL);
insert into t1 values(1,3,NULL,3.1,'2');
insert into t1 values(1,4,'c',3.5,'10');
insert into t1 values(1,5,'d',NULL,NULL);

-- count(*)와 count(X)의 차이 비교
select count(*), count(name), count(data)
from t1
group by type

-- result
count(*) count(name) count(data)
5 4 3

-- group_concat test query
select group_concat(id,' > ') as exp
from t1;

-- result
exp
1 > 2 > 3 > 4 > 5

select group_concat(data,' > ') exp
from t1
group by type

-- result
exp
1 > 2 > 10

/* sum(), tatal() function test */
select sum(data), total(data)
from t1
where id in (2,5)
group by type;

-- result
sum(data) total(data)
<null>    0

생각보다 SQLite에서 지원되는 내장 함수가 너무 적습니다. SQLite는 각 언어별로 확장 기능을 가지고 있긴 하지만, 쉽게 사용하는게 아니라서.. 한번의 SQL로 처리 가능한 것을 상당 부분 프로그램 코드에서 처리해야 하지 않을까 싶습니다. 그럼에도 불구하고 group_concat() 같은 기능의 함수는 Oracle에서도 11g가 되어서야 내장 함수로 제공할 정도로 대부분의 DB에서 제공하지 않는 함수인데.. SQLite에서 구현이 되어 있다는 게 놀라울 따름입니다.

이것으로 SQLite Function 부분을 마치며, 다음 포스트에서는 DateTime 및 DateTime Formatting에 대해서 알아보도록 하겠습니다.

Trackback 2 Comment 1
  1. suroMind 2011.04.06 17:00 신고 address edit & del reply

    좋은 정보 감사합니다. 출처 남기고 퍼가겠습니다.
    http://blog.suromind.com/43

2010.08.26 18:42

SQLite User Guide for Android, iPhone - DML (SELECT/INSERT/UPDATE/DELETE) 등




SQLite Databae에 대한 네번째 포스트입니다. DML은 대부분의 개발자들이 기본 SELECT/INSERT/UPDATE/DELETE에 대해서 워낙 잘 들 알고 계시므로 이번 포스트에서는 DML 관련하여 몇가지만 다루도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

1. EXPLAIN / EXPLAIN QUERY PLAN

실행계획을 볼 수 있는 명령어입니다.
EXPLAIN 명령어는 세부적인 VDBE의 operation code를 볼 수 있습니다.
EXPLAIN QUERY PLAN 은 실행계획을 보여 줍니다.

예제 테스트는 SQLite 첫번째 포스트에서 알려드린 SQLite Expert Personal 버전의 demodb를 이용해서 테스트하도록 하겠습니다.

예제)
EXPLAIN
SELECT K.NAME
      ,C.[CustNo]     
      ,C.[City]     
      ,O.OrderNo     
      ,O.[SaleDate]     
      ,E.[EmpNo]     
      ,i.[ItemNo]     
      ,i.PartNo
  FROM COUNTRY K JOIN CUSTOMER C ON K.[Name] = C.COUNTRY
       JOIN ORDERS O ON C.[CustNo] = O.CustNo
       JOIN ITEMS I ON O.[OrderNo] = I.OrderNo
       JOIN EMPLOYEE E ON O.[EmpNo] = E.EmpNo         
  WHERE K.NAME = 'Canada' 
    AND E.EMPNO = 145;

-- result
addr opcode p1 p2 p3 p4 p5 comment
0 Trace 0 0 0 "" 00 
1 String8 0 1 0 Canada 00 
2 Integer 145 2 0 "" 00 
3 Goto 0 53 0 "" 00 
4 OpenRead 5 5 0 keyinfo(1,BINARY) 00 
5 OpenRead 4 27 0 0 00 
6 OpenRead 1 9 0 8 00 
7 OpenRead 2 18 0 5 00 
8 OpenRead 6 41 0 keyinfo(1,BINARY) 00 
9 OpenRead 3 13 0 3 00 
10 OpenRead 7 39 0 keyinfo(1,BINARY) 00 
11 SeekGe 5 45 1 1 00 
12 IdxGE 5 45 1 1 01 
13 MustBeInt 2 44 0 "" 00 
14 NotExists 4 44 2 "" 00 
-- 중략.

EXPLAIN QUERY PLAN
SELECT K.NAME
      ,C.[CustNo]     
      ,C.[City]     
      ,O.OrderNo     
      ,O.[SaleDate]     
      ,E.[EmpNo]     
      ,i.[ItemNo]     
      ,i.PartNo
  FROM COUNTRY K JOIN CUSTOMER C ON K.[Name] = C.COUNTRY
       JOIN ORDERS O ON C.[CustNo] = O.CustNo
       JOIN ITEMS I ON O.[OrderNo] = I.OrderNo
       JOIN EMPLOYEE E ON O.[EmpNo] = E.EmpNo         
  WHERE K.NAME = 'Canada' 
    AND E.EMPNO = 145;

order from detail
0 0 TABLE COUNTRY AS K WITH INDEX sqlite_autoindex_country_1
1 4 TABLE EMPLOYEE AS E USING PRIMARY KEY
2 1 TABLE CUSTOMER AS C
3 2 TABLE ORDERS AS O WITH INDEX idx_orders_CustNo
4 3 TABLE ITEMS AS I WITH INDEX idx_items_ByOrderNo

VDBE의 OP코드 분석(EXPLAIN 결과) 및 실행계획에 대해서는 향후 포스트에서 상세하게 다루도록 하겠습니다.
맘이 급하십니까? 당장 알기 원하시면 다음의 링크를 참고 하시기 바랍니다.

실행계획/옵티마이저.
http://www.sqlite.org/optoverview.html
http://www.sqlite.org/queryplanner.html

VDBE의 OP코드
http://www.sqlite.org/opcode.html

2. SELECT

SELECT 는 워낙 잘 들 아시는 부분이라  한 두가지만 테스트 해 보도록 하겠습니다.
먼저, 오라클의 dual 테이블 같이 처리되는 구문은 MS-SQL 처럼 FROM 절 없이 사용하면 될 것 같습니다.
예)
SELECT 'A' a

페이징 처리 (ROWNUM, TOP과 유사한 기능)

페이징 처리시 오라클에서는 ROWNUM을 사용하고 MS-SQL에서는 TOP을 주로 사용들 합니다. 물론 요즘은 ROW_NUMBER() 함수를 더 많이 사용하신다구요?
SQLITE에서는 페이징 처리하기가 더 편리합니다. LIMIT와 OFFSET이 그것입니다.

SELECT CUSTNO, COMPANY, CITY
  FROM CUSTOMER 
 ORDER BY CUSTNO
 LIMIT 5 OFFSET 5;

ORDER BY와 상관없이 사용이 가능합니다. LIMIT에 값을 지정하면 화면에 출력할 레코드 개수를 지정할 수 있습니다. OFFSET은 건너뛸 레코드 수를 나타냅니다. 즉, 위의 쿼리는 CUSTNO로 정렬해서 나온 순서에서 6번째부터 5개의 레코드를 가져오는 SELECT문입니다. OFFSET은 생략도 가능합니다.

페이징은 편리하나 오라클의 ROWNUM 처럼 결과에 수치값을 나타낼 방법이 있다면 여러모로 편리한데 그런 기능은 아쉽습니다. 필요시에는 별도의 NUMBER를 가진 테이블을 하나 만들어서 조인해서 사용해야 할 듯 합니다.

USING 구문

일반적으로 EQUAL JOIN 구문에서 ON 이하는 두 테이블간의 조인조건의 컬럼을 기술하는데, 두 컬럼의 컬럼명이 동일한 경우가 많습니다. 이때 간략하게 USING을 사용하여 쿼리를 줄일수 있습니다.

SELECT * FROM A INNER JOIN B ON A.COL1 = B.COL1;

-- 아래와 같이 위 문장을 USING을 사용하여 기술할 수 있음.
SELECT * FROM A INNER JOIN B USING(COL1);

-- 여러 테이블 조인에서의 USING 사용 예)
SELECT * FROM A JOIN B USING (c) JOIN C USING (e) JOIN D USING (g)

3. UPDATE (JOIN)

UPDATE 구문도 워낙 잘 아시니 여기서는 JOIN UPDATE에 대해서만 확인해 보도록 하겠습니다.
테스트를 위해 CUSTOMER 테이블에 컬럼을 2개 추가했습니다.

/* JOIN UPDATE TEST */
-- ALTER TABLE에서 한번여 여러 컬럼 추가도 안됩니다.

ALTER TABLE CUSTOMER
ADD CAPITAL TEXT;

ALTER TABLE CUSTOMER
ADD COLUMN CONTINENT TEXT;

-- 오라클 스타일 JOIN UPDATE TEST => ERROR
UPDATE CUSTOMER C
   SET (CAPITAL, CONTINENT) = (SELECT CAPTIAL, CONTINENT  
                                WHERE COUNTRY K                               
                                  AND K.[Name] = C.COUNTRY)                                 
 WHERE COUNTRY IN (SELECT NAME FROM COUNTRY);

-- MS-SQL 스타일 JOIN UPDATE => ERROR
UPDATE CUSTOMER
   SET CAPITAL = K.CAPITAL,  
       CONTINENT = K.CONTINENT      
FROM CUSTOMER C JOIN COUNTRY K ON C.Country = K.NAME;

찾아보니 JOIN UPDATE 자체를 지원하지 않는답니다. 에구, 이게 안되는 건 좀 치명적인듯. 업데이트시에 어플에서 처리하는 방식밖에 안될 것 같습니다.

4. DELETE

DELETE시에도 LIMIT와 OFFSET 구문을 함께 사용할 수 있습니다. 이 부분은 편리한 듯~ , 참 UPDATE 구문에도 LIMIT와 OFFSET 구문을 함께 사용할 수 있습니다.

DELETE문도 단순 스타일은 워낙 잘 들 아시니 중복 레코드 제거 쿼리 테스트만 해보도록 하겠습니다.


/* 중복 제거 DELETE 문 테스트 */

-- 임시 테이블 생성.
create table t1 (
id integer,
name varchar(10)
);

-- 테스트 데이터 삽입.
insert into t1 values(1,'a');
insert into t1 values(2,'a');
insert into t1 values(2,'b');
insert into t1 values(1,'b');
insert into t1 values(1,'C');

-- 오라클에서 주로 사용하는 방식으로 테스트.
-- ANY 키워드를 지원하지 않아서 에러가 발생함.
DELETE FROM t1 A
WHERE ROWID > ANY (SELECT ROWID
                                     FROM t1 B
                                    WHERE A.id = B.id);

-- DELETE문에서 서브쿼리로 조인을 지원안해서 이것도 에러가 발생함.
DELETE FROM t1 A
WHERE ROWID > (SELECT MIN(ROWID)
                              FROM    t1 B
                             WHERE A.id = B.id);

-- 조금은 부하가 있지만, GROUP BY절을 이용한 NOT IN 서브쿼리로 중복 제거 가능.
DELETE FROM t1
WHERE ROWID NOT IN (SELECT MIN(ROWID)
                      FROM T1                     
                     GROUP BY ID);

5. COMMENT

SQLITE에서는 주석구문으로 블록주석인 /* */ 구문과 단일 행 주석인 -- 을 사용합니다.

6. NULL 처리

NULL값에 대한 처리는 Oracle과 거의 유사하게 동작합니다. SQLITE에서 NULL에 대한 상세한 정보를 얻으실려면 http://www.sqlite.org/nulls.html 를 참조하시기 바랍니다. 샘플코드 따라서 해보시면 금방 이해가 가실듯 합니다.

7. INDEXED BY

실행계획 편에서나 다룰 내용이지만, 여기서 INDEXED BY에 대해서 조금 다루도록 하겠습니다.


테이블명 뒤에 INDEXED BY 구문을 사용하면 지정된 인덱스를 쿼리가 무조건 사용합니다.
지정한 인덱스의 분포도나 이런거 상관없습니다. 무조건 사용합니다. 오라클의 INDEX 힌트와 유사해 보이지만 다릅니다. 오라클의 힌트는 힌트를 잘못 사용하더라도 에러가 발생하지 않지만, SQLITE에서는 SQL 구문이 FAIL 됩니다. 인덱스명을 잘못 사용하는 경우는 물론이고, 인덱스를 타지 않는 상황에서 인덱스를 지정해도 에러가 발생합니다.
아래 쿼리를 보겠습니다.

PRAGMA INDEX_LIST(COUNTRY); /* COUNTRY 테이블의 인덱스 현황입니다. */

seq name unique
0 sqlite_autoindex_country_1 1
--AUTOINCREMENT로 지정된 UNIQUE 인덱스가 하나 존재합니다.

--현재 상황에서 테스트할 쿼리의 실행계획을 보도록 하겠습니다.
EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY
 WHERE NAME LIKE 'c%'
   AND CAPITAL LIKE 'a%'

order from detail
0 0 TABLE COUNTRY
--테이블 FULL SCAN하는 실행계획이 생성되었습니다.

-- 인덱스를 추가하겠습니다.
CREATE INDEX COUNTRY_IX1 ON COUNTRY (NAME);
CREATE INDEX COUNTRY_IX2 ON COUNTRY (CAPITAL);

PRAGMA INDEX_LIST(COUNTRY);

seq name unique
0 COUNTRY_IX2 0
1 COUNTRY_IX1 0
2 sqlite_autoindex_country_1 1
-- 추가된 인덱스가 보입니다.
-- 이전에 실행한 쿼리를 다시 실행해 보도록 하겠습니다.

EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY
 WHERE NAME = 'c'
   AND CAPITAL = 'a';

order from detail
0 0 TABLE COUNTRY WITH INDEX sqlite_autoindex_country_1
-- 인덱스를 이용하지만, AUTOINCREMENT 컬럼에 대한 인덱스를 타고 검색합니다. 아마도 순차적으로 검색하려고 그런가 봅니다.

-- COUNTRY_IX2를 사용하도록 쿼리를 짠후 실행계획을 보면..
EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY INDEXED BY COUNTRY_IX1
 WHERE NAME = 'c'
   AND CAPITAL = 'a'

-- 인덱스를 사용하는 쿼리로 변경이 되었습니다.
order from detail
0 0 TABLE COUNTRY WITH INDEX COUNTRY_IX1

-- 재미있는 상황이 있군요.
EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY INDEXED BY COUNTRY_IX2
 WHERE NAME LIKE 'c%'
   AND CAPITAL LIKE 'a%'

/*
= 검색을 LIKE 검색으로 바꾸니 Cannot use index : COUNTRY_IX2란 에러가 발생했습니다. 일반적으로 오라클이나 MS-SQL은 단방향 LIKE 검색의 경우 인덱스를 태울 수 있습니다. SQLITE에서는 에러가 나는걸 보니 LIKE 검색은 인덱스를 이용하지 않나 봅니다. (이 부분은 향후 실행계획 부분에서 다시 자세히 보도록 하겠습니다.) */

본 포스트에서는 일반적인 DML에 대한 강좌 형식 보다는 SQLite에 대한 특징적인 면들을 살펴보았습니다. 그래서 insert 구문은 아예 다루지도 않았습니다. SQLite에 대한 기본 SQL 문법에 대해 더 궁금하시면 다음 사이트를 살펴보시기 바랍니다. http://www.sqlite.org/lang.html

다음 포스트에서는 SQLite Core Function 사용법에 대해 다뤄 보도록 하겠습니다. 
Trackback 1 Comment 0
2010.08.26 17:17

SQLite User Guide for Android, iPhone - DDL(CREATE, DROP 등)



SQLite User Guide 세번째 포스트입니다.  이번에는 CREATE TABLE, CREATE INDEX 등의 DDL 문에 대해 알아보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

SQLite DDL문은 특별한 게 없어서 대부분 잘 아시리라 생각됩니다. DDL문에 대해 전혀 모르신다구요? 상관없습니다. GUI 툴을 사용하셔서 테이블이나 인덱스를 만드시고 DDL문을 코드에 붙이셔도 됩니다. 본 포스트에서는 모든 DDL문을 다루기 보다는 주요한 몇가지와 SQLite DB에서의 특징적인 면을 살펴보도록 하겠습니다.

1. CREATE TABLE


예제1)
CREATE TABLE TEST2 (
     _ID INTEGER NOT NULL,    
     CLASS TEXT NOT NULL,    
     VALUE TEXT,
     CONSTRAINT TEST2_PK PRIMARY KEY (_ID, CLASS)
);

SQLite에서는 ALTER TABLE 문에서 ADD CONSTRAINT 구문이 지원되지 않기 때문에 PRIMARY KEY, UNIQUE, CHECK등의 TABLE LEVEL의 CONSTRAINT는 위 문장처럼 CREATE TABLE 문 제일 하단에 기술해야 합니다.


예제2)
CREATE TABLE TEST1 (
     _ID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
     TYPE TEXT NOT NULL DEFAULT 'HIGH',
     NAME TEX);

일련번호 채번은 위와 같이 AUTOINCREMENT 구문을 CRATE TABLE 의 해당 컬럼에 기술하면 자동 증가되는 일련번호를 사용할 수 있습니다.
AUTOINCREMENT 문을 가진 DDL이 최초 실행되면 SQLITE는 내부적으로 SQLITE_SEQUENCE 테이블을 생성합니다. 이 테이블은 NAME, SEQ 컬럼을 가진 테이블로 테이블마다 자동 증가되는 최종값을 가집니다. 재미있게도 SQLITE_SEQUENCE 테이블값을 직접 핸들링도 가능합니다.

/* SQLITE_SEQUENCE TEST */

--위에서 만든 TEST1 테이블에 데이터를 2개 넣었습니다.
INSERT INTO TEST1(NAME) VALUES('A');
INSERT INTO TEST1(NAME) VALUES('B');

SELECT * FROM SQLITE_SEQUENCE;

-- 현재 SEQ = 2인 SQLITE_SEQUENCE 테이블을 30으로 업데이트합니다.
UPDATE SQLITE_SEQUENCE
SET SEQ = 30
WHERE NAME = 'TEST1';

-- 다시 레코드를 INSERT한 후 TEST1 데이터를 조회해보니 마지막에 들어간 레코드값은 31로 들어갑니다.
INSERT INTO TEST1(NAME) VALUES('B');

SELECT * FROM TEST1;

_ID TYPE NAME
1 HIGH A
2 HIGH B
31 HIGH B

TEMP TABLE

/* TEMP TABLE CREATION */
CREATE TEMP TABLE COUTRY_TEMP
AS
SELECT *
  FROM COUNTRY     
 WHERE 1 = 2;

CREATE TABLE 구문 사이에 TEMP, TEMPORARY 명령어를 넣으시면 임시 테이블이 생성됩니다. 임시테이블은 Connection이 끊어지면 테이블이 없어집니다. 위 예에서 WHERE 1=2 라고 기술하시면 WHERE 조건이 거짓이 되어 테이블 생성시 COUNTRY 테이블과 동일한 테이블이 만들어지지만 데이터는 들어가지 않습니다.
앞의 PRAGMA 편 포스트에서 시스템 카탈로그를 조회할 수 있는 테이블이 SQLITE_MASTER라고 했는데, 임시테이블을 만들면 이 임시테이블은 SQLITE_MASTER로 조회해도 나타나지 않습니다.
임시테이블은 SQLITE_TEMP_MASTER를 조회하시면 조회가 가능합니다.

COLLATE

추가적으로 COLLATE에 대해서도 알아보도록 하겠습니다. TABLE 정의시 COLLATE를 지정할 수 있습니다. 값은 BINARY, RTRIM, NOCASE 구문이 가능한데, 각각의 케이스에 따라서 값을 비교하는 비교연산자의 결과에 영향을 미칠 수 있습니다. (http://www.sqlite.org/datatype3.html)

COLLATE로 지정가능 한 값.

BINARY – 내부적으로 텍스트 인코딩과 상관없이 memcmp() 함수를 이용하여 텍스트를 비교합니다.
NOCASE – BINARY와 유사하지만 영문자의 경우 대소문자를 동일하게 처리, 즉, 대소문자 구별을 하지 않습니다.
RTRIM – BINARY와 동일하지만 비교하는 문자열의 끝부분에 공백이 있는 경우 이를 제거하고 비교합니다.
디폴트값은 binary 입니다.

COLLATE에 대한 기본 처리 룰은 다음과 같습니다.
  • 이항 비교 연산자(=, <, >, <= 및 >=)의 경우 피연산자 중 하나가 열이면 해당 열의 기본 데이터 정렬 유형에 따라 비교에 사용되는 데이터 정렬 시퀀스가 결정됩니다. 두 피연산자가 모두 열이면 왼쪽 피연산자의 데이터 정렬 유형에 따라 사용되는 데이터 정렬 시퀀스가 결정됩니다. 두 피연산자가 모두 열이 아니면 BINARY 데이터 정렬 시퀀스가 사용됩니다.

  • BETWEEN...AND 연산자는 >= 및 <= 연산자가 있는 두 표현식을 사용하는 것과 같습니다. 예를 들어 x BETWEEN y AND z 표현식은 x >= y AND x <= z와 동일합니다. 따라서 BETWEEN...AND 연산자는 위와 같은 규칙에 따라 데이터 정렬 시퀀스를 결정합니다.

  • IN 연산자는 = 연산자와 같은 방법으로 사용할 데이터 정렬 시퀀스를 결정합니다. 예를 들어 x IN (y, z) 표현식에 사용되는 데이터 정렬 시퀀스는 x가 열인 경우 x의 기본 데이터 정렬 유형입니다. 그렇지 않은 경우에는 BINARY 데이터 정렬이 사용됩니다.

  • SELECT 문에 포함된 ORDER BY 절에는 정렬 작업에 사용할 데이터 정렬 시퀀스를 명시적으로 할당할 수 있습니다. 이렇게 하면 명시적인 데이터 정렬 시퀀스가 항상 사용됩니다. 그렇지 않으면 ORDER BY 절에서 정렬하는 표현식이 열인 경우 해당 열의 기본 데이터 정렬 유형에 따라 정렬 순서가 결정됩니다. 표현식이 열이 아니면 BINARY 데이터 정렬 시퀀스가 사용됩니다.

/* COLLATE TEST */

CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);

                                 /* x   a      b        c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');

/* Text comparison a=b is performed using the BINARY collating sequence. */
/* a, b 컬럼이 모두 BINARY로 정의되어 있으므로 BINARY모드로 비교합니다. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
/* 명시적으로 COLLATE를 지정하면 해당 COLLATE로 비교합니다. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */
/* 서로 컬럼의 COLLATE가 다른 경우 선행 컬럼의 COLLCATE를 사용해서 비교합니다. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
/* 서로 컬럼의 COLLATE가 다른 경우 선행 컬럼의 COLLCATE를 사용해서 비교합니다. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
/* 그룹바이절에 지정된 컬럼의 COLLATE를 사용해서 처리합니다. */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
/* ORDER BY절에 선행컬럼에 지정된 컬럼의 COLLATE를 사용해서 정렬하는군요 */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

코드 출처 : http://www.sqlite.org/datatype3.html

SQLite COLLATE 처리 원칙만 알면 대충 예상은 가능합니다. 그렇다면, 다음의 쿼리는 결과가 어떻게 나올까요?

SELECT d, c FROM t1 GROUP BY d, c ;

SELECT c, d FROM t1 GROUP BY c, d ;

직접 한 번 해보시기 바랍니다. 위 쿼리의 경우 2개를 동시에 비교해서 처리합니다. 테스트 해 본 결과 Sqlite에서는 Group by가 Sort Group by 방식을 사용하는 듯 합니다. (Group By 절에 지정된 순되로 레코드가 정렬되어서 나옵니다.) Group by나 Order시에 같은 Collate이면 rowId 순으로로 처리되는 것 같네요.

에구.. 복잡합니다.
개발할 때는 디폴트 값인  binary 모드로만 DDL을 생성해서 처리하는 게 예측하기 편리하고 좋을 것 같습니다.

ROWID

CREATE TABLE 관련하여 끝으로 ROWID에 대해서 알아보도록 하겠습니다.
SQLite의 모든 Table의 열은 unique한 64bit singed integer key를 내부적으로 가집니다. 이 값을 “ROWID”, “OID“, “_ROWID_”라고 부르며, SELECT 절에 기술하면 데이터 조회도 가능합니다.

만일 테이블 생성시에 컬럼의 정의를 INTEGER PRIMARY KEY라고 기술하면 이 컬럼(INTEGER PRIMARY KEY) 컬럼을 ROWID에 대한 ALIAS로 사용하게 됩니다. 즉, 컬럼과 ROWID가 같아집니다.
다음의 테스트를 통해 확인해 보도록 하겠습니다.

/* ROWID Alias Test */
create table t
(
x INTEGER PRIMARY KEY ASC,
v text
);

insert into t values(1,'a');
insert into t values(2,'a');
insert into t values(5,'a');

select rowid, x, v from t;

x x_1 v
1 1 a
2 2 a
5 5 a


x컬럼을 INTEGER PRIMARY KEY로 정의한 테이블 생성후, 데이터를 몇개 넣고 조회를 해보면 rowid라고 SELECT절에 기술한 컬럼의 명칭은은 x 로 표기되어 있습니다. 두번째 x는 x 이름이 중복되므로 _1을 붙여서 나왔습니다.

2. ALTER TABLE



ALTER TABLE은 컬럼추가 및 테이블명 변경외에는 안됩니다. 이 점은 중요합니다. 우리가 안드로이드 개발시 SQLite를 사용해야 하며, 컬럼 추가 이외에 DB 구조를 변경해야 한다면, upgrade 처리시에 여러가지를 고려해야 할 것입니다.

3. CREATE VIEW

CREATE VIEW에서 TEMP/TEMPORARY를 지정해서 뷰를 임시로 만들 수가 있습니다. 다른 DBMS에서는 못 본 것 같은데… 이런 기능도 있군요.

4. ANALYZE



ANALYZE는 테이블의 통계정보를 수집하는 명령어입니다. 우리가 사용하는 DB의 데이터가 많거나 속도가 안 나온다면 명령어를 실행해 보시기 바랍니다. 뒤에 파라미터에 값을 주지 않고 “ANALYZE” 명령어만 실행하면 전체 테이블, 인덱스에 대해서 통계정보가 생성됩니다. 생성된 통계정보는 SQLITE_STAT1 이란 테이블이 생성되면서 이곳에 저장됩니다. 문제는 ANALYZE 명령어가 데이터베이스 컨텐츠 변경시에 자동으로 실행되지는 않습니다.


ANALYZE
SELECT * FROM sqlite_stat1;

5. REINDEX



인덱스 리빌드 하는 명령어입니다. 단편화가 많이 된 인덱스를 다시 생성해 줍니다

6. VACUUM

VACUUM 명령어는 데이터베이스를 완전히 cleanzing 하는 명령어입니다. 데이터베이스 Object를 Drop하더라도 재사용을 위해 빈 공간은 남겨집니다. 이런 빈공간 및 DML 작업으로 인한 단편화를 제거하기 위해서 VACUUM 명령어를 실행할 수 있습니다. 실행하면 파일 사이즈도 줄여줍니다.

VACUUM 명령어를 실행하면 SQLITE는 전체 OBJECT를 TEMP 공간에 복사한 후 다시 복사하여 전부 새롭게 생성합니다. 이때 ROWID는 변경될 수도 있습니다.

본 포스트에서 간략하게나마 필요한 DDL문은 대충 다 다룬 것 같습니다. 일부 DDL과 뒤에 포스트에서 별도로 다룰  TRIGGER, TRANACTION 관련 DDL은 다루지 않았습니다.
DDL에 관한 전체 명령어 리스트 및 내용은 http://www.sqlite.org/lang.html  문서를 참고하시기 바랍니다.

Trackback 1 Comment 1
  1. 제눅스 2010.10.14 21:49 신고 address edit & del reply

    좋은 글 감사합니다.

2010.08.26 14:54

SQLite User Guide for Android, iPhone - PRAGMA, Data Type 등.




SQLite User Guide 두번째 포스트입니다.
SQLite Document를 보다 보니 PRAGMA란 지시어(확장 명령어)가 나옵니다. 오라클에서도 PL/SQL 개발할 때 자율 트랜젝션이나 에러 정의 할 때 등 몇몇 부분에 PRAGMA 지시어를 사용할 수 있긴 합니다만 SQLite만의 독특한 기능들이라서 별도로 포스트를 구성했습니다.

전체 PRAGMA 지시어 (http://www.sqlite.org/pragma.html ) 중에서 자주 사용할 법한 몇가지만 살펴보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

이전 포스트에도 밝혔듯이 테스트는 SQLite Expert Personal 제품에 들어있는 demodb를 사용하도록 하겠습니다.

PRAGMA 명령어

PRAGMA case_sensitive_like = boolean;

SQLite는 기본적으로 like 검색시에 대소문자를 무시합니다. ‘A’ LIKE ‘a’ 가 true가 됩니다. PRAGMA 에서 이 설정을 바꿀수 있습니다.

PRAGMA case_sensitive_like = true;

/* Default 설정에서 대소문자 처리에 대한 Equal(=) SELECT 테스트 */
SELECT *
  FROM COUNTRY   
 WHERE NAME = 'Brazil';

-- Result
Name Capital Continent Area Population
Brazil Brasilia South America 8511196 150400000

SELECT *
  FROM COUNTRY   
 WHERE NAME = 'brazil';

결과없음.

위 PRAGMA 설정은 LIKE 검색에 한해서만 적용되는 듯합니다. WHERE조건에 equal 검색은 대소문자를 정확히 구분합니다. (Collate 설정에 따라서 바꿀 수 있습니다. 뒤쪽에 나옴)

/* Default 설정 - Like 검색시 대소문자 구별 안함 에서 LIKE 검색 테스트*/

SELECT *
  FROM COUNTRY   
 WHERE NAME LIKE '%brazil%';

-- Result
Name Capital Continent Area Population
Brazil Brasilia South America 8511196 150400000

/* Default 설정 - Like 검색시 대소문자 구별 안함 에서 %없이 LIKE 검색 테스트*/
SELECT *
  FROM COUNTRY   
 WHERE NAME LIKE 'brazil';

-- Result
Name Capital Continent Area Population
Brazil Brasilia South America 8511196 150400000

이번에는 LIKE 검색에  % 연산자를 빼고 검색했습니다.(두번째 쿼리문) 오라클의 경우 이런식의 쿼리가 날아가면 Optimzer가 query를 like 검색을 =로 변경해 버립니다. 하지만, SQLite의 경우 그렇지 않고 like 검색 되는 것 같습니다. (equal과 거의 유사하지만 대소문자만 가리지 않는 방식).

명령어로 대소문자 비교를 하도록 설정을 변경 한 후 테스트 해보도록 하겠습니다.


PRAGMA case_sensitive_like = 'TRUE'

SELECT *
  FROM COUNTRY   
 WHERE NAME LIKE '%brazil%';

-- Result 없음.

역시나 설명되로 PRAGMA 명령어로 설정을 변경했더니 대소문자를 따져서 LIKE 검색을 하는군요.

PRAGMA count_changes;
PRAGMA count_changes
= boolean;

count_changes 값이 설정되면 INSERT, UPDATE, DELETE 시에 변경된 레코드 수(Integer)값을 받으며, false로 설정되어 있으면 받지 않는다고 합니다. GUI 툴에서는 툴 자체가 메시지를 표시하기 때문에 제대로 테스트가 안되더군요. 나중에 틈날 때 코드레벨에서 테스트를 해봐야 겠습니다.

PRAGMA encoding;
PRAGMA encoding = "UTF-8";
PRAGMA encoding = "UTF-16";
PRAGMA encoding = "UTF-16le";
PRAGMA encoding = "UTF-16be";

데이터베이스의 인코딩을 바꿀수 있는 옵션입니다. 거의 쓸 일은 없을 것 같습니다.

PRAGMA table_info(table-name);
테이블 정보를 조회하는 명령어입니다.

PRAGMA table_info(COUNTRY);

cid name type notnull dflt_value pk
0 Name CHAR(24) 0  1
1 Capital CHAR(24) 0  0
2 Continent CHAR(24) 0  0
3 Area FLOAT 0  0
4 Population FLOAT 0  0


PRAGMA index_list(table-name); 인덱스 리스트를 볼 수 있습니다.
PRAGMA index_info(index-name); 인덱스 정보를 조회할 수 있습니다.
PRAGMA foreign_key_list(table-name); fk 리스틀 볼 수 있습니다.

시스템 카탈로그를 조회할 수 있는 방법은 위 PRAGMA 명령 말고도 sqlite_master 테이블을 직접 조회해도 됩니다. SQLITE_MASTER 테이블은 READ-ONLY 테이블입니다.

SELECT * FROM SQLITE_MASTER;

SQLITE_MASTER 테이블을 조회하시면 테이블, 인덱스, 트리거등 모든 정보와 DDL문까지 알 수가 있습니다.

DATATYPE

Sqlite가 지원하는 데이터 타입은 다음과 같습니다.

1. Null
2. Integer – 부호있는 정수, 실제 값에 따라 1byte에서 8byte까지 가변적으로 저장됨.
3. Real – 실수
4. Text – 문자열
5. BLOB – blob 데이터

실제적으로 저희가 테이블 생성시 DDL상에 VARCHAR(10)이라고 컬럼 사이즈를 정의해도 SQLITE는 TEXT 타입으로 만들어집니다. 그렇기 때문에 10자 이상의 데이터도 삽입이 가능합니다. 그러니, DDL 문 만들 때 구지 다른 데이터 타입을 외울 필요없이 위 타입만 알고 있으면 될 것 같습니다.

재미있는 것은 Data and Time 즉, 날짜 관련 데이터 타입이 따로 없다는 것입니다. DATETIME은 입력되는 값에 따라서 TEXT, REAL, INTEGER 타입으로 저장됩니다. http://www.sqlite.org/datatype3.html 보시면 어떤 데이터 타입이 어떤 식으로 변경되는지 알수가 있습니다.

이것으로 SQLite User Guide 두번째 포스트를 마치겠습니다. 다음 포스트에서는 SQLite 사용에 꼭 필요한 DDL문(CREATE TABLE, CRATE INDEX 등)에 대해서 알아보도록 하겠습니다.

Trackback 0 Comment 0
2010.08.26 13:54

SQLite User Guide for Android, iPhone - 소개, GUI Tools 등




SQLite는 Android 및 iPhone에 탑재되면서 많이들 사용하고 있는 파일베이스 RDB입니다. 공부를 하는데, 생각보다 많은 자료가 없더군요. 공부하면서 정리한 자료인데.. 많은 도움 되시면 좋겠습니다.

앞으로 제가 포스트할 User Guide의 목차는 다음과 같습니다.

SQLite User Guide 포스트 목차
1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

SQLite의 특징.

Sqlite의 특징을 먼저 살펴보도록 하겠습니다.
1. Zero Configuration – sqlite는 별도의 설치 및 설정이 필요 없습니다. 파일 하나만 있으면 모든걸 할 수 있습니다.

2. Portabiliy – 이식성. 거의 모든 OS에서 다 돌아갑니다. 최대 2 terabytes까지 지원하며, 기본 encoding으로 UTF-8과 UTF-16을 지원합니다.

3. Compactness – 아주 가볍고 작습니다. Header file, library, 관계형 데이터를 다 포함해도 사이즈가 얼마 안됩니다.

4. Simplicity – sqlite는 아주 단순하고 사용하기 편리한 programming library를 제공하고 있으면서도 다양한 언어로 확장이 가능하도록 만들어졌습니다.

그밖에 Flexibility , Liberal Licensing(공짜죠), Reliability, Convenience 등이 나와 있습니다.
에구 좋은 말은 다 있는 것 같습니다.

위 내용은 일반적인 내용인것 같고, 보다 중요한 몇 가지를 짚고 넘어가겠습니다.

SQLite는 ANSI92의 기능을 대부분 지원하지만, 아래의 사항은 지원하지 않습니다.

1. RIGHT and FULL OUTER JOIN : LEFT OUTER JOIN만 지원합니다.

2. Complete ALTER TABLE Support : RENAME TABLE과 ADD COLUMN만 지원합니다.
DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT 등 다른 기능은 지원하지 않습니다.

3. Writing to VIEWs : SQLite에서 View는 read-only입니다.

4, GRANT and REVOKE : Sqlite에서 읽기/쓰기 권한은 OS 파일 시스템 권한을 사용합니다. 
별도로 권한 부여 기능이 없습니다.


SQLite 아키텍처


SQLite의 내부 아키텍처는 위 그림과 같습니다.  코어, SQL 컴파일러, 백앤드가 주요 핵심요소이고, 이외에 액세서리 역할을 하는 유틸리티와 테스트 코드로 이뤄져 있습니다.

GUI Tools for SQLite

SQLite를 지원하는 GUI Tools 리스트는 아래 링크에 나와있습니다.
http://sb2.info/commercial-and-freeware-sqlite-tools-list-2/

무지 많습니다. 그 중 몇개를 골라서 사용해 봤는데.. 제가 추천해 드리고 싶은 툴은 다음과 같습니다.

SQLite Expert (http://www.sqliteexpert.com/)


  
여러 제품 중에서 가장 무난하게 사용할 수 있는 제품인 것 같습니다. 필요한 기능은 다 있고, UI도 그럭저럭 쓸만합니다. Personal Edition과 Professional Edition으로 나누어지며, Personal Edition은 무료입니다.
(향후 SQLite관련 포스트에서 저는 이 제품으로 테스트 하겠습니다.) Pro Edition의 Crack도 그리 어렵지 않게 구할 수 있습니다.

SQLite Maestro (http://www.sqlmaestro.com/products/sqlite/maestro/)


아마도 제가 본 Sqlite 관련 툴 중에서 가장 강력한 제품인 것 같습니다. 다른 기능은 몰라도 Reverse로 ERD를 만들어 주는 기능은 이 제품만이 가능합니다.

여담이지만 제가 Touch Call(터치콜) App 개발할 당시 Android SDK2.0 기준의 전화번호부 DB를 이용해야 하는데, 구글의 document만으로는 이해하기가 상당히 어려웠습니다. 출판되어 있는 책들도 다 이전 데이터베이스를 기준으로 작성된 책들이고.. 그때 이 제품을 평가판으로 설치해서 애뮬레이터에 있는 contact2 데이터베이스를 가지고 ERD를 만들어 보니… 훨씬 빠르게 이해를 할 수 있었습니다. 

좋은 제품이긴 하지만 애석하게도 free 버전은 없습니다. 모두 상용버전 밖에 없으며, 한달 Trial 버전만 사용할 수 있습니다. 현재 버전의 crack은 거의 구하기 힘들며, 이전 버전은 구하실 수는 있을 겁니다.

DeZign (http://www.datanamic.com/dezign/index.html)
현재 SQLite를 Forward/Backward Engineering를 완벽하게 지원하는 모델링 툴은 datanamic의 dezign이란 모델링 툴 밖에 없습니다. 근데, 막상 평가판 설치해보니..저희 정서와 맞지 않는 툴이더군요. 저희는 한글로 논리모델을 만들고 영문으로 물리모델을 만드는데, 이 툴은 그런 개념없이 논리/물리가 함께 처리되는 툴이라 바로 지워 버렸습니다. 조금 불편하더라도 DA#이나 ERWin으로 모델링을 하고, Forward Generation한 스크립트를 수정해서 사용하는 게 더 편리할 듯 합니다.

끝으로 앞으로 저의 SQLite 관련 포스트에서 사용할 demo db에 대해서 소개를 하겠습니다. 위에서 얘기했듯이 저는 SQLlite Expert Personal 버전을 이용해서 테스트를 하도록 하겠습니다.

SQLite Expert Personal를 실행한 후 File > Open Demo Database를 실행합니다.
Dbdemos란 데이터베이스가 스키마브라우저에 나타납니다.


좌측 스키마 브라우저에 많은 테이블 리스트가 나옵니다. 저는 그중에서 아래 ERD에 나오는 몇 개의 테이블을 주로 사용해서 DML 테스트 등을 진행하도록 하겠습니다.


고객, 직원, 주문, 주문내역 등 간단한 ERD 구조입니다.
참고로 이 ERD는 Dbdemos SQLlite 데이터베이스를 SQLite Maestro를 이용하여 일부 테이블만 designer에 띄운 모습입니다.)

SQLIte User Guide 첫번째 포스트를 이만 마치며, 다음 포스트에서는 SQLite 의 PRAGMA 명령어 및 시스템 카탈로그 등에 대해서 알아보도록 하겠습니다.

 

Trackback 2 Comment 5
  1. BEOM 2010.09.09 11:40 신고 address edit & del reply

    좋은 정보 감사합니다~

  2. 고냉지채소 2010.11.10 17:52 신고 address edit & del reply

    좋은 글 감사합니다. 덕분에.. SQLite에 대해서 궁금했던 부분들 많이 해소 했습니다.
    복 받으실꺼에요..^^

  3. 안승진 2011.03.23 11:43 신고 address edit & del reply

    정말. 잘 배우고 갑니다.^^ 이런 글이 있다니!! 이해도 쉽고, 읽기도 편하고!! 너무 좋아요!

  4. 김병훈 2011.11.22 00:16 신고 address edit & del reply

    감사드립니다

  5. Miyo 2012.01.13 11:50 신고 address edit & del reply

    좋은정보 감사합니다~~