4Internal Specification

flameluxuriant_5cf1feb2
-
a96b
-
4bff
-
8bb8
-
0875e49fcc72.doc


Detailed Design Specification

© 2006, 2009 Ingres Corporation

Project Name

SQL BOOLEAN typ
e

Author

Joe Abbat
e

Last Saved Date

Sept 22, 2009



Revision

0.2

Template
Revision

1.7









flameluxuriant_5cf1feb2
-
a96b
-
4bff
-
8bb8
-
0875e49fcc72.doc






Page

2

of
30


Responsibility List




Note:
The Re
sponsibility List reflects those required to review and provide feedback
for the document. Signoff by those listed is required prior to the beginning of the
development phase.


Additional reviewers




Note:
The additional reviewers list reflects other
people that should be copied on the
document and invited to review meetings, but feedback from them is not required for
the document to be approved. Managers of other engineering groups are copied for
comment on how it affects their product or product area
. The manager for your own
engineering area is included in first table and can be removed from here.

Assigned To

Action

Responsibility

Signature


Owner

Engineer/Architect



Peer Review

Development Manager


Chris Rogers

Peer Review

QA Manager



Test Review

QA Engineer


David Reed

Peer Review

Sustaini
ng Engineering Manager



Sustainability Review

Sustaining Engineering Engineer


Pam Fowler

Peer Review

Level 1 Support Manager


Bill Maimone

Peer Review

Chief Architect


Elaine Grieco

Peer Review

Technical Writer


Christine Normile

Peer Review

Product

Manager



Peer Review

Services



Peer Review

Services




Assigned To

Action

Responsibility

Signature

Steve Ball

Information

DBMS


Teresa King

Information

Gateways


Teresa King

Information

Connectivity


Joe Kronk

Information

OpenROAD


Roger Whitco
mb

Information

Tools


Emma McGrattan

Information

Usability




flameluxuriant_5cf1feb2
-
a96b
-
4bff
-
8bb8
-
0875e49fcc72.doc






Page

3

of
30





SQL Language Review




Note:
The SQL language review table lists people that should be sent an initial draft
copy of this document if you answered “yes
” to any questions in section 2.2. If you did
not answer yes to any of those questions you may delete this table.


Assigned To

Action

Responsibility

Signature

Steve Ball

Language Review

DBMS


Teresa King

Language Review

Gateways


Teresa King

Language Review

Connectivity


Joe Kronk

Language Review

OpenROAD


David Reed

Language Review

Supportability and
Backward
Compatability




flameluxuriant_5cf1feb2
-
a96b
-
4bff
-
8bb8
-
0875e49fcc72.doc






Page

4

of
30


Change History:


Revision
Date

Last
Revision
By

Description of

Change

Sept. 10,
2009

Joe Abbate

Initial version

Sept. 22,
2009

Joe Abbate

R
evised based on DDS review feedback. Expanded
“Noteworthy Issues” section with background
information and more detail on specific issues.


flameluxuriant_5cf1feb2
-
a96b
-
4bff
-
8bb8
-
0875e49fcc72.doc






Page

5

of
30


TABLE OF CONTENTS
1 INTRODUCTION

................................
................................
................................
.......................

8

1.1

S
COPE AND SUMMARY

................................
................................
................................
...............

8

1.2

DEFINITIONS,

ACR
ONYMS

AND

ABBREVIATIONS

................................
........................

8

1.3

REFERENCES

................................
................................
................................
..........................

8

1.4

N
OTEWORTHY
I
SSUES

................................
................................
................................
................

8

1.4.1 Background

................................
................................
................................
............................

8

1.4.2 Boolean Input

................................
................................
................................
.........................

9

1.4.3

Boolean Output

................................
................................
................................
.....................

9

1.4.4 Boolean and Numeric Values

................................
................................
...............................

10

1.4.5 UNKNOWN as a Synonym for NULL

................................
................................
..................

10

1.4.6 Character Front
-
ends

................................
................................
................................
...........

10

2 ARCHITECTURE O
VERVIEW

................................
................................
.............................

11

2.1

HIGH

LEVEL

DESCRIPTION

................................
................................
...............................

11

2.2

SQL

L
ANGUAGE CHANGES

................................
................................
................................
......

11

2.2.1 New or Changed Syntax

................................
................................
................................
........

11

2.2.2 Non
-
syntax Related Changes to DBMS

................................
................................
.................

13

2.2.3 Language changes to Ingres/Star

................................
................................
..........................

13

2.2.4 Language Changes to ABF

................................
................................
................................
...

13

2.2.5 Language Changes to Database Procedures

................................
................................
........

14

2.2.6 Language Change
s to Embedded SQL pre
-
compilers

................................
..........................

14

2.2.7 Effects on dynamic SQL

................................
................................
................................
........

14

2.3

IMPLICATIONS FOR
GCA

................................
................................
................................
..........

15

2.4

C
ONNECTION PARAMETERS

................................
................................
................................
.....

15

2.5

LANGUAGE
,

UNICOD
E AND INTERNATIONALI
ZATION
I
SSUES

................................
..................

15

2.6

IMPLICATIONS FOR ING
RES
/
STAR

................................
................................
.............................

15

2.7

IMPLICATIONS FOR DBA

TOOLS

................................
................................
................................

15

2.8

NEW IMA
/
MIB OBJECTS

................................
................................
................................
.............

15

2.9

NEW T
RACE POINTS

................................
................................
................................
..................

15

2.10

C
ATALOG ALTERATIONS

................................
................................
................................
........

15

2.11

I
MPLICATION FOR
G
ATEWAYS

................................
................................
...............................

15

2.12

IMPLICATIONS FOR DAT
ABASE DRIVERS

