← 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"

 

BINGO! That didn't take long.  I knew the non-sql database question
would rear it's ugly head.  It just happened quicker than I thought it
would ;)

On 8/29/20 9:58 AM, Tim Hawkins wrote:
> https://calcite.apache.org/docs/adapter.html
> 
> Apache calcite is an SQL query engine for non-sql data sources. 
> 
> 
> 
> On Sat, Aug 29, 2020, 21:21 Jeff Young <jeff@xxxxxxxxx
> <mailto:jeff@xxxxxxxxx>> wrote:
> 
>     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
>>     <mailto: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>> 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
>>>
>>>                    Others would maybe like to save a file in ~/.KiCad (so
>>>                    the URI would be 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>>:
>>>
>>>                        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>>:
>>>
>>>                                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>> wrote:
>>>>
>>>>                                
>>>>                                Hi Clemens,
>>>>
>>>>                                On Fri, Aug 28, 2020 at 9:34 AM Clemens
>>>>                                Koller <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>
>>>>                                Unsubscribe :
>>>>                                https://launchpad.net/~kicad-developers
>>>>                                More help   :
>>>>                                https://help.launchpad.net/ListHelp
>>>                                _______________________________________________
>>>                                Mailing list:
>>>                                https://launchpad.net/~kicad-developers
>>>                                Post to     :
>>>                                kicad-developers@xxxxxxxxxxxxxxxxxxx
>>>     <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>>                                <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>>                                Unsubscribe :
>>>                                https://launchpad.net/~kicad-developers
>>>                                More help   :
>>>                                https://help.launchpad.net/ListHelp
>>>
>>>                            _______________________________________________
>>>                            Mailing list:
>>>                            https://launchpad.net/~kicad-developers
>>>                            Post to     :
>>>                            kicad-developers@xxxxxxxxxxxxxxxxxxx
>>>     <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>>                            <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>>                            Unsubscribe :
>>>                            https://launchpad.net/~kicad-developers
>>>                            More help 
>>>      : https://help.launchpad.net/ListHelp
>>>
>>>            _______________________________________________
>>>            Mailing list: https://launchpad.net/~kicad-developers
>>>            Post to     : kicad-developers@xxxxxxxxxxxxxxxxxxx
>>>     <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>>            <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>>            Unsubscribe : https://launchpad.net/~kicad-developers
>>>            More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>>
>>>        -- 
>>>        Mark
>>>
>>>
>>>     _______________________________________________
>>>     Mailing list: https://launchpad.net/~kicad-developers
>>>     Post to     : kicad-developers@xxxxxxxxxxxxxxxxxxx
>>>     <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>>     Unsubscribe : https://launchpad.net/~kicad-developers
>>>     More help   : https://help.launchpad.net/ListHelp
>>>
>>
>>     _______________________________________________
>>     Mailing list: https://launchpad.net/~kicad-developers
>>     Post to     : kicad-developers@xxxxxxxxxxxxxxxxxxx
>>     <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>>     Unsubscribe : https://launchpad.net/~kicad-developers
>>     More help   : https://help.launchpad.net/ListHelp
> 
>     _______________________________________________
>     Mailing list: https://launchpad.net/~kicad-developers
>     Post to     : kicad-developers@xxxxxxxxxxxxxxxxxxx
>     <mailto:kicad-developers@xxxxxxxxxxxxxxxxxxx>
>     Unsubscribe : https://launchpad.net/~kicad-developers
>     More help   : https://help.launchpad.net/ListHelp
> 


Follow ups

References