TimescaleDB에 구조체 삽입 후 조회(1)

TimescaleDB에 일반 데이터 타입이 아닌 구조체 데이터를 삽입 후 조회하기 위한 방법이다.

 

 

1. 로그인

• 비밀번호가 1234인 postgres 유저로 호스트 127.0.0.1, 포트 5432인 DBMS에 접속한 후에 example 데이터베이스를 선택한다.

[namsaenga@localhost ~]$ psql postgres://postgres:1234@127.0.0.1:5432/example
psql (14.2)
Type "help" for help.
example=#

 

 

2. Server Encoding 및 Client Encoding 확인

• 다양한 언어를 나타내기 위해서는 character set이 유니코드여야 한다. 유니코드를 사용하는 데이터베이스의 인코딩은 UTF-8이며, 서버와 클라이언트 모두 인코딩 종류가 같아야 데이터를 넣고 꺼낼때 문제가 발생하지 않는다.

example=# show server_encoding;
 server_encoding 
-----------------
 UTF8
(1 row)
example=# show client_encoding;
 client_encoding 
-----------------
 EUC_KR
(1 row)
example=# set client_encoding to 'utf8';
SET
example=# show client_encoding;
 client_encoding 
-----------------
 UTF8
(1 row)

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 example   | postgres | UTF8     | ko_KR.utf8 | ko_KR.utf8 | 
 postgres  | postgres | UTF8     | ko_KR.utf8 | ko_KR.utf8 | 
 template0 | postgres | UTF8     | ko_KR.utf8 | ko_KR.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | ko_KR.utf8 | ko_KR.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

 

 

3. 선택한 데이터베이스의 인코딩 확인

• where 절을 생략하면 모든 데이터베이스의 인코딩을 확인할 수 있다.

example=# select pg_encoding_to_char(encoding) from pg_database where datname = 'example';
 pg_encoding_to_char 
---------------------
 UTF8
(1 row)

 

 

4. 인코딩 변경 예시

update pg_database set encoding = pg_char_to_encoding('UTF-8') where datname = 'example';     // for Unicode
update pg_database set encoding = pg_char_to_encoding('iso8859-1') where datname = 'example'; // for ASCII
update pg_database set encoding = pg_char_to_encoding('EUC-KR') where datname = 'example';    // for Unix code
update pg_database set encoding = pg_char_to_encoding('SQL_ASCII') where datname = 'example'; // for ASCII

 

 

5. 시계열 테이블 생성

example=# create table if not exists test2 (time timestamp without time zone not null, varchar_content varchar null, bytea_content bytea null);
CREATE TABLE
example=# select create_hypertable('test2','time');
 create_hypertable  
--------------------
 (6,public,test2,t)
(1 row)

 

 

6. 데이터 삽입

• bytea 데이터 타입은 문자열이 hex로 변환되어 저장된다.

example=# insert into test2(time, varchar_content, bytea_content) values(now(), 'hello', 'hello');
INSERT 0 1
example=# select * from test2;
            time            | varchar_content | bytea_content 
----------------------------+-----------------+---------------
 2022-03-21 14:32:56.320848 | hello           | \x68656c6c6f
(1 row)

 

 

7. 데이터 조회

• select 쿼리문에서 convert_from 함수와 같이 사용하여 조회한다.
• bytea를 사용하는 이유는 varchar의 사이즈가 64KB인 반면에 bytea의 사이즈는 1GB이기 때문이다.

example=# select time, varchar_content, convert_from(bytea_content, 'UTF8') as bytea_content from test2;
            time            | varchar_content | bytea_content 
----------------------------+-----------------+---------------
 2022-03-21 14:32:56.320848 | hello           | hello
(1 row)

 

 

8. 번외 : base64로 인코딩된 데이터 삽입 및 조회

• 일반 문자열을 넣을 때 varchar에서는 base64로 인코딩하여 넣을 수 있지만 bytea는 base64로 인코딩하여 넣을 수 없다.
• base64로 인코딩하여 넣은 문자열은 base64로 디코딩 및 UTF8로 변환해야 한다.

