phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <>
Subject [jira] [Resolved] (PHOENIX-723) Tweak syntax for multi-tenant tables
Date Fri, 02 May 2014 04:52:15 GMT


James Taylor resolved PHOENIX-723.

       Resolution: Fixed
    Fix Version/s: 5.0.0

> Tweak syntax for multi-tenant tables
> ------------------------------------
>                 Key: PHOENIX-723
>                 URL:
>             Project: Phoenix
>          Issue Type: Task
>    Affects Versions: 3.0-Release
>            Reporter: James Taylor
>            Assignee: elevine
>             Fix For: 3.0.0, 4.0.0, 5.0.0
> The current syntax relies on a BASE_TABLE property, which is somewhat outside of the
SQL syntax.  Here's an example of the current syntax:
>     CREATE TABLE my_base_schema.base_table (
>         tenant_id CHAR(15), 
>         type_id CHAR(3), 
>         entity_id CHAR(15));
>     CREATE TABLE my_schema.my_table (
>         additional_col VARCHAR)
>         BASE_TABLE='my_base_schema.base_table',
>         TENANT_TYPE_ID='abc';
> Here's an example of the proposed new syntax:
>     CREATE TABLE my_base_schema.base_table (
>         tenant_id CHAR(15),
>         type_id CHAR(3),
>         entity_id CHAR(15)) MULTI_TENANT=true;
>     CREATE TABLE my_schema.my_table (column1 VARCHAR)
>     LIKE my_base_schema.base_table AS 'abc';
> This would also be allowed
>     ALTER TABLE my_base_schema.base_table SET MULTI_TENANT=true;
> as would this, but only if the table has no tenant-specific tables:
>     ALTER TABLE my_base_schema.base_table SET MULTI_TENANT=false;
> Changes include:
> * Explicitly declaring a table as multi-tenant. This will allow us to do any validation
required for the base table and create a second HBase table where the index rows would be
stored (see -491).
> * Don't allow a tenant connection to create a table from a base table that is not multi-tenant.
> * The tenant type ID is optionally specified in the AS clause. If not supplied, then
the automatic filtering we do on tenant-specific tables would be restricted to just the first
column (i.e. tenant_id) instead of two columns (i.e. tenant_id and type_id). The column definitions
should also be optional and if left off would just create a tenant-specific table without
any additional columns being defined.
> * Support using LIKE for non-tenant-specific use cases, as I believe the code would be
the same. I think in this case, we wouldn't want to restrict what you can/can't do on the
base table, though. Maybe the easiest way to handle this is by just not tracking the base
table for these cases - we'd essentially just be making a copy of the metadata to create a
table with a new name (that's what Postgres and MySQL support).

This message was sent by Atlassian JIRA

View raw message