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.
Enter the password to open this PDF file:
File name:
-
File size:
-
Title:
-
Author:
-
Subject:
-
Keywords:
-
Creation Date:
-
Modification Date:
-
Creator:
-
PDF Producer:
-
PDF Version:
-
Page Count:
-
Preparing document for printing…
0%
Comments 0
Log in to post a comment