CREATE TABLE with TABLESPACE

[jonny-xhl]

Can you tell me how to create table with tablespace.I thy create table with inschema,but schema is user.
Create.Table("TEST_XML").InSchema("ZL9BASEITEM") .WithColumn("input").AsString(2000).Nullable() .WithColumn("data").AsXml().Nullable();
I want to create table name is TEST_XML with tablespace name is ZL9BASEITEM.

[fubar-coder]

Is this for SQL Server or Oracle?

[jonny-xhl]

Is this for SQL Server or Oracle?

Only oracle has tablespace?My db is oracle

[fubar-coder]

It's not supported yet. I'll leave this open as a reminder for a new feature for Oracle. You may use the following workaround in the meantime:

Execute.Sql("ALTER DATABASE DEFAULT TABLESPACE ZL9BASEITEM");
Create.Table("TEST_XML")
    .WithColumn("input").AsString(2000).Nullable()
    .WithColumn("data").AsXml().Nullable();

I found this on this web-site: https://www.techonthenet.com/oracle/tablespaces/set_default.php

It's correct, that Oracle interprets the schema part of a fully qualified table name as user name.

I guess that it would be nice to have an oracle extension that would allow writing something like:

Create.Table("TEST_XML")
    .InTableSpace("ZL9BASEITEM")
    .WithColumn("input").AsString(2000).Nullable()
    .WithColumn("data").AsXml().Nullable();

This would be an Oracle-only extension.

[jonny-xhl]

It's not supported yet. I'll leave this open as a reminder for a new feature for Oracle. You may use the following workaround in the meantime:

Execute.Sql("ALTER DATABASE DEFAULT TABLESPACE ZL9BASEITEM");
Create.Table("TEST_XML")
    .WithColumn("input").AsString(2000).Nullable()
    .WithColumn("data").AsXml().Nullable();

I found this on this web-site: https://www.techonthenet.com/oracle/tablespaces/set_default.php

It's correct, that Oracle interprets the schema part of a fully qualified table name as user name.

I guess that it would be nice to have an oracle extension that would allow writing something like:

Create.Table("TEST_XML")
    .InTableSpace("ZL9BASEITEM")
    .WithColumn("input").AsString(2000).Nullable()
    .WithColumn("data").AsXml().Nullable();

This would be an Oracle-only extension.

Your guess perfect.My now solution is excute sql

[jzabroski]

Discussed in FluentMigrator gitter channel today with @hayer

Notes:

• Create GitHub Markdown table containing Database Provider, TableSpace Conceptual Name, CREATE TABLE w/ TableSpace SQL Syntax, CREATE TableSpace SQL Syntax

1. IBM DB2: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/intro/src/tpc/db2z_tablespaces.html
2. Firebird SQL: ?
3. PostgreSQL: https://www.postgresql.org/docs/10/manage-ag-tablespaces.html
4. Oracle: https://www.techonthenet.com/oracle/tablespaces/set_default.php
5. MySQL: https://dev.mysql.com/doc/refman/8.0/en/general-tablespaces.html
6. SQL Server: FILEGROUP instead of TABLESPACE

DB Provider	TableSpace Conceptual Name	CREATE TABLE w/ TABLESPACE SQL Syntax	CREATE TABLESPACE SQL Syntax
DB2	etc.	etc.	etc.
Firebird	etc.	etc.	etc.
PostgreSQL	etc.	etc.	etc.
Oracle	etc.	etc.	etc.
MySQL	etc.	etc.	etc.
SQL Server	etc.	etc.	etc.

[hayer]

Oracle docs for CREATE TABLESPACE. Oracle also, from version 12.2, has a concept of tablespace sets for sharding; I think these can be ignored in this iteration.

PostgreSQL docs for CREATE TABLESPACE.

MySQL docs for CREATE TABLESPACE.

How should the difference between FILEGROUP and TABLESPACE be handled in the SQL Server case?

[jzabroski]

I think it's just syntactic differences. I can try to explain what I mean next week when I have off Wednesday through Friday.

[fubar-coder]

Reopening it to increase visibility for this feature.

EDIT: @CQJonnyLin you can unsubscribe if you're not interested in this issue anymore.

[hayer]

Still interested. @jzabroski, did you have an idea for how to make the table-parts more extensible?

[jzabroski]

Yes. I think we need a new primitive FM element for TableSpace, apart from Table. You can use the Table Generator syntax as a guideline for creating a TableSpace. I think this is the hardest part and most amount of work.

Plugging the TableSpace then into the Table becomes a lot easier: you can either support doing it by name, or by a reference to the generator. @fubar-coder might have opinions here on what would be best. He wants to move to fully code generating a lot of these fluent extensions in the future.

So, the way I would break down a project plan would be:

Milestone 1: Creating tablespaces, deleting tablespaces, auto-reversing them

