How to interpret the RDB$FORMATS query result

user_532675 0
Viewed 202

To get the history of a table schema changes I ran the query:

select CAST(SUBSTRING(f.rdb$descriptor FROM 1 FOR 32000) AS VARCHAR(32000)) log 
from rdb$formats f
join rdb$relations r on r.rdb$relation_id = f.rdb$relation_id
where r.rdb$relation_name = 'MY_TABLE_NAME'

Documentation states the following:

RDB$DESCRIPTOR | BLOB FORMAT | Stores column names and data attributes as BLOB, as they were at the time the format record was created

Below is the result of the query:

LOG                                                  TABLE FORMAT ID
-------------------------------------------------    ----------------       
4: type=9 (LONG) length=4 sub_type=0 flags=0x0       15
8: type=9 (LONG) length=4 sub_type=0 flags=0x0   
12: type=14 (DATE) length=4 sub_type=0 flags=0x0 
16: type=9 (LONG) length=4 sub_type=0 flags=0x0  
20: type=9 (LONG) length=4 sub_type=0 flags=0x0  
24 <-- probably truncated?
-------------------------------------------------    ----------------
4: type=9 (LONG) length=4 sub_type=0 flags=0x0       16
8: type=3 (VARCHAR) length=12 sub_type=52 flags=0x0
20: type=14 (DATE) length=4 sub_type=0 flags=0x0
24: type=9 (LONG) length=4 sub_type=0 flags=0x0
28: type=9 (LONG) length=4 sub_type=0 flags=0x0

There are 28 rows of events in total for the table. But I can't understand the meaning behind the numbers 4, 8, 12, 16, 20, 24, 28. Okay, let's issue the following query:

SELECT
    RF.RDB$FIELD_POSITION,
    RF.RDB$FIELD_ID,
    F.RDB$FIELD_TYPE,
    F.RDB$FIELD_SUB_TYPE
FROM RDB$RELATION_FIELDS RF
JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
WHERE RF.RDB$RELATION_NAME = 'MY_TABLE_NAME'
ORDER BY RF.RDB$FIELD_POSITION;

And here are the results:

the results

As you can see I have only 22 columns, neither position nor id can match the 24/28 keys from the log above.

Another finding is that there's a type=3 (VARCHAR) with sub_type=52 in the log whereas 37 is the VARCHAR's code.

What is happening? How do I interpret this?

firebird2.5 firebird database
1 Answers

I can't understand the meaning behind the numbers 4, 8, 12, 16, 20, 24, 28

Those are byte pointer offsets in the unpacked memory buffer.

All your Format=15 rows have the same "length=4" column.
And that is exactly the differences between "4, 8, 12, 16, 20, 24"

In Format=16 rows the lengths are 4, 12, 4, 4, 4
And those are matching the gaps between the offsets: 4, 8, 20, 24, 28

  • Pointer 1 + Offset 1 = Pointer 2
  • Pointer 2 + Offset 2 = Pointer 3
  • et cetera

If you need to go low-level then read the low-level documentation:

  • "API Guide" - https://www.firebirdsql.org/en/reference-manuals/
  • "Firebird Internals (Work In Progress)" - same link as above
  • c:\Program Files\Firebird\Firebird_2_1\include\ibase.h - dtype_XXX and related constants
  • FLOSS low-level libraries parsing in-memory structures, like IB++ in C++ or UIB in Pascal. Those are parsing those types and subtypes to interprete the raw in-memory data in meaningful ways.

select CAST(SUBSTRING....

Since you mentioned IBExpert, I suggest you to look inside RDB$Formats.rdb$descriptor values using its built-in BLOB viewer. Your log misses one parameter there, important for numeric fields. Below is the dump from some one table.

Type=16 Scale=0 Length=8 Subtype=0 Flags=0 Offset=8
Type=17 Scale=4 Length=8 Subtype=1 Flags=0 Offset=16

whereas 37 is the VARCHAR's code.

Again, read the sources - ibase.h

   #define blr_varying             (unsigned char)37
   #define blr_varying2            (unsigned char)38

BLR stands for Binary Language Representation - it is internal Firebird's bytecode, its private semi-compiled "virtual machine". I sincerely do not think you really want to go THAT far into low level implementation details.

UPDATE: The "whereas 37 is the VARCHAR's code" actually is outright documented at the corresponding table description:
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref-appx04-fields.html

user_9763910
Related