Revision history for Perl extension DBIx::Class::Schema::Loader
+ - better type info for Oracle
- preliminary Informix support
- unregister dropped sources on rescan
- added 'preserve_case' option with support for SQLite, mysql, MSSQL and
- remove extra select for _filter_tables
- option to promote non-nullable unique constraints to PK (prefer int
columns when more than one) (RT#51696)
+ - figure out how to represent Informix 'DATETIME YEAR TO FRACTION(5)' in a
+ way that SQLT will like
+ - support domains (aka custom data types) as a Schema deploy hook
+ - when checking whether to delete size from a type definition, use an
+ inclusive list rather than an exclusive list, in order to support domains
+ - add hashref form of generate_pod to control which POD is generated
- Relationships
- Re-scan relations/tables after initial relation setup to find ->many_to_many() relations to be set up
- table/column comments
- introspect on_update/on_delete/is_deferrable
- introspect view SQL
+ - domains?
- Pg
- introspect on_update/on_delete/is_deferrable
- introspect view SQL
- preserve_case mode
+ - domains
- DB2
- data_type tests
- table/column comments
- introspect on_update/on_delete/is_deferrable
- introspect view SQL
- preserve_case mode
+ - domains
- Oracle
- data_type tests
- table/column comments
- introspect on_update/on_delete/is_deferrable
- introspect view SQL
- preserve_case mode
+ - domains
- Sybase ASE
- table/column comments
- introspect on_update/on_delete/is_deferrable
- introspect view SQL
+ - domains
- MSSQL
- table/column comments
- introspect on_update/on_delete/is_deferrable
- introspect view SQL
- computed column support
+ - domains
- Optimization
- use one query for whole table instead of query-per-column in _columns_info_for
- use placeholders when available
- introspect on_update/on_delete/is_deferrable
- introspect view SQL
- preserve_case mode
+ - domains
- Firebird
- table/column comments
- introspect on_update/on_delete/is_deferrable
- introspect view SQL
+ - domains
- Informix
- data_type tests
- preserve_case mode
- table/column comments
- introspect on_update/on_delete/is_deferrable
- introspect view SQL
+ - domains
}
sub _columns_info_for {
- my ($self, $table) = @_;
- return $self->next::method(uc $table);
-}
+ my ($self, $table) = (shift, shift);
-sub _extra_column_info {
- my ($self, $table, $column, $info, $dbi_info) = @_;
- my %extra_info;
+ my $result = $self->next::method(uc $table, @_);
my $dbh = $self->schema->storage->dbh;
- my $sth = $dbh->prepare_cached(
- q{
- SELECT COUNT(*)
- FROM all_triggers ut JOIN all_trigger_cols atc USING (trigger_name)
- WHERE atc.table_name = ? AND atc.column_name = ?
- AND lower(column_usage) LIKE '%new%' AND lower(column_usage) LIKE '%out%'
- AND trigger_type = 'BEFORE EACH ROW' AND lower(triggering_event) LIKE '%insert%'
- },
- {}, 1);
- $sth->execute($table, $column);
- if ($sth->fetchrow_array) {
- $extra_info{is_auto_increment} = 1;
+ my $sth = $dbh->prepare_cached(q{
+SELECT atc.column_name
+FROM all_triggers ut
+JOIN all_trigger_cols atc USING (trigger_name)
+WHERE atc.table_name = ?
+AND lower(column_usage) LIKE '%new%' AND lower(column_usage) LIKE '%out%'
+AND upper(trigger_type) LIKE '%BEFORE EACH ROW%' AND lower(triggering_event) LIKE '%insert%'
+ }, {}, 1);
+
+ $sth->execute(uc $table);
+
+ while (my ($col_name) = $sth->fetchrow_array) {
+ $result->{lc $col_name}{is_auto_increment} = 1;
+ }
+
+ while (my ($col, $info) = each %$result) {
+ no warnings 'uninitialized';
+
+ if ($info->{data_type} =~ /^(?:n?[cb]lob|long(?: raw)?|bfile|date|binary_(?:float|double)|rowid)\z/i) {
+ delete $info->{size};
+ }
+
+ if ($info->{data_type} =~ /^n(?:var)?char2?\z/i) {
+ $info->{size} = $info->{size} / 2;
+ }
+ elsif (lc($info->{data_type}) eq 'number') {
+ $info->{data_type} = 'numeric';
+
+ if (eval { $info->{size}[0] == 38 && $info->{size}[1] == 0 }) {
+ $info->{data_type} = 'integer';
+ delete $info->{size};
+ }
+ }
+ elsif (my ($precision) = $info->{data_type} =~ /^timestamp\((\d+)\)(?: with (?:local )?time zone)?\z/i) {
+ $info->{data_type} = join ' ', $info->{data_type} =~ /[a-z]+/ig;
+
+ if ($precision == 6) {
+ delete $info->{size};
+ }
+ else {
+ $info->{size} = $precision;
+ }
+ }
+ elsif (($precision) = $info->{data_type} =~ /^interval year\((\d+)\) to month\z/i) {
+ $info->{data_type} = join ' ', $info->{data_type} =~ /[a-z]+/ig;
+
+ if ($precision == 2) {
+ delete $info->{size};
+ }
+ else {
+ $info->{size} = $precision;
+ }
+ }
+ elsif (my ($day_precision, $second_precision) = $info->{data_type} =~ /^interval day\((\d+)\) to second\((\d+)\)\z/i) {
+ $info->{data_type} = join ' ', $info->{data_type} =~ /[a-z]+/ig;
+
+ if ($day_precision == 2 && $second_precision == 6) {
+ delete $info->{size};
+ }
+ else {
+ $info->{size} = [ $day_precision, $second_precision ];
+ }
+ }
+ elsif (lc($info->{data_type}) eq 'urowid' && $info->{size} == 4000) {
+ delete $info->{size};
+ }
+
+ if (eval { lc(${ $info->{default_value} }) eq 'sysdate' }) {
+ ${ $info->{default_value} } = 'current_timestamp';
+ }
}
- return \%extra_info;
+ return $result;
}
=head1 SEE ALSO
=cut
1;
+# vim:et sts=4 sw=4 tw=0:
user => $user,
password => $password,
data_types => {
- bigint => { data_type => 'bigint' },
- int8 => { data_type => 'bigint' },
- bigserial => { data_type => 'bigint', is_auto_increment => 1 },
- serial8 => { data_type => 'bigint', is_auto_increment => 1 },
- bit => { data_type => 'bit' },
- boolean => { data_type => 'boolean' },
- bool => { data_type => 'boolean' },
- box => { data_type => 'box' },
- bytea => { data_type => 'bytea' },
- cidr => { data_type => 'cidr' },
- circle => { data_type => 'circle' },
- date => { data_type => 'date' },
- 'double precision' => { data_type => 'double precision' },
- float8 => { data_type => 'double precision' },
- inet => { data_type => 'inet' },
+ # http://www.postgresql.org/docs/7.4/interactive/datatype.html
+ #
+ # Numeric Types
+ boolean => { data_type => 'boolean' },
+ bool => { data_type => 'boolean' },
+
+ 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' },
- interval => { data_type => 'interval' },
- 'interval(2)' => { size => 2, data_type => 'interval' },
- line => { data_type => 'line' },
- lseg => { data_type => 'lseg' },
- macaddr => { data_type => 'macaddr' },
- money => { data_type => 'money' },
- path => { data_type => 'path' },
- point => { data_type => 'point' },
- polygon => { data_type => 'polygon' },
- real => { data_type => 'real' },
- float4 => { data_type => 'real' },
- smallint => { data_type => 'smallint' },
- int2 => { data_type => 'smallint' },
serial => { data_type => 'integer', is_auto_increment => 1 },
serial4 => { data_type => 'integer', is_auto_increment => 1 },
- text => { data_type => 'text' },
- time => { data_type => 'time without time zone' },
- 'time(2)' => { size => 2, data_type => 'time without time zone' },
+ 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)' => { size => [6,3], data_type => 'numeric' },
+ 'decimal(6,3)' => { size => [6,3], data_type => 'numeric' },
+
+ # Bit String Types
+ #
+ # XXX alias 'bit varying' to 'varbit'
+ 'bit varying(2)' => { size => 2, data_type => 'bit varying' },
+ 'varbit(2)' => { size => 2, data_type => 'bit varying' },
+ 'varbit' => { size => 1, data_type => 'bit varying' },
+ # XXX support bit(n)
+ bit => { data_type => 'bit' },
+
+ # 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
+ # XXX alias 'character varying' to 'varchar'
+ 'character varying(2)' => { size => 2, data_type => 'character varying' },
+ 'varchar(2)' => { size => 2, data_type => 'character varying' },
+
+ # XXX alias 'character' to 'char'
+ 'character(2)' => { size => 2, data_type => 'character' },
+ 'char(2)' => { size => 2, data_type => 'character' },
+ 'character' => { size => 1, data_type => 'character' },
+ 'char' => { size => 1, data_type => 'character' },
+ text => { data_type => 'text' },
+
+ # Datetime Types
+ date => { data_type => 'date' },
+ interval => { data_type => 'interval' },
+ 'interval(2)' => { size => 2, data_type => 'interval' },
+ time => { data_type => 'time without time zone' },
+ 'time(2)' => { size => 2, data_type => 'time without time zone' },
'time without time zone' => { data_type => 'time without time zone' },
'time(2) without time zone' => { size => 2, data_type => 'time without time zone' },
'time with time zone' => { data_type => 'time with time zone' },
'time(2) with time zone' => { size => 2, data_type => 'time with time zone' },
+
+ # XXX alias 'timestamp without time zone' to 'timestamp'
timestamp => { data_type => 'timestamp without time zone' },
'timestamp default current_timestamp'
=> { data_type => 'timestamp without time zone', default_value => \'current_timestamp' },
'timestamp(2)' => { size => 2, data_type => 'timestamp without time zone' },
'timestamp without time zone' => { data_type => 'timestamp without time zone' },
'timestamp(2) without time zone' => { size => 2, data_type => 'timestamp without time zone' },
+
'timestamp with time zone' => { data_type => 'timestamp with time zone' },
'timestamp(2) with time zone' => { size => 2, data_type => 'timestamp with time zone' },
- 'bit varying(2)' => { size => 2, data_type => 'bit varying' },
- 'varbit(2)' => { size => 2, data_type => 'bit varying' },
- 'character varying(2)' => { size => 2, data_type => 'character varying' },
- 'varchar(2)' => { size => 2, data_type => 'character varying' },
- 'character(2)' => { size => 2, data_type => 'character' },
- 'char(2)' => { size => 2, data_type => 'character' },
- 'numeric(6, 3)' => { size => [6,3], data_type => 'numeric' },
- 'decimal(6, 3)' => { size => [6,3], data_type => 'numeric' },
- numeric => { data_type => 'numeric' },
- decimal => { data_type => 'numeric' },
- 'float(24)' => { data_type => 'real' },
- 'float(25)' => { data_type => 'double precision' },
- 'float(53)' => { data_type => 'double precision' },
- float => { data_type => 'double precision' },
+
+ # Blob Types
+ bytea => { data_type => 'bytea' },
},
extra => {
create => [
dsn => $dsn,
user => $user,
password => $password,
+ data_types => {
+ # From:
+ # http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330
+ #
+ # These tests require at least Oracle 9.2, because of the VARCHAR to
+ # VARCHAR2 casting.
+ #
+ # Character Types
+ 'char' => { data_type => 'char', size => 1 },
+ 'char(11)' => { data_type => 'char', size => 11 },
+ 'nchar' => { data_type => 'nchar', size => 1 },
+ 'nchar(11)' => { data_type => 'nchar', size => 11 },
+ 'varchar(20)' => { data_type => 'varchar2', size => 20 },
+ 'varchar2(20)' => { data_type => 'varchar2', size => 20 },
+ 'nvarchar2(20)'=> { data_type => 'nvarchar2', size => 20 },
+
+ # Numeric Types
+ #
+ # everything is alised to NUMBER
+ #
+ 'decimal' => { data_type => 'integer' },
+ 'dec' => { data_type => 'integer' },
+ 'numeric' => { data_type => 'integer' },
+
+ 'decimal(3)' => { data_type => 'numeric', size => [3,0] },
+ 'dec(3)' => { data_type => 'numeric', size => [3,0] },
+ 'numeric(3)' => { data_type => 'numeric', size => [3,0] },
+
+ 'decimal(3,3)' => { data_type => 'numeric', size => [3,3] },
+ 'dec(3,3)' => { data_type => 'numeric', size => [3,3] },
+ 'numeric(3,3)' => { data_type => 'numeric', size => [3,3] },
+
+ 'integer' => { data_type => 'integer' },
+ 'int' => { data_type => 'integer' },
+ 'smallint' => { data_type => 'integer' },
+
+ 'binary_float' => { data_type => 'binary_float' },
+ 'binary_double' => { data_type => 'binary_double' },
+
+ # Blob Types
+ 'raw(50)' => { data_type => 'raw', size => 50 },
+ 'clob' => { data_type => 'clob' },
+ 'nclob' => { data_type => 'nclob' },
+ 'blob' => { data_type => 'blob' },
+ 'bfile' => { data_type => 'bfile' },
+ # these must be tested one at a time, can't have 2 longs in one table
+ # XXX add overrides to type tester to handle this
+# 'long' => { data_type => 'long' },
+ 'long raw' => { data_type => 'long raw' },
+
+ # Datetime Types
+ 'date' => { data_type => 'date' },
+ 'date default sysdate'
+ => { data_type => 'date', default_value => \'current_timestamp' },
+ 'timestamp' => { data_type => 'timestamp' },
+ 'timestamp default current_timestamp'
+ => { data_type => 'timestamp', default_value => \'current_timestamp' },
+ 'timestamp(3)' => { data_type => 'timestamp', size => 3 },
+ 'timestamp with time zone'
+ => { data_type => 'timestamp with time zone' },
+ 'timestamp(3) with time zone'
+ => { data_type => 'timestamp with time zone', size => 3 },
+ 'timestamp with local time zone'
+ => { data_type => 'timestamp with local time zone' },
+ 'timestamp(3) with local time zone'
+ => { data_type => 'timestamp with local time zone', size => 3 },
+ 'interval year to month'
+ => { data_type => 'interval year to month' },
+ 'interval year(3) to month'
+ => { data_type => 'interval year to month', size => 3 },
+ 'interval day to second'
+ => { data_type => 'interval day to second' },
+ 'interval day(3) to second'
+ => { data_type => 'interval day to second', size => [3,6] },
+ 'interval day to second(3)'
+ => { data_type => 'interval day to second', size => [2,3] },
+ 'interval day(3) to second(3)'
+ => { data_type => 'interval day to second', size => [3,3] },
+
+ # Other Types
+ 'rowid' => { data_type => 'rowid' },
+ 'urowid' => { data_type => 'urowid' },
+ 'urowid(3333)' => { data_type => 'urowid', size => 3333 },
+ },
);
if( !$dsn || !$user ) {
else {
$tester->run_tests();
}
+# vim:et sw=4 sts=4 tw=0:
dsn => $dsn,
user => $user,
password => $password,
-# Test data types, see http://ispirer.com/wiki/sqlways/sybase/data-types
data_types => {
+ # http://ispirer.com/wiki/sqlways/sybase/data-types
+ # XXX organize by category
'integer identity' => { data_type => 'integer', is_auto_increment => 1 },
'AS getdate()' => { data_type => undef, inflate_datetime => 1, default_value => \'getdate()' },
text => { data_type => 'text' },
# http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/rf-datatypes.html
#
# Numeric types
+ # XXX rewrite low-precision floats to 'real'
'bit' => { data_type => 'bit' },
'tinyint' => { data_type => 'tinyint' },
'smallint' => { data_type => 'smallint' },
# http://www.ibphoenix.com/downloads/60DataDef.zip
#
# Numeric types
+ # XXX rewrite low precision floats to 'real'
'smallint' => { data_type => 'smallint' },
'int' => { data_type => 'integer' },
'integer' => { data_type => 'integer' },
my @size = split /,/, $size;
# some DBs don't like very long column names
- if ($self->{vendor} =~ /^firebird|sqlanywhere\z/i) {
+ if ($self->{vendor} =~ /^(?:firebird|sqlanywhere|oracle)\z/i) {
my ($col_def, $default) = $type_alias =~ /^(.*)(default.*)?\z/i;
$type_alias = substr $col_def, 0, 15;
projects / dbsrgits/DBIx-Class-Schema-Loader.git / commitdiff