[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
                )
            },
        ],
        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('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';
            }
        },
    },
);

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