[dbsrgits/DBIx-Class-Schema-Loader.git] / t / 10_03pg_common.t

use strict;
use warnings;
use utf8;
use DBIx::Class::Schema::Loader 'make_schema_at';
use DBIx::Class::Schema::Loader::Utils qw/no_warnings slurp_file/;
use Test::More;
use Test::Exception;
use Try::Tiny;
use File::Path 'rmtree';
use namespace::clean;

use lib qw(t/lib);
use dbixcsl_common_tests ();
use dbixcsl_test_dir '$tdir';

use constant EXTRA_DUMP_DIR => "$tdir/pg_extra_dump";

my $dsn      = $ENV{DBICTEST_PG_DSN} || '';
my $user     = $ENV{DBICTEST_PG_USER} || '';
my $password = $ENV{DBICTEST_PG_PASS} || '';

my $tester = dbixcsl_common_tests->new(
    vendor      => 'Pg',
    auto_inc_pk => 'SERIAL NOT NULL PRIMARY KEY',
    dsn         => $dsn,
    user        => $user,
    password    => $password,
    loader_options  => { preserve_case => 1 },
    connect_info_opts => {
        pg_enable_utf8 => 1,
        on_connect_do  => [ 'SET client_min_messages=WARNING' ],
    },
    quote_char  => '"',
    default_is_deferrable => 0,
    default_on_clause => 'NO ACTION',
    data_types  => {
        # http://www.postgresql.org/docs/7.4/interactive/datatype.html
        #
        # Numeric Types
	boolean     => { data_type => 'boolean' },
	bool        => { data_type => 'boolean' },
        'bool default false'
                    => { data_type => 'boolean', default_value => \'false' },
        'bool default true'
                    => { data_type => 'boolean', default_value => \'true' },
        'bool default 0::bool'
                    => { data_type => 'boolean', default_value => \'false' },
        'bool default 1::bool'
                    => { data_type => 'boolean', default_value => \'true' },

	bigint      => { data_type => 'bigint' },
	int8        => { data_type => 'bigint' },
	bigserial   => { data_type => 'bigint', is_auto_increment => 1 },
	serial8     => { data_type => 'bigint', is_auto_increment => 1 },
	integer     => { data_type => 'integer' },
	int         => { data_type => 'integer' },
	int4        => { data_type => 'integer' },
	serial      => { data_type => 'integer', is_auto_increment => 1 },
	serial4     => { data_type => 'integer', is_auto_increment => 1 },
	smallint    => { data_type => 'smallint' },
	int2        => { data_type => 'smallint' },

	money       => { data_type => 'money' },

	'double precision' => { data_type => 'double precision' },
	float8             => { data_type => 'double precision' },
	real               => { data_type => 'real' },
	float4             => { data_type => 'real' },
        'float(24)'        => { data_type => 'real' },
        'float(25)'        => { data_type => 'double precision' },
        'float(53)'        => { data_type => 'double precision' },
        float              => { data_type => 'double precision' },

        numeric        => { data_type => 'numeric' },
        decimal        => { data_type => 'numeric' },
	'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
	'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },

        # Bit String Types
	'bit varying(2)' => { data_type => 'varbit', size => 2 },
	'varbit(2)'      => { data_type => 'varbit', size => 2 },
	'varbit'         => { data_type => 'varbit' },
	bit              => { data_type => 'bit', size => 1 },
	'bit(3)'         => { data_type => 'bit', size => 3 },

        # Network Types
	inet    => { data_type => 'inet' },
	cidr    => { data_type => 'cidr' },
	macaddr => { data_type => 'macaddr' },

        # Geometric Types
	point   => { data_type => 'point' },
	line    => { data_type => 'line' },
	lseg    => { data_type => 'lseg' },
	box     => { data_type => 'box' },
	path    => { data_type => 'path' },
	polygon => { data_type => 'polygon' },
	circle  => { data_type => 'circle' },

        # Character Types
	'character varying(2)'           => { data_type => 'varchar', size => 2 },
	'varchar(2)'                     => { data_type => 'varchar', size => 2 },
	'character(2)'                   => { data_type => 'char', size => 2 },
	'char(2)'                        => { data_type => 'char', size => 2 },
        # check that default null is correctly rewritten
        'char(3) default null'           => { data_type => 'char', size => 3,
                                              default_value => \'null' },
	'character'                      => { data_type => 'char', size => 1 },
	'char'                           => { data_type => 'char', size => 1 },
	text                             => { data_type => 'text' },
        # varchar with no size has unlimited size, we rewrite to 'text'
	varchar                          => { data_type => 'text',
                                              original => { data_type => 'varchar' } },
        # check default null again (to make sure ref is safe)
        'varchar(3) default null'        => { data_type => 'varchar', size => 3,
                                              default_value => \'null' },

        # Datetime Types
	date                             => { data_type => 'date' },
	interval                         => { data_type => 'interval' },
	'interval(2)'                    => { data_type => 'interval', size => 2 },
	time                             => { data_type => 'time' },
	'time(2)'                        => { data_type => 'time', size => 2 },
	'time without time zone'         => { data_type => 'time' },
	'time(2) without time zone'      => { data_type => 'time', size => 2 },
	'time with time zone'            => { data_type => 'time with time zone' },
	'time(2) with time zone'         => { data_type => 'time with time zone', size => 2 },
	timestamp                        => { data_type => 'timestamp' },
        'timestamp default now()'
                                         => { data_type => 'timestamp', default_value => \'current_timestamp',
                                              original => { default_value => \'now()' } },
	'timestamp(2)'                   => { data_type => 'timestamp', size => 2 },
	'timestamp without time zone'    => { data_type => 'timestamp' },
	'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },

	'timestamp with time zone'       => { data_type => 'timestamp with time zone' },
	'timestamp(2) with time zone'    => { data_type => 'timestamp with time zone', size => 2 },

        # Blob Types
	bytea => { data_type => 'bytea' },

        # Enum Types
        pg_loader_test_enum => { data_type => 'enum', extra => { custom_type_name => 'pg_loader_test_enum',
                                                                 list => [ qw/foo bar baz/] } },
    },
    pre_create => [
        q{
            CREATE TYPE pg_loader_test_enum AS ENUM (
                'foo', 'bar', 'baz'
            )
        },
    ],
    extra       => {
        create => [
            q{
                CREATE SCHEMA dbicsl_test
            },
            q{
                CREATE SEQUENCE dbicsl_test.myseq
            },
            q{
                CREATE TABLE pg_loader_test1 (
                    id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
                    value VARCHAR(100)
                )
            },
            qq{
                COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
            },
            qq{
                COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
            },
            q{
                CREATE TABLE pg_loader_test2 (
                    id SERIAL PRIMARY KEY,
                    value VARCHAR(100)
                )
            },
            q{
                COMMENT ON TABLE pg_loader_test2 IS 'very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very long comment'
            },
            q{
                CREATE SCHEMA "dbicsl-test"
            },
            q{
                CREATE TABLE "dbicsl-test".pg_loader_test4 (
                    id SERIAL PRIMARY KEY,
                    value VARCHAR(100)
                )
            },
            q{
                CREATE TABLE "dbicsl-test".pg_loader_test5 (
                    id SERIAL PRIMARY KEY,
                    value VARCHAR(100),
                    four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
                    CONSTRAINT loader_test5_uniq UNIQUE (four_id)
                )
            },
            q{
                CREATE SCHEMA "dbicsl.test"
            },
            q{
                CREATE TABLE "dbicsl.test".pg_loader_test5 (
                    pk SERIAL PRIMARY KEY,
                    value VARCHAR(100),
                    four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
                    CONSTRAINT loader_test5_uniq UNIQUE (four_id)
                )
            },
            q{
                CREATE TABLE "dbicsl.test".pg_loader_test6 (
                    id SERIAL PRIMARY KEY,
                    value VARCHAR(100),
                    pg_loader_test4_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id)
                )
            },
            q{
                CREATE TABLE "dbicsl.test".pg_loader_test7 (
                    id SERIAL PRIMARY KEY,
                    value VARCHAR(100),
                    six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
                )
            },
            q{
                CREATE TABLE "dbicsl-test".pg_loader_test8 (
                    id SERIAL PRIMARY KEY,
                    value VARCHAR(100),
                    pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
                )
            },
            # 4 through 8 are used for the multi-schema tests
            q{
                create table pg_loader_test9 (
                    id bigserial primary key
                )
            },
            q{
                create table pg_loader_test10 (
                    id bigserial primary key,
                    nine_id int,
                    foreign key (nine_id) references pg_loader_test9(id)
                        on delete restrict on update set null deferrable
                )
            },
            q{
                create view pg_loader_test11 as
                    select * from pg_loader_test1
            },
            q{
                create table pg_loader_test12 (
                    id integer not null,
                    value integer,
                    active boolean,
                    name text
                )
            },
            q{
                create unique index uniq_id_lc_name on pg_loader_test12 (
                    id, lower(name)
                )
            },
            q{
                create unique index uniq_uc_name_id on pg_loader_test12 (
                    upper(name), id
                )
            },
            q{
                create unique index pg_loader_test12_value on pg_loader_test12 (
                    value
                )
            },
            q{
                create unique index pg_loader_test12_name_active on pg_loader_test12 (
                    name
                ) where active
            },
        ],
        pre_drop_ddl => [
            'DROP SCHEMA dbicsl_test CASCADE',
            'DROP SCHEMA "dbicsl-test" CASCADE',
            'DROP SCHEMA "dbicsl.test" CASCADE',
            'DROP TYPE pg_loader_test_enum',
            'DROP VIEW pg_loader_test11',
        ],
        drop  => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
        count => 10 + 30 * 2,
        run   => sub {
            my ($schema, $monikers, $classes) = @_;

            is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
                'dbicsl_test.myseq',
                'qualified sequence detected';

            my $class    = $classes->{pg_loader_test1};
            my $filename = $schema->loader->get_dump_filename($class);

            my $code = slurp_file $filename;

            like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
                'table comment';

            like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
                'column comment and attrs';

            $class    = $classes->{pg_loader_test2};
            $filename = $schema->loader->get_dump_filename($class);

            $code = slurp_file $filename;

            like $code, qr/^=head1 NAME\n\n^$class\n\n=head1 DESCRIPTION\n\n^very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very long comment\n\n^=cut\n/m,
                'long table comment is in DESCRIPTION';

            # test on delete/update fk clause introspection
            ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
                'got rel info');

            is $rel_info->{attrs}{on_delete}, 'RESTRICT',
                'ON DELETE clause introspected correctly';

            is $rel_info->{attrs}{on_update}, 'SET NULL',
                'ON UPDATE clause introspected correctly';

            is $rel_info->{attrs}{is_deferrable}, 1,
                'DEFERRABLE clause introspected correctly';

            foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
                lives_and {
                    rmtree EXTRA_DUMP_DIR;

                    my @warns;
                    local $SIG{__WARN__} = sub {
                        push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
                    };

                    make_schema_at(
                        'PGMultiSchema',
                        {
                            naming => 'current',
                            db_schema => $db_schema,
                            preserve_case => 1,
                            dump_directory => EXTRA_DUMP_DIR,
                            quiet => 1,
                        },
                        [ $dsn, $user, $password, {
                            on_connect_do  => [ 'SET client_min_messages=WARNING' ],
                        } ],
                    );

                    diag join "\n", @warns if @warns;

                    is @warns, 0;
                } 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';

                my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);

                lives_and {
                    ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
                        on_connect_do  => [ 'SET client_min_messages=WARNING' ],
                    });
                } 'connected test schema';

                lives_and {
                    ok $rsrc = $test_schema->source('PgLoaderTest4');
                } 'got source for table in schema name with dash';

                is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
                    'column in schema name with dash';

                is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
                    'column in schema name with dash';

                is try { $rsrc->column_info('value')->{size} }, 100,
                    'column in schema name with dash';

                lives_and {
                    ok $rs = $test_schema->resultset('PgLoaderTest4');
                } 'got resultset for table in schema name with dash';

                lives_and {
                    ok $row = $rs->create({ value => 'foo' });
                } 'executed SQL on table in schema name with dash';

                $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };

                is_deeply $rel_info->{cond}, {
                    'foreign.four_id' => 'self.id'
                }, 'relationship in schema name with dash';

                is $rel_info->{attrs}{accessor}, 'single',
                    'relationship in schema name with dash';

                is $rel_info->{attrs}{join_type}, 'LEFT',
                    'relationship in schema name with dash';

                lives_and {
                    ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
                } 'got source for table in schema name with dash';

                %uniqs = try { $rsrc->unique_constraints };

                is keys %uniqs, 2,
                    'got unique and primary constraint in schema name with dash';

                delete $uniqs{primary};

                is_deeply ((values %uniqs)[0], ['four_id'],
                    'unique constraint is correct in schema name with dash');

                lives_and {
                    ok $rsrc = $test_schema->source('PgLoaderTest6');
                } 'got source for table in schema name with dot';

                is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
                    'column in schema name with dot introspected correctly';

                is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
                    'column in schema name with dot introspected correctly';

                is try { $rsrc->column_info('value')->{size} }, 100,
                    'column in schema name with dot introspected correctly';

                lives_and {
                    ok $rs = $test_schema->resultset('PgLoaderTest6');
                } 'got resultset for table in schema name with dot';

                lives_and {
                    ok $row = $rs->create({ value => 'foo' });
                } 'executed SQL on table in schema name with dot';

                $rel_info = try { $rsrc->relationship_info('pg_loader_test7') };

                is_deeply $rel_info->{cond}, {
                    'foreign.six_id' => 'self.id'
                }, 'relationship in schema name with dot';

                is $rel_info->{attrs}{accessor}, 'single',
                    'relationship in schema name with dot';

                is $rel_info->{attrs}{join_type}, 'LEFT',
                    'relationship in schema name with dot';

                lives_and {
                    ok $rsrc = $test_schema->source('PgLoaderTest7');
                } 'got source for table in schema name with dot';

                %uniqs = try { $rsrc->unique_constraints };

                is keys %uniqs, 2,
                    'got unique and primary constraint in schema name with dot';

                delete $uniqs{primary};

                is_deeply ((values %uniqs)[0], ['six_id'],
                    'unique constraint is correct in schema name with dot');

                lives_and {
                    ok $test_schema->source('PgLoaderTest6')
                        ->has_relationship('pg_loader_test4');
                } 'cross-schema relationship in multi-db_schema';

                lives_and {
                    ok $test_schema->source('PgLoaderTest4')
                        ->has_relationship('pg_loader_test6s');
                } 'cross-schema relationship in multi-db_schema';

                lives_and {
                    ok $test_schema->source('PgLoaderTest8')
                        ->has_relationship('pg_loader_test7');
                } 'cross-schema relationship in multi-db_schema';

                lives_and {
                    ok $test_schema->source('PgLoaderTest7')
                        ->has_relationship('pg_loader_test8s');
                } 'cross-schema relationship in multi-db_schema';
            }

            # test that views are marked as such
            isa_ok $schema->resultset($monikers->{pg_loader_test11})->result_source, 'DBIx::Class::ResultSource::View',
                'views have table_class set correctly';

            is_deeply
                { $schema->source($monikers->{pg_loader_test12})->unique_constraints },
                { pg_loader_test12_value => ['value'] },
                'unique indexes are dumped correctly';
        },
    },
);

if( !$dsn || !$user ) {
    $tester->skip_tests('You need to set the DBICTEST_PG_DSN, _USER, and _PASS environment variables');
}
else {
    $tester->run_tests();
}

END {
    rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
}
# vim:et sw=4 sts=4 tw=0:
Commit	Line	Data
a78e3fed	1	use strict;
fcf328c7	2	use warnings;
fcf328c7	3	use utf8;
12b86f07	4	use DBIx::Class::Schema::Loader 'make_schema_at';
fcf328c7	5	use DBIx::Class::Schema::Loader::Utils qw/no_warnings slurp_file/;
fdd8ff16	6	use Test::More;
12b86f07	7	use Test::Exception;
12b86f07	8	use Try::Tiny;
c4a69b87	9	use File::Path 'rmtree';
fcf328c7	10	use namespace::clean;
c4a69b87	11
fcf328c7	12	use lib qw(t/lib);
fcf328c7	13	use dbixcsl_common_tests ();
c4a69b87	14	use dbixcsl_test_dir '$tdir';
	15
	16	use constant EXTRA_DUMP_DIR => "$tdir/pg_extra_dump";
a78e3fed	17
9e978a19	18	my $dsn = $ENV{DBICTEST_PG_DSN} \|\| '';
	19	my $user = $ENV{DBICTEST_PG_USER} \|\| '';
	20	my $password = $ENV{DBICTEST_PG_PASS} \|\| '';
a78e3fed	21
fbd83464	22	my $tester = dbixcsl_common_tests->new(
a78e3fed	23	vendor => 'Pg',
a78e3fed	24	auto_inc_pk => 'SERIAL NOT NULL PRIMARY KEY',
9e978a19	25	dsn => $dsn,
a78e3fed	26	user => $user,
a78e3fed	27	password => $password,
c930f78b	28	loader_options => { preserve_case => 1 },
c38ec663	29	connect_info_opts => {
af15ea33	30	pg_enable_utf8 => 1,
af15ea33	31	on_connect_do => [ 'SET client_min_messages=WARNING' ],
c38ec663	32	},
c930f78b	33	quote_char => '"',
958d5fcf	34	default_is_deferrable => 0,
958d5fcf	35	default_on_clause => 'NO ACTION',
ee07e280	36	data_types => {
760fd65c	37	# http://www.postgresql.org/docs/7.4/interactive/datatype.html
	38	#
	39	# Numeric Types
	40	boolean => { data_type => 'boolean' },
	41	bool => { data_type => 'boolean' },
45321eda	42	'bool default false'
45321eda	43	=> { data_type => 'boolean', default_value => \'false' },
96336646	44	'bool default true'
	45	=> { data_type => 'boolean', default_value => \'true' },
	46	'bool default 0::bool'
	47	=> { data_type => 'boolean', default_value => \'false' },
	48	'bool default 1::bool'
	49	=> { data_type => 'boolean', default_value => \'true' },
760fd65c	50
	51	bigint => { data_type => 'bigint' },
	52	int8 => { data_type => 'bigint' },
	53	bigserial => { data_type => 'bigint', is_auto_increment => 1 },
	54	serial8 => { data_type => 'bigint', is_auto_increment => 1 },
5163dc4a	55	integer => { data_type => 'integer' },
	56	int => { data_type => 'integer' },
	57	int4 => { data_type => 'integer' },
5163dc4a	58	serial => { data_type => 'integer', is_auto_increment => 1 },
5163dc4a	59	serial4 => { data_type => 'integer', is_auto_increment => 1 },
760fd65c	60	smallint => { data_type => 'smallint' },
	61	int2 => { data_type => 'smallint' },
	62
	63	money => { data_type => 'money' },
	64
	65	'double precision' => { data_type => 'double precision' },
	66	float8 => { data_type => 'double precision' },
	67	real => { data_type => 'real' },
	68	float4 => { data_type => 'real' },
	69	'float(24)' => { data_type => 'real' },
	70	'float(25)' => { data_type => 'double precision' },
	71	'float(53)' => { data_type => 'double precision' },
	72	float => { data_type => 'double precision' },
	73
	74	numeric => { data_type => 'numeric' },
	75	decimal => { data_type => 'numeric' },
f80b0ea7	76	'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
f80b0ea7	77	'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },
760fd65c	78
760fd65c	79	# Bit String Types
f80b0ea7	80	'bit varying(2)' => { data_type => 'varbit', size => 2 },
	81	'varbit(2)' => { data_type => 'varbit', size => 2 },
	82	'varbit' => { data_type => 'varbit' },
	83	bit => { data_type => 'bit', size => 1 },
	84	'bit(3)' => { data_type => 'bit', size => 3 },
760fd65c	85
	86	# Network Types
	87	inet => { data_type => 'inet' },
	88	cidr => { data_type => 'cidr' },
	89	macaddr => { data_type => 'macaddr' },
	90
	91	# Geometric Types
	92	point => { data_type => 'point' },
	93	line => { data_type => 'line' },
	94	lseg => { data_type => 'lseg' },
	95	box => { data_type => 'box' },
	96	path => { data_type => 'path' },
	97	polygon => { data_type => 'polygon' },
	98	circle => { data_type => 'circle' },
	99
	100	# Character Types
f80b0ea7	101	'character varying(2)' => { data_type => 'varchar', size => 2 },
	102	'varchar(2)' => { data_type => 'varchar', size => 2 },
	103	'character(2)' => { data_type => 'char', size => 2 },
	104	'char(2)' => { data_type => 'char', size => 2 },
87a43db1	105	# check that default null is correctly rewritten
	106	'char(3) default null' => { data_type => 'char', size => 3,
	107	default_value => \'null' },
f80b0ea7	108	'character' => { data_type => 'char', size => 1 },
f80b0ea7	109	'char' => { data_type => 'char', size => 1 },
760fd65c	110	text => { data_type => 'text' },
f80b0ea7	111	# varchar with no size has unlimited size, we rewrite to 'text'
8e030521	112	varchar => { data_type => 'text',
8e030521	113	original => { data_type => 'varchar' } },
87a43db1	114	# check default null again (to make sure ref is safe)
87a43db1	115	'varchar(3) default null' => { data_type => 'varchar', size => 3,
96336646	116	default_value => \'null' },
760fd65c	117
	118	# Datetime Types
	119	date => { data_type => 'date' },
	120	interval => { data_type => 'interval' },
f80b0ea7	121	'interval(2)' => { data_type => 'interval', size => 2 },
8e030521	122	time => { data_type => 'time' },
	123	'time(2)' => { data_type => 'time', size => 2 },
	124	'time without time zone' => { data_type => 'time' },
	125	'time(2) without time zone' => { data_type => 'time', size => 2 },
df956aad	126	'time with time zone' => { data_type => 'time with time zone' },
f80b0ea7	127	'time(2) with time zone' => { data_type => 'time with time zone', size => 2 },
f80b0ea7	128	timestamp => { data_type => 'timestamp' },
701cd3e3	129	'timestamp default now()'
	130	=> { data_type => 'timestamp', default_value => \'current_timestamp',
	131	original => { default_value => \'now()' } },
f80b0ea7	132	'timestamp(2)' => { data_type => 'timestamp', size => 2 },
	133	'timestamp without time zone' => { data_type => 'timestamp' },
	134	'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },
760fd65c	135
df956aad	136	'timestamp with time zone' => { data_type => 'timestamp with time zone' },
f80b0ea7	137	'timestamp(2) with time zone' => { data_type => 'timestamp with time zone', size => 2 },
760fd65c	138
	139	# Blob Types
	140	bytea => { data_type => 'bytea' },
12333562	141
12333562	142	# Enum Types
b8ae4518	143	pg_loader_test_enum => { data_type => 'enum', extra => { custom_type_name => 'pg_loader_test_enum',
463c86fb	144	list => [ qw/foo bar baz/] } },
ee07e280	145	},
12333562	146	pre_create => [
	147	q{
	148	CREATE TYPE pg_loader_test_enum AS ENUM (
	149	'foo', 'bar', 'baz'
	150	)
	151	},
	152	],
fdd8ff16	153	extra => {
	154	create => [
	155	q{
7b868481	156	CREATE SCHEMA dbicsl_test
	157	},
	158	q{
	159	CREATE SEQUENCE dbicsl_test.myseq
	160	},
	161	q{
fdd8ff16	162	CREATE TABLE pg_loader_test1 (
7b868481	163	id INTEGER NOT NULL DEFAULT nextval('dbicsl_test.myseq') PRIMARY KEY,
fdd8ff16	164	value VARCHAR(100)
	165	)
	166	},
fd97abca	167	qq{
af15ea33	168	COMMENT ON TABLE pg_loader_test1 IS 'The\15\12Table ∑'
fdd8ff16	169	},
fd97abca	170	qq{
fd97abca	171	COMMENT ON COLUMN pg_loader_test1.value IS 'The\15\12Column'
fdd8ff16	172	},
4b9fb838	173	q{
4b9fb838	174	CREATE TABLE pg_loader_test2 (
12b86f07	175	id SERIAL PRIMARY KEY,
4b9fb838	176	value VARCHAR(100)
	177	)
	178	},
	179	q{
	180	COMMENT ON TABLE pg_loader_test2 IS 'very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very long comment'
	181	},
12b86f07	182	q{
	183	CREATE SCHEMA "dbicsl-test"
	184	},
	185	q{
c4a69b87	186	CREATE TABLE "dbicsl-test".pg_loader_test4 (
12b86f07	187	id SERIAL PRIMARY KEY,
	188	value VARCHAR(100)
	189	)
	190	},
	191	q{
c4a69b87	192	CREATE TABLE "dbicsl-test".pg_loader_test5 (
12b86f07	193	id SERIAL PRIMARY KEY,
12b86f07	194	value VARCHAR(100),
5975bbe6	195	four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
5975bbe6	196	CONSTRAINT loader_test5_uniq UNIQUE (four_id)
12b86f07	197	)
	198	},
	199	q{
	200	CREATE SCHEMA "dbicsl.test"
	201	},
	202	q{
5975bbe6	203	CREATE TABLE "dbicsl.test".pg_loader_test5 (
	204	pk SERIAL PRIMARY KEY,
	205	value VARCHAR(100),
	206	four_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id),
	207	CONSTRAINT loader_test5_uniq UNIQUE (four_id)
	208	)
	209	},
	210	q{
c4a69b87	211	CREATE TABLE "dbicsl.test".pg_loader_test6 (
12b86f07	212	id SERIAL PRIMARY KEY,
c4a69b87	213	value VARCHAR(100),
c4a69b87	214	pg_loader_test4_id INTEGER REFERENCES "dbicsl-test".pg_loader_test4 (id)
12b86f07	215	)
	216	},
	217	q{
c4a69b87	218	CREATE TABLE "dbicsl.test".pg_loader_test7 (
	219	id SERIAL PRIMARY KEY,
	220	value VARCHAR(100),
	221	six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
	222	)
	223	},
	224	q{
	225	CREATE TABLE "dbicsl-test".pg_loader_test8 (
12b86f07	226	id SERIAL PRIMARY KEY,
12b86f07	227	value VARCHAR(100),
c4a69b87	228	pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
12b86f07	229	)
12b86f07	230	},
958d5fcf	231	# 4 through 8 are used for the multi-schema tests
	232	q{
	233	create table pg_loader_test9 (
	234	id bigserial primary key
	235	)
	236	},
	237	q{
	238	create table pg_loader_test10 (
	239	id bigserial primary key,
f8640ecc	240	nine_id int,
f8640ecc	241	foreign key (nine_id) references pg_loader_test9(id)
b9762446	242	on delete restrict on update set null deferrable
958d5fcf	243	)
958d5fcf	244	},
ce2f102a	245	q{
	246	create view pg_loader_test11 as
	247	select * from pg_loader_test1
	248	},
b32f8189	249	q{
	250	create table pg_loader_test12 (
	251	id integer not null,
a01ac8ee	252	value integer,
a01ac8ee	253	active boolean,
b32f8189	254	name text
	255	)
	256	},
	257	q{
	258	create unique index uniq_id_lc_name on pg_loader_test12 (
	259	id, lower(name)
	260	)
	261	},
	262	q{
	263	create unique index uniq_uc_name_id on pg_loader_test12 (
	264	upper(name), id
	265	)
	266	},
a01ac8ee	267	q{
	268	create unique index pg_loader_test12_value on pg_loader_test12 (
	269	value
	270	)
	271	},
	272	q{
	273	create unique index pg_loader_test12_name_active on pg_loader_test12 (
	274	name
	275	) where active
	276	},
fdd8ff16	277	],
7b868481	278	pre_drop_ddl => [
7b868481	279	'DROP SCHEMA dbicsl_test CASCADE',
12b86f07	280	'DROP SCHEMA "dbicsl-test" CASCADE',
12b86f07	281	'DROP SCHEMA "dbicsl.test" CASCADE',
12333562	282	'DROP TYPE pg_loader_test_enum',
ce2f102a	283	'DROP VIEW pg_loader_test11',
7b868481	284	],
b32f8189	285	drop => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
b32f8189	286	count => 10 + 30 * 2,
fdd8ff16	287	run => sub {
	288	my ($schema, $monikers, $classes) = @_;
	289
7b868481	290	is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
	291	'dbicsl_test.myseq',
	292	'qualified sequence detected';
	293
fdd8ff16	294	my $class = $classes->{pg_loader_test1};
c4a69b87	295	my $filename = $schema->loader->get_dump_filename($class);
fdd8ff16	296
fcf328c7	297	my $code = slurp_file $filename;
fdd8ff16	298
af15ea33	299	like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
fdd8ff16	300	'table comment';
fdd8ff16	301
fd97abca	302	like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
79a00530	303	'column comment and attrs';
baff904e	304
4b9fb838	305	$class = $classes->{pg_loader_test2};
c4a69b87	306	$filename = $schema->loader->get_dump_filename($class);
4b9fb838	307
fcf328c7	308	$code = slurp_file $filename;
4b9fb838	309
	310	like $code, qr/^=head1 NAME\n\n^$class\n\n=head1 DESCRIPTION\n\n^very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very very long comment\n\n^=cut\n/m,
	311	'long table comment is in DESCRIPTION';
12b86f07	312
958d5fcf	313	# test on delete/update fk clause introspection
f8640ecc	314	ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
958d5fcf	315	'got rel info');
	316
	317	is $rel_info->{attrs}{on_delete}, 'RESTRICT',
	318	'ON DELETE clause introspected correctly';
	319
	320	is $rel_info->{attrs}{on_update}, 'SET NULL',
	321	'ON UPDATE clause introspected correctly';
	322
b9762446	323	is $rel_info->{attrs}{is_deferrable}, 1,
958d5fcf	324	'DEFERRABLE clause introspected correctly';
958d5fcf	325
c4a69b87	326	foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
	327	lives_and {
	328	rmtree EXTRA_DUMP_DIR;
	329
	330	my @warns;
	331	local $SIG{__WARN__} = sub {
	332	push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
	333	};
	334
12b86f07	335	make_schema_at(
c4a69b87	336	'PGMultiSchema',
12b86f07	337	{
12b86f07	338	naming => 'current',
c4a69b87	339	db_schema => $db_schema,
12b86f07	340	preserve_case => 1,
c4a69b87	341	dump_directory => EXTRA_DUMP_DIR,
c4a69b87	342	quiet => 1,
12b86f07	343	},
	344	[ $dsn, $user, $password, {
	345	on_connect_do => [ 'SET client_min_messages=WARNING' ],
	346	} ],
	347	);
12b86f07	348
c4a69b87	349	diag join "\n", @warns if @warns;
12b86f07	350
c4a69b87	351	is @warns, 0;
c4a69b87	352	} 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
12b86f07	353
c4a69b87	354	my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
12b86f07	355
c4a69b87	356	lives_and {
	357	ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
	358	on_connect_do => [ 'SET client_min_messages=WARNING' ],
	359	});
	360	} 'connected test schema';
12b86f07	361
c4a69b87	362	lives_and {
4c2e2ce9	363	ok $rsrc = $test_schema->source('PgLoaderTest4');
c4a69b87	364	} 'got source for table in schema name with dash';
12b86f07	365
c4a69b87	366	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
c4a69b87	367	'column in schema name with dash';
12b86f07	368
c4a69b87	369	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
c4a69b87	370	'column in schema name with dash';
12b86f07	371
c4a69b87	372	is try { $rsrc->column_info('value')->{size} }, 100,
c4a69b87	373	'column in schema name with dash';
12b86f07	374
c4a69b87	375	lives_and {
4c2e2ce9	376	ok $rs = $test_schema->resultset('PgLoaderTest4');
c4a69b87	377	} 'got resultset for table in schema name with dash';
12b86f07	378
c4a69b87	379	lives_and {
	380	ok $row = $rs->create({ value => 'foo' });
	381	} 'executed SQL on table in schema name with dash';
12b86f07	382
5975bbe6	383	$rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
12b86f07	384
c4a69b87	385	is_deeply $rel_info->{cond}, {
	386	'foreign.four_id' => 'self.id'
	387	}, 'relationship in schema name with dash';
12b86f07	388
c4a69b87	389	is $rel_info->{attrs}{accessor}, 'single',
	390	'relationship in schema name with dash';
	391
	392	is $rel_info->{attrs}{join_type}, 'LEFT',
	393	'relationship in schema name with dash';
	394
	395	lives_and {
5975bbe6	396	ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
c4a69b87	397	} 'got source for table in schema name with dash';
	398
	399	%uniqs = try { $rsrc->unique_constraints };
12b86f07	400
c4a69b87	401	is keys %uniqs, 2,
c4a69b87	402	'got unique and primary constraint in schema name with dash';
12b86f07	403
5975bbe6	404	delete $uniqs{primary};
	405
	406	is_deeply ((values %uniqs)[0], ['four_id'],
	407	'unique constraint is correct in schema name with dash');
	408
c4a69b87	409	lives_and {
4c2e2ce9	410	ok $rsrc = $test_schema->source('PgLoaderTest6');
c4a69b87	411	} 'got source for table in schema name with dot';
12b86f07	412
c4a69b87	413	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
c4a69b87	414	'column in schema name with dot introspected correctly';
12b86f07	415
c4a69b87	416	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
c4a69b87	417	'column in schema name with dot introspected correctly';
12b86f07	418
c4a69b87	419	is try { $rsrc->column_info('value')->{size} }, 100,
c4a69b87	420	'column in schema name with dot introspected correctly';
12b86f07	421
c4a69b87	422	lives_and {
4c2e2ce9	423	ok $rs = $test_schema->resultset('PgLoaderTest6');
c4a69b87	424	} 'got resultset for table in schema name with dot';
12b86f07	425
c4a69b87	426	lives_and {
	427	ok $row = $rs->create({ value => 'foo' });
	428	} 'executed SQL on table in schema name with dot';
