1 Topic by Francesco Ferretti

  • Francesco Ferretti
  • Member
  • Offline
  • Registered: 19-06-2012
  • Posts: 8

Topic: Problems with deleting data

Dear all,

we are currently facing a couple of problems with our website, which probably occur because of the same reason.

1. We deleted a record in the warehouse (both as occurrence and as sample), but this record is still present in the maps - in our website - as well as in my list of occurrences. This record does not appear in the occurrence list and in the sample list, in the warehouse.
How could we completely delete this record?

2. We had to delete a survey ("Istrice", which is the italian name of the crested porcupine) to replace it with another survey ("ISTRICE") - that should hold the same sightings of Istrice.
When we deleted "Istrice", all relevant data disappeared from both the occurrence list and the sample list, as well as from the maps in the website. Then, we uploaded again these sightings to the new survey ISTRICE. These sightings are correctly present both in the occurrence and in the sample list, as well as in the maps. However - in the warehouse - when we try to build a report of entered data (Entered data-reports-library-occurrence list), the data of the previous survey Istrice are still present in the report browser.
Thus, it seems that we have not actually deleted these records
How could we delete completely all these records?

Thank you in advance!

Francesco

2 Reply by Jim Bacon (edited by Jim Bacon 30-10-2012 14:56:08)

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Problems with deleting data

Dear Francesco

My apologies for not noticing your post sooner.

The vital piece of information that you need to know is that none of the records in the database are ever completely deleted by Indicia.

Every table in Indicia has a column called Deleted and this field is set to true whenever you ask to delete something.

The consequence of this is that, in every query you ever make, you must include the condition 'where deleted = false' on the tables joined in the query.

You can see a demonstration of this by looking at the definition of the database views gv_occurrences or gv_samples which are what the warehouse uses to display the lists of occurrences or samples.

I note that the cache tables, which are very useful for simplifying and speeding up queries, do not contain a deleted column which is because the cache excludes deleted records for you.

By not completely deleting records it is possible to recover records accidentally deleted. If you really wish to completely delete records you will have to execute delete queries on the database directly.

Regards,
Jim Bacon.

3 Reply by Francesco Ferretti

  • Francesco Ferretti
  • Member
  • Offline
  • Registered: 19-06-2012
  • Posts: 8

Re: Problems with deleting data

Dear Jim,
Thank you for your reply! We will try as you suggest.
Best regards,
Francesco

4 Reply by Andrea Sforzi

  • Andrea Sforzi
  • Member
  • Offline
  • From: Grosseto, Tuscany, Italy
  • Registered: 16-04-2012
  • Posts: 32

Re: Problems with deleting data

Dear Jim, we tried your way, but some errors were reported (see below).
The question is: are there any other dependences on the table occurrence_comments?
[I hope that my English translation of the question written in Italian by our technician will work fine ...!]

indicia=# select count(*) from indicia.samples where deleted;
count
-------
    67
(1 row)

indicia=# select count(*) from indicia.occurrences where deleted;
count
-------
   108
(1 row)


indicia=# delete from indicia.occurrences where deleted;
ERROR:  update or delete on table "occurrences" violates foreign key constraint "fk_occurrence_comment_occurrence" on table "occurrence_comments"
DETAIL:  Key (id)=(67) is still referenced from table "occurrence_comments".
indicia=# rollback;
NOTICE:  there is no transaction in progress
ROLLBACK

Andrea Sforzi's Website

5 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Problems with deleting data

Hi Andrea,

You have found that you cannot delete occurrence records which have dependent occurrence_comment records.

The answer to your question is that there are no tables dependent on occurrence_comments.

However, you may have meant to ask if there are other tables dependent on the occurrences table to which the answer is yes.

The following query lists them

SELECT tc.constraint_name, tc.table_name from information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND ccu.table_name = 'occurrences'

On the test warehouse where I am working this does not include occurrence_images as a dependent table which I think is a mistake of the database set up.

In the past I have used the following query to delete one sample and its dependent records. 

set search_path=indicia, public;
drop table if exists var_sample_id;
select ##sample_id## into temp var_sample_id;

delete from occurrence_attribute_values where id in (
select oav.id
from occurrence_attribute_values oav
inner join occurrences o on o.id = oav.occurrence_id
inner join samples s on s.id = o.sample_id
where s.id = (select * from var_sample_id));

delete from occurrence_images where id in (
select oi.id
from occurrence_images oi
inner join occurrences o on o.id = oi.occurrence_id
inner join samples s on s.id = o.sample_id
where s.id = (select * from var_sample_id));

delete from occurrence_comments where id in (
select oc.id
from occurrence_comments oc
inner join occurrences o on o.id = oc.occurrence_id
inner join samples s on s.id = o.sample_id
where s.id = (select * from var_sample_id));

delete from sample_attribute_values where id in (
select sav.id
from sample_attribute_values sav
inner join samples s on s.id=sav.sample_id
where s.id = (select * from var_sample_id));

delete from sample_images where id in (
select si.id
from sample_images si
inner join samples s on s.id=si.sample_id
where s.id = (select * from var_sample_id));

delete from sample_comments where id in (
select sc.id
from sample_comments sc
inner join samples s on s.id=sc.sample_id
where s.id = (select * from var_sample_id));

delete from occurrences
where sample_id  = (select * from var_sample_id);

delete from samples where id = (select * from var_sample_id);

I would replace ##sample_id## with a sample id.
You will want to change that line to something like

select id into temp var_sample_id from samples where deleted = true;

and, in the delete subqueries, I expect you will need to change the filter from '=' to 'in' like

where s.id in (select * from var_sample_id)

Jim Bacon

6 Reply by Francesco Ferretti

  • Francesco Ferretti
  • Member
  • Offline
  • Registered: 19-06-2012
  • Posts: 8

Re: Problems with deleting data

Dear Jim,
we tried and finally we succeeded to delete data.
Thank you again!
Francesco

7 Reply by BDeed

  • BDeed
  • Veteran
  • Offline
  • From: Bolton
  • Registered: 01-03-2011
  • Posts: 447

Re: Problems with deleting data

Hi Jim,
Looking into deleting some records on one of the MBB datasets (i uploaded a bulk of Buzzards to test the upload feature!). We're using the BRC warehouse, i'm assuming this type of query can only be run from your end?
It's not the end of the world if they can't be removed (they're genuine records) but it does make the recording look very buzzard biased when i map all records for users to query.
Cheers,
Ben

Natural History & Biodiversity Data Enthusiast

8 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Problems with deleting data

Hi Ben,

Just stumbled on your post. Executing SQL commands directly on the database, especially delete commands, are definitely the privilege of the warehouse administrator. You can delete records one by one through the warehouse website and if, for example, you delete a sample, this is cascaded down to delete all related table entries automatically.
You could also write a client-side interface which would allow you to delete records owned by your website. It might still do it on the warehouse one at a time but your client side script could be written to select many and loop through them.

Jim Bacon.