example=# insert into test2(time, varchar_content, bytea_content) values(now(), encode('hello', 'base64'), encode('hello', 'base64'));
오류:  열 "bytea_content"은(는) bytea 자료형인데 표현식은 text 자료형입니다.
LINE 1: ...content) values(now(), encode('hello', 'base64'), encode('he...
                                                             ^
HINT:  다시 정의하거나 형변화자를 사용해보십시오
example=# insert into test2(time, varchar_content, bytea_content) values(now(), encode('hello', 'base64'), 'hello');
INSERT 0 1
example=# select * from test2;
            time            | varchar_content | bytea_content 
----------------------------+-----------------+---------------
 2022-03-21 15:05:36.968386 | aGVsbG8=        | \x68656c6c6f
(1 row)
example=# select time, decode(varchar_content, 'base64'), convert_from(bytea_content, 'UTF8') from test2;
            time            |    decode    | convert_from 
----------------------------+--------------+--------------
 2022-03-21 15:05:36.968386 | \x68656c6c6f | hello
(1 row)
example=# select time, convert_from(decode(varchar_content, 'base64'),'UTF8'), convert_from(bytea_content, 'UTF8') from test2;
            time            | convert_from | convert_from 
----------------------------+--------------+--------------
 2022-03-21 15:05:36.968386 | hello        | hello
(1 row)

 

 

---

 

 

다음은 libpqxx 라이브러리를 이용한 C++ 프로그래밍으로 구조체 데이터를 넣고 꺼내는 예시이다.

 

1. 구조체 데이터 넣기

 

1) 구조체 데이터를 char*로 바로 캐스팅하여 넣을 시

 

a. InputStruct.cpp 소스코드

#include <cpp_redis-master/includes/cpp_redis/cpp_redis>
#include <iostream>
#include <string>
#include <random>
#include <pqxx/pqxx>
using namespace std;

struct ANIMAL {
  char name[5];
  char gender;
  char color[5];
};

static int DoInputStruct() {

  struct ANIMAL chicken;
  struct ANIMAL pig;

  chicken.name[0] = 'B'; chicken.name[1] = 'I'; chicken.name[2] = 'B'; chicken.name[3] = 'I'; chicken.name[4] = 'Q';
  chicken.gender = 'M';
  chicken.color[0] = 'B'; chicken.color[1] = 'R'; chicken.color[2] = 'O'; chicken.color[3] = 'W'; chicken.color[4] = 'N'; 

  pig.name[0] = 'P'; pig.name[1] = 'O'; pig.name[2] = 'K'; pig.name[3] = 'E'; pig.name[4] = 'Y';
  pig.gender = 'F';
  pig.color[0] = 'P'; pig.color[1] = 'I'; pig.color[2] = 'N'; pig.color[3] = 'K'; pig.color[4] = 'K'; 


  pqxx::connection c{"postgres://postgres:1234@127.0.0.1:5432/example"};
  pqxx::work work{c};
  c.prepare("DoInputStructTest", "insert into test2(time, varchar_content, bytea_content) values($1, $2, $3)");


  work.exec_prepared("DoInputStructTest", "now()" , (char*)&chicken, (char*)&pig);
  work.commit();


  return 0;
}


int main() {
  
  return DoInputStruct();
}

 

 

b. 실행화면

[namsaenga@localhost postgresql_examples]$ g++ inputStruct.cpp -std=c++17 -o inputStruct -lpqxx
[namsaenga@localhost postgresql_examples]$ ./inputStruct

 

 

c. 데이터조회 및 결과화면

example=# select time, varchar_content, convert_from(bytea_content, 'UTF8') from test2;
            time            | varchar_content |        convert_from        
----------------------------+-----------------+----------------------------
 2022-03-21 15:31:13.138431 | BIBIQMBROWN\x01 | POKEYFPINKKBIBIQMBROWN\x01
(1 row)

 

 

 

2) 구조체 데이터를 std::basic_string<std::byte>로 만든 후 넣으려고 했을 시

 

 

a. 소스코드 추가 및 수정부분

  std::basic_string<std::byte> byte_str1(
    static_cast<const std::byte *>(static_cast<const void *>(&chicken)),
    sizeof(ANIMAL)
  );

  std::basic_string<std::byte> byte_str2(
    static_cast<const std::byte *>(static_cast<const void *>(&pig)),
    sizeof(ANIMAL)
  );


  work.exec_prepared("DoInputStructTest", "now()" , byte_str1, byte_str2);

 

 

b. 실행화면

• (위와 동일)

 

 

c. 데이터조회 및 결과화면

• 구조체를 BYTE로 변환한 후에 데이터베이스에 넣으면 원하는 결과가 나오는 것을 알 수 있다.

example=# select time, varchar_content, convert_from(bytea_content, 'UTF8') from test2;
            time            | varchar_content | convert_from 
----------------------------+-----------------+--------------
 2022-03-21 15:37:08.185024 | BIBIQMBROWN     | POKEYFPINKK
(1 row)

 

 

3) 구조체 멤버를 char array가 아닌 string을 사용해서 넣을 시

 

 

a. 소스코드 추가 및 수정부분

struct ANIMAL {
  char name[5];
  char gender;
  char color[5];
};

 

 

b. 실행화면

• BYTE로 변환한 후에 데이터베이스에 넣으려고 했는데 다음과 같은 에러가 발생했다. 컴파일은 잘 됬었다.

[namsaenga@localhost postgresql_examples]$ g++ inputStruct.cpp -std=c++17 -o inputStruct -lpqxx
[namsaenga@localhost postgresql_examples]$ ./inputStruct
terminate called after throwing an instance of 'pqxx::data_exception'
  what():  Failure during 'DoInputStructTest': 오류:  "UTF8" 인코딩에서 사용할 수 없는 문자가 있음: 0xa0
CONTEXT:  unnamed portal parameter $2

 

 

c. 소스코드 추가부분

• base64로 인코딩하여 넣으니 잘 들어갔다.

// Other functions
std::string base64_encode(const std::string &in) { typedef unsigned char uchar; std::string out; int val = 0, valb = -6; for (uchar c : in) { val = (val << 8) + c; valb += 8; while (valb >= 0) { out.push_back("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"[(val >> valb) & 0x3F]); valb -= 6; } } if (valb > -6) out.push_back("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"[((val << 8) >> (valb + 8)) & 0x3F]); while (out.size() % 4) out.push_back('='); return out; }
std::string base64_decode(const std::string &in) { typedef unsigned char uchar; static const std::string b = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"; std::string out; std::vector<int> T(256, -1); for (int i = 0; i < 64; i++) T[b[i]] = i; int val = 0, valb = -8; for (uchar c : in) { if (T[c] == -1) break; val = (val << 6) + T[c]; valb += 6; if (valb >= 0) { out.push_back(char((val >> valb) & 0xFF)); valb -= 8; } } return out; }


// In DoInputStructTest function
std::string str1(reinterpret_cast<char const*>(&byte_str1), sizeof(byte_str1));
std::string str2(reinterpret_cast<char const*>(&byte_str2), sizeof(byte_str2));

work.exec_prepared("DoInputStructTest", "now()" , base64_encode(str1), base64_encode(str2));

 

 

d. 데이터조회 및 결과화면

• base64로 복호화해서 결과를 조회하려고 할 때 다음과 같은 에러가 발생했다.

example=# select * from test2;
            time            |               varchar_content                |                                       bytea_con
tent                                        
----------------------------+----------------------------------------------+------------------------------------------------
--------------------------------------------
 2022-03-21 15:53:28.539631 | 4EEVAgAAAABIAAAAAAAAAEgAAAAAAAAAgAAAAAAAAAA= | \x774259564167414141414249414141414141414141456
741414141414141414159414141414141414141413d
(1 row)


example=# select time, convert_from(decode(varchar_content, 'base64'), 'UTF8'), convert_from(bytea_content, 'UTF8') from test2;
오류:  "UTF8" 인코딩에서 사용할 수 없는 문자가 있음: 0xe0 0x41 0x15

 

• 결론: 구조체의 멤버로 string을 사용하지 말자!

---

 

2. 구조체 데이터 읽어오기

 

 

a. OutputStruct.cpp 소스코드

#include <cpp_redis-master/includes/cpp_redis/cpp_redis>
#include <iostream>
#include <string>
#include <random>
#include <pqxx/pqxx>

using namespace std;

std::string base64_encode(const std::string &in) { typedef unsigned char uchar; std::string out; int val = 0, valb = -6; for (uchar c : in) { val = (val << 8) + c; valb += 8; while (valb >= 0) { out.push_back("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"[(val >> valb) & 0x3F]); valb -= 6; } } if (valb > -6) out.push_back("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"[((val << 8) >> (valb + 8)) & 0x3F]); while (out.size() % 4) out.push_back('='); return out; }
std::string base64_decode(const std::string &in) { typedef unsigned char uchar; static const std::string b = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"; std::string out; std::vector<int> T(256, -1); for (int i = 0; i < 64; i++) T[b[i]] = i; int val = 0, valb = -8; for (uchar c : in) { if (T[c] == -1) break; val = (val << 6) + T[c]; valb += 6; if (valb >= 0) { out.push_back(char((val >> valb) & 0xFF)); valb -= 8; } } return out; }

struct ANIMAL {
  char name[5];
  char gender;
  char color[5];
};


static int DoOutputStruct() {
  ANIMAL ani1;
  ANIMAL ani2;

  pqxx::connection c{"postgres://postgres:1234@192.168.1.2:5432/example"};
  pqxx::work work{c};

 
  pqxx::result result_value = work.exec("select time, varchar_content, convert_from(bytea_content, 'UTF8') from test2");
 

  for(pqxx::result::const_iterator iter = result_value.begin(); iter != result_value.end(); iter++){
    cout << "time = " << iter[0].as<string>() << endl;
    cout << "ani1 = " << iter[1].as<string>() << endl;
    cout << "ani2 = " << iter[2].as<string>() << endl;

    memmove((char*)&ani1, iter[1].as<string>().c_str(), sizeof(iter[2].as<string>()));
    cout << "ani1 name = " << ani1.name << "  ani1 gender = " << ani1.gender << "  ani1.color = " << ani1.color << endl;
  
    
    memmove((char*)&ani2, iter[2].as<string>().c_str(), sizeof(iter[2].as<string>()));
    cout << "ani2 name = " << ani2.name << "  ani2 gender = " << ani2.gender << "  ani2.color = " << ani2.color << endl;
    
  }
  
  return 0;
}


int main() {
  
  return DoOutputStruct();

}

 

 

b. 실행 및 결과화면

• name 출력 시에 name만 나와야 하는데 gender와 color까지 출력되는 것을 알 수 있다.

[namsaenga@localhost postgresql_examples]$ g++ outputStruct.cpp -std=c++17 -o outputStruct -lpqxx
[namsaenga@localhost postgresql_examples]$ ./outputStruct
time = 2022-03-21 16:04:25.932749
ani1 = BIBIQMBROWN
ani2 = POKEYFPINKK
ani1 name = BIBIQMBROWN  ani1 gender = M  ani1.color = BROWN
ani2 name = POKEYFPINKK  ani2 gender = F  ani2.color = PINKK

 

 

c. 소스코드 수정

• memmove를 사용한 다음의 cout 구문을 아래와 같이 수정한다.

cout << "ani1 name = ";
printf("%.*s ", 5, ani1.name);
cout << "  ani1 gender = " << ani1.gender << "  ani1.color = " << ani1.color << endl;


cout << "ani2 name = ";
printf("%.*s ", 5, ani2.name);
cout << "  ani2 gender = " << ani2.gender << "  ani2.color = " << ani2.color << endl;

 

 

d. 실행 및 결과화면

• 잘 나오는 것을 알 수 있다.

[namsaenga@localhost postgresql_examples]$ g++ outputStruct.cpp -std=c++17 -o outputStruct -lpqxx
[namsaenga@localhost postgresql_examples]$ ./outputStruct
time = 2022-03-21 16:04:25.932749
ani1 = BIBIQMBROWN
ani2 = POKEYFPINKK
ani1 name = BIBIQ   ani1 gender = M  ani1.color = BROWN
ani2 name = POKEY   ani2 gender = F  ani2.color = PINKK