Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Control reported domains of view columns [CORE2143] #2574

Open
firebird-automations opened this issue Oct 24, 2008 · 11 comments
Open

Control reported domains of view columns [CORE2143] #2574

firebird-automations opened this issue Oct 24, 2008 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Cosmin Apreutesei (cosmin_ap2)

sorry if this sounds more like a support question, but I didn't quite manage to find out what exactly is the algorithm by which firebird reports domains for columns of views. It seems like only the most simple views tend to keep the domain names of the underlying table columns. Any group by or sql expression (even if I cast/convert it to a domain) makes the columns loose their types and instead get those RDB$ domains with weird varchar lengths and no charset.

I'd like to be able to control the final reported domain (or datatype + charset etc.) of view columns because my app. code inspects them for formatting etc.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Cosmin,

You are right this does seem like a support question, but it would be appropriate to provide a simple example which would expand on the issue (i.e. I define "X" SQL but when I do "Y" my application sees "Z" results).

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

An example would be any view defined as a select with a group by clause, or an sql expression as selected field (joins are ok).

The result is that, for those fields, I get system-generated domain names in RDB$RELATION_FIELDS.RDB$FIELD_SOURCE, i.e. domains are lost.

Technically, whenever RDB$BASE_FIELD is lost (because your field is an sql expression, aggregate, grouped-by field, etc.), you lose track of domain too. That I understand. Yet _logically_, the domain is preserved in most cases, and when not, you can use CAST/CONVERT to enforce it.

A few examples where select column C looses its domain where it could not (i.e. there's enough information in the query to preserve it):
- select C from T group by C
- select CAST(C1 || ', ' || C2 as MY_DOMAIN) as C from T
- select CAST(LIST(C) as MY_UTF8_VARCHAR_DOMAIN) from T --- this makes a system-generated domain defined as VARCHAR(n*4) with no charset, where MY_UTF8_VARCHAR_DOMAIN is defined as VARCHAR(n) UTF8.

All this is disturbing IBX, FIBPlus, etc. which then create the wrong field types and/or type lengths for datasets.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

This issue also goes for COMPUTED-BY columns, as I found out.

For instance try CREATE TABLE T (C VARCHAR(200) CHARACTER SET UTF8, C2 COMPUTED BY (C)) and you'll get a RDB$xxx system domain defined as VARCHAR(800) on C2.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

When creating a view, the connection charset is used in the new created domains. This doesn't sound good for me.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Looks like the problem I talked is fixed in V2.5.

And ability to "Control reported domains of view columns" is unlike to happen. So please test view domains in 2.5 and report cases that could be improved.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

You kinda' let me hi and dry here. Unlikely because it's to hard to implement, limited application, minor importance, other reason?

Anyway, in hoping to raise some awareness on the issue of why domain inference on views is important:

\- fibplus delphi components \(and I'm sure there are others\) look at domains of dataset fields to automatically create the proper field objects at design time\. for instance it creates a TFIBBooleanField for whatever fields of a domain named BOOLEAN, a very handy option which goes wasted with a lot of my views\.

\- because character set and collation information is missing from the fields metadata, I have to redundantly set it manually in my datasets so my grids will sort right, get the max\. length for edits right etc\., a useless and fastidious exercise\.

\- a lot of input and display information is logically tied to domains, so where as a simple domain\-name\-based metadata catalog could automatically set proper attributes for all of the dataset fields in the whole application, in absence of such information from the database, all we're left is to redundantly reassigning domain names to fields in the application code\.

Those are only a few annoyances from my own experience, but it sure makes the database feel a lot less usable than it should. On the same line of thoughts is CORE1556, where one could implement an error msg. table around the metadata reported from the server.

You may find all these just minor details, but their implications amplifies them a lot in large business applications.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

btw, maybe a better request subject should have been "Proper inference of domains for select columns" because 1) it affects any select, not only views, and 2) I really don't care about controlling, i.e. changing the domain name, I just want it right in the first place.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

> btw, maybe a better request subject should have been "Proper inference of domains for select columns" because 1) it affects any select, not only views, and 2) I really don't care about controlling, i.e. changing the domain name, I just want it right in the first place.

1) Standalone selects doesn't carry a domain, so I believe you're talking about data type only.
2) Please verify if the problems exists in v2.5 - depending on the fix, it will not going to be done/backported to previously releases.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

1) "Standalone selects doesn't carry a domain, so I believe you're talking about data type only."
I know, and it's one of the reasons I'm creating views instead of standalone selects, so improvement-wise, I don't see any reason why this info (the domain name) shouldn't be passed on to stand alone selects also. But that's already a different kind of improvement.

2) So the problem was "When creating a view, the connection charset is used in the new created domains."
That's not what I experience (tested with 2.1.1 with a ODS11.1 db, and with 2.5a1 with a ODS11.2 db). What I get for a UTF8 VARCHAR column is always a RDB$xxx domain with the following NULL fields in RDB$FIELDS: RDB$CHARACTER_LENGTH, RDB$COLLATION_ID, RDB$FIELD_SUBTYPE. The only difference is that now (in 2.5) I do get a correct value in RDB$CHARACTER_SET_ID. However, I don't see the relation of that bug with the general issue of this post.

Many thanks,
Cosmin.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

> So the problem was "When creating a view, the connection charset is used in the new created domains."
> That's not what I experience (tested with 2.1.1 with a ODS11.1 db, and with 2.5a1 with a ODS11.2 db). What I get for a UTF8 VARCHAR column is always a RDB$xxx domain with the following NULL fields in RDB$FIELDS: RDB$CHARACTER_LENGTH, RDB$COLLATION_ID, RDB$FIELD_SUBTYPE. The only difference is that now (in 2.5) I do get a correct value in RDB$CHARACTER_SET_ID. However, I don't see the relation of that bug with the general issue of this post.
>
Inspection with "set sqlda_display on" had showed that the connection charset was used on the view creation in the same manner it's used for client parameters/results description of standalone selects, in HEAD.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants