def test_deterministic_merge_into(sql_compiler):
    meta = MetaData()
    users = Table(
        'users', meta,
        Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
        Column('name', String), Column('fullname', String))
    onboarding_users = Table(
        'onboarding_users', meta,
        Column('id', Integer, Sequence('new_user_id_seq'), primary_key=True),
        Column('name', String), Column('fullname', String),
        Column('delete', Boolean))
    merge = MergeInto(users, onboarding_users,
                      users.c.id == onboarding_users.c.id)
    merge.when_matched_then_update().values(
        name=onboarding_users.c.name, fullname=onboarding_users.c.fullname)
    merge.when_not_matched_then_insert().values(
        id=onboarding_users.c.id,
        name=onboarding_users.c.name,
        fullname=onboarding_users.c.fullname,
    ).where(onboarding_users.c.fullname != None)
    assert sql_compiler(merge) == "MERGE INTO users USING onboarding_users ON users.id = onboarding_users.id " \
                                  "WHEN MATCHED THEN UPDATE SET fullname = onboarding_users.fullname, " \
                                  "name = onboarding_users.name WHEN NOT MATCHED AND onboarding_users.fullname " \
                                  "IS NOT NULL THEN INSERT (fullname, id, name) VALUES (onboarding_users.fullname, " \
                                  "onboarding_users.id, onboarding_users.name)"
def test_deterministic_merge_into(sql_compiler):
    meta = MetaData()
    users = Table('users', meta,
                  Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
                  Column('name', String),
                  Column('fullname', String))
    onboarding_users = Table('onboarding_users', meta,
                             Column('id', Integer, Sequence('new_user_id_seq'), primary_key=True),
                             Column('name', String),
                             Column('fullname', String),
                             Column('delete', Boolean))
    merge = MergeInto(users, onboarding_users, users.c.id == onboarding_users.c.id)
    merge.when_matched_then_update().values(name=onboarding_users.c.name,
                                            fullname=onboarding_users.c.fullname)
    merge.when_not_matched_then_insert().values(
        id=onboarding_users.c.id,
        name=onboarding_users.c.name,
        fullname=onboarding_users.c.fullname,
    ).where(onboarding_users.c.fullname != None)
    assert sql_compiler(merge) == "MERGE INTO users USING onboarding_users ON users.id = onboarding_users.id " \
                                  "WHEN MATCHED THEN UPDATE SET fullname = onboarding_users.fullname, " \
                                  "name = onboarding_users.name WHEN NOT MATCHED AND onboarding_users.fullname " \
                                  "IS NOT NULL THEN INSERT (fullname, id, name) VALUES (onboarding_users.fullname, " \
                                  "onboarding_users.id, onboarding_users.name)"
def test_upsert(engine_testaccount, update_flag, insert_flag, delete_flag,
                conditional_flag):
    meta = MetaData()
    users = Table(
        'users', meta,
        Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
        Column('name', String), Column('fullname', String))
    onboarding_users = Table(
        'onboarding_users', meta,
        Column('id', Integer, Sequence('new_user_id_seq'), primary_key=True),
        Column('name', String), Column('fullname', String),
        Column('delete', Boolean))
    meta.create_all(engine_testaccount)
    conn = engine_testaccount.connect()
    try:
        conn.execute(users.insert(), [{
            'id': 1,
            'name': 'mark',
            'fullname': 'Mark Keller'
        }, {
            'id': 4,
            'name': 'luke',
            'fullname': 'Luke Lorimer'
        }, {
            'id': 2,
            'name': 'amanda',
            'fullname': 'Amanda Harris'
        }])
        conn.execute(onboarding_users.insert(), [{
            'id': 2,
            'name': 'amanda',
            'fullname': 'Amanda Charlotte Harris',
            'delete': True
        }, {
            'id': 3,
            'name': 'jim',
            'fullname': 'Jim Wang',
            'delete': False
        }, {
            'id': 4,
            'name': 'lukas',
            'fullname': 'Lukas Lorimer',
            'delete': False
        }, {
            'id': 5,
            'name': 'andras',
            'fullname': None,
            'delete': False
        }])

        merge = MergeInto(users, onboarding_users,
                          users.c.id == onboarding_users.c.id)
        if update_flag:
            clause = merge.when_matched_then_update().values(
                name=onboarding_users.c.name,
                fullname=onboarding_users.c.fullname)
            if conditional_flag:
                clause.where(onboarding_users.c.name != 'amanda')
        if insert_flag:
            clause = merge.when_not_matched_then_insert().values(
                id=onboarding_users.c.id,
                name=onboarding_users.c.name,
                fullname=onboarding_users.c.fullname,
            )
            if conditional_flag:
                clause.where(onboarding_users.c.fullname != None)
        if delete_flag:
            clause = merge.when_matched_then_delete()
            if conditional_flag:
                clause.where(onboarding_users.c.delete == True)

        conn.execute(merge)
        users_tuples = {tuple(row) for row in conn.execute(select([users]))}
        onboarding_users_tuples = {
            tuple(row)
            for row in conn.execute(select([onboarding_users]))
        }
        expected_users = {(1, 'mark', 'Mark Keller'),
                          (2, 'amanda', 'Amanda Harris'),
                          (4, 'luke', 'Luke Lorimer')}
        if update_flag:
            if not conditional_flag:
                expected_users.remove((2, 'amanda', 'Amanda Harris'))
                expected_users.add((2, 'amanda', 'Amanda Charlotte Harris'))
            expected_users.remove((4, 'luke', 'Luke Lorimer'))
            expected_users.add((4, 'lukas', 'Lukas Lorimer'))
        elif delete_flag:
            if not conditional_flag:
                expected_users.remove((4, 'luke', 'Luke Lorimer'))
            expected_users.remove((2, 'amanda', 'Amanda Harris'))
        if insert_flag:
            if not conditional_flag:
                expected_users.add((5, 'andras', None))
            expected_users.add((3, 'jim', 'Jim Wang'))
        expected_onboarding_users = {(2, 'amanda', 'Amanda Charlotte Harris',
                                      True), (3, 'jim', 'Jim Wang', False),
                                     (4, 'lukas', 'Lukas Lorimer', False),
                                     (5, 'andras', None, False)}
        assert users_tuples == expected_users
        assert onboarding_users_tuples == expected_onboarding_users
    finally:
        conn.close()
        users.drop(engine_testaccount)
        onboarding_users.drop(engine_testaccount)
def test_upsert(engine_testaccount, update_flag, insert_flag, delete_flag, conditional_flag):
    meta = MetaData()
    users = Table('users', meta,
                  Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
                  Column('name', String),
                  Column('fullname', String))
    onboarding_users = Table('onboarding_users', meta,
                             Column('id', Integer, Sequence('new_user_id_seq'), primary_key=True),
                             Column('name', String),
                             Column('fullname', String),
                             Column('delete', Boolean))
    meta.create_all(engine_testaccount)
    conn = engine_testaccount.connect()
    try:
        conn.execute(users.insert(), [
            {'id': 1, 'name': 'mark', 'fullname': 'Mark Keller'},
            {'id': 4, 'name': 'luke', 'fullname': 'Luke Lorimer'},
            {'id': 2, 'name': 'amanda', 'fullname': 'Amanda Harris'}])
        conn.execute(onboarding_users.insert(), [
            {'id': 2, 'name': 'amanda', 'fullname': 'Amanda Charlotte Harris', 'delete': True},
            {'id': 3, 'name': 'jim', 'fullname': 'Jim Wang', 'delete': False},
            {'id': 4, 'name': 'lukas', 'fullname': 'Lukas Lorimer', 'delete': False},
            {'id': 5, 'name': 'andras', 'fullname': None, 'delete': False}
        ])

        merge = MergeInto(users, onboarding_users, users.c.id == onboarding_users.c.id)
        if update_flag:
            clause = merge.when_matched_then_update().values(name=onboarding_users.c.name,
                                                             fullname=onboarding_users.c.fullname)
            if conditional_flag:
                clause.where(onboarding_users.c.name != 'amanda')
        if insert_flag:
            clause = merge.when_not_matched_then_insert().values(
                id=onboarding_users.c.id,
                name=onboarding_users.c.name,
                fullname=onboarding_users.c.fullname,
            )
            if conditional_flag:
                clause.where(onboarding_users.c.fullname != None)
        if delete_flag:
            clause = merge.when_matched_then_delete()
            if conditional_flag:
                clause.where(onboarding_users.c.delete == True)

        conn.execute(merge)
        users_tuples = {tuple(row) for row in conn.execute(select([users]))}
        onboarding_users_tuples = {tuple(row) for row in conn.execute(select([onboarding_users]))}
        expected_users = {
            (1, 'mark', 'Mark Keller'),
            (2, 'amanda', 'Amanda Harris'),
            (4, 'luke', 'Luke Lorimer')
        }
        if update_flag:
            if not conditional_flag:
                expected_users.remove((2, 'amanda', 'Amanda Harris'))
                expected_users.add((2, 'amanda', 'Amanda Charlotte Harris'))
            expected_users.remove((4, 'luke', 'Luke Lorimer'))
            expected_users.add((4, 'lukas', 'Lukas Lorimer'))
        elif delete_flag:
            if not conditional_flag:
                expected_users.remove((4, 'luke', 'Luke Lorimer'))
            expected_users.remove((2, 'amanda', 'Amanda Harris'))
        if insert_flag:
            if not conditional_flag:
                expected_users.add((5, 'andras', None))
            expected_users.add((3, 'jim', 'Jim Wang'))
        expected_onboarding_users = {
            (2, 'amanda', 'Amanda Charlotte Harris', True),
            (3, 'jim', 'Jim Wang', False),
            (4, 'lukas', 'Lukas Lorimer', False),
            (5, 'andras', None, False)
        }
        assert users_tuples == expected_users
        assert onboarding_users_tuples == expected_onboarding_users
    finally:
        conn.close()
        users.drop(engine_testaccount)
        onboarding_users.drop(engine_testaccount)