From 5bb13802a70196e0d4ae31a01f7185b11a6bd525 Mon Sep 17 00:00:00 2001 From: megamage Date: Mon, 20 Apr 2009 20:32:53 -0500 Subject: [7691] Added MySQL to PostgreSQL converter. Author: AlexDereka Tested on PostgreSQL 8.3 and MySQL 5.0. For compile see README. --HG-- branch : trunk --- contrib/mysql_to_pgsql/CMakeLists.txt | 21 ++ contrib/mysql_to_pgsql/README | 14 + contrib/mysql_to_pgsql/cmake/FindMySQL.cmake | 74 +++++ contrib/mysql_to_pgsql/cmake/FindPostgreSQL.cmake | 82 ++++++ contrib/mysql_to_pgsql/src/defines.h | 173 +++++++++++ contrib/mysql_to_pgsql/src/main.cpp | 339 ++++++++++++++++++++++ 6 files changed, 703 insertions(+) create mode 100644 contrib/mysql_to_pgsql/CMakeLists.txt create mode 100644 contrib/mysql_to_pgsql/README create mode 100644 contrib/mysql_to_pgsql/cmake/FindMySQL.cmake create mode 100644 contrib/mysql_to_pgsql/cmake/FindPostgreSQL.cmake create mode 100644 contrib/mysql_to_pgsql/src/defines.h create mode 100644 contrib/mysql_to_pgsql/src/main.cpp (limited to 'contrib/mysql_to_pgsql') diff --git a/contrib/mysql_to_pgsql/CMakeLists.txt b/contrib/mysql_to_pgsql/CMakeLists.txt new file mode 100644 index 00000000000..490a023e4ec --- /dev/null +++ b/contrib/mysql_to_pgsql/CMakeLists.txt @@ -0,0 +1,21 @@ +SET(CMAKE_VERBOSE_MAKEFILE ON) +cmake_minimum_required (VERSION 2.6) + +INCLUDE(cmake/FindMySQL.cmake) +INCLUDE(cmake/FindPostgreSQL.cmake) + +MESSAGE("-- Check PostgreSQL") +FIND_PGSQL() +ADD_DEFINITIONS(-DDO_POSTGRESQL) + +MESSAGE("-- Check MySQL") +FIND_MYSQL() +ADD_DEFINITIONS(-DDO_MYSQL) + +INCLUDE_DIRECTORIES(${MYSQL_INCLUDE_DIR}) +INCLUDE_DIRECTORIES(${PGSQL_INCLUDE_DIR}) + +ADD_EXECUTABLE (mysql2pgsql src/main.cpp) + +TARGET_LINK_LIBRARIES(mysql2pgsql ${PGSQL_LIBRARIES}) +TARGET_LINK_LIBRARIES(mysql2pgsql ${MYSQL_LIBRARIES}) diff --git a/contrib/mysql_to_pgsql/README b/contrib/mysql_to_pgsql/README new file mode 100644 index 00000000000..bf7083fb663 --- /dev/null +++ b/contrib/mysql_to_pgsql/README @@ -0,0 +1,14 @@ +Using cmake on a Windows +------------------ + 1. install cmake (http://www.cmake.org/cmake/resources/software.html) + 2. cmake -i + 3. Project.sln + 4. {Debug/Release}/mysql2pgsql.exe + +Using cmake on a Unix/Linux +------------------ + 1. install cmake + 2. cmake -i + 3. make + 4. ./mysql2pgsql + diff --git a/contrib/mysql_to_pgsql/cmake/FindMySQL.cmake b/contrib/mysql_to_pgsql/cmake/FindMySQL.cmake new file mode 100644 index 00000000000..e7d158de275 --- /dev/null +++ b/contrib/mysql_to_pgsql/cmake/FindMySQL.cmake @@ -0,0 +1,74 @@ +# - Find mysqlclient +# Find the native MySQL includes and library +# +# MYSQL_INCLUDE_DIR - where to find mysql.h, etc. +# MYSQL_LIBRARIES - List of libraries when using MySQL. +# MYSQL_FOUND - True if MySQL found. +MACRO(FIND_MYSQL) + +IF (MYSQL_INCLUDE_DIR) + # Already in cache, be silent + SET(MySQL_FIND_QUIETLY TRUE) +ENDIF (MYSQL_INCLUDE_DIR) + +FIND_PATH(MYSQL_INCLUDE_DIR mysql.h + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 6.0;Location]/include" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 5.1;Location]/include" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 5.0;Location]/include" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 4.1;Location]/include" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 4.0;Location]/include" + /usr/local/mysql/include + /usr/local/include/mysql + /usr/local/include + /usr/include/mysql + /usr/include + /usr/mysql/include +) + +IF(MSVC) + SET(MYSQL_NAMES libmysql) +ELSE(MSVC) + SET(MYSQL_NAMES mysqlclient mysqlclient_r) +ENDIF(MSVC) +SET(MYSQL_SEARCH_LIB_PATHS + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 6.0;Location]/lib/opt" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 5.1;Location]/lib/opt" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 5.0;Location]/lib/opt" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 4.1;Location]/lib/opt" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\MySQL AB\\MySQL Server 4.0;Location]/lib/opt" + /usr/local/mysql/lib + /usr/local/lib/mysql + /usr/local/lib + /usr/lib/mysql + /usr/lib +) +FIND_LIBRARY(MYSQL_LIBRARY + NAMES ${MYSQL_NAMES} + PATHS ${MYSQL_SEARCH_LIB_PATHS} +) + +IF (MYSQL_INCLUDE_DIR AND MYSQL_LIBRARY) + SET(MYSQL_FOUND TRUE) + SET( MYSQL_LIBRARIES ${MYSQL_LIBRARY} ) +ELSE (MYSQL_INCLUDE_DIR AND MYSQL_LIBRARY) + SET(MYSQL_FOUND FALSE) + SET( MYSQL_LIBRARIES ) +ENDIF (MYSQL_INCLUDE_DIR AND MYSQL_LIBRARY) + +IF (MYSQL_FOUND) + IF (NOT MySQL_FIND_QUIETLY) + MESSAGE(STATUS "Found MySQL: ${MYSQL_LIBRARY}") + ENDIF (NOT MySQL_FIND_QUIETLY) +ELSE (MYSQL_FOUND) + IF (MySQL_FIND_REQUIRED) + MESSAGE(STATUS "Looked for MySQL libraries named ${MYSQL_NAMES}.") + MESSAGE(FATAL_ERROR "Could NOT find MySQL library") + ENDIF (MySQL_FIND_REQUIRED) +ENDIF (MYSQL_FOUND) + +MARK_AS_ADVANCED( + MYSQL_LIBRARY + MYSQL_INCLUDE_DIR +) + +ENDMACRO(FIND_MYSQL) \ No newline at end of file diff --git a/contrib/mysql_to_pgsql/cmake/FindPostgreSQL.cmake b/contrib/mysql_to_pgsql/cmake/FindPostgreSQL.cmake new file mode 100644 index 00000000000..06a1adc9e7a --- /dev/null +++ b/contrib/mysql_to_pgsql/cmake/FindPostgreSQL.cmake @@ -0,0 +1,82 @@ +# - Find libpq +# Find the native PostgreSQL includes and library +# +# PGSQL_INCLUDE_DIR - where to find libpq-fe.h, etc. +# PGSQL_LIBRARIES - List of libraries when using PGSQL. +# PGSQL_FOUND - True if PGSQL found. + +MACRO(FIND_PGSQL) +IF (PGSQL_INCLUDE_DIR) + # Already in cache, be silent + SET(PostgreSQL_FIND_QUIETLY TRUE) +ENDIF (PGSQL_INCLUDE_DIR) + +# the registry settings checked are in order: +# - for pgInstaller 8.2.x postgresql version +# - for pgInstaller 8.3.x postgresql version +SET(PGSQL_WIN_BASE + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\PostgreSQL\\Installations\\{B823632F-3B72-4514-8861-B961CE263224};Base Directory]" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\PostgreSQL\\Installations\\{1F701DBD-1660-4108-B10A-FB435EA63BF0};Base Directory]") + +IF(PGSQL_WIN_BASE) + IF(MSVC) + SET(PGSQL_SEARCH_LIB_PATHS "${PGSQL_WIN_BASE}/lib/ms") + ELSE(MSVC) + SET(PGSQL_SEARCH_LIB_PATHS "${PGSQL_WIN_BASE}/lib") + ENDIF(MSVC) +ENDIF(PGSQL_WIN_BASE) + +FIND_PATH(PGSQL_INCLUDE_DIR libpq-fe.h + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\PostgreSQL\\Installations\\{1F701DBD-1660-4108-B10A-FB435EA63BF0};Base Directory]/include" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\PostgreSQL\\Installations\\{B823632F-3B72-4514-8861-B961CE263224};Base Directory]/include" + /usr/local/pgsql/include + /usr/local/postgresql/include + /usr/local/include/pgsql + /usr/local/include/postgresql + /usr/local/include + /usr/include/pgsql + /usr/include/postgresql + /usr/include + /usr/pgsql/include + /usr/postgresql/include +) + +SET(PGSQL_NAMES pq libpq) +SET(PGSQL_SEARCH_LIB_PATHS + ${PGSQL_SEARCH_LIB_PATHS} + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\PostgreSQL\\Installations\\{1F701DBD-1660-4108-B10A-FB435EA63BF0};Base Directory]/lib" + "[HKEY_LOCAL_MACHINE\\SOFTWARE\\PostgreSQL\\Installations\\{B823632F-3B72-4514-8861-B961CE263224};Base Directory]/lib" + /usr/local/pgsql/lib + /usr/local/lib + /usr/lib +) +FIND_LIBRARY(PGSQL_LIBRARY + NAMES ${PGSQL_NAMES} + PATHS ${PGSQL_SEARCH_LIB_PATHS} +) + +IF (PGSQL_INCLUDE_DIR AND PGSQL_LIBRARY) + SET(PGSQL_FOUND TRUE) + SET( PGSQL_LIBRARIES ${PGSQL_LIBRARY} ) +ELSE (PGSQL_INCLUDE_DIR AND PGSQL_LIBRARY) + SET(PGSQL_FOUND FALSE) + SET( PGSQL_LIBRARIES ) +ENDIF (PGSQL_INCLUDE_DIR AND PGSQL_LIBRARY) + +IF (PGSQL_FOUND) + IF (NOT PostgreSQL_FIND_QUIETLY) + MESSAGE(STATUS "Found PostgreSQL: ${PGSQL_LIBRARY}") + ENDIF (NOT PostgreSQL_FIND_QUIETLY) +ELSE (PGSQL_FOUND) + IF (PostgreSQL_FIND_REQUIRED) + MESSAGE(STATUS "Looked for PostgreSQL libraries named ${PGSQL_NAMES}.") + MESSAGE(FATAL_ERROR "Could NOT find PostgreSQL library") + ENDIF (PostgreSQL_FIND_REQUIRED) +ENDIF (PGSQL_FOUND) + +MARK_AS_ADVANCED( + PGSQL_LIBRARY + PGSQL_INCLUDE_DIR +) +ENDMACRO(FIND_PGSQL) + diff --git a/contrib/mysql_to_pgsql/src/defines.h b/contrib/mysql_to_pgsql/src/defines.h new file mode 100644 index 00000000000..662a7aab2fd --- /dev/null +++ b/contrib/mysql_to_pgsql/src/defines.h @@ -0,0 +1,173 @@ +/* + * Copyright (C) 2005-2009 MaNGOS + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program; if not, write to the Free Software + * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + */ + +#ifndef _DEFINES_ +#define _DEFINES_ + +#ifdef WIN32 +#include +#pragma warning(disable:4996) +#endif + +#include +#include +#include +#include +#include +#include +#include +#include +#include +using namespace std; + + +#ifdef WIN32 +typedef unsigned __int64 uint64; +typedef unsigned int uint32; +#else +#include +#ifndef uint64_t +#include +#endif +typedef uint64_t uint64; +typedef unsigned int uint32; +#endif + +struct sField +{ + string name; // field name + string def; // field default data + string type; // field type + uint32 flags; // filed flags, see field flags; +}; +typedef vector T_Table; +typedef vector T_TableList; +typedef map< string, T_Table > TDataBase; + +static +void pg_notice(void *arg, const char *message) +{ + /// Do nothing + //printf("%s\n", message); +} + +inline +string ConvertNativeType(enum_field_types mysqlType, uint32 length) +{ + + switch (mysqlType) + { + case FIELD_TYPE_TIMESTAMP: + return "timestamp"; + case FIELD_TYPE_BIT: + return "bit(1)"; + case FIELD_TYPE_DATETIME: + return "date"; + case FIELD_TYPE_YEAR: + case FIELD_TYPE_BLOB: + case FIELD_TYPE_SET: + case FIELD_TYPE_NULL: + case FIELD_TYPE_ENUM: + return "text"; + case FIELD_TYPE_TINY: + case FIELD_TYPE_SHORT: + case FIELD_TYPE_INT24: + return "integer"; + case FIELD_TYPE_LONGLONG: + return "int8"; + case FIELD_TYPE_LONG: + return "bigint"; + case FIELD_TYPE_DECIMAL: + case FIELD_TYPE_FLOAT: + case FIELD_TYPE_DOUBLE: + return "float"; + case FIELD_TYPE_STRING: + { + string temp; + char str[10]; + temp = "char"; + if (length) + { + temp.append("("); + sprintf(str,"%d",length); + temp.append(str); + temp.append(")"); + } + return temp; + } + case FIELD_TYPE_VAR_STRING: + { + string temp; + char str[10]; + temp = "varchar"; + if (length) + { + temp.append("("); + sprintf(str,"%d",length); + temp.append(str); + temp.append(")"); + } + return temp; + } + default: + return "text"; + } + return "text"; +} + +inline +bool IsNeeedEscapeString(enum_field_types mysqlType) +{ + switch(mysqlType) + { + case FIELD_TYPE_VAR_STRING: + case FIELD_TYPE_STRING: + case FIELD_TYPE_TINY_BLOB: + case FIELD_TYPE_MEDIUM_BLOB: + case FIELD_TYPE_LONG_BLOB: + case FIELD_TYPE_BLOB: + return true; + default: + return false; + } + return false; +} + +inline +void PG_Exec_str(string sql, PGconn *mPGconn) +{ + PGresult *res = PQexec (mPGconn, sql.c_str()); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + { + printf( "SQL: %s", sql.c_str() ); + printf( "SQL %s", PQerrorMessage(mPGconn) ); + } +} + +void PG_Escape_Str(string& str) +{ + if(str.empty()) + return; + char* buf = new char[str.size()*2+1]; + PQescapeString(buf,str.c_str(),str.size()); + str = buf; + delete[] buf; +} + +#endif + diff --git a/contrib/mysql_to_pgsql/src/main.cpp b/contrib/mysql_to_pgsql/src/main.cpp new file mode 100644 index 00000000000..8ca6772de20 --- /dev/null +++ b/contrib/mysql_to_pgsql/src/main.cpp @@ -0,0 +1,339 @@ +/* + * Copyright (C) 2005-2009 MaNGOS + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program; if not, write to the Free Software + * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + */ + +#include "defines.h" + +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