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