The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,740 other followers

Archive for the ‘Firebird’ Category

Firebird: RDB$RELATIONS.RDB$RELATION_TYPE and when it can be null

Posted by jpluimers on 2019/09/11

The RDB$RELATIONS.RDB$RELATION_TYPE was introduced in Firebird 2.1 with these enumeration values:

enum rel_t {
    rel_persistent = 0,
    rel_view = 1,
    rel_external = 2,
    rel_virtual = 3,
    rel_global_temp_preserve = 4,
    rel_global_temp_delete = 5
};

You should access it assuming NULL means zero (0), so you better treat it using [WayBackCOALESCE() like COALESCE(RDB$RELATIONS.RDB$RELATION_TYPE, 0).

Background information:

–jeroen

Posted in Database Development, Development, Firebird | Leave a Comment »

Firebird 3.x SQL Language Changes

Posted by jpluimers on 2019/09/05

Since I likely need these [WayBack] SQL Language Changes in the future (some breaking Firebird 2.x habits).

The odd thing is that I got there looking for “no column name specified for column number 1 in derived table” when doing some weird CTE query things in Firebird 2.5x

–jeroen

Posted in Database Development, Development, Firebird | Leave a Comment »

Counting rows for all tables in Firebird

Posted by jpluimers on 2019/08/28

Sometimes you want to count data in all tables on a database to get a feel for the orders of magnitude, but you cannot use the approximated example in How to speed up Count(*) in Interbase/Firebird – Stack Overflow as those require primary keys.

Then the below script can help: it generates the right queries as a union all ordering by the count in the tables.

Example generated code on C:\Program Files (x86)\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB:

SQL
--------------------------------------------------------------------------------------------------
select 128 as id, 'COUNTRY' as name, count(*) from "COUNTRY" union all
select 129 as id, 'JOB' as name, count(*) from "JOB" union all
select 130 as id, 'DEPARTMENT' as name, count(*) from "DEPARTMENT" union all
select 131 as id, 'EMPLOYEE' as name, count(*) from "EMPLOYEE" union all
select 133 as id, 'PROJECT' as name, count(*) from "PROJECT" union all
select 134 as id, 'EMPLOYEE_PROJECT' as name, count(*) from "EMPLOYEE_PROJECT" union all
select 135 as id, 'PROJ_DEPT_BUDGET' as name, count(*) from "PROJ_DEPT_BUDGET" union all
select 136 as id, 'SALARY_HISTORY' as name, count(*) from "SALARY_HISTORY" union all
select 137 as id, 'CUSTOMER' as name, count(*) from "CUSTOMER" union all
select 138 as id, 'SALES' as name, count(*) from "SALES" order by 3

Example output on C:\Program Files (x86)\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB:

ID NAME COUNT
133 PROJECT 6
128 COUNTRY 14
137 CUSTOMER 15
130 DEPARTMENT 21
135 PROJ_DEPT_BUDGET 24
134 EMPLOYEE_PROJECT 28
129 JOB 31
138 SALES 33
131 EMPLOYEE 42
136 SALARY_HISTORY 49

The generation code below uses a few tricks:

The rank helps me distinguish the last row (for the order by 3 clause) and other rows (for the union all clauses).

Generation code:

with tables(id, name) as (
    -- http://www.firebirdfaq.org/faq376/
    select r.RDB$RELATION_ID as id, trim(r.RDB$RELATION_NAME) as name
    from RDB$RELATIONS r
    where 1=1
      and (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0)
      and r.RDB$VIEW_BLR is null
    order by 1
  ),
  ranked_tables(id, rank, name) as ( 
    -- http://www.firebirdfaq.org/faq343/
    select tables.ID, count(others.id)+1 as "rank", tables.NAME
    from tables
    left join tables others on others.ID < tables.ID
    group by "ID", "NAME"
    order by "rank"
  ),
  parts(id, rank, name, suffix) as (
    select ranked_tables.id, 
            ranked_tables.rank,
            ranked_tables.name,
      case
        when ranked_tables.rank = 1 then 'union all' -- first 
        when ranked_tables.rank = (select count(*) from tables) then 'order by 3' --last 
        else 'union all' -- middle
      end as suffix      
    from ranked_tables 
  ) 
select -- parts.id, parts.rank, parts.name, parts.suffix,
       'select '||parts.id||' as id, '''||parts.name||''' as name, count(*) from "'||parts.name||'" '||parts.suffix||'' as SQL
from parts
order by parts.id

–jeroen

Read the rest of this entry »

Posted in Database Development, Development, Firebird, Software Development, SQL | Leave a Comment »

Firebird CASE construct

Posted by jpluimers on 2019/06/12

Reminder to self: sort out why a while ago I had trouble with a nested [WayBackCASE construct causing parsing anomalies in 2.5.8.

–jeroen

Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »

Testing for NULL and equality in practice

Posted by jpluimers on 2019/05/30

Great article: Testing for NULL and equality in practice [WayBack]

Most important table piece I learned:

 Testing (in)equality of A and B in different Firebird versions

Test type Firebird version
<= 1.5.x >= 2.0
Equality
A = B or A is null and B is null
A is not distinct from B
Inequality
A <> B
or A is null and B is not null
or A is not null and B is null
A is distinct from B

–jeroen

Posted in Database Development, Development, Firebird, SQL | Leave a Comment »

 
%d bloggers like this: