Cannot set up a virtual page using an SQLite database

Kirby 3 Solved ✅
1

Hi community,

I am developing in the latest PhpStorm IDE, using Kirby 3.5.1, PHP 7.4, and an SQLite database.

I wish to employ some virtual pages generated out of database content, together with the usual Kirby text based pages. I am not sure how to do this right, so I tried to use the content from database example from the Kirby Guide. I believe I set up everything as in the mentioned Guide article (basic version, not the extended one). On request, I can provide the contents of every component of my setup.

But it doesn’t work. In the panel, I see the comments virtual page as an unlisted page:

grafik

Clicking on that entry yields the following error message:

grafik

The definition of my table comments is as follows:

create table comments
(
    id   int not null
        constraint comments_pk
            primary key,
    text varchar(600),
    user varchar(100),
    slug varchar(185)
);
create unique index comments_id_uindex
    on comments (id);
create unique index comments_slug_uindex
    on comments (slug);

I believe this is exactly what the documentation specified. I suppose there is something else going wrong, but I have no clue what.

Any suggestions?

2

What is in the blueprint of the comments page?

3

Like in the docs:

title: Comments

sections:
  comments:
    headline: Comments
    type: pages
    info: "{{ page.user }}"
    template: comment
4

Is the database readable? Have you tested that your model actually returns something, e.g. in a template?

1 Like
5

The database is readable, at least in my IDE. It should be readable also for the web server:

hww@HWWWin10:/var/www/html/content$ ll
total 36
drwxrwxrwx 1 hww hww  4096 Feb  4 09:08 ./
drwxrwxrwx 1 hww hww  4096 Feb  3 12:27 ../
drwxrwxrwx 1 hww hww  4096 Feb  2 11:44 1_products/
drwxrwxrwx 1 hww hww  4096 Feb  1 08:55 2_services/
drwxrwxrwx 1 hww hww  4096 Feb  1 11:57 3_about-us/
drwxrwxrwx 1 hww hww  4096 Jan 28 14:54 4_contact/
drwxrwxrwx 1 hww hww  4096 Jan 13 17:15 5_legals/
drwxrwxrwx 1 hww hww  4096 Feb  2 11:58 _drafts/
drwxrwxrwx 1 hww hww  4096 Feb  4 08:55 comments/
-rwxrwxrwx 1 hww hww 36864 Feb  4 09:08 dewetec.sqlite*
drwxrwxrwx 1 hww hww  4096 Jan 13 17:15 error/
drwxrwxrwx 1 hww hww  4096 Feb  1 08:54 home/
drwxrwxrwx 1 hww hww  4096 Jan 13 17:15 login/
drwxrwxrwx 1 hww hww  4096 Feb  2 19:39 productgroups/
drwxrwxrwx 1 hww hww  4096 Jan 13 17:15 sidebar/
-rwxrwxrwx 1 hww hww    31 Jan 29 12:19 site.txt*

How can I “test the model”? The corresponding template comments.php contains

<?php
/** @noinspection PhpUndefinedMethodInspection */
declare(strict_types = 1);

use Kirby\Database\Db;

$comments =Db::select("comments");
?>
<ul class="comments">
    <?php foreach ($page->children() as $comment): ?>
        <li>
            <a href="<?= $comment->url() ?>">
                <?= $comment->text() ?> <small>by <?= $comment->user() ?></small>
            </a>
        </li>
    <?php endforeach ?>
</ul>

Here’s what happens in the frontend:

grafik
grafik934×603 44.5 KB

6

Got it. In my config, there has been a wrong path to the database file. It now correctly reads

return [
    'db' => [
        'type'     => 'sqlite',
        'database' => kirby()->root("content"). '/dewetec.sqlite', // full path to file
    ]
];

Thank you for you nudging me into the right direction.

7

Just a little correction. You cannot use kirby() in a config file, the documentation says so. Rather, this line has to read

'database' => "{$_SERVER['DOCUMENT_ROOT']}/content/dewetec.sqlite"

This one helped me.

Thank you @texnixe again for all your awesome work and help.

8

Are you sure?

I remember having complex routes in my config files, which often needed to use kirby() to do what they needed to do - e.g. kirby()->impersonate().

9

Yes, but that is inside a callback, in routes or hooks (or the ready callback that allows you to add standard settings using kirby()/site().