[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
            },
        ],
        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/ ],
        count => 9 + 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';
        },
    },
);

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	},
fdd8ff16	249	],
7b868481	250	pre_drop_ddl => [
7b868481	251	'DROP SCHEMA dbicsl_test CASCADE',
12b86f07	252	'DROP SCHEMA "dbicsl-test" CASCADE',
12b86f07	253	'DROP SCHEMA "dbicsl.test" CASCADE',
12333562	254	'DROP TYPE pg_loader_test_enum',
ce2f102a	255	'DROP VIEW pg_loader_test11',
7b868481	256	],
958d5fcf	257	drop => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10/ ],
ce2f102a	258	count => 9 + 30 * 2,
fdd8ff16	259	run => sub {
	260	my ($schema, $monikers, $classes) = @_;
	261
7b868481	262	is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
	263	'dbicsl_test.myseq',
	264	'qualified sequence detected';
	265
fdd8ff16	266	my $class = $classes->{pg_loader_test1};
c4a69b87	267	my $filename = $schema->loader->get_dump_filename($class);
fdd8ff16	268
fcf328c7	269	my $code = slurp_file $filename;
fdd8ff16	270
af15ea33	271	like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
fdd8ff16	272	'table comment';
fdd8ff16	273
fd97abca	274	like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
79a00530	275	'column comment and attrs';
baff904e	276
4b9fb838	277	$class = $classes->{pg_loader_test2};
c4a69b87	278	$filename = $schema->loader->get_dump_filename($class);
4b9fb838	279
fcf328c7	280	$code = slurp_file $filename;
4b9fb838	281
	282	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,
	283	'long table comment is in DESCRIPTION';
12b86f07	284
958d5fcf	285	# test on delete/update fk clause introspection
f8640ecc	286	ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
958d5fcf	287	'got rel info');
	288
	289	is $rel_info->{attrs}{on_delete}, 'RESTRICT',
	290	'ON DELETE clause introspected correctly';
	291
	292	is $rel_info->{attrs}{on_update}, 'SET NULL',
	293	'ON UPDATE clause introspected correctly';
	294
b9762446	295	is $rel_info->{attrs}{is_deferrable}, 1,
958d5fcf	296	'DEFERRABLE clause introspected correctly';
958d5fcf	297
c4a69b87	298	foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
	299	lives_and {
	300	rmtree EXTRA_DUMP_DIR;
	301
	302	my @warns;
	303	local $SIG{__WARN__} = sub {
	304	push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
	305	};
	306
12b86f07	307	make_schema_at(
c4a69b87	308	'PGMultiSchema',
12b86f07	309	{
12b86f07	310	naming => 'current',
c4a69b87	311	db_schema => $db_schema,
12b86f07	312	preserve_case => 1,
c4a69b87	313	dump_directory => EXTRA_DUMP_DIR,
c4a69b87	314	quiet => 1,
12b86f07	315	},
	316	[ $dsn, $user, $password, {
	317	on_connect_do => [ 'SET client_min_messages=WARNING' ],
	318	} ],
	319	);
12b86f07	320
c4a69b87	321	diag join "\n", @warns if @warns;
12b86f07	322
c4a69b87	323	is @warns, 0;
c4a69b87	324	} 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
12b86f07	325
c4a69b87	326	my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
12b86f07	327
c4a69b87	328	lives_and {
	329	ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
	330	on_connect_do => [ 'SET client_min_messages=WARNING' ],
	331	});
	332	} 'connected test schema';
12b86f07	333
c4a69b87	334	lives_and {
4c2e2ce9	335	ok $rsrc = $test_schema->source('PgLoaderTest4');
c4a69b87	336	} 'got source for table in schema name with dash';
12b86f07	337
c4a69b87	338	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
c4a69b87	339	'column in schema name with dash';
12b86f07	340
c4a69b87	341	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
c4a69b87	342	'column in schema name with dash';
12b86f07	343
c4a69b87	344	is try { $rsrc->column_info('value')->{size} }, 100,
c4a69b87	345	'column in schema name with dash';
12b86f07	346
c4a69b87	347	lives_and {
4c2e2ce9	348	ok $rs = $test_schema->resultset('PgLoaderTest4');
c4a69b87	349	} 'got resultset for table in schema name with dash';
12b86f07	350
c4a69b87	351	lives_and {
	352	ok $row = $rs->create({ value => 'foo' });
	353	} 'executed SQL on table in schema name with dash';
12b86f07	354
5975bbe6	355	$rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
12b86f07	356
c4a69b87	357	is_deeply $rel_info->{cond}, {
	358	'foreign.four_id' => 'self.id'
	359	}, 'relationship in schema name with dash';
12b86f07	360
c4a69b87	361	is $rel_info->{attrs}{accessor}, 'single',
	362	'relationship in schema name with dash';
	363
	364	is $rel_info->{attrs}{join_type}, 'LEFT',
	365	'relationship in schema name with dash';
	366
	367	lives_and {
5975bbe6	368	ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
c4a69b87	369	} 'got source for table in schema name with dash';
	370
	371	%uniqs = try { $rsrc->unique_constraints };
12b86f07	372
c4a69b87	373	is keys %uniqs, 2,
c4a69b87	374	'got unique and primary constraint in schema name with dash';
12b86f07	375
5975bbe6	376	delete $uniqs{primary};
	377
	378	is_deeply ((values %uniqs)[0], ['four_id'],
	379	'unique constraint is correct in schema name with dash');
	380
c4a69b87	381	lives_and {
4c2e2ce9	382	ok $rsrc = $test_schema->source('PgLoaderTest6');
c4a69b87	383	} 'got source for table in schema name with dot';
12b86f07	384
c4a69b87	385	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
c4a69b87	386	'column in schema name with dot introspected correctly';
12b86f07	387
c4a69b87	388	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
c4a69b87	389	'column in schema name with dot introspected correctly';
12b86f07	390
c4a69b87	391	is try { $rsrc->column_info('value')->{size} }, 100,
c4a69b87	392	'column in schema name with dot introspected correctly';
12b86f07	393
c4a69b87	394	lives_and {
4c2e2ce9	395	ok $rs = $test_schema->resultset('PgLoaderTest6');
c4a69b87	396	} 'got resultset for table in schema name with dot';
12b86f07	397
c4a69b87	398	lives_and {
	399	ok $row = $rs->create({ value => 'foo' });
	400	} 'executed SQL on table in schema name with dot';
12b86f07	401
c4a69b87	402	$rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
12b86f07	403
c4a69b87	404	is_deeply $rel_info->{cond}, {
	405	'foreign.six_id' => 'self.id'
	406	}, 'relationship in schema name with dot';
12b86f07	407
c4a69b87	408	is $rel_info->{attrs}{accessor}, 'single',
c4a69b87	409	'relationship in schema name with dot';
12b86f07	410
c4a69b87	411	is $rel_info->{attrs}{join_type}, 'LEFT',
c4a69b87	412	'relationship in schema name with dot';
12b86f07	413
c4a69b87	414	lives_and {
4c2e2ce9	415	ok $rsrc = $test_schema->source('PgLoaderTest7');
c4a69b87	416	} 'got source for table in schema name with dot';
	417
	418	%uniqs = try { $rsrc->unique_constraints };
	419
	420	is keys %uniqs, 2,
	421	'got unique and primary constraint in schema name with dot';
	422
5975bbe6	423	delete $uniqs{primary};
	424
	425	is_deeply ((values %uniqs)[0], ['six_id'],
	426	'unique constraint is correct in schema name with dot');
	427
c4a69b87	428	lives_and {
4c2e2ce9	429	ok $test_schema->source('PgLoaderTest6')
c4a69b87	430	->has_relationship('pg_loader_test4');
	431	} 'cross-schema relationship in multi-db_schema';
	432
	433	lives_and {
4c2e2ce9	434	ok $test_schema->source('PgLoaderTest4')
c4a69b87	435	->has_relationship('pg_loader_test6s');
	436	} 'cross-schema relationship in multi-db_schema';
	437
	438	lives_and {
4c2e2ce9	439	ok $test_schema->source('PgLoaderTest8')
c4a69b87	440	->has_relationship('pg_loader_test7');
	441	} 'cross-schema relationship in multi-db_schema';
	442
	443	lives_and {
4c2e2ce9	444	ok $test_schema->source('PgLoaderTest7')
c4a69b87	445	->has_relationship('pg_loader_test8s');
	446	} 'cross-schema relationship in multi-db_schema';
	447	}
ce2f102a	448
	449	# test that views are marked as such
	450	isa_ok $schema->resultset($monikers->{pg_loader_test11})->result_source, 'DBIx::Class::ResultSource::View',
	451	'views have table_class set correctly';
fdd8ff16	452	},
fdd8ff16	453	},
a78e3fed	454	);
a78e3fed	455
9e978a19	456	if( !$dsn \|\| !$user ) {
9e978a19	457	$tester->skip_tests('You need to set the DBICTEST_PG_DSN, _USER, and _PASS environment variables');
a78e3fed	458	}
	459	else {
	460	$tester->run_tests();
	461	}
c4a69b87	462
	463	END {
	464	rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
	465	}
8e64075f	466	# vim:et sw=4 sts=4 tw=0: