Provide package or other built-in means to obtain DDL for database objects

[mrotteveel]

Currently, Firebird itself has no means to "reverse engineer" the DDL of database objects. This responsibility is shifted to client tools like ISQL and third-party query tools. I think it would make sense for Firebird itself to be able to generate the necessary DDL, so third party tools and users can query this information directly from the server, instead of having to implement the necessary reverse engineering themselves.

I think a built-in package with function(s) and/or procedure(s) to do this would make sense, e.g. call it RDB$METADATA (which is generic enough that it can also house other metadata related functions in the future). As a rough sketch, I think it could provide the following functions (I think functions make more sense than procedures for this):

• EXTRACT_DATABASE_DDL() returns BLOB SUB_TYPE TEXT (UTF8)\ Generates the DDL of the entire database, similar to isql -extract
• EXTRACT_DDL(OBJECT_TYPE VARCHAR(...), OBJECT_NAME CHAR(63)) returns BLOB SUB_TYPE TEXT (UTF8)\ Generates the DDL of a single object, where OBJECT_TYPE is the name as used in DDL statements (i.e. CREATE <object_type>)

[aafemt]

If provide a package, consider interface compatible with Oracle DBMS_METADATA.

[mrotteveel]

If provide a package, consider interface compatible with Oracle DBMS_METADATA.

I see no point in doing that. That Oracle package has an extremely complex API.

[aafemt]

Yes, it may be complicated but flexible to allow to get many DDLs at once. The package also provides shortcut GET_DDL for simple returning of a single object. More important that it is an established API that users got used to.

[mrotteveel]

I don't think there is much overlap between Oracle and Firebird users, so I don't think that argument makes sense.