SQL-Admin
view release on metacpan or search on metacpan
t/02-driver-db2-parser.t view on Meta::CPAN
}],
[ 'generated by default AS IDENTITY (START WITH +1 , INCREMENT BY +1 , CACHE 20 , MINVALUE +1 , MAXVALUE +2147483647 , NO CYCLE , NO ORDER )', {
_ => {
sequence_start_with => 1,
sequence_increment_by => 1,
sequence_minvalue => 1,
sequence_maxvalue => 2147483647,
sequence_cache => 20,
},
}],
);
}
######################################################################
######################################################################
sub default_clause_value : Rule { # ;
(
[ 'null' => { null => 1 } ],
[ 'current time' => { current_time => 1 } ],
[ 'current timestamp' => { current_timestamp => 1 } ],
[ 'current date' => { current_date => 1 } ],
[ '\'2011-01-01\'' => { string => '2011-01-01' } ],
);
}
######################################################################
######################################################################
sub default_clause : Rule { # ;
(
[ 'default', { _ => {} } ],
[ 'with default', { _ => {} } ],
[ 'default null', { _ => { null => 1 } } ],
[ 'default current time' => { _ => { current_time => 1}} ],
[ 'default current timestamp' => { _ => { current_timestamp => 1}} ],
[ 'default current date' => { _ => { current_date => 1}} ],
[ 'default \'2011-01-01\'' => { _ => { string => '2011-01-01' } } ],
);
}
######################################################################
######################################################################
sub referential_update_action : Rule { # ;
(
[ 'CASCADE' => undef ], # undef: not supported by DB2
[ 'SET NULL' => undef ],
[ 'SET DEFAULT' => undef ],
[ 'RESTRICT' => 'restrict' ],
[ 'NO ACTION' => 'no_action' ],
);
}
######################################################################
######################################################################
sub referential_delete_action : Rule { # ;
(
[ 'SET DEFAULT' => undef ], # not supported by DB2
[ 'CASCADE' => 'cascade' ],
[ 'SET NULL' => 'set_null' ],
[ 'RESTRICT' => 'restrict' ],
[ 'NO ACTION' => 'no_action' ],
);
}
######################################################################
######################################################################
sub update_rule : Rule { # ;
(
[ 'ON UPDATE RESTRICT', { update_rule => 'restrict' } ],
[ 'ON UPDATE NO ACTION', { update_rule => 'no_action' } ],
);
}
######################################################################
######################################################################
sub delete_rule : Rule { # ;
(
[ 'ON DELETE RESTRICT', { _ => 'restrict' } ],
[ 'ON DELETE NO ACTION', { _ => 'no_action' } ],
[ 'ON DELETE CASCADE', { _ => 'cascade' } ],
[ 'ON DELETE SET NULL', { _ => 'set_null' } ],
);
}
######################################################################
######################################################################
sub referential_triggered_actions : Rule { # ;
(
[ 'on delete cascade' => { delete_rule => 'cascade' } ],
[ 'on update no action' => { update_rule => 'no_action' } ],
[ 'on delete cascade on update no action' => { delete_rule => 'cascade', update_rule => 'no_action' } ],
[ 'on update no action on delete cascade' => { delete_rule => 'cascade', update_rule => 'no_action' } ],
);
}
######################################################################
######################################################################
sub referencing_column_list : Rule { # ;
(
[ '(aaa)', { _ => [ 'aaa' ] } ],
[ '(aaa, bbb)', { _ => [ 'aaa', 'bbb' ] } ],
);
}
######################################################################
######################################################################
sub referenced_column_list : Rule { # ;
(
[ '(aaa)', { _ => [ 'aaa' ] } ],
[ '(aaa, bbb)', { _ => [ 'aaa', 'bbb' ] } ],
);
}
######################################################################
######################################################################
sub referenced_table : Rule { # ;
(
[ 'aaa', { _ => { name => 'aaa' }} ],
[ 'bbb.aaa', { _ => { name => 'aaa', schema => 'bbb' }} ],
);
}
######################################################################
######################################################################
sub referenced_table_and_columns : Rule { # ;
(
[ 'aaa' => { referenced_table => { name => 'aaa' } }],
[ 'aaa (bbb, ccc)' => {
referenced_table => { name => 'aaa' },
referenced_column_list => [ 'bbb', 'ccc' ],
} ],
);
}
######################################################################
######################################################################
sub reference_specification : Rule { # ;
(
[ 'REFERENCES aaa' => {
referenced_table => { name => 'aaa' },
}],
[ 'REFERENCES aaa (bbb,ccc)' => {
referenced_table => { name => 'aaa' },
referenced_column_list => [ 'bbb', 'ccc' ],
}],
[ 'REFERENCES aaa (bbb) ENABLE QUERY OPTIMIZATION' => {
referenced_table => { name => 'aaa' },
referenced_column_list => [ 'bbb' ],
}],
[ 'REFERENCES aaa (bbb) ON UPDATE RESTRICT ON DELETE CASCADE' => {
referenced_table => { name => 'aaa' },
referenced_column_list => [ 'bbb' ],
update_rule => 'restrict',
delete_rule => 'cascade',
}],
);
}
######################################################################
######################################################################
sub constraint_name_definition : Rule { # ;
(
[ 'CONSTRAINT aaa', { constraint_name => 'aaa' } ],
);
}
######################################################################
######################################################################
sub unique_constraint : Rule { # ;
(
[ 'UNIQUE (aaa)', { _ => { column_list => [ 'aaa' ] } } ],
[ 'UNIQUE (aaa, bbb)', { _ => { column_list => [ 'aaa', 'bbb' ] } } ],
);
}
######################################################################
######################################################################
sub primary_key_constraint : Rule { # ;
(
[ 'PRIMARY KEY (aaa)', { _ => { column_list => [ 'aaa' ] } } ],
[ 'PRIMARY KEY (aaa, bbb)', { _ => { column_list => [ 'aaa', 'bbb' ] } } ],
);
}
######################################################################
######################################################################
sub foreign_key_constraint : Rule { # ;
(
[ 'FOREIGN KEY (aaa) REFERENCES bbb' => { _ => {
referencing_column_list => [ 'aaa' ],
referenced_table => { name => 'bbb' },
}}],
[ 'FOREIGN KEY (aaa) REFERENCES bbb.ccc (ddd) ON DELETE CASCADE' => { _ => {
referencing_column_list => [ 'aaa' ],
referenced_table => { name => 'ccc', schema => 'bbb' },
referenced_column_list => [ 'ddd' ],
delete_rule => 'cascade',
}}],
);
}
######################################################################
######################################################################
sub table_constraint_definition : Rule { # ;
(
[ 'UNIQUE (aaa)', {
'unique_constraint' => {
column_list => [ 'aaa' ],
}
}],
[ 'CONSTRAINT xyz UNIQUE (aaa)', {
'unique_constraint' => {
constraint_name => 'xyz',
column_list => [ 'aaa' ],
}
}],
[ 'CONSTRAINT xyz PRIMARY KEY (aaa)', {
'primary_key_constraint' => {
constraint_name => 'xyz',
column_list => [ 'aaa' ],
}
}],
[ 'CONSTRAINT xyz FOREIGN KEY (aaa) REFERENCES bbb', {
'foreign_key_constraint' => {
constraint_name => 'xyz',
referencing_column_list => [ 'aaa' ],
referenced_table => { name => 'bbb' }
}
}],
);
}
######################################################################
######################################################################
sub add_constraint : Rule { # ;
(
[ 'ADD UNIQUE (aaa)', { _ => {
'unique_constraint' => {
column_list => [ 'aaa' ],
}
}}],
[ 'ADD CONSTRAINT xyz UNIQUE (aaa)', { _ => {
'unique_constraint' => {
constraint_name => 'xyz',
column_list => [ 'aaa' ],
}
}}],
[ 'ADD CONSTRAINT xyz PRIMARY KEY (aaa)', { _ => {
'primary_key_constraint' => {
constraint_name => 'xyz',
column_list => [ 'aaa' ],
}
}}],
[ 'ADD CONSTRAINT xyz FOREIGN KEY (aaa) REFERENCES bbb', { _ => {
'foreign_key_constraint' => {
constraint_name => 'xyz',
referencing_column_list => [ 'aaa' ],
referenced_table => { name => 'bbb' }
}
}}],
);
}
######################################################################
######################################################################
sub column_not_null : Rule { # ;
(
[ 'NOT NULL' => { _ => 1 } ],
);
}
######################################################################
######################################################################
sub column_constraint_definition : Rule { # ;
(
[ 'UNIQUE' => { column_unique => 1 } ],
[ 'PRIMARY KEY' => { column_primary_key => 1 } ],
[ 'CONSTRAINT aaa PRIMARY KEY' => { constraint_name => 'aaa', column_primary_key => 1 } ],
[ 'REFERENCES bbb.aaa (id)' => {
referenced_table => { schema => 'bbb', name => 'aaa' },
referenced_column_list => [ 'id' ],
} ]
);
}
######################################################################
######################################################################
sub column_definition : Rule { # ;
(
[ 'aaa INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH +1000)', { _ => {
column_name => 'aaa',
data_type => 'int4',
column_not_null => 1,
autoincrement => {
sequence_start_with => 1000,
},
}}],
[ 'aaa INT NOT NULL PRIMARY KEY REFERENCES bbb ON DELETE CASCADE', { _ => {
column_name => 'aaa',
data_type => 'int4',
column_not_null => 1,
column_primary_key => 1,
referenced_table => { name => 'bbb' },
delete_rule => 'cascade',
}}],
[ 'aaa GENERATED AS IDENTITY', { _ => {
column_name => 'aaa',
autoincrement => {},
}}],
[ 'aaa varchar(32) WITH DEFAULT', { _ => {
column_name => 'aaa',
data_type => 'varchar',
size => 32,
default_clause => {},
}}],
[ 'aaa varchar(32) DEFAULT NULL', { _ => {
column_name => 'aaa',
data_type => 'varchar',
size => 32,
default_clause => { null => 1 },
}}],
[ 'aaa date DEFAULT CURRENT DATE', { _ => {
column_name => 'aaa',
data_type => 'date',
default_clause => { current_date => 1 },
}}],
[ 'aaa varchar(32) DEFAULT \'\'', { _ => {
column_name => 'aaa',
data_type => 'varchar',
size => 32,
default_clause => { string => '' },
}}],
[ 'aaa INTEGER NOT NULL WITH DEFAULT 0', { _ => {
column_name => 'aaa',
data_type => 'int4',
column_not_null => 1,
default_clause => { numeric_constant => 0 },
}}],
);
}
######################################################################
######################################################################
sub column_definition_list : Rule { # ;
(
[ '(aaa int)' => [
{ column_definition => { column_name => 'aaa', data_type => 'int4' } },
]],
[ '(aaa int, bbb date)' => [
{ column_definition => { column_name => 'aaa', data_type => 'int4' } },
{ column_definition => { column_name => 'bbb', data_type => 'date' } },
]],
[ '(AAA INTEGER NOT NULL WITH DEFAULT 0)', [
{ column_definition => {
column_name => 'aaa',
data_type => 'int4',
column_not_null => 1,
default_clause => { numeric_constant => 0 },
}}
]],
);
}
t/02-driver-db2-parser.t view on Meta::CPAN
{ column_definition => { column_name => 'aaa', data_type => 'int4', column_not_null => 1 } },
]}],
[ 'ADD COLUMN ("aaa" integer with default 0)' => {_ => [
{ column_definition => { column_name => 'aaa', data_type => 'int4', default_clause => { numeric_constant => 0 } } },
]}],
);
}
######################################################################
######################################################################
sub alter_table : Rule { # ;
(
[ 'ALTER TABLE bbb.aaa ADD COLUMN ( ccc INTEGER NOT NULL WITH DEFAULT 0 )', {
_ => {
table_name => { schema => 'bbb', name => 'aaa' },
alter_table_actions => [
{ add_column => [ { column_definition => {
column_name => 'ccc',
data_type => 'int4',
column_not_null => 1,
default_clause => { numeric_constant => 0 },
}}]},
]},
}],
[ join (' ', 'ALTER TABLE bbb.aaa ADD COLUMN (',
'ccc VARCHAR(50) ,',
'ddd SMALLINT ,',
'eee BIGINT)' ), { _ => {
table_name => { schema => 'bbb', name => 'aaa' },
alter_table_actions => [
{ add_column => [ { column_definition => {
column_name => 'ccc',
data_type => 'varchar',
size => 50,
}}, { column_definition => {
column_name => 'ddd',
data_type => 'int2',
}}, { column_definition => {
column_name => 'eee',
data_type => 'int8',
}}, ]},
]}, }],
[ join (' ', (
'ALTER TABLE bbb.aaa',
'ADD CONSTRAINT ccc FOREIGN KEY',
'(ddd, eee) REFERENCES bbb.fff (ggg, hhh)',
'ON DELETE CASCADE ON UPDATE NO ACTION',
'ENFORCED ENABLE QUERY OPTIMIZATION',
)), { _ => {
table_name => { schema => 'bbb', name => 'aaa' },
alter_table_actions => [
{ add_constraint => { foreign_key_constraint => {
constraint_name => 'ccc',
referencing_column_list => [ 'ddd', 'eee' ],
referenced_table => { schema => 'bbb', name => 'fff' },
referenced_column_list => [ 'ggg', 'hhh' ],
update_rule => 'no_action',
delete_rule => 'cascade',
}}},
],
}} ],
);
}
sub comment_on : Rule { # ;
(
[ 'COMMENT ON COLUMN "aaa"."bbb"."ccc" IS \'ddd eee\'' => {
# _ => {
# column_name => 'ccc',
# table_name => { schema => 'aaa', name => 'bbb' },
# string => 'ddd eee',
# }
} ],
);
}
######################################################################
######################################################################
sub create_schema : Rule { # ;
(
[ 'CREATE SCHEMA "aaa "', { _ => {
schema_identifier => 'aaa',
}}]
);
}
######################################################################
######################################################################
sub create_table : Rule { # ;
(
[ join ('', (
' CREATE TABLE aaa.bbb (',
' ccc INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , CACHE 20 , MINVALUE +1 , MAXVALUE +2147483647 , NO CYCLE , NO ORDER ) ,',
' ddd VARCHAR(40) NOT NULL ,',
' eee VARCHAR(40) )',
' IN fff',
)), {
_ => {
table_name => { schema => 'aaa', name => 'bbb' },
table_hints => {
},
table_content => [ { column_definition => {
column_name => 'ccc',
data_type => 'int4',
column_not_null => 1,
autoincrement => {
sequence_start_with => 1,
sequence_increment_by => 1,
sequence_cache => 20,
sequence_minvalue => 1,
sequence_maxvalue => 2147483647,
},
}}, { column_definition => {
( run in 0.972 second using v1.01-cache-2.11-cpan-39bf76dae61 )