EntityCopy.cpp

Go to the documentation of this file.

#include <dcl/Config.h>
 
#if __DCL_WINDOWS
#include <windows.h>
#endif
 
#include <dcl/DateTime.h>
 
#include "main.h"
#include "EntityCopy.h"
 
#define __TRACE_THIS    0
#if __TRACE_THIS
#define __DCL_TRACE0_N  __DCL_TRACE0
#define __DCL_TRACE1_N  __DCL_TRACE1
#define __DCL_TRACE2_N  __DCL_TRACE2
#define __DCL_TRACE3_N  __DCL_TRACE3
#define __DCL_TRACE4_N  __DCL_TRACE4
#else
#define __DCL_TRACE0_N(fmt, arg)
#define __DCL_TRACE1_N(fmt, arg)
#define __DCL_TRACE2_N(fmt, arg1, arg2)
#define __DCL_TRACE3_N(fmt, arg1, arg2, arg3)
#define __DCL_TRACE4_N(fmt, arg1, arg2, arg3, arg4)
#endif
 
__DCL_BEGIN_NAMESPACE
 
#if __DCL_DEBUG
#undef __THIS_FILE__
static const char_t __THIS_FILE__[] = __T("entitycopy/EntityCopy.cpp");
#endif
 
static DatabaseHelper __database_helper__[] = {
    {
        L"DCLFirebird",
        L"SELECT RDB$RELATION_NAME FROM RDB$RELATIONS"
            " WHERE RDB$SYSTEM_FLAG = 0 AND RDB$RELATION_TYPE = 0"
            " ORDER BY RDB$RELATION_NAME",
        L"DELETE FROM "
    },
    {
        L"DCLInterBase",
        L"SELECT RDB$RELATION_NAME FROM RDB$RELATIONS"
            " WHERE RDB$SYSTEM_FLAG = 0 AND RDB$RELATION_TYPE = 'PERSISTENT'"
            " ORDER BY RDB$RELATION_NAME",
        L"DELETE FROM "
    },
    {
        L"DCLInformix",
        L"SELECT TABNAME FROM SYSTABLES"
            " WHERE OWNER = CURRENT_USER AND TABTYPE = 'T'"
            " ORDER BY TABNAME",
        L"TRUNCATE TABLE "
    },
    {
        L"DCLMariaDB",
        L"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
            " WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = DATABASE()"
            " ORDER BY TABLE_NAME",
        L"TRUNCATE TABLE "
    },
    {
        L"DCLOracleDb",
        L"SELECT TABLE_NAME FROM USER_TABLES"
            " ORDER BY TABLE_NAME",
        L"TRUNCATE TABLE "
    },
    {
        L"DCLPostgreSQL",
        L"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
            " WHERE TABLE_TYPE = 'BASE TABLE'"
                " AND TABLE_SCHEMA NOT  In('pg_catalog', 'information_schema')"
            " ORDER by TABLE_NAME",
        L"TRUNCATE TABLE "
    },
    { NULL, NULL, NULL }
};
 
static const DatabaseHelper* __get_helper(const String& _driverName)
{
    const DatabaseHelper* r = __database_helper__;
    while (r->driverName) {
        if (_driverName == r->driverName) {
            break;
        }
        r++;
    }
    return r;
}
 
Entity::Entity(const Entity& _s)
{
    name = _s.name;
    columns = _s.columns;
    toName = _s.toName;
    toColumns = _s.toColumns;
}
 
Entity& Entity::operator = (const Entity& _s)
{
    name = _s.name;
    columns = _s.columns;
    toName = _s.toName;
    toColumns = _s.toColumns;
 
    return *this;
}
 
String Entity::toString()
{
    return String::format(L"name[%ls] columns[%ls] toName[%ls] toColumns[%ls",
        name.data(), columns.data(), toName.data(), toColumns.data()
        );
}
 
EntityCopy::EntityCopy(const MainArguments& _args,
    const String& _srcDriver, const String& _dstDriver)
    : __args(_args), __srcConn(_srcDriver), __dstConn(_dstDriver)
    __DCL_THROWS2(SQLDriverException*, SQLException*)
{
    __srcHelper = __get_helper(_srcDriver);
    __dstHelper = __get_helper(_dstDriver);
 
    __DCL_ASSERT(__srcHelper != NULL && __dstHelper != NULL);
    __DCL_TRACE2_N(L"helper[%ls][%ls]\n", __srcHelper->driverName, __dstHelper->driverName);
 
    __lineRuler.assign(L'-', 80);
    __lineIndent.assign(L' ', 4);
}
 
static void __get_name_columns(const String& _entity, String& _name, String& _columns)
{
    size_t index = _entity.indexOf(L'(');
    if (index != (size_t)-1) {
        _name = _entity.substring(0, index);
        _columns = _entity.substring(index).trim().trim(L"()").trim();
    }
    else {
        _name = _entity;
    }
}
 
static void __verbose_out(Writer& _output,
        const String& _sql, const String& _indent)
{
    const wchar_t* begin = _sql.data();
    const wchar_t* end = begin + _sql.length();
    const wchar_t* p = begin;
    while (p < end) {
        // 길이가 70 이상이고, ' ', ',' 이전까지의 문자열을 출력한다.
        p += 70;
        while (p < end) {
            if (*p == L' ' || *p == L',') {
                break;
            }
            p++;
        }
        if (p < end) {
            _output << _indent;
            if (begin > _sql.data()) {
                _output << _indent;
            }
            _output.write(begin, p - begin);
            _output << endl;
            if (*p == L' ') {
                p++;
            }
            begin = p;
        }
    }
    if (begin < end) {
        _output << _indent;
        if (begin > _sql.data()) {
            _output << _indent;
        }
        _output.write(begin, end - begin);
        _output << endl;
    }
}
 
bool EntityCopy::initialize()
    __DCL_THROWS1(SQLException*)
{
    try {
        __srcConn.open(__args.source());
        __args.output() << L"Source database connected. ["
            << __args.source() << L"]" << endl;
 
        __dstConn.open(__args.destination());
        __args.output() << L"Destination database connected. ["
            << __args.destination() << L"]" << endl;
    }
    catch (Exception* _e) {
        __args.output() << L"Database connection failed. ["
            << _e->toStringAll() << L"]" << endl;
        _e->destroy();
        return false;
    }
 
    if (!__args.entities().isEmpty()) {
        StringArray a;
        __args.entities().split(L';', a);
        for (size_t i = 0; i < a.size(); i++) {
            const String& s = a[i];
            Entity e;
            if (s.contains(L'=')) {
                StringArray pair;
                s.split(L'=', pair);
                __get_name_columns(pair[0], e.name, e.columns);
                __get_name_columns(pair[1], e.toName, e.toColumns);
            }
            else {
                __get_name_columns(s, e.name, e.columns);
            }
            __entities.add(e);
            __DCL_TRACE1_N(L"entity[%ls]\n", e.toString().data());
        }
    }
    else {
        Array<Entity>  srcEntities;
        Array<Entity>  dstEntities;
 
        String srcTableSQL = __srcHelper->selectTableList;
        String dstTableSQL = __dstHelper->selectTableList;
 
        if (__args.verbose()) {
            __args.output() << L"Source entities SQL" << endl;
            __verbose_out(__args.output(), srcTableSQL, __lineIndent);
            __args.output() << L"Destination entities SQL" << endl;
            __verbose_out(__args.output(), dstTableSQL, __lineIndent);
        }
 
        {
            __srcConn.startTrans();
            SQLQuery q(__srcConn);
            q.execute(srcTableSQL);
            q.fetch();
            while (!q.eof()) {
                Entity e;
                e.name = q.fields()[0].asString().trim();
                srcEntities.add(e);
                q.fetch();
            }
            __srcConn.commitTrans();
        }
 
        {
            __dstConn.startTrans();
            SQLQuery q(__dstConn);
            q.execute(dstTableSQL);
            q.fetch();
            while (!q.eof()) {
                Entity e;
                e.name = q.fields()[0].asString().trim();
                dstEntities.add(e);
                q.fetch();
            }
            __dstConn.commitTrans();
        }
 
        if (srcEntities.size() < dstEntities.size()) {
            __entities = srcEntities;
        }
        else {
            __entities = dstEntities;
        }
#if __TRACE_THIS
        StringBuilder sb = L"{";
        for (size_t i = 0; i < __entities.size(); i++) {
            if (i > 0) {
                sb.append(L", ");
            }
            sb.append(__entities[i].name);
        }
        sb.append(L"}");
        __DCL_TRACE2_N(L"__entities[%ls][%ls]\n",
            srcEntities.size() < dstEntities.size() ? L"src" : L"dst",
            sb.toString().data());
#endif
    }
    return __entities.size() > 0;
}
 
void EntityCopy::translateFieldValue(SQLField& _field, SQLParam& _param)
    __DCL_THROWS1(SQLException*)
{
    switch (_field.dataType()) {
        case SQL::typeInteger: {
            if (_field.dataSizeMax() <= sizeof(int32_t)) {
                int32_t value = 0;
                _field.getValue(value);
                _param.setValue(value);
            }
            else {
                int64_t value = 0;
                _field.getValue(value);
                _param.setValue(value);
            }
            break;
        }
        case SQL::typeUInteger: {
            if (_field.dataSizeMax() <= sizeof(uint32_t)) {
                uint32_t value = 0;
                _field.getValue(value);
                _param.setValue(value);
            }
            else {
                uint64_t value = 0;
                _field.getValue(value);
                _param.setValue(value);
            }
            break;
        }
        case SQL::typeFloat: {
            if (_field.dataSizeMax() == sizeof(float)) {
                float value = 0.;
                _field.getValue(value);
                _param.setValue(value);
            }
            else {
                double value = 0.;
                _field.getValue(value);
                _param.setValue(value);
            }
            break;
        }
        case SQL::typeNumeric: {
            if (_field.precision() > 0 && _field.scale() == 0) {
                // 12345678901234567890
                // 2147483647
                // 9223372036854775807
                if (_field.precision() <= __args.i4precision()) {
                    int32_t value = 0;
                    _field.getValue(value);
                    _param.setValue(value);
                    break;
                }
                else if (_field.precision() <= __args.i8precision()) {
                    int64_t value = 0;
                    _field.getValue(value);
                    _param.setValue(value);
                    break;
                }
                else {
                    // BigInteger
                }
            }
            ByteString value;
            _field.getValue(value, SQL::typeText);
            _param.setValue(value, SQL::typeText);
            break;
        }
        case SQL::typeDate: {
            SQL::Date value;
            _field.getValue(value);
            _param.setData(&value, sizeof(value),
                SQL::typeDate, SQL::typeDate);
            break;
        }
        case SQL::typeTime: {
            SQL::Time value;
            _field.getValue(value);
            _param.setData(&value, sizeof(value),
                SQL::typeTime, SQL::typeTime);
            break;
        }
        case SQL::typeTimeStamp:
        case SQL::typeTimeStampTz: {
            SQL::TimeStamp value;
            _field.getValue(value);
            _param.setData(&value, sizeof(value),
                _field.dataType(), SQL::typeTimeStamp);
            break;
        }
        case SQL::typeInterval:
        case SQL::typeIntervalYm:
        case SQL::typeIntervalDs: {
            SQL::Interval value;
            _field.getValue(value);
            _param.setData(&value, sizeof(value),
                _field.dataType(), SQL::typeInterval);
            break;
        }
        case SQL::typeText:
        case SQL::typeLongText:
        case SQL::typeClob: {
            ByteString value;
            _field.getValue(value, SQL::typeText);
            _param.setValue(value, SQL::typeText);
            break;
        }
        case SQL::typeBinary:
        case SQL::typeLongBinary:
        case SQL::typeBlob: {
            ByteString value;
            _field.getValue(value, SQL::typeBinary);
            _param.setValue(value, SQL::typeBinary);
            break;
        }
    }
}
 
