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.
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?
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 ?
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).
Yah, I get that - I suspect it serves as a starting point for many (does for me). Anyway thanks for clearing that up.
Doodlebugger also had some trouble with setting up a database:
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.
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”.
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",
}
}
]
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.
That is indeed a very good option, I was not aware of SQL views. I’ll look into it, thanks!
Does anyone have any hints for getting
working within a KiCad 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?
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.
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.
PEBCAK Error! I think I had failed to regenerate the correct table view. Now working - but with the #13737 error mentioned.
i can’t see a
"column": "Documentation",
"name": "Datasheet",
in your fields in the dbl file…
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.
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
You should at least mention what NAS are you talking about. They are all proprietary with custom formats for “packs”.
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.
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].
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:
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:
Error from ODBC 0 01000 \[unixODBC\]\[Driver Manager\]Can't open lib '/usr/lib/libmaodbc.so' : file not 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.
Install Mariadb ODBC Driver sudo apt-get install -s odbc-mariadb.
install unixODBC driver manager apt install unixodbc
Check with nano /etc/odbcinst.ini that the Mariadb driver is correctly installed:
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
Check correct connection to the database:
isql KiCad root password
Check all tables are visible by the user with SHOW TABLES;:
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.
isql -v KiCad root password To connect to MariaDB using declared KiCad DNS in “.odbc.ini” filesudo nano ~/.odbc.ini To edit DNS configuration of ODBC driversudo nano /etc/odbcinst.ini To check which ODBC drivers are installed and the driver stringDo you mean KiCad crashed for you? I tried this and couldn’t reproduce it – if you have nothing in the database it should just show up as an empty library.