Topic: Indicia warehouse initial database issue
Hi,
as a hobby entomologist I am working on a small scale monitoring project together with some friends. To get all the data together I decided to try and program/setup a website. Searching the web for a while I came across Indicia and I really like what I have seen so far. Seems to suite our needs perfectly. So this is my first contact with Indicia, hope you don't mind my beginner questions.
After reading the docs and trying for a while I managed to get things up to an extend but still fail with the warehouse. This is where I'd like to ask advice on what I am doing wrong.
Specs:
Debian 9
Apache 2.4.25
PHP 7.0.19
PostgreSQL 9.6.6
PostGIS 2.3.1
So webserver and database are up and prepared. Opening the warehouse setup all things are shown as green (worked through all that before) and the setup does install the database part correctly as it tells me "Congratulations". Then as the manual says I login as admin, reset the password and then instead of the welcome page I end up with the error below:
2018-01-07 19:43:15 +00:00 --- error: Uncaught Kohana_Database_Exception: There was an SQL error: ERROR: Column »last_scheduled_task_check« does not exist
LINE 1: SELECT sum(case when last_scheduled_task_check > now()-'1 da...
^ - SELECT sum(case when last_scheduled_task_check > now()-'1 day'::interval then 1 else 0 end) as new, sum(case when last_scheduled_task_check <= now()-'1 day'::interval then 1 else 0 end) as old
FROM system
WHERE last_scheduled_task_check is not null in file system/libraries/drivers/Database/Pgsql.php on line 342
Tested different versions (1.49.0, 1.53.0, master, develop) but keep ending up there. Looking through the code I figured that the database was populated with the very initial stuff but is missing a lot of updates. After some time I figured that upgrading the DB can be done by opening the following URI: .../warehouse/index.php/home/upgrade (I am not allowed to post URLs)
This took a couple of seconds and then failed with:
2018-01-07 20:02:35 +00:00 --- error: Error in ..../warehouse/modules/cache_builder/db/version_1_15_0/201611
121607_cache_occurrences_id_diff_data.sql
2018-01-07 20:02:35 +00:00 --- error: There was an SQL error: ERROR: Relation »verification_rule_data« does not exist
LINE 5: from verification_rule_data vrd
^ - -- #slow script#
update cache_occurrences_functional o
set identification_difficulty=vrd.value::integer
from verification_rule_data vrd
join verification_rules vr on vr.id=vrd.verification_rule_id and vr.deleted=false
and vr.test_type='IdentificationDifficulty'
where vrd.header_name='Data' and upper(vrd.key)=o.taxa_taxon_list_external_key and vrd.deleted=false;
Couldn't help myself other than commenting that SQL code in the sql file and trying to upgrade again via URI .../warehouse/index.php/home/upgrade
This time I got:
2018-01-07 20:13:41 +00:00 --- error: Error in ..../warehouse/modules/cache_builder/db/version_1_49_0/201711071410_cache_occurrences_view.sql
2018-01-07 20:13:41 +00:00 --- error: There was an SQL error: ERROR: Column o.confidential does not exist
LINE 71: o.confidential
^ - CREATE OR REPLACE VIEW cache_occurrences AS
SELECT o.id,
o.record_status,
...
o.confidential
FROM cache_occurrences_functional o
JOIN cache_occurrences_nonfunctional onf on onf.id=o.id
JOIN cache_samples_nonfunctional snf on snf.id=o.sample_id
JOIN cache_taxa_taxon_lists cttl on cttl.id=o.taxa_taxon_list_id;
To me it feels like there is something that I am missing right from the start.
Thank you very much in advance!
Sebastian