Can't create DB Table with Execute from SQLite Query Builder - SQL Syntax Error because of CURRENT_TIMESTAMP paramter-binding?

The CMS Questions
1

Hi,

so I am working on a Calendar Function for a Project. Because the Calendar can get a lot of Dates and additional Infos, I want to store them in SQLite DB.

Step 1: Creating the DB - works fine

$database = new Database([
    'type'     => 'sqlite',
    'database' => $dbPath
]);
$database->fail(true);
$queryBuilder = new Sqlite( $database );

Step 2: Creating the Table

$queryCreateTable = $queryBuilder->createTable('calendar', [
    'id'         => ['type' => 'id'],
    'user_id'    => ['type' => 'text',      'null' => true],
    'created_at' => ['type' => 'timestamp', 'default' => 'CURRENT_TIMESTAMP'],
    'date_start' => ['type' => 'timestamp', 'null' => true],
    'date_end'   => ['type' => 'timestamp', 'null' => true],
    'title'      => ['type' => 'text',      'null' => true],
    'type'       => ['type' => 'text',      'null' => true],
]);

dump( $queryCreateTable );
dump( $database->execute( $queryCreateTable['query'],$queryCreateTable['bindings'] ) );

this creates a correct query:

CREATE TABLE "calendar" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"user_id" TEXT NULL,
"created_at" INTEGER NULL DEFAULT :created_at_default_dhWFLmOc,
"date_start" INTEGER NULL,
"date_end" INTEGER NULL,
"title" TEXT NULL,
"type" TEXT NULL
)

(And also a correct Binding: ‘:created_at_default_dhWFLmOc’ => CURRENT_TIMESTAMP )

This results in a SQL Error:

SQLSTATE[HY000]: General error: 1 near ":created_at_default_dhWFLmOc": syntax error

It works when I execute the Query as string, where I manually replace the paramater :created_at_default_dhWFLmOc with CURRENT_TIMESTAMP.

Question: Shouldn’t the $database->execute( $query, $bindings ) Method replace the :parameter with the value? What am I missing?

I was looking at the database->createTable() Method, which itself also seemingly just calls the execute( $query, $bindings ); Method.

2

Solved.

Not a Kirby Question, not a Kirby quirk. It’s a PHP PDO thing I wasn’t aware of.

Kirby Database Class has a hit Method, which then runs a classic PDO prepare() and bindValue(). And PHPs PDO Prepare does simply not work with Keywords (like CURRENT_TIMESTAMP), only with literal data. Which I didn’t know.

So any ‘CREATE TABLE’-Query build with the Kirby QueryBuilder will not work, if the table-Array uses a SQL-Keyword for a DEFAULT Value.

So the solution is either that :

(1) I write a classic Create Table String and execute it, or…

(2) … replace the bindings beforehand with some code, like this:

// -- No user Input, so I should be fine to simulate the 'binding'
foreach( $queryCreateTable['bindings'] as $param => $value ){
   str_replace( $param, $value, $queryCreateTable['query'] );
}

$database->execute( $queryCreateTable['query'] );
3

The issue is that DEFAULT values can’t be parameter-bound in SQL, bindings work for data values in INSERT/WHERE, not for DDL structural elements like column defaults. SQLite parses DEFAULT :param as literal syntax before binding happens, hence the error. CURRENT_TIMESTAMP needs to be inlined into the DDL, not passed as a binding. This looks like a Kirby query builder limitation where it wrongly parameterizes defaults. Inlining it, as you found, is the correct workaround.