Articles

How to determine Firebird architecture with SQL query?

Firebird Friday Joke #8 (from https://t.me/firebirdsql)
The script below determines the current architecture of running a Firebird server with SQL.
Normally, you need to take a look at the configuration file firebird.conf or to the settings of Firebird service, to understand, what is the architecture.
However, in our highly-automated test environment, it is necessary to check the architecture with pure SQL. 
The author of the method is Pavel Zotov, Firebird QA and IBSurgeon lead administrator.
For simplicity, code is declared as execute block, but it can be easily turned to the stored procedure. 
The script below is ready for isql.exe.

​set list on;

set term ^;
--create or alter procedure sys_get_fb_arch (
--     a_connect_with_usr varchar(31) default 'SYSDBA'
--    ,a_connect_with_pwd varchar(31) default 'masterkey'
--) returns(
--    fb_arch varchar(50)
--) as

execute block returns( fb_arch varchar(50) )
as
    declare a_connect_with_usr varchar(31) default 'SYSDBA';
    declare a_connect_with_pwd varchar(31) default 'masterkey';
    declare cur_server_pid int;
    declare ext_server_pid int;
    declare att_protocol varchar(255);
    declare v_test_sttm varchar(255);
    declare v_fetches_beg bigint;
    declare v_fetches_end bigint;
begin
    
    -- Aux SP for detect FB architecture.
 
    select a.mon$server_pid, a.mon$remote_protocol
    from mon$attachments a
    where a.mon$attachment_id = current_connection
    into cur_server_pid, att_protocol;
 
    if ( att_protocol is null ) then
        fb_arch = 'Embedded';
    else if ( upper(current_user) = upper('SYSDBA')
              and rdb$get_context('SYSTEM','ENGINE_VERSION') NOT starting with '2.5'
              and exists(select * from mon$attachments a
                         where a.mon$remote_protocol is null
                               and upper(a.mon$user) in ( upper('Cache Writer'), upper('Garbage Collector'))
                        )
            ) then
        fb_arch = 'SuperServer';
    else
        begin
            v_test_sttm =
                'select a.mon$server_pid + 0*(select 1 from rdb$database)'
                ||' from mon$attachments a '
                ||' where a.mon$attachment_id = current_connection';
 
            select i.mon$page_fetches
            from mon$io_stats i
            where i.mon$stat_group = 0  -- db_level
            into v_fetches_beg;
        
            execute statement v_test_sttm
            on external
                 'localhost:' || rdb$get_context('SYSTEM', 'DB_NAME')
            as
                 user a_connect_with_usr
                 password a_connect_with_pwd
                 role left('R' || replace(uuid_to_char(gen_uuid()),'-',''),31)
            into ext_server_pid;
        
            in autonomous transaction do
            select i.mon$page_fetches
            from mon$io_stats i
            where i.mon$stat_group = 0  -- db_level
            into v_fetches_end;
        
            fb_arch = iif( cur_server_pid is distinct from ext_server_pid,
                           'Classic',
                           iif( v_fetches_beg is not distinct from v_fetches_end,
                                'SuperClassic',
                                'SuperServer'
                              )
                         );
        end
 
    fb_arch = trim(fb_arch) || ' ' || rdb$get_context('SYSTEM','ENGINE_VERSION');
 
    suspend;
 
end
 
^ -- sys_get_fb_arch
set term ;^
commit;