| Subject: | Data types not picked up for Sybase ASE 15.5 |
A couple months ago, we migrated our server from 12.1 to 15.5, after
switching
our ORM from something hand-rolled to DBIx::Class. Our workflow for schema
updates starts with making changes to the development environment, running
DBIC::S::L on the database, and committing the ensuing changes
(including some
post-checksum massaging) to our repository. So far, this has kept our
ORM in
lock-step across all environments, and even allows us to use SQLite as a
testing database.
I noticed that after the upgrade, the DBIC::S::L component has been dropping
datatypes from our Result classes. I cracked open DBIC:S:L:DBI:Sybase and
found the following query is being used to retrieve column information, and
created a test table:
---
create table foo (
id int identity,
bar varchar(20),
baz datetime default getdate(),
constraint PK_foo
primary key clustered (id)
)
---
I then interpolated the requisite values from the query used in
_columns_info_for to see what I would get:
---
SELECT c.name name, bt.name base_type, ut.name user_type, cm.text deflt,
c.prec prec, c.scale scale, c.length len
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
LEFT JOIN systypes bt ON c.type = bt.type
LEFT JOIN systypes ut ON c.usertype = ut.usertype
LEFT JOIN syscomments cm
ON cm.id = CASE WHEN c.cdefault = 0 THEN c.computedcol ELSE
c.cdefault END
WHERE o.name = 'foo'
AND o.uid = 1
AND o.type IN ('U', 'V')
---
name,name,name,text,prec,scale,length
baz,datetime,datetime,DEFAULT getdate(),,,8
---
(NOTE: no renaming of columns here)
This is troublesome! It appears that this query is only returning
information
for columns that have a default value. Not all of our columns have a
default
value, nor should it be expected. This means that they don't get an
entry in
the syscolumns table, so this really mucks up our ability to generate a
schema.
As an experiment, I removed the join with syscomments to see what would get
returned:
---
SELECT c.name name, bt.name base_type, ut.name user_type, c.prec prec,
c.scale scale, c.length len
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
LEFT JOIN systypes bt ON c.type = bt.type
LEFT JOIN systypes ut ON c.usertype = ut.usertype
WHERE o.name = 'foo'
AND o.uid = 1
AND o.type IN ('U', 'V')
---
name,name,name,prec,scale,length
bar,sysname,varchar,,,20
bar,varchar,varchar,,,20
bar,nvarchar,varchar,,,20
bar,longsysname,varchar,,,20
id,int,int,,,4
baz,datetime,datetime,,,8
---
This is better, sort of. It shows that the type information is
available for
all columns, but you don't get the default info, since it is stored in
syscomments.
I have a few questions regarding the operation here:
* Why is this query dropping the unmatching results on the left hand side of
the query? Isn't the point of a left join that the left information
is kept
even when the right-hand information is missing?
* Has anyone tested/confirmed DBIC:S:L on Sybase ASE 15.5? Is there any
further
testing I can do?
* Are there any database configuration options regarding column comments
that
users should be aware of?
* Would a two-pass approach (data-type, then default values) to
information be
preferable?