Database with non-Ghini-standard tables and views does not export to Pocket

[thayeray]

As an administrator, I want to export a nonstandard Ghini desktop snapshot, so that it is usable in Pocket.

Expected behavior

Ghini Desktop will export a snapshot of a nonstandard database over a local network connection to Ghini Pocket, and if the export is unsuccessful a descriptive error will be generated.

Actual behavior

Ghini Desktop appears to have successfully generated a snapshot and to have transferred it to Pocket, however, once in Pocket none of the data, besides the standard installation data are found by searching. Under Desktop 1.0.82 the generated export file was ~50 kB, however an uncompressed SQL backup of the database is 20 MB.

The following error is generated by the search, and can be reproduced by swiping to a different page and then back or by rotating the device:

no such table: species (code1) while compiling: SELECT s.family, s.genus, s.epithet, a.code, p.code, a.source, p.location, a.start_date, p.end_date, p.n_of_pics, p.quantity, p.edit_pending, p._id FROM species s, accession a, plant p WHERE p.accession_id = a._id AND a.species_id = s._id AND a.code = ? AND p.code = ?

Steps to reproduce the behavior

See below

How to set up the same initial situation

Will test this now with a standard database, but I believe this is produced by either the PostGIS extension, geometry columns, views or extra tables.

Which actions to take in sequence, which contribute to create the critical situation

Last step, with the unexpected result

Search Ghini Pocket for a plant code e.g. "2018.0001" or "2018.0001.1" gives a page with no data, despite there being an entry in the database for that code. Similarly searching in the "empty lookup" page for a genus gives the default genus results, but nothing when searching for entries known to be in the species table.

Opinions and suggestions

Database with non-Ghini-standard tables and views does not transfer to Pocket by either the manual export and moving through the file system or through network pull from Ghini 1.0.88. The data is PostgreSQL with PostGIS extension. The database has multiple views, and tables that are not part of the standard Ghini installation. Many of the extra tables and views have geometry columns. Some are multipolygons and others are points.

[mfrasca]

PosgreSQL+PostGIS … it would be interesting to know how you managed to get this working, because ghini-1.0 inherited the geography table from bauble, and that's occupying a reserved spot for PostGIS.

in ghini-3.1 we have moved it to geographic_area. check #356 and #326.

can you either attach the database here, or post the output of .schema? (just execute sqlite3 pocket.db .schema)

[thayeray]

So, actually I just tried to track this down and I started with a fresh database that I created using "CREATE DATABASE ghini_default;" in postgres (no postgis, I have not altered the postgresql default database). I then added it in the Ghini connection window and Ghini populated the database. I then added two species, two accessions and two plants manually in Ghini. I then connected to my phone and I have the same problem finding the data. Maybe I will try now with a fresh SQLlite database.

With respect to the geography problem in postgis I just opened the SQL dump and renamed the Ghini geography to geography_r and then restored that with psql and added the postgis extension.

[thayeray]

I left out a step, I copied the default database by: "CREATE DATABASE ghini_default_ed TEMPLATE ghini_default" Then I added my test species, etc..

[thayeray]

I think the problem is PostgreSQL as I was able to create a new default SQLite database with the connection dialog in Ghini Desktop add my 2 species, accessions and plants manually and export it to Pocket where I was able to search and find the plants but not the species table entries.

[mfrasca]

renamed the Ghini geography to geography_r and then restored that with psql

oh. does it work at all? you don't mention editing the code. if you feel adventurous, you could also help me debug ghini-3.1-dev

it's curious that you don't experience any trouble within ghini.desktop after changing one of its table names: if you don't correct this in the code too, ghini will peruse postgis' table.

[mfrasca]

changes in the code: at least the following three spots.

geography.py:171:    __tablename__ = 'geographic_area'
geography.py:177:    parent_id = Column(Integer, ForeignKey('geographic_area.id'))
species_model.py:829:    geographic_area_id = Column(Integer, ForeignKey('geographic_area.id'), nullable=False)

[mfrasca]

one more question for you, the logs: can you check for the presence of error exporting species INFO lines? but then again, if you really need postgis, I suggest you move to 3.1, which is not production ready, and for which I need help and feedback. or I could publish a temporary version 1.2, where the geography table is renamed to geographic_area. also possible, but totally less interesting for me.

[mfrasca]

as said: please attach some data, some logs, or contact me through a chat (propose different times, consider I am in -0500). or I don't know what to do with this issue. the diagnoses as of now are:

• make sure that the code knows about your changes
• move to ghini 3.1

without data, I would close the issue as 'invalid'.

[thayeray]

Sorry for the misunderstanding. Attached is a Ghini default postgreSQL database with no extensions. I have only added two species, two accessions, and two plants. I am not able to transfer this data from desktop to pocket. I was able to transfer data from a SQLite database however. The attached file was generated with (I had to change the file type to txt to get it to upload to Github): pg_dump ghini_default_pg_database -U username -h localhost > '~/pg_dataToExportToPocket.sql' pg_dataToExportToPocket.txt

[mfrasca]

I don't know what to tell you. I tried it here and it just works, with your dump. create a database, populate it with psql pg-450 < your_dump, start ghini, create a connection for this database, open the pocket server dialog box, show the settings, create the snapshot, start the server; then on the pocket end: start the client activity, enter the IP code, register, pull, back to main activity, search 2018.0003 and find it

[mfrasca]

you proposed Skype as chat option, but you didn't give me your skype name. you have mine, so please consider contacting me, I am afraid we still have an issue here, and I hope we can discuss it.

[mfrasca]

if this is still an issue for you, please reopen, but do provide the sqlite3 dump as of my first comment.