On 26-5-2018 19:49, blackfalconsoftw...@outlook.com [firebird-support]
wrote:
> However, considering that Firebird is not supported to the same extent
> as SQL Server, my software may make a useful addition to the growing
> number of third-party tools for Firebird.
>
> The problem is, I need to know a way to access the source code for all
> the relevant database objects (ie: stored procedures) so that I can
> import them into my application for version control.
>
> I came across some information in Carlos Cantu's very well written
> migration guide for Firebird 3.x.x that describes the use of the RDB$
> tables for such database objects.
>
> I would like to know if it these are the tables that would allow me to
> access such source code for all such relevant database objects. If so,
> I would consider a conversion of my software to the Firebird Database
> Engine.
I'll describe the basic information source, but I don't have all
information at hand.
It's been a while since I did anything specifically with this, and you
may also glean information by inspecting for example the ISQL or Flame
Robin sources, or maybe in the metadata information in Jaybird.
This list is probably not complete, but here goes:
Tables
You can't directly get the original DDL of a table: you'll need to
rebuild it from information from:
- RDB$RELATIONS (note: also contains views, system tables and external
tables)
- RDB$RELATION_FIELDS
- RDB$FIELDS
- RDB$RELATION_CONSTRAINTS
- RDB$CHECK_CONSTRAINTS
- RDB$REF_CONSTRAINTS
You can discern between different types of tables/views using the
RDB$RELATION_TYPE column (check table RDB$TYPES for definitions)
Views:
Similar to tables. The query defining the view is in
RDB$RELATIONS.RDB$VIEW_SOURCE. Be aware that sometimes database creators
null this to 'hide' this information.
You'll need to recreate the view definition yourself.
Stored procedures:
- RDB$PROCEDURES
- RDB$PROCEDURE_PARAMETERS
- RDB$FIELDS
Assuming PSQL, the body (everything after "AS" in the definition) is in
the column RDB$PROCEDURE_SOURCE. And again database creators sometimes
null this. Since Firebird 3, it is also possible to define stored
procedure in external engines, eg Java. In that case the columns
RDB$ENGINE_NAME and RDB$ENTRYPOINT are non-null.
Exceptions:
- RDB$EXCEPTIONS
Functions:
- RDB$FUNCTIONS
- RDB$FUNCTION_ARGUMENTS
- RDB$FIELDS
Legacy UDFs have columns RDB$MODULE_NAME and RDB$ENTRYPOINT populated.
Firebird 3 PSQL functions have RDB$FUNCTION_SOURCE populated (again
everything after "AS" in the definition).
Domains:
- RDB$FIELDS
Where RDB$SYSTEM_FLAG = 0 and the RDB$FIELD_NAME does not start with RDB$
Indices
- RDB$INDICES
- RDB$INDEX_SEGMENTS
Take care to filter out indices that are automatically defined for
primary, unique and foreign keys (see RDB$RELATION_CONSTRAINTS)
Triggers
- RDB$TRIGGERS
Note: RDB$TRIGGER_SOURCE includes the "AS" part, contrary to other
source columns. Trigger type needs to be decode, see for example
https://github.com/mrotteveel/fbdtp/blob/master/org.eclipse.datatools.enablement.firebird/src/org/eclipse/datatools/enablement/firebird/catalog/FirebirdTrigger.java#L45
Since Firebird 3, it is also possible to define triggers in external
engines, eg Java. In that case the columns RDB$ENGINE_NAME and
RDB$ENTRYPOINT are non-null.
Packages
- RDB$PACKAGES
+ information on procedures and functions that references packages
Privileges
- RDB$USER_PRIVILEGES
Roles
- RDB$ROLES
Users
Users are generally not part of a Firebird database itself.
Other notes
Columns/parameters are defined in multiple levels. For example columns
of tables are defined in RDB$RELATION_FIELDS + RDB$FIELDS (+ technically
RDB$FIELD_DIMENSIONS, but hardly anyone uses arrays as they are barely
supported).
RDB$RELATION_FIELDS defines the specific field, while RDB$FIELDS defines
the underlying domain. Fields with a 'normal' type (instead of a named
domain) get their own implicit domain. When interpreting these, the
information in the specific table (eg RDB$RELATION_FIELDS) takes
precedence over RDB$FIELDS. For example if both define a (non-null)
default value, the one in RDB$RELATION_FIELDS is applied. Similarly for
(NOT) NULL constraints.
--
Mark Rotteveel
