Sqlite 프로그래밍(C/C++/Java)

이제는 모바일 개발 시에 필수 데이터베이스가 된 #sqlite는 보면 볼 수록 괜찮은 데이터베이스 인 것 같다. 여러가지 오픈소스 데이터베이스를 사용해서 프로그래밍 해보곤 했는 데, 구 중에서 간편하게 사용 할 수 있으며 포터블 한 데이터베이스 중에서는 단연 이 sqlite을 나는 꼽고 싶다.

이 데이터베이스를 사용해서 프로그래밍 한 결과가 그리 나쁘지 않으므로, sqlite 프로그래밍 하면서 진행했던 내용을 적어보려고 한다.

 

Sqlite 데이터베이스 오픈

데이터베이스 오픈은 다음과 같이 #sqlite3_open_v2 함수를 사용하여 진행 하였다.

rc = sqlite3_open_v2(sqlitepath, &pSqliteDB, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE, NULL);

 

 

 

용량이 큰 데이터베이스의 경우에는 인덱스를 생성 하는 경우 다음과 같이 메모리 모드를 진행 하는 경우 오류가 발생 하였으므로, 아래 모드는 인덱스를 생성 하거나 할 때는 피하는 것이 좋다는 결론이 났다.

sqlite3_exec(pSqliteDB, "PRAGMA encoding=\"UTF-8\"", NULL, NULL, &errmsg);
sqlite3_exec(pSqliteDB, "PRAGMA synchronous=OFF", NULL, NULL, &errmsg);
sqlite3_exec(pSqliteDB, "PRAGMA count_changes=OFF", NULL, NULL, &errmsg);
sqlite3_exec(pSqliteDB, "PRAGMA journal_mode=MEMORY", NULL, NULL, &errmsg);
sqlite3_exec(pSqliteDB, "PRAGMA temp_store=MEMORY", NULL, NULL, &errmsg);

 

따라서 인덱스를 생성하는 경우가 아닐 경우 디폴트는 위의 프라그마 키워드를 사용해서 데이터베이스를 읽기 쓰기를 수행 한 결과 꽤 괜찮을 성능을 보임을 알 수 있었다. 특히나 select와 insert의 경우에는 성능의 차이가 확연히 나타 났다.

 

조건이 있는 경우에는 인덱스를 걸어야 한다.

 

데이터베이스를 오픈 하는 경우의 함수인 openSqliteDB 코드는 다음과 같이 만들어 보았다.

bool openSqliteDB(connstrct *constr, bool isMemMode=true)
{
	int rc;
	char err[512];
	char *errmsg;
	char sqlitepath[1024];

	memset(sqlitepath, 0x00, sizeof(sqlitepath));
	memcpy(sqlitepath, constr->sqliteOpts.path, strlen(constr->sqliteOpts.path));
	strcat(sqlitepath, "\\");
	strcat(sqlitepath, constr->sqliteOpts.db_nm);


	LOG_TRACE(LOG_INFO, "[%s] open sqlite database: %s MODE[%s]\n\n", __FUNCTION__, sqlitepath, isMemMode ? "MemMode":"NonMemMode");
	rc = sqlite3_open_v2(sqlitepath, &pSqliteDB, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE, NULL);
	errmsg = err;

	if (rc != SQLITE_OK) {
		LOG_TRACE(LOG_ERROR, "Cannot open database: %s\n", sqlite3_errmsg(pSqliteDB));
		closeSqliteDB();

		return false;
	}


	if(isMemMode)
	{
		sqlite3_exec(pSqliteDB, "PRAGMA encoding=\"UTF-8\"", NULL, NULL, &errmsg);
		sqlite3_exec(pSqliteDB, "PRAGMA synchronous=OFF", NULL, NULL, &errmsg);
		sqlite3_exec(pSqliteDB, "PRAGMA count_changes=OFF", NULL, NULL, &errmsg);
		sqlite3_exec(pSqliteDB, "PRAGMA journal_mode=MEMORY", NULL, NULL, &errmsg);
		sqlite3_exec(pSqliteDB, "PRAGMA temp_store=MEMORY", NULL, NULL, &errmsg);
		//pragma journal_mode = WAL;
	}

	return true;
}

 

여기서 connstrct 구조체에서 데이터베이스이 위치와 이름을 읽어 들이도록 하였는 데, 이 구조체는 각자 알맞은 형태의 구조체를 만들어서 사용하면 좋을 것으로 보인다.

isMemMode 는 인덱스를 생성 할 경우에는 false로 두어 진행하되 기본은 true로 해 두었다.

 

Sqlite 데이터베이스 닫기

데이터베이스를 닫는 것은 다음과 같이 #sqlite3_close 함수를 사용해서 진행 한다.

bool closeSqliteDB()
{
	int rc;

	if(pSqliteDB)
	{
		rc = sqlite3_close(pSqliteDB);

		if (rc != SQLITE_OK) {
			LOG_TRACE(LOG_ERROR, "[%s]Cannot close database: %s\n", __FUNCTION__, errorMessage(pSqliteDB));

			return false;
		}
	}
	pSqliteDB=NULL;

	return true;
}

 

전역으로 선언 된 pSqliteDB 변수는 sqlite3의 포인터 변수이며, 인스턴스를 얻을 수 있도록 다음과 같이 따로 분리 하였다.

private:

sqlite3 *pSqliteDB;


public:

sqlite3* ptrSqliteDB() { return pSqliteDB;}

 

사용예: 기본 골격은 파일 경로에 데이터 베이스 파일을 생성하고, pragma 처리를 하여 둔다(메모리 기반).

그런 다음 데이터베이스를 open 하고 로직을 처리하고, close 하는 순으로 진행 하면 된다.

int rc; //반환값 선언
sqlite3 *pSqliteDB; //인스턴스 선언
sqlite3_stmt *pStmt; //Statement 선언
… 중략

if(!(pSqliteDB = FileDBMgr->OpenSqliteDB(pConnOpts)))
{
	LOG_TRACE(LOG_ERROR, "\n1. [%s]Can not open a FileDB[%s]\n", __FUNCTION__, FileDBMgr->errorMessage(pSqliteDB));

	return false;
}

..
rc = sqlite3_prepare_v2(pSqliteDB, “sql 문장처리”, -1, &pStmt, 0);
LOG_TRACE(LOG_INFO, "[%s] SQL: %s \n %d 바인딩 변수를 가짐\n", __FUNCTION__, “sql 문장처리”, sqlite3_bind_parameter_count(pStmt));

if (rc != SQLITE_OK ) {
	//.. 오류 이면 오류 처리
}


//트랜잭션을 시작한다.
sqlite3_exec(pSqliteDB, "BEGIN TRANSACTION", NULL, NULL, &err_msg);


for(;;)
{
	//질의를 수행한다.
	rcTsrm = sqlite3_step(pTsrmStmt);

	//질의가 종료 되었으면 끝냄
	if (rcTsrm == SQLITE_DONE) break;

	//오류가 발생 한 경우도 종료 함.
	if (rcTsrm != SQLITE_ROW) {
		break;
	}

	//해당 row가 반환 되었으면 로직을 진행 한다.
	if (SQLITE_ROW == rc)
	{
		//.....
	}
}

//트랜잭션을 끝내고, 커밋한다.
sqlite3_exec(pSqliteDB, "END TRANSACTION", NULL, NULL, &err_msg);
sqlite3_exec(pSqliteDB, "COMMIT TRANSACTION", NULL, NULL, &err_msg);

 

#자바에서 Sqlite의 처리

 

자바에서 Sqlite의 처리는 좀 더 단순해 보이는 데, 우선 자바용으로 만들어진 jdbc 브릿지 라이브러리인 #sqlite-jdbc 를 다운로드 하면 된다.

 

https://bitbucket.org/xerial/sqlite-jdbc

https://bitbucket.org/xerial/sqlite-jdbc

 

네이티브 라이브러리 위치

당연히 c로 구현된 sqlite 라이브러리 들은 어딘가 들어 있을 테고, 윈도우용 항상 제공 할 것이니 걱정 할 필요 는 없을 듯....

 

안드로이드 용은 예전에 한 번 만들어 본 내용을 기억해 보면 될 듯 하다.

 

커스텀 Sqlite 설치하고 개발하기

 

JDBC 드라이버 초기화

위의 사이트에서 구한 라이브러리를 사용 해서 드라이버를 다음과 같이 초기화 한다.

