--Setup extension
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION btree_gist;
CREATE EXTENSION powa;
-- Aggregate data every 5 snapshots
SET powa.coalesce = 5;
-- Test created ojects
SELECT * FROM powa_functions ORDER BY module, operation;
 srvid |          module          | operation |         function_name         |      query_source       | query_cleanup | added_manually | enabled | priority 
-------+--------------------------+-----------+-------------------------------+-------------------------+---------------+----------------+---------+----------
     0 | pg_stat_bgwriter         | aggregate | powa_stat_bgwriter_aggregate  |                         |               | f              | t       |       10
     0 | pg_stat_bgwriter         | purge     | powa_stat_bgwriter_purge      |                         |               | f              | t       |       10
     0 | pg_stat_bgwriter         | reset     | powa_stat_bgwriter_reset      |                         |               | f              | t       |       10
     0 | pg_stat_bgwriter         | snapshot  | powa_stat_bgwriter_snapshot   | powa_stat_bgwriter_src  |               | f              | t       |       10
     0 | pg_stat_statements       | aggregate | powa_statements_aggregate     |                         |               | f              | t       |       10
     0 | pg_stat_statements       | purge     | powa_statements_purge         |                         |               | f              | t       |       10
     0 | pg_stat_statements       | purge     | powa_databases_purge          |                         |               | f              | t       |       10
     0 | pg_stat_statements       | reset     | powa_statements_reset         |                         |               | f              | t       |       10
     0 | pg_stat_statements       | snapshot  | powa_databases_snapshot       | powa_databases_src      |               | f              | t       |       -3
     0 | pg_stat_statements       | snapshot  | powa_statements_snapshot      | powa_statements_src     |               | f              | t       |       -2
     0 | powa_stat_all_relations  | aggregate | powa_all_relations_aggregate  |                         |               | f              | t       |       10
     0 | powa_stat_all_relations  | purge     | powa_all_relations_purge      |                         |               | f              | t       |       10
     0 | powa_stat_all_relations  | reset     | powa_all_relations_reset      |                         |               | f              | t       |       10
     0 | powa_stat_all_relations  | snapshot  | powa_all_relations_snapshot   | powa_all_relations_src  |               | f              | t       |       10
     0 | powa_stat_user_functions | aggregate | powa_user_functions_aggregate |                         |               | f              | t       |       10
     0 | powa_stat_user_functions | purge     | powa_user_functions_purge     |                         |               | f              | t       |       10
     0 | powa_stat_user_functions | reset     | powa_user_functions_reset     |                         |               | f              | t       |       10
     0 | powa_stat_user_functions | snapshot  | powa_user_functions_snapshot  | powa_user_functions_src |               | f              | t       |       10
(18 rows)

-- test C SRFs
SELECT COUNT(*) = 0
FROM pg_database,
LATERAL powa_stat_user_functions(oid) f
WHERE datname = current_database();
 ?column? 
----------
 t
(1 row)

SELECT COUNT(*) > 10
FROM pg_database,
LATERAL powa_stat_all_rel(oid)
WHERE datname = current_database();
 ?column? 
----------
 t
(1 row)

-- Test snapshot
SELECT 1, COUNT(*) = 0 FROM powa_user_functions_history_current;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_all_relations_history_current;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_statements_history_current;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_statements_history_current_db;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_user_functions_history;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_all_relations_history;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT 1, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        1 | t
(1 row)

SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

SELECT 2, COUNT(*) >= 0 FROM powa_user_functions_history_current;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) > 0 FROM powa_all_relations_history_current;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) > 0 FROM powa_statements_history_current;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) > 0 FROM powa_statements_history_current_db;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) >= 0 FROM powa_user_functions_history;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) = 0 FROM powa_all_relations_history;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT 2, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        2 | t
(1 row)

SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

-- This snapshot will trigger the aggregate
SELECT powa_take_snapshot();
 powa_take_snapshot 
--------------------
                  0
(1 row)

SELECT 3, COUNT(*) >= 0 FROM powa_user_functions_history_current;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_all_relations_history_current;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_statements_history_current;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_statements_history_current_db;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) >= 0 FROM powa_user_functions_history;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_all_relations_history;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

SELECT 3, COUNT(*) > 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        3 | t
(1 row)

-- Test reset function
SELECT * from powa_reset(0);
 powa_reset 
------------
 t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_user_functions_history_current;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_all_relations_history_current;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_statements_history_current;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_statements_history_current_db;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_user_functions_history;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_all_relations_history;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

SELECT 4, COUNT(*) = 0 FROM powa_statements_history;
 ?column? | ?column? 
----------+----------
        4 | t
(1 row)

-- Check API
SELECT powa_register_server(hostname => '127.0.0.1',
    extensions => '{pg_qualstats}');
 powa_register_server 
----------------------
 t
(1 row)

SELECT COUNT(*) FROM powa_servers;
 count 
-------
     2
(1 row)

SELECT hostname FROM powa_servers WHERE id = 1;
 hostname  
-----------
 127.0.0.1
(1 row)

-- Check missing powa_statements FK for pg_qualstats doesn't prevent snapshot
INSERT INTO powa_qualstats_src_tmp(srvid, ts, uniquequalnodeid, dbid, userid,
    qualnodeid, occurences, execution_count, nbfiltered,
    mean_err_estimate_ratio, mean_err_estimate_num,
    queryid, constvalues, quals)
    SELECT 1, now(), 1, 1, 1,
        1, 1000, 1, 0,
        0, 0,
        1, '{}', ARRAY[(1259,1,607,'i')::qual_type];
SELECT count(*) FROM powa_qualstats_src_tmp;
 count 
-------
     1
(1 row)

SELECT powa_qualstats_snapshot(1);
 powa_qualstats_snapshot 
-------------------------
 
(1 row)

SELECT count(*) FROM powa_qualstats_src_tmp;
 count 
-------
     0
(1 row)