<commit_before>require('../support/test_helper.js');

var assert        = require('assert');
var Overviews     = require('../../lib/windshaft/utils/overviews');

function normalize_whitespace(txt) {
  return txt.replace(/\s+/g, " ").trim();
}

// compare SQL statements ignoring whitespace
function assertSameSql(sql1, sql2) {
    assert.equal(normalize_whitespace(sql1), normalize_whitespace(sql2));
}

describe('Overviews-support', function() {

  it('does not alter queries if no overviews data is present', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {};
    var overviews_sql = Overviews.query(sql, overviews);
    assert.equal(overviews_sql, sql);
    done();
  });


  it('does not alter queries which don\'t use overviews', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table2: {
          0: { table: 'table2_ov0' },
          1: { table: 'table2_ov1' },
          4: { table: 'table2_ov4' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    assert.equal(overviews_sql, sql);
    done();
  });

  // jshint multistr:true

  it('generates query with single overview layer for level 0', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table1: {
          0: { table: 'table1_ov0' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM table1_ov0, vovw_scale WHERE vovw_z = 0\
            UNION ALL\
            SELECT * FROM table1, vovw_scale WHERE vovw_z > 0\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query with single overview layer for level >0', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table1: {
          2: { table: 'table1_ov2' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM table1_ov2, vovw_scale WHERE vovw_z <= 2\
            UNION ALL\
            SELECT * FROM table1, vovw_scale WHERE vovw_z > 2\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query with multiple overview layers for all levels up to N', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table1: {
          0: { table: 'table1_ov0' },
          1: { table: 'table1_ov1' },
          2: { table: 'table1_ov2' },
          3: { table: 'table1_ov3' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM table1_ov0, vovw_scale WHERE vovw_z = 0\
            UNION ALL\
            SELECT * FROM table1_ov1, vovw_scale WHERE vovw_z = 1\
            UNION ALL\
            SELECT * FROM table1_ov2, vovw_scale WHERE vovw_z = 2\
            UNION ALL\
            SELECT * FROM table1_ov3, vovw_scale WHERE vovw_z = 3\
            UNION ALL\
            SELECT * FROM table1, vovw_scale WHERE vovw_z > 3\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query with multiple overview layers for random levels', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table1: {
          0: { table: 'table1_ov0' },
          1: { table: 'table1_ov1' },
          6: { table: 'table1_ov6' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM table1_ov0, vovw_scale WHERE vovw_z = 0\
            UNION ALL\
            SELECT * FROM table1_ov1, vovw_scale WHERE vovw_z = 1\
            UNION ALL\
            SELECT * FROM table1_ov6, vovw_scale WHERE vovw_z > 1 AND vovw_z <= 6\
            UNION ALL\
            SELECT * FROM table1, vovw_scale WHERE vovw_z > 6\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

});<commit_msg>Add tests for overviews queries with explicit schemas<commit_after>require('../support/test_helper.js');

var assert        = require('assert');
var Overviews     = require('../../lib/windshaft/utils/overviews');

function normalize_whitespace(txt) {
  return txt.replace(/\s+/g, " ").trim();
}

// compare SQL statements ignoring whitespace
function assertSameSql(sql1, sql2) {
    assert.equal(normalize_whitespace(sql1), normalize_whitespace(sql2));
}

describe('Overviews-support', function() {

  it('does not alter queries if no overviews data is present', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {};
    var overviews_sql = Overviews.query(sql, overviews);
    assert.equal(overviews_sql, sql);

    done();
  });


  it('does not alter queries which don\'t use overviews', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table2: {
          0: { table: 'table2_ov0' },
          1: { table: 'table2_ov1' },
          4: { table: 'table2_ov4' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    assert.equal(overviews_sql, sql);
    done();
  });

  // jshint multistr:true

  it('generates query with single overview layer for level 0', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table1: {
          0: { table: 'table1_ov0' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM table1_ov0, vovw_scale WHERE vovw_z = 0\
            UNION ALL\
            SELECT * FROM table1, vovw_scale WHERE vovw_z > 0\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query with single overview layer for level >0', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table1: {
          2: { table: 'table1_ov2' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM table1_ov2, vovw_scale WHERE vovw_z <= 2\
            UNION ALL\
            SELECT * FROM table1, vovw_scale WHERE vovw_z > 2\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query with multiple overview layers for all levels up to N', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table1: {
          0: { table: 'table1_ov0' },
          1: { table: 'table1_ov1' },
          2: { table: 'table1_ov2' },
          3: { table: 'table1_ov3' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM table1_ov0, vovw_scale WHERE vovw_z = 0\
            UNION ALL\
            SELECT * FROM table1_ov1, vovw_scale WHERE vovw_z = 1\
            UNION ALL\
            SELECT * FROM table1_ov2, vovw_scale WHERE vovw_z = 2\
            UNION ALL\
            SELECT * FROM table1_ov3, vovw_scale WHERE vovw_z = 3\
            UNION ALL\
            SELECT * FROM table1, vovw_scale WHERE vovw_z > 3\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query with multiple overview layers for random levels', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        table1: {
          0: { table: 'table1_ov0' },
          1: { table: 'table1_ov1' },
          6: { table: 'table1_ov6' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM table1_ov0, vovw_scale WHERE vovw_z = 0\
            UNION ALL\
            SELECT * FROM table1_ov1, vovw_scale WHERE vovw_z = 1\
            UNION ALL\
            SELECT * FROM table1_ov6, vovw_scale WHERE vovw_z > 1 AND vovw_z <= 6\
            UNION ALL\
            SELECT * FROM table1, vovw_scale WHERE vovw_z > 6\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query for a table with explicit schema', function(done){
    var sql = "SELECT * FROM public.table1";
    var overviews = {
        'public.table1': {
          2: { table: 'table1_ov2' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM public.table1_ov2, vovw_scale WHERE vovw_z <= 2\
            UNION ALL\
            SELECT * FROM public.table1, vovw_scale WHERE vovw_z > 2\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query for a table with explicit schema in the overviews info', function(done){
    var sql = "SELECT * FROM table1";
    var overviews = {
        'public.table1': {
          2: { table: 'table1_ov2' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM public.table1_ov2, vovw_scale WHERE vovw_z <= 2\
            UNION ALL\
            SELECT * FROM public.table1, vovw_scale WHERE vovw_z > 2\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query for a table that needs quoting with explicit schema', function(done){
    var sql = "SELECT * FROM public.\"table 1\"";
    var overviews = {
        'public."table 1"': {
          2: { table: '"table 1_ov2"' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          \"vovw_table 1\" AS (\
            SELECT * FROM public.\"table 1_ov2\", vovw_scale WHERE vovw_z <= 2\
            UNION ALL\
            SELECT * FROM public.\"table 1\", vovw_scale WHERE vovw_z > 2\
          )\
        SELECT * FROM \"vovw_table 1\"\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });

  it('generates query for a table with explicit schema that needs quoting', function(done){
    var sql = "SELECT * FROM \"user-1\".table1";
    var overviews = {
        '"user-1".table1': {
          2: { table: 'table1_ov2' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          vovw_table1 AS (\
            SELECT * FROM \"user-1\".table1_ov2, vovw_scale WHERE vovw_z <= 2\
            UNION ALL\
            SELECT * FROM \"user-1\".table1, vovw_scale WHERE vovw_z > 2\
          )\
        SELECT * FROM vovw_table1\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });


  it('generates query for a table with explicit schema both needing quoting', function(done){
    var sql = "SELECT * FROM \"user-1\".\"table 1\"";
    var overviews = {
        '"user-1"."table 1"': {
          2: { table: '"table 1_ov2"' }
        }
    };
    var overviews_sql = Overviews.query(sql, overviews);
    var expected_sql = "\
        WITH\
          vovw_scale AS ( SELECT CDB_ZoomFromScale(!scale_denominator!) AS vovw_z ),\
          \"vovw_table 1\" AS (\
            SELECT * FROM \"user-1\".\"table 1_ov2\", vovw_scale WHERE vovw_z <= 2\
            UNION ALL\
            SELECT * FROM \"user-1\".\"table 1\", vovw_scale WHERE vovw_z > 2\
          )\
        SELECT * FROM \"vovw_table 1\"\
    ";
    assertSameSql(overviews_sql, expected_sql);
    done();
  });



});
{'fraction_non_alphanumeric': 0.1151840490797546, 'fraction_numerical': 0.0200920245398773, 'mean_word_length': 2.0778852962001415, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2228715', 'n_tokens_mistral': 4467, 'n_tokens_neox': 4297, 'n_words': 1083}