PDO-Vorlagenklasse

[rkr]

Das habe ich jetzt auf die Schnelle zusammengehaun. Da sind sicher einige Fehler drin, weil ich die OIriginaltabellen nicht kenne:

$startDate = null;
$endDate = null;
$articleId = null;

$tourIds = $select->select()
->field('tour_id')
->from('kv2', 'wh_kit_version');

$maxVersion = $select->select()
->field('MAX(version)')
->from('ttour', 'wh_transaction_tour')
->where('kv2.kit_id = k1.kit_id');

$toursRange2 = $select->select()
->field('tour1.tour_id')
->field('(-1)', 'act_type')
->from('tour1', 'tour')
->where('tour1.tour_begin >= ?', $startDate)
->where('tour1.tour_begin <= ?', $endDate);

$toursRange = $select->select()
->field('tour1.tour_id')
->field('(1)', 'act_type')
->from('tour1', 'tour')
->where('DATE_ADD(tour_begin, INTERVAL 7 DAY) >= ?', $startDate)
->where('DATE_ADD(tour_begin, INTERVAL 7 DAY) <= ?', $endDate)
->unionAll($toursRange2);

$inner = $select->select()
->field('DATE_ADD(DATE(act_begin), INTERVAL IF(tour.act_type = -1, 1, 0) WEEK)', 'act_date')
->field('CASE WHEN ka1.aggregate = 1 THEN SUM(CASE WHEN tour.act_type = -1 THEN CASE WHEN ag1.ag_consumable = 0 THEN ka1.amount * -1 END ELSE ka1.amount END) ELSE CASE WHEN tour.act_type = -1 THEN MAX(ka1.amount) * -1 ELSE MAX(ka1.amount) END END', 'amount')
->from('tour', $toursRange)
->joinInner('a', 'activity', 'tour.tour_id = a.tour_id')
->joinInner('ao', 'activity_operation', 'tour.activity_id = a.activity_id')
->joinInner('k1', 'wh_kit', 'tour.operation_id = k1.operation_id')
->joinInner('kv1', 'wh_kit_version', 'tour.kit_id = kv1kit_id')
->joinInner('ka1', 'wh_kit_article', 'k1.kit_id = ka1.kit_id AND ka1.version_id = kv1.version_id')
->joinInner('a1', 'wh_article', 'a1.article_id = ka1.article_id')
->joinInner('ag1', 'wh_article_group', 'ag1.article_group_id = a1.article_group_id')
->where('tour.tour_id IN (?)', $tourIds)
->where('kv1.version = ?', $maxVersion)
->where('a1.article_id = ?', $articleId)
->groupBy('tour.tour_id', 'tour.act_date', 'tour.act_type', 'a1.article_id', 'ka1.amount', 'ka1.aggregate');

echo $select->select()
->field('t.article_id', 'articleId')
->field('SUM(t.amount)', 'amount')
->field('t.act_date', 'act_date')
->from('t', $inner)
->groupBy('t.article_id', 't.act_date'); 

Das CASE-Statement muss ich mir noch mal genauer ansehen.

SELECT
    t.article_id AS `articleId`,
    SUM(t.amount) AS `amount`,
    t.act_date AS `act_date`
