11.10.2021 15:17, Roman Simakov wrote:
Here is the second version of the proposal. It's taken into account all agreements we made during discussion and we'll do it in this
way if there are no objections.
================PROPOSAL======================
GOALS
======
1) Extend the current limits on database size
2) Keep non active parts of a database on slow disks (having big volume)
3) Split indices from the database
etc
SYNTAX
=======
Note: *MAIN* - is a name of the basic database file.
Please, use *DEFAULT* for default (main) tablespace at "main" database file.
It is much more consistent with SQL and allows to avoid new unnecessary keyword.
1. *CREATE TABLESPACE <TS NAME> FILE '/path/to/file'*
2. *ALTER TABLESPACE <TS NAME> FILE '/path/to/file'*
In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
convention. I.e. ALTER TABLESPACE <TS NAME> SET FILE '/path/to/file'
FILE can contain either an absolute path or a relative to the main database
file.
3. *DROP TABLESPACE <TS NAME> [INCLUDING CONTENTS]*
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 ...
(
...
FIELD TYPE CONSTRAINT ... USING INDEX ... *TABLESPACE {<TS NAME> | MAIN}* --
field constraint tablespace
...
CONSTRAINT ... USING INDEX ... *TABLESPACE {<TS NAME> | MAIN}* -- table
constraint tablespace
...
)
*TABLESPACE <TS NAME>*
Should we consider optional AT before TABLESPACE in all CREATE <object> [AT]
TABLESPACE
<tsname> statements ? And, perhaps, optional TO in ALTER <object> SET TABLESPACE [TO]
<tsname>.
Below I'll use both AT and TO, but not insist on it.
if the tablespace name is not specified *MAIN* will be used as default
tablespace for the table data.
DEFAULT, not MAIN, please.
A constraint will use the tablespace of the table if *TABLESPACE* is omitted.
5. ALTER TABLE <TABLE NAME> *ALTER TABLESPACE {<TS NAME> | MAIN}*
Data of the table will be moved to the specified tablespace or the main
database.
This statement not alters tablespace itself, i.e. there should be SET (and
DEFAULT):
ALTER TABLE <TABLE NAME> SET TABLESPACE [TO] <TS NAME> | DEFAULT
6. CREATE INDEX … *TABLESPACE {<TS NAME> | MAIN}*
The index will be created in the specified tablespace or the main database.
If tablespace is omitted the index will be created in the tablespace of the
table.
CREATE INDEX … [AT] TABLESPACE {<TS NAME> | DEFAULT}
7. ALTER INDEX <INDEX NAME>*ALTER TABLESPACE {<TS NAME> | MAIN}*
Data of the index will be moved to the specified tablespace or the main
database.
ALTER INDEX <INDEX NAME> SET TABLESPACE [TO] {<TS NAME> | DEFAULT}
It seems we missed
ALTER TABLE ADD CONSTRAINT ... [AT] TABLESPACE {<TS NAME> | DEFAULT}, and
ALTER TABLE ALTER COLUMN ... SET TABLESPACE [TO] {<TS NAME> | DEFAULT}
ODS CHANGES
=============
A new table RDB$TABLESPACES:
RDB$TABLESPACE_ID - SMALLINT
RDB$TABLESPACE_NAME - CHAR (63)
RDB$SECURITY_CLASS - CHAR (63)
RDB$DESCRIPTION - BLOB SUBTYPE TEXT SEGMENT SIZE 80
RDB$OWNER_NAME - CHAR (63)
RDB$FILE_NAME - VARCHAR (255)
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
Add page space id to page number in ods.h:index_root_page.
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
TS2 /path/to/tablespace2
or directly in the command line
gbak -c -ts <TSNAME1>=/path/to/tablespace1 -ts <TSNAME2>=...
It allows you to restore tablespace contents to new places.
If the option is not specified gbak will use old locations for every tablespace.
The initial implementation requires explicitly specifying all tablespace files. Later we can add merging tablespaces or default
original paths.
SOME DETAILS
=============
pag_header in every tablespace is reserved and may be replaced by a
new page type.
You mean page zero, which is currently always pag_header. I see no reason
to change this, so far. Header page uses to describe properties of database
and could be extended to describe pagespace when necessary.
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.
Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
I would reserve some more ID's for future system usage. I don't see it as
limitation for end users.
nbackup support is postponed.
At this stage I agree, but this must be 1st goal after initial
implementation, IMO.
Location of BLOB fields is postponed.
Less important, agree.
Page size is identical for every tablespace and the main database.
Support for many page sizes requires changes in page cache management and
should
be considered together. I don't see it as "must have" feature, btw.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