void  EntityCopy::copyEntity(const Entity& _e)
    __DCL_THROWS1(SQLException*)
{
    DateTime  begin = DateTime::getCurrentLocalTime();
    __args.output() << __lineRuler << endl
        << _e.name << L" [" << begin.toString()
        << L"]" << endl;
 
    __srcConn.startTrans();
    String selectSQL = getSelectSQL(_e);
    SQLQuery select(__srcConn);
    select.execute(selectSQL);
 
    String insertSQL = getInsertSQL(_e, select.fields());
    String truncateSQL = getTruncateSQL(_e);
 
    if (__args.truncate()) {
        if (__args.verbose()) {
            __args.output() << __lineIndent << truncateSQL << endl;
        }
        if (!__args.dryrun()) {
            __dstConn.startTrans();
            __dstConn.execute(truncateSQL);
            __dstConn.commitTrans();
        }
    }
 
    if (__args.verbose()) {
        __verbose_out(__args.output(), selectSQL, __lineIndent);
        __verbose_out(__args.output(), insertSQL, __lineIndent);
    }
 
    __dstConn.startTrans();
 
    SQLQuery insert(__dstConn);
    insert.prepare(insertSQL);
 
    __DCL_ASSERT(select.fields().count() == insert.params().count());
 
    select.fetch();
    int rows = 0;
    while (!select.eof()) {
        rows++;
        
        for (size_t i = 0; i < select.fields().count(); i++) {
            SQLField& field = select.fields()[i];
            SQLParam& param = insert.params()[i];
            if (field.isNull()) {
                param.setNull();
            }
            else {
                try {
                    translateFieldValue(field, param);
                }
                catch (Exception* e) {
                    __DCL_TRACE4(L"%ls[%d][%ls] [%ls]\n", _e.name.data(), rows,
                        field.name().data(), e->toStringAll().data());
                    throw e;
                }
            }
        }
 
        if (!__args.dryrun()) {
            insert.execute();
            //__DCL_TRACE1_N(L"affectedRows[%zd]\n", insert.affectedRows());
            if (__args.rows() > 0 && (rows % __args.rows()) == 0) {
                __dstConn.commitTrans();
                __dstConn.startTrans();
                insert.prepare(insertSQL);
                if (__args.verbose()) {
                    __args.output() << __lineIndent << L"COMMIT " << rows << endl;
                }
            }
        }
        select.fetch();
    }
 
    __dstConn.commitTrans();
    __srcConn.commitTrans();
 
    DateTime end = DateTime::getCurrentLocalTime();
    __args.output() << _e.name 
        << L" [" << rows << L" rows transfered. " << (end - begin).toString()
        << L"]" << endl;
}
 
void EntityCopy::doIt()
{
    for (size_t i = 0; i < __entities.size(); i++) {
        copyEntity(__entities[i]);
    }
}
 
String EntityCopy::getSelectSQL(const Entity& _e)
{
    StringBuilder sb = L"SELECT ";
    sb.append(!_e.columns.isEmpty() ? _e.columns : L"*")
        .append(L" FROM ")
        .append(_e.name);
 
    return sb;
}
 
String EntityCopy::getInsertSQL(const Entity& _e, const SQLFields& _fields)
{
    StringBuilder sb = L"INSERT INTO ";
    sb.append(!_e.toName.isEmpty() ? _e.toName : _e.name)
        .append(L"(");
    if (!_e.toColumns.isEmpty()) {
        sb.append(_e.toColumns);
    }
    else if (!_e.columns.isEmpty()) {
        sb.append(_e.columns);
    }
    else {
        sb.append(_fields[0].name());
        for (size_t i = 1; i < _fields.count(); i++) {
            sb.append(L",").append(_fields[i].name());
        }
    }
    sb.append(L") VALUES (?");
 
    for (size_t i = 1; i < _fields.count(); i++) {
        sb.append(L",?");
    }
    sb.append(L")");
 
    return sb;
}
 
String EntityCopy::getTruncateSQL(const Entity& _e)
{
    StringBuilder sb = __dstHelper->truncateTable;
    sb.append(!_e.toName.isEmpty() ? _e.toName : _e.name);
    return sb;
}
 
__DCL_END_NAMESPACE