C++ Connection::query方法代码示例

int body(mysqlpp::Connection& c)
{
     std::cout << "<div class=\"body\"><div class=\"main\">";
     //Here, list all user
     int count=0;
     const int MAX=4;

     mysqlpp::Query query = c.query("SELECT login FROM game_account ORDER BY login ASC;");
     query.parse();
     mysqlpp::StoreQueryResult r = query.store();
     if (r && r.num_rows() > 0) {
          std::cout << "<table class=\"playerList\">";
          for (size_t i = 0; i < r.num_rows(); ++i) {
               if (count == 0)
                    std::cout << "<tr>";

               std::string n;
               r[i]["login"].to_string(n);
               std::cout << "<td><a href=\"info-"
                         << n << "\">" << n << "</a></td>" << std::endl;
               count ++;
               if (count == MAX) {
                    std::cout << "</tr>";
                    count=0;
               }
          }

          std::cout << (count != 0 ? "</tr>":"") << "</table>" << std::endl;
     }
     std::cout << "</div></div>";
     return 0;
}

 inline bool TableExists(mysqlpp::Connection& databaseConnection,
     const std::string& schema, const char* table) {
   mysqlpp::Query query = databaseConnection.query();
   query << "SHOW TABLES IN " << schema << " LIKE " << mysqlpp::quote << table;
   mysqlpp::StoreQueryResult result = query.store();
   return !result.empty();
 }

void Durability::operator() (mysqlpp::Connection& conn, const uint32_t itemID, const uint32_t durability)
{
    mysqlpp::Query query = conn.query();

    query << "update items set Durability ="  << durability << " where ID = " << itemID;

    query.exec();
}

 inline bool LoadDirectoriesTable(mysqlpp::Connection& databaseConnection,
     const std::string& schema) {
   if(TableExists(databaseConnection, schema, "directories")) {
     return true;
   }
   mysqlpp::Query query = databaseConnection.query();
   query << "CREATE TABLE directories ("
     "id INTEGER UNSIGNED PRIMARY KEY NOT NULL,"
     "name VARCHAR(100) BINARY NOT NULL)";
   return query.execute();
 }

 inline bool LoadChildrenTable(mysqlpp::Connection& databaseConnection,
     const std::string& schema) {
   if(TableExists(databaseConnection, schema, "children")) {
     return true;
   }
   mysqlpp::Query query = databaseConnection.query();
   query << "CREATE TABLE children ("
     "entry INTEGER NOT NULL,"
     "child INTEGER NOT NULL)";
   return query.execute();
 }

 inline bool LoadPermissionsTable(mysqlpp::Connection& databaseConnection,
     const std::string& schema) {
   if(TableExists(databaseConnection, schema, "permissions")) {
     return true;
   }
   mysqlpp::Query query = databaseConnection.query();
   query << "CREATE TABLE permissions ("
     "source INTEGER UNSIGNED NOT NULL,"
     "target INTEGER UNSIGNED NOT NULL,"
     "permission INTEGER UNSIGNED NOT NULL)";
   return query.execute();
 }

 inline bool LoadSettings(mysqlpp::Connection& databaseConnection,
     Out<unsigned int> nextEntryId) {
   mysqlpp::Query query = databaseConnection.query();
   query << "SELECT * FROM settings";
   mysqlpp::StoreQueryResult result = query.store();
   if(!result) {
     return false;
   }
   settings row = result.front();
   *nextEntryId = row.next_entry_id;
   return true;
 }

// Print information about each of the tables we found
static void
show_table_info(mysqlpp::Connection& con, const vector<string>& tables)
{
	vector<string>::const_iterator it;
	for (it = tables.begin(); it != tables.end(); ++it) {
		mysqlpp::Query query = con.query();
		query << "describe " << *it;
		separator(cout, query.str());
		mysqlpp::StoreQueryResult res = query.store();

		size_t columns = res.num_fields();
		vector<size_t> widths;
		for (size_t i = 0; i < columns; ++i) {
			string s = res.field_name(int(i));
			if (s.compare("field") == 0) {
				widths.push_back(22);
			}
			else if (s.compare("type") == 0) {
				widths.push_back(20);
			}
			else if (s.compare("null") == 0) {
				widths.push_back(4);
			}
			else if (s.compare("key") == 0) {
				widths.push_back(3);
			}
			else if (s.compare("extra") == 0) {
				widths.push_back(0);
			}
			else {
				widths.push_back(15);
			}

			if (widths[i]) {
				cout << '|' << setw(widths[i]) << 
						res.field_name(int(i)) << '|';
			}
		}
		cout << endl;

		mysqlpp::StoreQueryResult::iterator rit;
		for (rit = res.begin(); rit != res.end(); ++rit) {
			for (unsigned int i = 0; i < columns; ++i) {
				if (widths[i]) {
					cout << ' ' << setw(widths[i]) <<
							(*rit)[i].c_str() << ' ';
				}
			}
			cout << endl;
		}
	}
}

