[MySQL] INSERT IF NOT EXISTS

[IMarks]

Huidige status

MySQL:

INSERT INTO acl (rol,toegang,resource_id)
SELECT '4','deny',NULL from acl
WHERE (SELECT COUNT(id) as total FROM acl WHERE rol='4' AND toegang='deny' AND resource_id IS NULL) = 0 LIMIT 1;

Alleen de array moet uitgelezen kunnen worden key='value', key='value', key='value'

---

Lang verhaal kort
Ik ben opzoek naar een query die alleen insert als de volledige rij uniek is.
de gegevens worden alsvolgt aangeleverd in me model.

PHP:

$data = array(
    'rol'           => $value->getRol(),
    'toegang'       => $value->getToegang(),
    'resource_id'   => $value->getResource_Id()
);

---

Uitgebreid verhaal
Ik ben bezig met een insert query die alleen moet toevoegen op moment dat een volledige rij niet gelijk is aan een die al bestaat.

De database is als volgt te zien

Id	Rol	Toegang	Resource_Id
1	2(admin)	allow	1(index)
2	2(admin)	allow	1(index)

Bovenstaande wil ik dus voorkomen dat er twee keer een rij voorkomt die 2, allow, 1 bevat.
Het probleem is echter dat ik geen INSERT IGNORE INTO kan gebruiken en tevens geen
ON DUPLICATE KEY UPDATE omdat er geen veld wordt mee gegeven die uniek is los van ID, maar die wordt bij een save functie dus niet meegegeven.

Ik maak gebruik van een framework die de gegevens als volgt aan leverd:

PHP:

$data = array(
    'rol'           => $value->getRol(),
    'toegang'       => $value->getToegang(),
    'resource_id'   => $value->getResource_Id()
);

op dit moment is mijn query en gegevens verwerking alsvolgt maar dit kijkt nog niet of de rij uniek is

PHP:

$keys   = array_keys($data);
$values = array_values($data);
        
$keys   = implode(",", $keys);
$values = implode("','", $values);

$select = $this->getDbTable()->getAdapter()->query('INSERT INTO acl
                                                    ('. $keys . ')
                                                    VALUES (\'' . $values . '\')
                                                    ');
return $select;

De enige oplossing die ik op dit moment kan bedenken is om in de model een losse query te plaatsen
die controleerd of die een 1 rij (of meer) tegenkomt en zoja niks uitvoert en zo nee me origenele insert query doet.
Echter weet ik niet hoe ik de array kan omzetten naar

MySQL:

WHERE rol = '2' AND toegang='allow' AND resource_id = NULL

Overige details voor als je wilt helpen tot het einde
Alle drie de velden kunnen leeg zijn. Voorbeeld hiervan is
rol = null, toegang = allow, resource_id = 1. Bij een dergelijk record wordt aan iedereen toegang gegeven tot de index.
Het is dus handig om in de query al rekening te houden met dat het feit dat een volgende velden ook niet uniek zijn

Id	Rol	Toegang	Resource_Id
1	2(admin)	allow	1(index)
2		allow	1(index)

[ Voor 5% gewijzigd door IMarks op 20-11-2012 16:55 ]

[Aapje]

left outer join op zichzelf, waar het tabel wat je joint null terug geeft?

[BtM909]

Waarom maak je geen rol(e) aan voor iedereen ipv null?

Als de hele rij uniek moet zijn (en het gaat maar om drie velden), waarom geen (composite) PK op die velden?

[IMarks]

De uitvoering aka verwerking van de gegevens gebeurd in Zend Framework in hun functie Zend Acl.
Bij Zend Acl is het zo dat als iets NULL is het automatisch voor alles geldt (dit kan dus een groep zijn maar ook voor alle pagina's zijn). Het is dus makkelijk om die NULL aan te houden gezien de uiteindelijk verwerking dan om groep 'everyone' aan te maken.

En wat kan ik voorstellen bij die (composite) PK, ik heb geen flauw idee hoe ik een gehele rij als primary key kan instellen. (ik gebruik navicat, als je daar uitleg in kan geven is handig, maar commando's/query's mogen/kunnen uiteraard ook)

@aapje waar wou je die left outer join op uitvoeren dan? en left join lijkt me moeilijk te gebruiken in een insert, en een where is net zo eenvoudig daarin.

[iH8]

IMarks schreef op dinsdag 20 november 2012 @ 11:05:
ik heb geen flauw idee hoe ik een gehele rij als primary key kan instellen.

Every table must have a primary key. You can declare the column for the primary key using the protected variable $_primary. This is either a string that names the single column for the primary key, or else it is an array of column names if your primary key is a compound key.

[IMarks]

Ik heb nu inderdaad alles een primary key gemaakt, echter worden de NULL velden veranderd in 0.
Dit is niet zo zeer een probleem want dat kan ik "modify'en" maar zodra ik een insert null, 0 of '0' doet krijg ik de error:

MySQL:

cannot add or update a child row: a foreign key constraint fails 
(`jbnb`.`acl`, CONSTRAINT `fk_acl_resource1` FOREIGN KEY (`resource_id`)
REFERENCES `resources` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

[MueR]

iH8 schreef op dinsdag 20 november 2012 @ 11:37:
[...]

Hoewel het in vrijwel alle gevallen wel klopt wat je quote, hoeft niet elke tabel een PK te hebben. Bij een simpele koppeltabel bijvoorbeeld is het onzin om daar een extra kolom in te gooien. Dan kan je prima volstaan met een UNIQUE op beide velden.

[T-MOB]

Als je een UNIQUE constraint op de rijen kolommen legt die uniek moeten zijn kun je wel met ON DUPLICATE KEY UPDATE werken. Dan kun je de PK op het `id` laten liggen en AUTO_INCREMENT blijven gebruiken (even vooropgesteld dat je dat wil).

Maar als er kolommen NULLable zijn dan heb je er niet zo heel veel aan. Want NULL is ongelijk aan NULL, dus
(2,allow,NULL) != (2,allow,NULL).

Je kunt dat oplossen door specifieke waarden te nemen voor de zaken waar je nu NULL voor misbruikt. Een ANY-rol met een specifiek id (bijvoorbeeld "0"). Een toegang "all" (of "none", net wat je nu met NULL impliceert) en een "ALL"-resource (of "None", wederom afhankelijk wat je nu uit NULL afleidt).

[Dentist]

misschien een beetje een hack, maar waarom hash je de gegevens niet en gebruik je dat niet als een identifier?

[Otherside1982]

Met iets als insert ... select ... is dat toch eenvoudig te doen.

Een andere mogelijkheid is een unique key/index leggen op alle velden en insert ignore ... gebruiken.

[ Voor 38% gewijzigd door Otherside1982 op 20-11-2012 14:50 ]

[IMarks]

ik kan het verkeerd hebben maar het lijk mij dat die query insert op het moment dat er juist een record (of meer) wordt gevonden.

[JaQ]

@TS: als je geen unique key op iets anders dan je PK hebt liggen, hoe kan je RDBMS dan "bedenken" dat je een duplicate value hebt? (want dat is toch exact wat je vraagt?)

Exact zoals hier wordt voorgesteld:

T-MOB schreef op dinsdag 20 november 2012 @ 13:28:
Als je een UNIQUE constraint op de rijen kolommen legt die uniek moeten zijn kun je wel met ON DUPLICATE KEY UPDATE werken. Dan kun je de PK op het `id` laten liggen en AUTO_INCREMENT blijven gebruiken (even vooropgesteld dat je dat wil).

[IMarks]

Ik heb de velden als combinatie UNIQUE gemaakt, zie de kopie van de index:

MySQL:

fk_acl_combined1    `rol`, `toegang`, `resource_id` Unique  0   A   0   4   0       0           

maar als ik nu de volgende query uitvoer krijg ik alsnog een dubbel resultaat.

MySQL:

INSERT INTO acl (rol,toegang,resource_id) VALUES ('4','allow',NULL)
ON DUPLICATE KEY UPDATE rol = '4', toegang='allow', resource_id=NULL

[P.O. Box]

IMarks schreef op dinsdag 20 november 2012 @ 15:34:
Ik heb de velden als combinatie UNIQUE gemaakt, zie de kopie van de index:

MySQL:

fk_acl_combined1    `rol`, `toegang`, `resource_id` Unique  0   A   0   4   0       0           

maar als ik nu de volgende query uitvoer krijg ik alsnog een dubbel resultaat.

MySQL:

INSERT INTO acl (rol,toegang,resource_id) VALUES ('4','allow',NULL)
ON DUPLICATE KEY UPDATE rol = '4', toegang='allow', resource_id=NULL

dat komt omdat een NULL apart gedrag vertoont bij een unique index...
misschien kun je van NULL dan beter 0 of -1 maken, om dat probleem te voorkomen?
alhoewel dat niet de schoonheidsprijs verdient m.i.

[IMarks]

Jammer genoeg is dat helaas niet mogelijk want die null van de resource id (en soms van de rol) is een foreign key, resource '0' of '-1' bestaat dus niet.

[T-MOB]

Dan maak je een resource "anything"... (en die kun je prima id 0 toekennen). Hetzelfde geldt trouwens voor rol en permissie. Een rol "anybody" en een permissie "*" zijn veel duidelijker dan NULL en NULL. Maar dat had ik om 13:28u ook geschreven.

[ Voor 85% gewijzigd door T-MOB op 20-11-2012 16:07 ]

[P.O. Box]

Otherside1982 schreef op dinsdag 20 november 2012 @ 14:45:
Met iets als insert ... select ... is dat toch eenvoudig te doen.

met dit zou het toch moeten lukken... evt. nog in combinatie met een tijdelijke tabel als mysql gaat mouwen dat een insert en een select niet tegelijk op dezelfde tabel mogen zijn (weet niet of dat in alle versies van mysql zo is namelijk, misschien dat jouw versie het geen probleem vind)

[IMarks]

Deze query is het uiteindelijk geworden.
moet alleen nog een manier vinden om de array om te zetten tot key = 'value', key= 'value'

MySQL:

INSERT INTO acl (rol,toegang,resource_id)
SELECT '4','deny',NULL from acl
WHERE (SELECT COUNT(id) as total FROM acl WHERE rol='4' AND toegang='deny' AND resource_id IS NULL) = 0 LIMIT 1;

[BtM909]

MueR schreef op dinsdag 20 november 2012 @ 13:28:
[...]

Hoewel het in vrijwel alle gevallen wel klopt wat je quote, hoeft niet elke tabel een PK te hebben. Bij een simpele koppeltabel bijvoorbeeld is het onzin om daar een extra kolom in te gooien. Dan kan je prima volstaan met een UNIQUE op beide velden.

Dat is idd een MySQL oplossing die ook nog handig is