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

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

use strict;
use warnings;
use utf8;
use DBIx::Class::Schema::Loader 'make_schema_at';
use DBIx::Class::Schema::Loader::Utils qw/no_warnings slurp_file/;
use Test::More;
use Test::Exception;
use Try::Tiny;
use File::Path 'rmtree';
use namespace::clean;

use lib qw(t/lib);
use dbixcsl_common_tests ();
use dbixcsl_test_dir '$tdir';

use constant EXTRA_DUMP_DIR => "$tdir/pg_extra_dump";

my $dsn      = $ENV{DBICTEST_PG_DSN} || '';
my $user     = $ENV{DBICTEST_PG_USER} || '';
my $password = $ENV{DBICTEST_PG_PASS} || '';

dbixcsl_common_tests->new(
    vendor      => 'Pg',
    auto_inc_pk => 'SERIAL NOT NULL PRIMARY KEY',
    dsn         => $dsn,
    user        => $user,
    password    => $password,
    loader_options  => { preserve_case => 1 },
    connect_info_opts => {
        pg_enable_utf8 => 1,
        on_connect_do  => [ 'SET client_min_messages=WARNING' ],
    },
    quote_char  => '"',
    default_is_deferrable => 0,
    default_on_clause => 'NO ACTION',
    data_types  => {
        # http://www.postgresql.org/docs/7.4/interactive/datatype.html
        #
        # Numeric Types
	boolean     => { data_type => 'boolean' },
	bool        => { data_type => 'boolean' },
        'bool default false'
                    => { data_type => 'boolean', default_value => \'false' },
        'bool default true'
                    => { data_type => 'boolean', default_value => \'true' },
        'bool default 0::bool'
                    => { data_type => 'boolean', default_value => \'false' },
        'bool default 1::bool'
                    => { data_type => 'boolean', default_value => \'true' },

	bigint      => { data_type => 'bigint' },
	int8        => { data_type => 'bigint' },
	bigserial   => { data_type => 'bigint', is_auto_increment => 1 },
	serial8     => { data_type => 'bigint', is_auto_increment => 1 },
	integer     => { data_type => 'integer' },
	int         => { data_type => 'integer' },
	int4        => { data_type => 'integer' },
	serial      => { data_type => 'integer', is_auto_increment => 1 },
	serial4     => { data_type => 'integer', is_auto_increment => 1 },
	smallint    => { data_type => 'smallint' },
	int2        => { data_type => 'smallint' },

	money       => { data_type => 'money' },

	'double precision' => { data_type => 'double precision' },
	float8             => { data_type => 'double precision' },
	real               => { data_type => 'real' },
	float4             => { data_type => 'real' },
        'float(24)'        => { data_type => 'real' },
        'float(25)'        => { data_type => 'double precision' },
        'float(53)'        => { data_type => 'double precision' },
        float              => { data_type => 'double precision' },

        numeric        => { data_type => 'numeric' },
        decimal        => { data_type => 'numeric' },
	'numeric(6,3)' => { data_type => 'numeric', size => [6,3] },
	'decimal(6,3)' => { data_type => 'numeric', size => [6,3] },

        # Bit String Types
	'bit varying(2)' => { data_type => 'varbit', size => 2 },
	'varbit(2)'      => { data_type => 'varbit', size => 2 },
	'varbit'         => { data_type => 'varbit' },
	bit              => { data_type => 'bit', size => 1 },
	'bit(3)'         => { data_type => 'bit', size => 3 },

        # Network Types
	inet    => { data_type => 'inet' },
	cidr    => { data_type => 'cidr' },
	macaddr => { data_type => 'macaddr' },

        # Geometric Types
	point   => { data_type => 'point' },
	line    => { data_type => 'line' },
	lseg    => { data_type => 'lseg' },
	box     => { data_type => 'box' },
	path    => { data_type => 'path' },
	polygon => { data_type => 'polygon' },
	circle  => { data_type => 'circle' },

        # Character Types
	'character varying(2)'           => { data_type => 'varchar', size => 2 },
	'varchar(2)'                     => { data_type => 'varchar', size => 2 },
	'character(2)'                   => { data_type => 'char', size => 2 },
	'char(2)'                        => { data_type => 'char', size => 2 },
        # check that default null is correctly rewritten
        'char(3) default null'           => { data_type => 'char', size => 3,
                                              default_value => \'null' },
	'character'                      => { data_type => 'char', size => 1 },
	'char'                           => { data_type => 'char', size => 1 },
	text                             => { data_type => 'text' },
        # varchar with no size has unlimited size, we rewrite to 'text'
	varchar                          => { data_type => 'text',
                                              original => { data_type => 'varchar' } },
        # check default null again (to make sure ref is safe)
        'varchar(3) default null'        => { data_type => 'varchar', size => 3,
                                              default_value => \'null' },

        # Datetime Types
	date                             => { data_type => 'date' },
	interval                         => { data_type => 'interval' },
	'interval(2)'                    => { data_type => 'interval', size => 2 },
	time                             => { data_type => 'time' },
	'time(2)'                        => { data_type => 'time', size => 2 },
	'time without time zone'         => { data_type => 'time' },
	'time(2) without time zone'      => { data_type => 'time', size => 2 },
	'time with time zone'            => { data_type => 'time with time zone' },
	'time(2) with time zone'         => { data_type => 'time with time zone', size => 2 },
	timestamp                        => { data_type => 'timestamp' },
        'timestamp default now()'
                                         => { data_type => 'timestamp', default_value => \'current_timestamp',
                                              original => { default_value => \'now()' } },
	'timestamp(2)'                   => { data_type => 'timestamp', size => 2 },
	'timestamp without time zone'    => { data_type => 'timestamp' },
	'timestamp(2) without time zone' => { data_type => 'timestamp', size => 2 },

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

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

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

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

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

            my $code = slurp_file $filename;

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

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

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

            $code = slurp_file $filename;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                delete $uniqs{primary};

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                delete $uniqs{primary};

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

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

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

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

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

            # test that views are marked as such
            isa_ok $schema->resultset($monikers->{pg_loader_test11})->result_source, 'DBIx::Class::ResultSource::View',
                'views have table_class set correctly';

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

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