................................
................................
................

15

2.13

IMPLICATIONS FOR OPE
N
ROAD

................................
................................
...............................

16

2.14

D
ESIGN
L
IMITATIONS AND
A
SSUMPTIONS

................................
................................
.............

16

2.14.1 Dependencies

................................
................................
................................
......................

16

2.15

P
LATFORM
S
PECIFIC
I
SSUES

................................
................................
................................
...

16

2.16

P
RODUCT INTERACTION

................................
................................
................................
.........

16

2.17

P
ATENT
I
NFORMATION

................................
................................
................................
...........

16

3 EXTERNAL SPECIFICA
TION

................................
................................
..............................

17

3.1

U
SER PERSPECTIVE

................................
................................
................................
...................

17

3.2

INSTALLATION AND
A
DMINISTRATION
P
ERSPECTIVE

................................
..............................

18

3.3

M
IGRATION
I
SSUES

................................
................................
................................
..................

18



3.4

S
ECURITY
I
MPACT

................................
................................
................................
....................

18

4 INTERNAL SPECIFICA
TION

................................
................................
...............................

19

4.1

E
STIMATED TASKS AND
E
FFORT

................................
................................
..............................

19

4.2

P
ROGRAMMING

................................
................................
................................
........................

19

4.2.1 iicommon.h: Types and names used by DBMS and front
-
ends

................................
...........

19

4.2.2 ADF: Abstract Datatype Facility headers

................................
................................
............

20

4.2.3 ADC: Common Datatype Functions

................................
................................
.....................

20

4.2.4 ADE: Compiled Expression Functions

................................
................................
................

20

4.2.5 ADG: Server and Session Related Datatype Functions

................................
.......................

21

4.2.6 ADU: Abstract Datatype Utility Functions

................................
................................
..........

21

4.2.7 DUC: Dat
abase Creation Utility Functions

................................
................................
.........

21

4.2.8 DUV: Database Upgrade Utility Functions

................................
................................
..........

21

4.2.9 OPC: Optimizer Query Compilation Functions

................................
................................
....

21

4.2.10 PSL: Parser Language Functi
ons

................................
................................
......................

21

4.2.11 Embedded Language modules

................................
................................
.............................

22

4.3

C
OMPATIBILITY LIBRARY

INTERFACE CHANGES

................................
................................
......

22

4.4

I
NTERFACE
................................
................................
................................
...............................

22

4.5

BUILD IMPLICATIONS

................................
................................
................................
................

22

4.6

PICCOLO CHANGE NUMBE
RS

................................
................................
................................
.....

22

5 IMPACT AND DOCUMEN
TATION SUMMARY

................................
...............................

23

5.1

P
RODUCT
/C
OMPONENT
I
MPACTS

................................
................................
.............................

23

5.1.1 Entities
................................
................................
................................
................................
...

23

5.2

D
O
CUMENTATION

................................
................................
................................
....................

23

6 QUALITY ISSUES

................................
................................
................................
....................

25

6.1

U
NIT
T
ESTING
S
UMMARY

................................
................................
................................
........

25

6.1.1 Unit Testing Description

................................
................................
................................
.......

25

6.2

HANDOFFQA IMPACT

................................
................................
................................
................

27

6.3

T
ESTING
R
ECOMMENDATIONS

................................
................................
................................
.

27

6.4

R
EGRESSION
R
ISK
A
SSESSMENT

................................
................................
..............................

28

6.4.1 Backward Compatibility Issues

................................
................................
.............................

28

7 PACKAGING AND INST
ALLATION IMPACT

................................
................................
..

29

8 SUPPORT IMPACT

................................
................................
................................
.................

30

8.1

EXAMPLES AND TESTS

................................
................................
................................
..............

30


PREFACE

This document describes external functional specifications as well as design
specifications for one feature of a release project. There will be many Deta
iled
Design Specifications (DDS) for each project, one for each major feature
described in the Software Requirements Specification (SRS) or project wiki page.
The SRS or the wiki page is the master document for the entire project.

This is intended to be a
living document.
The product development cycle is a
dynamic process in which our understanding of the project and its criteria for
success are refined over time. It is therefore expected that the completed
Detailed Design Specification will undergo many re
visions during the course of a
project as requirements; resources and constraints evolve. The engineer would
not be expected to complete all sections in the initial draft; some sections are
designed so that they can only be completed one the project is cod
ed.
*note that
you are expected to continue updating this document until the release
project is handed over to SE*

The Development Manager is responsible for the contents of this document.
Deliverables that must be completed prior to releasing this docume
nt are at least
one of:


Software Requirements Specification


Wiki page on the engineering web describing the components of the project


All template instructions can be identified by their
gray italic type
. This information
may be removed after completing t
he necessary project information.

Ant information detailed in this document should not be repeated in the wiki page
for this feature unless there is a compelling reason to do otherwise one of the
copies of the information may become out
-
of
-
date. If you nee
d to, refer to the DDS
on the wiki page.



1
INTRODUCTION

1.1
SCOPE AND SUMMARY

The objective is to implement the SQL predefined (or “built
-
in”) data type named
BOOLEAN as specified in the SQL:2003 Standar
d.

This will be particularly
useful for customers and part
ners migrating from MySQL and PostgreSQL.

1.2
DEFINITIONS, ACRONYM
S AND ABBREVIATIONS

Data Type
: A set of representable values. Every representable value belongs to
at least one data type and may belong to several data types [ISO9075
-
2].

Boolean
: The data type boolean comprises the distinct truth values
True

and
False
. Unless prohibited by a NOT NULL constraint, the boolean data type also
supports the truth value
Unknown

