Update procedures.sqlite
Created by: tweep
We're having some trouble with the live_roles view in our installtion. We are susing SQLite version 3.6.20 - somehow SQLite throws on this left join for the live_roles table with this error:
Error: cannot join using column analysis_id - column not present in both tables
LEFT JOIN analysis_base a USING(analysis_id)
However, this explicit LEFT join works for us and our SQLite installation:
LEFT JOIN analysis_base a ON a.analysis_id = r.analysis_id
Here's the full error when creating the live_roles view in SQLite:
sqlite> CREATE VIEW IF NOT EXISTS live_roles AS
...> SELECT w.meadow_user, w.meadow_type, w.resource_class_id, rc.name resource_class_name, r.analysis_id, a.logic_name, count(*)
...> FROM worker w
...> JOIN role r USING(worker_id)
...> LEFT JOIN resource_class rc ON w.resource_class_id=rc.resource_class_id
...> LEFT JOIN analysis_base a USING(analysis_id)
...> WHERE r.when_finished IS NULL
...> GROUP BY w.meadow_user, w.meadow_type, w.resource_class_id, rc.name, r.analysis_id, a.logic_name;
Error: cannot join using column analysis_id - column not present in both tables