Skip to content

The open alternative to Salesforce, designed for AI.

20.0 KB
Raw Blame
import { isDefined } from 'twenty-shared/utils';
import { type ObjectLiteral } from 'typeorm';

import { RelationType } from 'src/engine/metadata-modules/field-metadata/interfaces/relation-type.interface';

import { escapeIdentifier } from 'src/engine/workspace-manager/workspace-migration/utils/remove-sql-injection.util';
import { buildColumnResultAlias } from 'src/engine/twenty-orm/sql/utils/build-column-result-alias.util';
import {
  TwentyOrmException,
  TwentyOrmExceptionCode,
} from 'src/engine/twenty-orm/exceptions/twenty-orm.exception';
import { type FindOptionsLike } from 'src/engine/twenty-orm/query-builder/types/query-builder.type';
import { type WorkspaceTableShape } from 'src/engine/twenty-orm/table-shape/types/workspace-table-shape.type';

export type WhereClause = {
  operator: 'and' | 'or';
  sql: string;
};

export type JoinClause = {
  alias: string;
  parentAlias: string;
  relationFieldName: string;
  targetTableShape: WorkspaceTableShape;
  relationType: RelationType;
  joinType: 'INNER' | 'LEFT';
  isSelected?: boolean;
  condition?: string;
  toManyForeignKeyColumnName?: string;
  toManyPlainCondition?: string;
  toManyDedupOrder?: ToManyDedupOrder[];
  additionalOnConditions: string[];
};

export type ToManyDedupOrder = {
  columnName: string;
  direction: 'ASC' | 'DESC';
  nulls?: 'NULLS FIRST' | 'NULLS LAST';
};

export type ExistsFilterClause = {
  token: string;
  alias: string;
  parentAlias: string;
  relationFieldName: string;
  targetTableShape: WorkspaceTableShape;
  correlationCondition: string;
  conditionSql: string;
  additionalOnConditions: string[];
};

export type ColumnSelection = {
  alias: string;
  columnName: string;
};

export type SelectClause = {
  expression: string;
  alias: string;
};

export type OrderByClause = {
  expression: string;
  direction: 'ASC' | 'DESC';
  nulls?: 'NULLS FIRST' | 'NULLS LAST';
};

export type SelectStatementState = {
  alias: string;
  tableShape: WorkspaceTableShape;
  findOptions: FindOptionsLike;
  explicitSelection?: string[];
  extraSelectClauses: SelectClause[];
  columnSelections: ColumnSelection[];
  joinClauses: JoinClause[];
  whereClauses: WhereClause[];
  rowAccessConditions: string[];
  existsFilterClauses: ExistsFilterClause[];
  groupByExpressions: string[];
  orderByClauses: OrderByClause[];
  distinctOnExpressions: string[];
  includeDeleted: boolean;
  allowPlainToManyJoins: boolean;
  limitValue?: number;
  offsetValue?: number;
};

export const quoteColumn = (alias: string, columnName: string): string =>
  `${escapeIdentifier(alias)}.${escapeIdentifier(columnName)}`;

export const normaliseColumnExpression = (
  expression: string,
  defaultAlias: string,
): string => {
  if (expression.includes('"') || expression.includes('(')) {
    return expression;
  }

  const parts = expression.split('.');

  if (parts.length === 2) {
    return quoteColumn(parts[0], parts[1]);
  }

  return quoteColumn(defaultAlias, expression);
};

const SQL_TEXT_SEGMENTS = /('(?:[^']|'')*'|"[^"]*")/;

const quoteQualifiedAliasReferencesInSegment = (
  segment: string,
  aliases: string[],
): string =>
  aliases.reduce(
    (quotedSegment, alias) =>
      quotedSegment.replace(
        new RegExp(
          `(?<![\\w".:])${alias.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}\\.(\\w+)`,
          'g',
        ),
        (_, columnName) => quoteColumn(alias, columnName),
      ),
    segment,
  );

export const quoteQualifiedAliasReferences = (
  expression: string,
  aliases: string[],
): string =>
  expression
    .split(SQL_TEXT_SEGMENTS)
    .map((segment, segmentIndex) =>
      segmentIndex % 2 === 1
        ? segment
        : quoteQualifiedAliasReferencesInSegment(segment, aliases),
    )
    .join('');

export const collectStatementAliases = (
  state: SelectStatementState,
): string[] => [
  state.alias,
  ...state.joinClauses.map((joinClause) => joinClause.alias),
];

const buildJoinPropertyPath = (
  state: SelectStatementState,
  joinAlias: string,
): string => {
  const propertySegments: string[] = [];
  let currentAlias = joinAlias;

  while (currentAlias !== state.alias) {
    const joinClause = state.joinClauses.find(
      (candidate) => candidate.alias === currentAlias,
    );

    if (!isDefined(joinClause)) {
      throw new TwentyOrmException(
        `Alias "${currentAlias}" does not belong to this statement`,
        TwentyOrmExceptionCode.UNKNOWN_RELATION,
      );
    }

    propertySegments.unshift(joinClause.relationFieldName);
    currentAlias = joinClause.parentAlias;
  }

  return propertySegments.join('.');
};

export type JoinedColumnProjection = {
  joinAlias: string;
  columnName: string;
  resultAlias: string;
  propertyPath: string;
};

export const collectJoinedColumnProjections = (
  state: SelectStatementState,
): JoinedColumnProjection[] => {
  const projections: JoinedColumnProjection[] = [];
  const seenResultAliases = new Set<string>();

  const addProjection = (joinAlias: string, columnName: string) => {
    const resultAlias = buildColumnResultAlias(joinAlias, columnName);

    if (seenResultAliases.has(resultAlias)) {
      return;
    }

    seenResultAliases.add(resultAlias);
    projections.push({
      joinAlias,
      columnName,
      resultAlias,
      propertyPath: buildJoinPropertyPath(state, joinAlias),
    });
  };

  for (const joinClause of state.joinClauses) {
    if (joinClause.isSelected !== true) {
      continue;
    }

    for (const columnName of joinClause.targetTableShape.columnNames) {
      addProjection(joinClause.alias, columnName);
    }
  }

  for (const columnSelection of state.columnSelections) {
    if (columnSelection.alias === state.alias) {
      continue;
    }

    addProjection(columnSelection.alias, columnSelection.columnName);
  }

  return projections;
};

export const buildProjection = (
  state: SelectStatementState,
): { expressions: string[]; mainAliasColumnNames: string[] } => {
  const selectedColumnNames = Object.entries(state.findOptions.select ?? {})
    .filter(([, isSelected]) => isSelected)
    .map(([columnName]) => columnName);

  for (const columnName of selectedColumnNames) {
    if (!isDefined(state.tableShape.columnShapeByColumnName[columnName])) {
      throw new TwentyOrmException(
        `Column "${columnName}" does not exist on "${state.tableShape.nameSingular}"`,
        TwentyOrmExceptionCode.UNKNOWN_COLUMN,
      );
    }
  }

  const mainAliasColumnNames =
    state.explicitSelection ??
    (selectedColumnNames.length > 0
      ? selectedColumnNames
      : state.tableShape.columnNames);

  const expressions = mainAliasColumnNames.map(
    (columnName) =>
      `${quoteColumn(state.alias, columnName)} AS ${escapeIdentifier(
        buildColumnResultAlias(state.alias, columnName),
      )}`,
  );

  const projectedMainColumnNames = new Set(mainAliasColumnNames);

  for (const columnSelection of state.columnSelections) {
    if (
      columnSelection.alias !== state.alias ||
      projectedMainColumnNames.has(columnSelection.columnName)
    ) {
      continue;
    }

    projectedMainColumnNames.add(columnSelection.columnName);
    expressions.push(
      `${quoteColumn(state.alias, columnSelection.columnName)} AS ${escapeIdentifier(
        buildColumnResultAlias(state.alias, columnSelection.columnName),
      )}`,
    );
  }

  for (const joinedProjection of collectJoinedColumnProjections(state)) {
    expressions.push(
      `${quoteColumn(
        joinedProjection.joinAlias,
        joinedProjection.columnName,
      )} AS ${escapeIdentifier(joinedProjection.resultAlias)}`,
    );
  }

  const aliases = collectStatementAliases(state);

  for (const extraSelect of state.extraSelectClauses) {
    expressions.push(
      `${quoteQualifiedAliasReferences(
        extraSelect.expression,
        aliases,
      )} AS ${escapeIdentifier(extraSelect.alias)}`,
    );
  }

  return { expressions, mainAliasColumnNames };
};

const renderUserWhereExpression = (whereClauses: WhereClause[]): string =>
  whereClauses
    .map((clause, index) =>
      index === 0
        ? clause.sql
        : `${clause.operator.toUpperCase()} ${clause.sql}`,
    )
    .join(' ');

// Row access conditions stay outside the user expression so an OR in it can
// never bypass them
export const renderWhereExpressionWithRowAccess = ({
  whereClauses,
  rowAccessConditions,
}: {
  whereClauses: WhereClause[];
  rowAccessConditions: string[];
}): string => {
  const userExpression = renderUserWhereExpression(whereClauses);

  if (rowAccessConditions.length === 0) {
    return userExpression;
  }

  const rowAccessExpression = rowAccessConditions
    .map((rowAccessCondition) => `(${rowAccessCondition})`)
    .join(' AND ');

  return userExpression.length > 0
    ? `(${userExpression}) AND ${rowAccessExpression}`
    : rowAccessExpression;
};

const renderExistsFilter = (
  existsFilterClause: ExistsFilterClause,
  includeDeleted: boolean,
): string => {
  const conditions = [
    existsFilterClause.correlationCondition,
    ...existsFilterClause.additionalOnConditions,
  ];

  if (existsFilterClause.conditionSql.length > 0) {
    conditions.push(existsFilterClause.conditionSql);
  }

  if (
    !includeDeleted &&
    existsFilterClause.targetTableShape.hasDeletedAtColumn
  ) {
    conditions.push(
      `${quoteColumn(existsFilterClause.alias, 'deletedAt')} IS NULL`,
    );
  }

  const tableExpression = `${escapeIdentifier(
    existsFilterClause.targetTableShape.schemaName,
  )}.${escapeIdentifier(existsFilterClause.targetTableShape.tableName)}`;

  return `EXISTS (SELECT 1 FROM ${tableExpression} AS ${escapeIdentifier(
    existsFilterClause.alias,
  )} WHERE ${conditions.join(' AND ')})`;
};

export const substituteExistsFilterTokens = ({
  expression,
  existsFilterClauses,
  includeDeleted,
}: {
  expression: string;
  existsFilterClauses: ExistsFilterClause[];
  includeDeleted: boolean;
}): string =>
  existsFilterClauses.reduce(
    (substituted, existsFilterClause) =>
      substituted
        .split(existsFilterClause.token)
        .join(renderExistsFilter(existsFilterClause, includeDeleted)),
    expression,
  );

export const buildWhereExpression = (
  state: SelectStatementState,
  {
    includeSoftDeletePredicate = true,
    substituteExistsFilters = true,
  }: {
    includeSoftDeletePredicate?: boolean;
    substituteExistsFilters?: boolean;
  } = {},
): string => {
  const renderedWhereExpression = quoteQualifiedAliasReferences(
    renderWhereExpressionWithRowAccess({
      whereClauses: state.whereClauses,
      rowAccessConditions: state.rowAccessConditions,
    }),
    collectStatementAliases(state),
  );

  const filterExpression = substituteExistsFilters
    ? substituteExistsFilterTokens({
        expression: renderedWhereExpression,
        existsFilterClauses: state.existsFilterClauses,
        includeDeleted: state.includeDeleted,
      })
    : renderedWhereExpression;

  const shouldAddSoftDeletePredicate =
    includeSoftDeletePredicate &&
    !state.includeDeleted &&
    state.tableShape.hasDeletedAtColumn;

  if (!shouldAddSoftDeletePredicate) {
    return filterExpression;
  }

  const softDeletePredicate = `${quoteColumn(state.alias, 'deletedAt')} IS NULL`;

  if (filterExpression.length === 0) {
    return softDeletePredicate;
  }

  return `(${filterExpression}) AND ${softDeletePredicate}`;
};

export const buildFromClause = (state: SelectStatementState): string =>
  `FROM ${escapeIdentifier(state.tableShape.schemaName)}.${escapeIdentifier(
    state.tableShape.tableName,
  )} AS ${escapeIdentifier(state.alias)}`;

const buildToManyDedupedJoinSource = ({
  tableExpression,
  foreignKeyColumnName,
  includeSoftDeleteFilter,
  dedupOrder = [],
}: {
  tableExpression: string;
  foreignKeyColumnName: string;
  includeSoftDeleteFilter: boolean;
  dedupOrder?: ToManyDedupOrder[];
}): string => {
  const foreignKey = escapeIdentifier(foreignKeyColumnName);
  const whereClause = includeSoftDeleteFilter
    ? ` WHERE ${escapeIdentifier('deletedAt')} IS NULL`
    : '';

  const orderExpressions = [
    foreignKey,
    ...dedupOrder.map(
      (order) =>
        `${escapeIdentifier(order.columnName)} ${order.direction}${
          isDefined(order.nulls) ? ` ${order.nulls}` : ''
        }`,
    ),
    escapeIdentifier('id'),
  ];

  return `(SELECT DISTINCT ON (${foreignKey}) * FROM ${tableExpression}${whereClause} ORDER BY ${orderExpressions.join(', ')})`;
};

export const buildJoinClause = (state: SelectStatementState): string =>
  state.joinClauses
    .map((joinClause) => {
      const condition =
        joinClause.condition ??
        (state.allowPlainToManyJoins
          ? joinClause.toManyPlainCondition
          : undefined);

      if (!isDefined(condition)) {
        throw new TwentyOrmException(
          `Only to-one relations can be joined; "${joinClause.alias}" is to-many and must be loaded as a separate query`,
          TwentyOrmExceptionCode.UNSUPPORTED_OPERATION,
        );
      }

      const softDeletePredicateApplies =
        !state.includeDeleted && joinClause.targetTableShape.hasDeletedAtColumn;

      const toManyForeignKeyColumnName =
        joinClause.relationType === RelationType.ONE_TO_MANY
          ? joinClause.toManyForeignKeyColumnName
          : undefined;

      const onConditions = [condition, ...joinClause.additionalOnConditions];

      if (
        softDeletePredicateApplies &&
        !isDefined(toManyForeignKeyColumnName)
      ) {
        onConditions.push(
          `${quoteColumn(joinClause.alias, 'deletedAt')} IS NULL`,
        );
      }

      const tableExpression = `${escapeIdentifier(
        joinClause.targetTableShape.schemaName,
      )}.${escapeIdentifier(joinClause.targetTableShape.tableName)}`;

      const joinSource = isDefined(toManyForeignKeyColumnName)
        ? buildToManyDedupedJoinSource({
            tableExpression,
            foreignKeyColumnName: toManyForeignKeyColumnName,
            includeSoftDeleteFilter: softDeletePredicateApplies,
            dedupOrder: joinClause.toManyDedupOrder,
          })
        : tableExpression;

      return `${joinClause.joinType} JOIN ${joinSource} AS ${escapeIdentifier(
        joinClause.alias,
      )} ON ${onConditions.map((condition) => `(${condition})`).join(' AND ')}`;
    })
    .join(' ');

export const buildGroupByClause = (state: SelectStatementState): string => {
  if (state.groupByExpressions.length === 0) {
    return '';
  }

  return `GROUP BY ${state.groupByExpressions.join(', ')}`;
};

export const buildOrderByClause = (state: SelectStatementState): string => {
  if (state.orderByClauses.length === 0) {
    return '';
  }

  const aliases = collectStatementAliases(state);

  return `ORDER BY ${state.orderByClauses
    .map(
      (orderByClause) =>
        `${quoteQualifiedAliasReferences(orderByClause.expression, aliases)} ${
          orderByClause.direction
        }${isDefined(orderByClause.nulls) ? ` ${orderByClause.nulls}` : ''}`,
    )
    .join(', ')}`;
};

export const LIMIT_PARAMETER_NAME = 'ormLimit';
export const OFFSET_PARAMETER_NAME = 'ormOffset';

export const RESERVED_PARAMETER_NAMES: string[] = [
  LIMIT_PARAMETER_NAME,
  OFFSET_PARAMETER_NAME,
];

export const buildPaginationParameters = (
  state: SelectStatementState,
): Record<string, number> => ({
  ...(isDefined(state.limitValue)
    ? { [LIMIT_PARAMETER_NAME]: Number(state.limitValue) }
    : {}),
  ...(isDefined(state.offsetValue)
    ? { [OFFSET_PARAMETER_NAME]: Number(state.offsetValue) }
    : {}),
});

const buildDistinctOnClause = (state: SelectStatementState): string => {
  if (state.distinctOnExpressions.length === 0) {
    return '';
  }

  const aliases = collectStatementAliases(state);

  return `DISTINCT ON (${state.distinctOnExpressions
    .map((expression) => quoteQualifiedAliasReferences(expression, aliases))
    .join(', ')}) `;
};

export const buildSelectStatement = (state: SelectStatementState): string => {
  const whereExpression = buildWhereExpression(state);

  return [
    `SELECT ${buildDistinctOnClause(state)}${buildProjection(state).expressions.join(', ')}`,
    buildFromClause(state),
    buildJoinClause(state),
    whereExpression.length > 0 ? `WHERE ${whereExpression}` : '',
    buildGroupByClause(state),
    buildOrderByClause(state),
    isDefined(state.limitValue) ? `LIMIT :${LIMIT_PARAMETER_NAME}` : '',
    isDefined(state.offsetValue) ? `OFFSET :${OFFSET_PARAMETER_NAME}` : '',
  ]
    .filter((part) => part.length > 0)
    .join(' ');
};

