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?
