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

use strict;
use warnings;
use utf8;
use DBIx::Class::Optional::Dependencies;
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,
                    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';
        },
    },
);

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