[MySQL] BIT_OR gebruiken in UPDATE statement.

[cyberstalker]

Ik probeer een update statement te draaien. Er zijn nu drie tabellen. Een tabel met gebruikers (person), een tabel met vestigingen (mDepartment) en een tabel die aangeeft bij welke vestigingen een gebruiker hoort (mPerson2department). In de laatste tabel zitten twee velden, eentje die linkt naar het gebruikersid en eentje die linkt naar het vestigingsid. Dit vind ik een inefficiënte en onoverzichtelijke manier van werken. Daarom wil ik deze tabel elimineren.

Hierbij ga ik werken met bitwise operators. Ik heb een veld 'department' aan de person-tabel toegevoegd. Elke bit in dat veld staat voor een koppeling met een vestiging. De rechter bit is bijvoorbeeld vestiging met id 1. Dit werkt uitstekend.

Nu moet ik echter nog wel de gegevens uit de mPerson2department tabel overzetten naar het nieuwe veld. Ik probeer dat met de volgende query

SQL:

UPDATE
    person,
    mPerson2department
SET
    person.department    =    BIT_OR(POW(2, mPerson2department.fk_did - 1))
WHERE
    person.pid           =    mPerson2department.fk_pid;

Dit geeft mij echter een

ERROR 1111 (HY000): Invalid use of group function

fout. Ik heb al gegoogled en het gebruik van GROUP BY geprobeerd, maar zonder resultaat.

Weet iemand hoe ik dit kan oplossen?

[RobIII]

Waarom zou je de koppeltabel (mPerson2department) willen elimineren? Dit is heel gebruikelijk bij N:M relaties in SQL hoor?

Wat je nu wil gaan doen is lang niet zo schaalbaar en alleen maar "error-prone". Wat als je meer dan x-bits-vestigingen krijgt?

[ Voor 10% gewijzigd door RobIII op 01-11-2006 11:15 ]

[cyberstalker]

RobIII schreef op woensdag 01 november 2006 @ 11:14:
Waarom zou je de koppeltabel (mPerson2department) willen elimineren? Dit is heel gebruikelijk bij N:M relaties in SQL hoor?

Klopt. Alleen heeft dit systeem nu ongeveer 325 tabellen. Ik wil dat voor de eenvoud graag verkleinen. Daarnaast worden die vestigingen straks uitgebreid met afdelingen waarbij er nog meer tabellen nodig zouden zijn.

Wat je nu wil gaan doen is lang niet zo schaalbaar en alleen maar "error-prone". Wat als je meer dan x-bits-vestigingen krijgt?

Waarom zou dit errorprone zijn? Ik gebruik dit systeem voor meerdere dingen. Dit is alleen de eerste keer dat ik zoiets moet opbouwen uit oude gegevens en daar heb ik even hulp bij nodig.

Wanneer we meer dan 32 vestigingen krijgen (het is nu een 4bytes integer) wijzig ik de tabel gewoon in een 8bytes integer .

[bigbeng]

Jij vind het werken met bitsgewijze operatoren om bij gelinkte data te komen wel overzichtelijk? Het werken met koppeltabellen is een methode die iedereen die iets met db ontwerp te maken heeft gehad moet kunnen volgen. Als jij straks een opvolger moet gaan inwerken, dan zullen jouw "verbeteringen" flink wat extra uitleg gaan vergen.

Hoe dan ook, je gebruikt een aggregate of grouped functie, deze werkt aleen op een set, net als MAX, AVG etc. Deze kun je dus niet in een update gebruiken.

[cyberstalker]

Ik vind het overzichtelijk. Als je met het huidige systeem met de koppeltabel de gekoppelde vestigingen van een gebruiker wilt updaten zul je de entries moeten droppen van vestigingen die je er uit wilt halen en nieuwe entries maken voor de vestigingen waar iemand aan gekoppeld is.

Om een heel profiel bij te werken ga je dan naar 3 queries toe. Een voor de persoonsgegevens, een voor het droppen van vestigingen en eentje voor nieuwe vestigingen. Dat vind ik onoverzichtelijk. Met bitwise operators kan dat heel overzichtelijk in een enkele query.

Ik wil dit graag op deze manier gaan doen. Heeft iemand een idee hoe ik deze overzetting wel eenvoudig kan doen?

[Grijze Vos]

De overzichtelijkheid van je model heeft niks te maken met het aantal quieries of het aantal tabellen, maar met hoe je ze structureert.

