← Back to team overview

kicad-developers team mailing list archive

Re: Placing real components out of a database instead of: "Automatic assignment of footprint with a database"

 

Surely there must be an open source impl of an ODBC interface on a CSV file?

Although I’m not sure of the desire to avoid MySQL.  It’s remarkably easy to set up an instance (or auto-deploy one with an app).  

Apologies if we’ve already talked about that; I’ll confess to not having followed this thread 100%….

> On 29 Aug 2020, at 14:11, Wayne Stambaugh <stambaughw@xxxxxxxxx> wrote:
> 
> I would most likely reject any solution that was tied to a particular
> database.  All this would do is open up a Pandora's box of complaints as
> to why we didn't use database A over database B.  ODBC is the most
> flexible solution that I am aware of and allows users to choose their
> preferred database.
> 
> Cheers,
> 
> Wayne
> 
> On 8/29/20 8:29 AM, Jon Evans wrote:
>> Putting aside the fact that I think this feature isn't really aimed at
>> hobbyists, I would not be opposed to people wanting to extend it beyond
>> ODBC but that comes with extra complexity that must be handled.
>> 
>> With ODBC, KiCad just needs to know about the interface to retrieve the
>> data.
>> 
>> With a CSV file, KiCad actually needs to read that file in and keep it
>> in memory.  Watch for modifications on disk, or else lock it
>> exclusively.  Things like that.
>> 
>> I'm not sure I really see the advantage of a CSV-backed "database" over
>> the existing KiCad library system, if we're talking about a single user.
>> 
>> -Jon
>> 
>> On Sat, Aug 29, 2020 at 8:19 AM Mark Roszko <mark.roszko@xxxxxxxxx
>> <mailto:mark.roszko@xxxxxxxxx <mailto:mark.roszko@xxxxxxxxx>>> wrote:
>> 
>>> Sqlite was a quick&dirty way to test if my ideas would work. 
>>    There are ODBC wrappers for SQLite........
>> 
>>> I mean libreoffice would do for the management.
>>    Yes, and you know what you would use? Not CSV files.
>>    LibreOffice Base / Microsoft Access.
>>    This is the office suite database that's basically SQLite and there
>>    are ODBC wrappers as well :D
>> 
>>> Also, why would a hobbyist fire up a sql database when a CSV file
>>    would be sufficient? I mean libreoffice would do for the management.
>> 
>>    KiCad's uses aren't limited to hobbyists...
>>    And, you assume there aren't hobbyists like me who will gladly take
>>    that ODBC link and spin up an frontend in a few hours to the whole
>>    system :D
>> 
>> 
>>    On Sat, Aug 29, 2020 at 4:22 AM Johann Wilhelm
>>    <johann.wilhelm@wilhelm.consulting <mailto:johann.wilhelm@wilhelm.consulting>> wrote:
>> 
>>        Hi there!
>> 
>>        Well, then my comment was not completely wrong.
>> 
>>        Sqlite was a quick&dirty way to test if my ideas would work. For
>>        my future productive system I really want to use a mix of
>>        couchdb and maybe postgres. i.e. a JSON document storage for the
>>        component information and sql for inventory management.
>> 
>>        So ODBC would not work well for me.
>> 
>>        Also, why would a hobbyist fire up a sql database when a CSV
>>        file would be sufficient? I mean libreoffice would do for the
>>        management.
>> 
>>        Additionally, I'd suggest looking at the BOM creation. There,
>>        external programs are called.
>> 
>>        So why not define a dataformat (xml, json, csv,...) and just
>>        call an external app or read from a file/uri?
>> 
>>        Anyways, I would volunteer for implementing some alternatives
>>        (read from file/uri and output of executable) to the ODBC
>>        interface if someone guides me through the KiCad procedures.
>> 
>>        Regards,
>>            Johann
>> 
>> 
>>        Jon Evans <jon@xxxxxxxxxxxxx <mailto:jon@xxxxxxxxxxxxx> <mailto:jon@xxxxxxxxxxxxx <mailto:jon@xxxxxxxxxxxxx>>> schrieb
>>        am Fr., 28. Aug. 2020, 19:54:
>> 
>>            My idea is to make it possible for KiCad to talk to an
>>            external database.
>>            The database itself (and its schema) will not be defined as
>>            part of this spec, and will not be part of KiCad.
>> 
>>            The only requirement is that you have some columns in your
>>            schema that KiCad understands (for example, to point to the
>>            right symbols and footprints in the KiCad libraries).
>>            The planned interface to connect to the database is ODBC. 
>>            This would in theory allow using sqlite to create a database
>>            as a file on disk, although I anticipate that most users
>>            will be using something like MariaDB or Postgres.
>> 
>>            There has been some discussion of supporting talking to web
>>            interfaces via some REST API, or even talking to arbitrary
>>            interfaces via Python scripting, but that discussion should
>>            stay separate.
>>            The original thread was about getting component information
>>            out of a database and I just wanted to let people know that
>>            I am working on this.
>> 
>>            People are welcome to also discuss the ideas of getting
>>            component information from a web API or from some Python script.
>>            But, I am not working on that right now, and there hasn't
>>            even been a conversation started on what that spec would
>>            look like.
>> 
>>            Best,
>>            Jon
>> 
>>            On Fri, Aug 28, 2020 at 1:42 PM Johann Wilhelm
>>            <johann.wilhelm@wilhelm.consulting <mailto:johann.wilhelm@wilhelm.consulting>> wrote:
>> 
>>                Hi Jon,
>> 
>>                Well, you're idea was to define a database or at least a
>>                database schema if I understood this correctly:
>> 
>>                    What I plan on implementing is not a "full" database management system, but
>> 
>>                    rather an interface to grab info out of a database, just like you say.
>> 
>>                    The only difference is, there are no plans to store actual symbols or
>> 
>>                    footprints in the database.
>> 
>>                    The symbols and footprints will still be stored in "normal" KiCad library
>> 
>>                    files; the database will just contain "pointers" telling KiCad which symbol
>> 
>>                    to use (and what library it can be found in).
>> 
>> 
>> 
>>                I was pointing toward a specification of a data
>>                format.So either read the data from a file or
>>                webinterface (that's why I used URI).
>>                My script-set currently gives me that information via
>>                http://localhost:8000/API/Component/getMatchWisdom <http://localhost:8000/API/Component/getMatchWisdom>
>> 
>>                Others would maybe like to save a file in ~/.KiCad (so
>>                the URI would be file://~/.KiCad <file://~/.KiCad>)
>> 
>>                If "database"/"database interface" would include
>>                something which could read from files and/or web
>>                resources, well, never mind my comment :)
>> 
>>                With "global parameter" I mean something which could be
>>                part of a .pro file.
>> 
>>                Regards,
>>                    Johann
>> 
>> 
>>                Am Fr., 28. Aug. 2020 um 19:04 Uhr schrieb Jon Evans
>>                <jon@xxxxxxxxxxxxx <mailto:jon@xxxxxxxxxxxxx> <mailto:jon@xxxxxxxxxxxxx <mailto:jon@xxxxxxxxxxxxx>>>:
>> 
>>                    Hi Johann,
>> 
>>                    I am not sure exactly what you are arguing against. 
>>                    I don't see any difference between what you are
>>                    looking for and what is in my spec.
>>                    I am not sure what you mean by "global URI
>>                    parameter" but the part picker will be able to
>>                    filter using any of the external data present.
>> 
>>                    Best,
>>                    Jon
>> 
>>                    On Fri, Aug 28, 2020 at 12:56 PM Johann Wilhelm
>>                    <johann.wilhelm@wilhelm.consulting <mailto:johann.wilhelm@wilhelm.consulting>> wrote:
>> 
>>                        Hi everyone!
>> 
>>                        I'm new here in the mailing list but I'm
>>                        currently building my own electronics business
>>                        around KiCad which I use and love for years now.
>> 
>>                        I spent the last weeks, trying to tie together
>>                        procurement, PCB design, and fabrication in a
>>                        single script-set.
>>                        Just to have it mentioned - before getting
>>                        self-employed, I worked for 10 years in the
>>                        electronics industry in a huge enterprise.
>>                        So I had some strict requirements for my tooling
>>                        and I have some very strong opinions on how my
>>                        perfect workflow should look like :)
>> 
>>                        I'm very sorry but I need to say: PLEASE, don't
>>                        just throw a part database at the community!
>>                        Why? Because everyone has different
>>                        approaches how to do procurement and inventory
>>                        management!
>>                        It's ok (and actually good!) if you try to come
>>                        up with something but PLEASE, go for a defined
>>                        filter interface!
>> 
>>                        What I would suggest implementing  - actually, I
>>                        have plans to implement it myself in mid-term -
>>                        is a simple checkbox in the component dialog
>>                        named "Apply Parts Filter" and a global URI
>>                        parameter that provides the filter (or better:
>>                        the source of "wisdom" used by the filter).
>>                        Maybe another parameter defining the default for
>>                        the checkbox would be nice as well...
>> 
>>                        I think even a simple CSV with columns for
>>                        Symbol, Footprint, and Value could provide
>>                        sufficient information for effective
>>                        filtering/adaption of the symbol-tree.
>> 
>>                        A CSV like this:
>> 
>> 
>>                            "Audio:TLV320AIC23BPW", "TLV320AIC23BPW", "Package_SO:TSSOP-28_4.4x9.7mm_P0.65mm"
>>                            "Device:R_Small", "1k", "Resistor_SMD:R_0805_2012Metric"
>>                            "Device:R*", "12k4",
>>                            "Resistor_SMD:R_0805_2012Metric"
>>                            "Device:R*", "12k4", "Resistor_SMD:R_1206_3216Metric"
>>                            "Device:R*", "10R", "Resistor_SMD:R_0805_2012Metric"
>> 
>> 
>>                        should result in a component tree like this:
>> 
>>                            Audio
>> 
>>                                TLV320AIC23BPW     <-- this symbol has a
>>                                matching default footprint and value
>> 
>>                            Device
>> 
>>                                R  <-- this symbol is included in the
>>                                CSV data
>> 
>>                                    12k4     <-- a value of 12k4 would
>>                                    match Device:R and a corresponding
>>                                    symbol is added by the filter
>> 
>>                                        Resistor_SMD:R_0805_2012Metric 
>>                                         <--- both footprints would
>>                                        match Device:R 12k4 so the
>>                                        filter adds both symbols with
>>                                        the footprint and values fields
>>                                        complemented
>>                                        Resistor_SMD:R_0805_2012Metric 
>>                                         <--- ....
>> 
>>                                    10R
>> 
>>                                        Resistor_SMD:R_0805_2012Metric
>> 
>>                                R_Small
>> 
>>                                    1k    <-- 1k only matches Device:R_Small
>> 
>>                                        Resistor_SMD:R_0805_2012Metric
>> 
>>                                    12k4
>> 
>>                                        Resistor_SMD:R_0805_2012Metric
>>                                        Resistor_SMD:R_0805_2012Metric
>> 
>>                                    10R
>> 
>>                                        Resistor_SMD:R_0805_2012Metric
>> 
>> 
>>                        So the filter should add all symbols referenced
>>                        in the CSV.
>>                        If a symbol has no matching footprint and/or
>>                        value => "create" them on fly
>> 
>>                        With this, you can use the whole symbol library
>>                        or the existing parts in your inventory.
>>                        The best of both worlds!
>> 
>>                        Regards,
>>                            Johann
>> 
>> 
>>                        Am Fr., 28. Aug. 2020 um 16:38 Uhr schrieb Brian
>>                        <lotharyx@xxxxxxxxx <mailto:lotharyx@xxxxxxxxx> <mailto:lotharyx@xxxxxxxxx <mailto:lotharyx@xxxxxxxxx>>>:
>> 
>>                            Just want to add my +1 for the interface
>>                            approach.  I am glad to hear that is the
>>                            intent (I’ve not had time to read the
>>                            proposal).  With such an interface, as has
>>                            been pointed out already,
>>                            data-source-specific implementations should
>>                            be relatively straightforward, and then I
>>                            don’t have to potentially throw away my
>>                            years of privately curated data or figure
>>                            out how to cram it into some alternate
>>                            schema.  All I would need to do is write the
>>                            code to answer the questions presented by
>>                            the interface.
>> 
>>                            Hopefully in the next few days I’ll be able
>>                            to read Jon’s draft spec and comment from a
>>                            better-informed position.
>> 
>>                            Cheers,
>>                            -Brian
>> 
>>>                            On Aug 28, 2020, at 9:43 AM, Jon Evans
>>>                            <jon@xxxxxxxxxxxxx <mailto:jon@xxxxxxxxxxxxx>
>>>                            <mailto:jon@xxxxxxxxxxxxx <mailto:jon@xxxxxxxxxxxxx>>> wrote:
>>> 
>>>                            
>>>                            Hi Clemens,
>>> 
>>>                            On Fri, Aug 28, 2020 at 9:34 AM Clemens
>>>                            Koller <cko@xxxxxxxxx <mailto:cko@xxxxxxxxx>
>>>                            <mailto:cko@xxxxxxxxx <mailto:cko@xxxxxxxxx>>> wrote:
>>> 
>>>                                Hello!
>>> 
>>>                                This is related to the previous
>>>                                thread: "Automatic assignment of
>>>                                footprint with a database"
>>> 
>>>                                I would generally prefer assemble real
>>>                                components on a real PCB right from
>>>                                the beginning instead of first placing
>>>                                generic components and then assign
>>>                                footprints + manufacturers + types + x
>>>                                manually. This seems extra work for
>>>                                each component which could possibly be
>>>                                avoided.
>>> 
>>> 
>>>                            Me too!
>>> 
>>> 
>>>                                So, regarding on the Kicad codebase, I
>>>                                would very likely not recomment to
>>>                                embed a full component management /
>>>                                database system, since this might vary
>>>                                from site to site and even from
>>>                                project/assembly house to
>>>                                project/house. But it would be great
>>>                                to be able to have an interface to
>>>                                grab a component out of a database and
>>>                                Kicad grabs all desired / a selection
>>>                                of individual columns out of the
>>>                                database as needed.
>>>                                This might include the actual
>>>                                footprints stored in the database as well.
>>> 
>>> 
>>>                            What I plan on implementing is not a
>>>                            "full" database management system, but
>>>                            rather an interface to grab info out of a
>>>                            database, just like you say.
>>>                            The only difference is, there are no plans
>>>                            to store actual symbols or footprints in
>>>                            the database.
>>>                            The symbols and footprints will still be
>>>                            stored in "normal" KiCad library files;
>>>                            the database will just contain "pointers"
>>>                            telling KiCad which symbol to use (and
>>>                            what library it can be found in).
>>> 
>>>                            BTW, the example schema in your email
>>>                            looks very familiar to me.  This is the
>>>                            kind of data source that can be used with
>>>                            the feature I am talking about: just add
>>>                            columns to the schema for tracking which
>>>                            KiCad symbol and footprint(s) should be
>>>                            used for a part.
>>> 
>>>                            Regarding the advanced features you
>>>                            mention, some of them sound like they
>>>                            would be handled by a PLM tool.
>>>                            Some of the PLM tools I have worked with
>>>                            can interface to external databases for
>>>                            managing this kind of component library
>>>                            for an EDA or mechanical CAD tool.
>>> 
>>>                            Best,
>>>                            Jon
>>>                            _______________________________________________
>>>                            Mailing list:
>>>                            https://launchpad.net/~kicad-developers
>>>                            Post to     :
>>>                            kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>>                            <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>>
>>>                            Unsubscribe :
>>>                            https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
>>>                            More help   :
>>>                            https://help.launchpad.net/ListHelp <https://help.launchpad.net/ListHelp>
>>                            _______________________________________________
>>                            Mailing list:
>>                            https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
>>                            Post to     :
>>                            kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>                            <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>>
>>                            Unsubscribe :
>>                            https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
>>                            More help   :
>>                            https://help.launchpad.net/ListHelp <https://help.launchpad.net/ListHelp>
>> 
>>                        _______________________________________________
>>                        Mailing list:
>>                        https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
>>                        Post to     :
>>                        kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>                        <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>>
>>                        Unsubscribe :
>>                        https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
>>                        More help   : https://help.launchpad.net/ListHelp <https://help.launchpad.net/ListHelp>
>> 
>>        _______________________________________________
>>        Mailing list: https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
>>        Post to     : kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>        <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>>
>>        Unsubscribe : https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
>>        More help   : https://help.launchpad.net/ListHelp <https://help.launchpad.net/ListHelp>
>> 
>> 
>> 
>>    -- 
>>    Mark
>> 
>> 
>> _______________________________________________
>> Mailing list: https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
>> Post to     : kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>> Unsubscribe : https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
>> More help   : https://help.launchpad.net/ListHelp <https://help.launchpad.net/ListHelp>
>> 
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
> Post to     : kicad-developers@xxxxxxxxxxxxxxxxxxx <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
> Unsubscribe : https://launchpad.net/~kicad-developers <https://launchpad.net/~kicad-developers>
> More help   : https://help.launchpad.net/ListHelp <https://help.launchpad.net/ListHelp>

Follow ups

References