Re-order LEFT JOIN pages before LEFT JOIN rpm

[packages/dokuwiki-plugin-data.git] / separate-rpmdb.patch

Separate rpmdb data to rpmdb table, left join via %rpm% field name on demand.

As the rpmdb data is huge comparing to other data, it's best we keep it
in separate table, if it is still a performance problem, we could use
separate datafile.

You need to create 'rpm' table manually:
CREATE TABLE rpmdb (eid INTEGER PRIMARY KEY, pid INTEGER, value);

Handling it automatically would mean have to renumber upstream patches each
time...

--- dokuwiki-plugin-data-20160311/helper.php~	2016-11-22 23:06:06.000000000 +0200
+++ dokuwiki-plugin-data-20160311/helper.php	2016-11-22 23:06:44.267324645 +0200
@@ -385,6 +385,7 @@
             '%title%'   => array('page', 'title'),
             '%pageid%'  => array('title', 'page'),
             '%class%'   => array('class'),
+            '%rpm%'     => array('rpm'),
             '%lastmod%' => array('lastmod', 'timestamp')
         );
         if(isset($specials[$column['title']])) {
--- dokuwiki-plugin-data-20120227/lang/en/lang.php~	2012-02-27 16:32:51.000000000 +0200
+++ dokuwiki-plugin-data-20120227/lang/en/lang.php	2012-03-13 13:12:04.190745746 +0200
@@ -16,6 +16,7 @@
 $lang['page']      = 'Page';
 $lang['title']     = 'Page Name';
 $lang['class']     = 'Page Class';
+$lang['rpm']       = 'RPM Package';
 $lang['lastmod']   = 'Last Modified';
 
 $lang['name']      = 'Type Alias';
--- dokuwiki-plugin-data-20160311/syntax/table.php	2016-11-22 23:08:01.967907897 +0200
+++ dokuwiki-plugin-data-20220727/syntax/table.php	2023-04-25 15:27:50.645943694 +0300
@@ -584,6 +584,13 @@
                 $select[] = 'pages.lastmod';
             } elseif($key == '%title%') {
                 $select[] = "pages.page || '|' || pages.title";
+            } elseif($key == '%rpm%'){
+                if(!isset($tables[$key])){
+                    $tables[$key] = 'T'.(++$cnt);
+                    $from  .= ' LEFT JOIN rpmdb AS '.$tables[$key].' ON '.$tables[$key].'.pid = pages.pid';
+                }
+                $select[] = "group_concat(".$tables[$key].".value,'\n')";
+
             } else {
                 if(!isset($tables[$key])) {
                     $tables[$key] = 'T' . (++$cnt);
@@ -686,6 +693,21 @@
                 GROUP BY W1.pid
                 $order";
 
+        // Re-order LEFT JOIN pages before LEFT JOIN rpm
+        $sql = preg_replace_callback('/
+            (?P<before>.+)
+            (?P<rpmdb>LEFT\sJOIN\srpmdb\sAS\sT\d+\sON\sT\d+.pid\s=\spages.pid\s+)
+            (?P<pages>LEFT\sJOIN\spages\sON\sW\d+\.pid\s*=pages\.pid\s+)
+            (?P<after>.+)
+        /xm', function ($m) {
+            return implode("", [
+                $m['before'],
+                $m['pages'],
+                $m['rpmdb'],
+                $m['after'],
+            ]);
+        }, $sql);
+
         // offset and limit
         if($data['limit']) {
             $sql .= ' LIMIT ' . ($data['limit'] + 1);