def test_simple_join(self):
sql = """
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
CUSTOMER,
ORDERS,
LINEITEM,
SUPPLIER,
NATION,
REGION
where
o_custkey=c_custkey
and l_orderkey = o_orderkey
and s_suppkey= l_suppkey
and r_name = 'MIDDLE EAST'
and o_orderdate >= date '1994-01-01'
and o_orderdate < date '1994-01-01' + interval '1' year
group by
n_name
order by
revenue desc;
"""
tables = [FreeConnexTable.load_from_json(t) for t in TEST_CONFIG]
parser = FreeConnexParser(tables=tables,
sql=sql,
annotation_name="demo")
parser.parse()
root = parser.root_table
self.assertTrue(parser.is_free_connex())
def setUp(self) -> None:
self.query = """
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
CUSTOMER,
ORDERS,
LINEITEM
where
c_mktsegment = 'AUTOMOBILE'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '1995-03-13'
and l_shipdate > date '1995-03-13'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit
10;
"""
self.tables = [FreeConnexTable.load_from_json(t) for t in TEST_CONFIG]
self.codegen = FreeConnexParser(tables=self.tables,
sql=self.query,
annotation_name="demo")
def setUp(self) -> None:
self.table_1 = FreeConnexTable(table_name="1",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="b",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_2 = FreeConnexTable(table_name="2",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="c",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_3 = FreeConnexTable(table_name="3",
columns=[
Column(name="b",
column_type=TypeEnum.int),
Column(name="d",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_4 = FreeConnexTable(table_name="4",
columns=[
Column(name="d",
column_type=TypeEnum.int),
Column(name="f",
column_type=TypeEnum.int),
Column(name="g",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_5 = FreeConnexTable(table_name="5",
columns=[
Column(name="b",
column_type=TypeEnum.int),
Column(name="e",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
def test_is_cycle(self):
table1 = FreeConnexTable(table_name="1",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="b",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
table2 = FreeConnexTable(table_name="2",
columns=[
Column(name="b",
column_type=TypeEnum.int),
Column(name="c",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
table3 = FreeConnexTable(table_name="3",
columns=[
Column(name="c",
column_type=TypeEnum.int),
Column(name="a",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
table1.join(table2, "b", "b")
table2.join(table3, "c", "c")
self.assertRaises(Exception, table3.join, table1, "a", "a")
def generate_code_by_db():
"""
Perform a code generation by using db execution plan
:return:
"""
try:
data: dict = request.json
tables = [FreeConnexTable.load_from_json(t) for t in json.loads(data['table'])]
sql = data['sql']
password = getenv('password')
user = getenv('user')
database = data.get("database", None) if data.get("database", None) else getenv("database")
host = getenv("host")
port = getenv("port")
annotation_name = data['annotation_name']
driver = PostgresDBDriver(password=password,
user=user,
database_name=database,
host=host,
port=port,
tables=tables)
parser = CodeGenDB(db_driver=driver, sql=sql, tables=tables, annotation_name=annotation_name)
if "plan" in data and data["plan"] != "":
plan = PostgresDBPlan.from_json(data["plan"], tables=tables)
parser.parse(query_plan=plan)
else:
parser.parse()
output = parser.to_output(function_name=data.get("functionName"))
graph = parser.root_table.to_json_graph(
output_attrs=parser.get_output_attributes()) if parser.root_table else {}
is_free_connex, error_tables = parser.is_free_connex()
error_tables = [e.variable_table_name for e in error_tables]
return jsonify(
{"code": output, "joinGraph": graph, "isFreeConnex": is_free_connex, "errorTables": error_tables})
except Exception as e:
traceback.print_exc()
return Response(str(e), status=500)
def generate_code():
"""
Use default code gen
:return:
"""
try:
data = request.json
tables = [FreeConnexTable.load_from_json(t) for t in json.loads(data['table'])]
sql = data['sql']
annotation_name = data['annotation_name']
parser = FreeConnexParser(sql=sql, tables=tables, annotation_name=annotation_name)
output = parser.parse().to_output(data.get("functionName"))
graph = parser.root_table.to_json_graph(
output_attrs=parser.get_output_attributes())
is_free_connex, error_tables = parser.is_free_connex()
error_tables = [e.variable_table_name for e in error_tables]
return jsonify(
{"code": output, "joinGraph": graph, "isFreeConnex": is_free_connex, "errorTables": error_tables})
except Exception as e:
return Response(str(e), status=500)
# ORDERS_TABLE = Table(table_name="ORDERS",
# columns=[
# Column(name="o_custkey", column_type=TypeEnum.int),
# Column(name="o_orderkey", column_type=TypeEnum.int),
# Column(name="o_shippriority", column_type=TypeEnum.int)
# ])
# LINEITEM_TABLE = Table(table_name="LINEITEM",
# columns=[
# Column(name="l_orderkey", column_type=TypeEnum.int)
# ])
#
# SUPPLIER_TABLE = Table(table_name="SUPPLIER",
# columns=[
# Column(name="s_suppkey", column_type=TypeEnum.int),
# Column(name="s_nationkey", column_type=TypeEnum.int)
# ])
#
# NATION_TABLE = Table(table_name="NATION",
# columns=[
# Column(name="s_suppkey", column_type=TypeEnum.int),
# Column(name="s_nationkey", column_type=TypeEnum.int)
# ])
with open("examples/table_config.json", 'r') as f:
tables = [FreeConnexTable.load_from_json(t) for t in json.load(f)]
parser = Parser(sql=sql3, tables=tables)
parser.parse()
o = parser.root_table.to_json(parser.get_output_attributes())
print(o)
parser.to_file("examples/test.cpp")
def setUp(self) -> None:
self.tables = [FreeConnexTable.load_from_json(t) for t in TEST_CONFIG]
def test_is_free_connex_join4(self):
"""
See exaample/join_tree.drawio tree C
:return:
"""
self.table_1 = FreeConnexTable(table_name="1",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="b1",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_2 = FreeConnexTable(table_name="2",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="c",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_3 = FreeConnexTable(table_name="3",
columns=[
Column(name="b2",
column_type=TypeEnum.int),
Column(name="d1",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_4 = FreeConnexTable(table_name="4",
columns=[
Column(name="d2",
column_type=TypeEnum.int),
Column(name="f",
column_type=TypeEnum.int),
Column(name="g",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_5 = FreeConnexTable(table_name="5",
columns=[
Column(name="b3",
column_type=TypeEnum.int),
Column(name="e",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_1.join(self.table_2, "a", "a")
self.table_1.join(self.table_3, "b1", "b2")
self.table_3.join(self.table_4, "d1", "d2")
self.table_3.join(self.table_5, "b2", "b3")
output_attrs = ["b1", "d1", "e", "f"]
non_output_attrs = ["a", "c", "g"]
height_of_tree = self.table_1.get_height()
is_free_connex, output_tables = self.table_1.is_free_connex(
output_attrs=output_attrs,
non_output_attrs=non_output_attrs,
height=height_of_tree)
self.assertFalse(is_free_connex)
self.assertEqual(output_tables[0], self.table_3)
self.assertFalse(self.table_1.is_cycle())
class FreeConnexJoin(unittest.TestCase):
def setUp(self) -> None:
self.table_1 = FreeConnexTable(table_name="1",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="b",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_2 = FreeConnexTable(table_name="2",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="c",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_3 = FreeConnexTable(table_name="3",
columns=[
Column(name="b",
column_type=TypeEnum.int),
Column(name="d",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_4 = FreeConnexTable(table_name="4",
columns=[
Column(name="d",
column_type=TypeEnum.int),
Column(name="f",
column_type=TypeEnum.int),
Column(name="g",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_5 = FreeConnexTable(table_name="5",
columns=[
Column(name="b",
column_type=TypeEnum.int),
Column(name="e",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
def test_is_free_connex_join(self):
"""
See exaample/join_tree.drawio tree A
:return:
"""
self.table_1.join(self.table_2, "a", "a")
self.table_1.join(self.table_3, "b", "b")
self.table_3.join(self.table_4, "d", "d")
self.table_3.join(self.table_5, "b", "b")
output_attrs = ["b", "d", "e", "f"]
non_output_attrs = ["a", "c", "g"]
height_of_tree = self.table_1.get_height()
table, height = self.table_1.get_highest_with_attr("a", height_of_tree)
self.assertEqual(table, self.table_1)
self.assertEqual(height, 2)
table, height = self.table_1.get_highest_with_attr("d", height_of_tree)
self.assertEqual(table, self.table_3)
self.assertEqual(height, 1)
table, height = self.table_1.get_highest_with_attr("c", height_of_tree)
self.assertEqual(table, self.table_2)
self.assertEqual(height, 1)
is_free_connex, output_tables = self.table_1.is_free_connex(
output_attrs=output_attrs,
non_output_attrs=non_output_attrs,
height=height_of_tree)
self.assertFalse(is_free_connex)
self.assertEqual(output_tables[0], self.table_3)
def test_is_free_connex_join2(self):
"""
See exaample/join_tree.drawio tree B
:return:
"""
self.table_5.join(self.table_3, "b", "b")
self.table_3.join(self.table_1, "b", "b")
self.table_1.join(self.table_2, "a", "a")
self.table_3.join(self.table_4, "d", "d")
output_attrs = ["b", "d", "e", "f"]
non_output_attrs = ["a", "c", "g"]
height_of_tree = self.table_5.get_height()
table, height = self.table_5.get_highest_with_attr("a", height_of_tree)
self.assertEqual(table, self.table_1)
self.assertEqual(height, 1)
table, height = self.table_5.get_highest_with_attr("d", height_of_tree)
self.assertEqual(table, self.table_3)
self.assertEqual(height, 2)
table, height = self.table_5.get_highest_with_attr("c", height_of_tree)
self.assertEqual(table, self.table_2)
self.assertEqual(height, 0)
is_free_connex, _ = self.table_5.is_free_connex(
output_attrs=output_attrs,
non_output_attrs=non_output_attrs,
height=height_of_tree)
self.assertTrue(is_free_connex)
def test_is_free_connex_join3(self):
"""
See exaample/join_tree.drawio tree A
:return:
"""
self.table_1.join(self.table_2, "a", "a")
self.table_1.join(self.table_3, "b", "b")
self.table_3.join(self.table_4, "d", "d")
self.table_3.join(self.table_5, "b", "b")
output_attrs = ["b", "d", "e", "f"]
non_output_attrs = ["a", "c", "g", "sum"]
height_of_tree = self.table_1.get_height()
is_free_connex, output_tables = self.table_1.is_free_connex(
output_attrs=output_attrs,
non_output_attrs=non_output_attrs,
height=height_of_tree)
self.assertFalse(is_free_connex)
self.assertEqual(output_tables[0], self.table_3)
def test_is_free_connex_join4(self):
"""
See exaample/join_tree.drawio tree C
:return:
"""
self.table_1 = FreeConnexTable(table_name="1",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="b1",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_2 = FreeConnexTable(table_name="2",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="c",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_3 = FreeConnexTable(table_name="3",
columns=[
Column(name="b2",
column_type=TypeEnum.int),
Column(name="d1",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_4 = FreeConnexTable(table_name="4",
columns=[
Column(name="d2",
column_type=TypeEnum.int),
Column(name="f",
column_type=TypeEnum.int),
Column(name="g",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_5 = FreeConnexTable(table_name="5",
columns=[
Column(name="b3",
column_type=TypeEnum.int),
Column(name="e",
column_type=TypeEnum.int)
],
data_sizes=[100],
data_paths=[""],
annotations=[])
self.table_1.join(self.table_2, "a", "a")
self.table_1.join(self.table_3, "b1", "b2")
self.table_3.join(self.table_4, "d1", "d2")
self.table_3.join(self.table_5, "b2", "b3")
output_attrs = ["b1", "d1", "e", "f"]
non_output_attrs = ["a", "c", "g"]
height_of_tree = self.table_1.get_height()
is_free_connex, output_tables = self.table_1.is_free_connex(
output_attrs=output_attrs,
non_output_attrs=non_output_attrs,
height=height_of_tree)
self.assertFalse(is_free_connex)
self.assertEqual(output_tables[0], self.table_3)
self.assertFalse(self.table_1.is_cycle())
def test_swap(self):
self.table_1.join(self.table_2, "a", "a")
self.table_1.join(self.table_3, "b", "b")
self.table_3.join(self.table_4, "d", "d")
self.table_3.join(self.table_5, "b", "b")
# self.table_3.swap()
# self.assertEqual(self.table_5.parent, None)
# self.assertEqual(self.table_5.children, [self.table_3])
def test_is_cycle(self):
table1 = FreeConnexTable(table_name="1",
columns=[
Column(name="a",
column_type=TypeEnum.int),
Column(name="b",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
table2 = FreeConnexTable(table_name="2",
columns=[
Column(name="b",
column_type=TypeEnum.int),
Column(name="c",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
table3 = FreeConnexTable(table_name="3",
columns=[
Column(name="c",
column_type=TypeEnum.int),
Column(name="a",
column_type=TypeEnum.int),
],
data_sizes=[100],
data_paths=[""],
annotations=[])
table1.join(table2, "b", "b")
table2.join(table3, "c", "c")
self.assertRaises(Exception, table3.join, table1, "a", "a")