Loop vast in Union statement

Door - DHU - op 29-11-2020 16:25

5.053 views

Hallo allemaal,

Ik schaar mezelf nog steeds onder beginners hoewel eea wel duidelijker gaat worden. Nu wil ik vervolgstap zien te maken maar ik staar me blind om gevens uit twee tabellen met elkaar te vergelijken.

De stituatie is:

Er bestaat een administratie waarin personen rbac geautoriseerd worden volgens het principe Medewerker->Rol->Activiteit-Autorisatie. Dit noemen we de Soll. Daarnaast bestaat er applicatie waarbinnen gebruikers worden geautoriseerd voor bepaalde rechten. Dit noemen we de Ist.

De praktijk is:
Personen stromen in, door en weer uit. Mensen zijn dus in beweging en daardoor veranderd ook de behoefte aan autorisaties.

De wens is:
Periodiek een controle uitvoeren over users met hun rechten (ist) nog steeds overeenkomen met de administratie (soll). Daarvoor importeer ik mbv 3-tal ist-bestanden (autorisatie, user2autorisatie en users) in eigen tabellen.

Maar vanaf dit punt wordt het lastiger... deze bestanden moeten met elkaar vergeleken worden en daar waar verschillen onstaan moet dit zichbaar gaan worden. Wanneer iemand volgens de administratie een autorisatie heeft maar in werkelijkheid niet op het systeem hebben we te maken met een soll-verschil. Heeft iemand een autorisatie binnen de applicatie wel maar niet via de administratie dan hebben we een ist-verschil (mogelijk risico?).

Nu dacht ik de UNION of UNION ALL statement te gebruiken. Zie hieronder. Ik heb een tool FlySpeed SQL Query gebruikt maar die zet all gegevens onder elkaar terwijl voor het gemak de Soll (S) en Ist (I) juist naast elkaar worden getoond en zodoende eenvoudig gegroepeerd overzicht krijg..

De code die ik in elkaar heb geflanst is:


global $connection;

			$where = NULL;
			if (isset($_GET['doelsysteem'])){
				$where = 'WHERE MSKEYVALUE_DOELSYSTEEM = "'.mysqli_real_escape_string($connection, $_GET['doelsysteem']).'"';
			}
		
		$sqlUitlezen = mysqli_query($connection, "Select
			bigpicture.idm_autorisatie.MSKEYVALUE_DOELSYSTEEM,
			bigpicture.idm_ou.MSKEYVALUE_OU As S_OU,
			bigpicture.idm_ou.Z_ORG_DN As S_organisatie,
			Count(bigpicture.idm_autorisatie.MSKEYVALUE_AUTORISATIE) As soll
		From
			bigpicture.idm_autorisatie
			Left Join bigpicture.idm_act2aut On bigpicture.idm_act2aut.MSKEYVALUE_AUTORISATIE = bigpicture.idm_autorisatie.MSKEYVALUE_AUTORISATIE
			Left Join bigpicture.idm_activiteit On bigpicture.idm_activiteit.MSKEYVALUE_ACTIVITEIT = bigpicture.idm_act2aut.MSKEYVALUE_ACTIVITEIT
			Left Join bigpicture.idm_role2act On bigpicture.idm_role2act.MSKEYVALUE_ACTIVITEIT = bigpicture.idm_activiteit.MSKEYVALUE_ACTIVITEIT
			Left Join bigpicture.idm_role On bigpicture.idm_role.MSKEYVALUE_ROL = bigpicture.idm_role2act.MSKEYVALUE_ROL
			Left Join bigpicture.idm_person2role On bigpicture.idm_person2role.MSKEYVALUE_ROL = bigpicture.idm_role.MSKEYVALUE_ROL
			Left Join bigpicture.idm_person On bigpicture.idm_person.MSKEYVALUE_MEDEWERKER = bigpicture.idm_person2role.MSKEYVALUE_MEDEWERKER
			Left Join bigpicture.idm_ou On bigpicture.idm_ou.MSKEYVALUE_OU = bigpicture.idm_person.ACHMEA_REF_OU
		
		".$where." 
		
		Group By
			bigpicture.idm_autorisatie.MSKEYVALUE_DOELSYSTEEM,
			bigpicture.idm_ou.MSKEYVALUE_OU,
			bigpicture.idm_ou.Z_ORG_DN
		
		Union All
		
		Select
			bigpicture.ist_aut_swift_productie_cea.Systeem,
			bigpicture.idm_ou.MSKEYVALUE_OU As I_OU,
			bigpicture.idm_ou.Z_ORG_DN As I_organisatie,
			Count(bigpicture.ist_aut_swift_productie_cea.Autorisatienaam) As ist
		From
			bigpicture.ist_aut_swift_productie_cea
			Left Join bigpicture.ist_user2aut_swift_productie_cea On bigpicture.ist_user2aut_swift_productie_cea.Autorisatie_naam = bigpicture.ist_aut_swift_productie_cea.Autorisatienaam
			Left Join bigpicture.ist_user_swift_productie_cea On bigpicture.ist_user_swift_productie_cea.gebruikersnaam = bigpicture.ist_user2aut_swift_productie_cea.gebruikersnaam
			Left Join bigpicture.idm_person On bigpicture.idm_person.ACHMEA_EMPLOYEENUMBER = bigpicture.ist_user_swift_productie_cea.Personeelsnummer
			Left Join bigpicture.idm_ou On bigpicture.idm_ou.MSKEYVALUE_OU = bigpicture.idm_person.ACHMEA_REF_OU
		Group By
			bigpicture.ist_aut_swift_productie_cea.Systeem,
			bigpicture.idm_ou.MSKEYVALUE_OU,
			bigpicture.idm_ou.Z_ORG_DN
		Order By
			MSKEYVALUE_DOELSYSTEEM Desc,
			S_OU
			
			");

maar helaas is niet erg bruikbaar op deze manier.. iemand een idee? of kan me in een goede richten manoevreren?
Heel beniewd en ik waaardeer nu al jullie tomeloze inzet en voor het meedenken..

[size=xsmall]Toevoeging op 29/11/2020 16:40:22:[/size]

Een andere methode is dat ik heb geprobeerd om 2 select statements apart te definieren. Helaas geeft dat ook niet gewenste restultaat... ik draai maar rondjes en weet niet goed hoe ik dit nu moet aanpakken.


<?php

//------------------------------------------------------------------------ FUNCTIE Soll/Ist vergelijking op systeemniveau
	function soll_ist_systeem_swift_productie_cea(){
		global $connection;

			$where = NULL;
			if (isset($_GET['doelsysteem'])){
				$where = 'WHERE MSKEYVALUE_DOELSYSTEEM = "'.mysqli_real_escape_string($connection, $_GET['doelsysteem']).'"';
			}
		
		$sqlUitlezenSoll = mysqli_query($connection, "Select
			bigpicture.idm_autorisatie.MSKEYVALUE_DOELSYSTEEM,
			bigpicture.idm_ou.MSKEYVALUE_OU As S_OU,
			bigpicture.idm_ou.Z_ORG_DN As S_organisatie,
			Count(bigpicture.idm_autorisatie.MSKEYVALUE_AUTORISATIE) As soll
		From
			bigpicture.idm_autorisatie
			Left Join bigpicture.idm_act2aut On bigpicture.idm_act2aut.MSKEYVALUE_AUTORISATIE = bigpicture.idm_autorisatie.MSKEYVALUE_AUTORISATIE
			Left Join bigpicture.idm_activiteit On bigpicture.idm_activiteit.MSKEYVALUE_ACTIVITEIT = bigpicture.idm_act2aut.MSKEYVALUE_ACTIVITEIT
			Left Join bigpicture.idm_role2act On bigpicture.idm_role2act.MSKEYVALUE_ACTIVITEIT = bigpicture.idm_activiteit.MSKEYVALUE_ACTIVITEIT
			Left Join bigpicture.idm_role On bigpicture.idm_role.MSKEYVALUE_ROL = bigpicture.idm_role2act.MSKEYVALUE_ROL
			Left Join bigpicture.idm_person2role On bigpicture.idm_person2role.MSKEYVALUE_ROL = bigpicture.idm_role.MSKEYVALUE_ROL
			Left Join bigpicture.idm_person On bigpicture.idm_person.MSKEYVALUE_MEDEWERKER = bigpicture.idm_person2role.MSKEYVALUE_MEDEWERKER
			Left Join bigpicture.idm_ou On bigpicture.idm_ou.MSKEYVALUE_OU = bigpicture.idm_person.ACHMEA_REF_OU
			
		".$where." 
		
		Group By
			bigpicture.idm_autorisatie.MSKEYVALUE_DOELSYSTEEM,
			bigpicture.idm_ou.MSKEYVALUE_OU,
			bigpicture.idm_ou.Z_ORG_DN
		Order By
			bigpicture.idm_autorisatie.MSKEYVALUE_DOELSYSTEEM Desc,
			S_OU
		");
		
		
		$sqlUitlezenIst = mysqli_query($connection, "Select
			bigpicture.ist_aut_swift_productie_cea.Systeem,
			bigpicture.idm_ou.MSKEYVALUE_OU As I_OU,
			bigpicture.idm_ou.Z_ORG_DN As I_organisatie,
			Count(bigpicture.ist_aut_swift_productie_cea.Autorisatienaam) As ist
		From
			bigpicture.ist_aut_swift_productie_cea
			Left Join bigpicture.ist_user2aut_swift_productie_cea On bigpicture.ist_user2aut_swift_productie_cea.Autorisatie_naam = bigpicture.ist_aut_swift_productie_cea.Autorisatienaam
			Left Join bigpicture.ist_user_swift_productie_cea On bigpicture.ist_user_swift_productie_cea.gebruikersnaam = bigpicture.ist_user2aut_swift_productie_cea.gebruikersnaam
			Left Join bigpicture.idm_person On bigpicture.idm_person.ACHMEA_EMPLOYEENUMBER = bigpicture.ist_user_swift_productie_cea.Personeelsnummer
			Left Join bigpicture.idm_ou On bigpicture.idm_ou.MSKEYVALUE_OU = bigpicture.idm_person.ACHMEA_REF_OU
		Group By
			bigpicture.ist_aut_swift_productie_cea.Systeem,
			bigpicture.idm_ou.MSKEYVALUE_OU,
			bigpicture.idm_ou.Z_ORG_DN
			
		");
		
		$sqlAantalSoll = mysqli_num_rows($sqlUitlezenSoll);
				
		echo '<table id="idmdata">';
		echo '<tr>';
		echo '<th colspan="5">Audit: Soll/IST systeemrapport voor '.strtoupper($_GET['doelsysteem']).'</th>';
		echo '</tr>';
		echo '<tr>';
		echo '<th>Soll</th>';
		echo '<th>Ist</th>';
		echo '<th align="center">S</th>';
		echo '</tr>';
		echo '<tr>';
		echo '<th><font size="-1">OU code</th>';
		echo '<th><font size="-1">Afdeling</th>';
		echo '<th><font size="-1"></th>';
		echo '</tr>';

		if ($sqlAantalSoll > 0){
			while ($sqlDataSoll = mysqli_fetch_assoc($sqlUitlezenSoll)){		
				echo '<tr>';		
				echo '<td><font size="-2">'.$sqlDataSoll['S_OU'].'</td>';
				echo '<td><font size="-2">'.$sqlDataSoll['S_organisatie'].'</td>';
				echo '<td><font size="-2">'.$sqlDataSoll['soll'].'</td>';
				echo '</tr>';
			}
			echo '</table>';
			echo '<br />';

		echo '<button class="button1" style="vertical-align:middle"><span><div id="button"><a href="javascript:javascript:history.go(-1)">&nbsp;&nbsp;Vorige pagina</a></div></span></button>';	
		}
		
		$sqlAantalIst = mysqli_num_rows($sqlUitlezenIst);
				
		echo '<table id="idmdata">';
		echo '<tr>';
		echo '<th colspan="5">Audit: Soll/IST systeemrapport voor '.strtoupper($_GET['doelsysteem']).'</th>';
		echo '</tr>';
		echo '<tr>';
		echo '<th>Soll</th>';
		echo '<th>Ist</th>';
		echo '<th align="center">I</th>';
		echo '</tr>';
		echo '<tr>';
		echo '<th><font size="-1">OU code</th>';
		echo '<th><font size="-1">Afdeling</th>';
		echo '<th><font size="-1"></th>';
		echo '</tr>';

		if ($sqlAantalIst > 0){
			while ($sqlDataIst = mysqli_fetch_assoc($sqlUitlezenIst)){		
				echo '<tr>';		
				echo '<td><font size="-2">'.$sqlDataIst['I_OU'].'</td>';
				echo '<td><font size="-2">'.$sqlDataIst['I_organisatie'].'</td>';
				echo '<td><font size="-2">'.$sqlDataIst['ist'].'</td>';
				echo '</tr>';
			}
			echo '</table>';
			echo '<br />';	
		
		echo '<button class="button1" style="vertical-align:middle"><span><div id="button"><a href="javascript:javascript:history.go(-1)">&nbsp;&nbsp;Vorige pagina</a></div></span></button>';	
		}
		
		else{
			echo '<center><a href="javascript:javascript:history.go(-1)"><img src="../img/no-data.png"></a></center>';
		}
		
		
	}

?>

- - DHU -

14-12-2020 14:30

Die tabel is benodigd voor extra informatie over desbeteffende OU. Vraag me af of dit wel haalbaar is op een simpele localhost... ik blijf maar geen resultaat krijgen.. zelfs na half uur pollen niet. Ik forceer dus iedere keer maar herstart van de localhost. Misschien moet ik het er gewoon bij laten.

- Ariën -

14-12-2020 20:03

Waarom zou je steeds je webserver moeten herstarten?
Het klinkt alsof je een bepaalde grens overschrijdt, waarbij je een limiet moet oprekken?
Verder is het logischer dat je MySQL los een restart geeft (staat los van de webserver), maar het zou eigenlijk niet moeten.

Ger van Steenderen

15-12-2020 13:58

>> Die tabel is benodigd voor extra informatie over desbeteffende OU.
Die tabel vervalt ook niet, hij komt er alleen pas later bij.

Ik zal het proberen uit te leggen:
Op het moment dat je GROUP BY in een query gebruikt moet de database eerst alle informatie verzamelen voordat er gegroepeerd wordt. Dit kan (gezien het aantal koppeltabellen) best wel oplopen.
Grofweg:
a = Aantal personen x (gem) aantal rollen pp x (gem) aantal activiteiten per rol x (gem) aantal autorisaties per act.

Terwijl:
b = aantal afdelingen

Door eerst te groeperen in een subquery en daarna pas die tabel te joinen bespaar je dan b t.o.v van a (beetje simpel gesteld)

In je openingsvraag geef je aan dat je FlySpeed als tool hebt, ik denk dat je daar ook 'zelf geschreven' SQL statements kan uitvoeren. In dat geval doe dat, dan sluit je iig uit dat het geen PHP issue is.

- - DHU -

15-12-2020 18:57

- Ariën - op 14/12/2020 20:03:08

Waarom zou je steeds je webserver moeten herstarten?
Het klinkt alsof je een bepaalde grens overschrijdt, waarbij je een limiet moet oprekken?
Verder is het logischer dat je MySQL los een restart geeft (staat los van de webserver), maar het zou eigenlijk niet moeten.

Omdat bij uitvoering de mijn browser maar blijft pollen en draaien.... na een 1/2 uurtje ben ik dat toch wel zat hoor.. en dan forceer ik een herstart want anders kan ik niets anders doen... en mijn van van mijn laptop loopt als 'n dolle te blazen... die raakt klaarblijkelijk verhit

- Ariën -

15-12-2020 19:00

Misschien even uitzoeken waarom dat gebeurt?

- - DHU -

15-12-2020 19:01

Ger van Steenderen op 15/12/2020 13:58:46

>> Die tabel is benodigd voor extra informatie over desbeteffende OU.
Die tabel vervalt ook niet, hij komt er alleen pas later bij.

Ik zal het proberen uit te leggen:
Op het moment dat je GROUP BY in een query gebruikt moet de database eerst alle informatie verzamelen voordat er gegroepeerd wordt. Dit kan (gezien het aantal koppeltabellen) best wel oplopen.
Grofweg:
a = Aantal personen x (gem) aantal rollen pp x (gem) aantal activiteiten per rol x (gem) aantal autorisaties per act.

Terwijl:
b = aantal afdelingen

Door eerst te groeperen in een subquery en daarna pas die tabel te joinen bespaar je dan b t.o.v van a (beetje simpel gesteld)

In je openingsvraag geef je aan dat je FlySpeed als tool hebt, ik denk dat je daar ook 'zelf geschreven' SQL statements kan uitvoeren. In dat geval doe dat, dan sluit je iig uit dat het geen PHP issue is.

ik voer ook inderdaad ook eerst de sql statements in flyspeed... dat gaat wel maar tot aan de tabel ou.... want voeg ik die toe dan zegt ie mooi 'doet 't lekker zelf' :-) voor nu zie ik dit op mijn laptop als onmogelijk en er is al heel veel gezegd en geschreven hierover. Ben ook erkentelijk voor al die voorzetten, opmerkingen en aanvullingen maar het is simpelweg niet te doen. Kan geen andere conclusie trekke. Wederom gaat mijn dank uit naar iedereen die een bijdrage heeft geleverd aan dit topic.

? Onbekende gebruiker

17-12-2020 10:53 gewijzigd op 17-12-2020 10:55

Qua prestaties kan je overwegen om GROUP BY te vervangen voor Window-functie(s), dan hoeft MySQL maar 1x door de (uitvoer)tabel te gaan.

Het is wel lastig wanneer MySQL om onbekende reden blijft hangen. Ik heb dat ooit gehad op een server met ZFS als bestandssysteem op FreeBSD. Nadat ik terug ging naar UFS was het probleem verholpen.

In jouw geval zou ik via een tweede verbinding met MySQL vanuit een client kijken welk proces lang draait met de query SHOW PROCESSLIST; en eventueel door te kijken in het slow query log. Zodra je weet op welke SQL de database blijft hangen kan je de prestaties analyseren via EXPLAIN ANALYZE. Maar je kunt ook de query stapsgewijs uitvoeren (steeds meer JOINs en condities) en die stuk voor stuk uitproberen om te kijken waar het probleem zit.

Je kunt queries meestal wel goed optimaliseren met goed geplaatste indices (voor als je minder dan 15% van de rijen nodig hebt). Verder maakt de volgorde van JOINs soms uit, en wat ook uitmaakt zijn de WHERE-condities. Informatie aggregeren zoals met GROUP BY wil je het liefst in een zo laat mogelijk stadium doen en waar mogelijk met Window-functies. Als het niet genoeg helpt kan je eventueel nog tijdelijke (sessie-)tabellen gebruiken.

Deze info is meer algemeen van aard, toch hoop ik dat er iets tussenzit waarmee je verder komt.

Reageren

Inloggen om te reageren