KiCad: The case for Database driven design

[SumantKhalate]

My bad, I have made it public now.

[joost]

@craftyjon

@eeintech

So I understand the ${Capacitance} value placeholder in your test database needs to be updated or is there some automatic update missing on my side?

There is no field mapping for Capacitance in the kicad_dbl file so this reference is not resolved in the Value field. You could add one if you wanted to.

Care to explain this a bit more @craftyjon ? As @eeintech showed changing “column” to “Capacitance” works. But were does that leave the “Value” column in the database which contains that “{Capcitance}” value? Not needed? What did you use it for?

BTW, this database support may be one of the least sexy changes in K7 but for me is one of the best changes! I can now trash my home grown part numbering system and migrate it here. Very much appreciate this feature.

[craftyjon]

I’m not sure I really understand your question – maybe if you can explain what you’re trying to do with the database configuration I can give pointers on how to do it?

[joost]

The example (QA ?) database from the site shows a Value column and a Capacitance column (for the capacitor table). The value column has the string {Capacitance} for all rows. Is this column necessary? What does it do? As @eeintech pointed out, if the Kicad_dbl file is not changed (see his comment above) component values in the schematic show up that “{capacitance}” string as their value. I.e., something is not being resolved. Once the mapping to the Capacitor column is made, it seems to render the Value column useless… Am I getting this right ?

[craftyjon]

It’s a QA test database, not really an example for users (but people have been using it as one). For that column to be resolved, the a capacitance field would need to be mapped also.

Once the mapping to the Capacitor column is made, it seems to render the Value column useless…

One use would be if you wanted to remap the Value field on a single symbol to different database fields in different situations, and also maintain the source of the Value field as a separate field (for BOM export, maybe).

[joost]

@craftyjon

It’s a QA test database, not really an example for users

Yah, I get that - I suspect it serves as a starting point for many (does for me). Anyway thanks for clearing that up.

[paulvdh]

Doodlebugger also had some trouble with setting up a database:

Database Connection

Hi I’m having issues with connecting to a simple database. SQL i’m fine with, and usually ODBC as I use it in day job. However, even reading the manual and comparing “teletypeguy” contribution I get this error message [image] In theory this seems obvious… Below is the dbl file. I have tried the connection string way as below, the "teletypeguy"s way and using dsn, all to not avail. Also tried forward and back slashes. There is only one column and one table at present so nothing conflict…

It would be nice to have one or a few (for different database backends?) examples directly in KiCad. I don’t know where they would fit best, either in the Demo Projects, Templates or even in the Plugin and Content manager,

People fluent with databases and their setup would not need this, but I recon there are quite some KiCad users for who this is the first time ever they want to set up a database, and a working example is a great starting point and a boost in motivation to dive deeper.

[craftyjon]

Use of the database libraries feature requires some amount of knowledge of databases, there is no getting around that. We could expand the documentation but it’s really not intended that KiCad be someone’s introduction to “what is a SQL database”.

[eeintech]

Let’s say I have a main table for my parts but I would like to gather the symbol fields from different database tables, would this be a feasible implementation:

     "libraries": [
        {
            "name": "Resistors",
            "table": "Resistors",
            "key": "Part ID",
            "symbols": "Symbols",
            "footprints": "Footprints",
            "fields": [
                {
                    "table": "ManufacturerParts",
                    "key": "Part ID",
                    "column": "ManufacturerPartNumber",
                    "name": "MPN",
                },
                {
                    "table": "PartParameters",
                    "key": "Part ID",
                    "column": "Resistance",
                    "name": "Value",
                }
            ],
            "properties": {
                "description": "Description",
            }
        }
    ]

[craftyjon]

That syntax won’t work, but you can accomplish this fairly easily using a SQL view. A view is a “virtual table” that can pull together columns from multiple tables.

[eeintech]

That is indeed a very good option, I was not aware of SQL views. I’ll look into it, thanks!

[John_Pateman]

Does anyone have any hints for getting

1. ‘Datasheet’ links
2. Database description.

working within a KiCad database?

1. I have a functioning KiCad link to a MariaDB database. I am using this to serve a number of ‘Views’ of my PartKeepr database for different component classes. (I can share the necessary sql if anyone is interested) The ‘Views’ pull part parameters into a single table which includes a number of parameters and a ‘Datasheet’ field. This is one line from my database

# part_id	Description	Name	Manufacturer	Capacitance	Voltage Rating	Case	Symbols	Footprints	Datasheet
443	Cap Tant Solid 1uF 10V R CASE 10% (2.05 X 1.3 X 1.2mm) Inward L SMD 2012-12 25 Ohm 125C T/R	TAJR105K010RNJ	AVX	1 μF	10V	0805	Device:C_Polarized	Capacitor_SMD:C_0805_2012Metric	${KICAD_USER_DATASHEET_DIR}/b65b0354-12a9-11e8-a8e2-10ddb1f0c774

As you can see Datasheet is set to ${KICAD_USER_DATASHEET_DIR}/b65b0354-12a9-11e8-a8e2-10ddb1f0c774 within the database.

But when I view this in the ‘Choose Symbol’ dialogue, I find the Datasheet is still set to '~" although all the other parameters are transferred correctly.

This is the relevant section of my .kicad_dbl file.

I have tried both with ‘datasheet’ explicitly defined (as here) and omitted. I have also tried including the datasheet as a field definition, again with no luck.

 ...  {
        "name": "Capacitors_Tantalum",
        "description": "Sort these bad boys out",
        "table": "capacitors_tantalum",
        "key": "part_id",
        "symbols": "Symbols",
        "datasheet": "datasheet",
        "footprints": "Footprints",
        "fields": [
            {
                "column": "Description",
                "name": "Description",
                "visible_on_add": false,
                "visible_in_chooser": false,
                "show_name": false
            },
            {
                "column": "Name",
                "name": "MPN",
                "visible_on_add": true,
                "visible_in_chooser": true
            },
            {
                "column": "Manufacturer",
                "name": "Manufacturer",
                "visible_on_add": false,
                "visible_in_chooser": true
            },
            {
                "column": "Capacitance",
                "name": "Value",
                "visible_on_add": true,
                "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": true
            },
            {
                "column": "Tolerence",
                "name": "Tolerence",
                "visible_on_add": true,
                "visible_in_chooser": true
            },
            {
                "column": "Voltage Rating",
                "name": "Voltage",
                "visible_on_add": false,
                "visible_in_chooser": true
            },
            {
                "column": "Case",
                "name": "Case",
                "visible_on_add": false,
                "visible_in_chooser": true
            }
        ]
    }, ...

${KICAD_USER_DATASHEET_DIR}/b65b0354-12a9-11e8-a8e2-10ddb1f0c774.pdf resolves to a valid path - if I paste this into the symbol properties directly or into a web browser, it works as expected.

I have tried defining the datasheet both directly (file://blah.blah.blah.pdf) and via path substitution (as above) but to no avail. Anyone got this to work with a clickable link to the datasheet?

2. The ‘non database’ standard libraries have a description. eg Analog_ADC - ‘Analogue to digital converters’. Is it possible to set a library description for a database library?

[Claudio.Lorini]

the link in the symbol chooser i think is currently not working, see [feature request] eeschema - enable user variable substitution on the 'symbol chooser' Datasheet field (#13737) · Issues · KiCad / KiCad Source Code / kicad · GitLab

but when the component is placed in the sch, the datasheet should be available trough ‘D’ shortcut or
with the ‘esplorer’ icon in the symbol properties-> datasheet.

my kicad_dbl is like that:

    "libraries": [
        {
            "name": "R",
            "table": "kicad_R",
            "key": "Code_ID",
            "symbols": "KiCAD_Symbol",
            "footprints": "KiCAD_Footprint",
            "fields": [
                {
                    "column": "Code_ID",
                    "name": "IITCode",
                    "visible_on_add": true,
                    "visible_in_chooser": true
                },
                {
                    "column": "Resistance",
                    "name": "Value",
                    "visible_on_add": true,
                    "visible_in_chooser": true
                },
                {
                    "column": "Documentation",
                    "name": "Datasheet",
                    "visible_on_add": false,
                    "visible_in_chooser": false
                },
                {
                    "column": "MPN",
                    "name": "MPN",
                    "visible_on_add": false,
                    "visible_in_chooser": false
                },
                {
                    "column": "Code_Description",
                    "name": "ki_description",
                    "visible_on_add": false,
                    "visible_in_chooser": true
                }
            ]
        },

datasheets are linked via the ‘Datasheet’ field.

[John_Pateman]

Sadly, all I get in the symbol Properties or use the ‘D’ shortcuts is ‘Datasheet undefined’. As I say, the field is empty - just the default ~ not even a non-functional link.

[John_Pateman]

PEBCAK Error! I think I had failed to regenerate the correct table view. Now working - but with the #13737 error mentioned.

[Claudio.Lorini]

i can’t see a

                    "column": "Documentation",
                    "name": "Datasheet",

in your fields in the dbl file…

[John_Pateman]

I had several iterations of my kicad_dbl and of my SQL query to generate the ‘View’ table and I had tried a section that included a data sheet field name but it didn’t seem to work. On regenerating my table views, its now working. Hopefully when I have stable SQL query, this should work.

[AB007]

It wold be very nice if someone can implement a pack that can be install an a NAS drive; this will centralize the part usage/ sharing across multiple users.
There is support (in terms of lplugins) for MariaDB, Apache, Docker and PHP. Some nice web interface (similar with the one developed by @qu1ck for HTML BOM) can be develop for remote database management.

What do you think?

Best Regards,
Andrei

[qu1ck]

You should at least mention what NAS are you talking about. They are all proprietary with custom formats for “packs”.

[2norderEDO]

Hello fellow KiCad users,

Yesterday I was quite frustrated when trying to connect KiCad 7 to my MariaDB component database in my PopOs 22.04 (Ubuntu) system.

Part of the frustration was that I thought I could use the Microsoft ODBC SQL driver to connect to the database. The second most infuriating thing was that currently the MariaDB ODCB connector driver seems to have a nasty bug in their latest versions.

I finally managed to get it up and running with a simple example and I documented the process. I was able to replicate it in an Ubuntu 22.04 docker container.

You can find my tutorial in .pdf format here. Since I take my notes with Joplin in Markdown, I will also paste it below for better indexing.
KiCad7_Database_Library_With_MariaDB_ODBC.pdf (189.5 KB)

Any additions or corrections are welcomed.

Changelog:

• 11.03.2023-0: First post
• 11.03.2023-1: Removed an incorrect statement in point 7

MariaDB ODBC Driver installation and connection in Ubuntu 22.04:

This is an tutorial/explanation for those who are interested in using KiCad 7 and MariaDB running on a server in the local network. To prevent others from wasting the same amount of time that I did, I will proceed to explain what I tried and what I learned. If you are not interested in the learning part, you can jump to section 2.1.

Requirements: You should have already a MariaDB database with some test components running in you local network. I suggest doing something like you can see in [3].

1. First attemp with Microsoft ODBC 17/18 Driver:

My first attempt was to follow the video-tutorial [3] and install Microsoft ODBC driver 18 by following the instructions in the official Microsoft page. Everything seemed to work fine, but it was not connecting to my database. I enabled the TRACE outputs of the ODBC drives by adding the header below to the /etc/odbcinst.ini. This file lists the ODBC drivers installed in your system.

[ODBC]
Trace=Yes #// Print connection information
TraceFile=/dev/stdout #// In this case, print to the terminal instead of a file

Specifically, I got the following error: [Microsoft][ODBC Driver 18 for SQL Server]Protocol error in TDS stream. The trace output helped me verify the connection was reaching to my Mariadb IP and port, but still there was some issue I could not debug.

After some investigation, I found a StackOverflow post in which someone was asking why “ODBC Driver 18 for SQL Server” is not connecting to MySQL. Somebody replied something along the lines of: ‘Of course is not going to work, you are trying to connect a SQL driver to MySQL!’. It seems it is not the appropiate driver! :rollingeyes:

2. Attempt with MariaDB ODBC Driver

As of 11.03.2023, the latest version of the Mariadb ODBC driver (3.1.17) for Ubuntu 22.04 seems to have an incompatibility:

1. This is known as the ODBC-278
2. Error from ODBC 0 01000 \[unixODBC\]\[Driver Manager\]Can't open lib '/usr/lib/libmaodbc.so' : file not found"
3. By using ldd command in /usr/lib/libmaodbc.so, it can be seen that libssl.co.1.0.0 and libcrypto.1.0.0 can’t be found

This problem does not seem to show up when using version 3.1.15, installed with the procedure I explain below. If you find any problem, try to modify the first command so that the version 3.1.15 of the odbc-mariadb driver is installed.

2.1 Procedure:

1. Install Mariadb ODBC Driver sudo apt-get install -s odbc-mariadb.

2. install unixODBC driver manager apt install unixodbc

3. Check with nano /etc/odbcinst.ini that the Mariadb driver is correctly installed:
   

   

4. Go to the home directory and create the “.odbc.ini” file: sudo nano ~/.odbc.ini
   

   

   [KiCad] # This assigns a local DNS to your IP and port, put whatever you like
   Description=MariaDB server #
   Driver=MariaDB Unicode
   SERVER=192.168.1.1 ## example ip
   USER=root ## example user, do not use root!
   PASSWORD=password
   DATABASE=Library # Not needed to connect to MariaDB, but KiCad needs it to find the tables!
   PORT=3306
   

5. Check correct connection to the database:
   

   
   • For debuging, the verbose parameter ‘-v’ can be useful
   • If the password and user fields are not specified, they can be added to the isql command: isql KiCad root password

6. Check all tables are visible by the user with SHOW TABLES;:
   

   

7. From here you should be able to write the .kicad_dbl file from the KiCad 7 documentation and add the required information for connecting to the database.

4. Useful commands:

• isql -v KiCad root password To connect to MariaDB using declared KiCad DNS in “.odbc.ini” file
• sudo nano ~/.odbc.ini To edit DNS configuration of ODBC driver
• sudo nano /etc/odbcinst.ini To check which ODBC drivers are installed and the driver string

4. Useful links:

• [1] About MariaDB Connector/ODBC - MariaDB Knowledge Base
• [2] Creating a Data Source with MariaDB Connector/ODBC - MariaDB Knowledge Base
• [3] KiCad 7 Parts Database Setup - YouTube