Developing some app in Perl 5.18.2, I try to create a view as part of the database schema. Unfortunately the creation statement fails, and my program outputs:
[0] failed_execute: failed to execute: CREATE VIEW V_KEY_FLAT
(TYPE, INSTANCE, KEY_DATA, CREATOR, CREATED, MODIFIED, LOCKED, DK_DATA,
KEK_DATA) AS
SELECT
KEY_TYPE.NAME,
KEY_ID.INSTANCE,
KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED,
KEY_PROT.DK_DATA, KEY_PROT.KEK_DATA
FROM KEY_TYPE
INNER JOIN KEY_ID ON (KEY_TYPE.ID = KEY_ID.TYPE_ID)
INNER JOIN KEY_DATA ON (KEY_DATA.ID = KEY_ID.ID)
INNER JOIN KEY_PROT ON (KEY_PROT.ID = KEY_ID.ID)
INNER JOIN CREATORS ON (CREATORS.ID = CREATOR_ID);: near "(": syntax error
I suspect that the ( is that of "[( column-name [, column-name]...)]" where the docs say:
The column-name list syntax was added in SQLite versions 3.9.0 (2015-10-14).
I'm using sqlite3-3.44.0, so that should be fine, right? Also I was able to create that view in an empty database manually, so it seems the syntax is accepted.
I'm also using perl-DBI-1.628 and perl-DBD-SQLite-1.40.
Als the schema creation happens in a transaction that fails, running my program ends with an empty database. After creating the view manually, I see another strange error; first the view creation (note that none of the tables exists):
SQLite version 3.44.0 2023-11-01 11:23:50
Enter ".help" for usage hints.
sqlite> CREATE VIEW V_KEY_FLAT
...> (TYPE, INSTANCE, KEY_DATA, CREATOR, CREATED, MODIFIED, LOCKED, DK_DATA,
(x1...> KEK_DATA) AS
...> SELECT
...> KEY_TYPE.NAME,
...> KEY_ID.INSTANCE,
...> KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED,
...> KEY_PROT.DK_DATA, KEY_PROT.KEK_DATA
...> FROM KEY_TYPE
...> INNER JOIN KEY_ID ON (KEY_TYPE.ID = KEY_ID.TYPE_ID)
...> INNER JOIN KEY_DATA ON (KEY_DATA.ID = KEY_ID.ID)
...> INNER JOIN KEY_PROT ON (KEY_PROT.ID = KEY_ID.ID)
...> INNER JOIN CREATORS ON (CREATORS.ID = CREATOR_ID);
sqlite>
The error is see when running my program again (after having created the view manually) is:
[0] failed_prepare: prepare() of "SELECT EXISTS(
SELECT 1 FROM sqlite_master WHERE type='table' and name=?);" failed with "malformed database schema (V_KEY_FLAT) - near "(": syntax error"
Again, the command succeeds when I execute the commands manually (in a database with the view added manually):
SQLite version 3.44.0 2023-11-01 11:23:50
Enter ".help" for usage hints.
sqlite> SELECT 1 FROM sqlite_master WHERE type='table' and name='V_KEY_FLAT';
sqlite> SELECT 1 FROM sqlite_master WHERE type='view' and name='V_KEY_FLAT';
1
sqlite>
I'm not able to find the root cause of the issues seen.
Unfortunately the code involved is much too complex to show a "MWE", but essentially the code to execute the SQL commands is:
# execute SQL statement
sub _do_sql($$)
{
my ($self, $sql) = @_;
my $dbh = $self->dbh();
if (defined(my $result = $dbh->do($sql))) {
return $result;
} else {
$self->add_error(EB_DBI_FAILED_EXEC,
'failed to execute: ' . $sql . ': ' . $dbh->errstr);
return $result;
}
}
(and $dbh is the result of DBI->connect('dbi:SQLite:dbname=' . $filename))
It's really strange (printing $sql and $dbh->errstr in _do_sql):
Auth::KeyStore::SQLite::_do_sql(lib/Auth/KeyStore/SQLite.pm:164):
164: if (defined(my $result = $dbh->do($sql))) {
auto(-1) DB<8> p $sql, $dbh->errstr
CREATE TABLE METADATA (
PNAME VARCHAR(20) PRIMARY KEY NOT NULL,
PVALUE VARCHAR(20)
);
DB<9> n
Auth::KeyStore::SQLite::_do_sql(lib/Auth/KeyStore/SQLite.pm:167):
167: $self->add_error(EB_DBI_FAILED_EXEC,
168: 'failed to execute: ' . $sql . ': ' . $dbh->errstr);
auto(-1) DB<9> p $sql, $dbh->errstr
CREATE TABLE METADATA (
PNAME VARCHAR(20) PRIMARY KEY NOT NULL,
PVALUE VARCHAR(20)
);malformed database schema (V_KEY_FLAT) - near "(": syntax error
So it seems when creating a table, SQLite looks at the views and complains about the view created earlier.
Stepping into the database interface I found that compiled DBD::SQLite::st::_prepare($sth, $sql, @_) causes the error.
I found the answer in https://stackoverflow.com/a/36411540/6607497:
> perl -MDBD::SQLite -le'print $DBD::SQLite::sqlite_version'
3.7.17
So perl isn't using the system's SQLite (3.44.0), but its own version (3.7.17)!
As "column-name" was added for 3.9, the DBD::SQLite does not know it!