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
