Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

657 lines (575 sloc) 15.173 kb
\unset ECHO
\i test_setup.sql
SELECT plan(228);
--SELECT * FROM no_plan();
-- This will be rolled back. :-)
SET client_min_messages = warning;
CREATE TABLE public.sometab(
id INT NOT NULL PRIMARY KEY,
name TEXT DEFAULT '',
numb NUMERIC(10, 2),
myint NUMERIC(8)
);
CREATE INDEX idx_hey ON public.sometab(numb);
CREATE INDEX idx_foo ON public.sometab using hash(name);
CREATE INDEX idx_bar ON public.sometab(numb, name);
CREATE UNIQUE INDEX idx_baz ON public.sometab(LOWER(name));
RESET client_min_messages;
/****************************************************************************/
-- Test has_index().
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_hey', 'numb', 'whatever' ),
true,
'has_index() single column',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_hey', 'numb'::name ),
true,
'has_index() single column no desc',
'Index idx_hey should exist',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_foo', 'name', 'whatever' ),
true,
'has_index() hash index',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_foo', 'name'::name ),
true,
'has_index() hash index no desc',
'Index idx_foo should exist',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_bar', ARRAY['numb', 'name'], 'whatever' ),
true,
'has_index() multi-column',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_bar', ARRAY['numb', 'name'] ),
true,
'has_index() multi-column no desc',
'Index idx_bar should exist',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_baz', 'lower(name)', 'whatever' ),
true,
'has_index() functional',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_baz', 'whatever' ),
true,
'has_index() no cols',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_foo', 'whatever' ),
true,
'has_index() hash index',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_baz'::name ),
true,
'has_index() no cols no desc',
'Index idx_baz should exist',
''
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_foo'::name ),
true,
'has_index() no cols hash index no desc',
'Index idx_foo should exist',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_foo', 'name', 'whatever' ),
true,
'has_index() no schema single column',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_foo', 'name'::name ),
true,
'has_index() no schema single column no desc',
'Index idx_foo should exist',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_bar', ARRAY['numb', 'name'], 'whatever' ),
true,
'has_index() no schema multi-column',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_bar', ARRAY['numb', 'name'] ),
true,
'has_index() no schema multi-column no desc',
'Index idx_bar should exist',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_baz', 'lower(name)', 'whatever' ),
true,
'has_index() no schema functional',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_baz', 'lower(name)' ),
true,
'has_index() no schema functional no desc',
'Index idx_baz should exist',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_baz', 'whatever' ),
true,
'has_index() no schema or cols',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_foo', 'whatever' ),
true,
'has_index() hash index no schema or cols',
'whatever',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_baz' ),
true,
'has_index() no schema or cols or desc',
'Index idx_baz should exist',
''
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_foo' ),
true,
'has_index() hash index no schema or cols or desc',
'Index idx_foo should exist',
''
);
-- Check failure diagnostics.
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_heya', 'numb', 'whatever' ),
false,
'has_index() non-existent',
'whatever',
'Index idx_heya ON public.sometab not found'
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'blah', ARRAY['numb', 'name'], 'whatever' ),
false,
'has_index() missing',
'whatever',
'Index blah ON public.sometab not found'
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_bar', ARRAY['name', 'id'], 'whatever' ),
false,
'has_index() invalid',
'whatever',
' have: idx_bar ON public.sometab(numb, name)
want: idx_bar ON public.sometab(name, id)'
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_bar', ARRAY['name'], 'whatever' ),
false,
'has_index() missing column',
'whatever',
' have: idx_bar ON public.sometab(numb, name)
want: idx_bar ON public.sometab(name)'
);
SELECT * FROM check_test(
has_index( 'sometab', 'blah', ARRAY['numb', 'name'], 'whatever' ),
false,
'has_index() missing no schema',
'whatever',
'Index blah ON sometab not found'
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_bar', ARRAY['name', 'id'], 'whatever' ),
false,
'has_index() invalid no schema',
'whatever',
' have: idx_bar ON sometab(numb, name)
want: idx_bar ON sometab(name, id)'
);
SELECT * FROM check_test(
has_index( 'public', 'sometab', 'idx_baz', 'lower(wank)', 'whatever' ),
false,
'has_index() functional fail',
'whatever',
' have: idx_baz ON public.sometab(lower(name))
want: idx_baz ON public.sometab(lower(wank))'
);
SELECT * FROM check_test(
has_index( 'sometab', 'idx_baz', 'lower(wank)', 'whatever' ),
false,
'has_index() functional fail no schema',
'whatever',
' have: idx_baz ON sometab(lower(name))
want: idx_baz ON sometab(lower(wank))'
);
/****************************************************************************/
-- Test hasnt_index().
SELECT * FROM check_test(
hasnt_index( 'public', 'sometab', 'idx_foo', 'whatever' ),
false,
'hasnt_index(schema, table, index, desc)',
'whatever',
''
);
SELECT * FROM check_test(
hasnt_index( 'public', 'sometab', 'idx_foo'::name ),
false,
'hasnt_index(schema, table, index)',
'Index idx_foo should not exist',
''
);
SELECT * FROM check_test(
hasnt_index( 'public', 'sometab', 'idx_blah', 'whatever' ),
true,
'hasnt_index(schema, table, non-index, desc)',
'whatever',
''
);
SELECT * FROM check_test(
hasnt_index( 'public', 'sometab', 'idx_blah'::name ),
true,
'hasnt_index(schema, table, non-index)',
'Index idx_blah should not exist',
''
);
SELECT * FROM check_test(
hasnt_index( 'sometab', 'idx_foo', 'whatever' ),
false,
'hasnt_index(table, index, desc)',
'whatever',
''
);
SELECT * FROM check_test(
hasnt_index( 'sometab', 'idx_foo'::name ),
false,
'hasnt_index(table, index)',
'Index idx_foo should not exist',
''
);
SELECT * FROM check_test(
hasnt_index( 'sometab', 'idx_blah', 'whatever' ),
true,
'hasnt_index(table, non-index, desc)',
'whatever',
''
);
SELECT * FROM check_test(
hasnt_index( 'sometab', 'idx_blah'::name ),
true,
'hasnt_index(table, non-index)',
'Index idx_blah should not exist',
''
);
/****************************************************************************/
-- Test index_is_unique().
SELECT * FROM check_test(
index_is_unique( 'public', 'sometab', 'idx_baz', 'whatever' ),
true,
'index_is_unique()',
'whatever',
''
);
SELECT * FROM check_test(
index_is_unique( 'public', 'sometab', 'idx_baz' ),
true,
'index_is_unique() no desc',
'Index idx_baz should be unique',
''
);
SELECT * FROM check_test(
index_is_unique( 'sometab', 'idx_baz' ),
true,
'index_is_unique() no schema',
'Index idx_baz should be unique',
''
);
SELECT * FROM check_test(
index_is_unique( 'idx_baz' ),
true,
'index_is_unique() index only',
'Index idx_baz should be unique',
''
);
SELECT * FROM check_test(
index_is_unique( 'public', 'sometab', 'sometab_pkey', 'whatever' ),
true,
'index_is_unique() on pk',
'whatever',
''
);
SELECT * FROM check_test(
index_is_unique( 'public', 'sometab', 'sometab_pkey' ),
true,
'index_is_unique() on pk no desc',
'Index sometab_pkey should be unique',
''
);
SELECT * FROM check_test(
index_is_unique( 'sometab', 'sometab_pkey' ),
true,
'index_is_unique() on pk no schema',
'Index sometab_pkey should be unique',
''
);
SELECT * FROM check_test(
index_is_unique( 'sometab_pkey' ),
true,
'index_is_unique() on pk index only',
'Index sometab_pkey should be unique',
''
);
SELECT * FROM check_test(
index_is_unique( 'public', 'sometab', 'idx_bar', 'whatever' ),
false,
'index_is_unique() fail',
'whatever',
''
);
SELECT * FROM check_test(
index_is_unique( 'public', 'sometab', 'idx_bar' ),
false,
'index_is_unique() fail no desc',
'Index idx_bar should be unique',
''
);
SELECT * FROM check_test(
index_is_unique( 'sometab', 'idx_bar' ),
false,
'index_is_unique() fail no schema',
'Index idx_bar should be unique',
''
);
SELECT * FROM check_test(
index_is_unique( 'idx_bar' ),
false,
'index_is_unique() fail index only',
'Index idx_bar should be unique',
''
);
SELECT * FROM check_test(
index_is_unique( 'blahblah' ),
false,
'index_is_unique() no such index',
'Index blahblah should be unique',
''
);
/****************************************************************************/
-- Test index_is_primary().
SELECT * FROM check_test(
index_is_primary( 'public', 'sometab', 'sometab_pkey', 'whatever' ),
true,
'index_is_primary()',
'whatever',
''
);
SELECT * FROM check_test(
index_is_primary( 'public', 'sometab', 'sometab_pkey' ),
true,
'index_is_primary() no desc',
'Index sometab_pkey should be on a primary key',
''
);
SELECT * FROM check_test(
index_is_primary( 'sometab', 'sometab_pkey' ),
true,
'index_is_primary() no schema',
'Index sometab_pkey should be on a primary key',
''
);
SELECT * FROM check_test(
index_is_primary( 'sometab_pkey' ),
true,
'index_is_primary() index only',
'Index sometab_pkey should be on a primary key',
''
);
SELECT * FROM check_test(
index_is_primary( 'public', 'sometab', 'idx_baz', 'whatever' ),
false,
'index_is_primary() fail',
'whatever',
''
);
SELECT * FROM check_test(
index_is_primary( 'public', 'sometab', 'idx_baz' ),
false,
'index_is_primary() fail no desc',
'Index idx_baz should be on a primary key',
''
);
SELECT * FROM check_test(
index_is_primary( 'sometab', 'idx_baz' ),
false,
'index_is_primary() fail no schema',
'Index idx_baz should be on a primary key',
''
);
SELECT * FROM check_test(
index_is_primary( 'idx_baz' ),
false,
'index_is_primary() fail index only',
'Index idx_baz should be on a primary key',
''
);
SELECT * FROM check_test(
index_is_primary( 'blahblah' ),
false,
'index_is_primary() no such index',
'Index blahblah should be on a primary key',
''
);
/****************************************************************************/
-- Test is_clustered().
SELECT * FROM check_test(
is_clustered( 'public', 'sometab', 'idx_bar', 'whatever' ),
false,
'is_clustered() fail',
'whatever',
''
);
SELECT * FROM check_test(
is_clustered( 'public', 'sometab', 'idx_bar' ),
false,
'is_clustered() fail no desc',
'Table public.sometab should be clustered on index idx_bar',
''
);
SELECT * FROM check_test(
is_clustered( 'sometab', 'idx_bar' ),
false,
'is_clustered() fail no schema',
'Table sometab should be clustered on index idx_bar',
''
);
SELECT * FROM check_test(
is_clustered( 'idx_bar' ),
false,
'is_clustered() fail index only',
'Table should be clustered on index idx_bar',
''
);
CLUSTER idx_bar ON public.sometab;
SELECT * FROM check_test(
is_clustered( 'public', 'sometab', 'idx_bar', 'whatever' ),
true,
'is_clustered()',
'whatever',
''
);
SELECT * FROM check_test(
is_clustered( 'public', 'sometab', 'idx_bar' ),
true,
'is_clustered() no desc',
'Table public.sometab should be clustered on index idx_bar',
''
);
SELECT * FROM check_test(
is_clustered( 'sometab', 'idx_bar' ),
true,
'is_clustered() no schema',
'Table sometab should be clustered on index idx_bar',
''
);
SELECT * FROM check_test(
is_clustered( 'idx_bar' ),
true,
'is_clustered() index only',
'Table should be clustered on index idx_bar',
''
);
/****************************************************************************/
-- Test index_is_type().
SELECT * FROM check_test(
index_is_type( 'public', 'sometab', 'idx_bar', 'btree', 'whatever' ),
true,
'index_is_type()',
'whatever',
''
);
SELECT * FROM check_test(
index_is_type( 'public', 'sometab', 'idx_bar', 'BTREE', 'whatever' ),
true,
'index_is_type() ci',
'whatever',
''
);
SELECT * FROM check_test(
index_is_type( 'public', 'sometab', 'idx_bar', 'btree' ),
true,
'index_is_type() no desc',
'Index idx_bar should be a btree index',
''
);
SELECT * FROM check_test(
index_is_type( 'public', 'sometab', 'idx_bar', 'hash' ),
false,
'index_is_type() fail',
'Index idx_bar should be a hash index',
' have: btree
want: hash'
);
SELECT * FROM check_test(
index_is_type( 'sometab', 'idx_bar', 'btree' ),
true,
'index_is_type() no schema',
'Index idx_bar should be a btree index',
''
);
SELECT * FROM check_test(
index_is_type( 'sometab', 'idx_bar', 'hash' ),
false,
'index_is_type() no schema fail',
'Index idx_bar should be a hash index',
' have: btree
want: hash'
);
SELECT * FROM check_test(
index_is_type( 'idx_bar', 'btree' ),
true,
'index_is_type() no table',
'Index idx_bar should be a btree index',
''
);
SELECT * FROM check_test(
index_is_type( 'idx_bar', 'hash' ),
false,
'index_is_type() no table fail',
'Index idx_bar should be a hash index',
' have: btree
want: hash'
);
SELECT * FROM check_test(
index_is_type( 'idx_foo', 'hash' ),
true,
'index_is_type() hash',
'Index idx_foo should be a hash index',
''
);
/****************************************************************************/
-- Finish the tests and clean up.
SELECT * FROM finish();
ROLLBACK;
Jump to Line
Something went wrong with that request. Please try again.