On 06-10-2021 17:32, Roman Simakov wrote:
3. DROP TABLESPACE <TS NAME> [INCLUDING CONTENTS]
I think using {CASCADE | RESTRICT} instead of [INCLUDING CONTENTS] would
be better, as that fits with similar definitions in the SQL standard
(e.g. 11.2 <drop schema statement>, and Feature F032 "CASCADE drop
behaviour").
That is, you always have to specify either CASCADE (delete everything
contained in it), or RESTRICT (only delete if empty).
However, the SQL standard also specifies that all objects then need to
be dropped with CASCADE (so dependents are dropped as well), and
Firebird doesn't provide such feature (Feature F032 "CASCADE drop
behaviour") at the moment, and it is probably necessary for this to work
correctly, otherwise objects with dependents in other tablespaces will
result in the drop failing anyway.
Personally, I could live with not providing such feature for now, and
instead requiring the tablespace to be empty before dropping it.
If the tablespace contains some database objects the behaviour depends
on INCLUDING CONTENTS clause. if it is specified all database objects
in the tablespace will be dropped as well. Otherwise there will be an
error.
4. CREATE TABLE …
TABLESPACE <TS NAME>
5. ALTER TABLE <TABLE NAME> ALTER TABLESPACE <TS NAME>
Data of the table will be moved to the specified tablespace.
6. ALTER TABLE <TABLE NAME> DROP TABLESPACE
Data of the table will be moved to the main database.
Does this require a separate DROP TABLESPACE option? The same could be
achieved with ALTER TABLE <TABLE NAME> ALTER TABLESPACE ...
where ... might be DEFAULT, MAIN or the explicit name of the main
tablespace (assuming it has a name).
Using ALTER TABLESPACE also makes sense, because that is what's actually
happening.
7. CREATE INDEX … TABLESPACE {<TS NAME> | DEFAULT}
The index will be created:
- in the main database file if TABLESPACE is omitted.
- in the table tablespace for TABLESPACE DEFAULT.
- or in the specified tablespace.
8. ALTER INDEX <INDEX NAME> ALTER TABLESPACE <TS NAME>
Data of the index will be moved to the specified tablespace.
9. ALTER INDEX <INDEX NAME> DROP TABLESPACE
Data of the index will be moved to the main database.
How will this work for indexes backing constraints?
ODS CHANGES
=============
A new table RDB$TABLESPACES:
RDB$TABLESPACE_ID - SMALLINT
RDB$TABLESPACE_NAME - CHAR (63)
RDB$SECURITY_CLASS - CHAR (63)
RDB$SYSTEM_FLAG - SMALLINT
RDB$DESCRIPTION - BLOB SUBTYPE TEXT SEGMENT SIZE 80
RDB$OWNER_NAME - CHAR (63)
RDB$FILE_NAME - VARCHAR (255)
RDB$OFFLINE - SMALLINT
RDB$READ_ONLY - SMALLINT
A new field in RDB$INDICES:
RDB$TABLESPACE_NAME - CHAR (63)
A new field in RDB$RELATION_FIELDS:
RDB$TABLESPACE_NAME - CHAR (63)
New fields in RDB$RELATIONS:
RDB$TABLESPACE_NAME - CHAR (63)
RDB$POINTER_PAGE - INTEGER
RDB$ROOT_PAGE - INTEGER
UTILITIES
========
Logical backup
--------------------
gbak -b works as usual for now. It gets data from a database
transparently working with tablespaces.
Logical restore
--------------------
gbak -c -TABLESPACE_MAP(PING_FILE) /path/to/file/map/tablespaces
The option TABLESPACE_MAP(PING_FILE) specifies a path to the file
which maps tablespace names on file names. For example,
TS1 /path/to/tablespace1.dat
TS2 /path/to/tablespace2.dat
It allows you to restore tablespace contents to new places.
If the option is not specified gbak will use old locations for every tablespace.
I'd prefer if there is (also) an option to define the mapping on the
commandline without requiring users to first create the mapping file.
I also think that using the old locations should be an explicit option,
so if a database backup has tablespaces, and no option is specified to
define the tablespace mapping, or the mapping is incomplete, restore
should fail *before it started to write anything*.
As an aside, we really need to look at how we handle commandline
options, because having really long commandline options with no simple
one or two character abbreviations is not very friendly IMHO. Maybe we
should consider switching to `-v` vs `--verbose-names` convention (with
backwards compatibility for existing options).
SOME DETAILS
=============
pag_header in every tablespace is reserved and may be replaced by a
new page type.
I think it should be a separate page type.
pag_scns and pag_pip are located in every tablespace
pag_root is located in the tablespace where a table is located
It's possible to create up to 253 tablespaces.
==================END=========================
First of all, please let me know whether you agree or not with SYNTAX
and ODS parts. Other opinions and suggestions are welcome as well.
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