as the null value [ISO9075
-
2].

1.3

REFERENCES

[ISO9075
-
1] ISO/IEC 9075
-
1:2003

(E)

Information technology
—

Database
languages
—

SQL
—

Part 1: Framework (SQL/Framework
), 2003
-
07
-
25,
ISO/IEC JTC 1/SC 32/WG 3.

[ISO9075
-
2] ISO/IEC 9075
-
2:2003 (E)

Information technology
—

Database
languages
—

SQL
—

Part 2: Foundation (SQL/Foundation
), 2
003
-
07
-
25,
ISO/IEC JTC 1/SC 32/WG 3.

[ISO9075
-
3] ISO/IEC 9075
-
3:2003 (E)

Information technology
—

Database
languages
—

SQL
—

Part 3: Call
-
Level Interface (SQL/CLI
), 2003
-
07
-
25,
ISO/IEC JTC 1/SC 32/WG 3.

[MYSQL51] MySQL 5.1 Reference Manual, MySQL AB, Sun M
icrosystems, Inc,
http://dev.mysql.com/doc/refman/5.1/en/

[POSTG84] PostgreSQL 8.4.1 Documentation, The PostgreSQL Global Development
Group,
http://www.postgresql.org/docs/8.4/static/
.


1.4
NOTEWORTHY ISSUES

1.4.1

Background

The enhancement request asking for an implementation of BOOLEAN came from
a French company that developed a messaging and collaboration application. At
first, it used a MySQL data
base to store profile data and it was later ported to
PostgreSQL. Therefore, it is reasonable to look at the MySQL and PostgreSQL
implementations of BOOLEAN for reference.



MySQL implements BOOLEAN (and BOOL) as a synonym for TINYINT(1) with
non
-
zero values

considered to be true. It also aliases FALSE and TRUE to 0 and
1, respectively, and supports IS UNKNOWN as an equivalent to IS NULL for
boolean values, but does not allow UNKNOWN as the source value for an
assignment. For input, in addition to FALSE and

TRUE, any integer value is
accepted (and truncated if need be, with a warning). For output, 0 and 1
represent the normal truth values, but any TINYINT value (
-
128 to 127) can be
shown. CAST(FALSE AS CHAR(5)) and CAST(TRUE AS CHAR(4)) return '0'
and '1',

respectively, but CAST(number AS BOOLEAN) and CAST('string' AS
BOOLEAN) are not accepted. The MySQL 5.1 Reference states they “ intend to
implement full boolean type handling, in accordance with standard SQL, in a
future MySQL release” [MYSQL51].

PostgreS
QL implements “the standard SQL type
boolean
” [POSTG84]. Like
MySQL, it accepts IS UNKNOWN as equivalent to IS NULL, but does not
support UNKNOWN as the source in an assignment. In addition to FALSE and
TRUE for input, it accepts 'f', 'false', 'n', 'no',
'off', '0', and 't', 'true', 'y', 'yes', 'on',
'1', respectively, but the former are preferred [testing shows that 'off' and 'on' are
not accepted]. For output, it displays unquoted 'f' and 't' for FALSE and TRUE,
respectively. CAST(FALSE AS CHAR(5)) and C
AST(TRUE AS CHAR(4)) return
'false' and 'true', respectively. CAST(number AS BOOLEAN) returns
f

for 0 and
t

for non
-
zero integer values (other numeric types are not allowed). CAST('string'
AS BOOLEAN) allows substrings of 'false', 'true', 'no', 'yes', '0'
and '1' to return
the expected truth values, while other strings are disallowed.

1.4.2

Boolean Input

The SQL Standard specifies FALSE and TRUE as the only valid literals for a
non
-
nullable BOOLEAN value. Based on the CAST specification (see below), it is
reason
able to accept the strings 'FALSE' and 'TRUE', case
-
insensitively, to
automatically coerce to BOOLEAN. The Standard indicates that casting
BOOLEAN to or numeric types is invalid, so it is less desirable to automatically
coerce numeric input to BOOLEAN.

1.4.3

Bo
olean Output

The SQL Standard does not appear to specify an output format for a BOOLEAN
value, except indirectly. In [ISO9075
-
2], section 6.12 <cast specification>,
General Rules, when discussing CAST (<operand> AS <target>), it states that
the result fo
r a
True

operand is the literal 'TRUE' and for a
False

operand the
literal 'FALSE', each possibly padded on the right for fixed
-
length character
strings.

On the other hand, in section 20.4 <embedded SQL C program, [ISO9075
-
2]
states “'long' describes an ex
act numeric variable. The equivalent SQL data type
is INTEGER or BOOLEAN.” In [ISO9075
-
3], section 5.15 SQL/CLI data type


correspondences, table 45 indicates BOOLEAN corresponds to the C 'short' data
type, and table 46 shows BOOLEAN corresponds to COBOL 'P
ICTURE X'.

In view of the above and the varying implementations, for SQL Terminal Monitor
we will output the unquoted strings FALSE and TRUE for BOOLEAN values
(which is also symmetrical with allowing those same strings for input).

1.4.4

Boolean and Numeric Val
ues

As mentioned above, [ISO9075
-
2] clearly states that casting from BOOLEAN to
numeric is not allowed. However, the use of TINYINT(1) as synonym for
BOOLEAN by MySQL creates an unfortunate precedent. We recommend no
automatic coercion of numeric input to
BOOLEAN, but we will support
CAST(integer AS BOOLEAN), provided the integer has the values 0 or 1.

For embedded SQL and SQL/CLI (used by ODBC), it is proposed that
BOOLEAN be converted to a C char or short type, or a native boolean type for
other languages
.

1.4.5

UNKNOWN as a Synonym for NULL

The SQL Standard introduced the UNKNOWN reserved word as a synomy for
NULL in the context of a BOOLEAN value. It is arguably unnecessary and
redundant, but it is part of the Standard. As described above, MySQL and
PostgreSQ
L have only implemented the “IS [NOT] UNKNOWN” clause and not
the syntax that permits to INSERT or UPDATE/SET a value using the keyword.
We have not yet prototyped UNKNOWN as a keyword, but we will implement it in
full.

1.4.6

Character Front
-
ends

We are recomme
nding that BOOLEAN support not be added to the character
-
based front
-
ends. We believe it would require much more effort than currently
estimated for the DBMS and the embedded SQL precompilers, but resulting in
little or no benefit to existing or potential
customers. BOOLEAN support is more
likely to be used and appreciated with modern languages and interfaces. Ingres
Corp. Product Management will decide whether support for BOOLEAN will be
extended to character
-
based front
-
ends.



2
ARCHITECTURE OVERVIE
W

2.1
HIGH LE
VEL DESCRIPTION

The data type
BOOLEAN will be accepted as a valid type when defining a
column in a table or a variable In a database procedure. The SQL literals FALSE
and TRUE will be accepted in expressions including on the right
-
hand side of an
assignmen
t. The SQL literal UNKNOWN will also be accepted, as a synonym for
NULL, provided the target value or expression is of type BOOLEAN. It will be
possible to test BOOLEAN values or expressions against these literals, including
the implied variant, i.e., WH
ERE <boolean_value> will imply WHERE
<boolean_value> IS TRUE. Use of NOT <boolean_value>, to invert the result,
will also be allowed.

For input, the literals FALSE, TRUE, 'FALSE' and 'TRUE' and UNKNOWN (or
NULL) will be accepted, without case sensitivity.
For Terminal Monitor output, the
unquoted literals 'FALSE', 'TRUE' and a blank string will be used to denote the
FALSE, TRUE and UNKNOWN values, respectively. CAST(boolean AS
character
-
type) will be allowed. CAST(character
-
type AS BOOLEAN) will be
accepte
d if the character
-
type is the string 'FALSE' or 'TRUE', without regard to
case. CAST(integer
-
type AS BOOLEAN) will be accepted without error if the
integer
-
type has the values 0 or 1. Other CAST or automatic coercion variants
will be disallowed.

This req
uires changes to the Abstract Datatype Facility, the Parser, the Optimizer
and the Database Utilities. The existing internal BOOLEAN type
(DB_BOO_TYPE) will be extended to become a “first class” external type.
Internally, the BOOLEAN type will be stored
and manipulated as a single
-
byte
integer which can only take the values 0 and 1.

2.2
SQL LANGUAGE CHANGES

2.2.1
New or Changed Syntax

The following list is not intended to be all
-
inclusive.

ALTER TABLE
table_name

ADD [COLUMN]
column_name format
[
default_clause
]
…
an
d
ALTER TABLE
table_name

ALTER
[COLUMN]
column_name format
[
default_clause
]
…
will allow
BOOLEAN as an option for
format

and will allow FALSE, TRUE or
UNKNOWN as part of the
default_clause

(provided the column is
BOOLEAN).

ALTER TABLE
table_name

ADD CONSTR
AINT
constraint_name
constraint_clause
a
nd
ALTER TABLE
table_name

ALTER
[COLUMN]
column_name …
[
column_constraint
]

…
will allow


FALSE, TRUE or UNKNOWN as part of the
constraint_clause

and
column_constraint

clauses
.

COPY [TABLE] [
schema
.]
table_name

(
column
_name = format …
will allow BOOLEAN as an option for
format
.

CREATE INTEGRITY ON
table_name

[
corr_name]
IS
search_condition
will allow expresssions using FALSE, TRUE or
UNKNOWN as part of the
search_condition
.

CREATE RULE [
schema
.]
rule_name

BEFORE|AFTER
st
atement_type …

WHERE
qualification
will accept expresssions
using FALSE, TRUE or UNKNOWN as part of the
qualification
.

CREATE TABLE [
schema.]table_name (
column_name datatype
[[WITH] DEFAULT
default_spec
]
…
[CONSTRAINT
constraint_name
]
column_constraint

…
w
ill allow BOOLEAN as an
option for
datatype
, will accept FALSE, TRUE or UNKNOWN as the
default_spec

(provided the column is BOOLEAN), and will allow
FALSE, TRUE or UNKNOWN as part of the
column_constraint
.

CREATE TABLE table_name (…) AS
subselect …
will ha
ndle
creation of a table with a BOOLEAN column when the corresponding
column in the
subselect

is of type BOOLEAN or is a BOOLEAN
expression.

DECLARE GLOBAL TEMPORARY TABLE [SESSION.]
table_name

(
column_name format …

will allow BOOLEAN as an option for
form
at
.

INSERT INTO [
schema.
]
table_name …
VALUES (
expr …

will
accept FALSE, TRUE or UNKNOWN as the
expr
.

REGISTER TABLE [
schema
.]
table_name

(
column_name col_type
…

will allow BOOLEAN as an option for
col_type
.

SELECT …
expression

…

will allow FALSE, TRUE or U
NKNOWN as the
expression

or part thereof.
JOIN
source

USING (
column
{,
column
})

will accept BOOLEAN
column
s.

UPDATE [
schema.
]
table_name
[
corr_name
] … SET
column =
expression …

will allow FALSE, TRUE or UNKNOWN as part of the
expression
.

The
WHERE
search_co
ndition

clauses of the
DELETE
,
SELECT
and
UPDATE

statements, as well as the
JOIN

source

ON
search_condition
, will accept tests using
IS [NOT] TRUE
,
IS [NOT]
FALSE

and
IS [NOT] UNKNOWN

when applied to BOOLEAN columns
or expressions of type BOOLEAN. The tes
t
WHERE
boolean_column

will be equated to
WHERE
boolean_column
IS TRUE
. The test


WHERE
boolean_column
IS UNKNOWN

will be equated to
WHERE
boolean_column
IS NULL
.

CASE
expr
WHEN
cond1
THEN

expr2
... and
CASE WHEN
search_condition1
THEN

expr1
... will accep
t FALSE, TRUE or UNKNOWN in
condN

or
search_conditionN

or part thereof and
exprN

may include BOOLEAN columns
or literals.

CAST(
expr

AS
datatype
)

will accept BOOLEAN as the
datatype

and FALSE,
TRUE or UNKNOWN as the
expr
. According to [ISO9075
-
2], a BOOLEA
N
expression can be cast to a fixed or variable length CHARACTER data type or
a character large object, but may not be cast to numeric type. Conversely, a
character expression can be cast to BOOLEAN, but casting a numeric
expression to BOOLEAN is not vali
d. However,
CAST(
expr

AS BOOLEAN)

will be accepted if
expr

is a 0 or 1. For casting to strings, note also that the
datatype

needs to be of sufficient length, e.g., CHAR(5) for FALSE, or silent
truncation will occur (unless the
-
string_truncation=fail

is u
sed at
connect time). The Ingres shortcut, CHAR(
expr
) will return a single character,
i.e., 'F' or 'T', since it is interpreted as CAST(
expr

AS CHAR(1)).
CAST(UNKNOWN AS
datatype
) will return the NULL value.

2.2.2
Non
-
syntax Related Changes to DBMS

The
CREATE
INDEX

statement will allow indexes to be created on BOOLEAN
columns.

2.2.3
Language changes to Ingres/Star

The syntax changes shown in sections 2.2.1 should be implemented in Star. In
addition,
REGISTER [TABLE]

table_name

AS LINK

should accept local
tables wit
h columns of type BOOLEAN.

2.2.4
Language Changes to ABF

My recommendation, from a cost
-
benefit perspective, would be not to implement
any of these changes in ABF unless there is solid evidence that many customers
or partners want these features in ABF. Note tha
t although adding a BOOLEAN
variable type to the ABF 4GL language would not be that difficult, such support
would require changes to virtually all the character
-
based applications since
Vifred, QBF and Report Writer would need to be modified to display a
B
OOLEAN, support one or more ways of inputting a BOOLEAN (e.g., a textual,
numeric or stylistic [checkbox] representation of TRUE or FALSE), etc.



2.2.5
Language Changes to Database Procedures

[
CREATE] PROCEDURE [
schema
.]
proc_name

( [
param_mode
]
param_name
[=]
par
am_type …
) [RESULT ROW (
result_type ...
)

will allow BOOLEAN as options for
param_type

and
result_type
.

The declare section, i.e.,
DECLARE
var_name
[=]
var_type …

will
accept BOOLEAN as an option for
var_type
.

The statements section, will allow assignment o
f TRUE or FALSE values
to BOOLEAN variables or parameters and logical tests of such items as
in
IF
boolean_var
IS NOT TRUE

(or simply
IF NOT
boolean_var
).

2.2.6
Language Changes to Embedded SQL pre
-
compilers

The SQL syntax changes shown in section 2.2.1 will be
supported as needed in
the pre
-
compilers. As per [ISO9075
-
2], for host languages such as C and
COBOL, which may not support intrinsic boolean types, a SQL value of TRUE
will returned as an integer value of 1 and FALSE as a value of 0 (Note: C99
does supp
ort a
bool

type and
true

and
false

constants through the inclusion of
the <stdbool.h> header, but we do not currently support C99). For languages
such as Ada, C++, Fortran or Pascal which do support boolean types, the SQL
values will be returned using the

intrinsic host language constants. The specific
correspondences for each language are as follows:

Ada:
Boolean

(native enumerated type with values
False

and
True
)

BASIC (VMS only):
BYTE
, with values 0 and 1 (but could be
WORD

or
LONG
)

C:
char
, with values

0 and 1 (but could be
short

or
int
)

C++:
bool

(native type with values
false

and
true
)

COBOL:
PICTURE 9

(with values 0 and 1)

Fortran:
LOGICAL

(with values
.FALSE.

and
.TRUE.
)

Pascal (VMS only):
Boolean

(native enumerated type with values
False

and
True
)

EXEC SQL DECLARE
[
schema.
]
table_name

TABLE (
column_name
datatype

…
will allow BOOLEAN as an option for
datatype
.
The
dclgen

utility will be modified accordingly.

2.2.7
Effects on dynamic SQL

The
EXEC SQL PREPARE

and
EXEC SQL DESCRIBE

statements will need to
ha
ndle any BOOLEAN columns as well as any expressions using TRUE or
FALSE.



2.3
IMPLICATIONS FOR GCA

Obviously a new data type is being introduced and GCA will have to support this.

2.4
CONNECTION PARAMETER
S

No changes are presently required.

2.5
LANGUAGE, UNICODE AN
D IN
TERNATIONALIZATION I
SSUES

No issues at this time.

2.6
IMPLICATIONS FOR ING
RES/STAR

No implications other than those noted in section 2.2.2.

2.7
IMPLICATIONS FOR DBA

TOOLS

Tools such as copydb and unloaddb will need to properly export and import
BOOLEAN type val
ues.

2.8
NEW IMA/MIB OBJECTS

No IMA objects should be needed.

2.9
NEW TRACE POINTS

No trace points will be needed.

2.10
CATALOG ALTERATIONS

Two new rows will be added to iidefault, containing the default values “FALSE”
and “TRUE”.

2.11
IMPLICATION FOR GATE
WAYS


The gatew
ays need not implement any of the SQL changes.

2.12
IMPLICATIONS FOR DAT
ABASE DRIVERS

It is reasonable to support these changes in the database drivers, which should
be able to (a) convert SQL BOOLEAN values to native boolean types if available,
and (b) accept
syntax that uses the keywords
BOOLEAN
,
TRUE

and
FALSE
,
and the clauses
IS TRUE
,
IS FALSE
, and
IS UNKNOWN

(and the alternative
IS
NOT

versions).



2.13
IMPLICATIONS FOR OPE
NROAD

It is also reasonable to implement these changes in OpenROAD, where a
BOOLEAN type can

be naturally represented as a graphical checkbox.

2.14
DESIGN LIMITATIONS A
ND ASSUMPTIONS

As mentioned in section 2.1, the basic assumption is that a BOOLEAN will be
represented in the database as a one
-
byte integer that only has the values 0 or
1. A SQL FALS
E or TRUE constant will be converted to the corresponding value
accordingly. The literals 'FALSE' and 'TRUE' will be similarly converted, without
regard to case. The CAST function will only support casting BOOLEAN to and
from character types and from the i
nteger values 0 and 1.
No other conversions
or coercions will be allowed.

2.14.1
Dependencies

Not applicable.

2.15
PLATFORM SPECIFIC IS
SUES

Not applicable.

2.16
PRODUCT INTERACTION

If desired, VDBA would need to be changed to support the new data type,
constants and synta
x.

2.17
PATENT INFORMATION


Not applicable.



3
EXTERNAL SPECIFICATI
ON

3.1
USER PERSPECTIVE

The BOOLEAN type and the TRUE and FALSE constants are mostly self
-
explanatory. The following is a simple demonstration of a user interaction:

* CREATE TABLE users (


name V
ARCHAR(15) PRIMARY KEY, active BOOLEAN DEFAULT TRUE);
\
g

* INSERT INTO users (name) VALUES ('Jack');

(1 row)

* SELECT * FROM users;

+
---------------
+
------
+

|name |active|

+
---------------
+
------
+

|Jack |TRUE |

+
---------------
+
------
+

(1 row)

* INSERT INTO users VALUES ('Jill', NULL);

(1 row)

* INSERT INTO users VALUES ('Peter', TRUE);

(1 row)

* SELECT active, count(*) AS total FROM users WHERE active IS NOT UNKNOWN


GROUP BY active;

+
------
+
-------------
+

|active|col2 |

+
--
----
+
-------------
+

|t | 2|

+
------
+
-------------
+

(1 row)

* UPDATE users SET active = FALSE WHERE name = 'Jill';

(1 row)

* SELECT name, active FROM users;

+
---------------
+
------
+

|name |active|

+
---------------
+
------
+

|Jack

|TRUE |

|Jill |FALSE |

|Peter |TRUE |

+
---------------
+
------
+

(3 rows)



3.2
INSTALLATION AND ADM
INISTRATION PERSPECT
IVE

No special installation or administrative actions or support are required.

3.3
MIGRATION ISSUES

This is a new data ty
pe so it does not have any migration implications. It can co
-
exist with earlier releases as long as the older release clients do not attempt to
access a table or database procedure that uses the new keywords.

3.4
SECURITY IMPACT

Not applicable.



4
INTERNAL SPECI
FICATION

4.1
ESTIMATED TASKS AND
EFFORT



Task

Effort
(person
-
days)

Assigned to

Done
(yes/no)

Tested
(yes/no)

ADC changes

5

Joe

80%


ADU changes

7

Joe

20%


Other ADF changes

5

Joe

20%


PSF changes

7

Joe

40%


OPF changes

5

Joe

2%


DUF changes

3

Joe

25%


ESQL changes

5

Joe

no


Copydb/unloaddb
changes

3

Joe

no


Python unit test
development

5

Joe

50%


TOTAL

45

-

-

-


4.2
PROGRAMMING

4.2.1
iicommon.h: Types and names used by DBMS and front
-
ends

Change DB_DT_ID_MACRO to provide SQL and QUEL precedence values for
BOOLEAN (recommended as 19 [highest of intrinsic types] by Ian Kirkham).
Change comments to indicate DB_BOO_TYPE can now be stored in relations.
Add db_booltype member to DB_ANYTYPE union. Add defined constants
DB_FALSE and DB_TRUE. Add #defines for DB
_DEF_ID_FALSE and
DB_DEF_ID_TRUE.



4.2.2
ADF: Abstract Datatype Facility headers

adffiids.h: Add function instance constants: BOO_TO_BOO, BOO_ISFALSE,
BOO_NOFALSE, BOO_ISTRUE, BOO_NOTRUE and LTXT_TO_BOO. Possibly
add BOO_ISUNK and BOO_NOUNK.

adfhist.h: Add AD_
BOO_DHMIN_VAL, AD_BOO_DHMAX_VAL,
AD_BOO_HMIN_VAL, and AD_BOO_HMAX_VAL.

adfops.h: Add ISFALSE, NOFALSE, ISTRUE and NOTRUE operation codes.
Possibly add ISUNK and NOUNK.

aduint.h: Add prototypes for adu_bool_coerce. Possibly add prototype for
adu_boobldke
y.

4.2.3
ADC: Common Datatype Functions

adccompare.c: Possibly introduce adc_bool_compare (instead of continuing to
use adc_int_compare which first casts both values to i8).

adcgetempty.c: Add case for DB_BOO_TYPE in adc_1getempty_rti.

adchelem.c: Add case fo
r DB_BOO_TYPE in adc_1helem_rti.

adchgdtln.c: Add case for DB_BOO_TYPE in adc_1hg_dtln_rti.

adchmax.c: Add cases for DB_BOO_TYPE in adc_1hmax_rti and
adc_1dhmax_rti.

adchmin.c: Add cases for DB_BOO_TYPE in adc_1hmin_rti and
adc_2dhmin_rti.

adcisminmax.c
: Change case for DB_BOO_TYPE in adc_1isminmax_rti.

adckout.c: Add cases for DB_BOO_TYPE in adc_1klen_rti and adc_2kcvt_rti.

adclenchk.c: Add case for DB_BOO_TYPE in adc_1lenchk_rti. Change
adc_2lenchk_bool to use i1 as the underlying data type (note:
adc_2lenchk_bool
becomes redundant and could be deprecated or dropped entirely).

adcminmaxdv.c: Changes the DB_BOO_TYPE cases in adc_1minmaxdv_rti.

adctm.c: Add cases for DB_BOO_TYPE in adc_1tmlen_rti and adc_2tmcvt_rti.

adcvalchk.c: Add case for DB_BOO
_TYPE in adc_1valchk_rti.

4.2.4
ADE: Compiled Expression Functions

adeexecute.c: Add cases BOO_ISFALSE, BOO_NOFALSE, BOO_ISTRUE and
BOO_NOTRUE in ade_execute_cx. Possibly add BOO_ISUNK and
BOO_NOUNK.



4.2.5
ADG: Server and Session Related Datatype Functions

adgdtta
b.roc: Change “boolean” row of Adi_1RO_datatypes array to allow
BOOLEAN to be stored in tables (AD_INDB flag), and add functions to the
“boolean” adi_dt_com_vect (common datatype functions).

adgoptab.roc: Add "is [not] false" and "is [not] true]" operatio
ns. Possibly add “is
[not] unknown” operation.

fi_defn.txt: Add function instance definitions for BOOLEAN support:
BOO_ISFALSE, BOO_NOFALSE, BOO_ISTRUE, BOO_NOTRUE.
BOO_TO_BOO and LTXT_TO_BOO. Possibly add other function instances for
dealing with UNKNO
WN as a synonym for NULL.

4.2.6
ADU: Abstract Datatype Utility Functions

aduascii.c: Add case for DB_BOO_TYPE in adu_ascii.

adubool.c: New module to include adu_bool_coerce, adu_bool_isfalse and
adu_bool_istrue.

adukey.c: Possibly add new function adu_boobldk
ey or modify adu_ibldkey to
support BOOLEAN.

4.2.7
DUC: Database Creation Utility Functions

ducommon.qsc: Add DB_DEF_ID_FALSE and DB_DEF_ID_TRUE to
duc_init_default.

4.2.8
DUV: Database Upgrade Utility Functions

upgradedb.qsc: Add code to insert DB_DEF_ID_FALSE and
DB_DEF_ID_TRUE into iidefault, if upgrading (needs work).

4.2.9
OPC: Optimizer Query Compilation Functions

opcqual.c: Change PST_USER case in opc_cqual1 to work with extended
DB_BOO_TYPE.

4.2.10
PSL: Parser Language Functions

psldefau.c: Add cases for DB_BOO_TYPE in
psl_2col_ingres_default and
psl_col_user_default. Add cases for DB_DEF_ID_FALSE and
DB_DEF_ID_TRUE in psl_make_canon_default.

FORMTEXT

pslsgram.yi: Add psl_booltype to the union of tokens/non
-
terminals. Add
FALSECONST and TRUECONST types. Add bool_const

production. Add code
to boolean_primary production to deal with implied IS TRUE constructs.



pslsscan.c: Add FALSECONST and TRUECONST to the Key_string/Key_index
arrays.

4.2.11
Embedded Language modules

To be determined.

4.3
COMPATIBILITY LIBRAR
Y INTERFACE CHANGES


No changes required.

4.4
INTERFACE

How do other components that are external to the design interact with this
component? Describe methods and rules of interaction.


Communication protocols; is GCA affected? Other?


Changes of facility call interfaces in the DB
MS (e.g. is dmf_call changed?)


Changes to the interface of non static functions


New error codes or error conditions that will be logged or propagated up the
stack to other functions. Has the error handling for those functions been
updated?

GCA, OpenAPI, an
d the various drivers will have to be modified to support
BOOLEAN. The type will be transported as a single
-
byte integer and if coming
from the DBMS will only be expected to have the values 0 or 1.

4.5
BUILD IMPLICATIONS

No Jam changes should be necessary.

4.6
PI
CCOLO CHANGE NUMBERS

Provide piccolo change numbers for changes made for this feature. This will be
filled in after the fact and should include change numbers for propagations of the
same change to other branches if appropriate


Change
Number

Submitted to
(code
-
branch)

Submission date


main






5
IMPACT AND DOCUMENTA
TION SUMMARY

The estimates in this section are approximate and are intended to give other
groups such as Technical Writing, Services, Support and QA an idea of the
impact this change will have.

5.1
P
RODUCT/COMPONENT IMP
ACTS

5.1.1
Entities

List the tools, commands, reports and messages that are impacted by the
development of the module/function. Use the table below to summarize these
changes; you can refer to other sections for details.

Entity

New

Modifi
ed

C
omments

Tool




Commands




Messages




Help Modules





5.2
DOCUMENTATION

List the existing end
-
user documentation that is affected by modules changes,
and how it is affected. Be as specific and thorough as possible.

MANUAL

CHANGE
S
NEEDED

Estimated #
of

Pages

Installation Guide



Database Administrator Guide

See below


System Administrator Guide



Connectivity Guide



SQL Reference Guide

See below


Command Reference Guide



Migration Guide












Chapter 3 of the
SQL Reference Guide

should d
ocument the BOOLEAN type,
and the SQL constants TRUE and FALSE.

Appendix A of the
Database Administrator Guide

should be changed should be
changed to indicate that BOOLEAN is a valid type, e.g.,

when listing the values
for iicolumns.column_datatype and iic
olumns.column_ingdatatype.



6
QUALITY ISSUES

Look at the component from the QA point of view. Suggest any special tests
that will stress the component. Think how to make the component NOT work
and what special tests should be performed on this component. Th
is is a
guideline to the QA testing procedures
.

6.1
UNIT TESTING SUMMARY

A test
-
driven development (TDD) has been adopted, whereby tests are written
first and the code is then changed to eliminate the test failures. Using the
Python standard library
unittest

module and the existing Ingres Python driver,
SQL statements are sent to the DBMS for execution and results are checked
against expectations, e.g., for the DDL statements, the Ingres catalogs are
queried to verify DBMS operation.

6.1.1
Unit Testing Description

T
he following tests have been coded and used in development so far:

Table creation


Create a table with a (nullable) boolean column


Create a table with a non
-
nullable boolean column


Create a table with a non
-
nullable boolean column with no default


Create a t
able with a non
-
nullable boolean column with unspecified default


Create a table with a non
-
nullable boolean column with FALSE as default


Create a table with a nullable boolean column with TRUE as default


Create a table with a nullable boolean column with N
ULL as default


Create a table with a BOOLEAN column and second table AS SELECT

Indexes


Create a table with boolean column and create an index on it


Create a table with a multi
-
column index (INTEGER, BOOLEAN)

Primary key constraints


Create a table with a BO
OLEAN primary key


Create a table with a BOOLEAN primary key and insert FALSE/TRUE


Create a table with BOOLEAN primary key and insert two TRUEs

Table altering




Alter a table to add a BOOLEAN column


Alter a BOOLEAN WITH DEFAULT column to DEFAULT TRUE


Alter a
BOOLEAN DEFAULT TRUE column to WITH NULL DEFAULT FALSE

Inserts


Insert TRUE into a BOOLEAN NOT NULL column


Insert FALSE into a BOOLEAN column


Insert NULL into a nullable BOOLEAN column


Insert TRUE into a nullable BOOLEAN column


Insert FALSE into a nullable
BOOLEAN column


Insert into a BOOLEAN DEFAULT FALSE column


Insert into a BOOLEAN DEFAULT TRUE column


Insert into a BOOLEAN WITH DEFAULT column


Insert a textual 'FALSE' into a BOOLEAN column


Insert a textual 'true' into a BOOLEAN column


Fail to insert an INT
EGER into a BOOLEAN column

Updates


Insert TRUE into a BOOLEAN column and update it to FALSE


Insert FALSE into a BOOLEAN column and update it to TRUE

Deletes


Insert FALSE into a BOOLEAN column, delete it with WHERE IS FALSE


Insert TRUE into a BOOLEAN column
, delete it with WHERE IS TRUE


Insert TRUE into a BOOLEAN column, delete it with WHERE NOT FALSE


Insert FALSE into a BOOLEAN column, delete it with WHERE NOT TRUE

Standalone selects


Simply select TRUE


Select TRUE converted to character


Select FALSE convert
ed to character


Select TRUE converted to character using Ingres syntax

Singleton selects


Select where TRUE column IS TRUE




Select where TRUE column IS FALSE


Select where TRUE column IS NOT FALSE


Select where TRUE column (implied IS TRUE)

Aggregate selects


S
elect boolean column, COUNT(*) GROUP BY boolean column

Copy table

Unfortunately, these tests don't work because the Ingres Python driver is layered
on top of the ODBC driver which doesn't support COPY TABLE


Copy a table with a BOOLEAN column into a file

Cr
eate procedure


Create a procedure with a BOOLEAN parameter


Create a procedure with a BOOLEAN variable


Create a procedure with a BOOLEAN variable and assign TRUE/FALSE


Create a procedure with a BOOLEAN parameter and use it in an IF statement

Additional test
s based on section 2.2.1 will be forthcoming.

6.2
HANDOFFQA IMPACT

In this section you should document expected or observed diffs in HandofQA
caused by the feature as well as other things that impact HandoffQA; should any
new tests be added to HandoffQA for th
is feature to prevent regression?

The main area that would need new tests is BE/datatypes.

6.3
TESTING RECOMMENDATI
ONS

Suggest other additional function tests that are necessary. Special test
requirements, for example: the security levels, hardware or software

configurations,

code page and multiple code pages, multi
-
system issues. Note
anything that cannot be tested in a lab and which might require field tests. What
can go wrong? How are these situations dealt with?


Click here to begin typing

6.4
REGRESSION RISK A
SSESSMENT

Some of the changes in ADF may affect current behavior, since DB_BOO_TYPE
is an existing type. Naturally, we will try to ensure no regression in behavior is
present in the released code.



6.4.1
Backward Compatibility Issues

The GCA protocol level will
need to be changed (to indicate DB_BOO_TYPE is a
valid type).

Upgradedb will be required (to add new rows to iidefault).





7
PACKAGING AND INSTAL
LATION IMPACT

The SQL BOOLEAN type will be a standard feature of the DBMS package.





8
SUPPORT IMPACT

No diagnostic

or trace facilities are expected to be necessary.


8.1
EXAMPLES AND TESTS

See section 6.1.1.