DaDaBIK
This forum is devoted to the discussion about the software DaDaBIK database front-end
the PHP Database application generator for MySQL, PostgreSQL and SQLite; also available for Wordpress.

Welcome! Log In Create A New Profile

Advanced

Materialized Views

Posted by darren 
Materialized Views
July 06, 2018 12:37PM
Hello,

I would just like to request the support of PostgreSQL materialized views, I dont know how DaDaBik differentiates the views and non views, but materialized view do not show up as available to import under the "not installed tables/views". While I am sure this is not an easy change, materialized views serve a great purpose of offputting server load but not needing to be updated constantly and make data thats pulled from multiple tables much quicker to query.

Thank you.
Re: Materialized Views
July 25, 2018 04:50PM
I managed to change dadabik to include materialized views by changing two lines int db_functions_pdo

I changed the line from
select table_name as name, 'table' as type, table_schema from information_schema.tables where table_schema not in ('pg_catalog','information_schema') union select table_name as name, 'view' as type, table_schema  from information_schema.views where table_schema not in ( 'pg_catalog','information_schema')

to

select table_name as name, 'table' as type, table_schema from information_schema.tables where table_schema not in ('pg_catalog','information_schema') union select table_name as name, 'view' as type, table_schema  from information_schema.views where table_schema not in ( 'pg_catalog','information_schema') union select matviewname as name, 'view' as type, schemaname as table_schema from pg_matviews order by name

I just added a third union

and the other line

SELECT column_name, data_type, is_nullable, character_maximum_length FROM information_schema.columns WHERE table_name ='".$table_name."' order by ordinal_position"

to

select attname as column_name, format_type(atttypid, atttypmod) as data_type, case when attnotnull::integer = '0' then 'YES' else 'NO' end as is_nullable, null as character_maximum_length from pg_catalog.pg_attribute where attrelid = '".$table_name."'::regclass and not attisdropped and attstattarget != '0' order by attnum

it selects from the pg_catalog.pg_attribute table which is much more comprehensive than information_schema.columns

hope this is useful to anyone, cause it sure was for me!
Re: Materialized Views
July 26, 2018 01:50AM
Interesting, thanks for sharing! I'll go through it and I'll probably add this to the next release.

Best,

________________________________________________________
Eugenio |\.-./| DaDaBIK developer and project admin
Sorry, only registered users may post in this forum.

Click here to login