def _cross_join(a_table: Table, b_table: Table, r_table: Table):
a_table_rows, b_table_rows = a_table.get_rows(), b_table.get_rows()
for a_table_row in a_table_rows:
for b_table_row in b_table_rows:
r_row = a_table_row.copy()
r_row.extend(b_table_row)
r_table.add_row_list(r_row)
return r_table
def join_table(a_table: Table, b_table: Table, join_type, condition: Expr = None):
a_table_columns = a_table.full_columns()
b_table_columns = b_table.full_columns()
r_table_columns = a_table_columns.copy()
r_table_columns.extend(b_table_columns)
r_table = Table("", columns=r_table_columns)
if join_type == JoinType.inner_join or join_type == JoinType.cross_join or join_type == JoinType.straight_join:
return cross_join(a_table, b_table, r_table, condition)
return outer_join(a_table, b_table, r_table, condition, join_type)
def execute(self, a_table: Table):
rows = a_table.get_rows(copy=False)
columns_len = len(a_table.columns)
grouped_rows = _group_rows(rows, 0, columns_len - 1)
# 选择每个组的第一条
new_rows = []
for rows in grouped_rows:
new_rows.append(rows[0])
a_table.clear()
a_table.set_rows_list(new_rows)
return a_table
def _a_right_join_b_on_condition(a_table: Table, b_table: Table, r_table: Table, names, condition: Expr):
a_table_rows, b_table_rows = a_table.get_rows(), b_table.get_rows()
a_table_rows_map_list, b_table_rows_map_list = a_table.get_rows_map_list(names), b_table.get_rows_map_list(names)
for b_table_row, b_table_row_map in zip(b_table_rows, b_table_rows_map_list):
find = False
for a_table_row, a_table_row_map in zip(a_table_rows, a_table_rows_map_list):
values = a_table_row_map.copy()
values.update(b_table_row_map)
if not condition.execute(values):
continue
new_row = a_table_row.copy()
new_row.extend(b_table_row)
r_table.add_row_list(new_row)
if not find:
new_row = b_table_row.copy()
r_table.add_row_list(new_row, header=True)
return r_table
def execute(self, a_table: Table):
if a_table.empty():
return a_table
all_rows = a_table.get_rows(copy=False)
rows_expr_res, asc_infos = self._get_rows_expr_res_and_asc_info(a_table)
rows_expr_res = self._sort(rows_expr_res, asc_infos)
new_rows = self._get_rows_by_order(rows_expr_res, all_rows)
a_table.clear()
a_table.set_rows_list(new_rows)
return a_table
def _cross_join_on_condition(a_table: Table, b_table: Table, r_table: Table, names, condition: Expr):
a_table_rows, b_table_rows = a_table.get_rows(), b_table.get_rows()
a_table_rows_map_list, b_table_rows_map_list = a_table.get_rows_map_list(names), b_table.get_rows_map_list(names)
for a_table_row, a_table_row_map in zip(a_table_rows, a_table_rows_map_list):
for b_table_row, b_table_row_map in zip(b_table_rows, b_table_rows_map_list):
values = a_table_row_map.copy()
values.update(b_table_row_map)
if not condition.execute(values):
continue
new_row = a_table_row.copy()
new_row.extend(b_table_row)
r_table.add_row_list(new_row)
return r_table
def execute(self, a_table: Table):
if a_table.empty():
return a_table
all_rows = a_table.get_rows(copy=False)
rows_expr_res, asc_infos = self._get_rows_expr_res_and_asc_info(a_table)
rows_expr_res = self._sort(rows_expr_res, asc_infos)
grouped_orders = _group_rows(rows_expr_res, 0, len(self._order_items) - 1)
grouped_rows = self._get_grouped_rows_by_order(grouped_orders, all_rows)
a_table.clear()
a_table.set_rows_list(grouped_rows)
return a_table
def condition_filter(a_table: Table, r_table: Table, condition: Expr):
names = []
var_count, new_condition = condition.compile(names)
if var_count == 0:
value = new_condition.execute()
if value:
return r_table.set_rows_list(a_table.get_rows(copy=False))
return r_table
a_table_rows, a_table_rows_map_list = a_table.get_rows(copy=False), a_table.get_rows_map_list(names)
for a_table_row, a_table_row_map in zip(a_table_rows, a_table_rows_map_list):
if new_condition.execute(a_table_row_map):
r_table.add_row_list(a_table_row, copy=False)
return r_table
def _get_rows_expr_res_and_asc_info(self, a_table: Table):
all_names = set()
com_items = []
for order_item in self._order_items:
var_count, names, expr_, asc = order_item.execute()
all_names.update(set(names))
com_items.append((var_count, names, expr_, asc))
all_rows_map = a_table.get_rows_map_list(all_names)
rows_expr_res = []
for index, row_value_map in enumerate(all_rows_map):
row_expr_res = []
for _, names, expr_, _ in com_items:
row_expr_res.append(expr_.execute(row_value_map))
row_expr_res.append(index)
rows_expr_res.append(row_expr_res)
asc_infos = []
for _, _, _, asc in com_items:
asc_infos.append(asc)
return rows_expr_res, asc_infos
def execute(self, r_table: Table):
new_table = Table(table_name=r_table.table_name, columns=r_table.columns)
table_rows = r_table.get_rows(self._limit, self._offset)
for table_row in table_rows:
new_table.add_row_list(table_row, copy=False)
return new_table
def execute(self, a_table: Table):
if self._expr is None:
return a_table
r_table = Table(a_table.table_name, columns=a_table.columns)
return condition_filter(a_table, r_table, self._expr)
def _a_right_join_b_false(b_table: Table, r_table: Table):
b_table_rows = b_table.get_rows()
for b_table_row in b_table_rows:
r_table.add_row_list(b_table_row, header=True)
return r_table
def _a_left_join_b_false(a_table: Table, r_table: Table):
a_table_rows = a_table.get_rows()
for a_table_row in a_table_rows:
r_table.add_row_list(a_table_row)
return r_table
pass
def execute(self):
pass
class Stmt(Node):
def __init__(self, verbose, *args, **kwargs):
Node.__init__(self, verbose, *args, **kwargs)
def execute(self, *args, **kwargs):
pass
if __name__ == "__main__":
class_table = Table("class", columns=["id", "class_name", "class_number", "grade"])
student_table = Table("student", columns=["id", "name", "mentor_id", "class_id"])
class_table.add_row_list([1, "1班", 1, 1])
class_table.add_row_list([2, "2班", 1, 1])
class_table.add_row_list([3, "3班", 2, 1])
class_table.add_row_list([4, "4班", 2, 2])
class_table.add_row_list([5, "5班", 2, 1])
class_table.add_row_list([6, "6班", 1, 2])
class_table.add_row_list([7, "7班", 2, 2])
class_table.add_row_list([8, "8班", 2, 3])
class_table.add_row_list([8, "8班", 2, 3])
student_table.add_row_list([1, "王小明", 1, 2])
student_table.add_row_list([2, '张大鹏', 2, 5])
student_table.add_row_list([3, "胡志宁", 3, 3])