[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,
                    eleven_id int,
                    foreign key (eleven_id) references pg_loader_test9(id)
                        on delete restrict on update set null
                )
            },

        ],
        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  => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10/ ],
        count => 8 + 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('eleven')),
                '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}, 0,
                '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';
            }
        },
    },
);

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