19. DB访问Part 1: MySQL¶

iFun引擎的ORM已实现了用于管理游戏对象的DB自动化处理，但根据情况的不同，也会有须要无视ORM而写入或读取DB数据等直接访问DB服务器的情况。此时，可使用下面介绍的MySQL Connector，在与MySQL兼容的所有DB中进行SQL处理。

Important

该功能并不是为了与ORM并行使用，而是为了对那些因为须要访问运营工具或其他单独的table而导致ORM无法处理的内容进行处理。如果是为了处理游戏对象，请参考ORM。

如果通过MySQL Connector同时访问ORM所管理的对象，有可能会造成数据流失或失灵，十分危险。相关内容请参考从游戏服务器外部访问 DB 时注意事项。

Note

虽然也可以通过直接导入MySQL client库的方式来使用，但使用iFun引擎提供的class，可以在处理时不用注意线程干扰问题，故会更加便利。

Tip

以下的说明内容并没有覆盖所有功能，具体内容请参考 MariaDB class 。

19.1. DB连接初始化¶

19.1.1. 指定参数值¶

Ptr<Mariadb> Mariadb::Create(host, user, password, schema,
                             num_connections [, charset] [, auto_retry_on_deadlock])

Mariadb Mariadb.Create(host, user, password, schema,
                       num_connections [, charset] [, auto_retry_on_deadlock])

指定用于连接DB的参数值。

host

输入DB服务器的地址。例） tcp://127.0.0.1:3306

user

输入DB账户ID。

password

输入DB账户Password。

schema

输入DB名。

num_connections

输入Connection Pool的连接数。

charset

输入Connection的Character Set。如省略，则使用DB默认值。

auto_retry_on_deadlock

敬请参考http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlocks.html中的说明。如省略，则为false。

Note

若Character Set与已生成的DB/Table/Column不同，字符串有可能会成乱码。

Note

当Connection数为2个以上时，将并行执行，因此不保证Query的顺序。

Note

当 num_connections 较大时，部分连接将会失败，或在一定时间后输出连接断线的日志。请在MySQL的设置中将 max_connections 、 backlog 、 max_allowed_packet 值调高。

19.1.2. Thread pool初始化¶

void Mariadb::Initialize()

void Mariadb.Start()

按照通过 Create() 指定的连接数来初始化connection pool。从该函数被调用后开始，即可执行query。

19.2. 执行Query¶

使用以下函数中的一个。结尾带有 Sync 的函数是通过同步方式运行的函数。

void Mariadb::ExecuteQuery(query, callback)
Ptr<ResultSets> Mariadb::ExecuteQuerySync(query, Mariadb::Error *error)

public void Mariadb.ExecuteQuery(query, callback)
public ResultSets ExecuteQuerySync(string query, out Error error)

query

欲执行的SQL Query字符串。

callback

处理执行结果的Callback函数。

error

发生错误时，传输错误值的output变量。

Note

callback须为 void(const Ptr<ResultSets> &result_sets, const Mariadb::Error &error) 形式。

Note

当前版本不支持Prepared Statement。

19.3. MySQL connector使用示例¶

下面的示例假设了如下所示的Table和Procedure。

Member table:

===================
No.  Name.  Weight.
-------------------
0    dog    12.5
1    cat    2.1
2    bird   0.5
3    tiger  290.0

ExampleTable table:

CREATE TABLE ExampleTable (date DATETIME)

ExampleProc procedure:

CREATE PROCEDURE ExampleProc ()
BEGIN
  SELECT * FROM Member;
END

ExampleProc2 procedure:

CREATE PROCEDURE ExampleProc2 (OUT param1 INT, OUT param2 CHAR(50))
BEGIN
  SET param1 = 1234;
  SET param2 = "test";
END

19.3.1. 示例-SELECT¶

下面的示例输出了如下所示的结果。

====================
No: 0, Name: dog, Weight: 12.5
No: 3, Name: tiger, Weight: 290
====================
No: 2, Name: bird, Weight: 0.5

Ptr<Mariadb> connections;

void Initialize() {
  connections = Mariadb::Create("tcp://127.0.0.1:3306", "myid", "mypw",
                                "mydb", 5, "utf8", false);
  connections->Initialize();
}

void ExecuteQuery() {
  std::stringstream query;
  query << "SELECT * FROM Member WHERE Weight > 10.0;"
        << "SELECT * FROM Member WHERE Weight < 1.0;";

  connections->ExecuteQuery(query.str(), OnQueryExecuted);
}

void OnQueryExecuted(const Ptr<ResultSets> &result_sets,
                     const Mariadb::Error &error) {
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }

  // As many as SELECT queries.
  while (result_sets->SeekNextResultSet()) {
    LOG(INFO) << "=====================";

    // As many as result rows of each query.
    while (result_sets->SeekNextRow()) {
      LOG(INFO) << "No: " << result_sets->GetInt(0)
                << ", Name: " << result_sets->GetString(1)
                << ", Weight: " << result_sets->GetDouble(2);
    }
  }
}

Mariadb connections;

void Initialize() {
  Mariadb connections = Mariadb.Create (
      "tcp://127.0.0.1:3306", "funapi", "funapi", "funapi", 8, "utf8");

  connections.Start();
}

void ExecuteQuery() {
  StringBuilder strbuilder = new StringBuilder ();
  strbuilder.Append ("SELECT * FROM Member WHERE Weight > 10.0;");
  strbuilder.Append ("SELECT * FROM Member WHERE Weight < 1.0;");

  connections.ExecuteQuery (query.ToString(), OnQueryExecuted);
}

public void OnQueryExecuted(ResultSets result_sets, Mariadb.Error error)
{
  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  // As many as SELECT queries.
  while (result_sets.SeekNextResultSet ()) {
    Log.Info ("=====================");

    // As many as result rows of each query.
    while (result_sets.SeekNextRow ()) {
      Log.Info ("No: {0}", result_sets.GetString (0));  // 또는 GetInt("No")
      Log.Info (", Name: {0}", result_sets.GetString (1));
      Log.Info (", Weight: {0}", result_sets.GetDouble (2));
    }
  }
}

19.3.2. 示例-INSERT¶

下面是INSERT后通过SELECT来输出结果的示例。输出如下结果。

2016-06-14 16:23:19

Ptr<Mariadb> connections;

void Initialize() {
  connections = Mariadb::Create("tcp://127.0.0.1:3306", "myid", "mypw",
                                "mydb", 5, "utf8", false);
  connections->Initialize();
}

void ExecuteQuery() {
  string now = boost::posix_time::to_iso_extended_string(WallClock::Now());

  std::stringstream query;
  query << "INSERT INTO ExampleTable VALUES('" << now << "');";

  Mariadb::Error error;
  connections->ExecuteQuerySync(query.str(), &error);
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }

  string query2 = "SELECT date FROM ExampleTable;";
  connections->ExecuteQuery(query2, OnQueryExecuted);
}

void OnQueryExecuted(const Ptr<ResultSets> &result_sets,
                     const Mariadb::Error &error) {
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }
  if (not result_sets->SeekNextResultSet()) {
    LOG(ERROR) << "no result set.";
    return;
  }

  while (result_sets->SeekNextRow()) {
    WallClock::Value datetime = result_sets->GetDateTime(0);
    LOG(INFO) << "datetime: " << datetime;
  }
}

Mariadb connections;

void Initialize() {
  Mariadb connections = Mariadb.Create (
      "tcp://127.0.0.1:3306", "funapi", "funapi", "funapi", 8, "utf8");

  connections.Start();
}

// 쿼리 실행
void ExecuteQuery()
{
  string query = "INSERT INTO ExampleTable VALUES('{0}');";
        string now = WallClock.GetTimestring(WallClock.Now);

  Mariadb.Error error;
  connections.ExecuteQuerySync (String.Format (query, now), out error);

  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  string query2 = "SELECT date FROM ExampleTable;";

  connections.ExecuteQuery(query2, OnQueryExecuted);
}

public void OnQueryExecuted(ResultSets result_sets, Mariadb.Error error)
{
  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  if (!result_sets.SeekNextResultSet ()) {
    Log.Error ("no result set.");
    return;
  }

  while (result_sets.SeekNextRow ()) {
    System.DateTime dateTime = result_sets.GetDateTime (0);
    Log.Info ("Datetime : {0}", dateTime);
  }
}

19.3.3. 示例-利用Procedure进行SELECT¶

下面的示例是调用了生成如下所示结果值的Procedure的示例，它代替了直接通过SELECT获取结果。

No: 0, Name: dog, Weight: 12.5
No: 1, Name: cat, Weight: 2.1
No: 2, Name: bird, Weight: 0.5
No: 3, Name: tiger, Weight: 290

Ptr<Mariadb> connections;

void Initialize() {
  connections = Mariadb::Create("tcp://127.0.0.1:3306", "myid", "mypw",
                                "mydb", 5, "utf8", false);
  connections->Initialize();
}

void ExecuteQuery() {
  std::stringstream query;
  query << "CALL ExampleProc();";

  connections->ExecuteQuery(query.str(), OnQueryExecuted);
}

void OnQueryExecuted(const Ptr<ResultSets> &result_sets,
                     const Mariadb::Error &error) {
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }

  if (not result_sets->SeekNextResultSet()) {
    LOG(ERROR) << "no result set.";
    return;
  }

  while (result_sets->SeekNextRow()) {
    LOG(INFO) << "No: " << result_sets->GetInt(0)
              << ", Name: " << result_sets->GetString(1)
              << ", Weight: " << result_sets->GetDouble(2);
  }
}

Mariadb connections;

void Initialize() {
  Mariadb connections = Mariadb.Create (
      "tcp://127.0.0.1:3306", "funapi", "funapi", "funapi", 8, "utf8");

  connections.Start();
}

public void ExecuteQuery(Mariadb connections)
{
  connections.ExecuteQuery ("CALL ExampleProc();", OnQueryExecuted);
}

public void OnQueryExecuted(ResultSets result_sets, Mariadb.Error error)
{
  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  while (result_sets.SeekNextResultSet ()) {
    while (result_sets.SeekNextRow ()) {
      Log.Info ("No: {0}", result_sets.GetInt (0));  // 또는 GetInt("No")
      Log.Info (", Name: {0}", result_sets.GetString (1));
      Log.Info (", Weight: {0}", result_sets.GetDoubl e(2));
    }
  }
}

19.3.4. 示例-获取Procedure的OUTPUT¶

下面是使用Procedure的OUT参数的示例。

Note

当前版本不支持直接接收OUT参数。代入到SQL Session变量中，进行SELECT。

p1: 1234, p2: test

Ptr<Mariadb> connections;

void Initialize() {
  connections = Mariadb::Create("tcp://127.0.0.1:3306", "myid", "mypw",
                                "mydb", 5, "utf8", false);
  connections->Initialize();
}

void ExecuteQuery() {
  std::stringstream query;
  query << "CALL ExampleProc2(@p1, @p2);"
        << "SELECT @p1, @p2;";

  connections->ExecuteQuery(query.str(), OnQueryExecuted);
}

void OnQueryExecuted(const Ptr<ResultSets> &result_sets,
                     const Mariadb::Error &error) {
  if (error.code != 0) {
    LOG(ERROR) << "Error: code=" << error.code << ", desc=" << error.desc;
    return;
  }

  if (not result_sets->SeekNextResultSet()) {
    LOG(ERROR) << "no result set.";
    return;
  }

  while (result_sets->SeekNextRow()) {
    LOG(INFO) << "p1: " << result_sets->GetInt(0)
              << ", p2: " << result_sets->GetString(1);
  }
}

Mariadb connections;

void Initialize() {
  Mariadb connections = Mariadb.Create (
      "tcp://127.0.0.1:3306", "funapi", "funapi", "funapi", 8, "utf8");

  connections.Start();
}

void ExecuteQuery()
{
  StringBuilder query = new StringBuilder ();
  query.Append ("CALL ExampleProc2(@p1, @p2);");
  query.Append ("SELECT @p1, @p2;");
  connections.ExecuteQuery (query.ToString(), OnQueryExecuted);
}

public void OnQueryExecuted(ResultSets result_sets, Mariadb.Error error)
{
  if (error.Code != 0)
  {
    Log.Info ("Error: code={0}, desc={1}", error.Code, error.Desc);
  }

  if (!result_sets.SeekNextResultSet ()) {
    Log.Error ("no result set.");
    return;
  }

  while (result_sets.SeekNextRow ()) {
    Log.Info ("P1 : {0}", result_sets.GetInt (0));
    Log.Info (", P2 : {0}", result_sets.GetString (1));
  }
}