tests/analytics-engine-sql.unit.test.ts

Rama main · réplica de lectura del repositorio original.

import { afterEach, beforeEach, describe, expect, it, vi } from 'vitest'
import { lintAnalyticsEngineSql } from '../supabase/functions/_backend/utils/analyticsEngineSqlLint.ts'
import { collectAnalyticsEngineSqlFixtures } from './helpers/collectAnalyticsEngineSqlFixtures.ts'

describe('analytics engine sql lint rules', () => {
  it.concurrent('flags COUNT(*)', () => {
    expect(lintAnalyticsEngineSql('SELECT COUNT(*) AS total FROM device_info')).toEqual([
      expect.objectContaining({ rule: 'no-count-star' }),
    ])
  })

  it.concurrent('flags CASE WHEN inside argMax', () => {
    expect(lintAnalyticsEngineSql(
      'SELECT argMax(blob9, CASE WHEN blob9 != \'\' THEN timestamp ELSE toDateTime(\'1970-01-01 00:00:00\') END) FROM device_info',
    )).toEqual([
      expect.objectContaining({ rule: 'no-case-in-argmax' }),
    ])
  })

  it.concurrent.each([
    ['no-join', 'SELECT 1 FROM bandwidth_usage LEFT JOIN device_usage ON bandwidth_usage.blob1 = device_usage.blob1'],
    ['no-multiif', 'SELECT multiIf(blob4 != \'\', blob4, 1, \'ios\') FROM device_usage'],
    ['no-to-string', 'SELECT toString(toDate(timestamp)) AS date FROM app_log'],
    ['no-concat', 'SELECT concat(index1, \':\', blob1) FROM device_usage'],
  ])('flags %s', (rule, sql) => {
    expect(lintAnalyticsEngineSql(sql).map(issue => issue.rule)).toContain(rule)
  })

  it.concurrent('flags ORDER BY on a source column that is only projected under an alias', () => {
    expect(lintAnalyticsEngineSql(
      'SELECT blob2 AS action, timestamp AS created_at FROM app_log ORDER BY created_at ASC, blob2 ASC LIMIT 10',
    ).map(issue => issue.rule)).toContain('no-order-by-aliased-source-column')
    expect(lintAnalyticsEngineSql(
      'SELECT blob2 AS action, timestamp AS created_at FROM app_log ORDER BY timestamp ASC',
    ).map(issue => issue.rule)).toContain('no-order-by-aliased-source-column')
    expect(lintAnalyticsEngineSql(
      'SELECT blob2 AS action, timestamp AS created_at FROM app_log ORDER BY created_at ASC, action ASC LIMIT 10',
    ).map(issue => issue.rule)).not.toContain('no-order-by-aliased-source-column')
    expect(lintAnalyticsEngineSql(
      'SELECT blob2, count() AS total FROM app_log GROUP BY blob2 ORDER BY blob2',
    ).map(issue => issue.rule)).not.toContain('no-order-by-aliased-source-column')
    expect(lintAnalyticsEngineSql(
      'SELECT blob2 AS action FROM app_log ORDER BY action -- blob2',
    ).map(issue => issue.rule)).not.toContain('no-order-by-aliased-source-column')
    expect(lintAnalyticsEngineSql(
      'SELECT blob2 AS action FROM app_log ORDER BY action /* blob2 */ LIMIT 1',
    ).map(issue => issue.rule)).not.toContain('no-order-by-aliased-source-column')
    expect(lintAnalyticsEngineSql(
      'SELECT blob2 AS action FROM app_log WHERE blob3 != \'ORDER BY blob2\' ORDER BY action',
    ).map(issue => issue.rule)).not.toContain('no-order-by-aliased-source-column')
  })

  it.concurrent('accepts supported COUNT() and COUNT(DISTINCT) forms', () => {
    expect(lintAnalyticsEngineSql('SELECT COUNT() AS total FROM device_info')).toEqual([])
    expect(lintAnalyticsEngineSql('SELECT COUNT(DISTINCT blob1) AS total FROM device_info')).toEqual([])
  })

  it.concurrent('flags untyped NULL IF() branches', () => {
    expect(lintAnalyticsEngineSql(
      'SELECT min(if(blob2 = \'download_complete\' AND double1 > 0, double1, NULL)) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT min(if(blob2 IN (\'download_complete\', \'download_zip_complete\') AND double1 > 0, double1, NULL)) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT min(if(blob2 = \'download_complete\', NULL, double1)) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT min(if(blob2 = \'download_complete\', double1, null)) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT coalesce(double1, NULL) FROM app_log',
    ).map(issue => issue.rule)).not.toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT sum(if(blob2 = \'set\', 1, 0)) AS succeeded FROM app_log WHERE blob2 IN (\'set\', NULL)',
    ).map(issue => issue.rule)).not.toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT if(blob2 IN (\'set\', NULL), 1, 0) FROM app_log',
    ).map(issue => issue.rule)).not.toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT if(1, \'x)\', NULL) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT \'if(1, NULL, 2)\' FROM app_log',
    ).map(issue => issue.rule)).not.toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT 1 FROM app_log -- if(1, NULL, 0)',
    ).map(issue => issue.rule)).not.toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT 1 FROM app_log /* if(1, NULL, 0) */',
    ).map(issue => issue.rule)).not.toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT if(1, NULL, 0) FROM app_log -- if(1, NULL, 0)',
    ).map(issue => issue.rule)).toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT if(1, NULL /* ) */, 0) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT if(1 /* , */, NULL) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT if(1, /* NULL */, 0) FROM app_log',
    ).map(issue => issue.rule)).not.toContain('no-if-untyped-null')
    expect(lintAnalyticsEngineSql(
      'SELECT if(1, NULL -- )\n, 0) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-if-untyped-null')
  })

  it.concurrent('flags avgIf/sumIf/countIf because they expand to IF(..., NULL)', () => {
    expect(lintAnalyticsEngineSql(
      'SELECT avgIf(double1, blob2 IN (\'download_complete\', \'download_zip_complete\') AND double1 > 0) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT sumIf(double1, double1 > 0) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT countIf(blob2 = \'download_complete\') FROM app_log',
    ).map(issue => issue.rule)).toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT max(if(blob2 = \'download_complete\' AND double1 > 0, double1, 0.0)) FROM app_log',
    ).map(issue => issue.rule)).not.toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT \'avgIf(\' FROM app_log',
    ).map(issue => issue.rule)).not.toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT \'sumIf(\' FROM app_log',
    ).map(issue => issue.rule)).not.toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT \'countIf(\' FROM app_log',
    ).map(issue => issue.rule)).not.toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT 1 FROM app_log -- avgIf(double1, 1)',
    ).map(issue => issue.rule)).not.toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT 1 FROM app_log /* sumIf(double1, 1) */',
    ).map(issue => issue.rule)).not.toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT avgIf(double1, 1) FROM app_log -- countIf(1)',
    ).map(issue => issue.rule)).toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT \'it\\\'s avgIf(\' FROM app_log',
    ).map(issue => issue.rule)).not.toContain('no-conditional-agg-if')
    expect(lintAnalyticsEngineSql(
      'SELECT \'foo\\\\\', avgIf(double1, 1) FROM app_log',
    ).map(issue => issue.rule)).toContain('no-conditional-agg-if')
  })
})

describe('analytics engine sql fixtures', () => {
  beforeEach(() => {
    vi.unstubAllGlobals()
  })

  afterEach(() => {
    vi.unstubAllGlobals()
  })

  it('generates lint-clean queries for every Analytics Engine read path', async () => {
    const fixtures = await collectAnalyticsEngineSqlFixtures()

    expect(fixtures.length).toBeGreaterThan(30)

    const offenders = fixtures.flatMap((fixture) => {
      const issues = lintAnalyticsEngineSql(fixture.query)
      return issues.map(issue => ({ fixture: fixture.name, issue }))
    })

    expect(offenders, offenders.map(entry => `${entry.fixture}: ${entry.issue.rule}`).join('\n')).toEqual([])
  })
})