ui: DataGrid: Improve SQL datasource performance (#4140)
- Don't reload row count if only sorting has changed.
- Reduce flicker on reloads.
- Improve and simplify API to SQLDataSource.
diff --git a/ui/src/components/aggregation_adapter.ts b/ui/src/components/aggregation_adapter.ts
index 8e5f052..cc86aa7 100644
--- a/ui/src/components/aggregation_adapter.ts
+++ b/ui/src/components/aggregation_adapter.ts
@@ -30,6 +30,7 @@
import {AggregationPanel} from './aggregation_panel';
import {Pivot} from './widgets/datagrid/model';
import {SQLDataSource} from './widgets/datagrid/sql_data_source';
+import {createSimpleSchema} from './widgets/datagrid/sql_schema';
import {BarChartData, ColumnDef} from './aggregation';
import {
createPerfettoTable,
@@ -237,7 +238,8 @@
data = await aggregation?.prepareData(trace.engine);
dataSource = new SQLDataSource({
engine: trace.engine,
- baseQuery: data.tableName,
+ sqlSchema: createSimpleSchema(data.tableName),
+ rootSchemaName: 'query',
});
}
});
diff --git a/ui/src/components/widgets/datagrid/add_column_menu.ts b/ui/src/components/widgets/datagrid/add_column_menu.ts
index 9c347a0..bdcf02e 100644
--- a/ui/src/components/widgets/datagrid/add_column_menu.ts
+++ b/ui/src/components/widgets/datagrid/add_column_menu.ts
@@ -101,8 +101,7 @@
} else if (isParameterizedColumnDef(entry)) {
// Parameterized column - show available keys from datasource
const title = typeof entry.title === 'string' ? entry.title : columnName;
- const availableKeys =
- context.dataSource.result?.parameterKeys?.get(fullPath);
+ const availableKeys = context.dataSource.parameterKeys?.get(fullPath);
menuItems.push(
m(
MenuItem,
diff --git a/ui/src/components/widgets/datagrid/data_source.ts b/ui/src/components/widgets/datagrid/data_source.ts
index 1060226..8b09e06 100644
--- a/ui/src/components/widgets/datagrid/data_source.ts
+++ b/ui/src/components/widgets/datagrid/data_source.ts
@@ -21,7 +21,20 @@
}
export interface DataSource {
- readonly result?: DataSourceResult;
+ // The row data for the current data grid state (filters, sorting, pagination,
+ // etc)
+ readonly rows?: DataSourceRows;
+
+ // Available distinct values for specified columns (for filter dropdowns)
+ readonly distinctValues?: ReadonlyMap<string, readonly SqlValue[]>;
+
+ // Available parameter keys for parameterized columns (e.g., for 'args' ->
+ // ['foo', 'bar'])
+ readonly parameterKeys?: ReadonlyMap<string, readonly string[]>;
+
+ // Computed aggregate totals for each aggregate column (grand total across all
+ // filtered rows)
+ readonly aggregateTotals?: ReadonlyMap<string, SqlValue>;
// Whether the data source is currently loading data/updating.
readonly isLoading?: boolean;
@@ -36,23 +49,33 @@
}
export interface DataSourceModel {
+ // The columns to display, including their sort direction if any
readonly columns?: readonly Column[];
+
+ // Active filters to apply to the data
readonly filters?: readonly Filter[];
+
+ // Pagination settings (offset and limit for the current page)
readonly pagination?: Pagination;
+
+ // Pivot configuration for grouped/aggregated views
readonly pivot?: Pivot;
+
+ // Columns for which to fetch distinct values (for filter dropdowns)
readonly distinctValuesColumns?: ReadonlySet<string>;
- // Request parameter keys for these parameterized column prefixes (e.g., 'args', 'skills')
+
+ // Parameterized column prefixes for which to fetch available keys (e.g.,
+ // 'args')
readonly parameterKeyColumns?: ReadonlySet<string>;
}
-export interface DataSourceResult {
+export interface DataSourceRows {
+ // The total number of rows available in the dataset
readonly totalRows: number;
+
+ // The offset of the first row in this batch
readonly rowOffset: number;
+
+ // The actual row data for this batch
readonly rows: readonly Row[];
- readonly isLoading?: boolean;
- readonly distinctValues?: ReadonlyMap<string, readonly SqlValue[]>;
- // Available parameter keys for parameterized columns (e.g., for 'args' -> ['foo', 'bar'])
- readonly parameterKeys?: ReadonlyMap<string, readonly string[]>;
- // Computed aggregate totals for each aggregate column (grand total across all filtered rows)
- readonly aggregateTotals?: ReadonlyMap<string, SqlValue>;
}
diff --git a/ui/src/components/widgets/datagrid/datagrid.ts b/ui/src/components/widgets/datagrid/datagrid.ts
index 98f1bdd..c4867a6 100644
--- a/ui/src/components/widgets/datagrid/datagrid.ts
+++ b/ui/src/components/widgets/datagrid/datagrid.ts
@@ -287,7 +287,7 @@
readonly schema: SchemaRegistry;
readonly rootSchema: string;
readonly datasource: DataSource;
- readonly result: DataSource['result'];
+ readonly result: DataSource['rows'];
readonly columnInfoCache: Map<string, ReturnType<typeof getColumnInfo>>;
readonly structuredQueryCompatMode: boolean;
}
@@ -300,7 +300,7 @@
readonly schema: SchemaRegistry;
readonly rootSchema: string;
readonly datasource: DataSource;
- readonly result: DataSource['result'];
+ readonly result: DataSource['rows'];
readonly pivot: Pivot;
readonly structuredQueryCompatMode: boolean;
}
@@ -390,12 +390,12 @@
);
},
getRowCount: () => {
- return datasource?.result?.totalRows;
+ return datasource?.rows?.totalRows;
},
});
// Extract the result from the datasource
- const result = datasource.result;
+ const result = datasource.rows;
// Determine if we're in pivot mode (has groupBy columns and not drilling down)
const isPivotMode =
@@ -979,7 +979,6 @@
schema,
rootSchema,
datasource,
- result,
columnInfoCache,
structuredQueryCompatMode,
} = ctx;
@@ -1007,7 +1006,7 @@
m(FilterMenu, {
columnType,
structuredQueryCompatMode,
- distinctValues: result?.distinctValues?.get(field),
+ distinctValues: datasource.distinctValues?.get(field),
valueFormatter: (v) => colInfo?.cellFormatter?.(v, {}) ?? String(v),
onFilterAdd: (filter) => this.addFilter({field, ...filter}, attrs),
onRequestDistinctValues: () => this.distinctValuesColumns.add(field),
@@ -1086,7 +1085,7 @@
// Build subContent showing grand total if column has an aggregate
let subContent: m.Children;
if (aggregate) {
- const totalValue = result?.aggregateTotals?.get(field);
+ const totalValue = datasource.aggregateTotals?.get(field);
const isLoading = totalValue === undefined;
// Don't show grand total for ANY aggregation (it's just an arbitrary value)
subContent =
@@ -1184,7 +1183,6 @@
rootSchema,
datasource,
pivot,
- result,
structuredQueryCompatMode,
} = ctx;
@@ -1221,7 +1219,7 @@
m(FilterMenu, {
columnType,
structuredQueryCompatMode,
- distinctValues: result?.distinctValues?.get(field),
+ distinctValues: datasource.distinctValues?.get(field),
valueFormatter: (v) => colInfo?.cellFormatter?.(v, {}) ?? String(v),
onFilterAdd: (filter) => this.addFilter({field, ...filter}, attrs),
onRequestDistinctValues: () => this.distinctValuesColumns.add(field),
@@ -1362,7 +1360,7 @@
// Build subContent showing grand total with aggregate symbol
// Don't show grand total for ANY aggregation (it's just an arbitrary value)
- const aggregateTotalValue = result?.aggregateTotals?.get(alias);
+ const aggregateTotalValue = datasource.aggregateTotals?.get(alias);
const symbol = agg.function;
const isLoading = aggregateTotalValue === undefined;
const subContent =
diff --git a/ui/src/components/widgets/datagrid/in_memory_data_source.ts b/ui/src/components/widgets/datagrid/in_memory_data_source.ts
index 5bcfd7b..c3fdb38 100644
--- a/ui/src/components/widgets/datagrid/in_memory_data_source.ts
+++ b/ui/src/components/widgets/datagrid/in_memory_data_source.ts
@@ -15,7 +15,7 @@
import {stringifyJsonWithBigints} from '../../../base/json_utils';
import {assertUnreachable} from '../../../base/logging';
import {Row, SqlValue} from '../../../trace_processor/query_result';
-import {DataSource, DataSourceModel, DataSourceResult} from './data_source';
+import {DataSource, DataSourceModel, DataSourceRows} from './data_source';
import {Column, Filter, Pivot} from './model';
export class InMemoryDataSource implements DataSource {
@@ -35,17 +35,32 @@
this.filteredSortedData = data;
}
- get result(): DataSourceResult {
+ get rows(): DataSourceRows {
return {
rowOffset: 0,
rows: this.filteredSortedData,
totalRows: this.filteredSortedData.length,
- distinctValues: this.distinctValuesCache,
- parameterKeys: this.parameterKeysCache,
- aggregateTotals: this.aggregateTotalsCache,
};
}
+ get distinctValues(): ReadonlyMap<string, readonly SqlValue[]> | undefined {
+ return this.distinctValuesCache.size > 0
+ ? this.distinctValuesCache
+ : undefined;
+ }
+
+ get parameterKeys(): ReadonlyMap<string, readonly string[]> | undefined {
+ return this.parameterKeysCache.size > 0
+ ? this.parameterKeysCache
+ : undefined;
+ }
+
+ get aggregateTotals(): ReadonlyMap<string, SqlValue> | undefined {
+ return this.aggregateTotalsCache.size > 0
+ ? this.aggregateTotalsCache
+ : undefined;
+ }
+
notify({
columns,
filters = [],
diff --git a/ui/src/components/widgets/datagrid/in_memory_data_source_unittest.ts b/ui/src/components/widgets/datagrid/in_memory_data_source_unittest.ts
index 239895d..3da25b6 100644
--- a/ui/src/components/widgets/datagrid/in_memory_data_source_unittest.ts
+++ b/ui/src/components/widgets/datagrid/in_memory_data_source_unittest.ts
@@ -76,7 +76,7 @@
});
test('initialization', () => {
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.rowOffset).toBe(0);
expect(result.totalRows).toBe(sampleData.length);
expect(result.rows).toEqual(sampleData);
@@ -86,7 +86,7 @@
test('equality filter', () => {
const filters: Filter[] = [{field: 'name', op: '=', value: 'Alice'}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(1);
expect(result.rows[0].name).toBe('Alice');
});
@@ -94,7 +94,7 @@
test('inequality filter', () => {
const filters: Filter[] = [{field: 'active', op: '!=', value: 1}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(3); // Bob, David, Mallory
result.rows.forEach((row) => expect(row.active).toBe(0));
});
@@ -102,7 +102,7 @@
test('less than filter', () => {
const filters: Filter[] = [{field: 'value', op: '<', value: 150}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
// David (null), Alice (100), Eve (100)
expect(result.totalRows).toBe(3);
expect(result.rows.map((r) => r.id).sort()).toEqual([1, 4, 5]);
@@ -111,7 +111,7 @@
test('less than or equal filter', () => {
const filters: Filter[] = [{field: 'value', op: '<=', value: 150}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
// David (null), Alice (100), Charlie (150), Eve (100)
expect(result.totalRows).toBe(4);
expect(result.rows.map((r) => r.id).sort()).toEqual([1, 3, 4, 5]);
@@ -120,7 +120,7 @@
test('greater than filter', () => {
const filters: Filter[] = [{field: 'value', op: '>', value: 200}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(2); // Mallory (300n), Trent (250n)
expect(result.rows.map((r) => r.id).sort()).toEqual([6, 7]);
});
@@ -128,7 +128,7 @@
test('greater than or equal filter with bigint', () => {
const filters: Filter[] = [{field: 'value', op: '>=', value: 250n}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(2); // Mallory, Trent
expect(result.rows.map((r) => r.id).sort()).toEqual([6, 7]);
});
@@ -136,7 +136,7 @@
test('is null filter', () => {
const filters: Filter[] = [{field: 'value', op: 'is null'}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(1);
expect(result.rows[0].id).toBe(4); // David
});
@@ -144,7 +144,7 @@
test('is not null filter', () => {
const filters: Filter[] = [{field: 'blob', op: 'is not null'}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(6); // All except Charlie
expect(result.rows.find((r) => r.id === 3)).toBeUndefined();
});
@@ -152,7 +152,7 @@
test('glob filter', () => {
const filters: Filter[] = [{field: 'name', op: 'glob', value: 'A*e'}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(1);
expect(result.rows[0].name).toBe('Alice');
});
@@ -160,7 +160,7 @@
test('glob filter with ?', () => {
const filters: Filter[] = [{field: 'name', op: 'glob', value: 'B?b'}];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(1);
expect(result.rows[0].name).toBe('Bob');
});
@@ -171,7 +171,7 @@
{field: 'tag', op: '=', value: 'A'},
];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(3); // Alice, Charlie, Trent
result.rows.forEach((row) => {
expect(row.active).toBe(1);
@@ -184,7 +184,7 @@
{field: 'name', op: '=', value: 'NonExistent'},
];
dataSource.notify({filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.totalRows).toBe(0);
expect(result.rows.length).toBe(0);
});
@@ -194,7 +194,7 @@
test('sort by string ascending', () => {
const columns: Column[] = [{field: 'name', sort: 'ASC'}];
dataSource.notify({columns, filters: []});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.rows.map((r) => r.name)).toEqual([
'Alice',
'Bob',
@@ -209,7 +209,7 @@
test('sort by string descending', () => {
const columns: Column[] = [{field: 'name', sort: 'DESC'}];
dataSource.notify({columns, filters: []});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.rows.map((r) => r.name)).toEqual([
'Trent',
'Mallory',
@@ -224,7 +224,7 @@
test('sort by number ascending (includes nulls)', () => {
const columns: Column[] = [{field: 'value', sort: 'ASC'}];
dataSource.notify({columns, filters: []});
- const result = dataSource.result;
+ const result = dataSource.rows;
// Nulls first, then 100, 100, 150, 200, 250n, 300n
expect(result.rows.map((r) => r.id)).toEqual([4, 1, 5, 3, 2, 7, 6]);
});
@@ -232,7 +232,7 @@
test('sort by number descending (includes nulls and bigint)', () => {
const columns: Column[] = [{field: 'value', sort: 'DESC'}];
dataSource.notify({columns, filters: []});
- const result = dataSource.result;
+ const result = dataSource.rows;
// 300n, 250n, 200, 150, 100, 100, Nulls last
expect(result.rows.map((r) => r.id)).toEqual([6, 7, 2, 3, 1, 5, 4]);
});
@@ -240,14 +240,14 @@
test('sort by boolean ascending', () => {
const columns: Column[] = [{field: 'active', sort: 'ASC'}]; // 0 then 1
dataSource.notify({columns, filters: []});
- const result = dataSource.result;
+ const result = dataSource.rows;
expect(result.rows.map((r) => r.active)).toEqual([0, 0, 0, 1, 1, 1, 1]);
});
test('sort by Uint8Array ascending (by length)', () => {
const columns: Column[] = [{field: 'blob', sort: 'ASC'}];
dataSource.notify({columns, filters: []});
- const result = dataSource.result;
+ const result = dataSource.rows;
// null (Charlie, id:3), len 1 (David id:4, Mallory id:6), len 2 (Alice id:1, Trent id:7), len 3 (Bob id:2), len 4 (Eve id:5)
// Original order for same length: David before Mallory, Alice before Trent.
expect(result.rows.map((r) => r.id)).toEqual([3, 4, 6, 1, 7, 2, 5]);
@@ -256,7 +256,7 @@
test('sort by Uint8Array descending (by length)', () => {
const columns: Column[] = [{field: 'blob', sort: 'DESC'}];
dataSource.notify({columns, filters: []});
- const result = dataSource.result;
+ const result = dataSource.rows;
// len 4, len 3, len 2, len 2, len 1, len 0, null
expect(result.rows.map((r) => r.id)).toEqual([5, 2, 1, 7, 4, 6, 3]);
});
@@ -268,7 +268,7 @@
});
// Then unsort
dataSource.notify({});
- const result = dataSource.result;
+ const result = dataSource.rows;
// Should revert to original order if no filters applied
expect(result.rows.map((r) => r.id)).toEqual(sampleData.map((r) => r.id));
});
@@ -279,7 +279,7 @@
const filters: Filter[] = [{field: 'active', op: '=', value: 1}];
const columns: Column[] = [{field: 'value', sort: 'DESC'}];
dataSource.notify({columns, filters});
- const result = dataSource.result;
+ const result = dataSource.rows;
// Active: Alice (100), Charlie (150), Eve (100), Trent (250n)
// Sorted by value desc: Trent, Charlie, Alice, Eve (Alice/Eve order by original due to stable sort on value)
expect(result.rows.map((r) => r.id)).toEqual([7, 3, 1, 5]);
@@ -293,12 +293,12 @@
const columns: Column[] = [{field: 'name', sort: 'ASC'}];
dataSource.notify({columns, filters});
- const result1 = dataSource.result.rows; // Access internal array
+ const result1 = dataSource.rows.rows; // Access internal array
// Spy on internal methods if possible, or check object identity
// For this test, we'll check if the returned array reference is the same
dataSource.notify({columns, filters}); // Identical call
- const result2 = dataSource.result.rows;
+ const result2 = dataSource.rows.rows;
expect(result1).toBe(result2); // Should be the same array instance due to caching
});
@@ -309,10 +309,10 @@
const columns2: Column[] = [{field: 'name', sort: 'DESC'}];
dataSource.notify({columns: columns1, filters});
- const result1 = dataSource.result.rows;
+ const result1 = dataSource.rows.rows;
dataSource.notify({columns: columns2, filters}); // Different sort
- const result2 = dataSource.result.rows;
+ const result2 = dataSource.rows.rows;
expect(result1).not.toBe(result2);
expect(result1.map((r) => r.id)).not.toEqual(result2.map((r) => r.id));
@@ -324,10 +324,10 @@
const columns: Column[] = [{field: 'name', sort: 'ASC'}];
dataSource.notify({columns, filters: filters1});
- const result1 = dataSource.result.rows;
+ const result1 = dataSource.rows.rows;
dataSource.notify({columns, filters: filters2}); // Different filters
- const result2 = dataSource.result.rows;
+ const result2 = dataSource.rows.rows;
expect(result1).not.toBe(result2);
expect(result1.map((r) => r.id)).not.toEqual(result2.map((r) => r.id));
@@ -342,12 +342,12 @@
];
dataSource.notify({filters: filters1});
- const result1 = dataSource.result.rows;
+ const result1 = dataSource.rows.rows;
expect(result1.length).toBe(1);
expect(result1[0].id).toBe(1);
dataSource.notify({filters: filters2});
- const result2 = dataSource.result.rows;
+ const result2 = dataSource.rows.rows;
expect(result2.length).toBe(1);
expect(result2[0].id).toBe(2);
@@ -357,7 +357,7 @@
test('empty data source', () => {
const emptyDataSource = new InMemoryDataSource([]);
- const result = emptyDataSource.result;
+ const result = emptyDataSource.rows;
expect(result.rowOffset).toBe(0);
expect(result.totalRows).toBe(0);
expect(result.rows).toEqual([]);
@@ -366,7 +366,7 @@
columns: [{field: 'id', sort: 'DESC'}],
filters: [{field: 'name', op: '=', value: 'test'}],
});
- const resultAfterUpdate = emptyDataSource.result;
+ const resultAfterUpdate = emptyDataSource.rows;
expect(resultAfterUpdate.totalRows).toBe(0);
expect(resultAfterUpdate.rows).toEqual([]);
});
diff --git a/ui/src/components/widgets/datagrid/sql_data_source.ts b/ui/src/components/widgets/datagrid/sql_data_source.ts
index a88fc89..975167f 100644
--- a/ui/src/components/widgets/datagrid/sql_data_source.ts
+++ b/ui/src/components/widgets/datagrid/sql_data_source.ts
@@ -21,7 +21,7 @@
import {
DataSource,
DataSourceModel,
- DataSourceResult,
+ DataSourceRows,
Pagination,
} from './data_source';
import {Column, Filter, Pivot} from './model';
@@ -33,52 +33,79 @@
/**
* Configuration for SQLDataSource.
- * Either provide a base query string, or a schema with root schema name.
*/
export interface SQLDataSourceConfig {
/**
* The trace processor engine to run queries against.
*/
- engine: Engine;
-
- /**
- * Base SQL query. Required when not using a schema.
- * When using a schema, this is ignored (the base table comes from the schema).
- */
- baseQuery?: string;
+ readonly engine: Engine;
/**
* SQL schema registry defining tables and their relationships.
- * When provided, enables automatic JOIN generation for nested column paths.
+ * Enables automatic JOIN generation for nested column paths.
+ *
+ * For simple queries without explicit column definitions, use
+ * createSimpleSchema() to generate a passthrough schema.
*/
- sqlSchema?: SQLSchemaRegistry;
+ readonly sqlSchema: SQLSchemaRegistry;
/**
- * The root schema name to query from (e.g., 'slice').
- * Required when sqlSchema is provided.
+ * The root schema name to query from (e.g., 'slice', 'query').
*/
- rootSchemaName?: string;
+ readonly rootSchemaName: string;
+
+ /**
+ * Optional SQL prelude to execute before each query.
+ * Useful for imports like "INCLUDE PERFETTO MODULE xyz;"
+ */
+ readonly preamble?: string;
+}
+
+// Cache entry for row count resolution
+interface RowCountCache {
+ query: string;
+ count: number;
+}
+
+// Cache entry for rows resolution
+interface RowsCache {
+ query: string;
+ pagination: Pagination | undefined;
+ offset: number;
+ rows: Row[];
+}
+
+// Cache entry for aggregate resolution
+interface AggregatesCache {
+ query: string;
+ totals: Map<string, SqlValue>;
+}
+
+// Cache entry for distinct values resolution
+interface DistinctValuesCache {
+ query: string;
+ values: ReadonlyArray<SqlValue>;
}
/**
- * SQL data source for DataGrid that can operate in two modes:
+ * SQL data source for DataGrid.
*
- * 1. **Simple mode** (baseQuery only): Columns are queried directly from the
- * base query. Column names must match exactly what's in the query.
+ * Generates optimized SQL queries with JOINs based on column paths like
+ * 'parent.name' or 'thread.process.pid'. Supports parameterized columns
+ * like 'args.foo'.
*
- * 2. **Schema mode** (sqlSchema + rootSchemaName): Generates optimized SQL
- * queries with JOINs based on column paths like 'parent.name' or
- * 'thread.process.pid'. Supports parameterized columns like 'args.foo'.
- *
- * Example usage (simple mode):
+ * For arbitrary queries without explicit schema, use createSimpleSchema():
* ```typescript
+ * import {createSimpleSchema} from './sql_schema';
+ *
* const dataSource = new SQLDataSource({
* engine,
- * baseQuery: 'SELECT * FROM slice WHERE dur > 0',
+ * sqlSchema: createSimpleSchema('SELECT * FROM slice WHERE dur > 0'),
+ * rootSchemaName: 'query',
* });
* ```
*
- * Example usage (schema mode):
+ * For tables with relationships:
* ```typescript
* const schema: SQLSchemaRegistry = {
* slice: {
@@ -87,10 +114,6 @@
* id: {},
* name: {},
* parent: { ref: 'slice', foreignKey: 'parent_id' },
- * args: {
- * expression: (alias, key) => `extract_arg(${alias}.arg_set_id, '${key}')`,
- * parameterized: true,
- * },
* },
* },
* };
@@ -105,45 +128,34 @@
export class SQLDataSource implements DataSource {
private readonly engine: Engine;
private readonly limiter = new AsyncLimiter();
- private readonly baseQuery?: string;
- private readonly sqlSchema?: SQLSchemaRegistry;
- private readonly rootSchemaName?: string;
+ private readonly sqlSchema: SQLSchemaRegistry;
+ private readonly rootSchemaName: string;
+ private readonly prelude?: string;
- private workingQuery = '';
- private pagination?: Pagination;
- private pivot?: Pivot;
- private cachedResult?: DataSourceResult;
- private isLoadingFlag = false;
+ // Cache for each resolution type
+ private rowCountCache?: RowCountCache;
+ private rowsCache?: RowsCache;
+ private aggregatesCache?: AggregatesCache;
+ private distinctValuesCache = new Map<string, DistinctValuesCache>();
private parameterKeysCache = new Map<string, ReadonlyArray<string>>();
+ // Current results
+ private cachedResult?: DataSourceRows;
+ private cachedDistinctValues?: ReadonlyMap<string, ReadonlyArray<SqlValue>>;
+ private cachedAggregateTotals?: ReadonlyMap<string, SqlValue>;
+ private isLoadingFlag = false;
+
constructor(config: SQLDataSourceConfig) {
this.engine = config.engine;
- this.baseQuery = config.baseQuery;
this.sqlSchema = config.sqlSchema;
this.rootSchemaName = config.rootSchemaName;
-
- // Validate configuration
- if (!this.baseQuery && !this.sqlSchema) {
- throw new Error('SQLDataSource requires either baseQuery or sqlSchema');
- }
- if (this.sqlSchema && !this.rootSchemaName) {
- throw new Error(
- 'SQLDataSource requires rootSchemaName when sqlSchema is provided',
- );
- }
- }
-
- /**
- * Returns true if this data source is using schema mode.
- */
- private get useSchema(): boolean {
- return this.sqlSchema !== undefined && this.rootSchemaName !== undefined;
+ this.prelude = config.preamble;
}
/**
* Getter for the current rows result
*/
- get result(): DataSourceResult | undefined {
+ get rows(): DataSourceRows | undefined {
return this.cachedResult;
}
@@ -151,146 +163,63 @@
return this.isLoadingFlag;
}
+ get distinctValues(): ReadonlyMap<string, readonly SqlValue[]> | undefined {
+ return this.cachedDistinctValues;
+ }
+
+ get parameterKeys(): ReadonlyMap<string, readonly string[]> | undefined {
+ return this.parameterKeysCache.size > 0
+ ? this.parameterKeysCache
+ : undefined;
+ }
+
+ get aggregateTotals(): ReadonlyMap<string, SqlValue> | undefined {
+ return this.cachedAggregateTotals;
+ }
+
+ /**
+ * Get the current working query for the datasource.
+ * Useful for debugging or creating debug tracks.
+ */
+ getCurrentQuery(): string {
+ return this.rowsCache?.query ?? '';
+ }
+
/**
* Notify of parameter changes and trigger data update
*/
- notify({
- columns,
- filters = [],
- pagination,
- pivot,
- distinctValuesColumns,
- parameterKeyColumns,
- }: DataSourceModel): void {
+ notify(model: DataSourceModel): void {
this.limiter.schedule(async () => {
+ // Defer setting loading flag to avoid setting it synchronously during the
+ // view() call that triggered notify(). This avoids the bug that the
+ // current frame always has isLoading = true.
+ await Promise.resolve();
this.isLoadingFlag = true;
try {
- const workingQuery = this.buildWorkingQuery(columns, filters, pivot);
+ // Resolve row count
+ const rowCount = await this.resolveRowCount(model);
- if (
- workingQuery !== this.workingQuery ||
- !arePivotsEqual(this.pivot, pivot)
- ) {
- this.workingQuery = workingQuery;
- this.pivot = pivot;
+ // Resolve aggregates
+ const aggregateTotals = await this.resolveAggregates(model);
- // Clear the cache
- this.cachedResult = undefined;
- this.pagination = undefined;
+ // Resolve rows
+ const {offset, rows} = await this.resolveRows(model);
- // Update the cache with the total row count
- const rowCount = await this.getRowCount(workingQuery);
+ // Resolve distinct values
+ const distinctValues = await this.resolveDistinctValues(model);
- // Compute aggregate totals for pivot mode (but not drill-down mode)
- let aggregateTotals: Map<string, SqlValue> | undefined;
- const pivotAggregates = pivot?.aggregates ?? [];
- if (pivot && !pivot.drillDown && pivotAggregates.length > 0) {
- const aggregates = await this.getPivotAggregates(
- columns,
- filters,
- pivot,
- );
- aggregateTotals = new Map<string, SqlValue>();
- for (const [key, value] of Object.entries(aggregates)) {
- aggregateTotals.set(key, value);
- }
- }
+ // Resolve parameter keys
+ await this.resolveParameterKeys(model);
- // Compute column-level aggregations (non-pivot mode)
- const columnsWithAggregation = columns?.filter((c) => c.aggregate);
- if (
- columnsWithAggregation &&
- columnsWithAggregation.length > 0 &&
- !pivot
- ) {
- const columnAggregates = await this.getColumnAggregates(
- filters,
- columnsWithAggregation,
- );
- aggregateTotals = aggregateTotals ?? new Map<string, SqlValue>();
- for (const [key, value] of Object.entries(columnAggregates)) {
- aggregateTotals.set(key, value as SqlValue);
- }
- }
-
- this.cachedResult = {
- rowOffset: 0,
- totalRows: rowCount,
- rows: [],
- distinctValues: new Map<string, ReadonlyArray<SqlValue>>(),
- parameterKeys: this.parameterKeysCache,
- aggregateTotals,
- };
- }
-
- // Fetch data if pagination has changed
- if (!comparePagination(this.pagination, pagination)) {
- this.pagination = pagination;
- const {offset, rows} = await this.getRows(workingQuery, pagination);
- this.cachedResult = {
- ...this.cachedResult!,
- rowOffset: offset,
- rows,
- };
- }
-
- // Handle distinct values requests
- if (distinctValuesColumns) {
- for (const columnPath of distinctValuesColumns) {
- if (!this.cachedResult?.distinctValues?.has(columnPath)) {
- const query = this.buildDistinctValuesQuery(columnPath);
- if (query) {
- const result = await runQueryForQueryTable(query, this.engine);
- const values = result.rows.map((r) => r['value']);
- this.cachedResult = {
- ...this.cachedResult!,
- distinctValues: new Map<string, ReadonlyArray<SqlValue>>([
- ...this.cachedResult!.distinctValues!,
- [columnPath, values],
- ]),
- };
- }
- }
- }
- }
-
- // Handle parameter keys requests (schema mode only)
- if (parameterKeyColumns && this.useSchema) {
- for (const prefix of parameterKeyColumns) {
- if (!this.parameterKeysCache.has(prefix)) {
- const schema = this.sqlSchema![this.rootSchemaName!];
- const colDef = maybeUndefined(schema?.columns[prefix]);
-
- if (
- colDef &&
- isSQLExpressionDef(colDef) &&
- colDef.parameterized
- ) {
- if (colDef.parameterKeysQuery) {
- const baseTable = schema.table;
- const baseAlias = `${baseTable}_0`;
- const query = colDef.parameterKeysQuery(baseTable, baseAlias);
-
- try {
- const result = await runQueryForQueryTable(
- query,
- this.engine,
- );
- const keys = result.rows.map((r) => String(r['key']));
- this.parameterKeysCache.set(prefix, keys);
- this.cachedResult = {
- ...this.cachedResult!,
- parameterKeys: this.parameterKeysCache,
- };
- } catch {
- this.parameterKeysCache.set(prefix, []);
- }
- }
- }
- }
- }
- }
+ // Build final result
+ this.cachedResult = {
+ rowOffset: offset,
+ totalRows: rowCount,
+ rows,
+ };
+ this.cachedDistinctValues = distinctValues;
+ this.cachedAggregateTotals = aggregateTotals;
} finally {
this.isLoadingFlag = false;
}
@@ -298,195 +227,286 @@
}
/**
- * Export all data with current filters/sorting applied.
+ * Resolves the row count. Compares query against cache and reuses if unchanged.
*/
- async exportData(): Promise<Row[]> {
- if (!this.workingQuery) {
- return [];
+ private async resolveRowCount(model: DataSourceModel): Promise<number> {
+ // Build query without ORDER BY - ordering is irrelevant for counting
+ const countQuery = this.buildQuery(model, {includeOrderBy: false});
+
+ // Check cache
+ if (this.rowCountCache?.query === countQuery) {
+ return this.rowCountCache.count;
}
- const query = `SELECT * FROM (${this.workingQuery})`;
- const result = await runQueryForQueryTable(query, this.engine);
- return result.rows;
+ // Fetch new count
+ const result = await this.engine.query(
+ this.wrapQueryWithPrelude(`
+ WITH data AS (${countQuery})
+ SELECT COUNT(*) AS total_count
+ FROM data
+ `),
+ );
+ const count = result.firstRow({total_count: NUM}).total_count;
+
+ // Update cache
+ this.rowCountCache = {query: countQuery, count};
+
+ return count;
}
/**
- * Builds a complete SQL query based on the current mode.
+ * Resolves the rows for the current page. Compares query and pagination against cache.
*/
- private buildWorkingQuery(
- columns: ReadonlyArray<Column> | undefined,
- filters: ReadonlyArray<Filter>,
- pivot?: Pivot,
- ): string {
- if (this.useSchema) {
- return this.buildSchemaWorkingQuery(columns, filters, pivot);
- } else {
- return this.buildSimpleWorkingQuery(columns, filters, pivot);
+ private async resolveRows(
+ model: DataSourceModel,
+ ): Promise<{offset: number; rows: Row[]}> {
+ const {pagination} = model;
+
+ // Build query with ORDER BY for proper pagination ordering
+ const rowsQuery = this.buildQuery(model, {includeOrderBy: true});
+
+ // Check cache - both query and pagination must match
+ if (
+ this.rowsCache?.query === rowsQuery &&
+ comparePagination(this.rowsCache.pagination, pagination)
+ ) {
+ return {offset: this.rowsCache.offset, rows: this.rowsCache.rows};
}
+
+ // Fetch new rows
+ let query = `
+ WITH data AS (${rowsQuery})
+ SELECT *
+ FROM data
+ `;
+
+ if (pagination) {
+ query += `LIMIT ${pagination.limit} OFFSET ${pagination.offset}`;
+ }
+
+ const result = await runQueryForQueryTable(
+ this.wrapQueryWithPrelude(query),
+ this.engine,
+ );
+
+ const offset = pagination?.offset ?? 0;
+ const rows = result.rows;
+
+ // Update cache
+ this.rowsCache = {query: rowsQuery, pagination, offset, rows};
+
+ return {offset, rows};
}
/**
- * Builds a query for simple mode (no schema, direct column access).
+ * Resolves aggregate totals. Handles both pivot aggregates and column aggregates.
*/
- private buildSimpleWorkingQuery(
- columns: ReadonlyArray<Column> | undefined,
- filters: ReadonlyArray<Filter>,
- pivot?: Pivot,
- ): string {
- const colNames = columns?.map((c) => c.field) ?? ['*'];
+ private async resolveAggregates(
+ model: DataSourceModel,
+ ): Promise<Map<string, SqlValue> | undefined> {
+ const {columns, filters = [], pivot} = model;
- // Include column aggregates in the query string so changes trigger a reload
- const aggregateSuffix = columns
- ?.filter((c) => c.aggregate)
- .map((c) => `${c.field}:${c.aggregate}`)
- .join(',');
+ // Build a unique query string for the aggregates
+ const aggregateQuery = this.buildAggregateQuery(model);
- let query: string;
+ // If no aggregates needed, return undefined
+ if (!aggregateQuery) {
+ this.aggregatesCache = undefined;
+ return undefined;
+ }
- if (pivot && !pivot.drillDown) {
- // Pivot mode: Build aggregate columns from pivot.aggregates
- const aggregates = pivot.aggregates ?? [];
- const valCols = aggregates
- .map((agg) => {
- if (agg.function === 'COUNT') {
- return `COUNT(*) AS __count__`;
+ // Check cache
+ if (this.aggregatesCache?.query === aggregateQuery) {
+ return this.aggregatesCache.totals;
+ }
+
+ // Compute aggregates
+ const totals = new Map<string, SqlValue>();
+
+ // Pivot aggregates (but not drill-down mode)
+ const pivotAggregates = pivot?.aggregates ?? [];
+ if (pivot && !pivot.drillDown && pivotAggregates.length > 0) {
+ const aggregates = await this.fetchPivotAggregates(filters, pivot);
+ for (const [key, value] of Object.entries(aggregates)) {
+ totals.set(key, value);
+ }
+ }
+
+ // Column-level aggregations (non-pivot mode)
+ const columnsWithAggregation = columns?.filter((c) => c.aggregate);
+ if (columnsWithAggregation && columnsWithAggregation.length > 0 && !pivot) {
+ const columnAggregates = await this.fetchColumnAggregates(
+ filters,
+ columnsWithAggregation,
+ );
+ for (const [key, value] of Object.entries(columnAggregates)) {
+ totals.set(key, value as SqlValue);
+ }
+ }
+
+ // Update cache
+ this.aggregatesCache = {query: aggregateQuery, totals};
+
+ return totals;
+ }
+
+ /**
+ * Builds a unique string representing the aggregate query for cache comparison.
+ */
+ private buildAggregateQuery(model: DataSourceModel): string | undefined {
+ const {columns, filters = [], pivot} = model;
+
+ const parts: string[] = [];
+
+ // Include pivot aggregates
+ if (pivot && !pivot.drillDown && Boolean(pivot.aggregates?.length)) {
+ parts.push(`pivot:${JSON.stringify(pivot.aggregates)}`);
+ }
+
+ // Include column aggregates
+ const columnsWithAggregation = columns?.filter((c) => c.aggregate);
+ if (columnsWithAggregation && columnsWithAggregation.length > 0 && !pivot) {
+ const colAggs = columnsWithAggregation.map(
+ (c) => `${c.field}:${c.aggregate}`,
+ );
+ parts.push(`columns:${colAggs.join(',')}`);
+ }
+
+ if (parts.length === 0) {
+ return undefined;
+ }
+
+ // Include filters in the cache key
+ const filterKey = filters.map((f) => {
+ const value = 'value' in f ? f.value : '';
+ return `${f.field}:${f.op}:${value}`;
+ });
+ parts.push(`filters:${filterKey.join(',')}`);
+
+ return parts.join('|');
+ }
+
+ /**
+ * Resolves distinct values for requested columns.
+ */
+ private async resolveDistinctValues(
+ model: DataSourceModel,
+ ): Promise<Map<string, ReadonlyArray<SqlValue>>> {
+ const {distinctValuesColumns} = model;
+
+ const result = new Map<string, ReadonlyArray<SqlValue>>();
+
+ if (!distinctValuesColumns) {
+ return result;
+ }
+
+ for (const columnPath of distinctValuesColumns) {
+ const query = this.buildDistinctValuesQuery(columnPath);
+ if (!query) continue;
+
+ // Check cache
+ const cached = this.distinctValuesCache.get(columnPath);
+ if (cached?.query === query) {
+ result.set(columnPath, cached.values);
+ continue;
+ }
+
+ // Fetch new values
+ const queryResult = await runQueryForQueryTable(
+ this.wrapQueryWithPrelude(query),
+ this.engine,
+ );
+ const values = queryResult.rows.map((r) => r['value']);
+
+ // Update cache
+ this.distinctValuesCache.set(columnPath, {query, values});
+ result.set(columnPath, values);
+ }
+
+ return result;
+ }
+
+ /**
+ * Resolves parameter keys for parameterized columns.
+ */
+ private async resolveParameterKeys(model: DataSourceModel): Promise<void> {
+ const {parameterKeyColumns} = model;
+
+ if (!parameterKeyColumns) {
+ return;
+ }
+
+ for (const prefix of parameterKeyColumns) {
+ // Already cached
+ if (this.parameterKeysCache.has(prefix)) {
+ continue;
+ }
+
+ const schema = this.sqlSchema[this.rootSchemaName];
+ const colDef = maybeUndefined(schema?.columns[prefix]);
+
+ if (colDef && isSQLExpressionDef(colDef) && colDef.parameterized) {
+ if (colDef.parameterKeysQuery) {
+ const baseTable = schema.table;
+ const baseAlias = `${baseTable}_0`;
+ const query = colDef.parameterKeysQuery(baseTable, baseAlias);
+
+ try {
+ const result = await runQueryForQueryTable(
+ this.wrapQueryWithPrelude(query),
+ this.engine,
+ );
+ const keys = result.rows.map((r) => String(r['key']));
+ this.parameterKeysCache.set(prefix, keys);
+ } catch {
+ this.parameterKeysCache.set(prefix, []);
}
- const field = 'field' in agg ? agg.field : null;
- if (!field) return null;
- const alias = this.pathToAlias(field);
- if (agg.function === 'ANY') {
- return `MIN(${field}) AS ${alias}`;
- }
- return `${agg.function}(${field}) AS ${alias}`;
- })
- .filter(Boolean)
- .join(', ');
-
- const groupByFields = pivot.groupBy.map(({field}) => field);
- if (groupByFields.length > 0) {
- const groupCols = groupByFields.join(', ');
- const selectCols = valCols ? `${groupCols}, ${valCols}` : groupCols;
- query = `
- SELECT ${selectCols}
- FROM (${this.baseQuery})
- GROUP BY ${groupCols}
- `;
- } else {
- query = `
- SELECT ${valCols}
- FROM (${this.baseQuery})
- `;
- }
- } else if (pivot?.drillDown) {
- // Drill-down mode
- query = `\nSELECT ${colNames.join(', ')} FROM (${this.baseQuery})`;
-
- const drillDownConditions = pivot.groupBy
- .map((col) => {
- const field = col.field;
- const value = pivot.drillDown![field];
- if (value === null) {
- return `${field} IS NULL`;
- }
- return `${field} = ${sqlValue(value)}`;
- })
- .join(' AND ');
-
- if (drillDownConditions) {
- query = `SELECT * FROM (${query}) WHERE ${drillDownConditions}`;
- }
- } else {
- query = `\nSELECT ${colNames.join(', ')} FROM (${this.baseQuery})`;
- }
-
- // Add WHERE clause for filters
- if (filters.length > 0) {
- const whereConditions = filters.map(simpleFilter2Sql).join(' AND ');
- query = `SELECT * FROM (${query}) WHERE ${whereConditions}`;
- }
-
- // Add ORDER BY clause - find sorted column from columns or pivot
- const sortedColumn = this.findSortedColumn(columns, pivot);
- if (sortedColumn) {
- const {field, direction} = sortedColumn;
- let columnExists: boolean;
- if (pivot && !pivot.drillDown) {
- const groupByFields = pivot.groupBy.map(({field}) => field);
- const aggregateFields = (pivot.aggregates ?? []).map((a) =>
- 'field' in a ? a.field : '__count__',
- );
- const pivotColumns = [...groupByFields, ...aggregateFields];
- columnExists = pivotColumns.includes(field);
- } else {
- columnExists = columns === undefined || colNames.includes(field);
- }
- if (columnExists) {
- query += `\nORDER BY ${field} ${direction.toUpperCase()}`;
+ }
}
}
+ }
- // Append aggregate suffix as a comment so changes trigger reload
- if (aggregateSuffix) {
- query += ` /* aggregates: ${aggregateSuffix} */`;
+ private wrapQueryWithPrelude(query: string): string {
+ if (this.prelude) {
+ return `${this.prelude};\n${query}`;
}
-
return query;
}
/**
- * Find the column that has sorting applied.
+ * Export all data with current filters/sorting applied.
*/
- private findSortedColumn(
- columns: ReadonlyArray<Column> | undefined,
- pivot?: Pivot,
- ): {field: string; direction: 'ASC' | 'DESC'} | undefined {
- // Check pivot groupBy columns for sort
- if (pivot) {
- for (const col of pivot.groupBy) {
- if (typeof col !== 'string' && col.sort) {
- return {field: col.field, direction: col.sort};
- }
- }
- // Check pivot aggregates for sort
- for (const agg of pivot.aggregates ?? []) {
- if (agg.sort) {
- const field = 'field' in agg ? agg.field : '__count__';
- return {field, direction: agg.sort};
- }
- }
+ async exportData(): Promise<Row[]> {
+ const workingQuery = this.rowsCache?.query;
+ if (!workingQuery) {
+ return [];
}
- // Check regular columns for sort
- if (columns) {
- for (const col of columns) {
- if (col.sort) {
- return {field: col.field, direction: col.sort};
- }
- }
- }
-
- return undefined;
+ const query = `SELECT * FROM (${workingQuery})`;
+ const result = await runQueryForQueryTable(
+ this.wrapQueryWithPrelude(query),
+ this.engine,
+ );
+ return result.rows;
}
/**
- * Builds a query for schema mode (with JOINs based on column paths).
+ * Builds a complete SQL query from the model.
*/
- private buildSchemaWorkingQuery(
- columns: ReadonlyArray<Column> | undefined,
- filters: ReadonlyArray<Filter>,
- pivot?: Pivot,
+ private buildQuery(
+ model: DataSourceModel,
+ options: {includeOrderBy: boolean},
): string {
- const resolver = new SQLSchemaResolver(
- this.sqlSchema!,
- this.rootSchemaName!,
- );
+ const {columns, filters = [], pivot} = model;
+
+ const resolver = new SQLSchemaResolver(this.sqlSchema, this.rootSchemaName);
const baseTable = resolver.getBaseTable();
const baseAlias = resolver.getBaseAlias();
// For pivot mode without drill-down, we build aggregates differently
if (pivot && !pivot.drillDown) {
- return this.buildSchemaPivotQuery(resolver, filters, pivot);
+ return this.buildPivotQuery(resolver, filters, pivot, options);
}
// Normal mode or drill-down: select individual columns
@@ -506,6 +526,13 @@
resolver.resolveColumnPath(filter.field);
}
+ // Resolve drill-down groupBy fields to ensure their JOINs are added
+ if (pivot?.drillDown) {
+ for (const col of pivot.groupBy) {
+ resolver.resolveColumnPath(col.field);
+ }
+ }
+
// If no columns specified, select all from base table
if (selectExprs.length === 0) {
selectExprs.push(`${baseAlias}.*`);
@@ -553,26 +580,38 @@
}
}
- // Add ORDER BY clause - find sorted column
- const sortedColumn = this.findSortedColumn(columns, pivot);
- if (sortedColumn) {
- const {field, direction} = sortedColumn;
- const sqlExpr = resolver.resolveColumnPath(field);
- if (sqlExpr) {
- query += `\nORDER BY ${sqlExpr} ${direction.toUpperCase()}`;
+ // Add ORDER BY clause if requested
+ if (options.includeOrderBy) {
+ const sortedColumn = this.findSortedColumn(columns, pivot);
+ if (sortedColumn) {
+ const {field, direction} = sortedColumn;
+ const sqlExpr = resolver.resolveColumnPath(field);
+ if (sqlExpr) {
+ query += `\nORDER BY ${sqlExpr} ${direction.toUpperCase()}`;
+ }
}
}
+ // Include column aggregates in the query string so changes trigger a reload
+ const aggregateSuffix = columns
+ ?.filter((c) => c.aggregate)
+ .map((c) => `${c.field}:${c.aggregate}`)
+ .join(',');
+ if (aggregateSuffix) {
+ query += ` /* aggregates: ${aggregateSuffix} */`;
+ }
+
return query;
}
/**
- * Builds a pivot query with GROUP BY and aggregations (schema mode).
+ * Builds a pivot query with GROUP BY and aggregations.
*/
- private buildSchemaPivotQuery(
+ private buildPivotQuery(
resolver: SQLSchemaResolver,
filters: ReadonlyArray<Filter>,
pivot: Pivot,
+ options: {includeOrderBy: boolean},
): string {
const baseTable = resolver.getBaseTable();
const baseAlias = resolver.getBaseAlias();
@@ -642,19 +681,21 @@
query += `\nGROUP BY ${groupByOrigExprs.join(', ')}`;
}
- // Add ORDER BY - find sorted column from pivot
- const sortedColumn = this.findSortedColumn(undefined, pivot);
- if (sortedColumn) {
- const {field, direction} = sortedColumn;
- const aggregateFields = aggregates.map((a) =>
- 'field' in a ? a.field : '__count__',
- );
- const pivotColumns = [...groupByFields, ...aggregateFields];
- if (pivotColumns.includes(field)) {
- const alias = groupByFields.includes(field)
- ? this.pathToAlias(field)
- : field;
- query += `\nORDER BY ${alias} ${direction.toUpperCase()}`;
+ // Add ORDER BY if requested
+ if (options.includeOrderBy) {
+ const sortedColumn = this.findSortedColumn(undefined, pivot);
+ if (sortedColumn) {
+ const {field, direction} = sortedColumn;
+ const aggregateFields = aggregates.map((a) =>
+ 'field' in a ? a.field : '__count__',
+ );
+ const pivotColumns = [...groupByFields, ...aggregateFields];
+ if (pivotColumns.includes(field)) {
+ const alias = groupByFields.includes(field)
+ ? this.pathToAlias(field)
+ : field;
+ query += `\nORDER BY ${alias} ${direction.toUpperCase()}`;
+ }
}
}
@@ -662,34 +703,70 @@
}
/**
- * Builds a distinct values query based on the current mode.
+ * Find the column that has sorting applied.
+ */
+ private findSortedColumn(
+ columns: ReadonlyArray<Column> | undefined,
+ pivot?: Pivot,
+ ): {field: string; direction: 'ASC' | 'DESC'} | undefined {
+ // In drill-down mode, we display flat columns, so only check those for sort
+ if (pivot?.drillDown) {
+ if (columns) {
+ for (const col of columns) {
+ if (col.sort) {
+ return {field: col.field, direction: col.sort};
+ }
+ }
+ }
+ return undefined;
+ }
+
+ // Check pivot groupBy columns for sort
+ if (pivot) {
+ for (const col of pivot.groupBy) {
+ if (typeof col !== 'string' && col.sort) {
+ return {field: col.field, direction: col.sort};
+ }
+ }
+ // Check pivot aggregates for sort
+ for (const agg of pivot.aggregates ?? []) {
+ if (agg.sort) {
+ const field = 'field' in agg ? agg.field : '__count__';
+ return {field, direction: agg.sort};
+ }
+ }
+ }
+
+ // Check regular columns for sort
+ if (columns) {
+ for (const col of columns) {
+ if (col.sort) {
+ return {field: col.field, direction: col.sort};
+ }
+ }
+ }
+
+ return undefined;
+ }
+
+ /**
+ * Builds a distinct values query.
*/
private buildDistinctValuesQuery(columnPath: string): string | undefined {
- if (this.useSchema) {
- const resolver = new SQLSchemaResolver(
- this.sqlSchema!,
- this.rootSchemaName!,
- );
- const sqlExpr = resolver.resolveColumnPath(columnPath);
- if (!sqlExpr) return undefined;
+ const resolver = new SQLSchemaResolver(this.sqlSchema, this.rootSchemaName);
+ const sqlExpr = resolver.resolveColumnPath(columnPath);
+ if (!sqlExpr) return undefined;
- const baseTable = resolver.getBaseTable();
- const baseAlias = resolver.getBaseAlias();
- const joinClauses = resolver.buildJoinClauses();
+ const baseTable = resolver.getBaseTable();
+ const baseAlias = resolver.getBaseAlias();
+ const joinClauses = resolver.buildJoinClauses();
- return `
- SELECT DISTINCT ${sqlExpr} AS value
- FROM ${baseTable} AS ${baseAlias}
- ${joinClauses}
- ORDER BY ${sqlExpr} IS NULL, ${sqlExpr}
- `;
- } else {
- return `
- SELECT DISTINCT ${columnPath} AS value
- FROM (${this.baseQuery})
- ORDER BY ${columnPath} IS NULL, ${columnPath}
- `;
- }
+ return `
+ SELECT DISTINCT ${sqlExpr} AS value
+ FROM ${baseTable} AS ${baseAlias}
+ ${joinClauses}
+ ORDER BY ${sqlExpr} IS NULL, ${sqlExpr}
+ `;
}
/**
@@ -731,75 +808,11 @@
}
}
- private async getRowCount(workingQuery: string): Promise<number> {
- const result = await this.engine.query(`
- WITH data AS (${workingQuery})
- SELECT COUNT(*) AS total_count
- FROM data
- `);
- return result.firstRow({total_count: NUM}).total_count;
- }
-
- private async getPivotAggregates(
- _columns: ReadonlyArray<Column> | undefined,
+ private async fetchPivotAggregates(
filters: ReadonlyArray<Filter>,
pivot: Pivot,
): Promise<Row> {
- if (this.useSchema) {
- return this.getSchemaPivotAggregates(filters, pivot);
- } else {
- return this.getSimplePivotAggregates(filters, pivot);
- }
- }
-
- private async getSimplePivotAggregates(
- filters: ReadonlyArray<Filter>,
- pivot: Pivot,
- ): Promise<Row> {
- let filteredBaseQuery = `SELECT * FROM (${this.baseQuery})`;
- if (filters.length > 0) {
- const whereConditions = filters.map(simpleFilter2Sql).join(' AND ');
- filteredBaseQuery = `SELECT * FROM (${filteredBaseQuery}) WHERE ${whereConditions}`;
- }
-
- const aggregates = pivot.aggregates ?? [];
- const selectClauses = aggregates
- .map((agg) => {
- if (agg.function === 'COUNT') {
- return `COUNT(*) AS __count__`;
- }
- const field = 'field' in agg ? agg.field : null;
- if (!field) return null;
- const alias = this.pathToAlias(field);
- if (agg.function === 'ANY') {
- return `NULL AS ${alias}`;
- }
- return `${agg.function}(${field}) AS ${alias}`;
- })
- .filter(Boolean)
- .join(', ');
-
- if (!selectClauses) {
- return {};
- }
-
- const query = `
- SELECT ${selectClauses}
- FROM (${filteredBaseQuery})
- `;
-
- const result = await runQueryForQueryTable(query, this.engine);
- return result.rows[0] ?? {};
- }
-
- private async getSchemaPivotAggregates(
- filters: ReadonlyArray<Filter>,
- pivot: Pivot,
- ): Promise<Row> {
- const resolver = new SQLSchemaResolver(
- this.sqlSchema!,
- this.rootSchemaName!,
- );
+ const resolver = new SQLSchemaResolver(this.sqlSchema, this.rootSchemaName);
const baseTable = resolver.getBaseTable();
const baseAlias = resolver.getBaseAlias();
@@ -843,8 +856,8 @@
if (filters.length > 0) {
const filterResolver = new SQLSchemaResolver(
- this.sqlSchema!,
- this.rootSchemaName!,
+ this.sqlSchema,
+ this.rootSchemaName,
);
const whereConditions = filters.map((filter) => {
const sqlExpr = filterResolver.resolveColumnPath(filter.field);
@@ -853,63 +866,18 @@
query += `\nWHERE ${whereConditions.join(' AND ')}`;
}
- const result = await runQueryForQueryTable(query, this.engine);
- return result.rows[0] ?? {};
- }
-
- private async getColumnAggregates(
- filters: ReadonlyArray<Filter>,
- columns: ReadonlyArray<Column>,
- ): Promise<Row> {
- if (this.useSchema) {
- return this.getSchemaColumnAggregates(filters, columns);
- } else {
- return this.getSimpleColumnAggregates(filters, columns);
- }
- }
-
- private async getSimpleColumnAggregates(
- filters: ReadonlyArray<Filter>,
- columns: ReadonlyArray<Column>,
- ): Promise<Row> {
- let filteredBaseQuery = `SELECT * FROM (${this.baseQuery})`;
- if (filters.length > 0) {
- const whereConditions = filters.map(simpleFilter2Sql).join(' AND ');
- filteredBaseQuery = `SELECT * FROM (${filteredBaseQuery}) WHERE ${whereConditions}`;
- }
-
- const selectClauses = columns
- .filter((col) => col.aggregate)
- .map((col) => {
- const func = col.aggregate!;
- if (func === 'ANY') {
- return `MIN(${col.field}) AS ${col.field}`;
- }
- return `${func}(${col.field}) AS ${col.field}`;
- })
- .join(', ');
-
- if (!selectClauses) {
- return {};
- }
-
- const query = `
- SELECT ${selectClauses}
- FROM (${filteredBaseQuery})
- `;
-
- const result = await runQueryForQueryTable(query, this.engine);
- return result.rows[0] ?? {};
- }
-
- private async getSchemaColumnAggregates(
- filters: ReadonlyArray<Filter>,
- columns: ReadonlyArray<Column>,
- ): Promise<Row> {
- const resolver = new SQLSchemaResolver(
- this.sqlSchema!,
- this.rootSchemaName!,
+ const result = await runQueryForQueryTable(
+ this.wrapQueryWithPrelude(query),
+ this.engine,
);
+ return result.rows[0] ?? {};
+ }
+
+ private async fetchColumnAggregates(
+ filters: ReadonlyArray<Filter>,
+ columns: ReadonlyArray<Column>,
+ ): Promise<Row> {
+ const resolver = new SQLSchemaResolver(this.sqlSchema, this.rootSchemaName);
const baseTable = resolver.getBaseTable();
const baseAlias = resolver.getBaseAlias();
@@ -955,57 +923,12 @@
query += `\nWHERE ${whereConditions.join(' AND ')}`;
}
- const result = await runQueryForQueryTable(query, this.engine);
+ const result = await runQueryForQueryTable(
+ this.wrapQueryWithPrelude(query),
+ this.engine,
+ );
return result.rows[0] ?? {};
}
-
- private async getRows(
- workingQuery: string,
- pagination?: Pagination,
- ): Promise<{offset: number; rows: Row[]}> {
- let query = `
- WITH data AS (${workingQuery})
- SELECT *
- FROM data
- `;
-
- if (pagination) {
- query += `LIMIT ${pagination.limit} OFFSET ${pagination.offset}`;
- }
-
- const result = await runQueryForQueryTable(query, this.engine);
-
- return {
- offset: pagination?.offset ?? 0,
- rows: result.rows,
- };
- }
-}
-
-function simpleFilter2Sql(filter: Filter): string {
- switch (filter.op) {
- case '=':
- case '!=':
- case '<':
- case '<=':
- case '>':
- case '>=':
- return `${filter.field} ${filter.op} ${sqlValue(filter.value)}`;
- case 'glob':
- return `${filter.field} GLOB ${sqlValue(filter.value)}`;
- case 'not glob':
- return `${filter.field} NOT GLOB ${sqlValue(filter.value)}`;
- case 'is null':
- return `${filter.field} IS NULL`;
- case 'is not null':
- return `${filter.field} IS NOT NULL`;
- case 'in':
- return `${filter.field} IN (${filter.value.map(sqlValue).join(', ')})`;
- case 'not in':
- return `${filter.field} NOT IN (${filter.value.map(sqlValue).join(', ')})`;
- default:
- assertUnreachable(filter);
- }
}
function sqlValue(value: SqlValue): string {
@@ -1025,21 +948,3 @@
if (!a || !b) return false;
return a.limit === b.limit && a.offset === b.offset;
}
-
-function arePivotsEqual(a?: Pivot, b?: Pivot): boolean {
- if (a === b) return true;
- if (a === undefined || b === undefined) return false;
-
- // Compare groupBy fields
- const aGroupBy = a.groupBy.map(({field}) => field).join(',');
- const bGroupBy = b.groupBy.map(({field}) => field).join(',');
- if (aGroupBy !== bGroupBy) return false;
-
- // Compare aggregates
- if (JSON.stringify(a.aggregates) !== JSON.stringify(b.aggregates)) {
- return false;
- }
- if (JSON.stringify(a.drillDown) !== JSON.stringify(b.drillDown)) return false;
-
- return true;
-}
diff --git a/ui/src/components/widgets/datagrid/sql_schema.ts b/ui/src/components/widgets/datagrid/sql_schema.ts
index d3427e5..10bc6ee 100644
--- a/ui/src/components/widgets/datagrid/sql_schema.ts
+++ b/ui/src/components/widgets/datagrid/sql_schema.ts
@@ -421,3 +421,41 @@
this.aliasCounter = 0;
}
}
+
+/**
+ * Creates a simple schema from a table name or subquery.
+ *
+ * This enables using SQLDataSource with arbitrary queries/tables without
+ * defining explicit column schemas. Columns are accessed directly by name.
+ *
+ * @param tableOrQuery A table name (e.g., 'slice') or subquery (e.g., 'SELECT * FROM slice')
+ * @param schemaName Optional name for the schema (defaults to 'query')
+ * @returns A SQLSchemaRegistry with a single schema entry
+ *
+ * Example usage:
+ * ```typescript
+ * const schema = createSimpleSchema('SELECT * FROM slice WHERE dur > 0');
+ * const dataSource = new SQLDataSource({
+ * engine,
+ * sqlSchema: schema,
+ * rootSchemaName: 'query',
+ * });
+ * ```
+ */
+export function createSimpleSchema(
+ tableOrQuery: string,
+ schemaName: string = 'query',
+): SQLSchemaRegistry {
+ // If it looks like a query (contains SELECT, spaces, etc.), wrap in parens
+ const isQuery =
+ tableOrQuery.trim().toUpperCase().startsWith('SELECT') ||
+ tableOrQuery.includes(' ');
+ const table = isQuery ? `(${tableOrQuery})` : tableOrQuery;
+
+ return {
+ [schemaName]: {
+ table,
+ columns: {}, // Empty columns - all column access falls through to direct access
+ },
+ };
+}
diff --git a/ui/src/plugins/dev.perfetto.ExplorePage/query_builder/builder.ts b/ui/src/plugins/dev.perfetto.ExplorePage/query_builder/builder.ts
index b3eed2b..1d45934 100644
--- a/ui/src/plugins/dev.perfetto.ExplorePage/query_builder/builder.ts
+++ b/ui/src/plugins/dev.perfetto.ExplorePage/query_builder/builder.ts
@@ -83,6 +83,7 @@
SplitPanelDrawerVisibility,
} from '../../../widgets/split_panel';
import {SQLDataSource} from '../../../components/widgets/datagrid/sql_data_source';
+import {createSimpleSchema} from '../../../components/widgets/datagrid/sql_schema';
import {QueryResponse} from '../../../components/query_table/queries';
import {addQueryResultsTab} from '../../../components/query_table/query_result_tab';
import {SqlSourceNode} from './nodes/sources/sql_source';
@@ -637,7 +638,8 @@
this.dataSource = new SQLDataSource({
engine,
- baseQuery: `SELECT * FROM ${result.tableName}`,
+ sqlSchema: createSimpleSchema(result.tableName),
+ rootSchemaName: 'query',
});
this.queryExecuted = true;
this.isQueryRunning = false;
