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")