[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 TYPE "dbicsl.test".pg_loader_test_enum2 AS ENUM ('wibble','wobble')
            },
            q{
                CREATE TABLE "dbicsl.test".pg_loader_test7 (
                    id SERIAL PRIMARY KEY,
                    value "dbicsl.test".pg_loader_test_enum2,
                    six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
                )
            },
            q{
                CREATE TABLE "dbicsl-test".pg_loader_test8 (
                    id SERIAL PRIMARY KEY,
                    value VARCHAR(100),
                    pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
                )
            },
            # 4 through 8 are used for the multi-schema tests
            q{
                create table pg_loader_test9 (
                    id bigserial primary key
                )
            },
            q{
                create table pg_loader_test10 (
                    id bigserial primary key,
                    nine_id int,
                    foreign key (nine_id) references pg_loader_test9(id)
                        on delete restrict on update set null deferrable
                )
            },
            q{
                create view pg_loader_test11 as
                    select * from pg_loader_test1
            },
            q{
                create table pg_loader_test12 (
                    id integer not null,
                    value integer,
                    active boolean,
                    name text
                )
            },
            q{
                create unique index uniq_id_lc_name on pg_loader_test12 (
                    id, lower(name)
                )
            },
            q{
                create unique index uniq_uc_name_id on pg_loader_test12 (
                    upper(name), id
                )
            },
            q{
                create unique index pg_loader_test12_value on pg_loader_test12 (
                    value
                )
            },
            q{
                create unique index pg_loader_test12_name_active on pg_loader_test12 (
                    name
                ) where active
            },
        ],
        pre_drop_ddl => [
            'DROP SCHEMA dbicsl_test CASCADE',
            'DROP SCHEMA "dbicsl-test" CASCADE',
            'DROP SCHEMA "dbicsl.test" CASCADE',
            'DROP TYPE pg_loader_test_enum',
            'DROP VIEW pg_loader_test11',
        ],
        drop  => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
        count => 11 + 33 * 2,   # regular + multi-schema * 2
        run   => sub {
            my ($schema, $monikers, $classes) = @_;

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

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

            my $code = slurp_file $filename;

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

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

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

            $code = slurp_file $filename;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                delete $uniqs{primary};

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

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

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

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

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

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

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

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

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

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

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

                lives_and {
                    ok $rsrc = $test_schema->source('PgLoaderTest7');
                    my $col_info = $rsrc->column_info('value');
                    is $col_info->{data_type}, 'enum',
                        'enum column in schema name with dot';
                    is $col_info->{extra}{custom_type_name}, '"dbicsl.test".pg_loader_test_enum2',
                        'original data type for enum in schema name with dot';
                    is_deeply $col_info->{extra}{list}, [qw(wibble wobble)],
                        'value list for for enum in schema name with dot';
                } 'got source for table in schema name with dot';

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

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

                delete $uniqs{primary};

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

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

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

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

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

            # test that views are marked as such
            my $view_source = $schema->resultset($monikers->{pg_loader_test11})->result_source;
            isa_ok $view_source, 'DBIx::Class::ResultSource::View',
                'view result source';

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

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

END {
    rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
}
# vim:et sw=4 sts=4 tw=0:
Commit	Line	Data
406a97c2	1	use DBIx::Class::Schema::Loader::Optional::Dependencies
	2	-skip_all_without => 'test_rdbms_pg';
	3
a78e3fed	4	use strict;
fcf328c7	5	use warnings;
fcf328c7	6	use utf8;
12b86f07	7	use DBIx::Class::Schema::Loader 'make_schema_at';
fcf328c7	8	use DBIx::Class::Schema::Loader::Utils qw/no_warnings slurp_file/;
fdd8ff16	9	use Test::More;
12b86f07	10	use Test::Exception;
12b86f07	11	use Try::Tiny;
c4a69b87	12	use File::Path 'rmtree';
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{
a07eab0a	229	CREATE TYPE "dbicsl.test".pg_loader_test_enum2 AS ENUM ('wibble','wobble')
	230	},
	231	q{
c4a69b87	232	CREATE TABLE "dbicsl.test".pg_loader_test7 (
c4a69b87	233	id SERIAL PRIMARY KEY,
a07eab0a	234	value "dbicsl.test".pg_loader_test_enum2,
c4a69b87	235	six_id INTEGER UNIQUE REFERENCES "dbicsl.test".pg_loader_test6 (id)
	236	)
	237	},
	238	q{
	239	CREATE TABLE "dbicsl-test".pg_loader_test8 (
12b86f07	240	id SERIAL PRIMARY KEY,
12b86f07	241	value VARCHAR(100),
c4a69b87	242	pg_loader_test7_id INTEGER REFERENCES "dbicsl.test".pg_loader_test7 (id)
12b86f07	243	)
12b86f07	244	},
958d5fcf	245	# 4 through 8 are used for the multi-schema tests
	246	q{
	247	create table pg_loader_test9 (
	248	id bigserial primary key
	249	)
	250	},
	251	q{
	252	create table pg_loader_test10 (
	253	id bigserial primary key,
f8640ecc	254	nine_id int,
f8640ecc	255	foreign key (nine_id) references pg_loader_test9(id)
b9762446	256	on delete restrict on update set null deferrable
958d5fcf	257	)
958d5fcf	258	},
ce2f102a	259	q{
	260	create view pg_loader_test11 as
	261	select * from pg_loader_test1
	262	},
b32f8189	263	q{
	264	create table pg_loader_test12 (
	265	id integer not null,
a01ac8ee	266	value integer,
a01ac8ee	267	active boolean,
b32f8189	268	name text
	269	)
	270	},
	271	q{
	272	create unique index uniq_id_lc_name on pg_loader_test12 (
	273	id, lower(name)
	274	)
	275	},
	276	q{
	277	create unique index uniq_uc_name_id on pg_loader_test12 (
	278	upper(name), id
	279	)
	280	},
a01ac8ee	281	q{
	282	create unique index pg_loader_test12_value on pg_loader_test12 (
	283	value
	284	)
	285	},
	286	q{
	287	create unique index pg_loader_test12_name_active on pg_loader_test12 (
	288	name
	289	) where active
	290	},
fdd8ff16	291	],
7b868481	292	pre_drop_ddl => [
7b868481	293	'DROP SCHEMA dbicsl_test CASCADE',
12b86f07	294	'DROP SCHEMA "dbicsl-test" CASCADE',
12b86f07	295	'DROP SCHEMA "dbicsl.test" CASCADE',
12333562	296	'DROP TYPE pg_loader_test_enum',
ce2f102a	297	'DROP VIEW pg_loader_test11',
7b868481	298	],
b32f8189	299	drop => [ qw/pg_loader_test1 pg_loader_test2 pg_loader_test9 pg_loader_test10 pg_loader_test12/ ],
a07eab0a	300	count => 11 + 33 * 2, # regular + multi-schema * 2
fdd8ff16	301	run => sub {
	302	my ($schema, $monikers, $classes) = @_;
	303
7b868481	304	is $schema->source($monikers->{pg_loader_test1})->column_info('id')->{sequence},
	305	'dbicsl_test.myseq',
	306	'qualified sequence detected';
	307
fdd8ff16	308	my $class = $classes->{pg_loader_test1};
c4a69b87	309	my $filename = $schema->loader->get_dump_filename($class);
fdd8ff16	310
fcf328c7	311	my $code = slurp_file $filename;
fdd8ff16	312
af15ea33	313	like $code, qr/^=head1 NAME\n\n^$class - The\nTable ∑\n\n^=cut\n/m,
fdd8ff16	314	'table comment';
fdd8ff16	315
fd97abca	316	like $code, qr/^=head2 value\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
79a00530	317	'column comment and attrs';
baff904e	318
4b9fb838	319	$class = $classes->{pg_loader_test2};
c4a69b87	320	$filename = $schema->loader->get_dump_filename($class);
4b9fb838	321
fcf328c7	322	$code = slurp_file $filename;
4b9fb838	323
	324	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,
	325	'long table comment is in DESCRIPTION';
12b86f07	326
958d5fcf	327	# test on delete/update fk clause introspection
f8640ecc	328	ok ((my $rel_info = $schema->source('PgLoaderTest10')->relationship_info('nine')),
958d5fcf	329	'got rel info');
	330
	331	is $rel_info->{attrs}{on_delete}, 'RESTRICT',
	332	'ON DELETE clause introspected correctly';
	333
	334	is $rel_info->{attrs}{on_update}, 'SET NULL',
	335	'ON UPDATE clause introspected correctly';
	336
b9762446	337	is $rel_info->{attrs}{is_deferrable}, 1,
958d5fcf	338	'DEFERRABLE clause introspected correctly';
958d5fcf	339
c4a69b87	340	foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
	341	lives_and {
	342	rmtree EXTRA_DUMP_DIR;
	343
	344	my @warns;
	345	local $SIG{__WARN__} = sub {
	346	push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
	347	};
	348
12b86f07	349	make_schema_at(
c4a69b87	350	'PGMultiSchema',
12b86f07	351	{
12b86f07	352	naming => 'current',
c4a69b87	353	db_schema => $db_schema,
12b86f07	354	preserve_case => 1,
c4a69b87	355	dump_directory => EXTRA_DUMP_DIR,
c4a69b87	356	quiet => 1,
12b86f07	357	},
	358	[ $dsn, $user, $password, {
	359	on_connect_do => [ 'SET client_min_messages=WARNING' ],
	360	} ],
	361	);
12b86f07	362
c4a69b87	363	diag join "\n", @warns if @warns;
12b86f07	364
c4a69b87	365	is @warns, 0;
c4a69b87	366	} 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings';
12b86f07	367
c4a69b87	368	my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
12b86f07	369
c4a69b87	370	lives_and {
	371	ok $test_schema = PGMultiSchema->connect($dsn, $user, $password, {
	372	on_connect_do => [ 'SET client_min_messages=WARNING' ],
	373	});
	374	} 'connected test schema';
12b86f07	375
c4a69b87	376	lives_and {
4c2e2ce9	377	ok $rsrc = $test_schema->source('PgLoaderTest4');
c4a69b87	378	} 'got source for table in schema name with dash';
12b86f07	379
c4a69b87	380	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
c4a69b87	381	'column in schema name with dash';
12b86f07	382
c4a69b87	383	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
c4a69b87	384	'column in schema name with dash';
12b86f07	385
c4a69b87	386	is try { $rsrc->column_info('value')->{size} }, 100,
c4a69b87	387	'column in schema name with dash';
12b86f07	388
c4a69b87	389	lives_and {
4c2e2ce9	390	ok $rs = $test_schema->resultset('PgLoaderTest4');
c4a69b87	391	} 'got resultset for table in schema name with dash';
12b86f07	392
c4a69b87	393	lives_and {
	394	ok $row = $rs->create({ value => 'foo' });
	395	} 'executed SQL on table in schema name with dash';
12b86f07	396
5975bbe6	397	$rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_pg_loader_test5') };
12b86f07	398
c4a69b87	399	is_deeply $rel_info->{cond}, {
	400	'foreign.four_id' => 'self.id'
	401	}, 'relationship in schema name with dash';
12b86f07	402
c4a69b87	403	is $rel_info->{attrs}{accessor}, 'single',
	404	'relationship in schema name with dash';
	405
	406	is $rel_info->{attrs}{join_type}, 'LEFT',
	407	'relationship in schema name with dash';
	408
	409	lives_and {
5975bbe6	410	ok $rsrc = $test_schema->source('DbicslDashTestPgLoaderTest5');
c4a69b87	411	} 'got source for table in schema name with dash';
	412
	413	%uniqs = try { $rsrc->unique_constraints };
12b86f07	414
c4a69b87	415	is keys %uniqs, 2,
c4a69b87	416	'got unique and primary constraint in schema name with dash';
12b86f07	417
5975bbe6	418	delete $uniqs{primary};
5975bbe6	419
494e0205	420	is_deeply(
	421	(values %uniqs)[0], ['four_id'],
	422	'unique constraint is correct in schema name with dash'
	423	);
5975bbe6	424
c4a69b87	425	lives_and {
4c2e2ce9	426	ok $rsrc = $test_schema->source('PgLoaderTest6');
c4a69b87	427	} 'got source for table in schema name with dot';
12b86f07	428
c4a69b87	429	is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
c4a69b87	430	'column in schema name with dot introspected correctly';
12b86f07	431
c4a69b87	432	is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
c4a69b87	433	'column in schema name with dot introspected correctly';
12b86f07	434
c4a69b87	435	is try { $rsrc->column_info('value')->{size} }, 100,
c4a69b87	436	'column in schema name with dot introspected correctly';
12b86f07	437
c4a69b87	438	lives_and {
4c2e2ce9	439	ok $rs = $test_schema->resultset('PgLoaderTest6');
c4a69b87	440	} 'got resultset for table in schema name with dot';
12b86f07	441
c4a69b87	442	lives_and {
	443	ok $row = $rs->create({ value => 'foo' });
	444	} 'executed SQL on table in schema name with dot';
12b86f07	445
c4a69b87	446	$rel_info = try { $rsrc->relationship_info('pg_loader_test7') };
12b86f07	447
c4a69b87	448	is_deeply $rel_info->{cond}, {
	449	'foreign.six_id' => 'self.id'
	450	}, 'relationship in schema name with dot';
12b86f07	451
c4a69b87	452	is $rel_info->{attrs}{accessor}, 'single',
c4a69b87	453	'relationship in schema name with dot';
12b86f07	454
c4a69b87	455	is $rel_info->{attrs}{join_type}, 'LEFT',
c4a69b87	456	'relationship in schema name with dot';
12b86f07	457
c4a69b87	458	lives_and {
4c2e2ce9	459	ok $rsrc = $test_schema->source('PgLoaderTest7');
a07eab0a	460	my $col_info = $rsrc->column_info('value');
	461	is $col_info->{data_type}, 'enum',
	462	'enum column in schema name with dot';
	463	is $col_info->{extra}{custom_type_name}, '"dbicsl.test".pg_loader_test_enum2',
	464	'original data type for enum in schema name with dot';
	465	is_deeply $col_info->{extra}{list}, [qw(wibble wobble)],
	466	'value list for for enum in schema name with dot';
c4a69b87	467	} 'got source for table in schema name with dot';
	468
	469	%uniqs = try { $rsrc->unique_constraints };
	470
	471	is keys %uniqs, 2,
	472	'got unique and primary constraint in schema name with dot';
	473
5975bbe6	474	delete $uniqs{primary};
5975bbe6	475
494e0205	476	is_deeply(
	477	(values %uniqs)[0], ['six_id'],
	478	'unique constraint is correct in schema name with dot'
	479	);
5975bbe6	480
c4a69b87	481	lives_and {
4c2e2ce9	482	ok $test_schema->source('PgLoaderTest6')
c4a69b87	483	->has_relationship('pg_loader_test4');
	484	} 'cross-schema relationship in multi-db_schema';
	485
	486	lives_and {
4c2e2ce9	487	ok $test_schema->source('PgLoaderTest4')
c4a69b87	488	->has_relationship('pg_loader_test6s');
	489	} 'cross-schema relationship in multi-db_schema';
	490
	491	lives_and {
4c2e2ce9	492	ok $test_schema->source('PgLoaderTest8')
c4a69b87	493	->has_relationship('pg_loader_test7');
	494	} 'cross-schema relationship in multi-db_schema';
	495
	496	lives_and {
4c2e2ce9	497	ok $test_schema->source('PgLoaderTest7')
c4a69b87	498	->has_relationship('pg_loader_test8s');
	499	} 'cross-schema relationship in multi-db_schema';
	500	}
ce2f102a	501
ce2f102a	502	# test that views are marked as such
d7e0e0e8	503	my $view_source = $schema->resultset($monikers->{pg_loader_test11})->result_source;
	504	isa_ok $view_source, 'DBIx::Class::ResultSource::View',
	505	'view result source';
	506
	507	like $view_source->view_definition,
	508	qr/\A \s* select\b .* \bfrom \s+ pg_loader_test1 \s* \z/imsx,
	509	'view definition';
b32f8189	510
a01ac8ee	511	is_deeply
	512	{ $schema->source($monikers->{pg_loader_test12})->unique_constraints },
	513	{ pg_loader_test12_value => ['value'] },
	514	'unique indexes are dumped correctly';
fdd8ff16	515	},
fdd8ff16	516	},
406a97c2	517	)->run_tests();
c4a69b87	518
	519	END {
	520	rmtree EXTRA_DUMP_DIR unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
	521	}
8e64075f	522	# vim:et sw=4 sts=4 tw=0: