#!/usr/bin/env tarantool
local test = require("sqltester")
test:plan(1)

--!./tcltestrunner.lua
-- 2014-10-24
--
-- The author disclaims copyright to this source code.  In place of
-- a legal notice, here is a blessing:
--
--    May you do good and not evil.
--    May you find forgiveness for yourself and forgive others.
--    May you share freely, never taking more than you give.
--
---------------------------------------------------------------------------
--
-- This file implements regression tests for sql library.  The
-- focus of this script is testing automatic index creation logic,
-- and specifically ensuring that automatic indexes can be used with
-- co-routine subqueries.
--
-- ["set","testdir",[["file","dirname",["argv0"]]]]
-- ["source",[["testdir"],"\/tester.tcl"]]
-- Schema is from the Debian security database
--
test:do_execsql_test(
    "autoindex5-1.0",
    [[
        CREATE TABLE source_package_status
                (bug_name TEXT NOT NULL,
                 package INTEGER NOT NULL,
                 vulnerable INTEGER NOT NULL,
                 urgency TEXT NOT NULL,
                 PRIMARY KEY (bug_name, package));
        CREATE INDEX source_package_status_package
                    ON source_package_status(package);

        CREATE TABLE source_packages
                    (name TEXT NOT NULL,
                    release_t TEXT NOT NULL,
                    subrelease TEXT NOT NULL,
                    archive TEXT NOT NULL,
                    version TEXT NOT NULL,
                    version_id INTEGER NOT NULL DEFAULT 0,
                    PRIMARY KEY (name, release_t, subrelease, archive));

        CREATE TABLE bugs
                (name TEXT NOT NULL PRIMARY KEY,
                 cve_status TEXT NOT NULL
                     CHECK (cve_status IN
                            ('', 'CANDIDATE', 'ASSIGNED', 'RESERVED', 'REJECTED')),
                 not_for_us INTEGER NOT NULL CHECK (not_for_us IN (0, 1)),
                 description TEXT NOT NULL,
                 release_date TEXT NOT NULL,
                 source_file TEXT NOT NULL,
                 source_line INTEGER NOT NULL);

        CREATE TABLE package_notes
                (id INTEGER NOT NULL PRIMARY KEY,
                 bug_name TEXT NOT NULL,
                 package TEXT NOT NULL,
                 fixed_version TEXT
                     CHECK (fixed_version IS NULL OR fixed_version <> ''),
                 fixed_version_id INTEGER NOT NULL DEFAULT 0,
                 release_t TEXT NOT NULL,
                 package_kind TEXT NOT NULL DEFAULT 'unknown',
                 urgency TEXT NOT NULL,
                 bug_origin TEXT NOT NULL DEFAULT '');
        CREATE INDEX package_notes_package
                    ON package_notes(package);
        CREATE UNIQUE INDEX package_notes_bug
                    ON package_notes(bug_name, package, release_t);

        CREATE TABLE debian_bugs
                (bug INTEGER NOT NULL,
                 note INTEGER NOT NULL,
                 PRIMARY KEY (bug, note));


        CREATE VIEW debian_cve AS
                    SELECT DISTINCT debian_bugs.bug, st.bug_name
                    FROM package_notes, debian_bugs, source_package_status AS st
                    WHERE package_notes.bug_name = st.bug_name
                    AND debian_bugs.note = package_notes.id
                    ORDER BY debian_bugs.bug;
    ]], {
        -- <autoindex5-1.0>

        -- </autoindex5-1.0>
    })

-- The following query should use an automatic index for the view
-- in FROM clause of the subquery of the second result column.
--
-- do_execsql_test autoindex5-1.1 {
--   EXPLAIN QUERY PLAN
--   SELECT
--     st.bug_name,
--     (SELECT ALL debian_cve.bug FROM debian_cve
--       WHERE debian_cve.bug_name = st.bug_name
--       ORDER BY debian_cve.bug),
--     sp.release
--   FROM
--      source_package_status AS st,
--      source_packages AS sp,
--      bugs
--   WHERE
--      sp.rowid = st.package
--      AND st.bug_name = bugs.name
--      AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
--      AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release = 'jessie'
--             OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
--   ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
-- } {/SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX .bug_name=/}
test:finish_test()

