#!/usr/bin/env tclsh

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test_on_specific_db {:memory:} create-index-quoted-identifiers {
    CREATE TABLE "t t" ("a a");
    CREATE INDEX "idx idx" ON "t t" ("a a");
    CREATE UNIQUE INDEX "unique idx idx" ON "t t" ("a a");

    SELECT sql FROM sqlite_schema WHERE type='index';
} {
    "CREATE INDEX \"idx idx\" ON \"t t\" (\"a a\")"
    "CREATE UNIQUE INDEX \"unique idx idx\" ON \"t t\" (\"a a\")"
}


# single-column index key: creating unique index fails with duplicates
do_execsql_test_in_memory_any_error create-unique-index-with-duplicates-1 {
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 1);
    INSERT INTO t1 VALUES(1, 2);
    CREATE UNIQUE INDEX idx1 ON t1(a);
}

# multi-column index key: creating unique index fails with duplicates
do_execsql_test_in_memory_any_error create-unique-index-with-duplicates-2 {
    CREATE TABLE t2(a, b, c);
    INSERT INTO t2 VALUES(1, 2, 3);
    INSERT INTO t2 VALUES(1, 2, 4);
    CREATE UNIQUE INDEX idx2 ON t2(a, b);
}


# single-column index key: creating unique index succeeds because NULLs are never equal
do_execsql_test_on_specific_db {:memory:} create-unique-index-with-duplicates-3 {
    CREATE TABLE t3(a);
    INSERT INTO t3 VALUES(NULL);
    INSERT INTO t3 VALUES(NULL);
    CREATE UNIQUE INDEX idx3 ON t3(a);
    SELECT count(*) FROM t3;
} {2}

# multi-column index key: creating unique index succeeds because NULLs are never equal
do_execsql_test_on_specific_db {:memory:} create-unique-index-with-duplicates-4 {
    CREATE TABLE t4(a, b);
    INSERT INTO t4 VALUES(1, NULL);
    INSERT INTO t4 VALUES(1, NULL);
    CREATE UNIQUE INDEX idx4 ON t4(a, b);
    SELECT count(*) FROM t4;
} {2}

# multi-column index key: creating unique index succeeds when all NULLs
do_execsql_test_on_specific_db {:memory:} create-unique-index-with-duplicates-5 {
    CREATE TABLE t5(a, b);
    INSERT INTO t5 VALUES(NULL, NULL);
    INSERT INTO t5 VALUES(NULL, NULL);
    CREATE UNIQUE INDEX idx5 ON t5(a, b);
    SELECT count(*) FROM t5;
} {2}

# creating index on rowid (pseudo-column) should fail
# SQLite: "no such column: rowid", Limbo: "invalid expression in CREATE INDEX: rowid"
do_execsql_test_in_memory_error create-index-on-rowid {
    CREATE TABLE t6(x);
    CREATE INDEX idx6 ON t6(rowid);
} {rowid}

# creating index on _rowid_ (pseudo-column alias) should fail
# SQLite: "no such column: _rowid_", Limbo: "invalid expression in CREATE INDEX: _rowid_"
do_execsql_test_in_memory_error create-index-on-rowid-alias-1 {
    CREATE TABLE t7(x);
    CREATE INDEX idx7 ON t7(_rowid_);
} {_rowid_}

# creating index on oid (pseudo-column alias) should fail
# SQLite: "no such column: oid", Limbo: "invalid expression in CREATE INDEX: oid"
do_execsql_test_in_memory_error create-index-on-rowid-alias-2 {
    CREATE TABLE t8(x);
    CREATE INDEX idx8 ON t8(oid);
} {oid}

# creating index on shadowed rowid column should succeed
do_execsql_test_on_specific_db {:memory:} create-index-on-shadowed-rowid {
    CREATE TABLE t9(rowid int, x);
    CREATE INDEX idx9 ON t9(rowid);
    SELECT name FROM sqlite_schema WHERE type='index';
} {idx9}

# creating index on shadowed _rowid_ column should succeed
do_execsql_test_on_specific_db {:memory:} create-index-on-shadowed-rowid-alias-1 {
    CREATE TABLE t10(_rowid_ int, x);
    CREATE INDEX idx10 ON t10(_rowid_);
    SELECT name FROM sqlite_schema WHERE type='index';
} {idx10}

# creating index on shadowed oid column should succeed
do_execsql_test_on_specific_db {:memory:} create-index-on-shadowed-rowid-alias-2 {
    CREATE TABLE t11(oid int, x);
    CREATE INDEX idx11 ON t11(oid);
    SELECT name FROM sqlite_schema WHERE type='index';
} {idx11}