inline void MySqlDataStore::OpenDatabaseConnection(
mysqlpp::Connection& connection) {
auto connectionResult = connection.set_option(
new mysqlpp::ReconnectOption{true});
if(!connectionResult) {
BOOST_THROW_EXCEPTION(IO::ConnectException{
"Unable to set MySQL reconnect option."});
}
connectionResult = connection.connect(m_schema.c_str(),
m_address.GetHost().c_str(), m_username.c_str(), m_password.c_str(),
m_address.GetPort());
if(!connectionResult) {
BOOST_THROW_EXCEPTION(IO::ConnectException{std::string{
"Unable to connect to MySQL database - "} + connection.error()});
}
}
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 EnchantLv::operator() (mysqlpp::Connection& conn, const uint32_t itemID, const uint8_t level)
{
mysqlpp::Query query = conn.query();
query << "update items set EnchantLevel = " << mysqlpp::quote << level << " where ID = " << itemID;
query.exec();
}
void Position::operator() (mysqlpp::Connection &conn, const uint32_t itemID, const uint8_t pos)
{
mysqlpp::Query query = conn.query();
query << "update items set Position = " << mysqlpp::quote << pos << " where ID = " << itemID;
query.exec();
}
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();
}
void Quantity::operator() (mysqlpp::Connection& conn, const uint32_t itemID, const uint16_t quantity)
{
mysqlpp::Query query = conn.query();
query << "update items set Quantity = " << quantity << " where ID =" << itemID;
query.exec();
}
void MagicOption::operator() (mysqlpp::Connection &conn, const uint32_t itemID, const uint32_t optionID,
const uint32_t value)
{
mysqlpp::Query query = conn.query();
query << "insert into items_magicoptions values (" << itemID << "," << optionID << "," << value << ")";
query.exec();
}
bool DatabaseStorage::connectToDatabase(mysqlpp::Connection& con)
{
const char *server = 0;
try
{
if (!con.connect(0, server, USER, PASSWORD))
return false;
}
catch (std::exception& er)
{
std::cerr << "Attempt to connect to database - exception was thrown.\n";
return false;
}
mysqlpp::NoExceptions ne(con);
if (!con.select_db(DATABASE))
return false;
return true;
}
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 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;
}
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();
}
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 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;
}
}
}
uint32_t ID::operator() (mysqlpp::Connection &conn, const std::string &name)
{
mysqlpp::Query query = conn.query();
query << "select id from refitemdata where name = " << mysqlpp::quote << name;
mysqlpp::StoreQueryResult res = query.store();
if (res.num_rows())
return res[0]["id"];
return 0;
}
uint32_t TransportID::operator() (mysqlpp::Connection &conn, const std::string &name)
{
mysqlpp::Query query = conn.query();
query << "select ID from refcharacterdata where NameID = " << mysqlpp::quote << name;
mysqlpp::StoreQueryResult res = query.store();
if (res.num_rows())
return res[0]["ID"];
return 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();
}
void Delete::operator() (mysqlpp::Connection& conn, const uint32_t itemID)
{
mysqlpp::Query query = conn.query();
query << "delete from items where ID =" << itemID;
query.exec();
query.reset();
query << "delete from items_magicoptions where itemID = " << itemID;
query.exec();
}
// 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 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();
}
// Print out the names of all tables in the sample database, and
// return the list of tables.
static void
show_tables(mysqlpp::Connection& con)
{
mysqlpp::Query query = con.query("show tables");
separator(cout, query.str());
mysqlpp::StoreQueryResult res = query.store();
cout << "Tables found: " << res.size();
cout.setf(ios::left);
vector<string> tables;
mysqlpp::StoreQueryResult::iterator rit;
for (rit = res.begin(); rit != res.end(); ++rit) {
string tbl((*rit)[0]);
cout << "\n\t" << tbl;
tables.push_back(tbl);
}
show_table_info(con, tables);
}
uint32_t Create::operator() (mysqlpp::Connection& conn, const uint32_t ID, const Item& item)
{
if (!item.get_id())
return 0;
using mysqlpp::quote;
mysqlpp::Query query = conn.query();
query << "insert into items (charID,refItemID, EnchantLevel, Quantity, Location, "
<< "Position, Durability, Phy, Mag, Dur, AP, CB, PhyReinf, MagReinf) values ("
<< quote << ID << ","
<< quote << (mysqlpp::sql_int)item.get_id() << ","
<< quote << item.get_enchant_lv() << ","
<< quote << (mysqlpp::sql_int)item.get_quantity() << ","
<< quote << item.get_place() << ","
<< quote << item.get_position() << ","
<< quote << (mysqlpp::sql_int)item.get_durability() << ","
<< quote << item.get_physical_percent() << ","
<< quote << item.get_magical_percent() << ","
<< quote << item.get_durability_percent() << ","
<< quote << item.get_ap_ratio_percent() << ","
<< quote << item.get_cb_ratio_percent() << ","
<< quote << item.get_physical_reinf_percent() << ","
<< quote << item.get_magical_reinf_percent() << ")";
query.exec();
uint32_t itemID = query.insert_id();
std::vector<Item::attribute> attr = item.get_attr_list();
for ( size_t i = 0; i < attr.size(); ++i)
{
query.reset();
query << "insert into items_magicoptions values ("
<< itemID << "," << attr[i].first << "," << attr[i].second << ")";
query.exec();
}
return itemID;
}
void Percent::operator() (mysqlpp::Connection& conn, const std::string& name,
const Item& item)
{
using mysqlpp::quote;
mysqlpp::Query query = conn.query();
query << "update items set "
<< "Phy=" << quote << item.get_physical_percent() << ","
<< "Mag=" << quote << item.get_magical_percent() << ","
<< "Dur=" << quote << item.get_durability_percent() << ","
<< "AP=" << quote << item.get_ap_ratio_percent() << ","
<< "CB=" << quote << item.get_cb_ratio_percent() << ","
<< "PhyReinf=" << quote << item.get_physical_reinf_percent() << ","
<< "MagReinf=" << quote << item.get_magical_reinf_percent() << " "
<< "where name=" << quote << name << " "
<< "and Position =" << quote << item.get_position() << " "
<< "and Location =" << quote << item.get_place();
query.exec();
}
uint32_t SkillID::operator() (mysqlpp::Connection &conn, const uint32_t ID)
{
mysqlpp::Query query = conn.query();
query << "select arg1 from refitemdata where id = " << ID;
mysqlpp::StoreQueryResult res = query.store();
if (res.num_rows())
{
query.reset();
query << "select id from refskilldata where name = " << mysqlpp::quote << res[0]["arg1"];
mysqlpp::StoreQueryResult res = query.store();
if (res.num_rows())
return res[0]["id"];
}
return 0;
}
// Print out the MySQL server version
static void
show_mysql_version(mysqlpp::Connection& con)
{
separator(cout, "");
cout << "MySQL version: " << con.client_version();
}
