Another database case study

[Heath_Raftery]

Case studies like this one were really helpful in my own leap into DB world. I’ve been meaning to reciprocate, and here we are two versions later and I’m only getting around to it!

These are some excepts from my design document, with a bit of the thought process and a bit of the implementation. I’ve stripped out a lot of the descriptions of convention because it’s beside the database design.

Motivation

This library evolved from years of KiCad projects with individual libraries and tools that evolved as requirements dictated. Before that, lessons come from years of a centralised Altium library managed by an individual librarian. Most approaches have their strengths, but KiCad’s recent introduction of database libraries opens the door to a system that leverages the best of all worlds.

The advantages of individual libraries are:

• almost zero maintenance,
• zero legacy weight,
• continuous adoption of latest and greatest,
• highly fit for purpose,
• small, fast and easy to use

The advantages of a common library are:

• accumulation of IP,
• less repetitive work,

The goal of this library is to maximise the benefits of both approaches.

Library Design

The essential criteria of this library design is very low barrier to change. A low barrier to change is key for maintaining the benefits of an individual library while gaining the benefits of a common library.

Contents are:

• 3D Models : 3dmodels
  • Nothing too special here - they tend to only be associated with one footprint, so no need for onerous organisation/naming.
• Footprints : HouseLib.pretty
  • Typically a derivation of an existing footprint, so name/metadata is a modification of existing.
• Symbols : HouseLib.kicad_sym
  • Strictly generic. Even symbols for proprietary parts have generic fields.
• Parts : HouseLib.sqlite
  • Strictly atomic parts, with House fields. Even variants are added as a new part - it’s just another row in the table and then the part can be re-used without change.
  • DB library
    • sqlite so no need for a database server, just the ability to run sqlite on the local machine.
    • generated from a single big o’ csv file, so there’s no need for a part hierarchy upfront or deciding what goes where
      • that means not all columns apply to all parts - which is exactly the way a BOM works so nothing too new to learn
    • note this means the csv file is essentially a more workable kicad_sym file - at the end of the day KiCad generates a symbol library from the database, so the database is just an intermediary step. It does mean database tools can be used along the way, but otherwise this is actually a very lightweight progression.

Parts Fields

• Database specific fields:
  • ID : the unique identifier, which acts as the primary key. To ensure it never changes, it is an explicit, visible field, so its value gets used every time the database is re-generated. To ensure it’s always 5 digits, it starts at 10001. Existing IDs must not be changed! New IDs can (and should) be generated automatically by leaving them blank in the csv and running an import. The next numerical value will be assigned. The csv must then be updated with the generated value, which is done automatically by the import script.
    • Note this field is not a HPN, which is designed to be human friendly. After much thought, the advice in this thread, and more explicitly this article, was applied - a HPN scheme is not yet ready or needed. An autogenerated ID is much simpler to design, generate and maintain. A HPN scheme ought to be broader than this database, and preliminary schemes could be generated by using the ID as part of a longer format with category and/or other fields.
  • Note : Any internal notes about the part, like reasons for inclusion, warnings, considerations for use, etc.
  • Symbol : reference to the symbol to use for the part in the normal library:part format. Specifying multiple alternative symbols is a work in progress.
  • Footprint : reference to the footprint to use for the part, in the normal library:footprint format (note this means it’s a combination of the “Footprint Lib” and “Footprint” fields that are exported to the BOMs. Multiple footprints can be specified by delimiting with ; .
• Visual fields, for displaying on the schematic:
  • Value : KiCad uses this field prominently in listings of library components, so needs to be descriptive. The good news is that as a database we can do bulk replace to improve the convention, as the library content grows and provides better ideas.
  • Remark : the single big o’ csv file strategy limits options for specifying which fields other than Value are visible. Having a Remark field resolves this, and simplifies the design at the same time. So the Remark field can always be visible, and be either empty, or contain content that should be visible by default, such as the tolerance for a precision resistor or the reverse voltage for a Zener. It’s just a default, and easy to update in the library, or override in the schematic. So the user can also use it as a “name” field for things like connectors and LEDs, with values like Power , Serial Comms or Fault , if they wish to preserve the Value field.

<SNIP long list of other carefully thought out fields>

Adding to the Library

To add a new part:

1. open HouseLib.csv
2. add a new row (it doesn’t matter where)
3. fill in as many details as can be confidently determined, but leave “ID” blank and set “Phase” to “New”
4. if using a non-native csv editor (eg. a spreadsheet program like Numbers or Excel), export to csv and overwrite the original file
5. run update_db.sh .
6. If that ends successfully, check in both the csv and sqlite files.

[Heath_Raftery]

The secret sauce is then in the update_db.sh script. It performs the following steps:

1. Extracting column definitions from csv files. The definition includes a name, a type and a label.
2. Re-create the SQL table based on those definitions.
3. Populate the table with the data from HouseLib.csv.
4. Tidy up NULLs and apply data types.
5. Add auto-generated columns like exclude_from_bom and exclude_from_board.
6. Produce a diff of old vs new.
7. Confirm with user to go ahead and commit the new.

This was surprisingly complex to get right, but is working simply and reliably now.

[Heath_Raftery]

The final implementation looks like this

The source data is in frequently changed HouseLib.csv and the rarely changed columnDefs.csv. The update_db.sh draws from those two files and produces HouseLib.sqlite.

Then KiCad is setup with HouseLib.pretty, HouseLib.kicad_sym and HouseLib.kicad_dbl as global libraries.

[Heath_Raftery]

I was able to seed the database from a couple of recent projects to produce a decent starter set and ensure the design is good. Since then I’ve incrementally built it up on subsequent projects. It’s working a treat. I’m particularly pleased that I haven’t had to spend time playing with the database design, and instead have been getting value out of it consistently.

[teletypeguy]

That’s an awesome bit of work Heath! The alternate-symbol thing has also been clunky for me – I just define two id numbers with the same part and links to different symbols. This has only affected a couple of parts that I like to draw different ways just to make the schematic cleaner, like dual-diodes, spdt switches, etc. Thanks for posting your db creation details.

[Heath_Raftery]

Thanks. Yes, I use the same workaround, and it’s barely a concern now. Being a csv/db it’s easy to duplicate/sort/search, so maintaining largely similar rows is not as big a deal as I first thought.

One of the fields I snipped from my list is this one:

• Design Maturity (or Phase) : set to “New” when first created. Once used in PCB fabrication and checked, change to either “Validated” if it’s accurate, “Flawed” if it can’t be used without change, or “Provisional” if it can be used but should be improved. Could be other descriptive terms used, although probably best to avoid “Verified” since it’s either “New” or it works - there’s no points for matching the specs.

One of my big concerns moving to a db was that the barrier to adding to the db would be such that the lure of just going back to a “quick” local library would be too much. This is the death knell of all well-intentioned engineering systems.

This field was suggested to me as an aid and I’m stunned how effective it is - it is now easier to add a new part to the db instead of a local library. Everything is just “New” by default until proven otherwise. I put the project codename in the “Note” field, so bulk updating the last batch of “New” parts to “Validated”, “Flawed” or “Provisional” is easy and quite rewarding.