Bit-magic zorgt echt niet dat je systeem overzichtelijker wordt, zinnig normaliseren wel. Als je nu al zoveel tabellen hebt en het overzicht verliest, dan moet je eens kijken of je model in the first place wel zinnig was. (Tabellen erbij maken voor nieuwe vestigingen klinkt mij in de oren als een foutief model namelijk, tenzij die nieuwe vestigingen daadwerkelijk nieuwe functionaliteit nodig hebben natuurlijk.)

[cyberstalker]

Ja mensen, ik vind die hele bemoeienis over mijn databasemodel allemaal hartstikke schattig enzo, maar ik heb besloten hoe ik het wil doen en zou graag willen weten of hier een mogelijkheid voor is .

[justmental]

Dat je eigenwijs doet en tegen alle goede en goedbedoelde adviezen in wilt gaan is tot daar aan toe, maar deze denigrerend afdoen met 'schattig' gaat toch echt te ver.
Ik vind het niet gek dat je niet geholpen wordt met dingen kapotmaken, zeker als je je zo opstelt.

[sig69]

In de laatste tabel zitten twee velden, eentje die linkt naar het gebruikersid en eentje die linkt naar het vestigingsid. Dit vind ik een inefficiënte en onoverzichtelijke manier van werken.

Dit is juist de efficiente en overzichtelijke manier van werken!
Met een simpele query zijn voor alle vestigingen gebruikers op te halen, en andersom. Dat wil ik je met je bitwise operators nog wel eens zien doen...

[cyberstalker]

Dat is niet zo moeilijk hoor. Ik gebruik dat systeem voor van alles zoals bijvoorbeeld de rechten etc... Ik heb hier gewoon queries voor die prima werken. Het mooiste is nog wel dat je met dit systeem gebruikersgroepen kunt maken waarbij je gebruikers lid kunt maken van groepen die de gebruikers extra rechten verlenen. Deze rechten worden met bitwise operators bij elkaar opgeteld. Dit is onmogelijk op te lossen met een tussentabel.

Ditzelfde systeem wil ik hiervoor ook gebruiken. Geloof me, die queries heb (die werken al prima zelfs). Ik moet alleen die data nog uit die oude tabel halen.

Als jullie mijn manier van werken raar of onhandig vinden: prima! Jullie hoeven er immers niet mee te werken . Ik vraag alleen maar advies over hoe ik de gegevens kan omzetten.

[ Voor 171% gewijzigd door RobIII op 01-11-2006 14:24 ]

[_js_]

Jij kunt er mee werken, maar morgen wordt je ziek, of je wordt aangereden, en moet iemand anders er mee werken.

En wat betreft "als we meer dan 32 vestigingen krijgen maak ik er een 8 bytes integer van", en wat als je meer dan 64 vestigingen krijgt?

En als je de gegevens in 1 query in het andere formaat kunt krijgen, dan kun je bij het opvragen toch ook 1 query gebruiken? En dan hoef je niet eens moeilijk te doen over welke bit wat betekent.

Voor het antwoord op je vraag: je hebt een group by statement nodig, je wilt groeperen op alle velden die je niet in een aggregate functie gebruikt.

[RobIII]

sig69 schreef op woensdag 01 november 2006 @ 13:09:
Dit is onmogelijk op te lossen met een tussentabel.

Bull, dat kan wel degelijk.

SQL:

/*
DEMO DB aanmaken
*/
Create database [test]
go

use [test]
go

create table [users] (
  [id] int,
  [name] varchar(50)
)


create table [permissions] (
  [id] int,
  [key] varchar(25),
  [value] int
)

create table [userpermissions] (
  user_id int,
  permission_id int
)

insert into [users] ([id], [name]) values (1,'Jan klaassen')
insert into [users] ([id], [name]) values (2,'Pietje puk')
insert into [users] ([id], [name]) values (3,'Mien dobbelsteen')

insert into [permissions] ([id], [key], [value]) values (1,'del_account',1)
insert into [permissions] ([id], [key], [value]) values (2,'create_account',2)
insert into [permissions] ([id], [key], [value]) values (3,'print_report',4)
insert into [permissions] ([id], [key], [value]) values (4,'ship_products',8)

insert into [userpermissions] ([user_id], [permission_id]) values (1,1)
insert into [userpermissions] ([user_id], [permission_id]) values (1,2)
insert into [userpermissions] ([user_id], [permission_id]) values (1,3)
insert into [userpermissions] ([user_id], [permission_id]) values (2,4)
insert into [userpermissions] ([user_id], [permission_id]) values (2,4)
insert into [userpermissions] ([user_id], [permission_id]) values (3,2)

En "the meat":

SQL:

