2023-02-03

Access Path Suggestor and Generator -Mariadb

Earlier I have published accesspath suggestors for Oracle and Postgresql. Those are interpreting accesspath bidirectionally. Here is one for Mariadb. I quess it suits also for Mysql. I have not tried. This traverses the foreign key tree only in one direction. So the dependencies from a :roottable to :goaltablename in a :tableschema are reported. Also a query for the whole accesspath is generated.
with recursive fks as (
SELECT constraint_name,
       table_name,
       column_name,
       referenced_table_name,
       referenced_column_name
  FROM information_schema.key_column_usage
 WHERE table_schema = :tableschema 
   AND referenced_column_name IS NOT NULL
), pths as (
 select 1 lvl,
        cast(concat('/',table_name) as varchar(4000)) pth,
        constraint_name,
        table_name,
        column_name,
        referenced_table_name,
        referenced_column_name,
        cast(concat(referenced_table_name,', ',table_name,' ',constraint_name) as varchar(4000)) fro,
        cast(concat(referenced_table_name,'.',referenced_column_name,' = ',constraint_name,'.',column_name) as varchar(4000)) joi
   from fks
  where referenced_table_name = :roottablename
  union all
 select r.lvl+1,
        concat(r.pth,'/',c.table_name),
        c.constraint_name,
        c.table_name,
        c.column_name,
        c.referenced_table_name,
        c.referenced_column_name,
        concat(r.fro,', ',c.table_name,' ',c.constraint_name),
        concat(r.joi,' and ',r.constraint_name,'.',c.referenced_column_name,' = ',c.constraint_name,'.',c.column_name)
   from pths r, fks c
  where r.table_name  = c.referenced_table_name
) cycle table_name, referenced_table_name restrict
select concat('select * from ',fro,' where ',joi,';') sq
     , pths.pth
  from pths
 where table_name = :goaltablename
;

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.