export const buildCountStatement = (state: SelectStatementState): string => {
  const whereExpression = buildWhereExpression(state);

  const countExpression =
    state.joinClauses.length > 0
      ? `COUNT(DISTINCT ${quoteColumn(state.alias, 'id')})`
      : 'COUNT(1)';

  return [
    `SELECT ${countExpression} AS "count"`,
    buildFromClause(state),
    buildJoinClause(state),
    whereExpression.length > 0 ? `WHERE ${whereExpression}` : '',
  ]
    .filter((part) => part.length > 0)
    .join(' ');
};

type RelationColumnLeaf = {
  propertySegments: string[];
  value: unknown;
};

const buildRelationValue = (leaves: RelationColumnLeaf[]): unknown => {
  const idLeaf = leaves.find(
    (leaf) =>
      leaf.propertySegments.length === 1 && leaf.propertySegments[0] === 'id',
  );

  if (isDefined(idLeaf) && idLeaf.value === null) {
    return null;
  }

  const relationValue: Record<string, unknown> = {};
  const nestedLeavesByProperty = new Map<string, RelationColumnLeaf[]>();

  for (const leaf of leaves) {
    const [propertyName, ...remainingSegments] = leaf.propertySegments;

    if (remainingSegments.length === 0) {
      relationValue[propertyName] = leaf.value;
      continue;
    }

    const nestedLeaves = nestedLeavesByProperty.get(propertyName) ?? [];

    nestedLeaves.push({
      propertySegments: remainingSegments,
      value: leaf.value,
    });
    nestedLeavesByProperty.set(propertyName, nestedLeaves);
  }

  for (const [propertyName, nestedLeaves] of nestedLeavesByProperty) {
    relationValue[propertyName] = buildRelationValue(nestedLeaves);
  }

  return relationValue;
};

export const createRowToEntityMapper = <TRecord extends ObjectLiteral>(
  columnNameByResultAlias: Record<string, string>,
): ((row: Record<string, unknown>) => TRecord) => {
  const columns = Object.entries(columnNameByResultAlias).map(
    ([resultAlias, propertyPath]) => {
      const [propertyName, ...remainingSegments] = propertyPath.split('.');

      return { resultAlias, propertyName, remainingSegments };
    },
  );

  return (row) => {
    const entity: Record<string, unknown> = {};
    const relationLeavesByProperty = new Map<string, RelationColumnLeaf[]>();

    for (const { resultAlias, propertyName, remainingSegments } of columns) {
      if (!(resultAlias in row)) {
        continue;
      }

      if (remainingSegments.length === 0) {
        entity[propertyName] = row[resultAlias];
        continue;
      }

      const relationLeaves = relationLeavesByProperty.get(propertyName) ?? [];

      relationLeaves.push({
        propertySegments: remainingSegments,
        value: row[resultAlias],
      });
      relationLeavesByProperty.set(propertyName, relationLeaves);
    }

    for (const [relationProperty, relationLeaves] of relationLeavesByProperty) {
      entity[relationProperty] = buildRelationValue(relationLeaves);
    }

    return entity as TRecord;
  };
};

export const buildColumnNameByResultAlias = (
  alias: string,
  mainAliasColumnNames: string[],
): Record<string, string> =>
  Object.fromEntries(
    mainAliasColumnNames.map((columnName) => [
      buildColumnResultAlias(alias, columnName),
      columnName,
    ]),
  );

export const buildHydrationPathByResultAlias = (
  state: SelectStatementState,
): Record<string, string> => {
  const { mainAliasColumnNames } = buildProjection(state);

  const mainColumnNames = [
    ...mainAliasColumnNames,
    ...state.columnSelections
      .filter((columnSelection) => columnSelection.alias === state.alias)
      .map((columnSelection) => columnSelection.columnName),
  ];

  return {
    ...buildColumnNameByResultAlias(state.alias, mainColumnNames),
    ...Object.fromEntries(
      collectJoinedColumnProjections(state).map((joinedProjection) => [
        joinedProjection.resultAlias,
        `${joinedProjection.propertyPath}.${joinedProjection.columnName}`,
      ]),
    ),
  };
};