gistfile1.php

      // Attempting to add Strip Text using a INSERT INTO ... SELECT query kills
      // the execution time.
      //
      // GROUP_CONCAT(DISTINCT Strip_Text_Table.Text SEPARATOR "\n\n") AS Strip_Text ... LEFT JOIN Strip_Text AS Strip_Text_Table ON ((Strip_Text_Table.Parent_Table = "Panels" AND Strip_Panels.ID = Strip_Text_Table.Parent_ID) OR (Strip_Text_Table.Parent_Table = "Scenes" AND Strip_Panels.Scene_ID = Strip_Text_Table.Parent_ID))
      //
      // Locations_Used_Inherited uses logic that would be difficult or
      // impossible to implement using SQL.
      //
      // The User_Strip_Characters and User_Strip_Locations tables must be
      // compiled piecemeal to increment the ProminenceTally. Even if SQL logic
      // could be used to populate User_Strip_Characters, User_Strip_Locations
      // must still be compiled in this way to account for Locations_Used_
      // Inherited.
      //
      $db->query('INSERT INTO User_Strips SELECT Strip_Dates.Strip_Date, Strip_Dates.Strip_ID AS ID, DATE_FORMAT(Strip_Dates.Strip_Date, "%W") AS Day, COUNT(DISTINCT Strip_Panels.ID) AS Panels, COUNT(DISTINCT Strip_Panels.Scene_ID) AS Scenes, IF(Strip_Dates.Strip_Date = (SELECT MIN(Strip_Dates2.Strip_Date) FROM Strip_Dates AS Strip_Dates2 WHERE Strip_ID = Strip_Dates.Strip_ID),"No","Yes") AS Duplicate, IF(COUNT(DISTINCT Strip_Scene_Characters.Character_ID) > 0, CONCAT("-",GROUP_CONCAT(DISTINCT Strip_Scene_Characters.Character_ID SEPARATOR "-"),"-"), NULL) AS Characters_Appear, IF(COUNT(DISTINCT Strip_Subject_Characters.Subject_ID) > 0, CONCAT("-",GROUP_CONCAT(DISTINCT Strip_Subject_Characters.Subject_ID SEPARATOR "-"),"-"), NULL) AS Characters_Mentioned, CONCAT("-",GROUP_CONCAT(DISTINCT Strip_Scenes.Location SEPARATOR "-"),"-") AS Locations_Used, NULL AS Locations_Used_Inherited, IF(COUNT(DISTINCT Strip_Subject_Locations.Subject_ID) > 0, CONCAT("-",GROUP_CONCAT(DISTINCT Strip_Subject_Locations.Subject_ID SEPARATOR "-"),"-"), NULL) AS Locations_Mentioned, Storyline_Strips.Storyline_ID, NULL AS Strip_Text, SUBSTRING_INDEX(Strips.Notes, "\n\n", 1) AS Summary, IF(LOCATE("\n\n", Strips.Notes) IS NOT NULL AND LOCATE("\n\n", Strips.Notes) != 0, Strips.Notes, NULL) AS Notes FROM Strip_Dates LEFT JOIN Strip_Panels ON Strip_Dates.Strip_ID = Strip_Panels.Strip_ID LEFT JOIN Strip_Scene_Characters ON Strip_Panels.Scene_ID = Strip_Scene_Characters.Scene_ID LEFT JOIN Strip_Subjects AS Strip_Subject_Characters ON Strip_Subject_Characters.Subject_Table = "Characters" AND Strip_Dates.Strip_ID = Strip_Subject_Characters.Strip_ID LEFT JOIN Strip_Scenes ON Strip_Panels.Scene_ID = Strip_Scenes.ID LEFT JOIN Strip_Subjects AS Strip_Subject_Locations ON Strip_Subject_Locations.Subject_Table = "Locations" AND Strip_Dates.Strip_ID = Strip_Subject_Locations.Strip_ID LEFT JOIN Storyline_Strips ON Strip_Dates.Strip_ID = Storyline_Strips.Strip_ID LEFT JOIN Strips ON Strip_Dates.Strip_ID = Strips.ID GROUP BY Strip_Dates.Strip_Date ORDER BY Strip_Dates.Strip_Date ASC');

      scriptTimer($timer, 'initial User_Strip insertion'); // 1 Second

      $dates = $db->query('SELECT Strip_Date, ID, Duplicate, Characters_Appear, Characters_Mentioned, Locations_Used, Locations_Mentioned FROM User_Strips ORDER BY Strip_Date ASC');
      $dates = $dates->fetchAll(PDO::FETCH_ASSOC);

      $stripTextQuery = $db->prepare('SELECT Text FROM Strip_Text WHERE (Parent_Table = "Scenes" AND Parent_ID IN(SELECT Scene_ID FROM Strip_Panels WHERE Strip_ID = ?)) OR (Parent_Table = "Panels" AND Parent_ID IN(SELECT ID FROM Strip_Panels WHERE Strip_ID = ?))');
      $updateQuery = $db->prepare('UPDATE User_Strips SET Locations_Used_Inherited = ?, Strip_Text = ? WHERE Strip_Date = ?');
      $charactersUpdateQuery = $db->prepare('INSERT User_Strip_Characters SET Strip_ID = ?, Character_ID = ?, Type = ?, ProminenceTally = ?');
      $locationsUpdateQuery = $db->prepare('INSERT User_Strip_Locations SET Strip_ID = ?, Location_ID = ?, Type = ?, ProminenceTally = ?');

      $locationParents = array();

      foreach ($dates as $date) {

        // Commented out because Strip_Text, which is not publically exposed as
        // a constraint in the user interface, is one of the most time-intensive
        // portions of user table rebuild.
//        $stripTextQuery->execute(array($date['ID'], $date['ID']));
//        $stripText = $stripTextQuery->fetchAll(PDO::FETCH_COLUMN);
//
//        if (count($stripText) > 0)
//          $strip['Strip_Text'] = implode("\n\n", $stripText);
//        else
          $strip['Strip_Text'] = null;

        $locationsUsed = explode('-', substr($date['Locations_Used'], 1, -1));
        $locationsInherited = array();

        foreach ($locationsUsed as $location) {
          if (!isset($locationParents[$location])) {
            $locationParents[$location] = array('0');

            while (true) {
              $parentsQuery = $db->query('(SELECT DISTINCT Parent_ID AS Parent FROM Locations WHERE (ID = '.$location.' OR ID IN('.implode(',',$locationParents[$location]).')) AND Parent_ID != 0) UNION DISTINCT (SELECT DISTINCT Parent_ID AS Parent FROM Location_SecondaryParents WHERE Location_ID = '.$location.' OR Location_ID IN('.implode(',',$locationParents[$location]).')) ORDER BY Parent ASC');
              $newParents = $parentsQuery->fetchAll(PDO::FETCH_COLUMN);

              if (count($newParents) === 0) {
                $locationParents[$location] = $newParents;
                break;
              }
              elseif ($newParents === $locationParents[$location])
                break;
              else
                $locationParents[$location] = $newParents;
            }
          }

          $locationsInherited = array_unique(array_merge($locationsInherited, $locationParents[$location]));
        }

        $locationsInherited = array_diff($locationsInherited, $locationsUsed);

        if ($locationsInherited)
          $strip['Locations_Used_Inherited'] = '-'.implode('-',$locationsInherited).'-';
        else
          $strip['Locations_Used_Inherited'] = null;

        $updateQuery->execute(array($strip['Locations_Used_Inherited'], $strip['Strip_Text'], $date['Strip_Date']));

        if ($date['Duplicate'] === 'Yes')
          continue;

        if (!is_null($date['Characters_Appear']))
          $charactersAppear = explode('-', substr($date['Characters_Appear'], 1, -1));
        else
          $charactersAppear = array();

        if (!is_null($date['Characters_Mentioned']))
          $charactersMentioned = explode('-', substr($date['Characters_Mentioned'], 1, -1));
        else
          $charactersMentioned = array();

        if (!is_null($date['Locations_Mentioned']))
          $locationsMentioned = explode('-', substr($date['Locations_Mentioned'], 1, -1));
        else
          $locationsMentioned = array();

        foreach (array_merge($charactersAppear, $charactersMentioned) as $character) {
          if (!isset($characters[$character]))
            $characters[$character] = 1;
          else
            $characters[$character]++;
        }

        foreach ($charactersAppear as $character) {
          $charactersUpdateQuery->execute(array($date['ID'], $character, 'Appearance', $characters[$character]));
        }

        foreach ($charactersMentioned as $character) {
          $charactersUpdateQuery->execute(array($date['ID'], $character, 'Mention', $characters[$character]));
        }

        foreach (array_merge($locationsUsed, $locationsInherited, $locationsMentioned) as $location) {
          if (!isset($locations[$location]))
            $locations[$location] = 1;
          else
            $locations[$location]++;
        }

        foreach ($locationsUsed as $location) {
          $locationsUpdateQuery->execute(array($date['ID'], $location, 'Use', $locations[$location]));
        }

        foreach ($locationsInherited as $location) {
          $locationsUpdateQuery->execute(array($date['ID'], $location, 'Inherited', $locations[$location]));
        }

        foreach ($locationsMentioned as $location) {
          $locationsUpdateQuery->execute(array($date['ID'], $location, 'Mention', $locations[$location]));
        }
      }

      $db->query('UPDATE Strips SET Changed = "No"');

      scriptTimer($timer, 'supplemental User_Strip insertions'); // 257 Seconds