EntityCopy.cpp

Go to the documentation of this file.

#include <dcl/Config.h>
 
#ifdef __WINNT__
#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_HAVE_THIS_FILE__
#undef __THIS_FILE__
static const char_t __THIS_FILE__[] = __T("entitycopy/EntityCopy.cpp");
#endif
 
static DatabaseHelper __database_helper__[] = {
    {
        L"Firebird",    // LI-V6.3.2.1613 Firebird 5.0
        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"InterBase",   // InterBase LI-V6.3.2.1613
        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"Informix",    // IBM Informix Dynamic Server
        L"SELECT TABNAME FROM SYSTABLES"
            " WHERE OWNER = CURRENT_USER AND TABTYPE = 'T'"
            " ORDER BY TABNAME",
        L"TRUNCATE TABLE "
    },
    {
        L"MariaDB",     // 11.8.2-MariaDB-ubu2404
        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"Microsoft",   // Microsoft SQL Server 17.00.1000
        L"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
            " WHERE TABLE_TYPE = 'BASE TABLE'"
                " AND TABLE_SCHEMA = 'dbo'"
            " ORDER BY TABLE_NAME",
        L"TRUNCATE TABLE "
    },
    {
        L"Oracle",      // [Oracle Database 21c Enterprise
        L"SELECT TABLE_NAME FROM USER_TABLES"
            " ORDER BY TABLE_NAME",
        L"TRUNCATE TABLE "
    },
    {
        L"PostgreSQL",  // PostgreSQL 17.5 on x86_64
        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 "
    },
    {
        L"SQLite",  // SQLite 3.51.1
        L"SELECT NAME FROM SQLITE_SCHEMA"
            " WHERE TYPE = 'table'"
            " ORDER BY NAME",
        L"DELETE FROM "
    },
    { NULL, NULL, NULL }
};
 
static const DatabaseHelper* __get_helper(const String& _serverInfo)
{
    const DatabaseHelper* r = __database_helper__;
    while (r->identifier) {
        String serverInfo = _serverInfo.toLowerCase();
        String identifier = String(r->identifier).toLowerCase();
        if (serverInfo.indexOf(identifier) != (size_t)-1) {
            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
) __DCL_THROWS2(SQLDriverException*, SQLException*)
    : __args(_args), __srcConn(_srcDriver), __dstConn(_dstDriver)
{
    __srcHelper = __dstHelper = NULL;
 
    __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* _entity) {
        __args.output() << L"Database connection failed. ["
            << _entity->toStringAll() << L"]" << endl;
        _entity->destroy();
        return false;
    }
 
    {
        String serverInfo = __srcConn.getServerInfo();
        __srcHelper = __get_helper(serverInfo);
        if (!__srcHelper) {
            __args.output() << L"Unknown Database Vendor. ["
                << serverInfo << L"] Failed." << endl;
            return false;
        }
 
        serverInfo = __dstConn.getServerInfo();
        __dstHelper = __get_helper(serverInfo);
        if (!__dstHelper) {
            __args.output() << L"Unknown Database Vendor. ["
                << serverInfo << L"] Failed." << endl;
            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:
        case SQL::typeTimeTz: {
            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;
        }
        default: {
            __DCL_ASSERT(false);
        }
    }
}
 
void  EntityCopy::copyEntity(const Entity& _entity)
    __DCL_THROWS1(SQLException*)
{
    DateTime  begin = DateTime::getCurrentLocalTime();
    __args.output() << __lineRuler << endl
        << _entity.name << L" [" << begin.toString()
        << L"]" << endl;
 
    int rows = 0;
    __srcConn.startTrans();
    {
        String selectSQL = getSelectSQL(_entity);
        SQLQuery select(__srcConn);
        select.execute(selectSQL);
 
        String insertSQL = getInsertSQL(_entity, select.fields());
        String truncateSQL = getTruncateSQL(_entity);
 
        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);
        }
 
        select.fetch();
        while (!select.eof()) {
            __dstConn.startTrans();
            {
                SQLQuery insert(__dstConn);
                insert.prepare(insertSQL);
                __DCL_ASSERT(select.fields().count() == insert.params().count());
 
                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* _cause) {
                                __DCL_TRACE4(L"%ls[%d][%ls] [%ls]\n",
                                    _entity.name.data(), rows, field.name().data(),
                                    _cause->toStringAll().data()
                                );
                                throw _cause;
                            }
                        }
                    }
 
                    if (!__args.dryrun()) {
                        insert.execute();
                        //__DCL_TRACE1_N(L"affectedRows[%zd]\n", insert.affectedRows());
                    }
                    select.fetch();
                    if (__args.rows() > 0 && (rows % __args.rows()) == 0) {
                        break;
                    }
                }
            }
            __dstConn.commitTrans();
            if (__args.verbose()) {
                __args.output() << __lineIndent << L"COMMIT " << rows << endl;
            }
        }
    }
    __srcConn.commitTrans();
 
    DateTime end = DateTime::getCurrentLocalTime();
    __args.output() << _entity.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& _entity)
{
    StringBuilder sb = L"SELECT ";
    sb.append(!_entity.columns.isEmpty() ? _entity.columns : L"*")
        .append(L" FROM ")
        .append(_entity.name);
 
    return sb;
}
 
String EntityCopy::getInsertSQL(const Entity& _entity, const SQLFields& _fields)
{
    StringBuilder sb = L"INSERT INTO ";
    sb.append(!_entity.toName.isEmpty() ? _entity.toName : _entity.name)
        .append(L"(");
    if (!_entity.toColumns.isEmpty()) {
        sb.append(_entity.toColumns);
    }
    else if (!_entity.columns.isEmpty()) {
        sb.append(_entity.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& _entity)
{
    StringBuilder sb = __dstHelper->truncateTable;
    sb.append(!_entity.toName.isEmpty() ? _entity.toName : _entity.name);
    return sb;
}
 
__DCL_END_NAMESPACE