INFORMATION_SCHEMA [CORE737]

[firebird-automations]

Submitted by: german666 (german666)

Votes: 7

SFID: 1468113#⁠ Submitted By: german666

SQL STANDARD INFORMATION_SCHEMA

THANKS.

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 10761 ] => Firebird [ 15164 ]

[firebird-automations]

Commented by: Pabloj (pabloj_sourceforge)

Could be provided as a set of views

[firebird-automations]

Commented by: Ivan (patuljak)

please support all statistics in INFORMATION_SCHEMA

size of tables, size of index etc... on easy way.

[simonaubertbd]

Hello According to https://en.wikipedia.org/wiki/Information_schema "In relational databases, the information schema (information_schema) is an ANSI-standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database"

It appears Firebird does not support information_schema while it would be great to follow the norm. i would add, it does not require a lot of job to produce an information_schema schema and then some views in it.

Best regards,

Simon

[aafemt]

But adding support of schemas themselves is quite a lot of work.

[simonaubertbd]

@aafemt Thanks for your answer, guess I'm not the only one working on a saturday evening ^^. I thought you had most in RDB$ and then it would be a matter of a few hours to build the views based on RDB$. Am I wrong ? O_o

Best regards,

Simon

[aafemt]

I repeat: the views is not a problem. Lack of schemas is.

Reusing of RDB.RELATIONS.RDB$OWNER as a schema name is quite reasonable decision. Adding syntax <schema>.<table> to the parser is also quite straightforward. Architectural problem of backward-compatibility is harder. Single-level names must be somehow resolved consistently in backward-compatible way. Definitely we don't want to use Oracle way "one user - one schema" so some way to define "current" schema for session must be invented with some sane default value (like "public" for example). What the standard is talking about it? SQL security considerations add another level of complexity.

Feel free to prepare RFC for all that and share it in devel mail-list.

[mrotteveel]

@aafemt [..] I thought you had most in RDB$ and then it would be a matter of a few hours to build the views based on RDB$. Am I wrong ? O_o

The problem is not with adding the views themselves (I believe someone in the past already made a script to define them), the problem is that INFORMATION_SCHEMA is supposed to be a separate schema in the database, and Firebird doesn't have schemas. Adding this now without schema support in Firebird would introduce backward compatibility issues once schemas are added.

And no, RDB$ is not a schema, it is simply a name prefix.

[simonaubertbd]

@aafemt @mrotteveel Thanks for the clarification, I had misunderstood it at first. Get it.