Database library contains no symbols only when dbl key column is a "generated column"--bug?

Schematic Library Symbols
1

I’m putting together a database library and I realized that the database column specified as "key" in the database library config file (*.kicad_dbl) is used as the name of the symbol in the GUI.

Originally I had a numeric identifier here, but since the value is used in the GUI, I decided I needed a more descriptive value for “key”.

What I came up with was the concatenation of manufacturer and manufacturer part number. So MFR=TI, MPN=1234, gui name is TI-1234.

This is possible in sqlite using generated columns.

So, I made this sqlite database:

gen-concat.db 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE everything (
	symbol TEXT,
	footprint TEXT,
	value TEXT,
	package TEXT,
	mpn TEXT,
	mfr TEXT,
	guiname TEXT GENERATED ALWAYS AS ( mfr || '-' || mpn ) STORED,
	UNIQUE ( mpn , mfr )
);
INSERT INTO everything VALUES('C_Small','C_1210_3225Metric','10 uF','1210','CGA6P1X7R1N106M250AC','TDK');
COMMIT;
generated column working as expected 
$ sqlite3 gen-concat.db 'SELECT guiname FROM everything;'
TDK-CGA6P1X7R1N106M250AC

Then in *.kicad_dbl set "key": "guiname".

I found no symbols in the library, though the library itself (“AAAAA - Everything”) is picked up1:

image
image2455×1745 230 KB

If I simply change "key" from "guiname" to "mpn" (no longer referring to a generated column) in *.kicad_dbl, the symbol appears in the library after closing kicad completely and reopening2:

image
image2455×1745 235 KB

So that’s the problem. It seems KiCad doesn’t recognize a generated column.

I though maybe the problem had to do with the value. Too long, or the dash in it, maybe?

Nope, I made another database where the generated column is exactly MPN, forget the concatenation.

gen-no-concat.db 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE everything (
	symbol TEXT,
	footprint TEXT,
	value TEXT,
	package TEXT,
	mpn TEXT,
	mfr TEXT,
	guiname TEXT GENERATED ALWAYS AS ( mpn ) STORED,
	UNIQUE ( mpn , mfr )
);
INSERT INTO everything VALUES('C_Small','C_1210_3225Metric','10 uF','1210','CGA6P1X7R1N106M250AC','TDK');
COMMIT;
diff 
-        "connection_string": "Driver=Sqlite3;Database=./gen-concat.db;"
+        "connection_string": "Driver=Sqlite3;Database=./gen-no-concat.db;"

-            "key": "mpn",
+            "key": "guiname",
comparing guiname and mpn 
$ sqlite3 gen-no-concat.db 'SELECT guiname, mpn FROM everything;'
CGA6P1X7R1N106M250AC|CGA6P1X7R1N106M250AC

Same issue3:

image
image2455×1745 228 KB

So, I’m wondering, is this a bug?

I’m on KiCad 9.0.0 Flatpak. Here’s a git repo with the full demo files, commits 1 2 3 corresponding to superscripts 1 2 3 in my post:
kicad-gen-col-demo.zip (25.9 KB)

2

Yes it is a bug, but in the sqlite ODBC driver, not in KiCad. See Database library ignores SQLite3 'Generated Value' (#16952) · Issues · KiCad / KiCad Source Code / kicad · GitLab

As this is still not fixed upstream, if you want you can build my fork of the driver where I made a fix: Jon Evans / sqliteodbc · GitLab

Or, you can use a different database such as postgres if you want to use generated columns.

1 Like
3

Thanks, Jon. That answers my question. I’m not nearly familiar enough with Flatpak packaging to take a crack at integrating my own driver with the KiCad version I have installed now, so maybe it’s time to try postgres.

4

I found a (rather painful) workaround: set up triggers on insert, delete, update to table with generated column to sync up a table without generated column. My approach is here:

db-control.org (2.2 KB)

You edit everything and you point kicad at everythingsynced.

1 Like