KiCad: The case for Database driven design

Sure, thank you for your help! :slight_smile:

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.

image

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.

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.

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?)

1 Like

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:
image

Thank you so much for the help :smiley:

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

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

2 Likes

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.

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.

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

2 Likes

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

1 Like

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

@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.

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?

1 Like

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.

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

not sure about that either :slight_smile: 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…

Is there a reconnect directive in the ODBC settings?

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:

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.

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.

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