4 posts were split to a new topic: Assembly variant system and DNP
I am looking forward to v7 database functionality, and am trying it out using 6.99. I have been busy pulling my parts spreadsheet info into an sqlite db. A couple of questions:
It is nice to be able to open schematic symbol properties and click the little icon to open a datasheet. It seems like the db should contain links to datasheets (I want them local, not online). If I add a database field for datasheets, does each field need to contain a full pathname like:
/home/gil/kicad/datasheets/xyz.pdf
or can it use some sort of environment var for the path to keep the db entries shorter?
I am not a database power user, though I can tweak and manage it using db-browser or sqlite-studio. Either of those will get the job done, but I am wondering what other tools people find handy for working on an sqlite database (editing, reportingā¦) without getting deep into sql queries and such? Any pointers appreciated.
The datasheet field gets handed off to a PDF viewer. It defaults to the system PDF viewer which on my system is Chrome, but in Preferences you can point it to a script of your choice where you could implement smarts like expanding the path, e,g, differentiating between http* URLs and filenames and do the appropriate thing. I think you can also interpolate your selected environment variable.
Edit: I did a little experiment to see if a custom script can be avoided. First I defined a path variable as follows:
KICAD_USER_DATASHEET_DIR file://<absolute path to my stash of PDF datasheets>
Next I took a symbol in my libary which is a Hitachi nixie and set its datasheet field to:
${KICAD_USER_DATASHEET_DIR}/display/hitachi-cd71.pdf
Then while editing the symbol I chose Inspect > Show Datasheet and voilĆ it brought up a viewer on the datasheet.
Only glitch is on my system it fired up Calibre. Probably something to do with my desktop preferences for various file sufffixes. Your results will depend on your desktop settings.
2 Likes
I just set up a kicad path and have the datasheet loaded from the db now. Had to add a Datasheet field to the symbol. Now that brings up another question: How to add a new field to all symbols in a library? A script perhaps, or strategic text find/replace in the .kicad_sym file?
Edit ā is NOT necessary to add custom field to actual symbol (and Datasheet field is pre-defined)
Schematic Editor / File / Schematic Setup / General / Field Name Templates
If you add a name there, then it shows up as an empty field in all schematic symbols, and it get attached to any symbol for which you fill in the field.
1 Like
This is not in general the way to do it for database libraries though ā in most cases you would want to add the field via the database config file, not via the field name templates feature.
Was AFK there trying to make sense of all this. I thought I needed to add new fields (as defined in the database) to the actual symbol and that had major implications. But now I realize that was all nonsense and no edits are needed so ignore all that wonky rant of mine.
Now, let me see if I have the concepts and steps correct (as this seems to be how it is working with the little test files you provided, which I modified to point to my symbol and footprint libs):
- all kicad standard symbols have pre-defined fields for Ref, Value, Footprint, and Datasheet.
- new symbol fields can be added via mydb.sqlite and mydb.kicad_dbl files.
- in mydb.sqlite, for a part table such a Resistor, a field (column) such as MPN is defined, and populated appropriately for all parts.
- this new MPN field needs to be mapped properly in the mydb.kicad_dbl file to provide the symbol chooser with the linkage to the database.
- the real symbol library, mylib.kicad_sym, stays in the symbol editor and the new database is available when you add mydb.kicad_dbl alongside it.
- symbols are still edited in the mylib.kicad_sym library ā the db library is read-only in the symbol manager.
- from the schematic symbol chooser, parts are now available via the database in mydb-Capacitors, mydb-Resistors, etc, nicely divided into tables as defined in the database.
- from the schematic symbol chooser, parts are still available from the original mylib.kicad_sym library ā normal kicad mode.
I was able to add/commit a new field to the mydb.sqlite file, add some test data to the records, and modify the mydb.kicad_dbl file. Then I opened 6.99, created a project, placed parts on the schematic and the new field was available. If I change something in the database, the Tools/Update-Symbols-from-Library works as advertised.
So no extra edits to the symbol library as I had originally thought. I got datasheet and custom fields updating, and I am just about ready to get my production database running. I am editing and re-structuring my data in a csv file in librecalc and will import the tweaked csv into sqlite-studio to create the db. Then all changes will be in sqlite. I have all my symbols in one lib file, which keeps the chooser tidy as it is still visible, but I am going to take advantage of the db tables, as they organize the chooser nicely.
Still interested in any tips on database tools other than the db-browser and sqlite-studio with which I have been working. thx
1 Like
Yes, I set up a variable as you suggested and put ${KICAD_USER_DATASHEET_DIR}/xyz.pdf in the Datasheet fields in the sqlite database ā works great.
Didnāt need to define file:///homeā¦ but just /homeā¦ ā I am unsure if file:// is preferred or just explicit.
Apparently the utility handling the datasheet opening on Linux, xdg-open, can accept either form.
Yup, everything you describe here is correct!
What features are you looking for? I also use DBeaver sometimes.
Thanks for confirming. For a db tool, I can add/delete/tweak and maintain it with sqlite-studio pretty well, but it would be nice to find something that lets me generate some simple reports ā eg: from table x, I click checkboxes for a subset of columns, and dump to a pdf for viewing/printing. Maybe with a simple condition like āif column X is null or not null, include this record for reportingā¦ā I will peek at dbeaver.
It would actually be nice to have a way for kicad to ignore flagged records somehow (like NRND field not null), to limit the values displayed in the chooser. Example, I have started using 0402s for all my COG needs now, but I still have a batch of 0603s in my system, and need to leave them in there. A lot of chips are defined that I will not use again but need to stay in the db. Would be nice to have a visibility flag column that could be tested, but I realize that is feature creep and there are many things in the queue.
thx,gil
if you are inclined towards āprogramming languagesā i find that with a bit of SQL knowledge
you can manipulate / report db in a very efficient way, (but you can nuke them very fast as well 
)
I have a limited working knowledge of sql queries, but never really had an application until now. Is there a good python script to play with, for extracting data (I can hack my way through the sql queries) to output to something like pdf? Or C, C++, java. thx
you can start making practice using SQL queries even with the DB browser for SQLite,
you have a tab that enable execution of SQL command queries,
try typying:
select * from Capacitors where Footprints like '%0201%'
in the execute SQL tabā¦
and BTW the forun has syntax highlighting for SQL? how cool is that?
1 Like
A SQL view on the database server would be one way to accomplish this. A view is basically a āvirtual tableā that is the result of a SQL query. So you can create a view into your Resistors table that shows only the resistors you want to appear in KiCad (maybe ānot NRNDā or whatever you want the criteria to be).
1 Like
I started once with this example:
Creating A CRUD Desktop Application Using Python3 And MySQL Database Server
as base for a GUI to add components to my mysql database (the example is not from myself).
The GUI is based on tkinter and to connect to mysql it used a python library.
Once you have that running with you kicad database lib, it might be not to hard to add a pdf , csv or what ever export 
2 Likes
Thanks ā I grabbed this to play with, tweaked it for sqlite3 instead of mysql, and got it running. This is interesting and good way to learn some sql. Have not directly used tkinter before; I have done a few projects in pysimplegui which wraps tkinter and was easy to get a ui going in python. I have been trying out kivy, which has a steeper learning curve but can build nice interfaces. Might be just the project to bootstrap that.
iāve tried this method to link the datasheets and it seems to me that is only partially working:
once the component is placed on the schematic if you ckick the Datasheet filed, the pdf datasheet opens ok using the default pdf reader:
if you click the hypertext in the symbol chooser before placing the part:
the defined browser opens with an address that does not contains the substituted string:
http://%24%7Bedldb_datasheet_dir%7D/Capacitors/Murata/MURATA%20-%20Chip%20ceramic%20capacitos%20GRM033R61A225KE47.pdf
it seems that the variable substitution is not performed in the symbol chooser.
Anyone can confirm?
Application: KiCad x86_64 on x86_64
Version: 7.0.0-rc1-unknown-eba231a8bb~164~ubuntu22.04.1, release build
Libraries:
wxWidgets 3.2.1
FreeType 2.11.1
HarfBuzz 2.7.4
FontConfig 2.13.1
libcurl/7.81.0 OpenSSL/3.0.2 zlib/1.2.11 brotli/1.0.9 zstd/1.4.8 libidn2/2.3.2 libpsl/0.21.0 (+libidn2/2.3.2) libssh/0.9.6/openssl/zlib nghttp2/1.43.0 librtmp/2.3 OpenLDAP/2.5.13
Platform: Ubuntu 22.04.1 LTS, 64 bit, Little endian, wxGTK, mate, x11
Build Info:
Date: Jan 8 2023 20:32:54
wxWidgets: 3.2.1 (wchar_t,wx containers) GTK+ 3.24
Boost: 1.74.0
OCC: 7.5.2
Curl: 7.81.0
ngspice: 38
Compiler: GCC 11.3.0 with C++ ABI 1016
Build settings:
KICAD_USE_EGL=ON
KICAD_SPICE=ON
I have datasheets in the database now just like you show. I donāt put the datasheet field as visible in the schematic, so canāt comment on that, but I can click the little datasheet icon in the symbol properties dialog and it works fine.
You have spaces in your filename ā maybe that? I hate spaces in filenames (and resulting %20) and always replace them with hyphens.
The variables arenāt expanded in the symbol chooser.
1 Like