public boolean initJDBC()
{
	// load the sqlite-JDBC driver using the current class loader
	try {
		lass.forName("org.sqlite.JDBC");
	} catch (ClassNotFoundException e) {
		LOG.error("{}", e);
		return false;
	}

	return true;
}

 

테이블 생성하기 - 예제

연결 맺고, Statement 생성하고 실행 하면 된다.

public void createTsrmTable(String dbfileName)
{
	String sqlScript = DDL_CREATE_QUERY;

	Connection connection = null;
	Statement statement = null;

	try {
		// create a database connection
		connection = DriverManager.getConnection("jdbc:sqlite:" + dbfileName);
		statement = connection.createStatement();
		statement.setQueryTimeout(30); // set timeout to 30 sec.

		statement.executeUpdate("drop table if exists "+ ATCH_INFO.TAG);
		statement.executeUpdate(sqlScript);

		sqlScript = DDL_CREATE_QUERY;

		statement.executeUpdate("drop table if exists "+ ATCH_INFO.TAG);
		statement.executeUpdate(sqlScript);

	} catch (SQLException e) {
		// if the error message is "out of memory",
		// it probably means no database file is found
		LOG.error("{}", e);
	} finally {
		try {
			if(statement != null && !statement.isClosed()) statement.close();

			if (connection != null)
				connection.close();

		} catch (SQLException e) {
			LOG.error("{}", e);
		}
	}
}

 

PreparedStatement 사용하기 - 예제

간단 한 구문 이외에는 사실 이렇게 컴파일 하여 사용 하는 것이 좋다는 권고 사항이 있다.

private int conv(final Connection connection) throws SQLException
{
	PreparedStatement Pstatement = null;
	Statement perfStmt = null;

	int uCount = 0;
	int processCount = PROCESS_COUNT;

	String atch_key = null;
	String typ = null;

	ResultSet rs = null;

	try {
		perfStmt = connection.createStatement();
		perfStmt.execute("PRAGMA synchronous=OFF");
		perfStmt.execute("PRAGMA count_changes=OFF");
		perfStmt.execute("PRAGMA journal_mode=MEMORY");
		perfStmt.execute("PRAGMA temp_store=MEMORY");

		Pstatement = connection.prepareStatement(ATCH_INFO.INSERT_QUERY);
		Pstatement.setQueryTimeout(30); // set timeout to 30 sec.
		psmt = anotherconn.prepareStatement(ATCH_INFO.SELECT_QUERY);
		rs = psmt.executeQuery();

		while(rs.next())
		{
			atch_key = rs.getString(TB_SPLY_APLY_ATCH_INFO.iCOLS_FLE.atch_key);
			typ = rs.getString(TB_SPLY_APLY_ATCH_INFO.iCOLS_FLE.typ);

			Pstatement.setString(ATCH_INFO.iCOLS_FLE.atch_key, atch_key);
			Pstatement.setString(ATCH_INFO.iCOLS_FLE.typ, typ);

			uCount += tsmsPstatement.executeUpdate();

			if(processCount == 0)
			{
				processCount = PROCESS_COUNT;
				System.out.print(".");
			}

			processCount--;
		}

	} catch (SQLException e) {
		// if the error message is "out of memory",
		// it probably means no database file is found
		LOG.error("{}", e);
	} finally {
		if(rs != null && !rs.isClosed()) rs.close();
		if(psmt != null && ! psmt.isClosed())psmt.close();

		if(perfStmt != null && !perfStmt.isClosed()) perfStmt.close();
		if(Pstatement != null && !Pstatement.isClosed()) Pstatement.close();
	}

	return uCount;
}

 

중간에 C/C++ 코드에서 한 것 처검 pragma 선언을 다음과 같이 코드 상에 넣을 수 있다.

perfStmt = connection.createStatement();                     
perfStmt.execute("PRAGMA synchronous=OFF");                     
perfStmt.execute("PRAGMA count_changes=OFF");                     
perfStmt.execute("PRAGMA journal_mode=MEMORY");                     
perfStmt.execute("PRAGMA temp_store=MEMORY");

 

전체적인 맥락 만 이해하고 나머지는 API를 활용 하는 것이 최선 일 듯 하다.

 

C/C++ Interface For SQLite Version 3

 

 

 

이상.