You have to implement m:n relationships yourself inside of your plugin if you need that. I recommend trying to use an ORM like sql-alchemy to make life easier.
FYI there's also some cool Offline plugins that let you take Postgis tables offline in a temporary spatialite db, and then sync when back online. Thanks, Alex On 09/30/2016 11:37 AM, Tyler Veinot wrote: > That was my thought for a solution, do the data entry in QGIS and use that > as the primary got to for entry and data retrieval using data entry forms > and widgets. Issue has been the m:n relationships. I downloaded postgreSQL > portable to try and since upgrading to 2.16.3 I have not tried my SQLite > forms to see if they work better as of yet; hoping to get to it this > weekend but I got quite a bit of work to do before I can get back to it. > Likely going to have to go with PostgreSQL; it is the only one that works > well with both platforms. > Tyler > > On Fri, Sep 30, 2016 at 2:27 PM, Alex M <[email protected]> wrote: > >> I looked for this unicorn many moons ago. It sadly doesn't exist. I have >> not found any interface quite like Access (which I have successfully >> used via ODBC to sqlite). Libre Base comes close but there's been many >> years of them saying they'd implement sqlite as a 1st class db and no >> movement, they always prefer Java based solutions it seems. >> >> The conclusion I reached is that you need either a web or local >> interface to your db. For data entry I looked into writing a custom >> Python application with the widgets I wanted for data entry, a node type >> app could work also (share code between a web interface and desktop >> interface). One option would be to make custom forms, or a plugin for >> QGIS and do all your data entry there. >> >> For writing queries there's lots of GUI tools, I usually use Spatialite >> GUI, QGIS QSpatialite Plugin, or Firefox SQLite Manager (non-spatial >> queries only). But both of these would be terrible for mass data entry. >> >> You could also, do a spreadsheet->csv->db script >> >> Thanks, >> Alex >> >> On 09/28/2016 11:06 AM, Tyler Veinot wrote: >>> Luigi; >>> Thanks, I will give it a try. Also tried using using MS Access to connect >>> to the SQLite database following steps I found online using an ODBC >>> connection. Got no useable sqlite3.dll found. It doesn't tell me where it >>> is looking or anything; the ODBC driver install did put an sqlite3.dll in >>> the system32 folder so I am not sure what the issue is, if the file is >> not >>> what access is looking for or if it is looking in the wrong spot for it; >>> Libre seems to be able to find it no problem. >>> Tyler >>> >>> On Wed, Sep 28, 2016 at 1:22 PM, Luigi Pirelli <[email protected]> wrote: >>> >>>> Tyler may you try with latest backported bugfixes on sqlite ogr >>>> provider? Probably related with your problem ("unable to commit >>>> changes to the database"). Check if it is similar following these >>>> steps: https://hub.qgis.org/issues/15351 >>>> >>>> regards >>>> Luigi Pirelli >>>> >>>> ************************************************************ >>>> ************************************** >>>> * Boundless QGIS Support/Development: lpirelli AT boundlessgeo DOT com >>>> * LinkedIn: https://www.linkedin.com/in/luigipirelli >>>> * Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli >>>> * GitHub: https://github.com/luipir >>>> * Mastering QGIS: >>>> https://www.packtpub.com/application-development/mastering-qgis >>>> ************************************************************ >>>> ************************************** >>>> >>>> >>>> On 28 September 2016 at 17:54, Tyler Veinot <[email protected]> >>>> wrote: >>>>> I would prefer to go with postgre but I think that is going to have to >>>> be a >>>>> future project; right now we use a file geodatabase because it can be >>>> copied >>>>> and distributed. Some of our PC's are disconnected from a network >> and/or >>>>> have very limited network access. To my knowledge I cannot just copy a >>>>> Postgres, MySQL, Maria, or other like databases to a PC or tablet and >> run >>>>> them. I have run Postgre as a local service doing some testing and >>>> playing >>>>> but I have no place to put it for everyone to access, thats why I am >>>> looking >>>>> for a SQLite/File geodatabase type solution. >>>>> SQLite's only issue is that the qgsEditRelationWidget and >>>> qgsRelationWidget >>>>> don't seem to respond well to it. I have tried a number of different >>>> ways to >>>>> make those features work and it always ends in either a crash with >>>> minidump >>>>> or "unable to commit changes to the database". LibreBase does not >>>>> acknowledge SQLite's capabilities as a database; tried it with both >> JDBC >>>> and >>>>> ODBC drivers. I can build but cannot edit tables, will not support >>>> relations >>>>> or SQL Queries containing inner, outer, or full joins. >>>>> What about a GUI for SQLite? Something like Access for SQLite >> databases? >>>> If >>>>> there was such a thing I could move the data entry to that; looks like >> I >>>>> have some googling to do. >>>>> Tyler >>>>> >>>>> On Wed, Sep 28, 2016 at 12:01 PM, John Harrop <[email protected]> >>>> wrote: >>>>>> >>>>>> You could run it as a local server and it is available on all major >>>>>> platforms. If you need a server with wider access you could look at >> AWS >>>>>> (even the free tier will give you some RDB service). The Postgresql >>>>>> supported there has the PostGIS extension ready to load so for us it >>>> was a >>>>>> logical choice for hosting spatial data. >>>>>> >>>>>> John Harrop >>>>>> [email protected] >>>>>> >>>>>> >>>>>> >>>>>>> On Sep 27, 2016, at 8:09 AM, Tyler Veinot <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>> Hello; >>>>>>> I need some database options; I tried PostgreSQL, HSQL, >>>>>>> SQLite/Spatialitem, Access (mdb/accdb) and what I get is either it >>>> works >>>>>>> well with QGIS and doesn't work well at all with LibreBase or vice >>>> versa, >>>>>>> with the exception of PostgeSQL which works well with both but I >>>> don't have >>>>>>> a server to run it on. Are there any other databases that you can >>>> suggest? >>>>>>> With no server at the moment I would like something that exists in a >>>> file in >>>>>>> the same way; SQLite, Access, or file geodatabases do, but also >>>> compatible >>>>>>> with QGIS and LibreBase. >>>>>>> Thanks; >>>>>>> Tyler >>>>>>> _______________________________________________ >>>>>>> Qgis-user mailing list >>>>>>> [email protected] >>>>>>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user >>>>>>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user >> >> > > > > _______________________________________________ > Qgis-user mailing list > [email protected] > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user > _______________________________________________ Qgis-user mailing list [email protected] List info: http://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
