KiCad: The case for Database driven design

zand · December 14, 2022, 12:47pm

Sure, thank you for your help!

qa_dblib.kicad_dbl

{
    "meta": {
        "version": 0,
        "filename": "qa_dblib.kicad_dbl"
    },
    "name": "QA Database",
    "description": "A database for testing purposes",
    "source": {
        "type": "odbc",
        "dsn": "KiCad_DB"
    },
    "libraries": [
        {
            "name": "Resistors",
            "table": "Resistors",
            "key": "Part ID",
            "symbols": "Symbols",
            "footprints": "Footprints",
            "fields": [
                {
                    "column": "Manufacturer",
                    "name": "Manufacturer",
                    "visible_on_add": false,
                    "visible_in_chooser": false,
                    "show_name": true
                },
                {
                    "column": "MPN",
                    "name": "MPN",
                    "visible_on_add": false,
                    "visible_in_chooser": true
                    ,
                    "show_name": true
                },
                {
                    "column": "Value",
                    "name": "Value",
                    "visible_on_add": true,
                    "visible_in_chooser": true
                },
                {
                    "column": "Resistance",
                    "name": "Resistance",
                    "visible_on_add": false,
                    "visible_in_chooser": false
                },
                {
                    "column": "Resistance Tolerance",
                    "name": "Tolerance",
                    "visible_on_add": false,
                    "visible_in_chooser": false
                },
                {
                    "column": "Power Rating",
                    "name": "Power Rating",
                    "visible_on_add": true,
                    "visible_in_chooser": false,
                    "show_name": true
                }
            ],
            "properties": {
                "description": "Description"
            }
        },
        {
            "name": "Capacitors",
            "table": "Capacitors",
            "key": "Part ID",
            "symbols": "Symbols",
            "footprints": "Footprints",
            "fields": [
                {
                    "column": "Manufacturer",
                    "name": "Manufacturer",
                    "visible_on_add": false,
                    "visible_in_chooser": false
                },
                {
                    "column": "MPN",
                    "name": "MPN",
                    "visible_on_add": false,
                    "visible_in_chooser": true
                },
                {
                    "column": "Value",
                    "name": "Value",
                    "visible_on_add": true,
                    "visible_in_chooser": true
                },
                {
                    "column": "Dielectric Type",
                    "name": "Dielectric",
                    "visible_on_add": true,
                    "visible_in_chooser": false
                },
                {
                    "column": "Voltage Rating",
                    "name": "Voltage Rating",
                    "visible_on_add": true,
                    "visible_in_chooser": true
                }
            ],
            "properties": {
                "description": "Description"
            }
        }
    ]
}

It is basically the one used here (qa/data/dblib · master · KiCad / KiCad Source Code / kicad · GitLab) adapted with the correct DSN for my setup.

Claudio.Lorini · December 14, 2022, 1:14pm

have you already tried connecting using a connection string?

something like this:

    "name": "KiCAD_DB Database",
    "description": "KiCAD Database placement testing",
    "source": {
        "connection_string":"dsn=KiCad_DB" 
    },

but since you can see the (empty) categories, i think that it is not a problem of connection to the DB…
your kicad_dbl file seems ok to me.

zand · December 14, 2022, 1:22pm

Just tried the connetion string, leads to the same outcome.

I also think it is something different, maybe a permission thing with the database. As KiCad can access the .kicad_dbl file and its configuration of tables but the data inside the .sqlite file is not shown.

Claudio.Lorini · December 14, 2022, 1:40pm

another thing: have you tried installing SQLite under windows?
(now you have installed it on Linux, and you run KiCad under windows, isn’t it?)

zand · December 14, 2022, 1:57pm

I have moved everything to Windows now and it is working. It seems something with the WSL 2 / Ubuntu is not working as expected there.

Downloaded SQLite3 (DLL and EXE) and added those to the path variable. (This step is not needed as everything is set up when you install the ODBC driver)

This is the result:

Thank you so much for the help

Instructions TLDR;

Install ODBC driver on Windows
Download the reference/test database and configuration files mentioned above
Open “ODBC Data Sources” and create a new entry for user-dsn and point to the database downloaded in 2.
Name the user-dsn file e.g. “KiCad_DB” and write this name into the .kicad_dbl file (“dsn”: “KiCad_DB”)
Create an entry in KiCad Symbol Editor pointing at the .kicad_dbl file
Profit

craftyjon · December 14, 2022, 2:02pm

We found an issue with the official macOS builds causing this, should have it fixed soon.

John_Pateman · December 14, 2022, 2:43pm

Thanks for confirming this. I was going to write a bug report but figured there could be a bit of bumpiness in the initial arm64 builds so have hung fire.

marekr · December 14, 2022, 2:51pm

You shouldn’t need this part for normal setup. The sqlite odbc driver for windows has the sqlite dll statically linked.
However if you want the reference sqlite gui, then yea you need the exe but you shouldn’t need to add to PATH.

craftyjon · December 14, 2022, 2:56pm

The categories will appear even if connection to the database fails, since those categories are defined in the kicad_dbl file

craftyjon · December 14, 2022, 3:14pm

The newest one (kicad-unified-universal-20221214-0033-2f8cc84551.dmg should work)

zand · December 14, 2022, 3:21pm

Just checked, working like you said, thank you, I will adapt the tutorial

cdwilson · December 15, 2022, 5:21am

@John_Pateman FYI, I posted an update to my previous article with simplified instructions that work on both Intel and Apple Silicon Macs: cdwilson.dev

This should work with the kicad-unified-universal-20221214-0033-2f8cc84551.dmg (or newer) images.

Claudio.Lorini · December 20, 2022, 10:09am

Im using the DB libraries with a remote MySQL DB under VPN connection and i noticed that
if you leave KiCad open unattended for a certain amount of time (let’s say 10…15 min?),
when you restart working you get an error message:

Screenshot at 2022-12-20 11-06-28

It is Just a minor inconvenience, since if sufficient to close and reopen to make it work again,
but maybe there is a way to avoid that?
I suppose it is related to some DB timeout settings, since the same happens using OrCAD
on the same DB connection. I’ve tried to change the DB timeouts without any noticeble effect.
Probably is not related to the VPN connection, since it happens also when i’m on the local network.

I have not enough data to fill a bug report, maybe someone can suggest some things to test to try
to acquire more data?

retiredfeline · December 20, 2022, 10:22am

Probably to do with MySQL connection timeout. Maybe do something like this:

I also don’t know if it’s better instead to get a client to detect a broken connection and transparently reconnect.

PS: Do you want me to split this off into a separate thread? I’m thinking that this thread is very overloaded.

Claudio.Lorini · December 20, 2022, 10:35am

my thought also. Let’s see what craftyjon thinks about it.

not sure about that either i’ve seen that here is preferred to have small size topics, but doing like that the useful infos are scattered all over the place…

retiredfeline · December 20, 2022, 10:39am

Is there a reconnect directive in the ODBC settings?

zand · December 20, 2022, 6:36pm

As described before I got the reference database working.

I have added a new table “Connectors” with a DB editor, but it is not showing up in KiCad.

Also, I added a new capacitor but only the old ones are showing up in KiCad.

Is there anything I have to do to reload the database on the ODBC side?

In KiCad the "Connectors" are shown (as this is done via the .kicad_dbl config)

The data is visible in the DB editor:

Claudio.Lorini · December 20, 2022, 6:50pm

For the new family (connectors) you have also to add a library item to the .kicad_dbl file, you should be able to see the new parts as soon as you place them in the DB, so something unusual is happening…
I think that the DB is ‘queried’ once when you open the schematic and place the first part, then buffered until you close the schematic to have max. performance.

zand · December 20, 2022, 6:53pm

I can confirm that some changes need a close and open of the schematic editor yes.

The .kicad_dbl was modified to list the connectors. So this should not be the problem.

zand · December 20, 2022, 7:10pm

My fault, I am so sorry, chosen the wrong part → wrong file.