FROM
    (SELECT
        DATE_ADD(DATE(act_begin), INTERVAL IF(tour.act_type = -1, 1, 0) WEEK) AS `act_date`,
        CASE WHEN ka1.aggregate = 1 THEN SUM(CASE WHEN tour.act_type = -1 THEN CASE WHEN ag1.ag_consumable = 0 THEN ka1.amount * -1 END ELSE ka1.amount END) ELSE CASE WHEN tour.act_type = -1 THEN MAX(ka1.amount) * -1 ELSE MAX(ka1.amount) END END AS `amount`
    FROM
        (SELECT
            tour1.tour_id,
            (1) AS `act_type`
        FROM
            tour tour1
        WHERE
            (DATE_ADD(tour_begin, INTERVAL 7 DAY) >= NULL)
            AND
            (DATE_ADD(tour_begin, INTERVAL 7 DAY) <= NULL)
        
        UNION ALL
        SELECT
            tour1.tour_id,
            (-1) AS `act_type`
        FROM
            tour tour1
        WHERE
            (tour1.tour_begin >= NULL)
            AND
            (tour1.tour_begin <= NULL)) tour
    INNER JOIN
        activity a ON tour.tour_id = a.tour_id
    INNER JOIN
        activity_operation ao ON tour.activity_id = a.activity_id
    INNER JOIN
        wh_kit k1 ON tour.operation_id = k1.operation_id
    INNER JOIN
        wh_kit_version kv1 ON tour.kit_id = kv1kit_id
    INNER JOIN
        wh_kit_article ka1 ON k1.kit_id = ka1.kit_id AND ka1.version_id = kv1.version_id
    INNER JOIN
        wh_article a1 ON a1.article_id = ka1.article_id
    INNER JOIN
        wh_article_group ag1 ON ag1.article_group_id = a1.article_group_id
    WHERE
        (tour.tour_id IN ((SELECT
        tour_id
    FROM
        wh_kit_version kv2
    )))
        AND
        (kv1.version = (SELECT
        MAX(version)
    FROM
        wh_transaction_tour ttour
    WHERE
        (kv2.kit_id = k1.kit_id)
    ))
        AND
        (a1.article_id = NULL)
    GROUP BY
        tour.tour_id,
        tour.act_date,
        tour.act_type,
        a1.article_id,
        ka1.amount,
        ka1.aggregate) t
GROUP BY
    t.article_id,
    t.act_date 

So einen komplexen Fall hatte ich aber in der Tat noch nicht. Ich sehe aber auch einige Fälle, die man drastisch vereinfachen kann. Beispielsweise gibt du beim UnionStatement -1 oder 1 als Typ zurück. Würdest du 0 und 1 zurückgeben, würdest du aus:

CASE WHEN type = -1 THEN DATE_ADD(DATE(act_begin), INTERVAL 1 WEEK)
ELSE DATE(act_begin)
END 

DATE_ADD(DATE(act_begin), INTERVAL tour.act_type WEEK) 

machen können.

Oder aus

CASE
    WHEN ka1.aggregate = 1 THEN SUM(CASE WHEN tour.act_type = -1 THEN CASE WHEN ag1.ag_consumable = 0 THEN ka1.amount * -1 END ELSE ka1.amount END)
    ELSE
    CASE
    WHEN tour.act_type = -1 THEN MAX(ka1.amount) * -1
    ELSE MAX(ka1.amount) END
END 

eher

CASE
    WHEN ka1.aggregate = 1 THEN SUM(CASE WHEN tour.act_type = 0 AND ag1.ag_consumable = 0 THEN ka1.amount * -1 ELSE ka1.amount END)
    ELSE MAX(ka1.amount) * (tour.act_type - 1)
END 

Wahrscheinlich kann man das noch weiter vereinfachen. Naja.

[derwunner]

Ja, ein Beispiel wäre das hier: https://gist.github.com/chrisandchri...e78e8b68975185

Na Prost Mahlzeit! So eine unnötig komplizierte Implementierung zur Generierung in PHP möchte ich mal sehen xD

Ich verstehe nicht, warum viele immer versuchen, unnötig komplizierte queries auf Biegen und Brechen zu machen. Stattdessen wäre es wesentlich leichter, wesentlich performanter und wesentlich leichter zu lesen mal ein Stored Procedure zu machen. Aber dazu bin ich wohl zu old school, soetwas scheint heutzutage nicht mehr sexy zu sein...

[rkr]

Na Prost Mahlzeit! So eine unnötig komplizierte Implementierung zur Generierung in PHP möchte ich mal sehen xD

Ich verstehe nicht, warum viele immer versuchen, unnötig komplizierte queries auf Biegen und Brechen zu machen. Stattdessen wäre es wesentlich leichter, wesentlich performanter und wesentlich leichter zu lesen mal ein Stored Procedure zu machen. Aber dazu bin ich wohl zu old school, soetwas scheint heutzutage nicht mehr sexy zu sein...

Nutze ich tatsächlich, wenn es sinn macht.

