Performance when storing pages with SQLite

[rantonse]

Hi! I am trying to use an SQLite database to store a particular a particular type of pages, in this case attendees for an event. (We are using to SQLite in order to integrate smoothly with iPad check-off app.)

I also posted this in the Kirby Forum, because I first thought it was a Kirby issue, but right afterwards I realized the problem mght be more this plugin: https://forum.getkirby.com/t/performance-when-storing-pages-with-sqlite-using-the-pagetable-plugin/28158/1

I am using the approach outlined in https://getkirby.com/docs/guide/virtual-pages/content-from-database, and for the most part it works great!

However, I now have a particular table of attendees with ~5000 rows, and the request, in the panel to the attendees page, yields a 500 Internal Server Error.

• This only happens when using this plugin.
• If I use type: pages and layout: table in the blueprint, the problem disappears.
• However, with this plugin, I can sort the table by clicking on the column headers, which is something I really want to be able to do.

I get the following response message in the console:

file_put_contents([...]/uuid/page/0M/7bE5U957mpuQ2M.cache):
Failed to open stream: Too many open files

When I disabled UUID’s, I got a 502 Bad Gateway Error instead.

My models/attendees.php goes as follows, slightly abbreviated:

class AttendeesPage extends Kirby\Cms\Page
{

  public function children()
  {

    $attendees = [];

    $allAttendees = getAttendeeDatabase()->table('attendees')->all();

    foreach ($allAttendees as $attendee) {
      $attendees[] = [
        'slug'     => $attendee->slug(),
        'template' => 'attendee',
        'model'    => 'attendee',
        'content'  => [
          'name'  => $attendee->name(),
          'slug'  => $attendee->slug(),
          // Some more fields here.
      ];
    }

    return Pages::factory($attendees, $this);

The getAttendeeDatabase function is defined in a plugin and goes roughly like this:

public function getAttendeeDatabase()
{

  $database = new Database(
    [
      'type'     => 'sqlite',
      'database' => $this->root() . '/attendees.sqlite'
    ]
  );

  // Code here for creating the table if it does not exist

  return $database;
}

[sylvainjule]

Apologies for the year-late reply. As mentioned in #90, I will archive this repo in favor of the layout: table option which handles this properly, dealing with a large number of rows has always been a pity with pagetable. I understand that the ability to sort per-column is a great one, but might be best added as a fork of the current Pages section rather than a whole dedicated section like this plugin.