• Create "Create.Tablespace" FluentMigrator syntax extensions
• Code spike for how to inject database name into ALTER DATABASE [MyDb] ADD FILEGROUP fgWhatever
• Create "CREATE TABLESPACE" concrete SQL for each provider
  - The full list is documented, at the very least, under the IfDatabase Expressions documentation
  - [ ] SQLite
  - [ ] SAP HANA
  - [ ] SAP SQL Anywhere
  - [ ] PostgreSQL
  - [ ] PostgreSQL 9.2
  - [ ] Microsoft SQL Server
  - [ ] DB2
  - [ ] DB2 iSeries
  - [ ] MySQL
  - [ ] MariaDB
  - [ ] Aurora
  - Likely no action here, it's compatible with MySQL and PostgreSQL
  - [ ] Snowflake
  - [ ] Oracle
  - [ ] Microsoft JET Engine - probably not supported?
  - [ ] Firebird
  - [ ] Amazon Redshift
• Create "DROP TABLESPACE" FluentMigrator syntax extensions
  - The full list is documented, at the very least, under the IfDatabase Expressions documentation
  - [ ] SQLite
  - [ ] SAP HANA
  - [ ] SAP SQL Anywhere
  - [ ] PostgreSQL
  - [ ] PostgreSQL 9.2
  - [ ] Microsoft SQL Server
  - [ ] DB2
  - [ ] DB2 iSeries
  - [ ] MySQL
  - [ ] MariaDB
  - [ ] Aurora
  - Likely no action here, it's compatible with MySQL and PostgreSQL
  - [ ] Snowflake
  - [ ] Oracle
  - [ ] Microsoft JET Engine - probably not supported?
  - [ ] Firebird
  - [ ] Amazon Redshift
• Update documentation for Auto-reversing migrations

Phase 2: Referencing a TABLESPACE in a CREATE TABLE statement

• ALTER TABLE should be out-of-scope for minimum viable product. Who knows which databases support it robustly.

Phase 3: Rename a TABLESPACE

• Self-explanatory: I personally think this is more useful than ALTER DATABASE [MyDB] MODIFY FILEGROUP

Phase 4: ALTER DATABASE [MyDB] MODIFY FILEGROUP

• SQL Server let's you re-name a tablespace. Do other databases?

Phase 5: Stuff way out of scope for this project

• SQL Server and other databases support ALTER TABLE .. SWITCH for manipulating partitions
  • See: https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/
  • Out of scope because we're not trying to solve partitions here, just support for tablespaces, which is a pre-requisite to support partitions

Questions for Mark to help Peter (and me)

• @fubar-coder Do we have a single page in the documentation that lists all supported providers? e.g., Is there something other than IfDatabase Expressions?
• @fubar-coder Should the fluent syntax allow the Table Syntax to contain a virtual TableSpace entity, the same way the Table Syntax contains a virtual ColumnCollection entity?
• @fubar-coder Do we have any code today that injects the database name targeted by the migrations into the generated SQL script? How do you recommend we do that?

[fubar-coder]

1. The supported providers are a snippet that gets included from several sources (e.g. Introduction and IfDatabase Expressions). I think that it would be good to create a separate page containing exhaustive information about the providers, its supported features (e.g. tablespace support), and its limitations (DDL in transactions on MySQL, see issue Transaction Per Session Not Work - I'm Using Mariadb #1031).
2. I don't mind exposing a TableSpace property in the classes. I think it would just complicate the implementation.
3. I'd just use dependecy injection for additional information for a migration. I'm currently using Fluid as templating engine. Maybe we can make the templating implementation of SQL scripts exchangeable, which would allow more than just replacing values?

[jzabroski]

Added tags to make it clearer in generating release notes which databases a feature targets.

Effectively, this is a baby step to cleaning up / defining outcome for #1032

Separately, I found this issue on Firebird Tracker, with a funny comment I had to share:

User: awharrison
Logged In: YES
user_id=66088

this is pretty much an "over my dead body" issue. Much of the design of InterBase focused on eliminating the need for table spaces and other placement. It works well and makes managing the database possible without an advanced degree in Firebird

Snowflake has the concept of micro-partitions to facilitate eliminating static partitions. However, they also have the CLUSTER BY keyword on CREATE TABLE

This sort of "data plane extensibility" architecture seems to be the future, for many reasons. Because of how Snowflake "joins clusters together", the need for static partitioning goes away: it's replaced by creating data marts for individual customers. Queries across customers are simply joins on all the customer clusters. At least, I think that is how it works - need to play around with it to make sure my understanding is correct.

@hayer @fubar-coder if the above is true of Snowflake and Snowflake is truly "next gen datawarehouse" architecture, then I think we can add the TableSpace concept but ideally as part of a separate assembly. The broad point here is that in 10-20 years, every database will be in the cloud, and we literally won't know about filegroups/table-spaces in any meaningful sense we're discussing them here.

[hayer]

In 10-20 years, yes completely agree. In the mean time... 😉

[jzabroski]

Yes, I thought about it over the weekend. This feature will enable me to do some testing for a client who wanted to answer the question of what database technologies they should be using. Right now it is very painful to manage this stuff by hand.

I'd love to be able to quantify a few things for them. I suspect the winner will be Azure SQL Hyperscale or Snowflake based on reading, but without my own numbers, hard to say.

Plus, if you think about it, FluentMigrator is the most powerful tool for abstracting SQL DDL. There is nothing like it. It would be the ideal tool for writing database benchmarks and defining best practices. However, without tablespaces, we really are nowhere near being able to abstract SQL scenarios used by Hyperscalers.