db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Greg Monroe" <Greg.Mon...@DukeCE.com>
Subject Question about the handling of default="" in SQL generation
Date Tue, 18 Dec 2007 18:43:06 GMT
I just came across what I think is a long standing bug. However before I

fix it, I want to make sure that it's not done that way to keep from 
breaking some DB syntax restrictions.  The bug is this:
 
If the schema XML defines a "String" column with a default attribute set

to "", the SQL generated for a lot of DB's will just ignore this
default.  
For example, if you have a column defined as:
 
<column name="x" required="true" type="VARCHAR" size="254" default=""/>
 
The MySQL SQL generated for this column in the CREATE TABLE statement 
will be:
 
x VARCHAR(254) NOT NULL, 
 
The default value of "" gets ignored.  This doesn't effect records
stored via
Torque objects, because they set the default value and update all field.
However, 
if you do some non-Torque inserts, these fail if you don't specify all
the fields.
 
The problem is in the Column.getSqlString() code.  This ignores both
null and
empty string default values.  The fix would skip null and empty string
values for 
nonTextType fields but add DEFAULT '' to TextType fields if the default
is "".  
E.g. type="INTEGER" default="" would NOT have DEFAULT '' added but 
type="LONGVARCHAR" would.
 
However, this change would effect the SQL generated for a majority of
the
DB types (only a handfull, like MS SQL don't use this method.. but then 
again... MS SQL generates the correct SQL :) ).
 
As far as I can tell, the SQL standard says all columns in CREATE TABLE
can
have a DEFAULT [exp] option where [exp] needs to result in the correct
data 
type. So it should be within the standards.
 
Anyone know a reason NOT to do this?  E.g., a DB type that doesn't allow

'' as default values?
 
Greg Monroe <Monroe@DukeCE.com> (919)680-5050
C&IS Solutions Team Lead
Duke Corporate Education, Inc.
330 Blackwell St.
Durham, NC 27701


 


DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message