Eigentlich sprichst du ja von Stored Functions, richtig? Stored Procedures haben keinen Rückgabewert. Du kannst zwar theoretisch ein SQL-Statement darin ausführen, aber Stored Functions sind dafür gebräuchlicher.

Allerdings haben Stored Functions ziemlich schnell unschöne Grenzen:

Sie sind nicht dynamisch.
Wenn ich eine Stored Function schreibe, dann kann ich so was nicht machen:

$select = $db->select()
->from('p', 'products')
->where('p.active = 1')
->where('products#in_stock(p.id)');

if($categoryId) {
    $select->select()
    ->from('ptc', 'products_to_categories', 'ptc.product_id=p.id')
    ->where('ptc.category_id=?', $categoryId);
}

$eventDispatcher->fire('ec.products.list.query.extend', ['select' => $select]);

$rows = $select->fetchRows(); 

Glaube mir, solche Queries können richtig komplex werden.

Und nein, du willst keine Prepared Statements in Stored Funtions benutzen.

Sie sind nicht objektorientiert.
Stored Functions sind ein Mekka für Spaghetti-Code. Hast du mal versucht etwas komplexeres mit Ihnen zu implementieren? Ich habe ein Projekt, da habe ich mittlerweile über 50 Stored Functions. Das fühlt sich nach sehr viel an, weil die Dinger total schmerzhaft zu bearbeiten sind. Besonders, wenn man einen Migration-Manager für die Datenbankversionierung nutzt und benötigt. Dank fehlender Datenkapselung und dem miesen SQL-Dialekt für Programmstrukturen (MySQL) ist auch die Erstellung von Subroutinen in SQL alles andere als schön. Da nehme ich teils gerne in Kauf, dass ich etwas in PHP habe, was eigentlich besser und performanter in der DB liegen könnte.

Das Anliegen von SF etwas Komplexes hinter einem einfachen Interface zu verstecken, endet häufig damit, etwas eigentlich einfaches wesentlich komplexer zu machen, als es eigentlich sein müsste.

In PostgreSQL kann ich PL/Python nutzen - da gilt dieses Argument nicht so sehr.

Sie sind schwer kontrollierbar.
Da sie in der Datenbank liegen, stellt sich Refaktorierung mit Ihnen alles andere als einfach dar. Wenn ich die Parameterliste einer Stored Function ändern will, dann muss ich wissen, wo ich sie in meinen Projekten verwende. Da ich einen dezentralen Projektansatz verfolge, kann das schnell richtig unübersichtlich werden. Ich habe schon ein paar Mal versucht, SF über PHP-Funktionen zu mappen, damit mir PHPStorm deren Verwendung anzeigen kann - bislang habe ich noch kein Rezept gefunden, dass mich zufrieden stellt.

Stored Functions sind eher selten das richtige Rezept.
Stored Functions sind häufig viel langsamer als einfache Joins. Besonders da, wo sie selbst wieder ein DB-Statement ausführen, kann ihr Daseinszweck häufig durch ein einfaches Join wesentlich entkräftet werden.

Ich sehe Stored Functions viel mehr als eine Möglichkeit, ständig wiederkehrende Muster zu vereinheitlichen und so alle abhängigen Konsumenten (Projekte) auf eine einheitliche Linie zu bringen.

Stored Functions sind in ihren Möglichkeiten eingeschränkt.
Stored Functions können nicht (sollen nicht) auf Informationen außerhalb der eigenen Datenbank zugreifen. Das soll auch so sein und hängt nicht zuletzt zum Teil auch mit der funktionalen Natur von SQL zusammen, die oft davon ausgehen muss, dass Daten sich nicht plötzlich während eines Statements ändern können (Determinismus). Das schränkt aber die Möglichkeiten von SF oft stark ein.

Stored Functions sind in der Entwicklung sehr sperrig.
Schon mal versucht eine Stored Function (oder eine Stored Procedure) zu debuggen?


Aber, äh, ja. Prost!

[derwunner]

rkr Mag sein, dass wir gerade unterschiedliche Vorstellung von der Begrifflichkeit "Stored Procedures" haben. Soviel ich weiß ist das auch die offizielle Bezeichnung laut SQL Standard. Den Begriff an und für sich hatte ich jedoch nur auf der integrierten IBM DB2 auf einem System i (iOS5) von Big Blue gefunden. Im MySQL "Slang" nennt sich das ganze dann nur "Procedures", so zumindest im Database Manger von MySQL. Letzendlich sollten wir schon vom selben reden, allerdings mit anderen Namen für das Kind. Der Aufbau eines Stored Procedures sieht in der Regel immer so aus:

CREATE PROCEDURE meinProcedure
-- IN/OUT Parameter
BEGIN;
-- variable deklarieren für die SQL-Schleife:
DECLARE var1 as int,
-- Cursor für den Query anlegen
-- Query selbst definieren und z. B. var1 der ersten SELECT Spalte zuweisen
-- etwas tun in der Leseschleife, z. B. UPDATE, INSERT oder DELETE, etc.
-- falls notwendig, etwas als OUT Parameter zurückgeben
END;

Jedenfalls so oder so ähnlich. Den genauen Syntax dafür müsste ich auch erst wieder nachlesen. Außerdem varriert der auch stark von DB zu DB. An den wirklichen SQL-Standard hält sich da kaum ein Hersteller. Hoffe, ich konnte nun klarstellen, was ich damit meinte und ob wir evtl. vom selben sprachen.

PS: Ja, danke für das Anstoßen

Edit: Ich finde das jedenfalls sehr viel leichter und übersichtlicher, als alles in einem SELECT Query zu stopfen. Denn technisch gesehen macht es keinen Unterschied, ob man es so oder so macht. Also warum sollte man es sich unnötig schwer machen? Der einzige Unterschied ist halt, dass ein Stored Procedure in der DB gespeichert wird und der komplizierte SELECT Query im PHP-Teil.
Ich hatte auch schon von der These gehört, dass wenn man in einem SELECT eine if-Anweisung braucht, dann hat man eh schon von vorneherein was falsch gemacht. Das heißt man sollte in dem Fall nocheinmal seine WHERE Bedingung überdenken...

[rkr]

rkr Mag sein, dass wir gerade unterschiedliche Vorstellung von der Begrifflichkeit "Stored Procedures" haben. Soviel ich weiß ist das auch die offizielle Bezeichnung laut SQL Standard. Den Begriff an und für sich hatte ich jedoch nur auf der integrierten IBM DB2 auf einem System i (iOS5) von Big Blue gefunden. Im MySQL "Slang" nennt sich das ganze dann nur "Procedures", so zumindest im Database Manger von MySQL. Letzendlich sollten wir schon vom selben reden, allerdings mit anderen Namen für das Kind.

MySQL:

Procedure:

CREATE PROCEDURE `testproc`()
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
    /* ... */
END 

Function:

CREATE FUNCTION `testproc`()
    RETURNS INT
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
    /* ... */
    RETURN NULL;
END 

Das wird in anderen System ganz ähnlich sein.

Ich hatte auch schon von der These gehört, dass wenn man in einem SELECT eine if-Anweisung braucht, dann hat man eh schon von vorneherein was falsch gemacht. Das heißt man sollte in dem Fall nocheinmal seine WHERE Bedingung überdenken...

Bis du eine schlüssige Erklärung für diese Steile These bringst, ist das zunächst einfach totaler Bullshit.

[derwunner]

MySQL:

Procedure:

[...]

Function:

[...]

Das wird in anderen System ganz ähnlich sein.

Ja, denn sprach ich definitv vom Procedure in MySQL, denn in einem Procedure gab es keine return Anweisung. Das war schönes altes SQL im COBOL-Stil

Bis du eine schlüssige Erklärung für diese Steile These bringst, ist das zunächst einfach totaler Bullshit.

Naja, das hatte mir mal ein ehemaliger Chef gesagt, welcher auch selber schon länger programmiert als ich. Also musste ich es abnicken^^

[rkr]

Ja, denn sprach ich definitv vom Procedure in MySQL, denn in einem Procedure gab es keine return Anweisung. Das war schönes altes SQL im COBOL-Stil

Wie soll eine Stored Procedure ein langes SQL-Statement vereinfachen!?