pg_query 0.7.0

#![allow(non_snake_case)]
#![cfg(test)]

use pg_query::{fingerprint, Error};

#[test]
fn it_can_fingerprint_a_simple_statement() {
    let result = fingerprint("SELECT * FROM contacts.person WHERE id IN (1, 2, 3, 4);").unwrap();
    assert_eq!(result.hex, "643d2a3c294ab8a7");
}

#[test]
fn it_will_error_on_invalid_input() {
    let error = fingerprint("CREATE RANDOM ix_test ON contacts.person;").err().unwrap();
    assert_eq!(error, Error::Parse("syntax error at or near \"RANDOM\"".into()));
}

#[test]
fn it_works_for_multi_statement_queries() {
    let q1 = "SET x=?; SELECT A";
    let q2 = "SET x=?; SELECT a";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);

    let q1 = "SET x=?; SELECT A";
    let q2 = "SELECT a";
    assert_ne!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
}

#[test]
fn it_ignores_aliases() {
    let q1 = "SELECT a AS b";
    let q2 = "SELECT a AS c";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);

    let q1 = "SELECT a";
    let q2 = "SELECT a AS c";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);

    let q1 = "SELECT * FROM a AS b";
    let q2 = "SELECT * FROM a AS c";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);

    let q1 = "SELECT * FROM a";
    let q2 = "SELECT * FROM a AS c";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);

    let q1 = "SELECT * FROM (SELECT * FROM x AS y) AS a";
    let q2 = "SELECT * FROM (SELECT * FROM x AS z) AS b";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);

    let q1 = "SELECT a AS b UNION SELECT x AS y";
    let q2 = "SELECT a AS c UNION SELECT x AS z";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
}

// (pending in the Ruby test suite)
// #[test]
// fn it_ignores_aliases_referenced_in_query() {
//     let q1 = "SELECT s1.id FROM snapshots s1";
//     let q2 = "SELECT s2.id FROM snapshots s2";
//     assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
//     let q1 = "SELECT a AS b ORDER BY b";
//     let q2 = "SELECT a AS c ORDER BY c";
//     assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
// }

#[test]
fn it_ignores_param_references() {
    let q1 = "SELECT $1";
    let q2 = "SELECT $2";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
}

#[test]
fn it_ignores_SELECT_target_list_ordering() {
    let q1 = "SELECT a, b FROM x";
    let q2 = "SELECT b, a FROM x";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
    let q1 = "SELECT ?, b FROM x";
    let q2 = "SELECT b, ? FROM x";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
    let q1 = "SELECT ?, ?, b FROM x";
    let q2 = "SELECT ?, b, ? FROM x";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);

    // Testing uniqueness
    let q1 = "SELECT a, c FROM x";
    let q2 = "SELECT b, a FROM x";
    assert_ne!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
    let q1 = "SELECT b FROM x";
    let q2 = "SELECT b, a FROM x";
    assert_ne!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
}

#[test]
fn it_ignores_INSERT_col_ordering() {
    let q1 = "INSERT INTO test (a, b) VALUES (?, ?)";
    let q2 = "INSERT INTO test (b, a) VALUES (?, ?)";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);

    // Testing uniqueness
    let q1 = "INSERT INTO test (a, c) VALUES (?, ?)";
    let q2 = "INSERT INTO test (b, a) VALUES (?, ?)";
    assert_ne!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
    let q1 = "INSERT INTO test (b) VALUES (?, ?)";
    let q2 = "INSERT INTO test (b, a) VALUES (?, ?)";
    assert_ne!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
}

#[test]
fn it_ignores_IN_list_size() {
    let q1 = "SELECT * FROM x WHERE y IN (?, ?, ?)";
    let q2 = "SELECT * FROM x WHERE y IN (?)";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);

    let q1 = "SELECT * FROM x WHERE y IN ( ?::uuid, ?::uuid, ?::uuid )";
    let q2 = "SELECT * FROM x WHERE y IN ( ?::uuid )";
    assert_eq!(fingerprint(q1).unwrap().hex, fingerprint(q2).unwrap().hex);
}

