Metadata Support

From InterBase
Jump to: navigation, search

Go Up to Change Views


Subscription information is stored in a new system relation RDB$SUBSCRIPTIONS with a unique key on RDB$SUBSCRIPTION_NAME, RDB$SUBSCRIBER_NAME, RDB$DESTINATION. Additional fields store control information to facilitate "check in" and "check out" of changed data. This includes transaction IDs, timestamps, and transactional context of the last observation of changed data on the schema object.

  • The term "check out" denotes SELECT of changed columns of rows from subscribed tables when a subscription has been activated.
  • The term "check in" refers to INSERT, UPDATE, and DELETE of changed columns of rows from subscribed tables when a subscription has been activated.
  • A subscription becomes activated during a database session with the execution of SET SUBSCRIPTION ACTIVE.
  • It is deactivated with the execution of SET SUBSCRIPTION INACTIVE.

Subscription/Subscriber Tables

This topic covers the new and updated columns for the implementation of the Change Views feature. RDB$SUBSCRIPTIONS and RDB$SUBSCRIBERS are new tables covering the subscription/subscriber elements. The other tables listed show columns that have been updated or added to an existing table.

RDB$SUBSCRIPTIONS

This is a new system relation/table starting in ODS version 16:

Column Name Datatype Length Description
RDB$SUBSCRIPTION_NAME CHAR 67 Name of subscription.
RDB$RELATION_COUNTER SMALLINT 2 Counter to track multiple line items inside one subscription.
RDB$RELATION_NAME CHAR 67 Name of relation or view.
RDB$FIELD_NAME CHAR 67 Name of field.
RDB$DESCRIPTION BLOB Subtype text: Text description of the subscription.
RDB$SECURITY_CLASS CHAR 67 Security class of the subscription (the owner for SQL security purposes).
RDB$OWNER_NAME CHAR 67 User who created the subscription.
RDB$RUNTIME BLOB Run-time binary information to enhance performance.
RDB$FLAGS SMALLINT 2
RDB$INSERT BOOLEAN 2 Inserts are tracked.
RDB$UPDATE BOOLEAN 2 Updates are tracked.
RDB$DELETE BOOLEAN 2 Deletes are tracked
RDB$CHANGE BOOLEAN 2 Tracks all operations, but returns as soon as any column changes.

RDB$SUBSCRIBERS

This is a new system relation/table starting in ODS version 16. The required Subscriber information is stored in a system relation table:

Column Name Datatype Length Description
RDB$SUBSCRIBER_NAME CHAR 31 Name of subscribing user
RDB$SUBSCRIPTION_NAME CHAR 67 Name of subscription
RDB$DESTINATION CHAR 32 Destination of subscriber
RDB$FLAGS SMALLINT 2
RDB$CHECK_OUT_TRANSACTION_ID INT64 8 Transaction ID of last subscription check out
RDB$CHECK_OUT_TIMESTAMP TIMESTAMP 8 Date and time of last subscription check out
RDB$CHECK_OUT_OLDEST_TRANSACTION_ID INT64 8 Transaction of oldest active transaction at check out
RDB$CHECK_OUT_TRANSACTIONS BLOB Set of active transaction IDs at last transaction check out
RDB$CHECK_IN_TRANSACTION_ID INT64 8 Transaction ID of last subscription check in
RDB$CHECK_IN_TIMESTAMP TIMESTAMP 8 Date and time of last subscription check in
RDB$CHECK_IN_TRANSACTIONS BLOB Set of check in transaction IDs by this subscription


RDB$ENCRYPTIONS

RDB$ENCRYPTIONS describes the characteristics of encryptions stored in the database. The column name RDB$FLAGS was updated and RDB$ENCRYPTION_ID was added:

Column Name Datatype Length Description
RDB$FLAGS SMALLINT 2
  • 1 random initialization vector defined for cipher block chaining encryption mode.
  • 2 random padding of plain text.
  • 4 encryption is marked for deletion.
  • 32 indicates one or more subscriptions on the relation.
RDB$ENCRYPTION_ID SMALLINT Unique identifier for the Encryption Key.


RDB$FIELDS

RDB$FIELDS defines the characteristics of a column. Each domain or column has a corresponding row in RDB$FIELDS. A NEW column name RDB$SUBSCRIBE_FLAG was added:

Column Name Datatype Length Description
RDB$SUBSCRIBE_FLAG SMALLINT 2 Indicates one or more subscriptions of the field.


RDB$TRIGGERS

This table defines triggers. The column name RDB$PRIVILEGE was added with a new subscribe value:

Column Name Datatype Length Description
RDB$PRIVILEGE CHAR 6 Identifies the privilege granted to the user listed in the RDB$USER column, above. The character stored in the field corresponds to the valid values listed below.

Valid values are:

  • ALL (A)
  • SELECT (S)
  • DELETE (D)
  • INSERT (I)
  • UPDATE (U)
  • REFERENCE (R)
  • MEMBER OF (for roles) (M)
  • DECRYPT (T)
  • ENCRYPT (E)
  • SUBSCRIBE (B)

There are also changes to existing metadata relations:

RDB$RELATIONS

RDB$RELATIONS defines some of the characteristics of tables and views. The column name RDB$FLAGS was updated as follows:

Column Name Datatype Length Description
RDB$FLAGS SMALLINT 2 No
1 = SQL-defined table.
2 = Global temporary table.
4 = <reserved for future use>.
8 = Delete temporary rows on commit.
16 = Preserve temporary rows on commit; rows are deleted on database detach.
32 = Indicates one or more subscriptions on the relation.


RDB$RELATION_FIELDS

For database tables, RDB$RELATION_FIELDS lists columns and describes column characteristics for domains. Four column names have been added: RDB$FLAGS, RDB$FIELD_NAME, RDB$RELATION_NAME,, and RDB$FIELD_SOURCE, as follows:

Column Name Datatype Length Description
RDB$FIELD_NAME CHAR 67 Column name defined by the user.
RDB$RELATION_NAME CHAR 67 Table name defined by the user.
RDB$FIELD_SOURCE CHAR 67 Internal Column name that matches up with RDB$FIELDS.RDB$FIELD_NAME.
RDB$FLAGS SMALLINT 2 Yes 1 = One or more subscriptions on the field.


RDB$USER_PRIVILEGES

RDB$USER_PRIVILEGES keeps track of the privileges assigned to a user through a SQL GRANT statement. The column names RDB$USER and RDB$GRANTOR have been updated from a length of 31 to 67:

Column Name Datatype Length Description
RDB$USER CHAR 67 Names the user who was granted the privilege listed in the RDB$PRIVILEGE column.
RDB$GRANTOR CHAR 67 Names the user who granted the privilege.
RDB$PRIVILEGE CHAR 6 No Subscribe (B)

Advance To: