OK walczyłem długo, ale niestety...
Chcę napisać procedurę która podczas jednego zapytania sprawdziłaby czy
1) dany element istnieje (Czy jest element o danej obudowie i nazwie w bieżącej grupie), jeśli istnieje sprawdzi jego parametry oraz zwróci ID_elementu jeśli go znajdzie oraz informację czy taki element jest w tabeli (funkcja checkItem)
2.a) Jeśli status =2 (element istnieje), zwiększy liczbę elementów w magazynie // to nie jest problemem
2.b) Jeśli status != 2(nie istnieje, lub ma inne parametry), doda element o podanych parametrach...// to już jest problemem
W przypadku sprawdzania czy element o danych parametrach istnieje, wysyłam do funkcji checkItem informację o elemencie oraz informację o parametrach w formie ID_Parametru;"Wartsc";;ID_Parametru;"Wartosc jako text. Sprawdza się to dobrze (jak to zrobiłem można zobaczyć w kodzie poniżej, ale teraz nie ma to większego znaczenia
) szczególnie że ilość parametrów rzadko będzie przekraczać kilka, więc nie będzie to miało wpływu na wydajność.
Wykorzystując podobną technikę, chciałem napisać funkcję dodającą element wraz z wszystkimi potrzebnymi informacjami, dość szybko przekonałem się jednak że nie potrafię tego zrobić, a jeśli nawet udało by mi się to to napewno robię coś źle, bo jest to zbyt skomplikowane jak na takie proste zapytanie i napewno da się to zrobić lepiej... Chodzi dokładnie o ten
Kod: Zaznacz cały
-- dodanie wiązanie pomiędzy parametrem o znanym ID z nowo wstawioną wartością
-- INSERT INTO `test`.`wiazanie_elementyWartosci` (`id_parametru`, `ID_elementu`, `ID_wartosci`) VALUES (p, e , w);
SET @w = 1 ;
WHILE @w < 3 DO
SET @w = @w+1;
END WHILE;
kawałek kodu w funkcji addItem, funkcja ta ma za zadanie.
Dodać element, zapisać jego ID, dodać wartości (to akurat było łatwe
) zapisać ID ostatniej wartości i... dodać wiązania parametrów z wartościami...
To ostanie jest sporym problemem. Mam id_parametru (jest w zmiennej _parameters, zapisanej jak wyżej), mam ID elementu (dostaję po dodaniu nowego parametru), oraz mam ID_
OSTATNIEJwartości. Znaczy to że w tym whil'u muszę zapisywać wiązania po kolei tworząc stringa w którym usuwam jedną wartość i zastępuje ją wartością ID_wartości pomniejszoną o "w"... to jest kompletnie bez sensu i nawet nie będę próbował tego tak robić... (nie chcę stosować żadnych funkcji które trzeba doinstalowywać jako zewnętrzne pakiety, być może kiedyś będę się przesiadał na inny serwer i będę miał problemy z przenośnością kodu)
A chcę zrobić to jedną procedurą ze względu na to że serwer SQL jest daleko ode mnie i i wykonywanie kilku zapytań zajmuje sporo czasu, samo dodanie elementu instrukcja po instrukcji zajmuje dobre 1.5s, jako 'ciekawostkę" dodam że wykonanie np. 1000 zapytań do bazy, przez 1 klienta trwa 100s, a wykonanie 1000 zapytań przez 20 klientów trwa... 100s
więc chcę zamknąć całość w procedurze i przesyłać tylko minimalną ilość danych.
Gdyby coś było nie zrozumiałe proszę pisać, postaram się wytłumaczyć
Jak ktoś ma pomysł jak wykonać takie zapytanie to proszę o podzielenie się informacją
Pozdrawiam Zawisza
Kod: Zaznacz cały
CREATE PROCEDURE `test`.`addItem` (
IN _name VARCHAR(128),
IN _desc TEXT,
IN _groupID INT,
IN _caseID INT,
-- TYLKO WARTOŚCI BEZ PARAMETRÓW!! w formie'"vartość 1";;"wartość 2"'
IN _values TEXT,
IN _parameters TEXT)
BEGIN
DECLARE newItemID INT;
DECLARE lastValID INT;
DECLARE w INT ;
DECLARE indexBeg INT;
DECLARE indexEnd INT;
-- Dodawanie nowego elementu
INSERT INTO elementy (`nazwa_elementu`, `opis`, `id_obudowy`, `id_grupy`)
VALUES (_name, _desc, _caseID, _groupID);
-- odczytywanie ID nowego elementu
SET @newItemID = (SELECT last_insert_id());
-- Odczytywanie wartości parametrów i zapisywanie ich w tabeli
SET @values = REPLACE( _values, ';;', '),(');
SET @values = CONCAT('(', @values, ')');
SET @insert = CONCAT('INSERT INTO wartosci VALUES', @values);
PREPARE stmt FROM @insert;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Odczytanie ID ostatniego elementu
SET @lastValID = (SELECT last_insert_id());
-- dodanie wiązanie pomiędzy parametrem o znanym ID z nowo wstawioną wartością
-- INSERT INTO `test`.`wiazanie_elementyWartosci` (`id_parametru`, `ID_elementu`, `ID_wartosci`) VALUES (1, 16, 456);
SET @w = 1 ;
-- 3 poniżej powinna być ilością parametrów przekazanych do funkcji
WHILE @w < 3 DO
-- HMM...
SET @w = @w+1;
END WHILE;
END
Kod: Zaznacz cały
CREATE DEFINER=`root`@`%` PROCEDURE `checkItem`(
OUT _elementID INT,
OUT _status INT,
IN _name varchar(128),
IN _groupID INT,
IN _caseID INT,
IN _parameters TEXT) -- Parametry w postaci stringa: 'ID_Parametru;"Wartsc";;ID_Parametru;"Wartosc"' itd.
BEGIN
--
-- Procedura zwraca (przez wskaźnik _elementID, _status) wartości
-- 0 jeśli niema elementu
-- 1 jeśli element o podanej nazwie i obudowie istnieje, ale ma inne parametry
-- 2 jeśli element o podanej nazwie istnieje i ma takie same parametry
DECLARE parameterCount INT;
-- Wyfiltruj elementy z danej grupy o danych obudowach (olej opis!)
DROP TABLE IF EXISTS elementsList;
CREATE TEMPORARY TABLE elementsList ENGINE=MEMORY AS (
SELECT
elementy.idelementy AS ID,
elementy.nazwa_elementu AS name,
elementy.id_obudowy AS caseID,
elementy.id_grupy AS groupID ,
parametry.idparametry AS parameterID,
parametry.nazwa_parametru AS parameterName,
wartosci.wartosci AS value
FROM test.elementy
INNER JOIN wiazanie_elementyWartosci ON test.elementy.idelementy = test.wiazanie_elementyWartosci.ID_elementu
INNER JOIN wartosci ON test.wiazanie_elementyWartosci.ID_wartosci = wartosci.idwartosci
INNER JOIN parametry ON id_parametru = idparametry
INNER JOIN obudowy ON elementy.id_obudowy = obudowy.id_obudowy
WHERE ( elementy.id_grupy = _groupID ) AND (nazwa_elementu = _name) AND ( elementy.id_obudowy = _caseID ));
-- jeśli dany element jest w bazie
IF ( SELECT COUNT(1) FROM elementsList )
THEN
-- nie jest potrzebne ale w przypadku kiedy coś się stanie i tablica nie zwolni pamięci ratuje dupe
DROP TABLE IF EXISTS t;
CREATE TEMPORARY TABLE t ( id INT, val varchar(128) ) ENGINE=MEMORY;
SET @values = REPLACE( _parameters, ';;', '),(');
SET @values = REPLACE( @values , ';', ',');
SET @values = CONCAT('(', @values, ')');
SET @insert = CONCAT('INSERT INTO t VALUES', @values);
-- Execute INSERT statement
PREPARE stmt FROM @insert;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @parameterCount = (SELECT COUNT(1) FROM t);
-- sprawdź czy jest jakiś element w tymczasowej tablicy
IF EXISTS (SELECT 1 FROM elementsList
INNER JOIN t ON (t.id = parameterID) AND (t.val = value)
GROUP BY elementsList.ID HAVING COUNT(*) = @parameterCount LIMIT 0,1 )
-- jest jakiś element który ma takie same parametry nazwe itd!!
-- Ustaw status na 2 (znaleziono) i _elementID na znalezione ID
THEN
SET _status = 2;
SET _elementID = ( SELECT elementsList.ID
FROM elementsList
INNER JOIN t ON (t.id = parameterID) AND (t.val = value)
GROUP BY elementsList.ID HAVING COUNT(*) = @parameterCount LIMIT 0,1 );
ELSE
-- znaleziono element niestety parametry się nie zgadzajo
SET _status = 1;
END IF;
DROP TABLE elementsList;
DROP TABLE t;
-- jeśli elementu niema ustaw _status i IDElementu na NULL (nie rób z nim nic)
ELSE
SET _status = 0;
END IF;
END