12b86f07	429
c4a69b87	430	$rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
12b86f07	431
c4a69b87	432	is_deeply $rel_info->{cond}, {
	433	'foreign.six_id' => 'self.id'
	434	}, 'relationship in schema name with dot';
12b86f07	435
c4a69b87	436	is $rel_info->{attrs}{accessor}, 'single',
c4a69b87	437	'relationship in schema name with dot';
12b86f07	438
c4a69b87	439	is $rel_info->{attrs}{join_type}, 'LEFT',
c4a69b87	440	'relationship in schema name with dot';
12b86f07	441
c4a69b87	442	lives_and {
4c2e2ce9	443	ok $rsrc = $test_schema->source('PgLoaderTest7');
c4a69b87	444	} 'got source for table in schema name with dot';
	445
	446	%uniqs = try { $rsrc->unique_constraints };
	447
	448	is keys %uniqs, 2,
	449	'got unique and primary constraint in schema name with dot';
	450
5975bbe6	451	delete $uniqs{primary};
	452
	453	is_deeply ((values %uniqs)[0], ['six_id'],
	454	'unique constraint is correct in schema name with dot');
	455
c4a69b87	456	lives_and {
4c2e2ce9	457	ok $test_schema->source('PgLoaderTest6')
c4a69b87	458	->has_relationship('pg_loader_test4');
	459	} 'cross-schema relationship in multi-db_schema';
	460
	461	lives_and {
4c2e2ce9	462	ok $test_schema->source('PgLoaderTest4')
c4a69b87	463	->has_relationship('pg_loader_test6s');
	464	} 'cross-schema relationship in multi-db_schema';
	465
	466	lives_and {
4c2e2ce9	467	ok $test_schema->source('PgLoaderTest8')
c4a69b87	468	->has_relationship('pg_loader_test7');
	469	} 'cross-schema relationship in multi-db_schema';
	470
	471	lives_and {
4c2e2ce9	472	ok $test_schema->source('PgLoaderTest7')
c4a69b87	473	->has_relationship('pg_loader_test8s');
	474	} 'cross-schema relationship in multi-db_schema';
	475	}
ce2f102a	476
	477	# test that views are marked as such
	478	isa_ok $schema->resultset($monikers->{pg_loader_test11})->result_source, 'DBIx::Class::ResultSource::View',
	479	'views have table_class set correctly';
b32f8189	480
a01ac8ee	481	is_deeply
	482	{ $schema->source($monikers->{pg_loader_test12})->unique_constraints },
	483	{ pg_loader_test12_value => ['value'] },
	484	'unique indexes are dumped correctly';
fdd8ff16	485	},
fdd8ff16	486	},
a78e3fed	487	);
a78e3fed	488
9e978a19	489	if( !$dsn \|\| !$user ) {
9e978a19	490	$tester->skip_tests('You need to set the DBICTEST_PG_DSN, _USER, and _PASS environment variables');
a78e3fed	491	}
	492	else {
	493	$tester->run_tests();
	494	}
c4a69b87	495
	496	END {
	497	rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
	498	}
8e64075f	499	# vim:et sw=4 sts=4 tw=0: