Metadata issue, default column value

I've created a table (test) in EM with the following column descriptors:

col1 ShortString 10, TnxEmptyDefaultValueDescriptor
col2 NullString 10, TnxEmptyDefaultValueDescriptor
col3 GUID, TnxAutoGuidDefaultDescriptor
col4 DateTime, TnxCurrentDateTimeDefaultValueDescriptor
col5 NullString 20, TnxCurrentUserDefaultValueDescriptor

Checking the column descriptors in the #FIELDS system table, the
FIELD_DEFAULTVALUE column shows the following values:

<Empty>
<Empty>
NEWGUID
CURRENT_DATETIME
CURRENT_USER

Only NEWGUID and CURRENT_USER are valid values. <Empty> and CURRENT_DATETIME
are invalid default options.

Opening the table in the table editor in Database Workbench, the DDL looks
like:

CREATE TABLE test
(
col1 ShortString(10) DEFAULT <Empty>,
col2 VarChar(10) DEFAULT '<Empty>',
col3 GUID DEFAULT NEWGUID,
col4 Timestamp DEFAULT 'CURRENT_DATETIME',
col5 VarChar(20) DEFAULT CURRENT_USER
);

This statement won't execute, of course, because of the invalid default
specifications, but what's rather weird is that <Empty> is parsed as a
character string literal in the col2 (VARCHAR) column and as a keyword in
the col1 (SHORTSTRING) column.

Anyhow, the main issue is the bug in the creation of the system table.

Ole

> Only NEWGUID and CURRENT_USER are valid values. <Empty> and
> CURRENT_DATETIME are invalid default options.

Issue #2284

> This statement won't execute, of course, because of the invalid
> default specifications, but what's rather weird is that <Empty> is
> parsed as a character string literal in the col2 (VARCHAR) column and
> as a keyword in the col1 (SHORTSTRING) column.

That has to be a DBW thing.

--

Eivind Bakkestuen
Nexus Database Systems Pty Ltd

Eivind,

> Issue #2284

Additional issue:

If a TnxConstDefaultValueDescriptor is defined on a character string column,
the default value shown in #FIELDS.FIELD_DEFAULTVALUE shall be quoted.
Currently it shows up without the quotes.

The point is that the representation of default values in the relevant
system tables/views (the proprietary #FIELDS table in V2, the standard
INFORMATION_SCHEMA.COLUMNS in V3), shall be exactly how it appears in SQL
text.

For example, the default character string value 'Empty' shall appear as
'Empty' in the system table, not as Empty.

Ole

> > Only NEWGUID and CURRENT_USER are valid values. <Empty> and
> > CURRENT_DATETIME are invalid default options.
>
> Issue #2284
>
> > This statement won't execute, of course, because of the invalid
> > default specifications, but what's rather weird is that <Empty> is
> > parsed as a character string literal in the col2 (VARCHAR) column and
> > as a keyword in the col1 (SHORTSTRING) column.
>
> That has to be a DBW thing.

http://tracker.upscene.com/view.php?id=765


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Martijn,

>> That has to be a DBW thing.
>
> http://tracker.upscene.com/view.php?id=765

Actually, there should be no requirement for DBW to add quotes to default
values. DBW should simply grab the default specification from the
appropriate system table. This requires that NDS fixes the default value
metadata issues though.

Ole

> >> That has to be a DBW thing.
> >
> > http://tracker.upscene.com/view.php?id=765
>
> Actually, there should be no requirement for DBW to add quotes to default
> values. DBW should simply grab the default specification from the
> appropriate system table. This requires that NDS fixes the default value
> metadata issues though.

I'm doing all sort of funky stuff there, also to recognize empty strings and
so on.

I hope this won't be changed for v2.x...

DBW is fine now.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Martijn,

<< I'm doing all sort of funky stuff there, also to recognize empty strings
and so on. >>

Wouldn't it be much easier to just grab the default specification from the
metadata table ? This is how SQL clients are supposed to access it.

How would you determine whether the value Empty in
#FIELDS.FIELD_DEFAULTVALUE represents a constant (character string literal
'Empty') or the keyword EMPTY ?

Ole

Martijn,

<< How would you determine whether the value Empty in
#FIELDS.FIELD_DEFAULTVALUE represents a constant (character string literal
'Empty') or the keyword EMPTY ? >>

Currently, if I create a table in DBW by executing the following DDL
statement:

create table test (col1 varchar(10) default empty);

DBW reverse-engineers the table definition to:

CREATE TABLE test
(
col1 VarChar(10) DEFAULT '<Empty>'
);

Ole

> DBW reverse-engineers the table definition to:
>
> CREATE TABLE test
> (
> col1 VarChar(10) DEFAULT '<Empty>'
> );

Or the following:

create table test2 (col1 varchar(10) default 'User');

is reverse-engineered to:

CREATE TABLE test2
(
col1 VarChar(10) DEFAULT User
);

Ole

> > DBW reverse-engineers the table definition to:
> >
> > CREATE TABLE test
> > (
> > col1 VarChar(10) DEFAULT '<Empty>'
> > );

Apparantly, EMPTY creates a string of <Empty>,
which is a valid default value (for a string).

> Or the following:
>
> create table test2 (col1 varchar(10) default 'User');
>
> is reverse-engineered to:
>
> CREATE TABLE test2
> (
> col1 VarChar(10) DEFAULT User
> );

That's because User is a valid function and #fields
returns it as:
User

No quotes. So there's no knowing if it's a function or a literal.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com