- Extracting metadata in IBExpert
- How does IBExpert extract objects
- How does IBExpert extract blobs?
- Obtain current generator values
- Database repair using Extract Metadata
The Extract Metadata menu item can be found in the IBExpert Tools menu, or started using the respective icon in the Tools toolbar. This item is unfortunately not available in the IBExpert Personal Edition.
The Extract Metadata module can be used to generate a partial or full database metadata script, including table data, privileges and object descriptions if wished. It allows the user to extract metadata to file or clipboard. It is even possible to extract blob data and array fields' data (as blob data into a
Table data can be extracted into separate files (
TABLE_3.sql etc.) - the maximum file size can be specified on the Options page; once this size is reached, a new file is automatically generated by IBExpert, a particularly useful option when working with extremely large scripts, as problems are often encountered when executing scripts larger than 2 GB.
Support is also offered for secondary database file information; the corresponding
ALTER DATABASE statements are included into the result script as comments.
To begin preparing your metadata extraction, first select a database from the toolbar's drop-down list of all registered databases. The toolbar's Extract to options include:
- Script Executive (default)
- VCS Files
- Separate Files
The Separate Files mode extracts metadata (and data if specified) into a set of files: two files with metadata (
_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.
If either the File, VCS Files or Separate Files options are selected, it is of course necessary to specify a file path and name (
*.sql or Metadata Extract Configuration
The first dialog page, Meta Objects, displays the Select Objects tree. This Objects Tree window can be found in the following editors:
- Extract Metadata Editor on the first page, Meta Objects,
- Generate HTML Documentation Editor, also on the Objects page,
- Print Metadata dialog.
The Select Objects Tree feature offers the user the choice whether to extract all database objects (check option), or specify individual objects, (using the < or > buttons, drag 'n' dropping the object names or double-clicking on them), or object groups (using the << or >> buttons, drag 'n' dropping the object headings or double-clicking on them).
Multiple objects can be selected using the [Ctrl] or [Shift] keys. There is even the option to Add Related Objects by simply clicking the respective button above the Selected Objects window.
Objects can be dragged from the object dependencies trees (found on the Dependencies page in the object editors) and the field dependencies list (found in the Field Dependencies window at the bottom of the Fields page in the table/view editors) into the Selected Objects tree.
Since version 2010.07.26 IBExpert offers support of default database collation for Firebird 2.5 databases.
The Data Tables page can be used to specify whether data should also be extracted. This allows both user-defined and system tables to be selected - either all or individually:
again using the <, >>, > or >> buttons, drag 'n' dropping or double-clicking.
By selecting one of the tables in the Selected Tables list on the right-hand side, it is possible to add a
WHERE clause, if wished.
The Extract Metadata Options page offers a wide range of further check options:
- Generate 'CREATE DATABASE' statement: this determines whether a
CREATE DATABASEstatement should be included at the beginning of the generated script. If this option is unchecked, the
CONNECTstatement will be included instead.
- Generate 'CONNECT' statement: specifies the
- Include password into 'CONNECT' and 'CREATE DATABASE' statements: this determines whether the password should be included into the
CREATE DATABASEor the
CONNECTstatement in the resulting SQL script.
- Limit File Size: This defines the maximum file size of the resulting script(s). When this option is specified and the maximum file size is reached, IBExpert automatically creates the next file with suffixes
0002etc. Don't forget to specify the Separate Files option from the Extract to drop-down list at the top of the Extract Metadata window, when working with this option.
- Set Generators: if this option is checked, the
SET GENERATORstatement for each generator will be included into the resulting script.
- Include object descriptions: this determines whether database objects descriptions should be included into the generated script. See How does IBExpert extract objects descriptions? for more details.
- Use UPDATE instead of DESCRIBE: if this option is enabled, IBExpert will generate an
UPDATE RDB$xxx SET RDB$DESCRPTION ...statement instead of
DESCRIBEwhile extracting metadata. You first need to check the option, Use UPDATE instead of DESCRIBE when extracting object descriptions, found in the IBExpert Database menu item, Register Database or Database Registration Info under Additional / Extract Metadata. By default it corresponds to the value specified in the Database Registration Info.
- Use COMMENT statement (Firebird 2): this forces object descriptions to be extracted as a set of
COMMENTstatements. Refer to the Firebird 2.0.4 Release Notes chapter,
COMMENTstatement implemented for further information.
- Use UPDATE instead of DESCRIBE: if this option is enabled, IBExpert will generate an
- Extract COMPUTED BY fields separately: this option can be used to specify whether computed fields should be extracted separately (useful if there are bugs in the database; realistically however this option is seldom used).
- Always include the CHARACTER SET for domains/fields/parameters.
- Always include COLLATE for domains/fields/parameters: this option was introduced in IBExpert version 2012.02.21.
- Exclude IBExpert (IBE$*) objects: check option.
- Exclude TMP$* objects (InterBase 7.x): check option.
- Decode domains. if this is activated, the domain types will be inserted as comments just after domain names. For example:
CREATE TABLE Z (
B BOOL /* INTEGER DEFAULT 0 CHECK (VALUE IN(0,1)) */
- Use CREATE OR ALTER for procedures and triggers: compliant to Firebird 2.x.
- Do not use SET TERM command:
SET TERMis not necessary for scripts executed by IBExpert/IBEScript but may be necessary when working with other tools.
- Use SEQUENCE instead of GENERATOR: compliant to Firebird 2.x.
- Always quote identifiers:
- Date Format: this can be used to specify the date format and datetime format, with options to use an ANSI prefix for date/time values and to set the specified format as default.
- Remove trailing spaces and control characters from string values
- Extract Blobs: IBExpert cannot "read" blobs; it therefore uses a detour to make a reference to a separate database file containing such blobs. Only IBExpert has been able to do this so far. Other products only extract the definition of the blobs, and not the contents themselves. This option is only offered when you export data/metadata into a file. It is invisible when the Script Executive and Clipboard options have been selected.
- Use REINSERT instead of repeated INSERTs: uses the IBExpert
REINSERTcommand, to insert multiple data records.
- Insert 'COMMIT WORK' after number of (records): this option defines the number of records before inserting the
COMMITstatement into the script. The default value is
500, i.e. 500
INSERTcommands are performed and then committed.
- Extract privileges: for all or only for selected objects.
Finally, if wished, use the toolbar icon Save Configuration to File or the key combination [Ctrl + S] to save this configuration as a template for future use. The next time round, the template can be quickly and easily loaded using the Load Configuration icon (or [Ctrl + L]); the template specifications amended if necessary, and the extract started.
Once all objects and data have been selected, and all options specified, the extract can be started using the green > button or [F9].
The Output page displays the IBExpert log during the extraction. Following completion, if a file was specified, IBExpert asks whether the file should be loaded into the script editor.
If the Script Executive has been specified as the output option, the Script Executive is automatically loaded. The object tree on the left-hand side can be opened to display the individual statements relating to an object. By clicking on any of these statements, IBExpert springs to that part of SQL code, which is displayed on the right:
The statements display what IBExpert is doing and in which order. The script displays the creation of all objects, and then the subsequent insertion of the content data, using the
Extract Metadata is a great tool, and can be useful in a variety of situations. For example, it can be used to perform an incremental backup, should it be necessary for example, to back up just one single table every evening.
Any number of configurations may be saved in various formats:
- Metadata extract configuration (*.mec): this allows you to quickly and simply load a specified configuration in the Extract Metadata dialog.
- IBEBlock (*.ibeblock): this enables you to save the current settings as an
EXECUTE STATEMENTstatement. IBExpert creates a valid IBEBlock with the
ibec_ExtractMetadatafunction, which may be used later in scripts.
- All files (*.*).
Simply specify the directory and file name you wish to extract to, and then customize the Extract Metadata block on the IBEBlock page as required and save to file. This function offers a quick and simple solution for a number of otherwise cumbersome tasks, such as generating foreign language versions of your database, subsequent alteration of the character set, alternative backup and restore or incremental backups. Please refer to
ibec_ExtractMetadata for further information.
IBExpert uses a special extension of script language that enables it to extract objects' descriptions into script and then execute one using the Script Executive.
IBExpert uses an original mechanism to extract values of blob fields into a script. This allows you to store an entire database (metadata and data) in script files and execute these scripts with IBExpert. The following small example illustrates our method to extract blob values.
For example, a database has a table named
CREATE TABLE COMMENTS ( COMMENT_ID INTEGER NOT NULL PRIMARY KEY, COMMENT_TEXT BLOB SUBTYPE TEXT);
This table has three records:
If the Extract BLOBs option is unchecked you will get the following script:
CREATE TABLE COMMENTS ( COMMENT_ID INTEGER NOT NULL PRIMARY KEY, COMMENT_TEXT BLOB SUBTYPE TEXT); INSERT INTO COMMENTS (COMMENT_ID) VALUES (1); INSERT INTO COMMENTS (COMMENT_ID) VALUES (2); INSERT INTO COMMENTS (COMMENT_ID) VALUES (3);
... and, of course, you will lose your comments if you restore your database from this script.
But if the Extract BLOBs option is checked, IBExpert will generate a somewhat different script:
SET BLOBFILE 'C:\MY_SCRIPTS\RESULT.LOB'; CREATE TABLE COMMENTS ( COMMENT_ID INTEGER NOT NULL PRIMARY KEY, COMMENT_TEXT BLOB SUBTYPE TEXT); INSERT INTO COMMENTS (COMMENT_ID, COMMENT_TEXT) VALUES (1, h0000000_0000000D); INSERT INTO COMMENTS (COMMENT_ID, COMMENT_TEXT) VALUES (2, NULL); INSERT INTO COMMENTS (COMMENT_ID, COMMENT_TEXT) VALUES (3, h000000D_0000000F);
IBExpert also generates a special file with the extension
LOB, where blob values are stored. In the current example
result.lob will be 28 bytes long and its contents will be
First commentAnother comment.
SET BLOBFILE is a special extension of script language that allows the IBExpert Script Executive to execute scripts containing references to blob field values.
There are two methods to obtain the current generator values in a database. The first is using the IBExpert menu item Tools / Extract Metadata, where there is an option to set generators on the Options page.
In Firebird this can also be done using a stored procedure:
CREATE PROCEDURE GET_GENERATORS RETURNS ( GENERATOR_NAME CHAR(31), CURR_VAL BIGINT) AS declare variable sql varchar(100); BEGIN FOR select r.rdb$generator_name generator_name, cast(0 as bigint) curr_val from rdb$generators r where r.rdb$generator_name not containing '$' INTO :GENERATOR_NAME, :CURR_VAL DO BEGIN sql='Select gen_id('||GENERATOR_NAME||',0) from rdb$database'; execute statement :sql into :curr_val; SUSPEND; END END
The Firebird core package has no dump tool. So it's important to analyze your metadata scripts to trace what started to go wrong, where and when.
If your backups are failing regularly on the same table(s) due to irreparable data damage, and you've not been able to solve the problem using
GFIX, this is an alternative way to save at least all remaining healthy data and the database itself.
First attempt to restrict the problem to as few data sets as possible, using the
SELECT command on the table ID field.
- Then use the IBExpert Tools menu item, Extract Metadata. Connect to your database and select all tables for metadata and data.
- Extract to - select separate files from the drop-down list.
- Extract all objects and data from all tables.
- If any error occurs on specific data, add a
WHEREcondition for the table concerned. For example, click on the table name in the right-hand column of Selected Objects and add your
WHEREclause to exclude the range of damaged data, e.g.
WHERE ID>1000 AND ID<1100.
- Generate your script (green arrow icon or [F9]).
- Delete the original database file.
- If required, add the missing data as far as possible from an older extract file or backup copy of the database.
runme.all.bat(don't forget to add the path to
IBEScript.exe. This starts IBExpert's
runme.all.sql, which loads the files from
IBE$Start, then the data files and finally
This will create a new database with all objects and data, even including blob data.
IBE$Start runs the operations such as creating the database and metadata. Tables are generated, without any primary keys, foreign keys, constraints, triggers, etc. This is followed by a series of insert commands, using the IBEBlock function,
IBE$Finish then inserts all primary keys, foreign keys etc.
You can, of course carry all this out at script level, using
This method can also be used if you wish to make an alteration to an existing database, for example, update from SQL dialect 1 to 3, or specify a character set if no default character set was specified at the time of database creation. For example, to alter the default character set from
ISO8859_1, simply open
CHARACTER SET NONE and replace with
CHARACTER SET ISO8859_1, and then run the
runme.all.sql script, as mentioned above.