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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            my $code = slurp_file $filename;

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

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

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

            $code = slurp_file $filename;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                delete $uniqs{primary};

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                delete $uniqs{primary};

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

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

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

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

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

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

            is_deeply { $schema->source($monikers->{pg_loader_test12})->unique_constraints },
                {}, 'unique indexes with expressions are not dumped';
        },
    },
);

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

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