Deleting Duplicate Facts in Mass
Good Morning and thank you Tom... My data file is now RM7. What is the next step?
Good!
- Make a copy of your (.rmgc) database file and rename it, say, Old... so you can compare it with the result and have a fallback.
- Get a SQLite manager set up with a fake RMNOCASE collation, say SQLiteSpy and see RMNOCASE – faking it in SQLiteSpy #rmnocase #sqlitespy.
- Open your database file with the SQLite manager
- In its SQL editor, type or copy/paste:
REINDEX
;
CREATE TEMP TABLE OldEventTable AS
SELECT * FROM EventTable
;
CREATE TEMP TABLE NewEventTable AS
SELECT * FROM OldEventTable GROUP BY
EventType, OwnerType, OwnerID, PlaceID, SiteID, Date, Sentence, Details, Note
;
-- Uncomment to review the duplicates
--SELECT * FROM OldEventTable EXCEPT SELECT * FROM NewEventTable
;--empty the EventTable
DELETE FROM EventTable
;
INSERT INTO EventTable
SELECT * FROM NewEventTable
; - Execute above either one statement at a time or all at once, as you wish
- Now load and execute the script DeletePhantoms3.sql found at Delete Phantoms #database #delete #phantom
- Close the SQLite manager.
- Open the database with RM7 and run File>Database Tools>Rebuild Indexes and then Check Database Integrity. Repeat until OK.
- Inspect results.
I tested this on an old PLAY database and it turned up 39 duplicates.
Good luck!
Tom
Tom,
This looks way above my skill level. Would you possibly run this scrip on my file for a fee? Just asking...
Thanks,
BILL
Sure. Email to my username at gmail.com or share it some other way.
..
To close the loop for others who may have been following, the attached image shows the database file properties before and after deleting duplicate events. There was a great amount of duplication and many unused citations et al. Together with the Delete Phantoms script, the database file was shrunk from 37MB to 21MB.
Uploaded files: