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

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