ibec_ExtractMetadata

Extracts metadata (and data if specified) of a database into a script.

Description

This function extracts metadata/data of a specified database into a script.

Note: In Firebird mixed case table names that are unquoted are stored in uppercase; in queries these object names are case insensitive. You need to use the table name as it is stored in the metadata tables for this function. This means: UPPERCASE for unquoted object names or the exact casing of quoted object names.

ibec_ExtractMetadata also supports the OctetsAsHex option which allows the extraction of CHAR(n) CHARACTER SET OCTETS values in hexadecimal format.

Firebird 3.0

You should specify packages in the Options string:

PACKAGES=ALL;

or

PACKAGES=PACKAGE_1,PACKAGE_2,...;

or

PACKAGES=NONE;

The list of stored functions to be extracted should be specified in the Options string:

FUNCTIONS=ALL;

or

FUNCTIONS=FUNCTION_1,FUNCTION_2,...;

or

FUNCTIONS=NONE;

The ExtractDDLPrivileges option allows extraction of privileges on data definition language statements such as CREATE/ALTER/DROP (Firebird 3).

The UseUpdateOrInsert option allows extraction of data as UPDATE OR INSERT statements instead of INSERT.

Syntax

 function ibec_ExtractMetadata(Connection : variant; ScriptFile :string;
                             Options : string; CallbackProc : variant): string;

Parameters

Connection Active database connection.
ScriptFile Name of the resulting script file or directory name if the VCSFiles option is used.
Options List of options delimited with semicolon; possible options are:
ServerVersion You can use the following server version identifiers: IB5X, IB61, IB65, IB70, IB71, IB75, IB2007, FB10, FB15, FB20, FB21, FB25, YA10.
AddKeyword For example: FIRST, SECOND, START, TIME.
RemoveKeyword For example: FIRST, SECOND, START, TIME.
VCSFiles Each database object definition will be extracted into a separate file.
SeparateFiles Extracts metadata (and data if specified) into a set of files: two files with metadata (_ibe$start_.sql and _ibe$finish_.sql), files containing table data (one or more files for each database table) and a runme.sql file, that consists of a number of INPUT <file_name> statements in the correct order.
GenerateCreate Determines whether a CREATE DATABASE statement should be included at the beginning of the generated script.
GenerateConnect Determines whether a CONNECT statement should be included at the beginning of the generated script.
IncludePassword Determines whether the password should be included into the CREATE DATABASE or the CONNECT statement in the resulting SQL script.
SuppressComments Use to suppress comments in the resulting script.
IncludeCharset This option forces IBExpert/IBEScript to include the CHARACTER SET clause into the definition of all CHAR/VARCHAR/domains/columns/parameters, even if their CHARSET is equal to the default CHARSET of the database.
SeparateComputedBy Specifies whether computed fields should be extracted separately.
SetGenerators Use to set generator values.
ExtractDescriptions Determines whether database object descriptions should be included into the generated script.
DescriptionsAsUpdate Determines whether the raw UPDATE statement will be used for object descriptions instead of the IBExpert-specific DESCRIBE statement.
ExtractPrivileges Use to extract privileges.
OnlySelectedPrivileges If used only privileges of the selected objects will be included into the resulting script. Otherwise ALL privileges will be extracted.
UseReinsert Determines whether the IBExpert REINSERT command should be used to insert multiple data records.
UseUpdateOrInsert Allows extraction of data as UPDATE OR INSERT statements instead of INSERT.
UseRecreate If this option is specified, the RECREATE TABLE statement will be used instead of CREATE TABLE.
UseBlocks Implemented in IBExpert version 2021.02.09.
CommitBlocks Implemented in IBExpert version 2021.02.09.
ExtractBLOBs Determines whether blob values should be extracted.
BlobsAsHex If this option is enabled the values of blob fields will be written directly into the result script in hexadecimal notation. Hexadecimal notation for binary values is available in Firebird 2.5 and Firebird 3. The length of the binary value which may be encoded in hexadecimal notation is limited to 32765 bytes in Firebird 2.5 and to 65535 bytes in Firebird 3. If the blob value exceeds the limit it will be written in the lob-file as is.
ExcludeIBE Use to omit database objects with the prefix IBE$.
ExcludeTMP Use to omit database objects with the prefix TMP$ (InterBase® 7.x).
DecodeDomains Determines whether domain definitions will be extracted as comments to the corresponding table fields.
CommitAfter=X This option defines the number of records before inserting the COMMIT statement into the script. The default value is 500, i.e. 500 insert commands are performed and then committed.
MaxFileSize=X Defines the maximum file size of script files (in megabytes). The default value is 0, this means that there will be no file splitting.
DateTimeFormat= DateTimeFormat=
“dd.mm.yy hh:mm:ss.zzz” Allows user-defined formatting of datetime (timestamp) values. Usage is similar to the DateFormat option.
DateFormat=<format> Specifies the format of date values and date part of timestamp values.
Domains=<objects_list> Specifies list of domains to be extracted. Items should be separated with comma. If this option is not defined all domains will be extracted.
Tables=<objects_list> Specifies list of tables to be extracted. Items should be separated with comma. If this option is not defined all tables will be extracted.
Views=<objects_list> Specifies list of views to be extracted. Items should be separated with comma. If this option is not defined all views will be extracted.
Triggers=<objects_list> Specifies list of triggers to be extracted. Items should be separated with comma. If this option is not defined all triggers will be extracted.
DelayedTriggerActivation Delayed activation of database events and DDL triggers: if this option is ON, DB event and DDL triggers will be first created as inactive, and activated at the end of the result script.
Procedures=<objects_list> Specifies list of procedures to be extracted. Items should be separated with comma. If this option is not defined all procedures will be extracted.
Generators=<objects_list> Specifies list of generators to be extracted. Items should be separated with comma. If this option is not defined all generators will be extracted.
Exceptions=<objects_list> Specifies list of exceptions to be extracted. Items should be separated with comma. If this option is not defined all exceptions will be extracted.
UDFs=<objects_list> Specifies list of UDFs to be extracted. Items should be separated with comma. If this option is not defined all UDFs will be extracted.
Roles=<objects_list> Specifies list of roles to be extracted. Items should be separated with comma. If this option is not defined all roles will be extracted.
Packages Specifies list of packages (Firebird 3.0) to be extracted. Specify ALL, NONE or a comma-separated list, e.g. PACKAGE_1,PACKAGE_2,…;.
Stored functions Specifies list of stored functions (Firebird 3.0) to be extracted. Specify ALL, NONE or a comma-separated list, e.g. FUNCTION_1,FUNCTION_2,…;.
ExtractDDLPrivileges Allows extraction of privileges on data definition language statements such as CREATE/ALTER/DROP (Firebird 3).
DataTables=<objects_list> Specifies the list of tables from which data should be extracted. If this option is not defined NO data will be extracted. You can use the ALL keyword as a list of objects to specify that all objects of that type must be extracted. You can use the NONE keyword as a list of objects to omit all objects of that type.
AlwaysQuoteIdents When specified, all identifiers (except those in procedure/trigger bodies) will be quoted.
OctetsAsHex This option allows the extraction of CHAR(n) CHARACTER SET OCTETS values in hexadecimal format.
ExplicitDDLCommit Implemented in IBExpert version 2020.09.13, this option adds the COMMIT statement after each DDL statement in the result script.
CallbackProc A callback IBEBlock which will be executed for each record processed whilst comparing data. The callback IBEBlock must have at least one input parameter, which will be used to pass a number of processed records within it.
UseComment For support of the Firebird 2 COMMENT ON statement.
UseSequence For support of the Firebird 2.x CREATE / ALTER SEQUENCE.

Examples of usage

1. Extracting domain definitions in VCS-files:

 execute ibeblock
 as
 begin
    cbb = 'execute ibeblock (LogLine variant)
      as
      begin
        ibec_progress(LogLine);
      end';
 
    db = ibec_GetDefaultConnection();
 ibec_ExtractMetadata(db, 'E:\Domains\', 'Domains=ALL; Tables=NONE; Views=NONE;
 Triggers=NONE; Procedures=NONE; Generators=NONE;
 Exceptions=NONE; UDFs=NONE; Roles=NONE;
  VCSFiles', cbb);
 end;

2. Complete metadata extract:

 execute ibeblock
 as
 begin
    cbb = 'execute ibeblock (LogLine variant)
      as
      begin
        ibec_progress(LogLine);
      end';
 
  db = ibec_GetDefaultConnection();
ibec_ExtractMetadata(db, 'E:\meta.sql', 'GenerateCreate; ExtractPrivileges; ExtractDescriptions',
cbb);
  end;

3. Extracting data from specified tables:

 execute ibeblock
 as
 begin
    cbb = 'execute ibeblock (LogLine variant)
      as
      begin
        ibec_progress(LogLine);
      end';

    db = ibec_GetDefaultConnection();
 ibec_ExtractMetadata(db, 'E:\data.sql', 'Domains=NONE; Tables=NONE; Views=NONE; Triggers=NONE; 

 Procedures=NONE; Generators=NONE;
 Exceptions=NONE; UDFs=NONE; Roles=NONE;DataTables=IBE$$TEST_DATA, MY_TABLE;
 ExtractBLOBs;UseReinsert; CommitAfter=1000', cbb);
    end;

4. Using the IncludeCharset parameter:

 ibec_ExtractMetadata(db, 'E:\meta.sql', 'GenerateCreate;IncludeCharset;ExtractPrivileges; ExtractDescriptions',cbb);

5. Using a number of parameters:

 ibec_ExtractMetadata(MyDB, 'D:\MyDB.sql', 'ServerVersion=FB21; AddKeyword=FIRST,SECOND,KEY; 
 RemoveKeyword=START,TIME; ...', cbb);