Ejemplo n.º 1
0
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));
    }
}
Ejemplo n.º 2
0
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));
}
Ejemplo n.º 3
0
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 )));
}
Ejemplo n.º 4
0
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));
}
Ejemplo n.º 5
0
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));
	}
}
Ejemplo n.º 7
0
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));
    }
}
Ejemplo n.º 8
0
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;

}