/*
En hier is je 'oplossing' voor je probleem (MET tussentabel en toch "Bits" voor rechten)
*/

select [users].[name], sum([permissions].[value]) as [permissions]
from [users]
inner join [userpermissions] on [userpermissions].[user_id] = [users].[id]
inner join [permissions] on [userpermissions].[permission_id] = [permissions].[id]
group by [users].[name]

De output:

Name	Permissions
Jan klaassen	7
Mien dobbelsteen	2
Pietje puk	12

Waarbij "permissions" in de resultaten dus de decimale representatie van je "bitfield" is.

Voila. Niet dat het een wenselijke manier is van werken ofzo, maar het kan dus wel degelijk. De weg die jij ingeslagen bent is toch écht verkeerd; geloof ons nou maar

[ Voor 12% gewijzigd door RobIII op 01-11-2006 14:32 ]

[ACM]

cyberstalker schreef op woensdag 01 november 2006 @ 13:22:
Dat is niet zo moeilijk hoor. Ik gebruik dat systeem voor van alles zoals bijvoorbeeld de rechten etc... Ik heb hier gewoon queries voor die prima werken. Het mooiste is nog wel dat je met dit systeem gebruikersgroepen kunt maken waarbij je gebruikers lid kunt maken van groepen die de gebruikers extra rechten verlenen. Deze rechten worden met bitwise operators bij elkaar opgeteld. Dit is onmogelijk op te lossen met een tussentabel.

Je hebt de BIT_OR al gevonden en nog wil je beweren dat je dat niet met een simpele aggregate kan optellen? Dan is het ofwel heel erg complex en moet je je afvragen of je die gegevens uberhaupt wel in je database wilt verwerken (de db is er tenslotte niet om aan te geven welke rechten jij in je applicatie hebt, alleen maar om ze op te slaan) of je hebt nog niet genoeg met diverse logica-statements in SQL gewerkt (met het groeperen van de resultaten van een CASE-statement kom je heel erg ver).

Als jullie mijn manier van werken raar of onhandig vinden: prima! Jullie hoeven er immers niet mee te werken . Ik vraag alleen maar advies over hoe ik de gegevens kan omzetten.

Over het algemeen kan je aggregates niet zo in je queries verwerken. Dat zou je moeten weten natuurlijk, als je een ervaren SQL-gebruiker bent

Ik zou het zo proberen: UPDATE a SET b = (SELECT AGG(c) FROM d WHERE d.id = a.id), maar ik sluit me aan bij de adviezen om het niet te doen. 325 tabellen is niet per se veel, maar als je die allemaal los in één grote database hebt, moet je eens gaan nadenken over het scheiden in losse schemas (mysql behandelt databases zoals andere db's schemas behandelen).

[whoami]

cyberstalker schreef op woensdag 01 november 2006 @ 12:04:
Ik vind het overzichtelijk. Als je met het huidige systeem met de koppeltabel de gekoppelde vestigingen van een gebruiker wilt updaten zul je de entries moeten droppen van vestigingen die je er uit wilt halen en nieuwe entries maken voor de vestigingen waar iemand aan gekoppeld is.

Da's eenvoudig, en de manier waarop SQL het snelst gegevens kan verwerken.
Al eens gedacht wat je moet doen als jij in jouw veld iets wilt verwijderen ? Of een lijst wilt hebben van gebruikers die in een bepaalde vestiging zitten ?

Lees bv eens dit topic:
[MySQL] LIKE query op komma-gescheiden veld

Als jullie mijn manier van werken raar of onhandig vinden: prima! Jullie hoeven er immers niet mee te werken

Nee, maar iemands anders zal misschien wel dit soort crap van jou gaan erven, en er gigantisch veel tijd mee verliezen. Verder wordt dat systeem helemaal ononderhoudbaar en is het niet schaalbaar.
Waarom denk je dat jouw manier van werken nergens door pro's toegepast wordt ? Mocht het echt zo fantastisch zijn, dan zou iedere DB daar gebruik van maken.

[ Voor 30% gewijzigd door whoami op 01-11-2006 14:35 ]

[Janoz]

cyberstalker schreef op woensdag 01 november 2006 @ 13:22:
Als jullie mijn manier van werken raar of onhandig vinden: prima! Jullie hoeven er immers niet mee te werken . Ik vraag alleen maar advies over hoe ik de gegevens kan omzetten.

De adviesen lijken me duidelijk. Gewoon een koppeltabel. Mocht je het aantal tabellen teveel vinden dan lijkt het me inderdaad handiger om ze over meerdere schema's te verdelen.