A database case study

[teletypeguy]

I got very excited finding that v7 will have database functionality, and took it as an opportunity to drag my small shop into a more automated workflow. This post documents an adventure creating my first database, which is not likely the best way to do it, but fwiw here is my little case study. A great big thanks to @craftyjon (and any other devs involved) for adding this major feature.

Executive summary:

• pull part info together in a big-o spreadsheet
• break spreadsheet into csv tables
• create sqlite database from csv files
• map db fields for kicad
• grin ear-to-ear

The TLDR version follows:

My kicad library is comprised of five folders:

…/kicad/gil-lib/symbols/gil-lib.kicad_sym
…/kicad/gil-lib/gil-fp/… (xxx.kicad_mod files)
…/kicad/gil-lib/3dsymbols/… (xxx.step files)
…/kicad/gil-lib/database/… (gil-db.sqlite and gil-db-xxx.kicad_dbl files)
…/kicad/gil-lib/datasheets/…

I have been building the symbol/fp/3dmodel parts over the last year with v6, and now adding database (and also datasheets) with v7.

I started with my years-old master parts spreadsheet, updating/adding/deleting parts to get it
up to date, and also pulling all the current datasheets. Yeah, that took a while.

I rearraged/added to the spreadsheet to get all the columns (fields) I wanted:
PartNumID, Substitute, Status, BuildLevel, Description, Value, ExtValue, Package, Height,
Symbol, Footprint, TapeCorrection, Datasheet, Manufacturer, ManufacturerPN,
Vendor, VendorPN, VendorQty, VendorEach, VendorAvail,
AltVendor, AltVendorPN, AltVendorQty, AltVendorEach, AltVendorAvail,
JlcpcbPN, JlcpcbMfgPN, JlcpcbQty, JlcpcbEach, LabStock, Note, Temp

PartNumID is my company part number and will be the database unique key field.
I use 003-xxxxx for C=caps (third letter of alphabet), 004-xxxxx for D=diodes,
018-xxxxx for R=resistors, 026-xxxxx for Z=modules. It’s a simple pattern that I like, but you can use any unique number system that works for you, and the key can be integer or text afaik.

Status is for filtering, such as ‘N’ to not-display (eg: an old part), ‘1’ for current favorite parts, simply null for more parts… So a database view for fav parts displays only the parts flagged with 1, a view for more parts can be everything but N, a view for all parts ignores Status.

BuildLevel can be set to something like pcb2nd to flag an after-reflow operation like manually adding a lightpipe or plugging in a socketed module. I like the idea someone suggested about adding a little box symbol so it is treated like a part and shows in the bom.

Displayed on the schematic will ONLY be Value and ExtValue (optional extra value info).
I will jam the Description field full of detail for the selection dialog.

Most of these fields will get pulled into kicad (via .kicad_dbl file). Besides using the db to select parts, I want to generate a custom Assembly-BOM, a Cost/Availability-BOM, and maybe a custom placement file that can optionally adjust rotation with TapeCorrection, and perhaps add Height. To view a datasheet I open the symbol properties and click the little icon. Easy-peasy.

After much farting around with the spreadsheet, I saved separate csv files for each table I wanted (Capacitors…) leaving the first line in the csv with the field names for db import. I combine D (diode) and DS (led) parts into one table, J (jack) and P (plug/socket) into another, and the occasional T (transformer) will get tucked into L (inductor). The Z table is for oddball modules (dc-dc-conv, gps, bluetooth…) and any misc parts (B battery…) that are too few in number to need a separate table. These ten tables are all I need – frankly, one big table would also be workable, but I am breaking it up since the table-as-sublibrary mechanism is quite nice.

I then used the command line “sqlite3” tool to very quickly build the database from the csv files:

Boom, that part was easy. I then opened the gil-db.sqlite database file in SQLiteStudio – all
tables and data are there! I will never use a spreadsheet for these parts again, and changes or additions will be using SQLiteStudio (or similar tool).

All fields are TEXT, and I see no reason to change any types/affinities. For each table, I set PartNumID to primary key, unique, and not-null (a text key, in my case) – so that is a quick table-edit and commit for each table, which is all it needed. I am not even certain that the primary key “needed” to be defined in the db, but ocd-me had to do it.

You can add additional fields (columns) specific to each table (dielectric for caps, etc), but I have no need to use them to search as that detail is in my Description, so I may not bother. I also have a Note field where I can stick the odd comment about the part.

Next I created the gil-db-all.kicad_dbl file to perform the kicad-to-database mapping. I found this much easier by temporarily renaming it to gil-db-all.kicad_dbl.json and opening it in vscode to edit with the color highlighting and syntax checking I can no longer live without. After editing, restore the .kicad_dbl name.

I recalled that Jon mentioned using database “views” – so I had to go figure out what a view is, and how to do it, and it turns out it is frickin’ awesome – this is why I defined a Status field for filtering. In SQLiteStudio I created two views for each of the ten tables, eg:
View name: FavCapacitors
SELECT * FROM Capacitors WHERE Status = 1
View name: MainCapacitors
SELECT * FROM Capacitors WHERE Status != ‘N’

And then I created gil-db-main.kicad_dbl and gil-db-fav.kicad_dbl from the original gil-db-all.kicad_dbl file, changing the table names to the new views.

In kicad 7.0.0-rc1, I used Preferences/Manage-Symbol-Libraries to add these three .kicad_dbl files (in addition to the original gil-lib.kicad_sym file), and just make one of them visible so the symbol selection dialog is not cluttered. Just a couple of clicks to change the visibility and hence the db view as I work. Nice.

So now I need to figure out custom bom generation – any links on how to do that in v7?

[craftyjon]

@teletypeguy

I found this much easier by temporarily renaming it to gil-db-all.kicad_dbl.json and opening it in vscode to edit with the color highlighting and syntax checking I can no longer live without. After editing, restore the .kicad_dbl name.

By the way, there’s an easier way: Open the kicad_dbl file in VSCode, then click the status bar in the lower right where it says “plain text”, then choose the option ‘Configure File Association for ‘.kicad_dbl’…’ then pick JSON. Now VSCode remembers that these should be highlighted as JSON.

[craftyjon]

@teletypeguy

So now I need to figure out custom bom generation – any links on how to do that in v7?

Check the python BOM exporter bom_csv_grouped_extra which got added recently, you might be able to use that as a starting point and select what fields you want to export.

[teletypeguy]

I finally have my database all ready and my libraries tweaked and ready for doing real work with v7, and it is all working very nicely. I am ready to create a new board with a fully-database-driven library.

However, converting a v6 board to use the database is a non-trivial bit of work. I have no problem editing sch/pcb files in a text editor to do find/replace and repair things reasonably quickly when needed (like when I changed the name of my libraries), but that does not really help for changing original lib symbols to database symbols.

I am only interested in converting a couple of v6 boards, but it will require selecting a symbol, change-symbol, and then select the proper db symbol. It might not be too bad if I could see the Description field in the change-symbol dialog, but all I get is the part number and it is a slow slog to find the correct part:

As opposed to how the description shows in the normal symbol chooser:

Is there any way to display the description in the change-symbol dialog, or should I just abandon any idea of converting a v6 project to v7-database?
thx

[craftyjon]

@teletypeguy

Is there any way to display the description in the change-symbol dialog

It should be displayed in the bottom status area when you have a symbol selected

[teletypeguy]

Hmm. I need to manually update every symbol on every page. I think I will just store them in a “from-v6” folder, open/save to update them to v7, but just leave the original library symbols alone. Even parts with the same value and fp need to be updated individually. That’s alright though – price of progress. For folks not using a database the v6 to v7 transition will be transparent, so this is just a special use case.

[teletypeguy]

A follow-up: I have been using v7 with the database since January, and I could not be happier. I have done a few smallish projects to test it out and have done two non-trivial production projects as well. Everything has been rock-solid. My database as outlined above has been under linux.

Yesterday I decided to try setting things up under windows 10, to see what was different. I am not a big fan of winblows but had a spare laptop lying around so thought I would give it a try. So as an FYI (and so I can remember how I did it):

I loaded kicad 7.0.2 on the win machine, and just manually tweaked preferences to match my linux setup (is there a way to export/import prefs?). I copied over my kicad stuff:
\devo\kicad\projects-v7 (my projects)
\devo\kicad\gil-templates (primarily for my .kicad_wks title-block files)
\devo\kicad\gil-lib\symbols (about 500 symbols in one big .kicad_sym file)
\devo\kicad\gil-lib\footprints (about 700 .kicad_mod files)
\devo\kicad\gil-lib\3dmodels ( .step files)
\devo\kicad\gil-lib\datasheets (a bunch o pdfs)
\devo\kicad\gil-lib\database (the gil-db.sqlite database file, and a few .kicad_dbl files)

A couple of notes: Though the recommendation is to have multiple smaller symbol files, I am happy with all the symbols in one big file and have found no loading or performance detriment. I have edited all of my .kicad_mod footprint files to point to the corresponding .step files (there is no database connection for the 3d models). The sqlite database points to the symbol/footprint/datasheet for each part, and tables and table views make selecting the parts easy.

But windows needed a couple of changes:

I had to load the SQLite ODBC Driver (sqliteodbc_w64.exe) from
http://www.ch-werner.de/sqliteodbc/

Then using ODBC Data Source Administrator I added a User-Data-Source I called KiCad-gildb, using the ODBC Driver:

Edit: …and defined the database location after clicking the Configure button:

The database did not need any changes. However, the .kicad_dbl files under linux looked like this:
{
“meta”: {
“version”: 0,
“filename”: “gil-db-main.kicad_dbl”
},
“name”: “Gil Parts Database Main View”,
“description”: “Gil Parts Database Main View Desc”,
“source”: {
“type”: “odbc”,
“dsn”: “”,
“username”: “”,
“password”: “”,
“timeout_seconds”: 2,
“connection_string”: “;Database=/home/gil/aaa-devo/kicad/gil-lib/database/gil-db.sqlite;Driver=SQLite3”
},
(etc)

but for windows the only change was that the “connection_string” field had to change to “DSN=KiCad_gildb;”
{
“meta”: {
“version”: 0,
“filename”: “gil-db-main.kicad_dbl”
},
“name”: “Gil Parts Database Main View”,
“description”: “Gil Parts Database Main View Desc”,
“source”: {
“type”: “odbc”,
“dsn”: “”,
“username”: “”,
“password”: “”,
“timeout_seconds”: 2,
“connection_string”: “DSN=KiCad_gildb;”
},

Note that this dsn is NOT in the dsn field, but in the connection string. I wasted a bunch of time trying to put in c-colon-double-backslash… stuff, but the above change got me running.

Also, checking the .kicad_dbl files with an online json validator will catch silly errors that are easy to make.

Sadly win10 does not support dark mode, but I now have another kicad system.

[Heath_Raftery]

Really appreciate the write-up. Helps a lot with planning my own jump into DB world.

there is no database connection for the 3d models

Is that a choice or a necessity? Is it practical/useful to store associations between footprints and models in the database? I sought of envisaged the move to a DB as the opportunity to freely create 1:many potential relationships between symbol, footprint and model, so you could “pick” the association you want at schematic design time.

[Claudio.Lorini]

@Heath_Raftery

Is that a choice or a necessity?

I did the same, out of a choice. In my DB footprints and 3D has a 1:1 relation in almost all the items.
In the beginning i configured the DB with a KiCad3Dmodel column but during the migration i realized that things could be simpler associating the 3D directly inside the footprints (but i’m ready to switch to DB association if the need arises).

@teletypeguy

The database did not need any changes. However, the .kicad_dbll files under linux looked like this:

in my case we use exactly the same .dbl for Linux and windows (MySQL), no differentiation needed.

[teletypeguy]

Yes indeed Heath, it does seem like the db should also point to the 3d model and perhaps it will someday.

Note that if the db can point to the 3dmodel then the model link embedded in each footprint file becomes unused. Which is actually happening with the footprint today – since the database is now used to link symbol and footprint, it has rendered moot the footprint linkage in each symbol. Those footprint properties are needed for non-database use, but someone will be asking why they are changing the default footprint in the symbol editor and always being overridden by the database.

But the database feature is brand-spanking new and a big leap forward, and I’m sure it will get fine-tuned as the version number creeps up.

At schematic design time you don’t pick the associations between symbol and footprint – that is hard-coded in your database by assigning unique part numbers.

Here editing the database in sqlitestudio: I define part number 021-022105 to use a TLV9062 opamp in a big-o soic, 021-022106 to use the vssop package, and 021-022107 for the nice little sot23-8:

Here editing a kicad schematic: in the symbol chooser I just look at the Description field, where I jam all of my quick-selection details:

[teletypeguy]

Yeah Claudio, I don’t know the differences between different databases and their connection needs in the .kicad_dbl files. And in the interest of full disclosure, my database/sql knowledge is just self-taught of late

It is a good point to mention again that this little case study of mine is using an sqlite database, which was appealing to me as it is a nice single-file local database with no client/server overhead.