Support Adding a New Not Null With Default Value - Design Specifcation
See Also: Functional Specifcation
Currently when a new NOT NULL column with DEFAULT value is added to a populated table using ALTER TABLE ... ADD statement in ISQL, the column is initialized to 0 or blank depending on the data type of the column. The default value is not used for the initialization. For example:
CREATE TABLE t1 (c1 INT); INSERT INTO t1 (c1) VALUES (1); ALTER TABLE t1 ADD c2 INT DEFAULT 99 NOT NULL, ADD c3 CHAR (10) DEFAULT USER NOT NULL;
Currently we get:
SELECT * FROM t1; C1 C2 C3 ====== ====== ====== 1 0
If one trys to GBAK restore/create from the .gbk file, GBAK will wrongly issue the following messages:
gbak: ERROR: validation error for column C2, value "*** null ***" gbak: ERROR: warning --- record could not be found.
The correct result should be:
C1 C2 C3 ====== ====== ====== 1 99 BIETIE
This database should be GBAK restore/create able.
One way to solve this problem is writing out the default value to disk when adding a NOT NULL with DEFAULT value column to a populated table. The problem with this approach is performance. If there is 1 million rows of data in the existing table, then the engine has to write 1 million times of default value to disk. This will make InterBase look very bad.
In this project, we are taking a different approach. There will be no writing default value to disk until it absolutely have to. For example in an UPDATE operation, when the user modifies the data of a NOT NULL with DEFAULT column, then the engine will write out the modified data to the disc. For column with an index, the engine will do the same thing and additionaly make the index key with the default value.
The engine will do the same thing during GBAK. Currently GBAK is not able to GBAK restore/create any database that has NOT NULL columns with DEFAULT value but have no data value in disk. With this approach, when GBAK sees a NOT NULL column with DEFAULT value but has no data value in disk, it will pick up the default value stored in FLD/Field Block Structure and writes it out to disk.
- The server reads the data of a column from the disk.
- When there is no data, make sure that the column is a NOT NULL column by checking fld_not_null in the FLD/Field Block Structure.
- Then get the default value from the fld_default_value in the FLD/Field Block Structure if there is one.
- Assign the field with value from the fld_default_value
- For column with an index we will do the same and additionaly make, the index key with the default value.
nod_field used to point only to e_fld_stream and e_fld_id. Now nod_field not only points to e_fld_stream and e_fld_id, but also points to e_fld_default_value. If the column is a NOT NULL column with DEFAULT value, the fld_node->nod_arg [e_fld_default_value] points to the default value. Otherwise it points to NULL.
Two new error messages are added:
- isc_bad_default_value, can not define a not null column with NULL as default value, 335544759L
- isc_invalid_clause, invalid clase NOT NULL DEFAULT NULL, 335544760L
Above messages will be issued for the following invalid SQL statements:
1. CREATE TABLE t1 ( c1 INT DEFAULT NULL NOT NULL); 2. ALTER TABLE t1 ADD c1 INT DEFAULT NULL NOT NULL;
If there is no data on the disk for a column, then get the column information from rel_fields of REL/Relation Block. If the DEFAULT value is USER, then assign the owner of the table to dsc_address of DSC/Descripter Block. Otherwise assign fld_default_value of FLD/Field Block to dsc_address of DSC/Descripter Block. The same logic is good for both compound index and regular index.
generates a new nod_field and assigns it to temp_node. If the input node is a nod_field and the input node has a default value, then assign the default value of the input node to temp_node->nod_arg [e_fld_default_value].
Get the data of nod_argument from disk. If there is none, then assign the default value of nod_field to dsc_address of impure->vlu_desc if and only of
- parent node of nod_argument is nod_assignment
- the "assign to" sub-node pointed by the nod_assignment is nod_field
- the nod_field has a default value
Get the data of nod_field from disk. If there is none, then get the default value from the REL/Relation Block. If the default value is a USER, then the owner of the relation is the default value. Otherwise it will be what ever the default value that user specified.
Looping through each columns for new_format->fmt_count
- get the new descriptor for the column. If there is no data, then get the default value from the REL/Relation Block. If the default value is USER, then the owner of the relation will be the default value. Otherwise it is what ever the user specified as default value.
- get the original descriptor for the column, If there is no data, then get the default value from the REL/Relation Block. If the default value is USER, then the owner of the relation will be the default value. Otherwise it is what ever the user specified as default value.
- End of the loop.
- Looping through each columns for insertion_idx->idx_count
- get the descriptor for each column from the disk. If there is no data, then get the default value from the REL/Relation Block. If the default value is USER, then the owner of the relation will be the default value. Otherwise it is what ever the user specified as default value.
- End of the loop.
If the column has default value then assign the default value in REL/Relation Block into node field's nod_arg [e_fld_default_value]. Otherwise is set to NULL.
If the node type of the default value is NULL then set default_null_flag to TRUE. Otherwise is set to FALSE.
If the column is NOT NULL column and default_null_flag is TRUE, then issues isc_bad_default_value and isc_invalid_clause error message.
CREATE/ALTER TABLE Statement:
- DEFAULT numeric literal
- DEFAULT character literal
- DEFAULT NULL
- DEFAULT USER
- default value was in the domain
- column function ( COUNT/DISTINCT/.../MIN/MAX)
- [NOT] BETWEEN
- [NOT] LIKE
- [NOT] IN
- [NOT] CONTAINING
- [NOT] STARTING
- search using index
- search with out using index
- sorting using index
- sorting with out using index
- gbak -b xx.gdb xx.gbk
- gbak -c xx.gbk aa.gdb
- gbak -r xx.gdb bb.gdb
Other unrelated problems not addressed
- special register USER returns inconsistent user information (some time returns user information in lower case and some time in upper case)
- adding a NOT NULL column with no DEFAULT value to populated table