#[test]
fn it_works() {
    let result = fingerprint("SELECT 1").unwrap();
    assert_eq!(result.hex, "50fde20626009aba");

    let result = fingerprint("SELECT 2").unwrap();
    assert_eq!(result.hex, "50fde20626009aba");

    let result = fingerprint("SELECT ?").unwrap();
    assert_eq!(result.hex, "50fde20626009aba");

    let result = fingerprint("SELECT $1").unwrap();
    assert_eq!(result.hex, "50fde20626009aba");

    let result = fingerprint("SELECT 1; SELECT a FROM b").unwrap();
    assert_eq!(result.hex, "3efa3b10d558d06d");

    let result = fingerprint("SELECT COUNT(DISTINCT id), * FROM targets WHERE something IS NOT NULL AND elsewhere::interval < now()").unwrap();
    assert_eq!(result.hex, "4380dd02d56bbe1a");

    let result = fingerprint("INSERT INTO test (a, b) VALUES (?, ?)").unwrap();
    assert_eq!(result.hex, "51e63b8083b48bdd");

    let result = fingerprint("INSERT INTO test (b, a) VALUES (?, ?)").unwrap();
    assert_eq!(result.hex, "51e63b8083b48bdd");

    let result =
        fingerprint("INSERT INTO test (a, b) VALUES (ARRAY[?, ?, ?, ?], ?::timestamptz), (ARRAY[?, ?, ?, ?], ?::timestamptz), (?, ?::timestamptz)")
            .unwrap();
    assert_eq!(result.hex, "4dfdd5260cac5acf");

    let result = fingerprint("SELECT b AS x, a AS y FROM z").unwrap();
    assert_eq!(result.hex, "1a8bf5d7614de3a5");

    let result = fingerprint("SELECT * FROM x WHERE y = ?").unwrap();
    assert_eq!(result.hex, "4ff39426bd074231");

    let result = fingerprint("SELECT * FROM x WHERE y = ANY ($1)").unwrap();
    assert_eq!(result.hex, "4ff39426bd074231");

    let result = fingerprint("SELECT * FROM x WHERE y IN (?)").unwrap();
    assert_eq!(result.hex, "4ff39426bd074231");

    let result = fingerprint("SELECT * FROM x WHERE y IN (?, ?, ?)").unwrap();
    assert_eq!(result.hex, "4ff39426bd074231");

    let result = fingerprint("SELECT * FROM x WHERE y IN ( ?::uuid )").unwrap();
    assert_eq!(result.hex, "4ff39426bd074231");

    let result = fingerprint("SELECT * FROM x WHERE y IN ( ?::uuid, ?::uuid, ?::uuid )").unwrap();
    assert_eq!(result.hex, "4ff39426bd074231");

    let result = fingerprint("PREPARE a123 AS SELECT a").unwrap();
    assert_eq!(result.hex, "9b5e6ead8be993e8");

    let result = fingerprint("EXECUTE a123").unwrap();
    assert_eq!(result.hex, "44ef1d2beabd53e8");

    let result = fingerprint("DEALLOCATE a123").unwrap();
    assert_eq!(result.hex, "d8a65a814fbc5f95");

    let result = fingerprint("DEALLOCATE ALL").unwrap();
    assert_eq!(result.hex, "d8a65a814fbc5f95");

    let result = fingerprint("EXPLAIN ANALYZE SELECT a").unwrap();
    assert_eq!(result.hex, "82845c1b5c6102e5");

    let result = fingerprint("WITH a AS (SELECT * FROM x WHERE x.y = ? AND x.z = 1) SELECT * FROM a").unwrap();
    assert_eq!(result.hex, "6831e38bbb3dd18c");

    let result =
        fingerprint("CREATE TABLE types (a float(2), b float(49), c NUMERIC(2, 3), d character(4), e char(5), f varchar(6), g character varying(7))")
            .unwrap();
    assert_eq!(result.hex, "008d6ba4aa0f4c6e");

    let result =
        fingerprint("CREATE VIEW view_a (a, b) AS WITH RECURSIVE view_a (a, b) AS (SELECT * FROM a(1)) SELECT \"a\", \"b\" FROM \"view_a\"").unwrap();
    assert_eq!(result.hex, "6236405577a6cea6");

    let result = fingerprint("VACUUM FULL my_table").unwrap();
    assert_eq!(result.hex, "fdf2f4127644f4d8");

    let result = fingerprint("SELECT * FROM x AS a, y AS b").unwrap();
    assert_eq!(result.hex, "4e9acae841dae228");

    let result = fingerprint("SELECT * FROM y AS a, x AS b").unwrap();
    assert_eq!(result.hex, "4e9acae841dae228");

    let result = fingerprint("SELECT x AS a, y AS b FROM x").unwrap();
    assert_eq!(result.hex, "65dff5f5e9a643ad");

    let result = fingerprint("SELECT y AS a, x AS b FROM x").unwrap();
    assert_eq!(result.hex, "65dff5f5e9a643ad");

    let result = fingerprint("SELECT x, y FROM z").unwrap();
    assert_eq!(result.hex, "330267237da5535f");

    let result = fingerprint("SELECT y, x FROM z").unwrap();
    assert_eq!(result.hex, "330267237da5535f");

    let result = fingerprint("INSERT INTO films (code, title, did) VALUES ('UA502', 'Bananas', 105), ('T_601', 'Yojimbo', DEFAULT)").unwrap();
    assert_eq!(result.hex, "459fdc70778b841e");

    let result = fingerprint("INSERT INTO films (code, title, did) VALUES (?, ?, ?)").unwrap();
    assert_eq!(result.hex, "459fdc70778b841e");

    let result = fingerprint("SELECT * FROM a").unwrap();
    assert_eq!(result.hex, "fcf44da7b597ef43");

    let result = fingerprint("SELECT * FROM a AS b").unwrap();
    assert_eq!(result.hex, "fcf44da7b597ef43");

    let result = fingerprint("UPDATE users SET one_thing = $1, second_thing = $2 WHERE users.id = ?").unwrap();
    assert_eq!(result.hex, "a0ea386c1cfd1e69");

    let result = fingerprint("UPDATE users SET something_else = $1 WHERE users.id = ?").unwrap();
    assert_eq!(result.hex, "3172bc3e0d631d55");

    let result = fingerprint("UPDATE users SET something_else = (SELECT a FROM x WHERE uid = users.id LIMIT 1) WHERE users.id = ?").unwrap();
    assert_eq!(result.hex, "f1127a8b91fbecbf");

    let result = fingerprint("SAVEPOINT some_id").unwrap();
    assert_eq!(result.hex, "8ebd566ea1bf947b");

    let result = fingerprint("RELEASE some_id").unwrap();
    assert_eq!(result.hex, "60d618658252d2af");

    let result = fingerprint("PREPARE TRANSACTION 'some_id'").unwrap();
    assert_eq!(result.hex, "d993959a33d627d4");

    let result = fingerprint("START TRANSACTION READ WRITE").unwrap();
    assert_eq!(result.hex, "4ca25828c835d55a");

    let result = fingerprint("DECLARE cursor_123 CURSOR FOR SELECT * FROM test WHERE id = 123").unwrap();
    assert_eq!(result.hex, "0119adaeb91afad0");

    let result = fingerprint("FETCH 1000 FROM cursor_123").unwrap();
    assert_eq!(result.hex, "37f4d2f6a957ae48");

    let result = fingerprint("CLOSE cursor_123").unwrap();
    assert_eq!(result.hex, "2c7963684fc2bad9");

    let result = fingerprint("-- nothing").unwrap();
    assert_eq!(result.hex, "d8d13f8b2da6c9ad");

    let result = fingerprint("CREATE FOREIGN TABLE ft1 () SERVER no_server").unwrap();
    assert_eq!(result.hex, "74481c4af7c76be1");

    let result = fingerprint("UPDATE x SET a = 1, b = 2, c = 3").unwrap();
    assert_eq!(result.hex, "fd5c248c0e642ce4");

    let result = fingerprint("UPDATE x SET z = now()").unwrap();
    assert_eq!(result.hex, "78fe872f5ec28674");

    let result = fingerprint("CREATE TEMPORARY TABLE my_temp_table (test_id integer NOT NULL) ON COMMIT DROP").unwrap();
    assert_eq!(result.hex, "1407ed5c5bb00967");

    let result = fingerprint("CREATE TEMPORARY TABLE my_temp_table AS SELECT 1").unwrap();
    assert_eq!(result.hex, "dd5fac57c3c4524c");

    let result = fingerprint("SELECT INTERVAL (0) $2").unwrap();
    assert_eq!(result.hex, "50fde20626009aba");

    let result = fingerprint("SELECT INTERVAL (2) $2").unwrap();
    assert_eq!(result.hex, "50fde20626009aba");

    let result = fingerprint("SELECT * FROM t WHERE t.a IN (1, 2) AND t.b = 3").unwrap();
    assert_eq!(result.hex, "346aea01be9173b6");

    let result = fingerprint("SELECT * FROM t WHERE t.b = 3 AND t.a IN (1, 2)").unwrap();
    assert_eq!(result.hex, "346aea01be9173b6");

    let result = fingerprint("SELECT * FROM t WHERE a && '[1,2]'").unwrap();
    assert_eq!(result.hex, "673f199f13dfe665");

    let result = fingerprint("SELECT * FROM t WHERE a && '[1,2]'::int4range").unwrap();
    assert_eq!(result.hex, "673f199f13dfe665");

    let result = fingerprint("SELECT * FROM t_20210301_x").unwrap();
    assert_eq!(result.hex, "6f8169980cd70a25");

    let result = fingerprint("SELECT * FROM t_20210302_x").unwrap();
    assert_eq!(result.hex, "6f8169980cd70a25");

    let result = fingerprint("SELECT * FROM t_20210302_y").unwrap();
    assert_eq!(result.hex, "d357dac4a24fcf1b");

    let result = fingerprint("SELECT * FROM t_1").unwrap();
    assert_eq!(result.hex, "018bd9230646143e");

    let result = fingerprint("SELECT * FROM t_2").unwrap();
    assert_eq!(result.hex, "3f1444da570c1a66");
}