MySQL, trigger, pętle i zmienne :)

[krzyszp]

Mam dość ciekawy problem do rozwiązania.
Potrzebuję napisać trigger do mysql'a wykonywanego za każdym update na jednej tabeli... Ale, żeby nie było za prosto, cała operacja ma wyglądać tak:

1. Select na jednej tabeli, w wyniku jedno pole z ID rekordu (zwykły select, zwraca kilkaset rekordów).
2. Dla każdego ze zwróconych rekordów kolejny select, gdzie kluczem jest właśnie pobrana wartość z pierwszego zapytania 1, zwracane jest jedno pole z wartością INTEGER.
3. Update na dwóch tabelach z ID z pierwszego zapytania i wartością otrzymaną z wyniku z drugiego zapytania zapytania.

Więc problemem dla mnie jest zrobienie odpowiednich pętli, gdzie wynik zapytań będzie przekazywany do kolejnych zapytań...

Całość w VB6 wygląda tak:

Kod [Zaznacz] Rozwiń

sSQL = "Select KitID FROM tblstockkits"
rsS.OpenRs sSQL, cnM, adOpenStatic, adLockReadOnly
Do While Not rsS.EOF

    sSQL = "SELECT MIN(((tblstock.StockQty - tblstock.DueOut) / tblstockkitsconts.Qty)) as Available " & _
            "FROM (`roads-prod`.tblstockkitsconts tblstockkitsconts INNER JOIN`roads-prod`.tblstock tblstock " & _
            "ON (tblstockkitsconts.StockID = tblstock.StockID)) INNER JOIN`roads-prod`.tblstockkits tblstockkits " & _
            "ON (tblstockkits.KitID = tblstockkitsconts.KitID) Where tblstockkits.KitID = " & rsS.Fields(0)
    rsK.OpenRs sSQL, cnM, adOpenStatic, adLockReadOnly
        If Not rsK.EOF Then
            If rsK.Fields(0) > 0 Then
                sSQL = "UPDATE tblstockkits SET FreeStock = " & Int(rsK.Fields(0)) & " WHERE KitID = " & rsS.Fields(0)
                cnM.Execute sSQL
                sSQL = "UPDATE tblebayitems SET FreeStock = " & Int(rsK.Fields(0)) & " WHERE KitID = " & rsS.Fields(0)
                cnM.Execute sSQL
        End If
    rsK.CloseRecordset
    rsS.MoveNext
Loop
rsS.CloseRecordset

Obecnie całość tej pętli zajmuje ok 4-6s (wykonuje około 650 update'ów), celem jest przerzucenie tej roboty na serwer.

[Bezprym]

Co powiesz na takie rozwiązanie:

1. Utworzenie widoku , opartego na zapytaniu:

                    CREATE VIEW Widok1 AS
                    SELECT
                      tblstockkitsconts.KitID
                      ,MIN(((tblstock.StockQty - tblstock.DueOut) / tblstockkitsconts.Qty)) as Available
                    FROM tblstockkitsconts tblstockkitsconts
                    INNER JOIN tblstock tblstock
                     ON tblstockkitsconts.StockID = tblstock.StockID
                    INNER JOIN tblstockkits tblstockkits
                     ON tblstockkits.KitID = tblstockkitsconts.KitID
(bez warunku WHERE, dla wszystkich KitID)

2. Wewnątrz triggera dwa UPDATE:
UPDATE tblstockkits SET FreeStock = Widok1.Available FROM tblstockkits  JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID
i drugie analogicznie.

[krzyszp]

Zapytanie do triggera zwraca błąd:

Kod [Zaznacz] Rozwiń

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM tblstockkits  JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID' at line 3
i faktycznie jakoś dziwnie wygląda...

[Bezprym]

Piszę trochę na "sucho", bo nie mam dostępu do serwera MySQL, a na dodatek już minęło trochę czasu, odkąd miałem do czynienia z jego składnią :)
Chodzi mi w każdym razie o wykonanie UPDATE na podstawie złączenia z zewnętrzną tabelą/widokiem.

Spróbuj:
UPDATE tblstockkits
JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID
SET FreeStock = Widok1.Available FROM tblstockkits 

To jest zgodne ze składnią na forum MySQL:

Kod [Zaznacz] Rozwiń

http://forums.mysql.com/read.php?97,45724,45724

[krzyszp]

Mi cały czas to "FROM" nie pasuje, zakończyłem tak:

