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

use DBIx::Class::Schema::Loader::Optional::Dependencies
    -skip_all_without => 'test_rdbms_pg';

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 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} || '';

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 },
        'bit varying'    => { data_type => 'varbit' },
        '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(0)'                    => { data_type => 'interval', size => 0 },
        'interval(2)'                    => { data_type => 'interval', size => 2 },
        time                             => { data_type => 'time' },
        'time(0)'                        => { data_type => 'time', size => 0 },
        'time(2)'                        => { data_type => 'time', size => 2 },
        'time without time zone'         => { data_type => 'time' },
        'time(0) without time zone'      => { data_type => 'time', size => 0 },
        'time with time zone'            => { data_type => 'time with time zone' },
        'time(0) with time zone'         => { data_type => 'time with time zone', size => 0 },
        '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(0)'                   => { data_type => 'timestamp', size => 0 },
        'timestamp(2)'                   => { data_type => 'timestamp', size => 2 },
        'timestamp without time zone'    => { data_type => 'timestamp' },
        'timestamp(0) without time zone' => { data_type => 'timestamp', size => 0 },
        'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },

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

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

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

            like $view_source->view_definition,
                qr/\A \s* select\b .* \bfrom \s+ pg_loader_test1 \s* \z/imsx,
                'view definition';

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

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