Discussion: Backup & Restore

Backup/Restore/Archiving

This is more of a stream of consciousness. Skip to the end for more refined/recent thoughts. Read the whole thing if you want to understand the reasons.

Initial Thoughts

1. We need an 'export' format that contains all data in a single file
2. the format should be version-independent (so we can backup in 4.1 and have a decent chance of restoring in 4.0)
3. The file need to contain both data and cover images

Initial Conclusions

• Use an sqlite database
• it's OK to use the 'current' database format; but if we do then we need a very simple database layer that provides a cursor for each object type, including covers, and a list of object types. This layer would need updating with each database change

Initial Plan

• restructure 'CatalogueDBAdapter' to have a new version for each new database version.
• opening a database file will do one of two things depending on parameters passed:
  1. Upgrade to the current format (this is the standard current behaviour)
  2. Return an 'archive' interface that gives access to object cursors as described above

Implementation

We can use 'pragma user_version' in the same way DBHelper uses it: we can check the DB version, and then either upgrade as normal, or return the correct 'BookCatalogueArchive' implementation for that version.

We would need to add some tables to the database:

• objects (id int, name text)
• object_queries (id int, object_id int, queryToRetrieve text)

BookCatalogueArchive would allow any object from the 'objects' table to be retrieved using the query defined in 'object_queries'.

eg.

object(BookCatalogueArchive.BOOKS_ID, 'book');
object_queries(1, BookCatalogueArchive.BOOKS_ID, 'select * from books');
object(BookCatalogueArchive.AUTHORS_ID, 'author');
object_queries(1, BookCatalogueArchive.AUTHORS_ID, 'select * from books');

...etc.

We could then call:

Cursor c = BookCatalogueArchive.getObjectCursor(BookCatalogueArchive.BOOKS_ID);

...WORK IN PROGRESS...

Need to consider sub-objects. How to get book-authors. Perhaps:

Cursor c = BookCatalogueArchive.getSubObjectCursor(BookCatalogueArchive.BOOKS_ID, BookCatalogueArchive.AUTHORS_ID);

...to return a list of full author details from the archive. This would include author names as well as IDs.

The basic idea is to support the flexibility we have with 'export.csv' -- no fixed IDs, no knowledge of structure beyond the obvious.

To achieve this we would probably need some more tables, OR just a BookCatalogueArchive implementation that is version specific (though easily subclassed).

eg.

BookCatalogueArchive (Interface) public Cursor getObjects(id int); public Cursor getSubObjects(id int, id int);

BookCatalogueArchive40 (version 4.0 Implementation -- would work for 4.1 as well)
   public Cursor getObjects(id int) {
        switch(id) {
            case BOOKS_ID:
                return getBooks();
            ...
            case else:
                return null; // Unknown object type
        }
   }

   private Cursor getBooks() {
       return newCursor("select * from books");
   }

   public Cursor getSubObjects(id int, curr Cursor, sub int) {
        switch(id) {
            case BOOKS_ID:
                return getBooksSubObject();
            ...
            case else:
                return null; // Unknown object type
        }
   }

   private Cursor getBooksSubObjects(curr Cursor, sub int) {
        switch(id) {
            case AUTHORS_ID:
                int bookId = curr.getLong("_id"); // get the current book ID
                return newCursor("Select a.* from authors a, book_author ba where...") ;
            ...
            case else:
                return null; // Unknown object type
        }
   }

while this approach would seem to work it still does not isolate us from field name changes very well. eg. simply changing the 'given_names' field to 'names_other' would break prior importers.

Which leads to the view we need to encapsulate each cursor as well.

We define certain objects like a 'booksCursor'. The 'booksCursor' implements getAuthors() method and the bookAuthors cursor implements a 'given_names' getter, and potentially later, a 'names_other' getter. Because given_names will always be implemented, old versions will always be able to import the file. Newer versions will always have access to their preferred representation.

Downsides

• May be a maintenance nightmare. Not sure. Once the initial version is written...it won't change at a huge rate.
• lots of really boring code to write. Probably worth automating.

Example: changing or adding a field name

• do all the same current steps

  1. change/add the constant value
  2. create an upgrade script
  3. update all related queries

• new steps

  1. subclass the existing archive cursor object
  2. add a new getter (for new name)
  3. (if a changed name) override old getter to use new field name
  4. update 'restore' code to use new field name if present, otherwise use oldname...HMMMM...don't reallt want to use reflection....so maybe back to plan A...no more encapsulation

Don't encapsulate

Back to this (from above):

BookCatalogueArchive (Interface)
   public Cursor getObjects(id int);
   public Cursor getSubObjects(id int, id int);

BookCatalogueArchive40 (version 4.0 Implementation -- would work for 4.1 as well)
   public Cursor getObjects(id int) {
        switch(id) {
            case BOOKS_ID:
                return getBooks();
            ...
            case else:
                return null; // Unknown object type
        }
   }

   private Cursor getBooks() {
       return newCursor("select * from books");
   }

   public Cursor getSubObjects(id int, curr Cursor, sub int) {
        switch(id) {
            case BOOKS_ID:
                return getBooksSubObject();
            ...
            case else:
                return null; // Unknown object type
        }
   }

   private Cursor getBooksSubObjects(curr Cursor, sub int) {
        switch(id) {
            case AUTHORS_ID:
                int bookId = curr.getLong("_id"); // get the current book ID
                return newCursor("Select a.* from authors a, book_author ba where...") ;
            ...
            case else:
                return null; // Unknown object type
        }
   }

and then we need to make sure that the new cursor also returns old field names. eg.

select ..., newName as oldName, newName as newName, ...

then...old import code should cope, and new import code will work as expected.

Example: changing or adding a field name

• do all the same current steps

  1. change/add the constant value
  2. create an upgrade script
  3. update all related queries

• new steps

  1. add duplicate fields with different names for renamed fields
  2. update 'restore' code to use new field name if present, otherwise use oldname

Refined Approach

Keep it simple

• Use existing 'export.csv' as a model
• 'book' is the basic currency
• books can have a bunch of data fields, dependant on version
• a book has a cover image
• books can have one or more:
  • authors
  • series
  • bookshelves
  • anthology titles

Interface

Archive Object
    new(DBHelper db, String location) // Copies the DB to location and loads the cover table from the images
    Archive.BooksCursor getBooks() // returns an Archive.BooksCursor (distinct from the existing 'BooksCursor')

Archive.BooksCursor Interface extends Cursor
    Archive.AuthorsCursor getAuthors() // Returns a cursor of authors (first name, last name)
    Archive.SeriesCursor getSeries() // Returns a cursor of series (name, position)
    Archive.BookshelvesCursor getBookshelves() // returns a cursor of bookshelves (name)
    Archive.AnthologyTitlesCursor getAnthologyTitles() // returns a cursor of anthology titles (name)
    Archive.CoverCursor getCover(); // Returns the blob cursor for the cover of the current book

Archive.BooksCursor40 implements Archive.BooksCursor extends Cursor...

Implementation

First thoughts:

• copy existing DB
• add a covers table
• store image files as blobs (optional)
• require each DB update to build a compatible loader (see interfaces above)