Skip to content

The easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:

21.4 KB
Raw Blame
import { createMockMetadata } from "__support__/metadata";
import * as Lib from "metabase-lib";
import type { DatasetQuery } from "metabase-types/api";
import {
  createMockCard,
  createMockMeasure,
  createMockSegment,
  createMockStructuredDatasetQuery,
} from "metabase-types/api/mocks";
import {
  ORDERS_ID,
  PEOPLE_ID,
  PRODUCTS,
  PRODUCTS_ID,
} from "metabase-types/api/mocks/presets";

import {
  DEFAULT_TEST_QUERY,
  SAMPLE_DATABASE,
  SAMPLE_METADATA,
  SAMPLE_PROVIDER,
} from "./test-helpers";

const METRIC_ID = 42;
const QUESTION_ID = 43;

const DEFAULT_QUERY: DatasetQuery = {
  database: SAMPLE_DATABASE.id,
  type: "query",
  query: {
    "source-table": ORDERS_ID,
  },
};

describe("fromJsQuery", () => {
  // this is a very important optimization that the FE heavily relies upon
  it("should return the same object for the same database id, query, and metadata", () => {
    const metadataProvider = Lib.metadataProvider(
      SAMPLE_DATABASE.id,
      SAMPLE_METADATA,
    );
    const query1 = Lib.fromJsQuery(metadataProvider, DEFAULT_QUERY);
    const query2 = Lib.fromJsQuery(metadataProvider, DEFAULT_QUERY);
    expect(query1 === query2).toBe(true);
  });
});

describe("toLegacyQuery", () => {
  it("should serialize a query", () => {
    const query = Lib.createTestQuery(SAMPLE_PROVIDER, DEFAULT_TEST_QUERY);
    expect(Lib.toLegacyQuery(query)).toEqual(DEFAULT_QUERY);
  });
});

describe("suggestedName", () => {
  it("should suggest a query name", () => {
    const query = Lib.createTestQuery(SAMPLE_PROVIDER, DEFAULT_TEST_QUERY);
    expect(Lib.suggestedName(query)).toBe("Orders");
  });
});

describe("stageIndexes", () => {
  it("should return stage indexes for a single-stage query", () => {
    const query = Lib.createTestQuery(SAMPLE_PROVIDER, DEFAULT_TEST_QUERY);
    expect(Lib.stageIndexes(query)).toEqual([0]);
  });

  it("should return stage indexes for a multi-stage query", () => {
    const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
      stages: [
        {
          source: {
            type: "table",
            id: PRODUCTS_ID,
          },
        },
        {},
        {},
      ],
    });
    expect(Lib.stageIndexes(query)).toEqual([0, 1, 2]);
  });
});

