From 3e220fae5f28dd4779c167b4485a7bd0c5436f4d Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Thu, 26 Mar 2020 15:33:46 +0000 Subject: [PATCH 01/14] console: rewrote `fetchTableListQuery` to include materialized views Instead of sticking to the information_schema tables, this now uses pg internals. This is not a regression, as the query was already relying on some of them. Furthermore, this heavily simplifies the comment subqueries, since we have access to the oid. --- console/src/components/Services/Data/utils.js | 171 ++++++++---------- 1 file changed, 80 insertions(+), 91 deletions(-) diff --git a/console/src/components/Services/Data/utils.js b/console/src/components/Services/Data/utils.js index 99993f52f58c4..ebed00f77da12 100644 --- a/console/src/components/Services/Data/utils.js +++ b/console/src/components/Services/Data/utils.js @@ -281,25 +281,28 @@ export const fetchTrackedTableListQuery = options => { return query; }; -const generateWhereClause = options => { +const generateWhereClause = (options, + tableName = 'ist.table_name', + schemaName = 'ist.table_schema', + clausePrefix = 'where') => { let whereClause = ''; const whereCondtions = []; if (options.schemas) { options.schemas.forEach(schemaName => { - whereCondtions.push(`(ist.table_schema='${schemaName}')`); + whereCondtions.push(`(${schemaField}='${schemaName}')`); }); } if (options.tables) { options.tables.forEach(tableInfo => { whereCondtions.push( - `(ist.table_schema='${tableInfo.table_schema}' and ist.table_name='${tableInfo.table_name}')` + `(${schemaName}='${tableInfo.table_schema}' and ${tableName}='${tableInfo.table_name}')` ); }); } if (whereCondtions.length > 0) { - whereClause = 'where'; + whereClause = clausePrefix; } whereCondtions.forEach((whereInfo, index) => { @@ -315,23 +318,23 @@ const generateWhereClause = options => { export const fetchTrackedTableFkQuery = options => { const whereQuery = generateWhereClause(options); - const runSql = `select + const runSql = `select COALESCE( json_agg( row_to_json(info) - ), + ), '[]' :: JSON - ) AS tables -FROM + ) AS tables +FROM ( select - hdb_fkc.*, - fk_ref_table.table_name IS NOT NULL AS is_ref_table_tracked - from - hdb_catalog.hdb_table AS ist - JOIN hdb_catalog.hdb_foreign_key_constraint AS hdb_fkc ON hdb_fkc.table_schema = ist.table_schema - and hdb_fkc.table_name = ist.table_name - LEFT OUTER JOIN hdb_catalog.hdb_table AS fk_ref_table ON fk_ref_table.table_schema = hdb_fkc.ref_table_table_schema + hdb_fkc.*, + fk_ref_table.table_name IS NOT NULL AS is_ref_table_tracked + from + hdb_catalog.hdb_table AS ist + JOIN hdb_catalog.hdb_foreign_key_constraint AS hdb_fkc ON hdb_fkc.table_schema = ist.table_schema + and hdb_fkc.table_name = ist.table_name + LEFT OUTER JOIN hdb_catalog.hdb_table AS fk_ref_table ON fk_ref_table.table_schema = hdb_fkc.ref_table_table_schema and fk_ref_table.table_name = hdb_fkc.ref_table ${whereQuery} ) as info @@ -346,24 +349,24 @@ FROM export const fetchTrackedTableReferencedFkQuery = options => { const whereQuery = generateWhereClause(options); - const runSql = `select + const runSql = `select COALESCE( json_agg( row_to_json(info) - ), + ), '[]' :: JSON - ) AS tables -FROM + ) AS tables +FROM ( select DISTINCT ON (hdb_fkc.constraint_oid) - hdb_fkc.*, + hdb_fkc.*, fk_ref_table.table_name IS NOT NULL AS is_table_tracked, hdb_uc.constraint_name IS NOT NULL AS is_unique - from - hdb_catalog.hdb_table AS ist - JOIN hdb_catalog.hdb_foreign_key_constraint AS hdb_fkc ON hdb_fkc.ref_table_table_schema = ist.table_schema - and hdb_fkc.ref_table = ist.table_name - LEFT OUTER JOIN hdb_catalog.hdb_table AS fk_ref_table ON fk_ref_table.table_schema = hdb_fkc.table_schema + from + hdb_catalog.hdb_table AS ist + JOIN hdb_catalog.hdb_foreign_key_constraint AS hdb_fkc ON hdb_fkc.ref_table_table_schema = ist.table_schema + and hdb_fkc.ref_table = ist.table_name + LEFT OUTER JOIN hdb_catalog.hdb_table AS fk_ref_table ON fk_ref_table.table_schema = hdb_fkc.table_schema and fk_ref_table.table_name = hdb_fkc.table_name LEFT OUTER JOIN hdb_catalog.hdb_unique_constraint AS hdb_uc ON hdb_uc.table_schema = hdb_fkc.table_schema and hdb_uc.table_name = hdb_fkc.table_name and ARRAY(select json_array_elements_text(hdb_uc.columns) ORDER BY json_array_elements_text) = ARRAY(select json_object_keys(hdb_fkc.column_mapping) ORDER BY json_object_keys) @@ -378,74 +381,60 @@ FROM }; export const fetchTableListQuery = options => { - const whereQuery = generateWhereClause(options); + const whereQuery = generateWhereClause( + options, + tableName = 'pgc.relname', + schemaName = 'pgn.nspname', + clausePrefix = 'and' + ); // TODO: optimise this. Multiple OUTER JOINS causes data bloating const runSql = ` -select - COALESCE( - json_agg( - row_to_json(info) - ), - '[]' :: JSON - ) AS tables -FROM - ( - select - ist.table_schema, - ist.table_name, - ist.table_type, - obj_description( - ( - quote_ident(ist.table_schema) || '.' || quote_ident(ist.table_name) - ):: regclass, - 'pg_class' - ) AS comment, - COALESCE(json_agg( - DISTINCT row_to_json(is_columns) :: JSONB || jsonb_build_object( - 'comment', - ( - SELECT - pg_catalog.col_description( - c.oid, is_columns.ordinal_position :: int - ) - FROM - pg_catalog.pg_class c - WHERE - c.oid = (quote_ident(ist.table_schema) || '.' || quote_ident(ist.table_name)):: regclass :: oid - AND c.relname = is_columns.table_name - ) - ) - ) FILTER (WHERE is_columns.column_name IS NOT NULL), '[]' :: JSON) AS columns, - COALESCE(json_agg( - DISTINCT row_to_json(is_triggers) :: JSONB || jsonb_build_object( - 'comment', - ( - SELECT description FROM pg_description JOIN pg_trigger ON pg_description.objoid = pg_trigger.oid - WHERE - tgname = is_triggers.trigger_name - AND tgrelid = (quote_ident(is_triggers.event_object_schema) || '.' || quote_ident(is_triggers.event_object_table)):: regclass :: oid - ) - ) - ) FILTER (WHERE is_triggers.trigger_name IS NOT NULL), '[]' :: JSON) AS triggers, - row_to_json(is_views) AS view_info - FROM - information_schema.tables AS ist - LEFT OUTER JOIN information_schema.columns AS is_columns ON - is_columns.table_schema = ist.table_schema - AND is_columns.table_name = ist.table_name - LEFT OUTER JOIN information_schema.views AS is_views ON is_views.table_schema = ist.table_schema - AND is_views.table_name = ist.table_name - LEFT OUTER JOIN information_schema.triggers AS is_triggers ON - is_triggers.event_object_schema = ist.table_schema AND - is_triggers.event_object_table = ist.table_name - ${whereQuery} - GROUP BY - ist.table_schema, - ist.table_name, - ist.table_type, - is_views.* - ) AS info +SELECT + COALESCE(Json_agg(Row_to_json(info)), '[]' :: json) AS tables +FROM ( + SELECT + pgn.nspname as table_schema, + pgc.relname as table_name, + case + when pgc.relkind = 'r' then 'BASE TABLE' + when pgc.relkind = 'v' then 'VIEW' + when pgc.relkind = 'm' then 'MATERIALIZED VIEW' + end as table_type, + obj_description(pgc.oid) AS comment, + COALESCE(json_agg(DISTINCT row_to_json(isc) :: jsonb || jsonb_build_object('comment', col_description(pga.attrelid, pga.attnum))) filter (WHERE isc.column_name IS NOT NULL), '[]' :: json) AS columns, + COALESCE(json_agg(DISTINCT row_to_json(ist) :: jsonb || jsonb_build_object('comment', obj_description(pgt.tgrelid))) filter (WHERE ist.trigger_name IS NOT NULL), '[]' :: json) AS triggers, + row_to_json(isv) AS view_info + + FROM pg_class as pgc + INNER JOIN pg_namespace as pgn + ON pgc.relnamespace = pgn.oid + + /* columns */ + LEFT OUTER JOIN pg_attribute AS pga + ON pga.attrelid = pgc.oid + LEFT OUTER JOIN information_schema.columns AS isc + ON isc.table_schema = pgn.nspname + AND isc.table_name = pgc.relname + AND isc.column_name = pga.attname + + /* triggers */ + LEFT OUTER JOIN pg_trigger AS pgt + ON pgt.tgrelid = pgc.oid + LEFT OUTER JOIN information_schema.triggers AS ist + ON ist.event_object_schema = pgn.nspname + AND ist.event_object_table = pgc.relname + AND ist.trigger_name = pgt.tgname + + /* views */ + LEFT OUTER JOIN information_schema.views AS isv + ON isv.table_schema = pgn.nspname + AND isv.table_name = pgc.relname + WHERE + pgc.relkind IN ('r', 'v', 'm') + ${whereQuery} + GROUP BY pgc.oid, pgn.nspname, pgc.relname, table_type, isv.* +) AS info; `; return getRunSqlQuery( runSql, @@ -617,7 +606,7 @@ export const commonDataTypes = [ * Filter types whose typename is unknown and type category is not 'Pseudo' and it is valid and available to be used * */ export const fetchColumnTypesQuery = ` -SELECT +SELECT string_agg(t.typname, ',') as "Type Name", string_agg(pg_catalog.format_type(t.oid, NULL), ',') as "Display Name", string_agg(coalesce(pg_catalog.obj_description(t.oid, 'pg_type'), ''), ':') as "Descriptions", From 7caa8159cf40480709fc502bf9fa3b66429f2137 Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Thu, 26 Mar 2020 16:51:04 +0000 Subject: [PATCH 02/14] console: inserted definition of columns This is horribly verbose; but it does the job. The giant expression of `isc` is almost exactly the definition of the `information_schema.columns` view, with one major restriction: it also list columns present in materialized views. This is very ugly and could be simplified heavily. For technical details: I obtained the details of the view by running `psql -E` and using `\dS+ information_schema.columns`. The only difference is this: before: `c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'p'::"char"])` after: `c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char"])` --- console/src/components/Services/Data/utils.js | 133 ++++++++++++++++-- 1 file changed, 122 insertions(+), 11 deletions(-) diff --git a/console/src/components/Services/Data/utils.js b/console/src/components/Services/Data/utils.js index ebed00f77da12..9e3936c17b9a2 100644 --- a/console/src/components/Services/Data/utils.js +++ b/console/src/components/Services/Data/utils.js @@ -281,28 +281,30 @@ export const fetchTrackedTableListQuery = options => { return query; }; -const generateWhereClause = (options, - tableName = 'ist.table_name', - schemaName = 'ist.table_schema', - clausePrefix = 'where') => { +const generateWhereClause = ( + options, + sqlTableName = 'ist.table_name', + sqlSchemaName = 'ist.table_schema', + clausePrefix = 'where' +) => { let whereClause = ''; const whereCondtions = []; if (options.schemas) { options.schemas.forEach(schemaName => { - whereCondtions.push(`(${schemaField}='${schemaName}')`); + whereCondtions.push(`(${sqlSchemaName}='${schemaName}')`); }); } if (options.tables) { options.tables.forEach(tableInfo => { whereCondtions.push( - `(${schemaName}='${tableInfo.table_schema}' and ${tableName}='${tableInfo.table_name}')` + `(${sqlSchemaName}='${tableInfo.table_schema}' and ${sqlTableName}='${tableInfo.table_name}')` ); }); } if (whereCondtions.length > 0) { - whereClause = clausePrefix; + whereClause = clausePrefix; } whereCondtions.forEach((whereInfo, index) => { @@ -383,9 +385,9 @@ FROM export const fetchTableListQuery = options => { const whereQuery = generateWhereClause( options, - tableName = 'pgc.relname', - schemaName = 'pgn.nspname', - clausePrefix = 'and' + 'pgc.relname', + 'pgn.nspname', + 'and' ); // TODO: optimise this. Multiple OUTER JOINS causes data bloating @@ -413,7 +415,116 @@ FROM ( /* columns */ LEFT OUTER JOIN pg_attribute AS pga ON pga.attrelid = pgc.oid - LEFT OUTER JOIN information_schema.columns AS isc + LEFT OUTER JOIN ( + SELECT + current_database()::information_schema.sql_identifier AS table_catalog, + nc.nspname::information_schema.sql_identifier AS table_schema, + c.relname::information_schema.sql_identifier AS table_name, + a.attname::information_schema.sql_identifier AS column_name, + a.attnum::information_schema.cardinal_number AS ordinal_position, + CASE + WHEN a.attgenerated = ''::char THEN pg_get_expr(ad.adbin, ad.adrelid) + ELSE NULL::text + END::information_schema.character_data AS column_default, + CASE + WHEN a.attnotnull OR t.typtype = 'd'::char AND t.typnotnull THEN 'NO'::text + ELSE 'YES'::text + END::information_schema.yes_or_no AS is_nullable, + CASE + WHEN t.typtype = 'd'::char THEN + CASE + WHEN bt.typelem <> 0::oid AND bt.typlen = '-1'::integer THEN 'ARRAY'::text + WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer) + ELSE 'USER-DEFINED'::text + END + ELSE + CASE + WHEN t.typelem <> 0::oid AND t.typlen = '-1'::integer THEN 'ARRAY'::text + WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer) + ELSE 'USER-DEFINED'::text + END + END::information_schema.character_data AS data_type, + information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length, + information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_octet_length, + information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision, + information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision_radix, + information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_scale, + information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS datetime_precision, + information_schema._pg_interval_type(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.character_data AS interval_type, + NULL::integer::information_schema.cardinal_number AS interval_precision, + NULL::name::information_schema.sql_identifier AS character_set_catalog, + NULL::name::information_schema.sql_identifier AS character_set_schema, + NULL::name::information_schema.sql_identifier AS character_set_name, + CASE + WHEN nco.nspname IS NOT NULL THEN current_database() + ELSE NULL::name + END::information_schema.sql_identifier AS collation_catalog, + nco.nspname::information_schema.sql_identifier AS collation_schema, + co.collname::information_schema.sql_identifier AS collation_name, + CASE + WHEN t.typtype = 'd'::char THEN current_database() + ELSE NULL::name + END::information_schema.sql_identifier AS domain_catalog, + CASE + WHEN t.typtype = 'd'::char THEN nt.nspname + ELSE NULL::name + END::information_schema.sql_identifier AS domain_schema, + CASE + WHEN t.typtype = 'd'::char THEN t.typname + ELSE NULL::name + END::information_schema.sql_identifier AS domain_name, + current_database()::information_schema.sql_identifier AS udt_catalog, + COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS udt_schema, + COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, + NULL::name::information_schema.sql_identifier AS scope_catalog, + NULL::name::information_schema.sql_identifier AS scope_schema, + NULL::name::information_schema.sql_identifier AS scope_name, + NULL::integer::information_schema.cardinal_number AS maximum_cardinality, + a.attnum::information_schema.sql_identifier AS dtd_identifier, + 'NO'::character varying::information_schema.yes_or_no AS is_self_referencing, + CASE + WHEN a.attidentity = ANY (ARRAY['a'::char, 'd'::char]) THEN 'YES'::text + ELSE 'NO'::text + END::information_schema.yes_or_no AS is_identity, + CASE a.attidentity + WHEN 'a'::char THEN 'ALWAYS'::text + WHEN 'd'::char THEN 'BY DEFAULT'::text + ELSE NULL::text + END::information_schema.character_data AS identity_generation, + seq.seqstart::information_schema.character_data AS identity_start, + seq.seqincrement::information_schema.character_data AS identity_increment, + seq.seqmax::information_schema.character_data AS identity_maximum, + seq.seqmin::information_schema.character_data AS identity_minimum, + CASE + WHEN seq.seqcycle THEN 'YES'::text + ELSE 'NO'::text + END::information_schema.yes_or_no AS identity_cycle, + CASE + WHEN a.attgenerated <> ''::char THEN 'ALWAYS'::text + ELSE 'NEVER'::text + END::information_schema.character_data AS is_generated, + CASE + WHEN a.attgenerated <> ''::char THEN pg_get_expr(ad.adbin, ad.adrelid) + ELSE NULL::text + END::information_schema.character_data AS generation_expression, + CASE + WHEN (c.relkind = ANY (ARRAY['r'::char, 'p'::char])) OR (c.relkind = ANY (ARRAY['v'::char, 'f'::char])) AND pg_column_is_updatable(c.oid::regclass, a.attnum, false) THEN 'YES'::text + ELSE 'NO'::text + END::information_schema.yes_or_no AS is_updatable + FROM pg_attribute a + LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum + JOIN (pg_class c + JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid + JOIN (pg_type t + JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid + LEFT JOIN (pg_type bt + JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::char AND t.typbasetype = bt.oid + LEFT JOIN (pg_collation co + JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR co.collname <> 'default'::name) + LEFT JOIN (pg_depend dep + JOIN pg_sequence seq ON dep.classid = 'pg_class'::regclass::oid AND dep.objid = seq.seqrelid AND dep.deptype = 'i'::char) ON dep.refclassid = 'pg_class'::regclass::oid AND dep.refobjid = c.oid AND dep.refobjsubid = a.attnum + WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::char, 'v'::char, 'm'::char])) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) + ) AS isc ON isc.table_schema = pgn.nspname AND isc.table_name = pgc.relname AND isc.column_name = pga.attname From b97bcccba09efa0491623e7e363c8520a64d136b Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Fri, 27 Mar 2020 13:01:15 +0000 Subject: [PATCH 03/14] console: fix `fetchTableListQuery` by removing some columns To be more precise: I have used the definition of `information_schema.columns` as it was in [version 9.5](https://github.com/postgres/postgres/blob/REL9_5_STABLE/src/backend/catalog/information_schema.sql), which is the oldest version we support. I tested it on my local database, which is on version 12. To make sure it worked, I had to remove several functions call that did not exist anymore. The result is a trimmed-down version, that has only the most essential fields. I am going to run more tests to see whether this is enough or not. --- console/src/components/Services/Data/utils.js | 149 +++++------------- 1 file changed, 42 insertions(+), 107 deletions(-) diff --git a/console/src/components/Services/Data/utils.js b/console/src/components/Services/Data/utils.js index 9e3936c17b9a2..adcce3f85f210 100644 --- a/console/src/components/Services/Data/utils.js +++ b/console/src/components/Services/Data/utils.js @@ -417,113 +417,48 @@ FROM ( ON pga.attrelid = pgc.oid LEFT OUTER JOIN ( SELECT - current_database()::information_schema.sql_identifier AS table_catalog, - nc.nspname::information_schema.sql_identifier AS table_schema, - c.relname::information_schema.sql_identifier AS table_name, - a.attname::information_schema.sql_identifier AS column_name, - a.attnum::information_schema.cardinal_number AS ordinal_position, - CASE - WHEN a.attgenerated = ''::char THEN pg_get_expr(ad.adbin, ad.adrelid) - ELSE NULL::text - END::information_schema.character_data AS column_default, - CASE - WHEN a.attnotnull OR t.typtype = 'd'::char AND t.typnotnull THEN 'NO'::text - ELSE 'YES'::text - END::information_schema.yes_or_no AS is_nullable, - CASE - WHEN t.typtype = 'd'::char THEN - CASE - WHEN bt.typelem <> 0::oid AND bt.typlen = '-1'::integer THEN 'ARRAY'::text - WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer) - ELSE 'USER-DEFINED'::text - END - ELSE - CASE - WHEN t.typelem <> 0::oid AND t.typlen = '-1'::integer THEN 'ARRAY'::text - WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer) - ELSE 'USER-DEFINED'::text - END - END::information_schema.character_data AS data_type, - information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length, - information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_octet_length, - information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision, - information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision_radix, - information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_scale, - information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS datetime_precision, - information_schema._pg_interval_type(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.character_data AS interval_type, - NULL::integer::information_schema.cardinal_number AS interval_precision, - NULL::name::information_schema.sql_identifier AS character_set_catalog, - NULL::name::information_schema.sql_identifier AS character_set_schema, - NULL::name::information_schema.sql_identifier AS character_set_name, - CASE - WHEN nco.nspname IS NOT NULL THEN current_database() - ELSE NULL::name - END::information_schema.sql_identifier AS collation_catalog, - nco.nspname::information_schema.sql_identifier AS collation_schema, - co.collname::information_schema.sql_identifier AS collation_name, - CASE - WHEN t.typtype = 'd'::char THEN current_database() - ELSE NULL::name - END::information_schema.sql_identifier AS domain_catalog, - CASE - WHEN t.typtype = 'd'::char THEN nt.nspname - ELSE NULL::name - END::information_schema.sql_identifier AS domain_schema, - CASE - WHEN t.typtype = 'd'::char THEN t.typname - ELSE NULL::name - END::information_schema.sql_identifier AS domain_name, - current_database()::information_schema.sql_identifier AS udt_catalog, - COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS udt_schema, - COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, - NULL::name::information_schema.sql_identifier AS scope_catalog, - NULL::name::information_schema.sql_identifier AS scope_schema, - NULL::name::information_schema.sql_identifier AS scope_name, - NULL::integer::information_schema.cardinal_number AS maximum_cardinality, - a.attnum::information_schema.sql_identifier AS dtd_identifier, - 'NO'::character varying::information_schema.yes_or_no AS is_self_referencing, - CASE - WHEN a.attidentity = ANY (ARRAY['a'::char, 'd'::char]) THEN 'YES'::text - ELSE 'NO'::text - END::information_schema.yes_or_no AS is_identity, - CASE a.attidentity - WHEN 'a'::char THEN 'ALWAYS'::text - WHEN 'd'::char THEN 'BY DEFAULT'::text - ELSE NULL::text - END::information_schema.character_data AS identity_generation, - seq.seqstart::information_schema.character_data AS identity_start, - seq.seqincrement::information_schema.character_data AS identity_increment, - seq.seqmax::information_schema.character_data AS identity_maximum, - seq.seqmin::information_schema.character_data AS identity_minimum, - CASE - WHEN seq.seqcycle THEN 'YES'::text - ELSE 'NO'::text - END::information_schema.yes_or_no AS identity_cycle, - CASE - WHEN a.attgenerated <> ''::char THEN 'ALWAYS'::text - ELSE 'NEVER'::text - END::information_schema.character_data AS is_generated, - CASE - WHEN a.attgenerated <> ''::char THEN pg_get_expr(ad.adbin, ad.adrelid) - ELSE NULL::text - END::information_schema.character_data AS generation_expression, - CASE - WHEN (c.relkind = ANY (ARRAY['r'::char, 'p'::char])) OR (c.relkind = ANY (ARRAY['v'::char, 'f'::char])) AND pg_column_is_updatable(c.oid::regclass, a.attnum, false) THEN 'YES'::text - ELSE 'NO'::text - END::information_schema.yes_or_no AS is_updatable - FROM pg_attribute a - LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum - JOIN (pg_class c - JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid - JOIN (pg_type t - JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid - LEFT JOIN (pg_type bt - JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::char AND t.typbasetype = bt.oid - LEFT JOIN (pg_collation co - JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR co.collname <> 'default'::name) - LEFT JOIN (pg_depend dep - JOIN pg_sequence seq ON dep.classid = 'pg_class'::regclass::oid AND dep.objid = seq.seqrelid AND dep.deptype = 'i'::char) ON dep.refclassid = 'pg_class'::regclass::oid AND dep.refobjid = c.oid AND dep.refobjsubid = a.attnum - WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::char, 'v'::char, 'm'::char])) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) + current_database() AS table_catalog, + nc.nspname AS table_schema, + c.relname AS table_name, + a.attname AS column_name, + a.attnum AS ordinal_position, + pg_get_expr(ad.adbin, ad.adrelid) AS column_default, + CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END AS is_nullable, + CASE WHEN t.typtype = 'd' THEN + CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY' + WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null) + ELSE 'USER-DEFINED' END + ELSE + CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY' + WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null) + ELSE 'USER-DEFINED' END + END AS data_type, + CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS collation_catalog, + nco.nspname AS collation_schema, + co.collname AS collation_name, + CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END AS domain_catalog, + CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END AS domain_schema, + CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END AS domain_name, + current_database() AS udt_catalog, + coalesce(nbt.nspname, nt.nspname) AS udt_schema, + coalesce(bt.typname, t.typname) AS udt_name, + a.attnum AS dtd_identifier, + CASE WHEN c.relkind = 'r' OR + (c.relkind IN ('v', 'f') AND + pg_column_is_updatable(c.oid, a.attnum, false)) + THEN 'YES' ELSE 'NO' END AS is_updatable + FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum) + JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid + JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid + LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) + ON (t.typtype = 'd' AND t.typbasetype = bt.oid) + LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) + ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default') + WHERE (NOT pg_is_other_temp_schema(nc.oid)) + AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'm') + AND (pg_has_role(c.relowner, 'USAGE') + OR has_column_privilege(c.oid, a.attnum, + 'SELECT, INSERT, UPDATE, REFERENCES')) ) AS isc ON isc.table_schema = pgn.nspname AND isc.table_name = pgc.relname From 4b8b285e2d70e655b3efa44854b05af25c2e777e Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Mon, 30 Mar 2020 14:27:12 +0100 Subject: [PATCH 04/14] console: fixed trigger description id --- console/src/components/Services/Data/utils.js | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/console/src/components/Services/Data/utils.js b/console/src/components/Services/Data/utils.js index adcce3f85f210..8c3ebcb18039b 100644 --- a/console/src/components/Services/Data/utils.js +++ b/console/src/components/Services/Data/utils.js @@ -405,7 +405,7 @@ FROM ( end as table_type, obj_description(pgc.oid) AS comment, COALESCE(json_agg(DISTINCT row_to_json(isc) :: jsonb || jsonb_build_object('comment', col_description(pga.attrelid, pga.attnum))) filter (WHERE isc.column_name IS NOT NULL), '[]' :: json) AS columns, - COALESCE(json_agg(DISTINCT row_to_json(ist) :: jsonb || jsonb_build_object('comment', obj_description(pgt.tgrelid))) filter (WHERE ist.trigger_name IS NOT NULL), '[]' :: json) AS triggers, + COALESCE(json_agg(DISTINCT row_to_json(ist) :: jsonb || jsonb_build_object('comment', obj_description(pgt.oid))) filter (WHERE ist.trigger_name IS NOT NULL), '[]' :: json) AS triggers, row_to_json(isv) AS view_info FROM pg_class as pgc From 7dd669e0c991d257afdf5663051e36ae03437d5b Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Tue, 31 Mar 2020 00:43:43 +0100 Subject: [PATCH 05/14] tests: store graphql-engine logs as circle artifact --- .circleci/config.yml | 2 ++ 1 file changed, 2 insertions(+) diff --git a/.circleci/config.yml b/.circleci/config.yml index c423bb86b84a1..d2fb8f29a2459 100644 --- a/.circleci/config.yml +++ b/.circleci/config.yml @@ -483,6 +483,8 @@ jobs: - run: name: test console command: .circleci/test-console.sh + - store_artifacts: + path: /build/_console_output/server.log # test server upgrade from last version to current build test_server_upgrade: From b940fd03dd1ab5130a618c7b2025c0a72e8496f3 Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Wed, 1 Apr 2020 18:10:34 +0100 Subject: [PATCH 06/14] console: full support for materialized views --- .../data/materialized-views/spec.js | 457 ++++++++++++++++++ .../data/materialized-views/test.js | 53 ++ .../src/components/Common/utils/pgUtils.js | 6 +- .../Data/TableBrowseRows/ViewTable.js | 58 +-- .../Services/Data/TableCommon/TableHeader.js | 3 +- .../Data/TableModify/ModifyActions.js | 85 ++-- .../Services/Data/TableModify/ModifyTable.js | 2 +- .../Services/Data/TableModify/ModifyView.js | 26 +- .../Data/TableModify/TableCommentEditor.js | 4 +- console/src/components/Services/Data/utils.js | 37 +- 10 files changed, 635 insertions(+), 96 deletions(-) create mode 100644 console/cypress/integration/data/materialized-views/spec.js create mode 100644 console/cypress/integration/data/materialized-views/test.js diff --git a/console/cypress/integration/data/materialized-views/spec.js b/console/cypress/integration/data/materialized-views/spec.js new file mode 100644 index 0000000000000..a9322e64fe92c --- /dev/null +++ b/console/cypress/integration/data/materialized-views/spec.js @@ -0,0 +1,457 @@ +import { + getElementFromAlias, + baseUrl, + tableColumnTypeSelector, +} from '../../../helpers/dataHelpers'; + +import { + setMetaData, + validateCT, + createView, + validateColumn, + validateView, +} from '../../validators/validators'; +import { setPromptValue } from '../../../helpers/common'; + +const userId = 5555; + +export const Createtable = (name, dict) => { + cy.url().should('eq', `${baseUrl}/data/schema/public/table/add`); + cy.get(getElementFromAlias('tableName')).type(`${name}_table_vt`); + const keys = Object.keys(dict).map(k => k); + const values = Object.keys(dict).map(k => dict[k]); + for (let i = 0; i < keys.length; i += 1) { + cy.get(getElementFromAlias(`column-${i}`)).type(keys[i]); + tableColumnTypeSelector(`col-type-${i}`); + cy.get(getElementFromAlias(`data_test_column_type_value_${values[i]}`)) + .first() + .click(); + } + cy.get(getElementFromAlias('primary-key-select-0')).select('id'); + cy.get(getElementFromAlias('table-create')).click(); + cy.wait(7000); + cy.url().should( + 'eq', + `${baseUrl}/data/schema/public/tables/${name}_table_vt/modify` + ); + + validateCT(`${name}_table_vt`, 'success'); +}; + +export const passVCreateTables = () => { + cy.get(getElementFromAlias('data-create-table')).click(); + Createtable('author', { id: 'integer', name: 'text' }); + cy.get(getElementFromAlias('sidebar-add-table')).click(); + Createtable('article', { + id: 'integer', + title: 'text', + Content: 'text', + author_id: 'integer', + rating: 'integer', + }); + cy.get(getElementFromAlias('sidebar-add-table')).click(); + Createtable('comment', { + id: 'integer', + user_id: 'integer', + article_id: 'integer', + comment: 'text', + }); +}; + +export const passVCreateMaterializedViews = () => { + createView(`CREATE MATERIALIZED VIEW author_average_rating_vt AS + SELECT author_table_vt.id, avg(article_table_vt.rating) + From author_table_vt, article_table_vt + WHERE author_table_vt.id = article_table_vt.author_id + GROUP BY author_table_vt.id`); +}; + +export const passTrackTable = () => { + cy.visit('/data'); + cy.wait(7000); + cy.get( + getElementFromAlias('add-track-table-author_average_rating_vt') + ).click(); + cy.wait(7000); + // cy.get('.notification-error'); + validateView('author_average_rating_vt', 'success'); +}; + +export const passMaterializedViewRoute = () => { + cy.get(getElementFromAlias('author_average_rating_vt')).click(); + cy.url().should( + 'eq', + `${baseUrl}/data/schema/public/views/author_average_rating_vt/browse` + ); +}; + +export const passVAddDataarticle = (data, index) => { + // Click the Insert Again button. + cy.get('label') + .contains('id') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('id') + .next() + .find('input') + .last() + .type(data[0]); + cy.get('label') + .contains('title') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('title') + .next() + .find('input') + .last() + .type(data[1]); + cy.get('label') + .contains('Content') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('Content') + .next() + .find('input') + .last() + .type(data[2]); + cy.get('label') + .contains('author_id') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('author_id') + .next() + .find('input') + .last() + .type(data[3]); + cy.get('label') + .contains('rating') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('rating') + .next() + .find('input') + .last() + .type(data[4]); + if (index) { + cy.get(getElementFromAlias('insert-save-button')).click(); + } else { + cy.get(getElementFromAlias('insert-save-button')).click(); + } + + cy.wait(5000); +}; + +export const passVAddDataauthor = (data, index) => { + cy.get('label') + .contains('id') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('id') + .next() + .find('input') + .last() + .type(data[0]); + cy.get('label') + .contains('name') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('name') + .next() + .find('input') + .last() + .type(data[1]); + if (index) { + cy.get(getElementFromAlias('insert-save-button')).click(); + } else { + cy.get(getElementFromAlias('insert-save-button')).click(); + } + cy.wait(5000); +}; + +export const passVAddDatacomment = (data, index) => { + cy.get('label') + .contains('id') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('id') + .next() + .find('input') + .last() + .type(data[0]); + cy.get('label') + .contains('user_id') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('user_id') + .next() + .find('input') + .last() + .type(data[1]); + cy.get('label') + .contains('article_id') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('article_id') + .next() + .find('input') + .last() + .type(data[2]); + cy.get('label') + .contains('comment') + .next() + .find('input') + .last() + .type('{selectall}{del}'); + cy.get('label') + .contains('comment') + .next() + .find('input') + .last() + .type(data[3]); + if (index) { + cy.get(getElementFromAlias('insert-save-button')).click(); + } else { + cy.get(getElementFromAlias('insert-save-button')).click(); + } + cy.wait(5000); +}; + +const checkQuerySuccess = () => { + // Expect only 4 rows i.e. expect fifth element to not exist + cy.get('[role=gridcell]').contains(userId); + cy.get('[role=row]') + .eq(2) + .should('not.exist'); +}; + +export const passVAddData = () => { + let data; + cy.get(getElementFromAlias('article_table_vt')).click(); + cy.get(getElementFromAlias('table-insert-rows')).click(); + data = [1, 'A', 'Sontent', userId, 4]; + passVAddDataarticle(data, 0); + data = [2, 'B', 'Sontenta', 2, 4]; + passVAddDataarticle(data, 1); + data = [3, 'C', 'Sontentb', userId, 4]; + passVAddDataarticle(data, 2); + cy.get(getElementFromAlias('author_table_vt')).click(); + cy.get(getElementFromAlias('table-insert-rows')).click(); + + data = [userId, 'A']; + passVAddDataauthor(data, 0); + data = [2, 'B']; + passVAddDataauthor(data, 1); + cy.get(getElementFromAlias('comment_table_vt')).click(); + cy.get(getElementFromAlias('table-insert-rows')).click(); + + data = [1, 1, 1, 'new comment']; + passVAddDatacomment(data, 0); + data = [2, 2, 2, 'new comment']; + passVAddDatacomment(data, 1); + data = [3, 1, 2, 'new comment']; + passVAddDatacomment(data, 2); +}; + +export const passVFilterQueryEq = () => { + // Select column with type `text` + cy.get('select') + .find('option') + .contains('-- column --') + .parent() + .first() + .select('id'); + // Select operator as `eq` + cy.get('select') + .find('option') + .contains('-- op --') + .parent() + .last() + .select('$eq'); + // Type value as `filter-text` + cy.get("input[placeholder='-- value --']") + .last() + .type(userId); + // Run query + cy.get(getElementFromAlias('run-query')).click(); + cy.wait(5000); + // Check if the query was successful + checkQuerySuccess(); +}; + +const checkOrder = order => { + // Utility function to get right element + const curElement = cy.get('[role=row]'); + if (order === 'asc') { + curElement.each(($el, index) => { + if (index === 1) { + cy.wrap($el) + .find('[role=gridcell]') + .first() + .next() + .next() + .contains(2); + } + if (index === 2) { + cy.wrap($el) + .find('[role=gridcell]') + .first() + .next() + .next() + .contains(userId); + } + }); + } else { + curElement.each(($el, index) => { + if (index === 2) { + cy.wrap($el) + .find('[role=gridcell]') + .first() + .next() + .next() + .contains(2); + } + if (index === 1) { + cy.wrap($el) + .find('[role=gridcell]') + .first() + .next() + .next() + .contains(userId); + } + }); + } +}; + +export const passVAscendingSort = () => { + // Scroll to top TODO responsive is messy + cy.wait(7000); + // cy.scrollTo('top'); + // Select column with type 'serial' + cy.get('select') + .find('option') + .contains('-- column --') + .parent() + .last() + .select('id'); + // Run query + cy.get(getElementFromAlias('run-query')).click(); + // Check order + checkOrder('asc'); +}; + +export const passModifyMaterializedView = () => { + cy.get(getElementFromAlias('table-modify')).click(); + cy.get('button') + .contains('Modify') + .last() + .click(); + cy.url().should('eq', `${baseUrl}/data/sql`); +}; + +export const passVAddManualObjRel = () => { + cy.get(getElementFromAlias('author_average_rating_vt')).click(); + cy.wait(2000); + cy.get(getElementFromAlias('table-relationships')).click(); + cy.wait(2000); + cy.get(getElementFromAlias('create-edit-manual-rel')).click(); + cy.get(getElementFromAlias('manual-relationship-type')).select('object'); + cy.get("input[placeholder='Enter relationship name']").type('author'); + cy.get(getElementFromAlias('manual-relationship-ref-schema')).select( + 'public' + ); + cy.get(getElementFromAlias('manual-relationship-ref-table')).select( + 'author_table_vt' + ); + cy.get(getElementFromAlias('manual-relationship-lcol-0')).select('id'); + cy.get(getElementFromAlias('manual-relationship-rcol-0')).select('id'); + cy.get(getElementFromAlias('create-manual-rel-save')).click(); + cy.wait(7000); + validateColumn( + 'author_average_rating_vt', + ['avg', { name: 'author', columns: ['name'] }], + 'success' + ); +}; + +export const passVDeleteRelationships = () => { + cy.get(getElementFromAlias('author_average_rating_vt')).click(); + cy.get(getElementFromAlias('table-relationships')).click(); + cy.get(getElementFromAlias('relationship-toggle-editor-author')).click(); + cy.get(getElementFromAlias('relationship-remove-author')).click(); + cy.on('window:alert', str => { + return expect(str === 'Are you sure?').to.be.true; + }); + cy.wait(7000); + validateColumn( + 'author_average_rating_vt', + ['avg', { name: 'author', columns: ['name'] }], + 'failure' + ); +}; + +export const passVDeleteMaterializedView = () => { + cy.get(getElementFromAlias('table-modify')).click(); + setPromptValue('author_average_rating_vt'); + cy.get(getElementFromAlias('delete-view')).click(); + cy.window() + .its('prompt') + .should('be.called'); + cy.wait(7000); + // cy.get('.notification-error'); + validateView('author_average_rating_vt', 'failure'); +}; + +export const Deletetable = name => { + cy.get(getElementFromAlias(name)).click(); + cy.get(getElementFromAlias('table-modify')).click(); + setPromptValue(name); + cy.get(getElementFromAlias('delete-table')).click(); + cy.window() + .its('prompt') + .should('be.called'); + cy.wait(7000); + validateCT(name, 'failure'); + cy.wait(7000); +}; + +export const passVDeleteTables = () => { + Deletetable('comment_table_vt'); + Deletetable('article_table_vt'); + Deletetable('author_table_vt'); +}; + +// ////////////////////////////////////////////////////////////////////////////////////// + +export const setValidationMetaData = () => { + setMetaData(); +}; diff --git a/console/cypress/integration/data/materialized-views/test.js b/console/cypress/integration/data/materialized-views/test.js new file mode 100644 index 0000000000000..003812eb35249 --- /dev/null +++ b/console/cypress/integration/data/materialized-views/test.js @@ -0,0 +1,53 @@ +/* eslint no-unused-vars: 0 */ +/* eslint import/prefer-default-export: 0 */ + +import { + passVCreateTables, + passVCreateMaterializedViews, + passVAddData, + passTrackTable, + passVAddManualObjRel, + passVAscendingSort, + passModifyMaterializedView, + passVFilterQueryEq, + passMaterializedViewRoute, + passVDeleteRelationships, + passVDeleteMaterializedView, + passVDeleteTables, +} from './spec'; +import { testMode } from '../../../helpers/common'; +import { setMetaData } from '../../validators/validators'; + +const setup = () => { + describe('Setup route', () => { + it('Visit the index route', () => { + // Visit the index route + cy.visit('/data/schema/public'); + cy.wait(7000); + // Get and set validation metadata + setMetaData(); + }); + }); +}; + +export const runMaterializedViewsTest = () => { + describe('Materialized Views', () => { + it('Create Tables', passVCreateTables); + it('Add data to table', passVAddData); + it('Create MaterializedView', passVCreateMaterializedViews); + it('Adding it to the table', passTrackTable); + it('Check the materializedview route', passMaterializedViewRoute); + it('Ascending order MaterializedView Table', passVAscendingSort); + it('Filter the MaterializedView table', passVFilterQueryEq); + it('Modify the View', passModifyMaterializedView); + it('Adding Object Relationship to MaterializedView', passVAddManualObjRel); + it('Deleting Relationship', passVDeleteRelationships); + it('Deleting MaterializedView', passVDeleteMaterializedView); + it('Deleting Tables', passVDeleteTables); + }); +}; + +if (testMode !== 'cli') { + setup(); + runMaterializedViewsTest(); +} diff --git a/console/src/components/Common/utils/pgUtils.js b/console/src/components/Common/utils/pgUtils.js index 4b02ec3294cb0..5129990647f70 100644 --- a/console/src/components/Common/utils/pgUtils.js +++ b/console/src/components/Common/utils/pgUtils.js @@ -11,6 +11,10 @@ export const getTableSchema = table => { return table.table_schema; }; +export const getTableType = table => { + return table.table_type; +}; + // TODO: figure out better pattern for overloading fns // tableName and tableNameWithSchema are either/or arguments export const generateTableDef = ( @@ -51,7 +55,7 @@ export const getTableNameWithSchema = (tableDef, wrapDoubleQuotes = false) => { }; export const checkIfTable = table => { - return table.table_type === 'BASE TABLE'; + return table.table_type === 'TABLE'; }; export const displayTableName = table => { diff --git a/console/src/components/Services/Data/TableBrowseRows/ViewTable.js b/console/src/components/Services/Data/TableBrowseRows/ViewTable.js index 66c7d09626077..e638b3f329634 100644 --- a/console/src/components/Services/Data/TableBrowseRows/ViewTable.js +++ b/console/src/components/Services/Data/TableBrowseRows/ViewTable.js @@ -8,67 +8,13 @@ import { UPDATE_TRIGGER_ROW, UPDATE_TRIGGER_FUNCTION, } from './ViewActions'; +import { checkIfTable } from '../../../Common/utils/pgUtils'; import { setTable } from '../DataActions'; import TableHeader from '../TableCommon/TableHeader'; import ViewRows from './ViewRows'; import { NotFoundError } from '../../../Error/PageNotFound'; -const genHeadings = headings => { - if (headings.length === 0) { - return []; - } - - const heading = headings[0]; - if (typeof heading === 'string') { - return [heading, ...genHeadings(headings.slice(1))]; - } - if (typeof heading === 'object') { - if (!heading._expanded) { - const headingName = - heading.type === 'obj_rel' ? heading.lcol : heading.relname; - return [ - { name: headingName, type: heading.type }, - ...genHeadings(headings.slice(1)), - ]; - } - if (heading.type === 'obj_rel') { - const subheadings = genHeadings(heading.headings).map(h => { - if (typeof h === 'string') { - return heading.relname + '.' + h; - } - return heading.relname + '.' + h.name; - }); - return [...subheadings, ...genHeadings(headings.slice(1))]; - } - } - - throw 'Incomplete pattern match'; // eslint-disable-line no-throw-literal -}; - -const genRow = (row, headings) => { - if (headings.length === 0) { - return []; - } - - const heading = headings[0]; - if (typeof heading === 'string') { - return [row[heading], ...genRow(row, headings.slice(1))]; - } - if (typeof heading === 'object') { - if (!heading._expanded) { - const rowVal = heading.type === 'obj_rel' ? row[heading.lcol] : '[...]'; - return [rowVal, ...genRow(row, headings.slice(1))]; - } - if (heading.type === 'obj_rel') { - const subrow = genRow(row[heading.relname], heading.headings); - return [...subrow, ...genRow(row, headings.slice(1))]; - } - } - - throw 'Incomplete pattern match'; // eslint-disable-line no-throw-literal -}; - class ViewTable extends Component { constructor(props) { super(props); @@ -175,7 +121,7 @@ class ViewTable extends Component { const styles = require('../../../Common/Common.scss'); // Is this a view - const isView = tableSchema.table_type !== 'BASE TABLE'; + const isView = !checkIfTable(tableSchema); // Are there any expanded columns const viewRows = ( diff --git a/console/src/components/Services/Data/TableCommon/TableHeader.js b/console/src/components/Services/Data/TableCommon/TableHeader.js index 052e2a5cc66a2..c4ad32095d058 100644 --- a/console/src/components/Services/Data/TableCommon/TableHeader.js +++ b/console/src/components/Services/Data/TableCommon/TableHeader.js @@ -9,6 +9,7 @@ import { checkIfTable, getTableName, getTableSchema, + getTableType, } from '../../../Common/utils/pgUtils'; import { getSchemaBaseRoute, @@ -43,7 +44,7 @@ const TableHeader = ({ const activeTab = tabNameMap[tabName]; const saveTableNameChange = newName => { - dispatch(changeTableName(tableName, newName, isTable)); + dispatch(changeTableName(tableName, newName, isTable, getTableType(table))); }; const getBreadCrumbs = () => { diff --git a/console/src/components/Services/Data/TableModify/ModifyActions.js b/console/src/components/Services/Data/TableModify/ModifyActions.js index d422f5cb83b96..b400f5a8e41fa 100644 --- a/console/src/components/Services/Data/TableModify/ModifyActions.js +++ b/console/src/components/Services/Data/TableModify/ModifyActions.js @@ -715,12 +715,11 @@ const setUniqueKeys = keys => ({ keys, }); -const changeTableName = (oldName, newName, isTable) => { +const changeTableName = (oldName, newName, isTable, objectType) => { return (dispatch, getState) => { - const property = isTable ? 'table' : 'view'; - dispatch({ type: SAVE_NEW_TABLE_NAME }); + const property = objectType.toLowerCase(); if (oldName === newName) { return dispatch( showErrorNotification( @@ -742,13 +741,19 @@ const changeTableName = (oldName, newName, isTable) => { ) ); } + const compositeName = { + TABLE: 'table', + VIEW: 'view', + 'MATERIALIZED VIEW': 'materialized_view', + }[objectType]; const currentSchema = getState().tables.currentSchema; const upSql = `alter ${property} "${currentSchema}"."${oldName}" rename to "${newName}";`; const downSql = `alter ${property} "${currentSchema}"."${newName}" rename to "${oldName}";`; const migrateUp = [getRunSqlQuery(upSql)]; const migrateDown = [getRunSqlQuery(downSql)]; // apply migrations - const migrationName = `rename_${property}_` + currentSchema + '_' + oldName; + const migrationName = + `rename_${compositeName}_` + currentSchema + '_' + oldName; const requestMsg = `Renaming ${property}...`; const successMsg = `Renaming ${property} successful`; @@ -797,7 +802,7 @@ ${trigger.action_timing} ${trigger.event_manipulation} ON "${tableSchema}"."${ta FOR EACH ${trigger.action_orientation} ${trigger.action_statement};`; if (trigger.comment) { - downMigrationSql += `COMMENT ON TRIGGER "${triggerName}" ON "${tableSchema}"."${tableName}" + downMigrationSql += `COMMENT ON TRIGGER "${triggerName}" ON "${tableSchema}"."${tableName}" IS ${sqlEscapeText(trigger.comment)};`; } const migrationDown = [getRunSqlQuery(downMigrationSql)]; @@ -908,11 +913,19 @@ const untrackTableSql = tableName => { const fetchViewDefinition = (viewName, isRedirect) => { return (dispatch, getState) => { const currentSchema = getState().tables.currentSchema; - const sqlQuery = - 'select view_definition from information_schema.views where table_name = ' + - "'" + - viewName + - "'"; + const sqlQuery = ` +SELECT + CASE WHEN pg_has_role(c.relowner, 'USAGE') THEN pg_get_viewdef(c.oid) + ELSE null + END AS view_definition, + CASE WHEN c.relkind = 'v' THEN 'VIEW' ELSE 'MATERIALIZED VIEW' END AS view_type +FROM pg_class c +WHERE c.relname = '${viewName}' + AND c.relkind in ('v', 'm') + AND (pg_has_role(c.relowner, 'USAGE') + OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') + )`; const reqBody = getRunSqlQuery(sqlQuery, false, true); const url = Endpoints.query; @@ -937,17 +950,23 @@ const fetchViewDefinition = (viewName, isRedirect) => { dispatch(_push('/data/sql')); } - const runSqlDef = - 'CREATE OR REPLACE VIEW ' + - '"' + - currentSchema + - '"' + - '.' + - '"' + - viewName + - '"' + - ' AS \n' + - finalDef; + const viewType = data.result[1][1]; + const fullName = '"' + currentSchema + '"."' + viewName + '"'; + let runSqlDef = ''; + + if (viewType == 'VIEW') { + runSqlDef = + 'CREATE OR REPLACE VIEW ' + fullName + ' AS \n' + finalDef; + } else { + runSqlDef = + 'DROP MATERIALIZED VIEW ' + + fullName + + ';\n' + + 'CREATE MATERIALIZED VIEW ' + + fullName + + ' AS \n' + + finalDef; + } dispatch({ type: SET_SQL, data: runSqlDef }); }, err => { @@ -959,11 +978,21 @@ const fetchViewDefinition = (viewName, isRedirect) => { }; }; -const deleteViewSql = viewName => { +const deleteViewSql = (viewName, viewType) => { return (dispatch, getState) => { const currentSchema = getState().tables.currentSchema; + const property = viewType.toLowerCase(); + const capitalizedProperty = property[0].toUpperCase() + property.slice(1); const sqlDropView = - 'DROP VIEW ' + '"' + currentSchema + '"' + '.' + '"' + viewName + '"'; + 'DROP ' + + viewType + + ' "' + + currentSchema + + '"' + + '.' + + '"' + + viewName + + '"'; const sqlUpQueries = [getRunSqlQuery(sqlDropView)]; // const sqlCreateView = ''; //pending // const sqlDownQueries = [ @@ -976,9 +1005,9 @@ const deleteViewSql = viewName => { // Apply migrations const migrationName = 'drop_view_' + currentSchema + '_' + viewName; - const requestMsg = 'Deleting view...'; - const successMsg = 'View deleted'; - const errorMsg = 'Deleting view failed'; + const requestMsg = `Deleting ${property}...`; + const successMsg = `${capitalizedProperty} deleted`; + const errorMsg = `Deleting ${property} failed`; const customOnSuccess = () => { dispatch(_push('/data/')); @@ -1358,7 +1387,7 @@ const deleteConstraintSql = (tableName, cName) => { }; }; -const saveTableCommentSql = isTable => { +const saveTableCommentSql = tableType => { return (dispatch, getState) => { let updatedComment = getState().tables.modify.tableCommentEdit.editedValue; if (!updatedComment) { @@ -1369,7 +1398,7 @@ const saveTableCommentSql = isTable => { const commentQueryBase = 'COMMENT ON ' + - (isTable ? 'TABLE' : 'VIEW') + + tableType + ' ' + '"' + currentSchema + diff --git a/console/src/components/Services/Data/TableModify/ModifyTable.js b/console/src/components/Services/Data/TableModify/ModifyTable.js index 9790b37db7620..81abbcd0aacae 100644 --- a/console/src/components/Services/Data/TableModify/ModifyTable.js +++ b/console/src/components/Services/Data/TableModify/ModifyTable.js @@ -228,7 +228,7 @@ class ModifyTable extends React.Component { diff --git a/console/src/components/Services/Data/TableModify/ModifyView.js b/console/src/components/Services/Data/TableModify/ModifyView.js index 46b24349a8dbf..5e49bf2393d52 100644 --- a/console/src/components/Services/Data/TableModify/ModifyView.js +++ b/console/src/components/Services/Data/TableModify/ModifyView.js @@ -182,7 +182,6 @@ const ModifyView = props => { const getViewRootFieldsSection = () => { const existingRootFields = getTableCustomRootFields(tableSchema); - return (

@@ -270,7 +269,7 @@ const ModifyView = props => {

Columns

@@ -307,6 +306,7 @@ const ModifyView = props => { ModifyView.propTypes = { sql: PropTypes.string.isRequired, tableName: PropTypes.string.isRequired, + tableType: PropTypes.string.isRequired, allSchemas: PropTypes.array.isRequired, currentSchema: PropTypes.string.isRequired, activeEdit: PropTypes.object.isRequired, @@ -319,12 +319,30 @@ ModifyView.propTypes = { serverVersion: PropTypes.string, }; +const findViewType = (currentSchema, viewName, allItems) => { + for (let index = 0; index < allItems.length; index++) { + const item = allItems[index]; + if (item.table_schema === currentSchema && item.table_name === viewName) { + return item.table_type; + } + } + return 'VIEW'; +}; + const mapStateToProps = (state, ownProps) => { + const tableName = ownProps.params.table; + const schemaName = state.tables.currentSchema; + const tableType = findViewType( + schemaName, + tableName, + state.tables.allSchemas + ); return { - tableName: ownProps.params.table, + tableName: tableName, + tableType: tableType, + currentSchema: schemaName, allSchemas: state.tables.allSchemas, sql: state.rawSQL.sql, - currentSchema: state.tables.currentSchema, migrationMode: state.main.migrationMode, readOnlyMode: state.main.readOnlyMode, serverVersion: state.main.serverVersion, diff --git a/console/src/components/Services/Data/TableModify/TableCommentEditor.js b/console/src/components/Services/Data/TableModify/TableCommentEditor.js index e37035d9735be..037691fb467ba 100644 --- a/console/src/components/Services/Data/TableModify/TableCommentEditor.js +++ b/console/src/components/Services/Data/TableModify/TableCommentEditor.js @@ -10,7 +10,7 @@ import styles from './ModifyTable.scss'; const TableCommentEditor = ({ tableComment, tableCommentEdit, - isTable, + tableType, dispatch, }) => { const editCommentClicked = () => { @@ -22,7 +22,7 @@ const TableCommentEditor = ({ }; const commentEditSave = () => { - dispatch(saveTableCommentSql(isTable)); + dispatch(saveTableCommentSql(tableType)); }; const commentEditCancel = () => { diff --git a/console/src/components/Services/Data/utils.js b/console/src/components/Services/Data/utils.js index 8c3ebcb18039b..ab284b4f6c295 100644 --- a/console/src/components/Services/Data/utils.js +++ b/console/src/components/Services/Data/utils.js @@ -399,7 +399,7 @@ FROM ( pgn.nspname as table_schema, pgc.relname as table_name, case - when pgc.relkind = 'r' then 'BASE TABLE' + when pgc.relkind = 'r' then 'TABLE' when pgc.relkind = 'v' then 'VIEW' when pgc.relkind = 'm' then 'MATERIALIZED VIEW' end as table_type, @@ -413,6 +413,10 @@ FROM ( ON pgc.relnamespace = pgn.oid /* columns */ + /* This is a simplified version of how information_schema.columns was + ** implemented in postgres 9.5, but modified to support materialized + ** views. + */ LEFT OUTER JOIN pg_attribute AS pga ON pga.attrelid = pgc.oid LEFT OUTER JOIN ( @@ -472,10 +476,37 @@ FROM ( AND ist.event_object_table = pgc.relname AND ist.trigger_name = pgt.tgname - /* views */ - LEFT OUTER JOIN information_schema.views AS isv + /* This is a simplified version of how information_schema.views was + ** implemented in postgres 9.5, but modified to support materialized + ** views. + */ + LEFT OUTER JOIN ( + SELECT + current_database() AS table_catalog, + nc.nspname AS table_schema, + c.relname AS table_name, + CASE WHEN pg_has_role(c.relowner, 'USAGE') THEN pg_get_viewdef(c.oid) ELSE null END AS view_definition, + CASE WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED' + WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL' + ELSE 'NONE' + END AS check_option, + CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20 THEN 'YES' ELSE 'NO' END AS is_updatable, + CASE WHEN pg_relation_is_updatable(c.oid, false) & 8 = 8 THEN 'YES' ELSE 'NO' END AS is_insertable_into, + CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81) THEN 'YES' ELSE 'NO' END AS is_trigger_updatable, + CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73) THEN 'YES' ELSE 'NO' END AS is_trigger_deletable, + CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69) THEN 'YES' ELSE 'NO' END AS is_trigger_insertable_into + FROM pg_namespace nc, pg_class c + + WHERE c.relnamespace = nc.oid + AND c.relkind in ('v', 'm') + AND (NOT pg_is_other_temp_schema(nc.oid)) + AND (pg_has_role(c.relowner, 'USAGE') + OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')) + ) AS isv ON isv.table_schema = pgn.nspname AND isv.table_name = pgc.relname + WHERE pgc.relkind IN ('r', 'v', 'm') ${whereQuery} From a8dfbc7b95915ff4351ae27feca80f443e85b003 Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Thu, 2 Apr 2020 10:35:49 +0100 Subject: [PATCH 07/14] update changelog --- CHANGELOG.md | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/CHANGELOG.md b/CHANGELOG.md index e769738f9d7f0..7c3e23d421319 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -10,6 +10,10 @@ ## `v1.2.0-beta.3` +### console: support for Postgres [materialized views](https://www.postgresql.org/docs/current/rules-materializedviews.html) + +Postgres materialized views are views that are persisted in a table-like form. They are now supported in the Hasura Console, in the same way as views. They will appear on the 'Schema' page, under the 'Data' tab, in the 'Untracked tables or views' section. + ### console: manage Postgres check constraints Postgres Check constraints allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression. They can be used to put in simple input validations for mutations and with this release, these constraints can now be added while creating a table or later from Modify tab on the console. From d31f5fef666fe5483f9e941772aaeabefc3b9996 Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Thu, 2 Apr 2020 11:41:37 +0100 Subject: [PATCH 08/14] console: fix post-rebase mistakes --- console/src/components/Services/Data/TableModify/ModifyView.js | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/console/src/components/Services/Data/TableModify/ModifyView.js b/console/src/components/Services/Data/TableModify/ModifyView.js index 5e49bf2393d52..45b324246fd37 100644 --- a/console/src/components/Services/Data/TableModify/ModifyView.js +++ b/console/src/components/Services/Data/TableModify/ModifyView.js @@ -33,6 +33,7 @@ const ModifyView = props => { const { sql, tableName, + tableType, allSchemas, ongoingRequest, lastError, @@ -239,7 +240,7 @@ const ModifyView = props => { const confirmMessage = `This will permanently delete the view "${tableName}" from the database`; const isOk = getConfirmation(confirmMessage, true, tableName); if (isOk) { - dispatch(deleteViewSql(tableName)); + dispatch(deleteViewSql(tableName, tableType)); } }; const deleteBtn = ( From 4c2f1150b92f871332637c10868ed736ada72cd6 Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Thu, 2 Apr 2020 16:30:28 +0100 Subject: [PATCH 09/14] applied all suggestions --- .../src/components/Common/utils/jsUtils.js | 4 +++ .../Services/Data/TableCommon/TableHeader.js | 5 ++- .../Data/TableModify/ModifyActions.js | 33 +++++++------------ .../Services/Data/TableModify/ModifyTable.js | 2 +- .../Services/Data/TableModify/ModifyView.js | 3 +- .../EventTrigger/TableCommon/TableHeader.js | 10 ++++-- 6 files changed, 27 insertions(+), 30 deletions(-) diff --git a/console/src/components/Common/utils/jsUtils.js b/console/src/components/Common/utils/jsUtils.js index 648f95385401a..6ea0819bf7ce7 100644 --- a/console/src/components/Common/utils/jsUtils.js +++ b/console/src/components/Common/utils/jsUtils.js @@ -78,6 +78,10 @@ export function isJsonString(str) { return true; } +export function capitalize(str) { + return str[0].toUpperCase() + str.slice(1); +} + export function getAllJsonPaths(json, leafKeys = [], prefix = '') { const _paths = []; diff --git a/console/src/components/Services/Data/TableCommon/TableHeader.js b/console/src/components/Services/Data/TableCommon/TableHeader.js index c4ad32095d058..9d4d4f3b89e06 100644 --- a/console/src/components/Services/Data/TableCommon/TableHeader.js +++ b/console/src/components/Services/Data/TableCommon/TableHeader.js @@ -2,6 +2,7 @@ import React from 'react'; import { Link } from 'react-router'; import Helmet from 'react-helmet'; import { changeTableName } from '../TableModify/ModifyActions'; +import { capitalize } from '../../Common/utils/jsUtils'; import EditableHeading from '../../../Common/EditableHeading/EditableHeading'; import BreadCrumb from '../../../Common/Layout/BreadCrumb/BreadCrumb'; import { tabNameMap } from '../utils'; @@ -31,8 +32,6 @@ const TableHeader = ({ }) => { const styles = require('../../../Common/TableCommon/Table.scss'); - const capitalisedTabName = tabName[0].toUpperCase() + tabName.slice(1); - const tableName = getTableName(table); const tableSchema = getTableSchema(table); const isTable = checkIfTable(table); @@ -85,7 +84,7 @@ const TableHeader = ({ return (
diff --git a/console/src/components/Services/Data/TableModify/ModifyActions.js b/console/src/components/Services/Data/TableModify/ModifyActions.js index b400f5a8e41fa..ba80b9120b00f 100644 --- a/console/src/components/Services/Data/TableModify/ModifyActions.js +++ b/console/src/components/Services/Data/TableModify/ModifyActions.js @@ -34,6 +34,7 @@ import { getDropConstraintSql, getDropPkSql, getCreatePkSql, + capitalize, } from '../../../Common/utils/sqlUtils'; import { getConfirmation } from '../../../Common/utils/jsUtils'; import { @@ -586,14 +587,14 @@ const saveForeignKeys = (index, tableSchema, columns) => { alter table "${schemaName}"."${tableName}" drop constraint "${generatedConstraintName}", add constraint "${constraintName}" foreign key (${Object.keys(oldConstraint.column_mapping) - .map(lc => `"${lc}"`) - .join(', ')}) + .map(lc => `"${lc}"`) + .join(', ')}) references "${oldConstraint.ref_table_table_schema}"."${ - oldConstraint.ref_table - }" + oldConstraint.ref_table +}" (${Object.values(oldConstraint.column_mapping) - .map(rc => `"${rc}"`) - .join(', ')}) + .map(rc => `"${rc}"`) + .join(', ')}) on update ${pgConfTypes[oldConstraint.on_update]} on delete ${pgConfTypes[oldConstraint.on_delete]}; `; @@ -715,11 +716,11 @@ const setUniqueKeys = keys => ({ keys, }); -const changeTableName = (oldName, newName, isTable, objectType) => { +const changeTableName = (oldName, newName, isTable, tableType) => { return (dispatch, getState) => { dispatch({ type: SAVE_NEW_TABLE_NAME }); - const property = objectType.toLowerCase(); + const property = tableType.toLowerCase(); if (oldName === newName) { return dispatch( showErrorNotification( @@ -745,7 +746,7 @@ const changeTableName = (oldName, newName, isTable, objectType) => { TABLE: 'table', VIEW: 'view', 'MATERIALIZED VIEW': 'materialized_view', - }[objectType]; + }[tableType]; const currentSchema = getState().tables.currentSchema; const upSql = `alter ${property} "${currentSchema}"."${oldName}" rename to "${newName}";`; const downSql = `alter ${property} "${currentSchema}"."${newName}" rename to "${oldName}";`; @@ -982,17 +983,7 @@ const deleteViewSql = (viewName, viewType) => { return (dispatch, getState) => { const currentSchema = getState().tables.currentSchema; const property = viewType.toLowerCase(); - const capitalizedProperty = property[0].toUpperCase() + property.slice(1); - const sqlDropView = - 'DROP ' + - viewType + - ' "' + - currentSchema + - '"' + - '.' + - '"' + - viewName + - '"'; + const sqlDropView = `DROP ${viewType} "${currentSchema}"."${viewName}"`; const sqlUpQueries = [getRunSqlQuery(sqlDropView)]; // const sqlCreateView = ''; //pending // const sqlDownQueries = [ @@ -1006,7 +997,7 @@ const deleteViewSql = (viewName, viewType) => { const migrationName = 'drop_view_' + currentSchema + '_' + viewName; const requestMsg = `Deleting ${property}...`; - const successMsg = `${capitalizedProperty} deleted`; + const successMsg = `${capitalize(property)} deleted`; const errorMsg = `Deleting ${property} failed`; const customOnSuccess = () => { diff --git a/console/src/components/Services/Data/TableModify/ModifyTable.js b/console/src/components/Services/Data/TableModify/ModifyTable.js index 81abbcd0aacae..4766f9788cf10 100644 --- a/console/src/components/Services/Data/TableModify/ModifyTable.js +++ b/console/src/components/Services/Data/TableModify/ModifyTable.js @@ -228,7 +228,7 @@ class ModifyTable extends React.Component { diff --git a/console/src/components/Services/Data/TableModify/ModifyView.js b/console/src/components/Services/Data/TableModify/ModifyView.js index 45b324246fd37..0dd4854a5d876 100644 --- a/console/src/components/Services/Data/TableModify/ModifyView.js +++ b/console/src/components/Services/Data/TableModify/ModifyView.js @@ -321,8 +321,7 @@ ModifyView.propTypes = { }; const findViewType = (currentSchema, viewName, allItems) => { - for (let index = 0; index < allItems.length; index++) { - const item = allItems[index]; + for (const item of allItems) { if (item.table_schema === currentSchema && item.table_name === viewName) { return item.table_type; } diff --git a/console/src/components/Services/EventTrigger/TableCommon/TableHeader.js b/console/src/components/Services/EventTrigger/TableCommon/TableHeader.js index d36e9527e691f..ff99063588270 100644 --- a/console/src/components/Services/EventTrigger/TableCommon/TableHeader.js +++ b/console/src/components/Services/EventTrigger/TableCommon/TableHeader.js @@ -1,12 +1,11 @@ import React from 'react'; import { Link } from 'react-router'; import Helmet from 'react-helmet'; +import { capitalize } from '../../../Common/utils/jsUtils'; import BreadCrumb from '../../../Common/Layout/BreadCrumb/BreadCrumb'; const TableHeader = ({ triggerName, tabName, count, readOnlyMode }) => { const styles = require('./EventTable.scss'); - let capitalised = tabName; - capitalised = capitalised[0].toUpperCase() + capitalised.slice(1); let showCount = ''; if (!(count === null || count === undefined)) { showCount = '(' + count + ')'; @@ -50,7 +49,12 @@ const TableHeader = ({ triggerName, tabName, count, readOnlyMode }) => { return (
From 5f3a71538722e3bb40e4e1f73de8807138aa05a3 Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Thu, 2 Apr 2020 16:32:39 +0100 Subject: [PATCH 10/14] applied more suggestions --- .../src/components/Services/Data/TableModify/ModifyView.js | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/console/src/components/Services/Data/TableModify/ModifyView.js b/console/src/components/Services/Data/TableModify/ModifyView.js index 0dd4854a5d876..8e83fb58bef65 100644 --- a/console/src/components/Services/Data/TableModify/ModifyView.js +++ b/console/src/components/Services/Data/TableModify/ModifyView.js @@ -338,8 +338,8 @@ const mapStateToProps = (state, ownProps) => { state.tables.allSchemas ); return { - tableName: tableName, - tableType: tableType, + tableName, + tableType, currentSchema: schemaName, allSchemas: state.tables.allSchemas, sql: state.rawSQL.sql, From d6cc5d7f1e83b534b81e52098168bb1b55263e49 Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Thu, 2 Apr 2020 16:53:59 +0100 Subject: [PATCH 11/14] fix compilation --- .../src/components/Services/Data/TableCommon/TableHeader.js | 2 +- .../src/components/Services/Data/TableModify/ModifyActions.js | 3 +-- 2 files changed, 2 insertions(+), 3 deletions(-) diff --git a/console/src/components/Services/Data/TableCommon/TableHeader.js b/console/src/components/Services/Data/TableCommon/TableHeader.js index 9d4d4f3b89e06..f74a562a69dd9 100644 --- a/console/src/components/Services/Data/TableCommon/TableHeader.js +++ b/console/src/components/Services/Data/TableCommon/TableHeader.js @@ -2,7 +2,7 @@ import React from 'react'; import { Link } from 'react-router'; import Helmet from 'react-helmet'; import { changeTableName } from '../TableModify/ModifyActions'; -import { capitalize } from '../../Common/utils/jsUtils'; +import { capitalize } from '../../../Common/utils/jsUtils'; import EditableHeading from '../../../Common/EditableHeading/EditableHeading'; import BreadCrumb from '../../../Common/Layout/BreadCrumb/BreadCrumb'; import { tabNameMap } from '../utils'; diff --git a/console/src/components/Services/Data/TableModify/ModifyActions.js b/console/src/components/Services/Data/TableModify/ModifyActions.js index ba80b9120b00f..07dfd2cbd3778 100644 --- a/console/src/components/Services/Data/TableModify/ModifyActions.js +++ b/console/src/components/Services/Data/TableModify/ModifyActions.js @@ -34,9 +34,8 @@ import { getDropConstraintSql, getDropPkSql, getCreatePkSql, - capitalize, } from '../../../Common/utils/sqlUtils'; -import { getConfirmation } from '../../../Common/utils/jsUtils'; +import { getConfirmation, capitalize } from '../../../Common/utils/jsUtils'; import { findTable, generateTableDef, From c7e697e18048bd15a9e91dbd95bcc4c4668b77d1 Mon Sep 17 00:00:00 2001 From: Antoine Leblanc Date: Thu, 2 Apr 2020 18:33:18 +0100 Subject: [PATCH 12/14] fix case in tests --- .../data/materialized-views/spec.js | 16 ++++++++-------- console/cypress/integration/data/views/spec.js | 18 +++++++++--------- 2 files changed, 17 insertions(+), 17 deletions(-) diff --git a/console/cypress/integration/data/materialized-views/spec.js b/console/cypress/integration/data/materialized-views/spec.js index a9322e64fe92c..a0a7674759fd7 100644 --- a/console/cypress/integration/data/materialized-views/spec.js +++ b/console/cypress/integration/data/materialized-views/spec.js @@ -15,7 +15,7 @@ import { setPromptValue } from '../../../helpers/common'; const userId = 5555; -export const Createtable = (name, dict) => { +export const createTable = (name, dict) => { cy.url().should('eq', `${baseUrl}/data/schema/public/table/add`); cy.get(getElementFromAlias('tableName')).type(`${name}_table_vt`); const keys = Object.keys(dict).map(k => k); @@ -40,9 +40,9 @@ export const Createtable = (name, dict) => { export const passVCreateTables = () => { cy.get(getElementFromAlias('data-create-table')).click(); - Createtable('author', { id: 'integer', name: 'text' }); + createTable('author', { id: 'integer', name: 'text' }); cy.get(getElementFromAlias('sidebar-add-table')).click(); - Createtable('article', { + createTable('article', { id: 'integer', title: 'text', Content: 'text', @@ -50,7 +50,7 @@ export const passVCreateTables = () => { rating: 'integer', }); cy.get(getElementFromAlias('sidebar-add-table')).click(); - Createtable('comment', { + createTable('comment', { id: 'integer', user_id: 'integer', article_id: 'integer', @@ -431,7 +431,7 @@ export const passVDeleteMaterializedView = () => { validateView('author_average_rating_vt', 'failure'); }; -export const Deletetable = name => { +export const deleteTable = name => { cy.get(getElementFromAlias(name)).click(); cy.get(getElementFromAlias('table-modify')).click(); setPromptValue(name); @@ -445,9 +445,9 @@ export const Deletetable = name => { }; export const passVDeleteTables = () => { - Deletetable('comment_table_vt'); - Deletetable('article_table_vt'); - Deletetable('author_table_vt'); + deleteTable('comment_table_vt'); + deleteTable('article_table_vt'); + deleteTable('author_table_vt'); }; // ////////////////////////////////////////////////////////////////////////////////////// diff --git a/console/cypress/integration/data/views/spec.js b/console/cypress/integration/data/views/spec.js index 7dc572c1fd26a..42e904caffdfc 100644 --- a/console/cypress/integration/data/views/spec.js +++ b/console/cypress/integration/data/views/spec.js @@ -15,7 +15,7 @@ import { setPromptValue } from '../../../helpers/common'; const userId = 5555; -export const Createtable = (name, dict) => { +export const createTable = (name, dict) => { cy.url().should('eq', `${baseUrl}/data/schema/public/table/add`); cy.get(getElementFromAlias('tableName')).type(`${name}_table_vt`); const keys = Object.keys(dict).map(k => k); @@ -40,9 +40,9 @@ export const Createtable = (name, dict) => { export const passVCreateTables = () => { cy.get(getElementFromAlias('data-create-table')).click(); - Createtable('author', { id: 'integer', name: 'text' }); + createTable('author', { id: 'integer', name: 'text' }); cy.get(getElementFromAlias('sidebar-add-table')).click(); - Createtable('article', { + createTable('article', { id: 'integer', title: 'text', Content: 'text', @@ -50,7 +50,7 @@ export const passVCreateTables = () => { rating: 'integer', }); cy.get(getElementFromAlias('sidebar-add-table')).click(); - Createtable('comment', { + createTable('comment', { id: 'integer', user_id: 'integer', article_id: 'integer', @@ -409,7 +409,7 @@ export const passVDeleteRelationships = () => { cy.get(getElementFromAlias('relationship-toggle-editor-author')).click(); cy.get(getElementFromAlias('relationship-remove-author')).click(); cy.on('window:alert', str => { - expect(str === 'Are you sure?').to.be.true; + return expect(str === 'Are you sure?').to.be.true; }); cy.wait(7000); validateColumn( @@ -431,7 +431,7 @@ export const passVDeleteView = () => { validateView('author_average_rating_vt', 'failure'); }; -export const Deletetable = name => { +export const deleteTable = name => { cy.get(getElementFromAlias(name)).click(); cy.get(getElementFromAlias('table-modify')).click(); setPromptValue(name); @@ -445,9 +445,9 @@ export const Deletetable = name => { }; export const passVDeleteTables = () => { - Deletetable('comment_table_vt'); - Deletetable('article_table_vt'); - Deletetable('author_table_vt'); + deleteTable('comment_table_vt'); + deleteTable('article_table_vt'); + deleteTable('author_table_vt'); }; // ////////////////////////////////////////////////////////////////////////////////////// From 61a6754901f37b085a1f1ee4872958f5012df8a5 Mon Sep 17 00:00:00 2001 From: rikinsk Date: Thu, 23 Apr 2020 13:26:15 +0530 Subject: [PATCH 13/14] update changelog --- CHANGELOG.md | 10 ++++++---- 1 file changed, 6 insertions(+), 4 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index c195035db5fe6..ba54bf67e0255 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -23,6 +23,12 @@ Along with the check for filtering rows that can be updated, you can now set a p (close #4142) (#4313) +### console: support for Postgres [materialized views](https://www.postgresql.org/docs/current/rules-materializedviews.html) + +Postgres materialized views are views that are persisted in a table-like form. They are now supported in the Hasura Console, in the same way as views. They will appear on the 'Schema' page, under the 'Data' tab, in the 'Untracked tables or views' section. + +(close #91) (#4270) + ### Bug fixes and improvements - server: add support for `_inc` on `real`, `double`, `numeric` and `money` (fix #3573) @@ -83,10 +89,6 @@ The order, collapsed state of columns and rows limit is now persisted across pag ## `v1.2.0-beta.3` -### console: support for Postgres [materialized views](https://www.postgresql.org/docs/current/rules-materializedviews.html) - -Postgres materialized views are views that are persisted in a table-like form. They are now supported in the Hasura Console, in the same way as views. They will appear on the 'Schema' page, under the 'Data' tab, in the 'Untracked tables or views' section. - ### console: manage Postgres check constraints Postgres Check constraints allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression. They can be used to put in simple input validations for mutations and with this release, these constraints can now be added while creating a table or later from Modify tab on the console. From 44f06ff87022a01aa476373a13324315cb5988e7 Mon Sep 17 00:00:00 2001 From: rikinsk Date: Fri, 24 Apr 2020 12:47:50 +0530 Subject: [PATCH 14/14] readd missed fn --- .../src/components/Common/utils/jsUtils.js | 38 +++++++++++++++++++ 1 file changed, 38 insertions(+) diff --git a/console/src/components/Common/utils/jsUtils.js b/console/src/components/Common/utils/jsUtils.js index 85ce1980298f3..2b93e5d56e614 100644 --- a/console/src/components/Common/utils/jsUtils.js +++ b/console/src/components/Common/utils/jsUtils.js @@ -121,6 +121,44 @@ export const arrayDiff = (arr1, arr2) => { /* JSON utils */ +export const getAllJsonPaths = (json, leafKeys = [], prefix = '') => { + const _paths = []; + + const addPrefix = subPath => { + return prefix + (prefix && subPath ? '.' : '') + subPath; + }; + + const handleSubJson = (subJson, newPrefix) => { + const subPaths = getAllJsonPaths(subJson, leafKeys, newPrefix); + + subPaths.forEach(subPath => { + _paths.push(subPath); + }); + + if (!subPaths.length) { + _paths.push(newPrefix); + } + }; + + if (isArray(json)) { + json.forEach((subJson, i) => { + handleSubJson(subJson, addPrefix(i.toString())); + }); + } else if (isObject(json)) { + Object.keys(json).forEach(key => { + if (leafKeys.includes(key)) { + _paths.push({ [addPrefix(key)]: json[key] }); + } else { + handleSubJson(json[key], addPrefix(key)); + } + }); + } else { + _paths.push(addPrefix(json)); + } + + return _paths; +}; + /* TRANSFORM utils*/ export const capitalize = s => {