[SQL]Joinen op tabel met variabele naam

[mithras]

Evenals [MySQL]Kolomnaam is zelf waarde van veld heb ik hier weer een probleem met een veldwaarde die ik in de query zelf wil gebruiken. Ik ben bezig met een RBAC systeem en heb daarvoor de volgende tabellen aangemaakt:

[b]users[/]
id (int)
name (varchar)

[b]groups[/]
id (int)
name (varchar)

[b]user_in_groups[/]
group_id (int)
user_id (int)

[b]actions[/]
id (int)
name (varchar)

[b]domains[/]
id (int)
name (varchar)
object_table (varchar)

[b]permissions[/]
id (int)
domain_id (int)
object_id (int)
group_id (int)
action_id (int)

Deze constructie zorgt ervoor dat een gebruiker in een groep terecht komt. De groep krijgt een permissie om een bepaalde actie op een object binnen een domein uit te voeren.

Bijvoorbeeld "gast mag reactie binnen blog aanmaken" en "admin mag gebruiker binnen gebruikers wijzigen".

Omdat ik echter met een modulair systeem werk, wil ik niet één tabel maken met alle objecten die een koppeling krijgen naar het domein. Ik wil alle data van alle plugins gescheiden houden dus heb in dit systeem verzonnen dat in de tabel domains je de tabelnaam van de objecten binnen dat domein kan zetten. Ik heb nu bijvoorbeeld de rijen
1 - blog - blog_objects
2 - user - user_objects
aangemaakt en bijbehorende tabellen user_objects en blog_objects. Die zien er als volgt uit:

[b]object_table_X[/]
id (int)
name (varchar)

Vervolgens wil ik in één query de volgende gegevens ophalen:
Alle permissies die een gebruiker heeft, met "actienaam", "domeinnaam" en "objectnaam".

Ik kom gemakkelijk uit de stap om de eerste twee te achterhalen:

SQL:

SELECT a.name action, d.name domain, p.object_id object
FROM permissions p
LEFT JOIN actions a ON p.action_id = a.id
LEFT JOIN groups g ON p.group_id = g.id
LEFT JOIN domains d ON p.domain_id = d.id
LEFT JOIN user_in_groups u ON u.group_id = g.id
WHERE u.user_id =1

Hier blijf ik dus vastzitten in de objectnaam. Die kan ik uit domains.object_table halen waarbij je dus in (domains.object_table).name de waarde van het object krijgt.

Haal ik me (weer) wat onmogelijks op de hals? Ik ben niet zo'n sql expert dat ik dergelijke problemen kan oplossen. Het object_id is namelijk niet uniek. De combinatie van object en domein is uniek en je kan de naam van het object achterhalen door bij het id van het domein de juiste tabel op te zoeken.
Het lijkt me niet verstandig om uiteindelijk bij een gebruiker de tientallen combinaties van domein_id en object_id te gaan zoeken in aparte queries, want dat nekt echt de performance.

En wederom is het Googlen niet heel makkelijk, doordat de termen te pas en te onpas worden gebruikt en mijn probleem niet naar voren komt

[Voutloos]

Het kan, met beide condities in een join, of dmv unions.

mithras schreef op donderdag 22 mei 2008 @ 10:17:
En wederom is het Googlen niet heel makkelijk, doordat de termen te pas en te onpas worden gebruikt en mijn probleem niet naar voren komt

Dan moet je googlen naar een tekst over datamodellen en mooie foreign keys.

[mithras]

Voutloos schreef op donderdag 22 mei 2008 @ 10:22:
Het kan, met beide condities in een join, of dmv unions.

Ik heb de UNION documentatie gelezen. Ik kende het nog niet Ik zie dat je uit twee select statements met elk één resultaat, een resultatenset met twee resultaten krijgt. Dat is natuurlijk niet de bedoeling, maar ik zie dat in een Oracle helpforum de tip werd gegeven om te groeperen zodat rijen tot één resultaat werden gevoegd.

Toch blijft dan de vraag hoe ik de "FROM hier_tabel_naam" invul toch? Dat is namelijk een waarde uit een eerdere query. Het probleem blijft ook bestaan met een join. Ik kan meerdere condities verzinnen voor de join, maar dan moet ik nog wel de naam van de tabel weten.

Misschien heb ik wel een foute denkwijze hoor Ik snapte dat je de gegevens moest joinen, maar hoe je dat doet als je tig tabellen hebt met objectgegevens: dat is me nog (steeds) onduidelijk.

Wel bedankt voor je hulp hoor: het wordt zeker gewaardeerd!

[...]
Dan moet je googlen naar een tekst over datamodellen en mooie foreign keys.

Commentaar op mijn datamodel?

Verder kan / heb ik wel foreign keys aanmaken, maar die zijn afaik niet van belang bij het selecteren van gegevens. Het is (weer afaik) bedoeld om je datamodel consistent te houden en niet te gebruiken bij het (makkelijker kunnen) selecteren van gegevens (daar rept de manual ook niet over: alleen ON UPDATE en ON DELETE).

[T-MOB]

Waarom aparte tabellen? Wat is er mis met:

[b]objects[/b]
id(int)
domainid(int)
name(varchar)

Variabele tabelnamen lijkt me een hel om te onderhouden...

[Voutloos]

mithras schreef op donderdag 22 mei 2008 @ 12:03:
Verder kan / heb ik wel foreign keys aanmaken, maar die zijn afaik niet van belang bij het selecteren van gegevens. Het is (weer afaik) bedoeld om je datamodel consistent te houden en niet te gebruiken bij het (makkelijker kunnen) selecteren van gegevens (daar rept de manual ook niet over: alleen ON UPDATE en ON DELETE).

Zelfs als je je dbms niet verteld wat de foreign keys zijn, gebruik je het nog wel altijd zo.

[justmental]

Die variabele tabelnamen heb je helemaal niet nodig.
Als je je permissies wilt checken in een query dan weet je welke tabel gefilterd gaat worden, die hoeft niet dynamisch bepaald te worden.

[mithras]

T-MOB schreef op donderdag 22 mei 2008 @ 12:24:
Waarom aparte tabellen? Wat is er mis met:

[b]objects[/b]
id(int)
domainid(int)
name(varchar)

Variabele tabelnamen lijkt me een hel om te onderhouden...

Ik wil graag de logica van de onderdelen gescheiden houden. Daaruit volgt ook dat de accessController geen idee heeft waar de objecten van de plugin blog zich bevinden.

De AC hoeft het ook niet te weten want het is vrij aan de blog om de tabel ergens neer te zetten. De AC moet alleen de data verwerken en vraagt (bijna) aan de blog waar de tabel met de blog-objecten zich bevindt.
Hierdoor is er maar één tabel waar de gehele koppeling van plugins staat: naam van plugin, status van plugin en locatie van het php bestand wat de controller van de plugin bevat

Ik krijg dus in php iets als

PHP:

class blog{
  public function viewArticle( $id ){
    if( $user->isAllowed( "blog", "article", "view" )
      $this->parseArticle( $id );
  }
}

Blog vraagt aan de AC of de user het mag. De AC ziet dat het van de blog is, kijkt bij de blog of het object article bestaat en kijkt bij de permissions of de koppeling van gebruiker -> groep -> object -> permissie toegestaan is.

Voutloos schreef op donderdag 22 mei 2008 @ 12:50:
[...]
Zelfs als je je dbms niet verteld wat de foreign keys zijn, gebruik je het nog wel altijd zo.

Ok

justmental schreef op donderdag 22 mei 2008 @ 12:55:
Die variabele tabelnamen heb je helemaal niet nodig.
Als je je permissies wilt checken in een query dan weet je welke tabel gefilterd gaat worden, die hoeft niet dynamisch bepaald te worden.

Om de performance nog enigszins te drukken ga ik niet bij elke actie de autorisatie controleren. Ik wil één keer (per pageview) de gegevens ophalen en die opslaan. Het is zelfs nog mogelijk dit per sessie te doen, maar elke actie controleren met de database gaat een enorme overhead geven (lijkt mij).

En Voutloos zegt dat het kan, dus moet het kunnen

[mithras]

Ik ben er wat verder mee gegaan. Toch kom ik er nog niet uit. Als je kijkt naar de subquery manual is dat lijkt me the way to go. Vervolgens ga ik kijken of de query nog omgeschreven kan worden in een join zoals hier beschreven staat.

Toch stuit ik op wat errors:

SQL:

SELECT o.name object, d.name domain, a.name action
FROM permissions p
LEFT JOIN actions a 
  ON p.action_id = a.id
LEFT JOIN groups g 
  ON p.group_id = g.id
LEFT JOIN domains d 
  ON p.domain_id = d.id
LEFT JOIN user_in_groups u 
  ON u.group_id = g.id
LEFT JOIN (SELECT object_table FROM domains d2 WHERE d2.id = p.object_id) o 
  ON p.object_id = o.id
WHERE u.user_id =2

Het lijkt me vergelijkbaar als hier waarbij in de subquery ook een parameter uit de hoofdquery gebruikt wordt:

SQL:

SELECT * FROM t1 AS t WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

Toch krijg ik een error:

Unknown table 'p' in where clause 

Waarom het voorbeeld wel en ik niet?

[Alain]

Ik vermoed dat bij het samenstellen van de verzameling (joinen) nog niet op rijniveau geevalueerd wordt. Je kunt een subquery dan alleen afhankelijk maken van de hoofdquery binnen een conditie die op rijniveau geeavalueerd wordt, zoals binnen een WHERE.

[Verwijderd]

offtopic:
Waarom wordt er zo vaak een synoniem aan een tabelnaam gegeven die minder duidelijk wordt? Sowieso is het beste synoniem de tabel naam zelf en bij meerdele malen dezelfde tabel kun je ar altijd een beter beschrijvende synoniem aan geven. bv:
p.action_id = a.id -> permissions.action_id = actions.id

[mithras]

AlainS schreef op zondag 25 mei 2008 @ 01:48:
Ik vermoed dat bij het samenstellen van de verzameling (joinen) nog niet op rijniveau geevalueerd wordt. Je kunt een subquery dan alleen afhankelijk maken van de hoofdquery binnen een conditie die op rijniveau geeavalueerd wordt, zoals binnen een WHERE.

Tja, is daar iets op te verzinnen? Ik kan dus niet zomaar een join en een subquery gebruiken omdat je met die twee nooit op een rijniveau zit?

Mijn probleem lijkt me nu eigenlijk niet zo moeilijk dat het onmogelijk is toch? Op GoT is er veel kennis aanwezig, maar helaas zitten er nu nog weinig bruikbare tips tussen. Is er toch nog iemand die een duwtje kan geven?

Verwijderd schreef op zondag 25 mei 2008 @ 11:24:

offtopic:
Waarom wordt er zo vaak een synoniem aan een tabelnaam gegeven die minder duidelijk wordt? Sowieso is het beste synoniem de tabel naam zelf en bij meerdele malen dezelfde tabel kun je ar altijd een beter beschrijvende synoniem aan geven. bv:
p.action_id = a.id -> permissions.action_id = actions.id

offtopic:
Het kost meer schrijven. Langere regels zijn imho slechter leesbaar dan synoniemen die voor de hand liggen. Elk synoniem komt voort uit een beginletter van de tabel en die is in dit geval nog uniek, en dus bruikbaar.

[Verwijderd]

mithras schreef op zondag 25 mei 2008 @ 20:32:
[...]

offtopic:
Het kost meer schrijven. Langere regels zijn imho slechter leesbaar dan synoniemen die voor de hand liggen. Elk synoniem komt voort uit een beginletter van de tabel en die is in dit geval nog uniek, en dus bruikbaar.

offtopic:
En simpelweg een grotere kans op bugs, en daarnaast, je IDE maakt het wel voor je af dus dat typelen is ook niet bepaald een sterk argument.

[Alain]

mithras schreef op zondag 25 mei 2008 @ 20:32:
[...]
Tja, is daar iets op te verzinnen? Ik kan dus niet zomaar een join en een subquery gebruiken omdat je met die twee nooit op een rijniveau zit?

Je kunt een verzameling maken met een subquery binnen een FROM. Oftewel, een niet-gecorreleerde subselect.

mithras schreef op zondag 25 mei 2008 @ 20:32:
[...]
Mijn probleem lijkt me nu eigenlijk niet zo moeilijk dat het onmogelijk is toch? Op GoT is er veel kennis aanwezig, maar helaas zitten er nu nog weinig bruikbare tips tussen. Is er toch nog iemand die een duwtje kan geven?

Als ik je een tip mag geven zou ik voorstellen dat je je databaseontwerp anders opstelt. Een variabele tabelnaam druist tegen alle regels van een relationele database in. Al snap ik dat stukje van de accessController niet.

[ Voor 24% gewijzigd door Alain op 25-05-2008 21:00 ]

[mithras]

AlainS schreef op zondag 25 mei 2008 @ 20:55:
Als ik je een tip mag geven zou ik voorstellen dat je je databaseontwerp anders opstelt. Een variabele tabelnaam druist tegen alle regels van een relationele database in. Al snap ik dat stukje van de accessController niet.

Mm, tja helaas. Ik zat al te twijfelen of het een juiste aanpak was gezien de weinige reacties.

Ik ben bang dat één tabel met objecten van plugins foutgevoeliger is. Als je een plugin toevoegt, moet je op die manier alle objecten via de accessController in de tabel zien te krijgen. Als je dan de plugin weer verwijdert moet je zorgen dat alle objecten van dat domein weer weggaan. Anders krijg je weer problemen mocht je die plugin weer gaan installeren. Het bijhouden van een enkele tabel gaat denk ik heel veel rotzooi genereren. En dat is zo foutgevoelig. Met mijn methode is dat niet, omdat je per domain maar een enkele entry in je rbac systeem hebt zitten. De verdere foutgevoeligheid blijft dan beperkt in het domain zelf en gaat niet in dit rbac systeem zitten rommelen...

Verwijderd schreef op zondag 25 mei 2008 @ 20:47:
[...]

offtopic:
En simpelweg een grotere kans op bugs, en daarnaast, je IDE maakt het wel voor je af dus dat typelen is ook niet bepaald een sterk argument.

De ide's die ik tot dusverre heb gebruikt herkennen prima php variabelen en Eclipse gaat daar erg ver in (wat ik fijn vind). Echter kan zo'n ide niet ontdekken dat een bepaalde string een sql query is en daarbij nog de tabelnamen herkennen ook...