extract all ics events from db

christf commented 3 years ago

I am opening this as an issue because I am unsure where else to put this. I accidentally deleted my caldav account on my phone during a nextcloud upgradecycle so my calendar is gone. Before that happened I sent myself an export of all the events thinking it would be a zip file containing all the ics files that would go into caldav. Unfortunately I didn't check but I still have all my data.

So, how can I interpret that db file to extract the calendar information and restore my data? There is no wiki available on this page so I am not sure where else I can look for documentation on this.

By looking at the file I have learned that it is an sqlite database. I can open it using the sqlite binary. The tables are described as

.tables
Attendees             Events                _sync_state_metadata
CalendarAlerts        EventsRawTimes        android_metadata    
CalendarCache         ExtendedProperties    somc_metadata       
CalendarMetaData      Instances             view_events         
Calendars             Reminders           
Colors                _sync_state

querying the Events and view_events tables seems possible. This leaves me hopeful the data can be retrieved but frankly I have no idea what structure I am looking at here. Hints towards documentation are appreciated.

cainbantam commented 3 years ago

I have the same issue. It is possible to export data but not to restore data, so we're stuck. Please help!

christf commented 3 years ago

I am currently working on a small python script to convert the content of the events to ics files. It does not work yet as there are two main issues:

my nextcloud 20 installation does not accept yet the import and discards all VEVENTS as broken even though the file in question is considered to be fine by the https://icalendar.org/validator.html#results
writing VEVENTS with RRULES seems broken. I think this is due to the fact that a) the ics standard is an utter mess and b) that python datatype handling is odd at best.

Nevertheless it is a start and I am confident it can be made to work maybe with the help of someone a little more experienced than I am myself.

The current state of the code can be found here: https://github.com/christf/etardb2ics/pull/1

jspricke commented 3 years ago

Hi, technically this has nothing to do with Etar, as it does not store any data but only views the what is provided by the calendar provider in Android, see the readme: https://github.com/Etar-Group/Etar-Calendar#technical-explanation. I'm ok with you discussing it over here but note that it is not a Etar bug, nor a feature we would add.

christf commented 3 years ago

Hi, technically this has nothing to do with Etar, as it does not store any data but only views the what is provided by the calendar provider in Android, see the readme: https://github.com/Etar-Group/Etar-Calendar#technical-explanation. I'm ok with you discussing it over here but note that it is not a Etar bug, nor a feature we would add.

Thank you for your input. I agree that tools like etardb2ics should remain separate from etar. I understand there is a feature request to do ics exports for the etar project as part of #553 and I am sharing this quick hack in the hope it is useful as part of the etar ecosystem.

The content of the technical explanation linked by you makes me think the tool might need a different name though...

john-999 commented 3 years ago

I second the requrest for the features:

"Export [all events] to .ics file"
"Import [multiple events] from .ics file"

hubbty commented 3 years ago

I have the same issue. It is possible to export data but not to restore data, so we're stuck. Please help!

Calendar Import-Export (Version 2.6) from F-Droid did the import trick for me. It inserted 91 entries from one ics file into Etar (org.lineage.etar, Version 10).

Etar-Group / Etar-Calendar

extract all ics events from db #806