int show(const std::string& login, mysqlpp::Connection& c)
{
     std::map<std::string, unsigned int> unitList;
     int id=0,faction=0,xp=0,level=0;

     mysqlpp::Query query = c.query("SELECT id,faction,xp,level FROM game_account where login = %0Q;");
     query.parse();
     mysqlpp::StoreQueryResult r = query.store(login);
     if (r && r.num_rows() > 0) {
          //Should only be one account with that name
          id = r[0]["id"];
          faction = r[0]["faction"];
          xp = r[0]["xp"];
          level = r[0]["level"];

          //Now we have the account, get its units and their number
          query.reset();
          query << "select unit_name,unit_number from game_unit_available where id_account = %0;";
          query.parse();
          mysqlpp::StoreQueryResult u = query.store(id);
          if (u && u.num_rows() > 0)
               for (size_t i = 0; i < u.num_rows(); ++i) {
                    int num = u[i]["unit_number"];
                    if(num > 0) {
                         std::string name;
                         u[i]["unit_name"].to_string(name);
                         unitList[name] = num;
                    }
               }
     } //Should never happen, as we are connected !

     //Display both form and information
     std::cout << "<div class=\"body\"><div class=\"main\" >"
               << "<center>" << __tr("faction") << ": <strong>" << __tr(factionName[faction]) << "</strong><br />"
               << "<strong>" << __tr("army_composition") << "</strong><br />";

     if (unitList.size() > 0) {
          std::cout << "<table class=\"army\"><tr>"
                    << "<td><strong>" << __tr("unit") << "</strong></td>"
                    << "<td><strong>" << __tr("number") << "</strong></td></tr>";
          for (auto it = unitList.begin(); it != unitList.end(); ++it) {
               std::cout << "<tr><td><strong>" << __tr(it->first) << "</strong></td>"
                         << "<td><strong>" << it->second << "</strong></td></tr>";
          }
          std::cout << "</table>";
     } else  std::cout << "<em>" << __tr("army_empty") << "</em>";

     std::cout << "</center></div>";

     std::cout << "</div></div>" << std::endl;
     return 0;
}

// Print out the names of all the databases managed by the server
static void
show_databases(mysqlpp::Connection& con)
{
	mysqlpp::Query query = con.query("show databases");
	separator(cout, query.str());
	mysqlpp::StoreQueryResult res = query.store();

	cout << "Databases found: " << res.size();
	cout.setf(ios::left);
	mysqlpp::StoreQueryResult::iterator rit;
	for (rit = res.begin(); rit != res.end(); ++rit) {
		cout << "\n\t" << (*rit)[0];
	}
}

 inline bool LoadAccountsTable(mysqlpp::Connection& databaseConnection,
     const std::string& schema) {
   if(TableExists(databaseConnection, schema, "accounts")) {
     return true;
   }
   mysqlpp::Query query = databaseConnection.query();
   query << "CREATE TABLE accounts ("
     "id INTEGER UNSIGNED PRIMARY KEY NOT NULL,"
     "name VARCHAR(100) BINARY NOT NULL,"
     "password VARCHAR(100) BINARY NOT NULL,"
     "registration_time DATETIME NOT NULL,"
     "last_login_time DATETIME NOT NULL)";
   return query.execute();
 }

 inline bool LoadSettingsTable(mysqlpp::Connection& databaseConnection,
     const std::string& schema) {
   if(TableExists(databaseConnection, schema, "settings")) {
     return true;
   }
   mysqlpp::Query query = databaseConnection.query();
   query << "CREATE TABLE settings (next_entry_id INTEGER UNSIGNED NOT NULL)";
   if(!query.execute()) {
     return false;
   }
   query.reset();
   SqlInsert::settings settingsRow(0);
   query.insert(settingsRow);
   return query.execute();
 }

std::vector<Item::extended_type> Select::operator () (mysqlpp::Connection& conn, const uint32_t CharID,
    const uint8_t location)
{
    mysqlpp::Query query = conn.query();

    query << "select * from items where CharID =" << CharID << " and Location = " << mysqlpp::quote << location;

    mysqlpp::StoreQueryResult res = query.store();

    Item::extended_type attr;
    std::vector<Item::extended_type> v;


    for (size_t j=0; j < res.num_rows(); ++j)
    {
        attr.dbid = res[j]["ID"];
            attr.id = res[j]["refItemID"];
            attr.enchant_lv = res[j]["EnchantLevel"];
            attr.quantity = res[j]["Quantity"];
            attr.pos = res[j]["Position"];
            attr.place = res[j]["Location"];
            attr.durability = res[j]["Durability"];
            attr.phy_percent = res[j]["Phy"];
            attr.mag_percent = res[j]["Mag"];
            attr.dur_percent = res[j]["Dur"];
            attr.ap_percent = res[j]["AP"];
            attr.cb_percent = res[j]["CB"];
            attr.phyreinf_percent = res[j]["PhyReinf"];
            attr.magreinf_percent = res[j]["MagReinf"];

    query.reset();

    query << "select * from items_magicoptions where itemID = " << attr.dbid;

    mysqlpp::StoreQueryResult ires = query.store();

    for (size_t i = 0; i < ires.num_rows(); ++i)
        attr.attr_list.push_back(std::make_pair(ires[i]["optionID"],ires[i]["Value"]));

            v.push_back(attr);

            attr.attr_list.clear();
    }

    return v;
}

void createRSS(const std::string& locale, mysqlpp::Connection& c)
{
     std::ofstream f("rss/"+locale+".xml");
     if(f) {
          f <<
               "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n"
               "<rss version=\"2.0\">\n"
               "        <channel>\n"
               "                <description>" << __tr("description") << "</description>\n"
               "                <link>http://okzoniom.fr</link>\n"
               "                <title>Okzoniom::RPG</title>\n";

          //write all
          mysqlpp::Query query = c.query();
          query <<  "SELECT * FROM web_news WHERE locale='" << locale << "'ORDER BY date DESC";
          mysqlpp::StoreQueryResult r = query.store();
          if(r && r.num_rows() > 0) {
               for(int i = 0; i < r.num_rows(); ++i) {
                    std::string title, titleLink;
                    std::string content;
                    std::string date;
                    std::string id;
                    r[i]["id"].to_string(id);
                    r[i]["title"].to_string(title);
                    r[i]["content"].to_string(content);
                    r[i]["date"].to_string(date);
                    titleLink = title;

                    f <<
                    "            <item>\n"
                    "                <title>" << title << "</title>\n"
                    "                <link>" << "http://okzoniom.fr/index-"+id+"-"+urlHTML(titleLink) << "</link>\n"
                    "                <description>"
                    << "<![CDATA[<p>" << content << "</p>]]>"<<
                    "                </description>\n"
                    "                <pubDate>" << date << "</pubDate>\n"
                    "            </item>\n";
               }
          }
          f <<
               "        </channel>\n"
               "</rss>\n";
          f.close();
     }
}

 inline bool LoadEntriesTable(mysqlpp::Connection& connection,
     const std::string& schema) {
   if(Beam::MySql::TestTable(schema, "entries", connection)) {
     return true;
   }
   auto query = connection.query();
   query << "CREATE TABLE entries ("
     "name VARCHAR(16) BINARY NOT NULL,"
     "item_a INTEGER NOT NULL,"
     "item_b BIGINT NOT NULL,"
     "item_c BIGINT NOT NULL,"
     "item_d VARCHAR(256) BINARY NOT NULL,"
     "timestamp BIGINT UNSIGNED NOT NULL,"
     "query_sequence BIGINT UNSIGNED NOT NULL,"
     "INDEX sequence_index(name, query_sequence),"
     "INDEX timestamp_index(name, timestamp, query_sequence))";
   return query.execute();
 }