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

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
          

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

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

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

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
          

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
        

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> ]
      

  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.

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))
      

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;
      

 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.

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

See Tracker ticket CORE-4955.

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.