пн, 11 окт. 2021 г. в 18:42, Vlad Khorsun <hv...@optima.com.ua>:
> 11.10.2021 15:17, Roman Simakov wrote:
> > 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.
>
I'd be happy to agree. Actually we took a look at Oracle syntax. The fact
is that DEFAULT means different things. For example, DEFAULT tablespace for
indices is the tablespace of its table. That's why DEFAULT is not such an
obvious name as we want it to be. But MAIN exactly specifies the database
itself. We especially have removed DEFAULT from the new version of the
proposal because it's better to explicitly require a tablespace name in the
beginning. Later we can add defaults.
But if you have a good idea how to resolve this issue we will be happy to
use it in the proposal.
>
> > 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'
>
Oracle syntax:
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';
I don't like it and we have only one data file actually and have no
preferences here. We can make [SET] optional.
> > 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.
>
Oracle doesn't use prepositions here.
> > if the tablespace name is not specified *MAIN* will be used as default
> tablespace for the table data.
>
> DEFAULT, not MAIN, please.
>
See above.
> > 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
>
Oracle uses ALTER TABLE <TABLE NAME> MOVE TABLESPACE <TS NAME> and without
any prepositions as well.
However I agree. SET looks more appropriate here than ALTER. But I still
see no point in prepositions.
Regarding DEFAULT see above.
>
> > 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}
>
The same reference to Oracle.
> > 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}
>
Agreed about SET.
Prepositions and DEFAULT see above.
>
> It seems we missed
>
> ALTER TABLE ADD CONSTRAINT ... [AT] TABLESPACE {<TS NAME> | DEFAULT},
> and
> ALTER TABLE ALTER COLUMN ... SET TABLESPACE [TO] {<TS NAME> | DEFAULT}
>
Thank you. I agree with the same remarks.
>
> > 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.
>
I have no objections. I suppose it will be used for nbackup implementation
at least.
> > 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 ?
>
Currently it's like
const USHORT DB_PAGE_SPACE = 1;
// .. here all tablespace IDs. Keep TRANS_PAGE_SPACE right after DB_PAGE_SPACE
const USHORT TRANS_PAGE_SPACE = 255; // is not used for tablespace id
I would reserve some more ID's for future system usage. I don't see it as
> limitation for end users.
>
I see no problem with it. At least for the first version we can use 63
tablespaces for example (64 including the main database) .
We add another constant for the border of the available IDs.
--
Roman Simakov
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
