Create a view result in malformed database schema

(1.2) By René J. (janihani) on 2023-12-07 23:40:20 edited from 1.1 [source]

Hello,
after creating a view I get a database disk image is malformed
malformed database schema (Brenner Starts and Stops) - near "(": syntax error"

        Create View 'Brenner Starts and Stops' As

        Select
        substr(Datum,1,10) As Tag, 
        Count(Brenner) FILTER (Where Brenner = 'EIN') As 'Brenner Starts' ,
        Count(Brenner) FILTER (Where Brenner = 'AUS') As 'Brenner Stops' 

        from Brenner

        Group By substr(Datum,1,10)

        Order By Datum Desc

The view itselfs is working fine.

The name is not the problem, because the problem occurs also when I use the name BrennerStartsAndStops.

I use SQLite version 3.44.2 2023-11-24 11:41:44 (UTF-16 console I/O) and try to insert Data with the following powershell command.

        $SQL = "Insert into 'Temperaturen' (Datum, Zeit, VLHK, RLHK, VL, RL, VLSolar, Speicher, Brenner, SP, UP,WW, Zirku, ZP) 
                Values ('$Datum','$Zeit','$VLHK','$RLHK','$VL','$RL','$VLSolar','$Speicher','$Brenner','$SP','$Up','$WW','$Zirku','$ZP');"

        invoke-SqliteQuery -DataSource $DataSource -Query $SQL -QueryTimeout 3

When I delete the view invoke-SqliteQuery runs without an error.

(2) By Simon Slavin (slavin) on 2023-12-08 12:44:21 in reply to 1.2 [link] [source]

Run an integrity check on the database, just in case the database genuinely is malformed and only your VIEW is reading the part which causes the error.

Use single quotes ['] for strings.

Use double quotes ["] for names of things, e.g.names of columns.

You cannot just swap these. They do different things inside SQLite.

Once you have made the above changes, try building up your VIEW slowly. Do you get the problem with just

CREATE VIEW 'Brenner Starts and Stops' AS
        SELECT
        SUBSTR(Datum,1,10) AS Tag;

? If so, you now have a simpler thing to debug. If not, add the next part and see if that introduces the error.

(3) By René J. (janihani) on 2023-12-08 22:28:11 in reply to 2 [link] [source]

Run an integrity check on the database,

The result of an integrity check with the view is: ok

The view itselfs is working fine.

Use double quotes ["] for names of things, e.g.names of columns.

Create View "Brenner Starts And Stops" As

Select substr(Datum,1,10) As Tag, Count(Brenner) FILTER (Where Brenner = 'EIN') As "Brenner Starts" , Count(Brenner) FILTER (Where Brenner = 'AUS') As "Brenner Stops"

from Brenner

Group By substr(Datum,1,10)

Order By Datum Desc

Do you get the problem with just

CREATE VIEW 'Brenner Starts and Stops' AS SELECT SUBSTR(Datum,1,10) AS Tag;

No.

The problem occurs, when I add one or both lines

Count(Brenner) FILTER (Where Brenner = 'EIN') As "Brenner Starts" ,
Count(Brenner) FILTER (Where Brenner = 'AUS') As "Brenner Stops"

(4) By Bo Lindbergh (_blgl_) on 2023-12-08 22:36:30 in reply to 3 [link] [source]

What do the definitions of the Brenner and Temperaturen tables look like?

(5) By René J. (janihani) on 2023-12-08 23:18:31 in reply to 4 [link] [source]

CREATE TABLE "Temperaturen" (
	"id"	INTEGER NOT NULL UNIQUE,
	"Datum"	TEXT,
	"Zeit"	TEXT,
	"VLHK"	TEXT,
	"RLHK"	TEXT,
	"VL"	TEXT,
	"RL"	TEXT,
	"VLSolar"	TEXT,
	"Speicher"	TEXT,
	"WW"	TEXT,
	"Zirku"	TEXT,
	"Brenner"	TEXT,
	"SP"	TEXT,
	"UP"	TEXT,
	"ZP"	TEXT,
	PRIMARY KEY("id" AUTOINCREMENT)
);

CREATE TABLE "Brenner" (
	"ID"	INTEGER NOT NULL UNIQUE,
	"Datum"	TEXT,
	"Zeit"	TEXT,
	"Brenner"	TEXT,
	PRIMARY KEY("ID" AUTOINCREMENT)
);

(6) By Phil G (phil_g) on 2023-12-09 00:45:09 in reply to 1.2 [link] [source]

I use SQLite version 3.44.2 2023-11-24 11:41:44 (UTF-16 console I/O)

Are you certain about that? That error looks very similar to what I'd expect from an older version of Sqlite that doesn't understand the FILTER clause.

I'm not familiar with invoke-SqliteQuery, but a quick search suggests that it uses System.Data.SQLite rather than Sqlite's CLI; perhaps therefore not the version you're expecting if you checked the CLI's version.

Please check with something like:

invoke-SqliteQuery -DataSource $DataSource -Query "select sqlite_version();"

(7) By Simon Slavin (slavin) on 2023-12-09 07:32:28 in reply to 3 [link] [source]

I'm trying to isolate the part that causes the problem. Try

CREATE VIEW "test1" AS
    Count(Brenner) FILTER (Where Brenner = 'EIN') AS "Brenner Starts";

If that makes it fail, try removing the AS clause at the end. If that still fails, try removing the FILTER clause.

Note: I changed the name of the VIEW because this no longer is the useful VIEW you're trying to make, it's just a VIEW that tests for the bug. Make sure you no longer create the original VIEW you want to use when the problem is solved.

(8) By René J. (janihani) on 2023-12-09 21:06:11 in reply to 6 [link] [source]

Are you certain about that?

I thought, because I installed the latest sqlite3 version.

I'm not familiar with invoke-SqliteQuery, but a quick search suggests that it uses System.Data.SQLite rather than Sqlite's CLI;

You are so right.

invoke-SqliteQuery -DataSource $DataSource -Query "select sqlite_version();"

3.8.8.3

(9) By Simon Slavin (slavin) on 2023-12-10 13:36:01 in reply to 8 [link] [source]

You don't 'install' SQLite. It's not a property of a computer or other device. It's a programmer's library and each programmer includes it in their program. Actually, each programmer includes whatever version they like in their program.

So you can talk to the developers of SQLiteModule for PowerShell (which is what I think you're using). Or you can have PowerShell call the CLI you downloaded from SQLite's website. Or several other solutions.