마음의 안정을 찾기 위하여 - Sqlite3에서의 데이터 백업
1846554
72
512
관리자새글쓰기
태그위치로그방명록
별일없다의 생각
dawnsea's me2day/2010
색상(RGB)코드 추출기(Color...
Connection Generator/2010
최승호PD, '4대강 거짓말 검...
Green Monkey**/2010
Syng의 생각
syng's me2DAY/2010
천재 작곡가 윤일상이 기획,...
엘븐킹's Digital Factory/2010
Sqlite3에서의 데이터 백업
Database/Sqlite | 2010/05/14 17:44
운영중인 머드의 데이터를 웹과 공유하여 사용하고 있는데, 웹서버와 머드 구동서버가 분리되어 있는지라, 데이터를 웹서버에 Mysql Database에 저장하여 사용하고 있다.

헌데, 가끔 머드상에서 웹서버 데이터베이스에 저장해놓은 데이터들을 가져와야할 경우가 간혹 생긴다. (단순 Select)

데이터가 정확히 일치해야할 필요성은 없지만, 그래도 대략적으로 정보가 필요한지라... 이걸 매번 웹서버내의 데이터베이스에서 가져오려니 속도감이 좀 느껴지는지라, 크론탭에 등록하여 Mysql 데이터를 Sqlite3로 변환시켜 로컬에 저장해두도록 하였다.

헌데 Mysql에서 Select한 데이터를 한컬럼씩 가져올때마다 Sqlite3에 인서트 하려니 속도가 많이 느려서 우선은 Sqlite3 메모리 디비에 내용을 저장한다음 이 메모리 디비를 로컬(머드 구동서버)에 저장하는 형태로 작업을 진행하였다.

현재로서는 결과에 만족...... 작업하면서 참고한 자료는 아래와 같음

출처 : http://www.sqlite.org/backup.html

◆ 메모리 디비를 파일 디비로 저장하는 방법

/*
** This function is used to load the contents of a database file on disk 
** into the "main" database of open database connection pInMemory, or
** to save the current contents of the database opened by pInMemory into
** a database file on disk. pInMemory is probably an in-memory database, 
** but this function will also work fine if it is not.
**
** Parameter zFilename points to a nul-terminated string containing the
** name of the database file on disk to load from or save to. If parameter
** isSave is non-zero, then the contents of the file zFilename are 
** overwritten with the contents of the database opened by pInMemory. If
** parameter isSave is zero, then the contents of the database opened by
** pInMemory are replaced by data loaded from the file zFilename.
**
** If the operation is successful, SQLITE_OK is returned. Otherwise, if
** an error occurs, an SQLite error code is returned.
*/
int loadOrSaveDb(sqlite3 *pInMemory, const char *zFilename, int isSave){
  int rc;                   /* Function return code */
  sqlite3 *pFile;           /* Database connection opened on zFilename */
  sqlite3_backup *pBackup;  /* Backup object used to copy data */
  sqlite3 *pTo;             /* Database to copy to (pFile or pInMemory) */
  sqlite3 *pFrom;           /* Database to copy from (pFile or pInMemory) */

  /* Open the database file identified by zFilename. Exit early if this fails
  ** for any reason. */
  rc = sqlite3_open(zFilename, &pFile);
  if( rc==SQLITE_OK ){

    /* If this is a 'load' operation (isSave==0), then data is copied
    ** from the database file just opened to database pInMemory. 
    ** Otherwise, if this is a 'save' operation (isSave==1), then data
    ** is copied from pInMemory to pFile.  Set the variables pFrom and
    ** pTo accordingly. */
    pFrom = (isSave ? pInMemory : pFile);
    pTo   = (isSave ? pFile     : pInMemory);

    /* Set up the backup procedure to copy from the "main" database of 
    ** connection pFile to the main database of connection pInMemory.
    ** If something goes wrong, pBackup will be set to NULL and an error
    ** code and  message left in connection pTo.
    **
    ** If the backup object is successfully created, call backup_step()
    ** to copy data from pFile to pInMemory. Then call backup_finish()
    ** to release resources associated with the pBackup object.  If an
    ** error occurred, then  an error code and message will be left in
    ** connection pTo. If no error occurred, then the error code belonging
    ** to pTo is set to SQLITE_OK.
    */
    pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
    if( pBackup ){
      (void)sqlite3_backup_step(pBackup, -1);
      (void)sqlite3_backup_finish(pBackup);
    }
    rc = sqlite3_errcode(pTo);
  }

  /* Close the database connection opened on database file zFilename
  ** and return the result of this function. */
  (void)sqlite3_close(pFile);
  return rc;
}


위나 아래나 뭐 별반 다를바 없는거라 생각한다.... Source Database를 어떤걸 지정해주느냐의 차이일테니
반대로 적용하면 로컬데이터베이스를 메모리 디비로 읽어들이는 것도 쉽게 될듯.

◆ 지정 디비와 동일한 디비를 생성(백업)하는 방법

/*
** Perform an online backup of database pDb to the database file named
** by zFilename. This function copies 5 database pages from pDb to
** zFilename, then unlocks pDb and sleeps for 250 ms, then repeats the
** process until the entire database is backed up.
** 
** The third argument passed to this function must be a pointer to a progress
** function. After each set of 5 pages is backed up, the progress function
** is invoked with two integer parameters: the number of pages left to
** copy, and the total number of pages in the source file. This information
** may be used, for example, to update a GUI progress bar.
**
** While this function is running, another thread may use the database pDb, or
** another process may access the underlying database file via a separate 
** connection.
**
** If the backup process is successfully completed, SQLITE_OK is returned.
** Otherwise, if an error occurs, an SQLite error code is returned.
*/
int backupDb(
  sqlite3 *pDb,               /* Database to back up */
  const char *zFilename,      /* Name of file to back up to */
  void(*xProgress)(int, int)  /* Progress function to invoke */     
){
  int rc;                     /* Function return code */
  sqlite3 *pFile;             /* Database connection opened on zFilename */
  sqlite3_backup *pBackup;    /* Backup handle used to copy data */

  /* Open the database file identified by zFilename. */
  rc = sqlite3_open(zFilename, &pFile);
  if( rc==SQLITE_OK ){

    /* Open the sqlite3_backup object used to accomplish the transfer */
    pBackup = sqlite3_backup_init(pFile, "main", pDb, "main");
    if( pBackup ){

      /* Each iteration of this loop copies 5 database pages from database
      ** pDb to the backup database. If the return value of backup_step()
      ** indicates that there are still further pages to copy, sleep for
      ** 250 ms before repeating. */
      do {
        rc = sqlite3_backup_step(pBackup, 5);
        xProgress(
            sqlite3_backup_remaining(pBackup),
            sqlite3_backup_pagecount(pBackup)
        );
        if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
          sqlite3_sleep(250);
        }
      } while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );

      /* Release resources allocated by backup_init(). */
      (void)sqlite3_backup_finish(pBackup);
    }
    rc = sqlite3_errcode(pFile);
  }
  
  /* Close the database connection opened on database file zFilename
  ** and return the result of this function. */
  (void)sqlite3_close(pFile);
  return rc;
}



※ sqlite3_backup 을 사용하기 위해서는 Sqlite3 버전이 3.6.14.1 이상이어야 한다.

2010/05/14 17:44 2010/05/14 17:44
Article tag list Go to top
View Comment 0
Trackback URL :: 이 글에는 트랙백을 보낼 수 없습니다
 
 
 
 
: [1] ... [228][229][230][231][232][233][234][235][236] ... [851] :
«   2021/02   »
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28            
전체 (851)
출판 준비 (0)
My-Pro... (41)
사는 ... (492)
블로그... (22)
My Lib... (32)
게임 ... (23)
개발관... (3)
Smart ... (1)
Delphi (76)
C Builder (0)
Object... (0)
VC, MF... (9)
Window... (1)
Open API (3)
Visual... (0)
Java, JSP (2)
ASP.NET (0)
PHP (4)
Database (12)
리눅스 (28)
Windows (21)
Device... (1)
Embedded (1)
게임 ... (0)
Web Se... (2)
Web, S... (19)
잡다한... (5)
프로젝트 (0)
Personal (0)
대통령... (9)
Link (2)