Firebird 4 - Column name - Size limit

[Hugo Eyng]

Hello Friends.

Is there a size limit for a column name.

I already read about it and I found out that the size changed from 31 bytes in the FB3 to 63 bytes in FB4.

Is it true?

[Tomasz Tyrakowski]

Yes, see https://github.com/FirebirdSQL/firebird/issues/1124.
Every time a question about the maximum identifier length pops up, I
can't help but wonder (and finally gathered my courage to ask): what's a
real life scenario in which it's necessary to have identifiers
consisting of 30 characters or more? The queries must look like a total
mess. Can you please share a bit about your motivation, 'cause I've
never actually considered the name length limit to be a problem in my
20+ years of dealing with Interbase/Firebird. Unless you try to encode
something into the names, in which case it's data and belongs to the data ;)
But, as always, I may be completely wrong or ignorant (always ready to
learn, though ;) ).

regards
Tomasz

[Karol Bieniaszewski]

For tables it is seldom but can happen too

but think about descriptive name of stored procedures or triggers.

 

regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/d0e76de8-9db7-3821-c5bd-827570cc717d%40sol-system.pl.

 

[Dalton Calford]

When porting, very often we are coming from databases that use schema, even recursive schema.

For example, some accounting databases, use the same table structure/names but use different schema names to differentiate between companies  such as company1.GeneralLedger etc.
Then they would perform cross company queries for consolidated reports.   
With Oracle or DB2 you can often see recursive schema and the use of synonyms to separate out various name/query spaces with default schema as part of the fundamental part of the design.
To support such structures with Firebird either requires simulating schema with quoted identifier names that have periods in them or a series of stored procedures that query cross database.
Also, for queries, if you have a long identifier, used as embedded documentation, you use aliases in the query itself as in select a.* from myreallylongtablenamethatexplainsitsownpurpose a so the queries are not really impacted and if used properly are easier to debug.
At this point, having a size of identifier that matches or exceeds the primary competition to ease porting, is the highest priority, but, in truth, we need proper recursive schema namespaces.
 

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4HqFHy5pq2z2K24Tg%40smtp.poczta.onet.pl.

[Hugo Eyng | Datamais]

Dear Tomasz.

Thank you for your valuable, kind and funny answer.

I am used to creating short names for objects.

Usually something like SP_0001, SP_0002 (for Stored Procedure), TG_0001, TG_0002 (for Trigger), TB0001, TB0002 (for Table), and so on. Column names XX_UP_TO_8_CHARS where XX are two letters that indicates kinds of use (NM = names, VL = values, NR = numbers - counters, ID = identificators)  and "UP_TO_8_CHARS" shortly describe a name (order, person. ID_ORDER, NM_PERSON).

The most used objects are easily memorized by the Dev, and most of the time, long names are not comprehensible for many people (even you write a book about).
 
Also, I use the "description" space to describe the object, where anyone can check the meaning.

My point of view is that, if I want to give the actual meaning to an object, probability I would never have enough characters (regardless how many were available)..

I learned that after programming and creating tables, databases and others, since 1986, or 35 years ago.  (Dataflex, Clipper, Pascal, Cobol).

Well, so why did I ask about the amount of characters that can be used in an object name: because not only my databases are running around the world. So I need to deal with databases that I did not create and eventually, they have column names with 35 characters! So I cannot say why they did so, but I have to deal with that.

Thank you again.

Atenciosamente,

Hugo

[Daniel Rail]

It is true.

And in Firebird 4, I see that the length is configurable, at the database level in databases.conf.  

There are 2 settings to take into account:

- MaxIdentifierByteLength  (multiply the MaxIdentifierCharLength by 4 to get the correct value to place here)

- MaxIdentifierCharLength

The descriptions of these settings are in firebird.conf.  

Daniel

[Hugo Eyng | Datamais]

Hi Daniel.

I think it is configurable but limited to 63 bytes. Isn´t it?

Atenciosamente,

Hugo

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/11582b14-699b-4094-8183-99eba1b39236n%40googlegroups.com.

[Dimitry Sibiryakov]

Hugo Eyng | Datamais wrote 10.11.2021 20:54:
> I think it is configurable but limited to 63 bytes. Isn´t it?

It isn't. 63 _characters_ the limit is.

--
WBR, SD.

[Dimitry Sibiryakov]

Dalton Calford wrote 10.11.2021 20:41:
> such as company1.GeneralLedger etc.

Just for the record: "company1.GeneralLedger" has length 22 characters.

--
WBR, SD.

[Tomasz Tyrakowski]

Hugo, I'm glad you didn't take my response as any kind of criticism,
'cause it wasn't meant to be one. I was genuinely curious about the
scenarios requiring such long names. Combining the answers from you,
Karol and Dalton, the scenarios appear to be:
- maintaining legacy databases, which already have long object names,
- porting schema-qualified tables from different DBMs (that still looks
a bit like encoding data into object names, but never mind ),
- providing descriptive names of stored procedures and triggers.
All three seem plausible enough to me, so thank you all for insights.
Maybe I've been lucky enough so far not to have to deal with those cases.

regards
Tomasz

>> But, as always, I may be completely wrong or ignorant (always ready to

>> learn, though ).

[Kjell Rilbe]

Den 2021-11-10 kl. 19:49, skrev Tomasz Tyrakowski:
> Every time a question about the maximum identifier length pops up, I
> can't help but wonder (and finally gathered my courage to ask): what's
> a real life scenario in which it's necessary to have identifiers
> consisting of 30 characters or more? The queries must look like a
> total mess. Can you please share a bit about your motivation, 'cause
> I've never actually considered the name length limit to be a problem
> in my 20+ years of dealing with Interbase/Firebird. Unless you try to
> encode something into the names, in which case it's data and belongs
> to the data ;)

For me, I often find the old 31 byte limit too restrictive. For one
thing, whenever I use Swedish letters in identifiers (yes, I do use my
own language, actually), these are encoded with 2 byte, which even
further restricts the maximum _character_ length.

I work with a model driven framework that includes a O/R-mapper. For
link classes it will by default generate an identifier like
"SourceClassLinkroleAtSourceEndTargetClassLinkroleAtTargetEnd", where
"LinkroleAt...End" is the name of the association end at that class.
This is needed to distinguish between link classes if the pair of linked
classes has more than one link class. When the generated name, it
creates a three character hash from the trailing part that doesn't fit
and puts that at the end of the identifier.

It's possible to pick your own name for the link class, which I often
do. But if I have one class called "SelectionSystemConfig" and it's
linked to "SMTPSettings", etc. it's not always easy to come up with a
distinctive and descriptive name that fits into 31 byte.

Also, I really prefer a long descriptive name over short ones that I
won't understand after a few months. That's true for both DB identifiers
and C# identifiers, and I would really hope that nobody would consider
naming things in source code like "VR_0001" etc. Debugging or
refactoring such code would be a real horror.

Regards,
Kjell

[Mark Rotteveel]

Yes and no, the maximum size of object names changed from 31 bytes to 63
*characters UTF8*. See the Firebird 4.0 release notes:

https://www.firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb40-ddl-objectnames

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 10-11-2021 19:49, Tomasz Tyrakowski wrote:
> Yes, see https://github.com/FirebirdSQL/firebird/issues/1124.
> Every time a question about the maximum identifier length pops up, I
> can't help but wonder (and finally gathered my courage to ask): what's a
> real life scenario in which it's necessary to have identifiers
> consisting of 30 characters or more? The queries must look like a total
> mess. Can you please share a bit about your motivation, 'cause I've
> never actually considered the name length limit to be a problem in my
> 20+ years of dealing with Interbase/Firebird. Unless you try to encode
> something into the names, in which case it's data and belongs to the
> data ;)
> But, as always, I may be completely wrong or ignorant (always ready to
> learn, though ;) ).

For table names and column names I'd generally agree with you, but those
aren't the only identifiers. The more pressing concern - for me - is
with constraint identifiers. For example, for foreign key constraints, I
prefer to name them to include both the source and target table, and
either the source column name or the role/function of the constraint.
With 31 bytes, you run out of space rather quickly ;)

Mark
--
Mark Rotteveel

[Dalton Calford]

On Wed, 10 Nov 2021 at 15:09, Dimitry Sibiryakov <s...@ibphoenix.com> wrote:

   Just for the record: "company1.GeneralLedger" has length 22 characters.

Like I was saying, recursive schema,  that was a schema ie "company1.GeneralLedger.AccountStructure" as a representative table.  Each module (AP/AR/OE/IC etc) has it's own table structure.   Plus a synonym structure that mirrors the original but in different languages surfaced via views (it was obviously a port on its own but, they have alot of third party tools like excel query the data and needed the language to user consistent).

This is just an example of how some companies have used both schema and long identifier names to suite their purpose.   I have ended up not using Firebird on some earlier projects as it had not implemented longer identifiers at the time, or even now, I am finding myself using it less and less due to a lack of schema support.

I am not saying that using a long identifier is always the best coarse of action, only that sometimes, it is what is needed for the task at hand.  Things like recursive schema and longer identifiers are those tools that when they are needed, they are irreplaceable, but, you don't always need them.

[Tim Crawford]

Exactly what I was going to say, but was too lazy to type.
As I start doing more with constaints and triggers, the naming
gets pretty with the shorter length names.
And triggers and index names etc must be unique across entire database....
Tim