Kod [Zaznacz] Rozwiń

CREATE TRIGGER bar AFTER UPDATE ON tblstock
FOR EACH ROW BEGIN
UPDATE tblstockkits JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID SET FreeStock = Widok1.Available;
UPDATE tblebayitems  JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID SET FreeStock = Widok1.Available FROM tblstockkits ;
END
DELIMITER ;
Jak widzisz, w 3 lini skasowałem "FROM", ale teraz dostaję dziwny error:

Kod [Zaznacz] Rozwiń

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
Czyli z pierwszym update'em dalej coś nie tak (z drugim też, ale jeszcze do tego nie doszedł).

Widok zrobiłem wg Twojego przepisu i działa (chyba) poprawnie.
Co ciekawe, update poza triggerem działa ok (ale zmienia tylko jeden rekord, co być może jest dobrze, nie mam jak sprawdzić w tej chwili).

[Bezprym]

Co ciekawe, update poza triggerem działa ok (ale zmienia tylko jeden rekord, co być może jest dobrze, nie mam jak sprawdzić w tej chwili).

To może wywołania UPDATE umieścisz wewnątrz kodu Visual Basic?

Czy przeniesienie pętli na serwer SQL miało na celu skrócenie czasu wykonania?
Myślę, że te dwa UPDATE pójdą błyskawicznie . Pętle działały wolno, ponieważ zapytanie, które umieściłem w widoku wykonywane było tyle razy, ile jest wierszy w tblstockkits.
Moje UPDATE redukują to do dwóch razy, niezależnie od zawartości tblstockkits.

[krzyszp]

Tak, chcę przenieść na serwer, bo to jest średnio 650 update'ow co kilka minut - za każdym razem, jak któryś z userów doda jakiś produkt (spośród 3k produktów) do zamówienia, zmienia się ilość towaru do wykorzystania (wolngo) na magazynie. Dużą rolę odgrywa tu właśnie używanie tzw. "kitów" - są to grupy produktów tworzące nowy produkt - więc za każdą zmianą ilości jakiegoś towaru musi być także aktualizowana wartość dla kitów, które zawierają ten produkt.
W efekcie powstaje "pętla w pętli w pętli" ;) i tę robotę chcę przenieść na serwer.

[Bezprym]

Spróbuj w takim razie zamienić kod z pętlami na moje dwa UPDATE. Liczba 650 zredukuje się do 2 :)

Jeśli tak czy inaczej będziesz chciał pracować raczej na serwerze, to już nie mogę Ci pomóc, bo nie mam serwera MySQL w podorędziu. Trzeba będzie trochę poszperać w internecie i poeksperymentować z definicją triggera, żeby zadziałał.

[krzyszp]

Generalnie dobry pomysł (przeniesienie tych update do vb i korzystanie z widoku.
Niemniej, pozostaje kwestia widoku - jednak nie wykonuje się ok... Mam tylko jeden rekord z niego, więc stany się nie przeliczają.

Bezprym, jak masz ochotę pomóc, to dam Ci dostęp do PHPMyAdmina u mnie na testowym serwerze z tą bazą...

[Bezprym]

Dodaj:
GROUP BY tblstockkitsconts.KitID
na koniec zapytania tworzącego widok - teraz widzę, że to pominąłem :)

Pewnie że pomogę, ale będę mógł dopiero za 2-3 godziny.

[krzyszp]

Poszły dane na PW - group pomógł, testuję teraz, czy wszystko się prawidłowo aktualizuje.

Niemniej, taki trigger jak napisałem na początku by mnie uszczęśliwił, bo bazując na nim wyeliminowałbym bardzo dużo zapytań z aplikacji, co przy połączeniu zdalnym robi ogromną różnicę.

[Bezprym]

Wykonałem takie polecenie:

Kod [Zaznacz] Rozwiń

delimiter |

CREATE TRIGGER ag_bar AFTER UPDATE ON tblstock
FOR EACH ROW BEGIN
UPDATE tblstockkits JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID SET FreeStock = Widok1.Available;
UPDATE tblebayitems  JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID SET FreeStock = Widok1.Available  ;
END
|

delimiter ;

i trigger został utworzony.
Widok zdaje się też działać prawidłowo.

Przetestuj, może mamy już to, czego szukamy :)

[krzyszp]

DZIAŁA!!!

Dzięki wielkie :)

[Bezprym]

No to super  :p_arr: