0

I have this data:

CREATE TABLE `ViewDependencies` (
  `ViewName` varchar(255) DEFAULT NULL,
  `DependentOn` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `ViewDependencies` (`ViewName`, `DependentOn`) VALUES
('Contacts.NaturalPersonViews', 'Contacts.NaturalPersons'),
('Contacts.NaturalPersonViews', 'Contacts.ContactInheritanceViews'),
('Contacts.NaturalPersonViews', 'Contacts.Persons'),
('Contacts.PersonViews',    'Contacts.PersonBaseViews'),
('Contacts.PersonViews',    'Contacts.ContactInheritanceViews'),
('Contacts.NaturalPersonCompositionViews',  'Contacts.NaturalPersons'),
('Contacts.NaturalPersonCompositionViews',  'Contacts.PersonCompositionViews'),
('Contacts.PersonBaseViews',    'Contacts.Persons'),
('Contacts.PersonBaseViews',    'Contacts.NaturalPersons'),
('Contacts.PersonBaseViews',    'Contacts.JuridicalPersons'),
('Contacts.NaturalPersonInheritanceViews',  'Contacts.NaturalPersons'),
('Contacts.NaturalPersonInheritanceViews',  'Contacts.PersonInheritanceViews'),
('Contacts.PersonCompositionViews', 'Contacts.PersonBaseViews'),
('Contacts.PersonCompositionViews', 'Contacts.ContactCompositionViews'),
('Contacts.RelationViews',  'Contacts.Relations'),
('Contacts.RelationViews',  'Contacts.JobTitles'),
('Contacts.RelationViews',  'Contacts.JuridicalPersonCompositionViews'),
('Contacts.RelationViews',  'Contacts.NaturalPersonCompositionViews'),
('Contacts.BankAccountViews',   'Contacts.BankAccounts'),
('Contacts.BankAccountViews',   'Contacts.PersonCompositionViews'),
('Contacts.AddressCompositionViews',    'Contacts.Addresses'),
('Contacts.AddressCompositionViews',    'Geo.LocationCompositionViews'),
('Contacts.JuridicalPersonInheritanceViews',    'Contacts.JuridicalPersons'),
('Contacts.JuridicalPersonInheritanceViews',    'Contacts.PersonInheritanceViews'),
('Contacts.ContactInheritanceViews',    'Contacts.Contacts'),
('Contacts.JuridicalPersonCompositionViews',    'Contacts.JuridicalPersons'),
('Contacts.JuridicalPersonCompositionViews',    'Contacts.ContactInheritanceViews'),
('Contacts.JuridicalPersonCompositionViews',    'Contacts.Persons'),
('Contacts.JuridicalPersonViews',   'Contacts.JuridicalPersons'),
('Contacts.JuridicalPersonViews',   'Contacts.ContactInheritanceViews'),
('Contacts.JuridicalPersonViews',   'Contacts.Persons'),
('Contacts.SocialProfileViews', 'Contacts.SocialProfiles'),
('Contacts.SocialProfileViews', 'Contacts.SocialNetworks'),
('Contacts.PersonInheritanceViews', 'Contacts.PersonBaseViews'),
('Contacts.PersonInheritanceViews', 'Contacts.ContactInheritanceViews'),
('Contacts.ContactCompositionViews',    'Contacts.Contacts'),
('Contacts.AddressViews',   'Contacts.Addresses'),
('Contacts.AddressViews',   'Geo.LocationCompositionViews'),
('Contacts.PhoneViews', 'Contacts.Phones'),
('Contacts.PhoneViews', 'Contacts.Contacts'),
('Contacts.PhoneViews', 'Geo.Countries');

This shows the dependencies between views and tables.

Some views like Contacts.PersonBaseViews are not dependent upon any other views. Some are just simple tables and they are also not dependent upon other views of course.

Some views, however, are dependent on other views.

I'm creating a tool to generate a database. For that, I need to create the least dependent views first. In other words, I need to create views based on their dependency graph.

Yet I'm stuck at this point.

How can I get a sorted result from the least dependent view, to the most dependent view (which means I need to exclude tables from the result, and I need to include views that are only inside the DependentOn column).

This is the db-fiddle

1
  • you will nedd mysql8 and a recursive cte
    – nbk
    Mar 12 at 10:53

1 Answer 1

1
WITH RECURSIVE
cte AS (
  SELECT DependentOn ViewName, 1 level FROM ViewDependencies
  UNION
  SELECT ViewDependencies.ViewName, cte.level + 1
  FROM ViewDependencies
  JOIN cte ON cte.ViewName = ViewDependencies.DependentOn
  )
SELECT ViewName, MAX(level) level
FROM cte
GROUP BY 1
ORDER BY 2;
ViewName level
Contacts.Relations 1
Contacts.SocialProfiles 1
Contacts.Contacts 1
Geo.LocationCompositionViews 1
Contacts.Addresses 1
Contacts.BankAccounts 1
Contacts.SocialNetworks 1
Contacts.Phones 1
Contacts.JobTitles 1
Geo.Countries 1
Contacts.JuridicalPersons 1
Contacts.Persons 1
Contacts.NaturalPersons 1
Contacts.PhoneViews 2
Contacts.AddressCompositionViews 2
Contacts.SocialProfileViews 2
Contacts.AddressViews 2
Contacts.ContactCompositionViews 2
Contacts.PersonBaseViews 2
Contacts.ContactInheritanceViews 2
Contacts.JuridicalPersonCompositionViews 3
Contacts.NaturalPersonViews 3
Contacts.PersonViews 3
Contacts.PersonInheritanceViews 3
Contacts.JuridicalPersonViews 3
Contacts.PersonCompositionViews 3
Contacts.NaturalPersonCompositionViews 4
Contacts.NaturalPersonInheritanceViews 4
Contacts.BankAccountViews 4
Contacts.JuridicalPersonInheritanceViews 4
Contacts.RelationViews 5

fiddle

Recommendation: after all views with level=1 processed requery your data excluding the rows which uses the views which are already processed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.