QueryResultMysql::QueryResultMysql(MYSQL_RES *result, uint64 rowCount, uint32 fieldCount) :
QueryResult(rowCount, fieldCount), mResult(result)
{
mCurrentRow = new Field[mFieldCount];
assert(mCurrentRow);
// mysql-5.0.22 version mysql_fetch_fields get data error, maybe mysql static lib have changed
// MYSQL_FIELD *fields = mysql_fetch_fields(mResult);
//
// for (uint32 i = 0; i < mFieldCount; i++)
// {
// mCurrentRow[i].SetName(fields[i].name);
// mCurrentRow[i].SetType(ConvertNativeType(fields[i].type));
//
// }
MYSQL_FIELD *field = NULL;
for (uint32 i = 0; i < mFieldCount; i++)
{
field = mysql_fetch_field(mResult);
mCurrentRow[i].SetName(field->name);
mCurrentRow[i].SetType(ConvertNativeType(field->type));
}
}
QueryResultMysql::QueryResultMysql(MYSQL_RES* result, MYSQL_FIELD* fields, uint64 rowCount, uint32 fieldCount) :
QueryResult(rowCount, fieldCount), mResult(result)
{
mCurrentRow = new Field[mFieldCount];
MANGOS_ASSERT(mCurrentRow);
for (uint32 i = 0; i < mFieldCount; ++i)
mCurrentRow[i].SetType(ConvertNativeType(fields[i].type));
}
QueryResultPostgre::QueryResultPostgre(PGresult *result, uint64 rowCount, uint32 fieldCount) :
QueryResult(rowCount, fieldCount), mResult(result), mTableIndex(0)
{
mCurrentRow = new Field[mFieldCount];
ASSERT(mCurrentRow);
for (uint32 i = 0; i < mFieldCount; i++)
mCurrentRow[i].SetType(ConvertNativeType(PQftype( result, i )));
}
QueryResultMysql::QueryResultMysql(MYSQL_RES *result, MYSQL_FIELD *fields, GDBC::uint64 rowCount, GDBC::uint32 fieldCount) :
QueryResult(rowCount, fieldCount), mResult(result)
{
mCurrentRow = new Field[mFieldCount];
assert(mCurrentRow);
for (GDBC::uint32 i = 0; i < mFieldCount; i++)
mCurrentRow[i].SetType(ConvertNativeType(fields[i].type));
}
QueryResult::QueryResult(MYSQL_RES *result, MYSQL_FIELD *fields, uint64 rowCount, uint32 fieldCount)
: mFieldCount(fieldCount)
, mRowCount(rowCount)
, mResult(result)
{
mCurrentRow = new Field[mFieldCount];
ASSERT(mCurrentRow);
for (uint32 i = 0; i < mFieldCount; i++)
mCurrentRow[i].SetType(ConvertNativeType(fields[i].type));
}
QueryResultMysql::QueryResultMysql(MYSQL_RES *result, uint64 rowCount, uint32 fieldCount) :
QueryResult(rowCount, fieldCount), mResult(result)
{
mCurrentRow = new Field[mFieldCount];
ASSERT(mCurrentRow);
MYSQL_FIELD *fields = mysql_fetch_fields(mResult);
for (uint32 i = 0; i < mFieldCount; i++)
{
mCurrentRow[i].SetName(fields[i].name);
mCurrentRow[i].SetType(ConvertNativeType(fields[i].type));
}
}
QueryResultMysql::QueryResultMysql(MYSQL_RES *result, uint64 rowCount, uint32 fieldCount) :
QueryResult(rowCount, fieldCount), mResult(result)
{
// There were rows returned from the query, so fetch them.
mCurrentRow = new Field[mFieldCount];
ASSERT(mCurrentRow);
MYSQL_FIELD *fields = mysql_fetch_fields(mResult);
for (uint32 i = 0; i < mFieldCount; ++i)
{
//mCurrentRow[i].SetName(fields[i].name);
mCurrentRow[i].SetType(ConvertNativeType(fields[i].type));
}
}
int main(int argc, char* argv[])
{
char sPGhost[26], sPGport[26], sPGdb[26], sPGuser[26], sPGpass[26];
printf("Postgres connection settings\n Host>");
scanf("%s", sPGhost);
printf(" Port>");
scanf("%s", sPGport);
printf(" Base>");
scanf("%s", sPGdb);
printf(" User>");
scanf("%s", sPGuser);
printf(" Pass>");
scanf("%s", sPGpass);
///////////////////////////////
///////PGSQL Connect///////////
///////////////////////////////
PGconn* mPGconn = NULL;
mPGconn = PQsetdbLogin(sPGhost, sPGport, NULL, NULL, sPGdb, sPGuser, sPGpass);
if (PQstatus(mPGconn) != CONNECTION_OK)
{
printf("Could not connect to Postgre database at [%s]: \n %s\n", sPGhost, PQerrorMessage(mPGconn));
PQfinish(mPGconn);
return 1;
}
else
{
printf("Connected to Postgre database at [%s]\n", sPGhost);
printf(" PostgreSQL server ver: [%d]\n\n", PQserverVersion(mPGconn));
}
/// Set dummy notice processor
PQsetNoticeProcessor(mPGconn, pg_notice, mPGconn);
///////////////////////////////
///////MySQL Connect///////////
///////////////////////////////
MYSQL* mysqlInit;
mysqlInit = mysql_init(NULL);
if (!mysqlInit)
{
printf("Could not initialize Mysql connection\n");
return 1;
}
char sMYhost[26], sMYdb[26], sMYuser[26], sMYpass[26];
int iMYport;
printf("Mysql connection settings \n Host>");
scanf("%s", sMYhost);
printf(" Port>");
scanf("%d", &iMYport);
printf(" Base>");
scanf("%s", sMYdb);
printf(" User>");
scanf("%s", sMYuser);
printf(" Pass>");
scanf("%s", sMYpass);
mysql_options(mysqlInit, MYSQL_SET_CHARSET_NAME, "utf8");
MYSQL* mMysql;
mMysql = mysql_real_connect(mysqlInit, sMYhost, sMYuser, sMYpass, sMYdb, iMYport, NULL, 0);
if (mMysql)
{
printf("Connected to MySQL database at [%s] \n", sMYhost);
printf(" MySQL client library: [%s] \n", mysql_get_client_info());
printf(" MySQL server ver: [%s] \n\n", mysql_get_server_info(mMysql));
}
else
{
printf("Could not connect to MySQL database at [%s]:\n %s\n", sMYhost , mysql_error(mysqlInit));
mysql_close(mysqlInit);
return 1;
}
//////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////
MYSQL_RES* result = NULL;
MYSQL_ROW row;
MYSQL_FIELD* fields = NULL;
uint64 rowCount = 0;
uint32 fieldCount = 0;
result = mysql_list_tables(mMysql , NULL);
rowCount = mysql_num_rows(result);
/***********************/
/* get list of tables */
/***********************/
T_TableList mTableList;
mTableList.reserve((size_t)rowCount);
while ((row = mysql_fetch_row(result)) != NULL)
{
for (uint32 i = 0; i < mysql_num_fields(result); i++)
{
mTableList.push_back(row[i]);
}
}
mysql_free_result(result);
/****************************************/
/* convert filed type and default type */
/****************************************/
T_Table m_Table;
TDataBase m_DataBase_Map;
m_DataBase_Map.clear();
for (uint32 j = 0; j < mTableList.size(); ++j)
{
result = mysql_list_fields(mMysql, mTableList[j].c_str(), NULL);
fieldCount = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for (uint32 i = 0; i < fieldCount; ++i)
{
sField mfield;
mfield.name = fields[i].name;
if (!fields[i].def)
{
mfield.def = "NULL";
}
else if (!strcmp(fields[i].def, "0000-00-00 00:00:00"))
{
/// Convert MySQL Default timestamp to PGSQL Default timestamp
mfield.def.append("'1970-01-01 00:00:00'");
}
else
{
/// Append '
mfield.def.append("'");
mfield.def.append(fields[i].def);;
mfield.def.append("'");
}
mfield.type = ConvertNativeType(fields[i].type, fields[i].length);
mfield.flags = fields[i].flags;
m_Table.push_back(mfield);
}
m_DataBase_Map[mTableList[j]] = m_Table;
m_Table.clear();
mysql_free_result(result);
}
/******************************************/
/* Conversion of the layout of the tables */
/******************************************/
uint32 count = 0;
TDataBase::const_iterator citr;
for (citr = m_DataBase_Map.begin(); citr != m_DataBase_Map.end(); ++citr)
{
ostringstream sql_str;
sql_str << "DROP TABLE IF EXISTS " << (*citr).first.c_str() << ";\n";
sql_str << "CREATE TABLE " << (*citr).first.c_str() << "(\n";
T_Table::const_iterator v_iter;
ostringstream prim_key_str;
ostringstream index_str;
for (v_iter = (*citr).second.begin();
v_iter != (*citr).second.end();
++v_iter)
{
sql_str << " " << (*v_iter).name;
if (((*v_iter).flags & AUTO_INCREMENT_FLAG) != 0)
{
/// AUTO_INCREMENT fields not have "default" data
sql_str << " bigserial";
}
else
{
sql_str << " " << (*v_iter).type;
sql_str << " default " << (*v_iter).def;
}
/// IF column have PRIMARY KEY flag then use column in PRIMARY KEY
if (IS_PRI_KEY((*v_iter).flags) != 0)
{
if (prim_key_str.str().size())
prim_key_str << ", ";
else
{
prim_key_str << "ALTER TABLE ";
prim_key_str << (*citr).first.c_str();
prim_key_str << " ADD CONSTRAINT pk_";
prim_key_str << (*citr).first.c_str();
prim_key_str << "_";
prim_key_str << (*v_iter).name;
prim_key_str << " PRIMARY KEY (";
}
prim_key_str << (*v_iter).name;
}
else if (((*v_iter).flags & MULTIPLE_KEY_FLAG) != 0)
{
/// IF column have INDEX flag then create INDEX
index_str << "CREATE INDEX idx_";
index_str << (*citr).first.c_str();
index_str << "_";
index_str << (*v_iter).name;
index_str << " ON ";
index_str << (*citr).first.c_str();
index_str << " USING btree (";
index_str << (*v_iter).name;
index_str << ");\n";
}
else if (((*v_iter).flags & UNIQUE_KEY_FLAG) != 0)
{
/// IF column have UNIQUE INDEX flag then create INDEX
index_str << "CREATE UNIQUE INDEX uidx_";
index_str << (*citr).first.c_str();
index_str << "_";
index_str << (*v_iter).name;
index_str << " ON ";
index_str << (*citr).first.c_str();
index_str << " USING btree (";
index_str << (*v_iter).name;
index_str << ");\n";
}
/// don't output "," for last column
if (v_iter + 1 != (*citr).second.end())
sql_str << ",\n";
else
sql_str << "\n";
}
sql_str << ")\n";
/// Out Table structure
PG_Exec_str(sql_str.str(), mPGconn);
/// out PRIMARY KEY
if (prim_key_str.str().size())
{
prim_key_str << ")";
PG_Exec_str(prim_key_str.str(), mPGconn);
}
/// out INDEX's
if (index_str.str().size())
PG_Exec_str(index_str.str(), mPGconn);
++count;
printf("Convert [%d] tables...\r", count);
}
printf("Completed the conversion of [%d] tables!\n", count);
/****************/
/* Copying data */
/****************/
count = 0;
for (uint32 j = 0; j < mTableList.size(); ++j)
{
ostringstream sql_str;
sql_str << "SELECT * FROM ";
sql_str << mTableList[j].c_str();
if (mysql_query(mysqlInit, sql_str.str().c_str()))
continue;
if (!(result = mysql_store_result(mysqlInit)))
continue;
while ((row = mysql_fetch_row(result)) != NULL)
{
ostringstream insert_str;
insert_str << "INSERT INTO ";
insert_str << mTableList[j].c_str();
insert_str << " VALUES (";
fieldCount = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for (uint32 i = 0 ; i < fieldCount ; ++i)
{
if (!row[i])
insert_str << "NULL";
else
{
if (IsNeeedEscapeString(fields[i].type))
{
string field_str = row[i];
PG_Escape_Str(field_str);
insert_str << "E'";
insert_str << field_str.c_str();
insert_str << "'";
}
else if (!strcmp(row[i], "0000-00-00 00:00:00"))
{
/// Convert MySQL timestamp to PGSQL timestamp
insert_str << "'1970-01-01 00:00:00'";
}
else
{
insert_str << "'";
insert_str << row[i];
insert_str << "'";
}
}
/// don't output "," for last column
if (i + 1 != fieldCount)
insert_str << ",";
else
insert_str << ")\n";
}
PG_Exec_str(insert_str.str(), mPGconn);
}
mysql_free_result(result);
++count;
printf("Copied data from [%d] tables...\r", count);
}
printf("Finished copying the data from [%d] tables!\n", count);
mTableList.clear();
m_DataBase_Map.clear();
/// Close connections
mysql_close(mMysql);
PQfinish(mPGconn);
printf("end\n");
return 0;
}