<reponame>Agile-Data/flat-ql<gh_stars>1-10
import os

from flatql import parse_from_hocon_path
from flatql.parser.flatql_parser import parse_flatql
from flatql.rewriter.sql_rewriter import SqlRewriter

github_schema = parse_from_hocon_path(f"{os.path.dirname(__file__)}/schemas/github")


def test_select1():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0"'


def test_select3():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.name = 'abc'").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0" WHERE "ta_0"."name" = \'abc\''


def test_select4():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.age >= 18").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0" WHERE "ta_0"."age" >= 18'


def test_select5():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.name IN ('a', 'b')").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."name" IN (\'a\', \'b\')'


def test_select6():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.name NOT IN ('a', 'b')").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."name" NOT IN (\'a\', \'b\')'


def test_select7():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.age BETWEEN 18 AND 42").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."age" BETWEEN 18 AND 42'


def test_select8():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql(
        "SELECT User.login, User.name, User.location FROM github WHERE User.age NOT BETWEEN 18 AND 42").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."age" NOT BETWEEN 18 AND 42'


def test_select9():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github "
                 "WHERE User.name IN ('a', 'b') AND User.age NOT BETWEEN 18 AND 42").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."name" IN (\'a\', \'b\') AND "ta_0"."age" NOT BETWEEN 18 AND 42'


def test_select10():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name AS \"UserName\", User.location FROM github ORDER BY \"UserName\"").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name" AS \"UserName\", "ta_0"."location" ' \
                                    'FROM "users" AS "ta_0" ORDER BY \"UserName\" ASC'


def test_select12():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql(
        "SELECT User.login, User.name AS \"UserName\", User.location FROM github WHERE User.age NOT BETWEEN 18 AND 42 ORDER BY \"UserName\"").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name" AS \"UserName\", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."age" NOT BETWEEN 18 AND 42 ORDER BY \"UserName\" ASC'


def test_select13():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github LIMIT 1").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0" LIMIT 1'


def test_select14():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github LIMIT 1 OFFSET 10").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0" LIMIT 1 OFFSET 10'


def test_select16():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login AS Login, User.name AS \"Name\" FROM github").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login" AS "Login", "ta_0"."name" AS "Name" FROM "users" AS "ta_0"'


def test_select20():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.location, SUM(User.age) AS UserCount FROM github WHERE User.name LIKE '%demo'").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."location", SUM("ta_0"."age") AS "UserCount" FROM "users" AS "ta_0" ' \
                                    'WHERE "ta_0"."name" LIKE \'%demo\' GROUP BY "ta_0"."location"'


def test_select21():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, Activity.action FROM github").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_1"."action" FROM "activities" AS "ta_1" ' \
                                    'INNER JOIN "users" AS "ta_0" ON "ta_0"."id" = "ta_1"."user_id"'


def test_select22():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, Activity.action FROM github WHERE User.name LIKE '%abc'").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_1"."action" FROM "activities" AS "ta_1" ' \
                                    'INNER JOIN "users" AS "ta_0" ON "ta_0"."id" = "ta_1"."user_id" ' \
                                    'WHERE "ta_0"."name" LIKE \'%abc\''


def test_select23():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, Activity.action FROM github "
                 "WHERE User.name LIKE '%abc'").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_1"."action" FROM "activities" AS "ta_1" ' \
                                    'INNER JOIN "users" AS "ta_0" ON "ta_0"."id" = "ta_1"."user_id" ' \
                                    'WHERE "ta_0"."name" LIKE \'%abc\''


def test_select24():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.name, Repository.name FROM github "
                 "WHERE User.name LIKE '%abc'").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."name", "ta_1"."name" FROM "activities" AS "ta_2" INNER JOIN "repositories" AS "ta_1" ON "ta_1"."id" = "ta_2"."repository_id" INNER JOIN "users" AS "ta_0" ON "ta_0"."id" = "ta_2"."user_id" WHERE "ta_0"."name" LIKE \'%abc\''

import os

from flatql import parse_from_hocon_path
from flatql.parser.flatql_parser import parse_flatql
from flatql.rewriter.sql_rewriter import SqlRewriter

github_schema = parse_from_hocon_path(f"{os.path.dirname(__file__)}/schemas/github")


def test_select1():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0"'


def test_select3():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.name = 'abc'").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0" WHERE "ta_0"."name" = \'abc\''


def test_select4():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.age >= 18").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0" WHERE "ta_0"."age" >= 18'


def test_select5():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.name IN ('a', 'b')").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."name" IN (\'a\', \'b\')'


def test_select6():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.name NOT IN ('a', 'b')").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."name" NOT IN (\'a\', \'b\')'


def test_select7():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github WHERE User.age BETWEEN 18 AND 42").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."age" BETWEEN 18 AND 42'


def test_select8():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql(
        "SELECT User.login, User.name, User.location FROM github WHERE User.age NOT BETWEEN 18 AND 42").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."age" NOT BETWEEN 18 AND 42'


def test_select9():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github "
                 "WHERE User.name IN ('a', 'b') AND User.age NOT BETWEEN 18 AND 42").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."name" IN (\'a\', \'b\') AND "ta_0"."age" NOT BETWEEN 18 AND 42'


def test_select10():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name AS \"UserName\", User.location FROM github ORDER BY \"UserName\"").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name" AS \"UserName\", "ta_0"."location" ' \
                                    'FROM "users" AS "ta_0" ORDER BY \"UserName\" ASC'


def test_select12():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql(
        "SELECT User.login, User.name AS \"UserName\", User.location FROM github WHERE User.age NOT BETWEEN 18 AND 42 ORDER BY \"UserName\"").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name" AS \"UserName\", "ta_0"."location" FROM "users" ' \
                                    'AS "ta_0" WHERE "ta_0"."age" NOT BETWEEN 18 AND 42 ORDER BY \"UserName\" ASC'


def test_select13():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github LIMIT 1").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0" LIMIT 1'


def test_select14():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, User.name, User.location FROM github LIMIT 1 OFFSET 10").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_0"."name", "ta_0"."location" FROM "users" AS "ta_0" LIMIT 1 OFFSET 10'


def test_select16():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login AS Login, User.name AS \"Name\" FROM github").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login" AS "Login", "ta_0"."name" AS "Name" FROM "users" AS "ta_0"'


def test_select20():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.location, SUM(User.age) AS UserCount FROM github WHERE User.name LIKE '%demo'").rewrite(
        sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."location", SUM("ta_0"."age") AS "UserCount" FROM "users" AS "ta_0" ' \
                                    'WHERE "ta_0"."name" LIKE \'%demo\' GROUP BY "ta_0"."location"'


def test_select21():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, Activity.action FROM github").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_1"."action" FROM "activities" AS "ta_1" ' \
                                    'INNER JOIN "users" AS "ta_0" ON "ta_0"."id" = "ta_1"."user_id"'


def test_select22():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, Activity.action FROM github WHERE User.name LIKE '%abc'").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_1"."action" FROM "activities" AS "ta_1" ' \
                                    'INNER JOIN "users" AS "ta_0" ON "ta_0"."id" = "ta_1"."user_id" ' \
                                    'WHERE "ta_0"."name" LIKE \'%abc\''


def test_select23():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.login, Activity.action FROM github "
                 "WHERE User.name LIKE '%abc'").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."login", "ta_1"."action" FROM "activities" AS "ta_1" ' \
                                    'INNER JOIN "users" AS "ta_0" ON "ta_0"."id" = "ta_1"."user_id" ' \
                                    'WHERE "ta_0"."name" LIKE \'%abc\''


def test_select24():
    sql_rewriter = SqlRewriter(github_schema)
    parse_flatql("SELECT User.name, Repository.name FROM github "
                 "WHERE User.name LIKE '%abc'").rewrite(sql_rewriter)
    assert sql_rewriter.to_sql() == 'SELECT "ta_0"."name", "ta_1"."name" FROM "activities" AS "ta_2" INNER JOIN "repositories" AS "ta_1" ON "ta_1"."id" = "ta_2"."repository_id" INNER JOIN "users" AS "ta_0" ON "ta_0"."id" = "ta_2"."user_id" WHERE "ta_0"."name" LIKE \'%abc\''