/**
* Warehouse Service
* Manages SQL Analytics / Data Warehouse operations in Microsoft Fabric
*/
import * as vscode from 'vscode';
import { FabricApiService } from '../core/api/fabric-api-service';
import { sanitizeSqlIdentifier, sanitizeSqlNumber, AuditLogger } from '../core/security';
export interface Warehouse {
id: string;
displayName: string;
description?: string;
workspaceId: string;
connectionString?: string;
state: 'Active' | 'Paused' | 'Resuming' | 'Scaling';
createdTime?: Date;
lastModifiedTime?: Date;
}
export interface WarehouseSchema {
name: string;
tables: WarehouseTable[];
views: WarehouseView[];
storedProcedures: StoredProcedure[];
}
export interface WarehouseTable {
name: string;
schemaName: string;
columns: TableColumn[];
rowCount?: number;
sizeBytes?: number;
distribution?: 'Hash' | 'Round_Robin' | 'Replicate';
partitionColumn?: string;
}
export interface WarehouseView {
name: string;
schemaName: string;
columns: TableColumn[];
definition?: string;
}
export interface TableColumn {
name: string;
dataType: string;
nullable: boolean;
isPrimaryKey: boolean;
isForeignKey: boolean;
maxLength?: number;
precision?: number;
scale?: number;
}
export interface StoredProcedure {
name: string;
schemaName: string;
parameters: ProcedureParameter[];
definition?: string;
lastModified?: Date;
}
export interface ProcedureParameter {
name: string;
dataType: string;
direction: 'IN' | 'OUT' | 'INOUT';
defaultValue?: string;
}
export interface QueryResult {
columns: string[];
rows: unknown[][];
rowCount: number;
executionTimeMs: number;
messages?: string[];
}
export interface QueryHistory {
id: string;
query: string;
status: 'Running' | 'Completed' | 'Failed' | 'Cancelled';
startTime: Date;
endTime?: Date;
rowCount?: number;
errorMessage?: string;
userId?: string;
}
export class WarehouseService {
private api: FabricApiService;
constructor() {
this.api = FabricApiService.getInstance();
}
async getWarehouses(workspaceId: string): Promise<Warehouse[]> {
try {
const result = await this.api.get<{ value: Warehouse[] }>(
`/workspaces/${workspaceId}/warehouses`
);
return result?.value || [];
} catch (error) {
console.error('Failed to get warehouses:', error);
return [];
}
}
async getWarehouse(workspaceId: string, warehouseId: string): Promise<Warehouse | null> {
try {
return await this.api.get<Warehouse>(
`/workspaces/${workspaceId}/warehouses/${warehouseId}`
);
} catch (error) {
console.error('Failed to get warehouse:', error);
return null;
}
}
async createWarehouse(workspaceId: string, name: string, description?: string): Promise<Warehouse | null> {
try {
return await this.api.post<Warehouse>(
`/workspaces/${workspaceId}/warehouses`,
{ displayName: name, description }
);
} catch (error) {
console.error('Failed to create warehouse:', error);
return null;
}
}
async deleteWarehouse(workspaceId: string, warehouseId: string): Promise<boolean> {
try {
await this.api.delete(`/workspaces/${workspaceId}/warehouses/${warehouseId}`);
return true;
} catch (error) {
console.error('Failed to delete warehouse:', error);
return false;
}
}
async getSchemas(workspaceId: string, warehouseId: string): Promise<WarehouseSchema[]> {
try {
const result = await this.api.get<{ value: WarehouseSchema[] }>(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/schemas`
);
return result?.value || [];
} catch (error) {
console.error('Failed to get schemas:', error);
return [];
}
}
async getTables(workspaceId: string, warehouseId: string, schemaName?: string): Promise<WarehouseTable[]> {
try {
const params: Record<string, string> = {};
if (schemaName) {
params.schema = schemaName;
}
const result = await this.api.get<{ value: WarehouseTable[] }>(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/tables`,
params
);
return result?.value || [];
} catch (error) {
console.error('Failed to get tables:', error);
return [];
}
}
async getViews(workspaceId: string, warehouseId: string, schemaName?: string): Promise<WarehouseView[]> {
try {
const params: Record<string, string> = {};
if (schemaName) {
params.schema = schemaName;
}
const result = await this.api.get<{ value: WarehouseView[] }>(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/views`,
params
);
return result?.value || [];
} catch (error) {
console.error('Failed to get views:', error);
return [];
}
}
async getStoredProcedures(workspaceId: string, warehouseId: string, schemaName?: string): Promise<StoredProcedure[]> {
try {
const params: Record<string, string> = {};
if (schemaName) {
params.schema = schemaName;
}
const result = await this.api.get<{ value: StoredProcedure[] }>(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/storedProcedures`,
params
);
return result?.value || [];
} catch (error) {
console.error('Failed to get stored procedures:', error);
return [];
}
}
async executeQuery(workspaceId: string, warehouseId: string, query: string): Promise<QueryResult | null> {
try {
const startTime = Date.now();
const result = await this.api.post<QueryResult>(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/query`,
{ query }
);
if (result) {
result.executionTimeMs = Date.now() - startTime;
}
return result;
} catch (error) {
console.error('Failed to execute query:', error);
return null;
}
}
async cancelQuery(workspaceId: string, warehouseId: string, queryId: string): Promise<boolean> {
try {
await this.api.post(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/queries/${queryId}/cancel`,
{}
);
return true;
} catch (error) {
console.error('Failed to cancel query:', error);
return false;
}
}
async getQueryHistory(workspaceId: string, warehouseId: string, maxResults: number = 100): Promise<QueryHistory[]> {
try {
const result = await this.api.get<{ value: QueryHistory[] }>(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/queryHistory`,
{ top: maxResults }
);
return result?.value || [];
} catch (error) {
console.error('Failed to get query history:', error);
return [];
}
}
async getTablePreview(workspaceId: string, warehouseId: string, schemaName: string, tableName: string, maxRows: number = 100): Promise<QueryResult | null> {
// CRIT-003: Sanitize all SQL identifiers to prevent SQL injection
const safeSchema = sanitizeSqlIdentifier(schemaName);
const safeTable = sanitizeSqlIdentifier(tableName);
const safeMaxRows = sanitizeSqlNumber(Math.min(maxRows, 1000)); // Cap at 1000 rows
const query = `SELECT TOP ${safeMaxRows} * FROM ${safeSchema}.${safeTable}`;
AuditLogger.log('SQL_QUERY_PREVIEW', { schemaName, tableName, maxRows: safeMaxRows });
return this.executeQuery(workspaceId, warehouseId, query);
}
async getTableDDL(workspaceId: string, warehouseId: string, schemaName: string, tableName: string): Promise<string | null> {
try {
// Validate identifiers before use in API path
const safeSchema = sanitizeSqlIdentifier(schemaName).replace(/^\[|\]$/g, '');
const safeTable = sanitizeSqlIdentifier(tableName).replace(/^\[|\]$/g, '');
const result = await this.api.get<{ ddl: string }>(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/tables/${encodeURIComponent(safeSchema)}.${encodeURIComponent(safeTable)}/ddl`
);
return result?.ddl || null;
} catch (error) {
console.error('Failed to get table DDL:', error);
return null;
}
}
async pauseWarehouse(workspaceId: string, warehouseId: string): Promise<boolean> {
try {
await this.api.post(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/pause`,
{}
);
return true;
} catch (error) {
console.error('Failed to pause warehouse:', error);
return false;
}
}
async resumeWarehouse(workspaceId: string, warehouseId: string): Promise<boolean> {
try {
await this.api.post(
`/workspaces/${workspaceId}/warehouses/${warehouseId}/resume`,
{}
);
return true;
} catch (error) {
console.error('Failed to resume warehouse:', error);
return false;
}
}
getDataTypeIcon(dataType: string): string {
const type = dataType.toLowerCase();
if (type.includes('int') || type.includes('decimal') || type.includes('float') || type.includes('numeric')) {
return '$(symbol-number)';
}
if (type.includes('char') || type.includes('text') || type.includes('string')) {
return '$(symbol-string)';
}
if (type.includes('date') || type.includes('time')) {
return '$(calendar)';
}
if (type.includes('bool') || type.includes('bit')) {
return '$(symbol-boolean)';
}
if (type.includes('binary') || type.includes('varbinary')) {
return '$(file-binary)';
}
return '$(symbol-field)';
}
formatBytes(bytes?: number): string {
if (!bytes) { return 'N/A'; }
const units = ['B', 'KB', 'MB', 'GB', 'TB'];
let size = bytes;
let unitIndex = 0;
while (size >= 1024 && unitIndex < units.length - 1) {
size /= 1024;
unitIndex++;
}
return `${size.toFixed(1)} ${units[unitIndex]}`;
}
}
