SELECT f.rdb$field_precision, f.rdb$field_scale, f.rdb$field_type, f.rdb$field_sub_type, t.rdb$type_name, st.rdb$type_name
FROM rdb$fields f
JOIN rdb$relation_fields rf ON rf.rdb$field_source = f.rdb$field_name
-JOIN rdb$types t ON f.rdb$field_type = t.rdb$type AND t.rdb$field_name = 'RDB$FIELD_TYPE'
-JOIN rdb$types st ON f.rdb$field_sub_type = st.rdb$type AND st.rdb$field_name = 'RDB$FIELD_SUB_TYPE'
+LEFT JOIN rdb$types t ON f.rdb$field_type = t.rdb$type AND t.rdb$field_name = 'RDB$FIELD_TYPE'
+LEFT JOIN rdb$types st ON f.rdb$field_sub_type = st.rdb$type AND st.rdb$field_name = 'RDB$FIELD_SUB_TYPE'
WHERE rf.rdb$relation_name = ?
AND rf.rdb$field_name = ?
EOF
s/\s+\z// for $type_name, $sub_type_name;
# fixups primarily for DBD::InterBase
- if ($info->{data_type} =~ /^integer|int|smallint|bigint|-9581\z/) {
- if ($precision && $type_name =~ /^LONG|INT64\z/ && $sub_type_name eq 'BLR') {
+ if ($info->{data_type} =~ /^(?:integer|int|smallint|bigint|-9581)\z/) {
+ if ($precision && $type_name =~ /^(?:LONG|INT64)\z/ && $sub_type_name eq 'BLR') {
$info->{data_type} = 'decimal';
}
- elsif ($precision && $type_name =~ /^LONG|SHORT|INT64\z/ && $sub_type_name eq 'TEXT') {
+ elsif ($precision && $type_name =~ /^(?:LONG|SHORT|INT64)\z/ && $sub_type_name eq 'TEXT') {
$info->{data_type} = 'numeric';
}
elsif ((not $precision) && $type_name eq 'INT64' && $sub_type_name eq 'BINARY') {
}
}
- if ($info->{data_type} =~ /^decimal|numeric\z/ && defined $precision && defined $scale) {
+ if ($info->{data_type} =~ /^(?:decimal|numeric)\z/ && defined $precision && defined $scale) {
if ($precision == 9 && $scale == 0) {
delete $info->{size};
}
}
# DBD::InterBase sets scale to '0' for some reason for char types
- if ($info->{data_type} =~ /^char|varchar\z/ && ref($info->{size}) eq 'ARRAY') {
+ if ($info->{data_type} =~ /^(?:char|varchar)\z/ && ref($info->{size}) eq 'ARRAY') {
$info->{size} = $info->{size}[0];
}
- elsif ($info->{data_type} !~ /^char|varchar|numeric|decimal\z/) {
+ elsif ($info->{data_type} !~ /^(?:char|varchar|numeric|decimal)\z/) {
delete $info->{size};
}
return $self->_filter_tables(\@tables, $opts);
}
-# check for IDENTITY columns
sub _columns_info_for {
- my $self = shift;
+ my $self = shift;
+ my ($table) = @_;
+
my $result = $self->next::method(@_);
- while (my ($col, $info) = each %$result) {
+ my $dbh = $self->schema->storage->dbh;
+
+ while (my ($column, $info) = each %$result) {
my $def = $info->{default_value};
if (ref $def eq 'SCALAR' && $$def eq 'autoincrement') {
delete $info->{default_value};
$info->{is_auto_increment} = 1;
}
+
+ my ($user_type) = $dbh->selectrow_array(<<'EOF', {}, $table, lc $column);
+SELECT ut.type_name
+FROM systabcol tc
+JOIN systab t ON tc.table_id = t.table_id
+JOIN sysusertype ut on tc.user_type = ut.type_id
+WHERE t.table_name = ? AND lower(tc.column_name) = ?
+EOF
+ $info->{data_type} = $user_type if defined $user_type;
+
+ if ($info->{data_type} eq 'double') {
+ $info->{data_type} = 'double precision';
+ }
+
+ if ($info->{data_type} =~ /^(?:char|varchar|binary|varbinary)\z/ && ref($info->{size}) eq 'ARRAY') {
+ $info->{size} = $info->{size}[0];
+ }
+ elsif ($info->{data_type} !~ /^(?:char|varchar|binary|varbinary|numeric|decimal)\z/) {
+ delete $info->{size};
+ }
+
+ my $sth = $dbh->prepare(<<'EOF');
+SELECT tc.width, tc.scale
+FROM systabcol tc
+JOIN systab t ON t.table_id = tc.table_id
+WHERE t.table_name = ? AND lower(tc.column_name) = ?
+EOF
+ $sth->execute($table, lc $column);
+ my ($width, $scale) = $sth->fetchrow_array;
+ $sth->finish;
+
+ if ($info->{data_type} =~ /^(?:numeric|decimal)\z/) {
+ # We do not check for the default precision/scale, because they can be changed as PUBLIC database options.
+ $info->{size} = [$width, $scale];
+ }
+ elsif ($info->{data_type} =~ /^(?:n(?:varchar|char) | varbit)\z/x) {
+ $info->{size} = $width;
+ }
+
+ delete $info->{default_value} if ref($info->{default_value}) eq 'SCALAR' && ${ $info->{default_value} } eq 'NULL';
}
return $result;
=cut
1;
+# vim:et sw=4 sts=4 tw=0:
password => $odbc_password,
} : ()),
],
+ data_types => {
+ # http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/rf-datatypes.html
+ #
+ # Numeric types
+ 'bit' => { data_type => 'bit' },
+ 'tinyint' => { data_type => 'tinyint' },
+ 'smallint' => { data_type => 'smallint' },
+ 'int' => { data_type => 'integer' },
+ 'integer' => { data_type => 'integer' },
+ 'bigint' => { data_type => 'bigint' },
+ 'float' => { data_type => 'float' },
+ 'real' => { data_type => 'float' },
+ 'double' => { data_type => 'double precision' },
+ 'double precision' =>
+ { data_type => 'double precision' },
+
+ 'float(2)' => { data_type => 'float' },
+ 'float(24)' => { data_type => 'float' },
+ 'float(25)' => { data_type => 'double precision' },
+ 'float(53)' => { data_type => 'double precision' },
+
+ # This test only works with the default precision and scale options.
+ #
+ # They are preserved even for the default values, because the defaults
+ # can be changed.
+ 'decimal' => { data_type => 'decimal', size => [30,6] },
+ 'dec' => { data_type => 'decimal', size => [30,6] },
+ 'numeric' => { data_type => 'numeric', size => [30,6] },
+
+ 'decimal(3)' => { data_type => 'decimal', size => [3,0] },
+ 'dec(3)' => { data_type => 'decimal', size => [3,0] },
+ 'numeric(3)' => { data_type => 'numeric', size => [3,0] },
+
+ 'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
+ 'dec(3,3)' => { data_type => 'decimal', size => [3,3] },
+ 'numeric(3,3)' => { data_type => 'numeric', size => [3,3] },
+
+ 'decimal(18,18)' => { data_type => 'decimal', size => [18,18] },
+ 'dec(18,18)' => { data_type => 'decimal', size => [18,18] },
+ 'numeric(18,18)' => { data_type => 'numeric', size => [18,18] },
+
+ # money types
+ 'money' => { data_type => 'money' },
+ 'smallmoney' => { data_type => 'smallmoney' },
+
+ # bit arrays
+ 'long varbit' => { data_type => 'long varbit' },
+ 'long bit varying'
+ => { data_type => 'long varbit' },
+ 'varbit' => { data_type => 'varbit', size => 1 },
+ 'varbit(20)' => { data_type => 'varbit', size => 20 },
+ 'bit varying' => { data_type => 'varbit', size => 1 },
+ 'bit varying(20)'
+ => { data_type => 'varbit', size => 20 },
+
+ # Date and Time Types
+ 'date' => { data_type => 'date' },
+ 'datetime' => { data_type => 'datetime' },
+ 'smalldatetime'
+ => { data_type => 'smalldatetime' },
+ 'timestamp' => { data_type => 'timestamp' },
+ 'timestamp DEFAULT current timestamp'
+ => { data_type => 'timestamp', default_value => \"current timestamp" },
+ 'time' => { data_type => 'time' },
+
+ # String 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' => { data_type => 'varchar', size => 1 },
+ 'varchar(20)' => { data_type => 'varchar', size => 20 },
+ 'char varying(20)'
+ => { data_type => 'varchar', size => 20 },
+ 'character varying(20)'
+ => { data_type => 'varchar', size => 20 },
+ 'nvarchar(20)' => { data_type => 'nvarchar', size => 20 },
+ 'xml' => { data_type => 'xml' },
+ 'uniqueidentifierstr'
+ => { data_type => 'uniqueidentifierstr' },
+
+ # Binary types
+ 'binary' => { data_type => 'binary', size => 1 },
+ 'binary(20)' => { data_type => 'binary', size => 20 },
+ 'varbinary' => { data_type => 'varbinary', size => 1 },
+ 'varbinary(20)'=> { data_type => 'varbinary', size => 20 },
+ 'uniqueidentifier'
+ => { data_type => 'uniqueidentifier' },
+
+ # Blob types
+ 'long binary' => { data_type => 'long binary' },
+ 'image' => { data_type => 'image' },
+ 'long varchar' => { data_type => 'long varchar' },
+ 'text' => { data_type => 'text' },
+ 'long nvarchar'=> { data_type => 'long nvarchar' },
+ 'ntext' => { data_type => 'ntext' },
+ },
+
);
if (not ($dbd_sqlanywhere_dsn || $odbc_dsn)) {
projects / dbsrgits/DBIx-Class-Schema-Loader.git / commitdiff