Firebird Documentation IndexFirebird 3.0.2 Release NotesData Definition Language (DDL) → DDL Enhancements
Firebird Home Firebird Home Prev: Data Definition Language (DDL)Firebird Documentation IndexUp: Data Definition Language (DDL)Next: Data Manipulation Language (DML)

DDL Enhancements

New Data Types
Manage Nullability in Domains and Columns
Modify Generators (Sequences)
Alter the Default Character Set
BLOB Expressions in Computed Columns
Linger” Database Closure for Superserver
Option to Preserve Shadow File
New SQL for Managing Users and Access Privileges

The following enhancements have been added to the SQL data definition language lexicon:

New Data Types

A fully-fledged Boolean type is introduced in this release, along with a surfaced emulation of the Microsoft-style “identity” column.

BOOLEAN Data Type

Adriano dos Santos Fernandes

The SQL-2008 compliant BOOLEAN data type (8 bits) 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. The specification does not make a distinction between the NULL value of this data type and the truth value UNKNOWN that is the result of an SQL predicate, search condition, or boolean value expression: they may be used interchangeably to mean exactly the same thing.

As with many programming languages, the SQL BOOLEAN values can be tested with implicit truth values. For example, field1 OR field2 and NOT field1 are valid expressions.

The IS Operator

Predications use the operator IS [NOT] for matching. For example, field1 IS FALSE, or field1 IS NOT TRUE.

Note

Equivalence operators (“=”, “!=”, “<>” and so on) are valid in all comparisons.

Examples

CREATE TABLE TBOOL (ID INT, BVAL BOOLEAN);
COMMIT;

INSERT INTO TBOOL VALUES (1, TRUE);
INSERT INTO TBOOL VALUES (2, 2 = 4);
INSERT INTO TBOOL VALUES (3, NULL = 1);
COMMIT;

SELECT * FROM TBOOL
          ID    BVAL
============ =======
           1 <true>
           2 <false>
           3 <null>

-- Test for TRUE value
SELECT * FROM TBOOL WHERE BVAL
          ID    BVAL
============ =======
           1 <true>

-- Test for FALSE value
SELECT * FROM TBOOL WHERE BVAL IS FALSE
          ID    BVAL
============ =======
           2 <false>

-- Test for UNKNOWN value
SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN
          ID    BVAL
============ =======
           3 <null>

-- Boolean values in SELECT list
SELECT ID, BVAL, BVAL AND ID < 2
  FROM TBOOL
          ID    BVAL
============ ======= =======
           1 <true>  <true>
           2 <false> <false>
           3 <null>  <false>

-- PSQL Declaration with start value
DECLARE VARIABLE VAR1 BOOLEAN = TRUE;

-- Valid syntax, but as with a comparison 
-- with NULL, will never return any record
SELECT * FROM TBOOL WHERE BVAL = UNKNOWN
SELECT * FROM TBOOL WHERE BVAL <> UNKNOWN
          
Use of Boolean against other data types

Although BOOLEAN is not inherently convertible to any other datatype, from v.3.0.1 the strings 'true' and 'false' (case-insensitive) will be implicitly cast to Boolean in value expressions, e.g.,

  if (true > 'false') then ...
            

'false' is converted to Boolean. An attempt use the Boolean operators AND, NOT, OR and IS will fail. NOT 'False', for example, is invalid.

A Boolean can be explicitly converted to and from string with CAST. UNKNOWN is not available for any form of casting.

Other Notes

  • Represented in the API with the FB_BOOLEAN type and FB_TRUE and FB_FALSE constants.

  • The value TRUE is greater than the value FALSE.

Keywords INSERTING, UPDATING and DELETING

To avoid ambiguities when used in Boolean expressions, the previously non-reserved keywords INSERTING, UPDATING and DELETING, which return True|False when tested in PSQL, have been made reserved words in all contexts. If you have used any of these words as identifiers for database objects, columns, variables or parameters, it will be necessary to redefine them, either with new names or by enclosing their identifiers in double quotes.

Identity Column Type

Adriano dos Santos Fernandes

An identity column is a column associated with an internal sequence generator. Its value is set automatically when the column is omitted in an INSERT statement.

Syntax Patterns

    <column definition> ::=
        <name> <type> GENERATED BY DEFAULT AS IDENTITY [ (START WITH <value>) ]<constraints>
        

When defining a column, the optional START WITH clause allows the generator to be initialised to a value other than zero. See Tracker ticket CORE-4199.

    <alter column definition> ::=
        <name> RESTART [ WITH <value> ]
        

A column definition can be altered to modify the starting value of the generator. RESTART alone resets the generator to zero; the optional WITH <value> clause allows the restarted generator to start at a value other than zero. See Tracker ticket CORE-4206.

Rules

  • The data type of an identity column must be an exact number type with zero scale. Allowed types are thus SMALLINT, INTEGER, BIGINT, NUMERIC(x,0) and DECIMAL(x,0).

  • An identity column cannot have DEFAULT or COMPUTED value.

Notes

  • An identity column cannot be altered to become a regular column. The reverse is also true.

  • Identity columns are implicitly NOT NULL (non-nullable).

  • Uniqueness is not enforced automatically. A UNIQUE or PRIMARY KEY constraint is required to guarantee uniqueness.

  • The use of other methods of generating key values for IDENTITY columns, e.g., by trigger-generator code or by allowing users to change or add them, is discouraged to avoid unexpected key violations.

Example

create table objects (
  id integer generated by default as identity primary key,
  name varchar(15)
);

insert into objects (name) values ('Table');
insert into objects (name) values ('Book');
insert into objects (id, name) values (10, 'Computer');

select * from objects;

          ID NAME
============ ===============
           1 Table
           2 Book
          10 Computer
          
Implementation Details

Two new columns have been inserted in RDB$RELATION_FIELDS to support identity columns: RDB$GENERATOR_NAME and RDB$IDENTITY_TYPE.

  • RDB$GENERATOR_NAME stores the automatically created generator for the column. In RDB$GENERATORS, the value of RDB$SYSTEM_FLAG of that generator will be 6.

  • Currently, RDB$IDENTITY_TYPE will currently always store the value 1 (by default) for identity columns and NULL for non-identity columns. In the future this column will store the value 0, too (for ALWAYS) when Firebird implements support for this type of identity column.

Manage Nullability in Domains and Columns

A. dos Santos Fernandes

ALTER syntax is now available to change the nullability of a table column or a domain

Syntax Pattern

ALTER TABLE <table name> ALTER <field name> { DROP | SET } [NOT] NULL

ALTER DOMAIN <domain name> { DROP | SET } [NOT] NULL
        

Notes

The success of a change in a table column from NULL to NOT NULL is subject to a full data validation on the table, so ensure that the column has no nulls before attempting the change.

A change in a domain subjects all the tables using the domain to validation.

An explicit NOT NULL on a column that depends on a domain prevails over the domain. In this situation, the changing of the domain to make it nullable does not propagate to the column.

Modify Generators (Sequences)

More statement options have been added for modifying generators (sequences). Where previously in SQL the only option was ALTER SEQUENCE <sequence name> RESTART WITH <value>, now a full lexicon is provided and GENERATOR and SEQUENCE are synonyms for the full range of commands.

RESTART can now be used on its own to restart the sequence at its previous start or restart value. A new column RDB$INITIAL_VALUE is added to the system table RDB$GENERATORS to store that value.

A generator (sequence) can also be [re]created or altered to include an optional “step” clause to make the generator increment the series by two or more steps instead of the default 1. The clause is implemented as INCREMENT BY <number> and is stored in RDB$GENERATORS in RDB$GENERATOR_INCREMENT.

Syntax Forms

{ CREATE | RECREATE } { SEQUENCE | GENERATOR } <sequence name> [ START WITH <value> ]
   [ INCREMENT BY <number> ]

CREATE OR ALTER { SEQUENCE | GENERATOR } <sequence name> { RESTART | START WITH <value> }
   [ INCREMENT BY <number> ]

ALTER { SEQUENCE | GENERATOR } <sequence name> RESTART [ WITH <value> ]
   [ INCREMENT BY <number> ]
      

Function GEN_ID()

The legacy function GEN_ID(generator_name, step_value)) still works to set a one-time step value in its second argument. If it is used, the stored RDB$GENERATOR_INCREMENT value is overridden.

Alter the Default Character Set

A. dos Santos Fernandes
  ALTER DATABASE
    ...
    SET DEFAULT CHARACTER SET <new_charset>
      

The alteration does not change any existing data. The new default character set is used only in subsequent DDL commands and will assume the default collation of the new character set.

BLOB Expressions in Computed Columns

Adriano dos Santos Fernandes

A substring from a BLOB column can now be used to define a computed column.

For Example

ALTER TABLE ATABLE 
  ADD ABLOB
  COMPUTED BY (SUBSTRING(BLOB_FIELD FROM 1 FOR 20))
      

Linger” Database Closure for Superserver

Sometimes it is desirable to have the Superserver engine keep the database open for a period after the last attachment is closed, i.e., to have it “linger” a while. It can help to improve performance at low cost, under conditions where the database is opened and closed frequently, by keeping resources “warm” for next time it is reopened.

Firebird 3.0 introduces an enhancement to ALTER DATABASE to manage this optional LINGER capability for databases running under Superserver.

Syntax Form

  ALTER DATABASE SET LINGER TO {seconds};
  ALTER DATABASE DROP LINGER;
      

Usage

To set linger for the database do:

 ALTER DATABASE SET LINGER TO 30;  -- sets linger interval to 30 seconds
      

Either of the following forms will clear the linger setting and return the database to the normal condition (no linger):

 ALTER DATABASE DROP LINGER;
 ALTER DATABASE SET LINGER TO 0;
      

Note

Dropping LINGER is not an ideal solution for the occasional need to turn it off for some once-only condition where the server needs a forced shutdown. The gfix utility now has the -NoLinger switch, which will close the specified database immediately the last attachment is gone, regardless of the LINGER setting in the database. The LINGER setting is retained and works normally the next time.

The same one-off override is also available through the Services API, using the tag isc_spb_prp_nolinger, e.g. (in one line):

 fbsvcmgr host:service_mgr user sysdba password xxx
        action_properties dbname employee prp_nolinger
        

See also Tracker ticket CORE-4263 for some discussion of the development of this feature.

Option to Preserve Shadow File

Alex Peshkov

The DROP SHADOW command has a new option to preserve the shadow file in the filesystem:

DROP SHADOW shadow_num
  [{DELETE | PRESERVE} FILE]
        

See Tracker ticket CORE-4955.

New SQL for Managing Users and Access Privileges

A number of new features and enhancements have been added to the DDL lexicon for managing users and their access to objects in databases. They are described in detail in Chapter 7, Security.

The SQL set of DDL commands for managing user accounts has been enhanced in Firebird 3, thus improving the capability to manage (add, modify or delete) users in a security database from a regular database attachment.

gsec is deprecated!

The command-line and shell utility gsec is deprecated from this release forward. It will continue to work with security3.fdb but it will not work with alternative security databases.

The SQL2008-compliant operator SET ROLE allows the CURRENT_ROLE context variable to be set to one that has been granted to the CURRENT_USER or to a user assigned to the database attachment as trusted (SET TRUSTED ROLE).

Previously, the grantor or revoker of SQL privileges was always the current user. The GRANTED BY clause makes it so that a different grantor or revoker can be specified in GRANT and REVOKE commands.

When a user is removed from the security database or another authentication source, this new command is useful for revoking its access to all objects in the database.

In Firebird 3, the system tables are read-only. This SQL syntax provides the means to assign metadata write privileges to specified users or roles for specified objects.

EXECUTE permission is now supported for UDFs (both legacy and PSQL based ones).

New SQL-2008 compliant USAGE permission is introduced to protect metadata objects other than tables, views, procedures and functions.

Prev: Data Definition Language (DDL)Firebird Documentation IndexUp: Data Definition Language (DDL)Next: Data Manipulation Language (DML)
Firebird Documentation IndexFirebird 3.0.2 Release NotesData Definition Language (DDL) → DDL Enhancements