Schemas in Firebird - SYSTEM schema, migration from old versions and plugin objects

[Adriano dos Santos Fernandes]

Hi!

There will be SYSTEM schema where system objects will live.

My idea is to not block this schema for user and treat system objects
like now, i.e., they cannot be changed but new objects will be allowed
in system schema.

Migration from old versions to separate schemas (including system schema
plus single user schema) could be complicated, so I'm inclined to make
gbak restore any objects from non-schema backup into SYSTEM schema.

There is also PLG$ objects, like security and profiler. Same thing here,
they will be created in SYSTEM schema.

Please note that if there were no backward compatibility involved, my
opinions on this probably would be different. But I think this is enough
compromise for an initial version, and tools to "move" from SYSTEM
schema could be introduced later.


Adriano

[Mark Rotteveel]

On 01/06/2024 04:02, Adriano dos Santos Fernandes wrote:
> My idea is to not block this schema for user and treat system objects
> like now, i.e., they cannot be changed but new objects will be allowed
> in system schema.

I'm not sure I like that. Currently we have no schemas, so when moving
to schemas, we should close off unnecessary stuff I think. Allowing
creation of indexes and triggers on system tables (and as a result,
placing them in the SYSTEM schema), sounds OKish to me.

However, I don't think that should mean users should have free reign to
just create objects in SYSTEM. That sounds like a recipe for disaster,
and/or SYSTEM becoming the de-facto default schema.

> Migration from old versions to separate schemas (including system schema
> plus single user schema) could be complicated, so I'm inclined to make
> gbak restore any objects from non-schema backup into SYSTEM schema.

What would be the problem with migrating things into a default user
schema unless they are a system table, or a user-created object on a
system table (e.g. a trigger or index)?

> There is also PLG$ objects, like security and profiler. Same thing here,
> they will be created in SYSTEM schema.

Have you considered a separate schema for plugins?

> Please note that if there were no backward compatibility involved, my
> opinions on this probably would be different. But I think this is enough
> compromise for an initial version, and tools to "move" from SYSTEM
> schema could be introduced later.

Specifically what backwards compatibility issues would necessitate
having gbak create objects in SYSTEM?

Mark
--
Mark Rotteveel

[Adriano dos Santos Fernandes]

On 01/06/2024 03:47, 'Mark Rotteveel' via firebird-devel wrote:
> That sounds like a recipe for disaster,

Only if you consider current Firebird as a disaster.

> and/or SYSTEM becoming the de-facto default schema.
>

For existing databases, I'm afraid yes, but not for new ones.

>> Migration from old versions to separate schemas (including system schema
>> plus single user schema) could be complicated, so I'm inclined to make
>> gbak restore any objects from non-schema backup into SYSTEM schema.
>
> What would be the problem with migrating things into a default user
> schema unless they are a system table, or a user-created object on a
> system table (e.g. a trigger or index)?
>

It would need to re-encode BLR when an user object references a system one.

That is complicated alone, but it will make downgrades impossible.

>> There is also PLG$ objects, like security and profiler. Same thing here,
>> they will be created in SYSTEM schema.
>
> Have you considered a separate schema for plugins?
>

It's the same and even worse. You know that team decided my intentions
to improve system GDML queries where not necessary and that GPRE is good
enough.

Team also decided that GPRE does not need to be improved for schemas.

So let's live with the decisions consequences.

So inside Firebird code, GPRE-generated modules should be bound to the
SYSTEM schema.


Adriano

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 01.06.2024 23:41:
> It's the same and even worse. You know that team decided my intentions
> to improve system GDML queries where not necessary and that GPRE is good
> enough.
>
> Team also decided that GPRE does not need to be improved for schemas.
>
> So let's live with the decisions consequences.
>
> So inside Firebird code, GPRE-generated modules should be bound to the
> SYSTEM schema.

Not SYSTEM, but DEFAULT or PUBLIC, please.

--
WBR, SD.

[Adriano dos Santos Fernandes]

SYSTEM.


Adriano

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 01.06.2024 23:49:
>>> So inside Firebird code, GPRE-generated modules should be bound to the
>>> SYSTEM schema.
>>   Not SYSTEM, but DEFAULT or PUBLIC, please.
>>
> SYSTEM.

Is there a technical reason for that?

--
WBR, SD.

[Mark Rotteveel]

On 01/06/2024 23:41, Adriano dos Santos Fernandes wrote:
> On 01/06/2024 03:47, 'Mark Rotteveel' via firebird-devel wrote:
>> That sounds like a recipe for disaster,
>
> Only if you consider current Firebird as a disaster.

I mean that this behaviour will result in upgraded databases using
SYSTEM as a normal schema, instead of reserving it for system objects.
And because normal objects will be in there, people will continue to add
new objects in there, and likely will do the same with _new_ database,
because their other databases do the same.

That is a situation I consider a disaster. Because then you might just
as well not have a SYSTEM schema, or schemas at all.

>> and/or SYSTEM becoming the de-facto default schema.
>
> For existing databases, I'm afraid yes, but not for new ones.
>
>>> Migration from old versions to separate schemas (including system schema
>>> plus single user schema) could be complicated, so I'm inclined to make
>>> gbak restore any objects from non-schema backup into SYSTEM schema.
>>
>> What would be the problem with migrating things into a default user
>> schema unless they are a system table, or a user-created object on a
>> system table (e.g. a trigger or index)?
>>
>
> It would need to re-encode BLR when an user object references a system one.
>
> That is complicated alone, but it will make downgrades impossible.

To be honest, I think that is a necessary pain to avoid creating a
confusing mess of the SYSTEM schema.

On the other hand, I recognize that is easy for me to say from the
sidelines.

Mark
--
Mark Rotteveel

[Денис Симонов]

Why not make the default schema PUBLIC, like Postgresql does?

Let all system tables be located in the SYSTEM schema. There are many ways to solve backward compatibility problems, for example making all current RDB$ tables views on the SYSTEM table, or aliases of objects from the SYSTEM schema.

воскресенье, 2 июня 2024 г. в 12:13:04 UTC+3, Mark Rotteveel:

[Денис Симонов]

Technically, the USE SCHEMA <some> statement will automatically disable the ability to read metadata in any legacy application, so to solve backward compatibility problems we need to come up with something better than just placing SYSTEM objects in the default schema. 

For example, you can make sure that system objects are visible in any of the newly created schemas. What other opinions are there on this matter?

понедельник, 3 июня 2024 г. в 17:11:12 UTC+3, Денис Симонов:

[Dimitry Sibiryakov]

Денис Симонов wrote 03.06.2024 16:32:
> Technically, the USE SCHEMA <some> statement will automatically disable the
> ability to read metadata in any legacy application, so to solve backward
> compatibility problems we need to come up with something better than just
> placing SYSTEM objects in the default schema.
> For example, you can make sure that system objects are visible in any of the
> newly created schemas. What other opinions are there on this matter?

In previous discussion we agreed that there must be support for PATH for
unqualified identifier.
Search for existing objects can follow this rule:

1) SYSTEM schema
2) Defined schema PATH (or USE SCHEMA as you suggest)
3) PUBLIC schema

Creation of new objects with unqualified identifier can use different rules
that may be one of these:

1) Current schema
2) Default user schema
3) DEFAULT schema

Thus no creation of user objects in SYSTEM schema allowed while system
objects are freely available to any old application (including GDML ones).

New plugins are strongly recommended to create their objects in own schema.
Old plugins may have their objects created in user schema and adapting to the
new rules is their problem, not ours.

--
WBR, SD.

[Adriano dos Santos Fernandes]

On 03/06/2024 11:11, Денис Симонов wrote:
> Why not make the default schema PUBLIC, like Postgresql does?
>

Different default schema for new objects can be done, specially in new
databases.

But this is not topic of this thread. This is about migration of backups
done in previous versions.

> Let all system tables be located in the SYSTEM schema. There are many
> ways to solve backward compatibility problems, for example making all
> current RDB$ tables views on the SYSTEM table, or aliases of objects
> from the SYSTEM schema.
>

First, we're not going to create aliases (synonyms).

Second, half made solutions will not make good things.

It's not about system tables that can be transformed to views only.

There is domains, system domains that can be used when user's views
references system views, system packages. etc.


Adriano

[Adriano dos Santos Fernandes]

On 03/06/2024 11:46, 'Dimitry Sibiryakov' via firebird-devel wrote:
> Денис Симонов wrote 03.06.2024 16:32:
>> Technically, the USE SCHEMA <some> statement will automatically
>> disable the ability to read metadata in any legacy application, so to
>> solve backward compatibility problems we need to come up with
>> something better than just placing SYSTEM objects in the default schema.
>> For example, you can make sure that system objects are visible in any
>> of the newly created schemas. What other opinions are there on this
>> matter?
>
>   In previous discussion we agreed that there must be support for PATH
> for unqualified identifier.

Paths should be used in prepare (and hence BLR generation) time.

Paths does not solve problem of BLR not referencing schemas.

Current BLR verbs that uses object names should be used only in the case
of an object referencing another object in the same schema. References
to others schemas should use new verbs with explicit schema name.

That's why separation of system/user schema requires BLR re-encoding.

Again, I'm not against it. But initial, very incomplete, full of bugs
patch of schemas is already 27K lines, touching many files. Add to that
that we have concurrent branch (shared metadata) also touching many and
same files, we have merge disaster for integration of this.

So any non-fundamental thing should IMO be deferred to after initial
merge. That could mean even before the release, but maybe not.


Adriano

[Mark Rotteveel]

On 03/06/2024 16:11, Денис Симонов wrote:
> Why not make the default schema PUBLIC, like Postgresql does?
>
> Let all system tables be located in the SYSTEM schema. There are many
> ways to solve backward compatibility problems, for example making all
> current RDB$ tables views on the SYSTEM table, or aliases of objects
> from the SYSTEM schema.

In PostgreSQL, the system tables do not reside in public, but in pg_catalog.

Mark
--
Mark Rotteveel

[Денис Симонов]

I'm not saying that system objects should be located in PUBLIC. On the contrary, they should be in SYSTEM. But it is better to make the default scheme PUBLIC. And yes, system objects must be visible in any schema as PATH/SYSNONIM, otherwise, when switching the schema via the USE SCHEMA statement, any Legacy application will immediately stop working, since the drivers will not be able to read the metadata.

But if the current implementation is only preliminary in order to generally debug the database schemas, then I see nothing wrong with what Adriano proposed.

вторник, 4 июня 2024 г. в 10:11:24 UTC+3, Mark Rotteveel:

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 03.06.2024 23:10:
> Again, I'm not against it. But initial, very incomplete, full of bugs
> patch of schemas is already 27K lines, touching many files. Add to that
> that we have concurrent branch (shared metadata) also touching many and
> same files, we have merge disaster for integration of this.

That's why big things must be done by little logically complete steps that
can be implemented, reviewed and tested independently.

--
WBR, SD.