TableCompareABC.cpp

/*----------------------------------------------------------------------
Copyright (c) Dan Petitt, http://www.coderanger.com
All Rights Reserved.
Please see the file "licence.txt" for licencing details.

File:	TableCompareABC.cpp
Owner:	danp@coderanger.com

Purpose:	Abstract class for database syncronisation.
					Override OnProcessTable to perform different algorithms for
					generating differences SQL scripts
----------------------------------------------------------------------*/
#include "stdafx.h"
#include "TableCompareABC.h"


static const std::string strPreStatements = _T("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n\n");
static const std::string strPostStatements = _T("\n\nSET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;\n");

CTableCompareABC::CTableCompareABC(void)
	: m_tStart( time(NULL) )

	, m_pFile( NULL )
	, m_bDisplayProgress( true )
	, m_bUseBulkInserts( true )

	, m_uTotalIdentical( 0 )
	, m_uTotalDifferent( 0 )
	, m_uTotalInserted( 0 )
	, m_uTotalDeleted( 0 )

	, m_uRecordsIdentical( 0 )
	, m_uRecordsDifferent( 0 )
	, m_uRecordsInserted( 0 )
	, m_uRecordsDeleted( 0 )
{
}


CTableCompareABC::~CTableCompareABC(void)
{
}


void CTableCompareABC::SetSourceDatabase( LPCTSTR pcszHost, LPCTSTR pcszUser, LPCTSTR pcszPassword, LPCTSTR pcszDatabase, UINT uPort /*= 3306*/ )
{
	m_mySource.Open( pcszHost, pcszUser, pcszPassword, uPort );
	m_mySource.SelectDatabase( pcszDatabase );
}


void CTableCompareABC::SetTargetDatabase( LPCTSTR pcszHost, LPCTSTR pcszUser, LPCTSTR pcszPassword, LPCTSTR pcszDatabase, UINT uPort /*= 3306*/ )
{
	m_myTarget.Open( pcszHost, pcszUser, pcszPassword, uPort );
	m_myTarget.SelectDatabase( pcszDatabase );
}



void CTableCompareABC::Start( LPCTSTR pcszExportFilePath, bool bDisplayProgress, bool bUseBulkInserts /*= true*/ )
{
	m_bDisplayProgress = bDisplayProgress;
	m_bUseBulkInserts = bUseBulkInserts;

	if( !m_mySource.IsConnected() || !m_myTarget.IsConnected() )
	{
		throw new XMySQL::CException( XMySQL::CException::CONNECTION_FAILURE, _T("There is no valid Source or Target database connection. Perhaps mySQL timed out or either SetSourceDatabase or SetTargetDatabase have not been called") );
	}

	const errno_t err = _tfopen_s( &m_pFile, pcszExportFilePath, _T("wb") );
	if( err != 0 )
	{
		LPTSTR pszError;
		(void)FormatMessage( FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM, NULL, GetLastError(), MAKELANGID( LANG_NEUTRAL, SUBLANG_DEFAULT ), (LPTSTR) &pszError, 0, NULL );

		std::stringstream errorStream;
		errorStream << _T("Unable to create export file at ") << pcszExportFilePath;
		errorStream << _T(" because: ") << pszError;

		LocalFree( pszError );

		throw new XMySQL::CException( XMySQL::CException::INITIALISATION_FAILURE, errorStream.str().c_str() );
	}


	std::stringstream strInfo;
	strInfo << "-- MySQL Script generated by Coderanger.com DBSync, Version 1.0.0";
	strInfo << "\n--   Source database: " << std::string( m_mySource.CurrentDatabase() );
	strInfo << "\n--   Target database: " << std::string( m_myTarget.CurrentDatabase() );
	strInfo << "\n-- Run this SQL file against the target database, but please back it up first\n\n\n";
	(void)fwrite( strInfo.str().c_str(), sizeof( char ), strInfo.str().size(), m_pFile );

	(void)fwrite( strPreStatements.c_str(), sizeof( char ), strPreStatements.size(), m_pFile );


	std::stringstream strUseDB;
	strUseDB << "USE " << std::string( m_myTarget.CurrentDatabase() ) << ";\n\n\nBEGIN;";
	(void)fwrite( strUseDB.str().c_str(), sizeof( char ), strUseDB.str().size(), m_pFile );

	fflush( m_pFile );


	std::vector< XMySQL::CConnection::Table > tables;
	m_mySource.GetTableInfo( tables );

	for( UINT u = 0; u < tables.size(); u++ )
	{
		const XMySQL::CConnection::Table &tbl = tables.at( u );
#ifdef _DEBUG
			if( tbl.strName != _T("programme") )
			{
// 				continue;
			}
#endif // _DEBUG

		ProcessTable( tbl );

		m_uTotalIdentical += GetIdenticalCount();
		m_uTotalDifferent += GetDifferentCount();
		m_uTotalDeleted += GetDeletedCount();
		m_uTotalInserted += GetInsertedCount();
	}


	const std::string strCommit( _T("\n\nCOMMIT;\n") );
	(void)fwrite( strCommit.c_str(), sizeof( char ), strCommit.size(), m_pFile );

	(void)fwrite( strPostStatements.c_str(), sizeof( char ), strPostStatements.size(), m_pFile );

	(void)fclose( m_pFile );


	if( m_uTotalDifferent == 0 && m_uTotalDeleted == 0 && m_uTotalInserted == 0 )
	{
		_tprintf( _T("\nAll done. Databases are in Sync\n") );
	}
	else
	{
		_tprintf( _T("\nAll done. Found %d indentical, %d to update, %d to delete, %d to insert"), m_uTotalIdentical, m_uTotalDifferent, m_uTotalDeleted, m_uTotalInserted );
		_tprintf( _T("\n(SQL File written to '%s')\n"), pcszExportFilePath );
	}

	const float fTook = (float)(time( NULL ) - m_tStart) / 60;
	_tprintf( _T("\nFinished (took %.2f minutes)\n"), fTook );
}



void CTableCompareABC::ProcessTable( const XMySQL::CConnection::Table &tbl )
{
	m_uRecordsIdentical = 0;
	m_uRecordsDifferent = 0;
	m_uRecordsInserted = 0;
	m_uRecordsDeleted = 0;

	m_arrDelete.clear();
	m_arrUpdate.clear();
	m_arrInsert.clear();


	_tprintf( _T("Analysing table '%s'\n"), tbl.strName.c_str() );
	time_t tStart = time( NULL );


	OnProcessTable( tbl );


	if( m_arrDelete.size() )
	{
		std::stringstream stream;
		stream << _T("\n\n--\n--  Deleting Data from Table: ") << tbl.strName << _T("\n--\n");
		(void)fwrite( stream.str().c_str(), sizeof( char ), stream.str().size(), m_pFile );
		WriteOutStatements( m_arrDelete );
	}

	if( m_arrUpdate.size() )
	{
		std::stringstream stream;
		stream << _T("\n\n--\n--  Updating Data on Table: ") << tbl.strName << _T("\n--\n");
		(void)fwrite( stream.str().c_str(), sizeof( char ), stream.str().size(), m_pFile );
		WriteOutStatements( m_arrUpdate );
	}

	if( m_arrInsert.size() )
	{
		std::stringstream stream;
		stream << _T("\n\n--\n--  Inserting new Data into Table: ") << tbl.strName << _T("\n--\n");
		(void)fwrite( stream.str().c_str(), sizeof( char ), stream.str().size(), m_pFile );
		WriteOutStatements( m_arrInsert );
	}


	fflush( m_pFile );


	const float fTook = (float)(time( NULL ) - tStart) / 60;
	_tprintf( _T("\r\n  Done (took %.2f min). Found: %d same; %d different; %d new; %d delete         \n\n"), fTook, m_uRecordsIdentical, m_uRecordsDifferent, m_uRecordsInserted, m_uRecordsDeleted );
}



void CTableCompareABC::WriteOutStatements( const std::vector< std::string > &arr )
{
	if( m_pFile )
	{
		for( size_t u = 0; u < arr.size(); u++ )
		{
			const std::string &str = arr.at( u );
			(void)fwrite( str.c_str(), sizeof( char ), str.size(), m_pFile );
		}
	}
}


bool CTableCompareABC::FieldIsQuoted( UINT uFieldType )
{
	if(
		   uFieldType == MYSQL_TYPE_STRING || uFieldType == MYSQL_TYPE_VAR_STRING || uFieldType == MYSQL_TYPE_VARCHAR || uFieldType == MYSQL_TYPE_GEOMETRY 
		|| uFieldType == MYSQL_TYPE_TINY_BLOB || uFieldType == MYSQL_TYPE_MEDIUM_BLOB || uFieldType == MYSQL_TYPE_LONG_BLOB || uFieldType == MYSQL_TYPE_BLOB 
		|| uFieldType == MYSQL_TYPE_TIMESTAMP || uFieldType == MYSQL_TYPE_DATE || uFieldType == MYSQL_TYPE_TIME || uFieldType == MYSQL_TYPE_DATETIME || uFieldType == MYSQL_TYPE_NEWDATE
		)
	{
		return true;
	}

	return false;
}