Support node for Projections in Vertica - a new node to display all the projections in a database
I recommend using Materialized View icon if an icon is needed for Projections in the Schema Browser.
I understand Projections are different from Materialized Views, but I think reusing existing icon will save lots of time.
I have begun work on this. I have the tree node and detail view working. I am working on full extraction and recreation scripts. Should have this all functional and working but without GUI support by monday...
I have begun work on this. I have the tree node and detail view working. I am working on full extraction and recreation scripts. Should have this all functional and working but without GUI support by monday...
The following support has been added for Vertica v4.6+ -> Projection objects:
1.) Vertica Tree now has a Projections folder. Under folder you will have projection objects with there columns folder and column objects. The projections object will have scripting menu options for [script select(*), script create/drop/alter]
a.) Projections has a detail view model.
2.) Object Search allows you to search for projections and projection columns
3.) Schema Object Generator -> Will allow for scripting of projections
4.) There is NO ERM support for projections at the time.
The following support has been added for Vertica v4.6+ -> Projection objects:
1.) Vertica Tree now has a Projections folder. Under folder you will have projection objects with there columns folder and column objects. The projections object will have scripting menu options for [script select(*), script create/drop/alter]
a.) Projections has a detail view model.
2.) Object Search allows you to search for projections and projection columns
3.) Schema Object Generator -> Will allow for scripting of projections
4.) There is NO ERM support for projections at the time.
I used the following code to test:
create schema ivan
go
create table ivan.a (a int not null primary key, b int not null, c varchar(20), d date)
go
create table ivan.ohmy (a int not null primary key, b varchar(255))
go
alter table ivan.a add constraint fkey01 foreign key (b) references ivan.ohmy (a)
go
insert into ivan.a values (0, 100, 'ivan gron', '3/24/2015')
go
insert into ivan.a values (1, 101, 'niels gron', '3/24/2015')
go
insert into ivan.a values (2, 102, 'guillermo gron', '3/24/2015')
go
insert into ivan.a values (3, 103, 'margarette gron', '3/24/2015')
go
insert into ivan.a values (4, 104, 'edith gron', '3/24/2015')
go
insert into ivan.a values (5, 105, 'yvonne gron', '3/24/2015')
go
insert into ivan.a values (6, 106, 'william gron', '3/24/2015')
go
insert into ivan.ohmy values (100, 'eduardo')
go
insert into ivan.ohmy values (101, 'douglas')
go
insert into ivan.ohmy values (102, 'xxx')
go
insert into ivan.ohmy values (103, 'gloria')
go
insert into ivan.ohmy values (104, 'caledonia')
go
insert into ivan.ohmy values (105, 'rose')
go
insert into ivan.ohmy values (106, 'bill')
go
DROP PROJECTION ivan.a_proj_a
go
CREATE PROJECTION IF NOT EXISTS ivan.a_proj_a
(id ACCESSRANK 3, ohmy, name ENCODING RLE, sdate ACCESSRANK 1, middle)
AS SELECT a.a, a.b, a.c, a.d, b.b FROM "ivan"."a" INNER JOIN "ivan"."ohmy" b on a.b = b.a ORDER BY a
SEGMENTED BY MODULARHASH(d) ALL NODES OFFSET 2
go
SELECT EXPORT_OBJECTS('','ivan.a_proj_a',false)
FYI: Catalog tables only capture the first table in which a projection is created on when created on multiple tables. Extraction for the detail view comes from the catalog tables, but the DDL scripts are generated via the vertica function EXPORT_OBJECTS('<destination>','<object_schema.object_name>', <ksafe> true/false)
I used the following code to test:
create schema ivan
go
create table ivan.a (a int not null primary key, b int not null, c varchar(20), d date)
go
create table ivan.ohmy (a int not null primary key, b varchar(255))
go
alter table ivan.a add constraint fkey01 foreign key (b) references ivan.ohmy (a)
go
insert into ivan.a values (0, 100, 'ivan gron', '3/24/2015')
go
insert into ivan.a values (1, 101, 'niels gron', '3/24/2015')
go
insert into ivan.a values (2, 102, 'guillermo gron', '3/24/2015')
go
insert into ivan.a values (3, 103, 'margarette gron', '3/24/2015')
go
insert into ivan.a values (4, 104, 'edith gron', '3/24/2015')
go
insert into ivan.a values (5, 105, 'yvonne gron', '3/24/2015')
go
insert into ivan.a values (6, 106, 'william gron', '3/24/2015')
go
insert into ivan.ohmy values (100, 'eduardo')
go
insert into ivan.ohmy values (101, 'douglas')
go
insert into ivan.ohmy values (102, 'xxx')
go
insert into ivan.ohmy values (103, 'gloria')
go
insert into ivan.ohmy values (104, 'caledonia')
go
insert into ivan.ohmy values (105, 'rose')
go
insert into ivan.ohmy values (106, 'bill')
go
DROP PROJECTION ivan.a_proj_a
go
CREATE PROJECTION IF NOT EXISTS ivan.a_proj_a
(id ACCESSRANK 3, ohmy, name ENCODING RLE, sdate ACCESSRANK 1, middle)
AS SELECT a.a, a.b, a.c, a.d, b.b FROM "ivan"."a" INNER JOIN "ivan"."ohmy" b on a.b = b.a ORDER BY a
SEGMENTED BY MODULARHASH(d) ALL NODES OFFSET 2
go
SELECT EXPORT_OBJECTS('','ivan.a_proj_a',false)
FYI: Catalog tables only capture the first table in which a projection is created on when created on multiple tables. Extraction for the detail view comes from the catalog tables, but the DDL scripts are generated via the vertica function EXPORT_OBJECTS('<destination>','<object_schema.object_name>', <ksafe> true/false)
Issue #10956 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build v16.0.3-1 |
No time estimate |
I recommend using Materialized View icon if an icon is needed for Projections in the Schema Browser.
I understand Projections are different from Materialized Views, but I think reusing existing icon will save lots of time.