describe("createTestQuery", () => {
  describe("source", () => {
    it("should create a query with a table source", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
          },
        ],
      });
      expect(Lib.sourceTableOrCardId(query)).toBe(PRODUCTS_ID);
    });

    it("should create a query with a card source", () => {
      const provider = Lib.metadataProvider(
        SAMPLE_DATABASE.id,
        createMockMetadata({
          databases: [SAMPLE_DATABASE],
          questions: [
            createMockCard({
              id: QUESTION_ID,
              name: "Orders question",
              dataset_query: createMockStructuredDatasetQuery({
                database: SAMPLE_DATABASE.id,
                query: {
                  "source-table": ORDERS_ID,
                },
              }),
            }),
          ],
        }),
      );

      const query = Lib.createTestQuery(provider, {
        stages: [
          {
            source: {
              type: "card",
              id: QUESTION_ID,
            },
          },
        ],
      });

      expect(Lib.toJsQuery(query)).toMatchObject({
        stages: [
          {
            "source-card": QUESTION_ID,
          },
        ],
      });
    });

    it("should create a query with a metric aggregation", () => {
      const provider = Lib.metadataProvider(
        SAMPLE_DATABASE.id,
        createMockMetadata({
          databases: [SAMPLE_DATABASE],
          questions: [
            createMockCard({
              id: METRIC_ID,
              name: "Revenue",
              type: "metric",
              dataset_query: createMockStructuredDatasetQuery({
                database: SAMPLE_DATABASE.id,
                query: {
                  "source-table": ORDERS_ID,
                  aggregation: [["count"]],
                },
              }),
            }),
          ],
        }),
      );

      const query = Lib.createTestQuery(provider, {
        stages: [
          {
            source: {
              type: "table",
              id: ORDERS_ID,
            },
            aggregations: [{ type: "metric", id: METRIC_ID }],
          },
        ],
      });

      expect(Lib.sourceTableOrCardId(query)).toBe(ORDERS_ID);
      expect(
        Lib.displayInfo(query, 0, Lib.aggregations(query, 0)[0]),
      ).toMatchObject({
        displayName: "Revenue",
      });
    });
  });

  describe("fields", () => {
    it("should create a query with fields", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            fields: [
              {
                type: "column",
                name: "ID",
              },
              {
                type: "column",
                name: "VENDOR",
              },
            ],
          },
        ],
      });

      const fields = Lib.fields(query, 0);
      expect(fields).toHaveLength(2);

      expect(Lib.displayInfo(query, 0, fields[0])).toMatchObject({
        name: "ID",
      });
      expect(Lib.displayInfo(query, 0, fields[1])).toMatchObject({
        name: "VENDOR",
      });
    });
  });

  describe("expressions", () => {
    it("should create a query with expressions", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            expressions: [
              {
                name: "Foo",
                value: {
                  type: "operator",
                  operator: "+",
                  args: [
                    { type: "column", name: "ID" },
                    { type: "literal", value: 42 },
                  ],
                },
              },
            ],
          },
        ],
      });

      const expressions = Lib.expressions(query, 0);
      expect(expressions).toHaveLength(1);

      expect(Lib.displayInfo(query, 0, expressions[0])).toMatchObject({
        displayName: "Foo",
      });
    });
  });

  describe("breakouts", () => {
    it("should create a query with breakouts", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            breakouts: [{ type: "column", name: "CATEGORY" }],
          },
        ],
      });

      const breakouts = Lib.breakouts(query, 0);
      expect(breakouts).toHaveLength(1);
      expect(Lib.displayInfo(query, 0, breakouts[0])).toMatchObject({
        displayName: "Category",
      });
    });

    it("should create a query with temporal breakouts", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            breakouts: [{ type: "column", name: "CREATED_AT", unit: "month" }],
          },
        ],
      });

      const breakouts = Lib.breakouts(query, 0);
      expect(breakouts).toHaveLength(1);
      expect(Lib.displayInfo(query, 0, breakouts[0])).toMatchObject({
        displayName: "Created At: Month",
      });
    });

    it("should create a query with breakouts with binning based on count", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            breakouts: [
              {
                type: "column",
                name: "RATING",
                binning: { strategy: "num-bins", numBins: 10 },
              },
            ],
          },
        ],
      });

      const breakouts = Lib.breakouts(query, 0);
      expect(breakouts).toHaveLength(1);
      expect(Lib.displayInfo(query, 0, breakouts[0])).toMatchObject({
        displayName: "Rating: 10 bins",
      });
    });

    it("should create a query with breakouts with binning based on width", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PEOPLE_ID,
            },
            breakouts: [
              {
                type: "column",
                name: "LATITUDE",
                binning: { strategy: "bin-width", binWidth: 10 },
              },
            ],
          },
        ],
      });

      const breakouts = Lib.breakouts(query, 0);
      expect(breakouts).toHaveLength(1);
      expect(Lib.displayInfo(query, 0, breakouts[0])).toMatchObject({
        displayName: "Latitude: 10°",
      });
    });
  });

  describe("joins", () => {
    it("should create a query with joins", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            joins: [
              {
                source: {
                  type: "table",
                  id: PEOPLE_ID,
                },
                strategy: "inner-join",
                conditions: [
                  {
                    operator: "=",
                    left: {
                      type: "column",
                      name: "VENDOR",
                    },
                    right: {
                      type: "operator",
                      operator: "+",
                      args: [
                        {
                          type: "column",
                          name: "ID",
                        },
                        {
                          type: "literal",
                          value: 1,
                        },
                      ],
                    },
                  },
                ],
              },
            ],
          },
        ],
      });

      const joins = Lib.joins(query, -1);
      expect(joins).toHaveLength(1);

      const conditions = Lib.joinConditions(joins[0]);
      expect(conditions).toHaveLength(1);

      expect(Lib.displayInfo(query, -1, conditions[0])).toMatchObject({
        displayName: "Vendor is ID + 1",
      });
    });

    it("should create a query with joins that have binning", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            joins: [
              {
                source: {
                  type: "table",
                  id: PEOPLE_ID,
                },
                strategy: "inner-join",
                conditions: [
                  {
                    operator: "=",
                    left: {
                      type: "column",
                      name: "CREATED_AT",
                      unit: "month",
                    },
                    right: {
                      type: "column",
                      name: "CREATED_AT",
                      unit: "month",
                    },
                  },
                ],
              },
            ],
          },
        ],
      });

      const joins = Lib.joins(query, -1);
      expect(joins).toHaveLength(1);

      const conditions = Lib.joinConditions(joins[0]);
      expect(conditions).toHaveLength(1);

      expect(Lib.displayInfo(query, -1, conditions[0])).toMatchObject({
        displayName: "Created At: Month is Created At: Month",
      });
    });

    it("should create a query with suggested joins", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: ORDERS_ID,
            },
            joins: [
              {
                source: {
                  type: "table",
                  id: PRODUCTS_ID,
                },
                strategy: "inner-join",
              },
            ],
          },
        ],
      });

      const joins = Lib.joins(query, -1);
      expect(joins).toHaveLength(1);

      const conditions = Lib.joinConditions(joins[0]);
      expect(conditions).toHaveLength(1);

      expect(Lib.displayInfo(query, -1, conditions[0])).toMatchObject({
        displayName: "Product ID is ID",
      });
    });
  });

  describe("filters", () => {
    it("should create a query with a segment filter", () => {
      const SEGMENT_ID = 12;
      const provider = Lib.metadataProvider(
        SAMPLE_DATABASE.id,
        createMockMetadata({
          databases: [SAMPLE_DATABASE],
          segments: [
            createMockSegment({
              id: SEGMENT_ID,
              table_id: PRODUCTS_ID,
              definition: createMockStructuredDatasetQuery({
                query: {
                  "source-table": PRODUCTS_ID,
                  filter: ["=", ["field", PRODUCTS.CATEGORY, null], "Widget"],
                },
              }),
            }),
          ],
        }),
      );
      const query = Lib.createTestQuery(provider, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            filters: [{ type: "segment", id: SEGMENT_ID }],
          },
        ],
      });

      expect(Lib.toLegacyQuery(query)).toMatchObject({
        query: { filter: ["segment", SEGMENT_ID] },
      });
    });

    it("should create a query with filters", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            filters: [
              {
                type: "operator",
                operator: ">=",
                args: [
                  { type: "column", name: "PRICE" },
                  { type: "literal", value: 100 },
                ],
              },
            ],
          },
        ],
      });

      const filters = Lib.filters(query, -1);
      expect(filters).toHaveLength(1);

      expect(Lib.displayInfo(query, -1, filters[0])).toMatchObject({
        displayName: "Price is greater than or equal to 100",
      });
    });
  });

  describe("aggregations", () => {
    it("should create a query with a measure aggregation", () => {
      const MEASURE_ID = 34;

      const provider = Lib.metadataProvider(
        SAMPLE_DATABASE.id,
        createMockMetadata({
          databases: [SAMPLE_DATABASE],
          measures: [
            createMockMeasure({
              id: MEASURE_ID,
              table_id: PRODUCTS_ID,
              definition: Lib.createTestJsQuery(SAMPLE_PROVIDER, {
                stages: [
                  {
                    source: { type: "table", id: PRODUCTS_ID },
                    aggregations: [{ type: "operator", operator: "count" }],
                  },
                ],
              }),
            }),
          ],
        }),
      );

      const query = Lib.createTestQuery(provider, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            aggregations: [{ type: "measure", id: MEASURE_ID }],
          },
        ],
      });

      expect(Lib.toLegacyQuery(query)).toMatchObject({
        query: {
          aggregation: [
            [
              "aggregation-options",
              expect.anything(),
              { "display-name": "Measure" },
            ],
          ],
        },
      });
    });

    it("should create a query with aggregations", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            aggregations: [
              {
                type: "operator",
                operator: ">=",
                args: [
                  { type: "column", name: "PRICE" },
                  { type: "literal", value: 100 },
                ],
              },
            ],
          },
        ],
      });

      const aggregations = Lib.aggregations(query, -1);
      expect(aggregations).toHaveLength(1);

      expect(Lib.displayInfo(query, -1, aggregations[0])).toMatchObject({
        displayName: "Price is greater than or equal to 100",
      });
    });
  });

  describe("order bys", () => {
    it("should create a query with order bys", () => {
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            orderBys: [{ type: "column", name: "CATEGORY" }],
          },
        ],
      });

      const orderBys = Lib.orderBys(query, 0);
      expect(orderBys).toHaveLength(1);
      expect(Lib.displayInfo(query, 0, orderBys[0])).toMatchObject({
        displayName: "Category",
      });
    });
  });

  describe("limit", () => {
    it("should create a query with a limit", () => {
      const LIMIT = 42;
      const query = Lib.createTestQuery(SAMPLE_PROVIDER, {
        stages: [
          {
            source: {
              type: "table",
              id: PRODUCTS_ID,
            },
            limit: LIMIT,
          },
        ],
      });

      expect(Lib.currentLimit(query, 0)).toBe(LIMIT);
    });
  });
});

describe("createTestNativeQuery", () => {
  it.each(["text", "number", "date", "boolean"] as const)(
    "should create a native query with a %s tag",
    (type) => {
      const queryText = "SELECT * FROM ORDERS WHERE ID = {{x}} ";
      const query = Lib.createTestNativeQuery(SAMPLE_PROVIDER, {
        query: queryText,
        templateTags: {
          x: {
            type,
          },
        },
      });

      expect(Lib.rawNativeQuery(query)).toBe(queryText);
      expect(Lib.templateTags(query)).toEqual({
        x: {
          id: expect.any(String),
          type,
          name: "x",
          "display-name": "X",
        },
      });
    },
  );

  it("should create a native query with a dimension template tag", () => {
    const queryText = "SELECT * FROM ORDERS WHERE {{x}}";
    const query = Lib.createTestNativeQuery(SAMPLE_PROVIDER, {
      query: queryText,
      templateTags: {
        x: {
          type: "dimension",
          dimension: PRODUCTS.CATEGORY,
        },
      },
    });

    expect(Lib.rawNativeQuery(query)).toBe(queryText);
    expect(Lib.templateTags(query)).toEqual({
      x: {
        id: expect.any(String),
        type: "dimension",
        name: "x",
        "display-name": "X",
        dimension: ["field", PRODUCTS.CATEGORY, { "base-type": "type/Text" }],
        "widget-type": "text",
      },
    });
  });

  it("should create a native query with a temporal unit template tag", () => {
    const queryText = "SELECT * FROM ORDERS WHERE {{x}}";
    const query = Lib.createTestNativeQuery(SAMPLE_PROVIDER, {
      query: queryText,
      templateTags: {
        x: {
          type: "temporal-unit",
          dimension: PRODUCTS.CREATED_AT,
        },
      },
    });

    expect(Lib.rawNativeQuery(query)).toBe(queryText);
    expect(Lib.templateTags(query)).toEqual({
      x: {
        id: expect.any(String),
        type: "temporal-unit",
        name: "x",
        "display-name": "X",
        dimension: [
          "field",
          PRODUCTS.CREATED_AT,
          { "base-type": "type/DateTime" },
        ],
      },
    });
  });

  it("should create a native query with a snippet tag", () => {
    const queryText = "SELECT * FROM ORDERS WHERE {{snippet: foo}}";
    const query = Lib.createTestNativeQuery(SAMPLE_PROVIDER, {
      query: queryText,
      templateTags: {
        "snippet: foo": {
          type: "snippet",
          "snippet-id": 12,
        },
      },
    });

    expect(Lib.rawNativeQuery(query)).toBe(queryText);
    expect(Lib.templateTags(query)).toEqual({
      "snippet: foo": {
        id: expect.any(String),
        type: "snippet",
        name: "snippet: foo",
        "snippet-id": 12,
        "snippet-name": "foo",
        "display-name": "Snippet: Foo",
      },
    });
  });

  it("should create a native query with a card tag", () => {
    const queryText = "SELECT * FROM ORDERS WHERE {{#1-users}}";
    const query = Lib.createTestNativeQuery(SAMPLE_PROVIDER, {
      query: queryText,
      templateTags: {
        "#1-users": {
          type: "card",
        },
      },
    });

    expect(Lib.rawNativeQuery(query)).toBe(queryText);
    expect(Lib.templateTags(query)).toEqual({
      "#1-users": {
        id: expect.any(String),
        type: "card",
        name: "#1-users",
        "display-name": "#1 Users",
        "card-id": 1,
      },
    });
  });

  it("should add default, required properties", () => {
    const queryText = "SELECT * FROM ORDERS WHERE ID = {{x}} ";
    const query = Lib.createTestNativeQuery(SAMPLE_PROVIDER, {
      query: queryText,
      templateTags: {
        x: {
          type: "text",
          default: "CA",
          required: true,
          "widget-type": "string/contains",
        },
      },
    });

    expect(Lib.rawNativeQuery(query)).toBe(queryText);
    expect(Lib.templateTags(query)).toEqual({
      x: {
        id: expect.any(String),
        type: "text",
        name: "x",
        required: true,
        default: "CA",
        "widget-type": "string/contains",
        "display-name": "X",
      },
    });
  });
});