Copy blob data between tables simply?

[Fangbeast]

I'm adding the ability to convert an old Keeper v3 database to the new MyInfo format. Keeper had an Attachments table (as does MyInfo) linked to a record via the record's id in the MyInfo table. It is a BLOB type.

Do I manually have to read the blob into a buffer and then write it to the new table from the buffer or can a blob field be copied much simpler than that between tables?

If nobody knows, I will use the old way.

Just to make your eyes bleed, here is the bulk of my conversion code (With lots of custom stuff in it obviously)

Code: Select all

Procedure ConvertDatabaseNow()
  
  Structure RecordNumbers
    OldRecordNumber.s
    NewRecordNumber.s
  EndStructure
  
  NewList LinkedRecord.s()
  
  If  Program\AttachedDatabase  <>  #Empty$
    
    SetInfoBarArea("Headings", "Info", "Starting to convert and import the attached database, please wait a while", #PB_Compiler_Procedure)
    
    AttachedDatabaseQuery.s           + 
    "SELECT * FROM RemoteDatabase "   + 
    "ORDER BY Title "                 + 
    "ASC"
    
    If DatabaseQuery(Program\DatabaseHandle, AttachedDatabaseQuery.s) <>  #DatabaseQueryFail
      
      While NextDatabaseRow(Program\DatabaseHandle)
      
        AttachedDatabaseQuery.s = #Empty$
        
        MyInfo\Title        = KillQuote(GetDatabaseString(Program\DatabaseHandle,  0))                 ;
        MyInfo\Information  = KillQuote(GetDatabaseString(Program\DatabaseHandle,  1))                 ;
        MyInfo\Category     = KillQuote(GetDatabaseString(Program\DatabaseHandle,  2))                 ;
        MyInfo\Archived     =           GetDatabaseString(Program\DatabaseHandle,  3)                  ;
        MyInfo\Favourite    =           GetDatabaseString(Program\DatabaseHandle,  4)                  ;
        MyInfo\Locked       =           GetDatabaseString(Program\DatabaseHandle,  5)                  ;
        MyInfo\Deleted      =           GetDatabaseString(Program\DatabaseHandle,  6)                  ;
        MyInfo\Updated      =           GetDatabaseString(Program\DatabaseHandle,  7)                  ;
        MyInfo\Owner        = KillQuote(GetDatabaseString(Program\DatabaseHandle,  8))                 ;
        MyInfo\Attachments  =           GetDatabaseString(Program\DatabaseHandle,  9)                  ;
        MyInfo\Recordid     =           GetDatabaseString(Program\DatabaseHandle, 10)                  ;
        
        MyInfoInsertString.s                          + 
        "INSERT INTO MyInfo("                         + 
        "Title, "                                     + 
        "Information, "                               + 
        "Category, "                                  + 
        "Archived, "                                  + 
        "Favourite, "                                 + 
        "Locked, "                                    + 
        "Deleted, "                                   + 
        "Updated, "                                   + 
        "Owner, "                                     + 
        "Attachments) "                               + 
        "VALUES("                                     + 
        "'"   +   RepQuote(MyInfo\Title)       + "'"  + 
        ", '" +   RepQuote(MyInfo\Information) + "'"  + 
        ", '" +   RepQuote(MyInfo\Category)    + "'"  + 
        ", '" +   RepQuote(MyInfo\Archived)    + "'"  + 
        ", '" +   RepQuote(MyInfo\Favourite)   + "'"  + 
        ", '" +   RepQuote(MyInfo\Locked)      + "'"  + 
        ", '" +   RepQuote(MyInfo\Deleted)     + "'"  + 
        ", '" +   RepQuote(MyInfo\Updated)     + "'"  + 
        ", '" +   RepQuote(MyInfo\Owner)       + "'"  + 
        ", '" +   "0"                          + "'"  + 
        ")"
        
        If DatabaseUpdate(Program\DatabaseHandle, MyInfoInsertString.s)  <> #DatabaseUpdateFail
          NewRecordid.s = DatabaseLastInsertRowId()
          If NewRecordId.s <> #Empty$
            AddGadgetItem(#Gadget_Convert_Titles, #AtTheEndOfTheList, KillQuote(MyInfo\Title) + #LF$  + "Added"  + #LF$  + NewRecordId.s)
            LastLine(#Gadget_Convert_Titles, NewNumberOfItems.i - 1)
            If MyInfo\Attachments <> #Empty$
              AddElement(LinkedRecord.s())
              LinkedRecord.s()\OldRecordNumber  = MyInfo\Recordid
              LinkedRecord.s()\NewRecordNumber  = NewRecordId.s
            EndIf
            If Toggle\MessageSave = #True
              MessageRequesterEBS("New Item", "New item: " + KillQuote(MyInfo\Title)  + " has been saved to the database", #MB_ICONINFORMATION | #PB_MessageRequester_Ok)
              ;MessageRequester("New Item", "New item: " + KillQuote(MyInfo\Title)  + " has been saved to the database", #PB_MessageRequester_Ok)
            EndIf
          Else
            SetInfoBarArea("Headings",  "Error",  "No record number created " + DatabaseError(), #PB_Compiler_Procedure)
          EndIf
        Else
          SetInfoBarArea("Headings", "Error", "Database table failed to be updated " + DatabaseError(), #PB_Compiler_Procedure)
        EndIf
        
        ; Update the category table now
        
        NewCategoryInsert.s         + 
        "INSERT INTO Categories("   + 
        "Category, "                + 
        "ItemCount, "               + 
        "Iconname) "                + 
        "VALUES("                   + 
        "'"                         + 
        RepQuote(MyInfo\Category)   + 
        "', '"                      + 
        "0"                         + 
        "', '"                      + 
        #Empty$                     + 
        "')"
        If DatabaseUpdate(Program\DatabaseHandle, NewCategoryInsert.s)  <> #DatabaseUpdateFail
          UpdateCategoryItemCount(MyInfo\Category)
        Else
          SetInfoBarArea("Headings", "Error", "Database table 'Categories' failed to be updated " + DatabaseError(), #PB_Compiler_Procedure)
        EndIf
        
        ; Update the Owners table now
        
        NewOwnerInsert.s            + 
        "INSERT INTO Owners("       + 
        "Owner, "                   + 
        "Iconname, "                + 
        "Itemcount) "               + 
        "VALUES("                   + 
        "'"                         + 
        RepQuote(MyInfo\Owner       + 
        "', "                       + 
        "'"                         + 
        #Empty$                     + 
        "', "                       + 
        "'0')"
        
        If DatabaseUpdate(Program\DatabaseHandle, NewOwnerInsert.s)  <> #DatabaseUpdateFail
          UpdateOwnerItemCount(MyInfo\Owner)
        Else
          SetInfoBarArea("Headings", "Error", "Database table 'Owners' failed to be updated " + DatabaseError(), #PB_Compiler_Procedure)
        EndIf
        
        ; Update the attachments table now
        
        ForEach LinkedRecord.s()
          ; Grab the old blob from attachments table, save it to new table linking it to the new 
          ; Record number by knowing the old record number
        Next
        
        ; Clean out all the variables
        
        AttachedDatabaseQuery.s = #Empty$
        MyInfoInsertString.s    = #Empty$
        NewCategoryInsert.s     = #Empty$
        NewOwnerInsert.s        = #Empty$
        
        MyInfo\Title            = #Empty$
        MyInfo\Information      = #Empty$
        MyInfo\Category         = #Empty$
        MyInfo\Archived         = #Empty$
        MyInfo\Favourite        = #Empty$
        MyInfo\Locked           = #Empty$
        MyInfo\Deleted          = #Empty$
        MyInfo\Updated          = #Empty$
        MyInfo\Owner            = #Empty$
        MyInfo\Recordid         = #Empty$
        
      Wend
    
      FinishDatabaseQuery(Program\DatabaseHandle)
      
    Else
      SetInfoBarArea("Headings", "Error", "The database query failed or was empty", #PB_Compiler_Procedure)
    EndIf
    
  Else
    SetInfoBarArea("Headings", "Error", "There is currently no attached database selected for conversion", #PB_Compiler_Procedure)
    MessageRequesterEBS("No database attached", "There is currently no attached database selected for conversion", #MB_ICONINFORMATION | #PB_MessageRequester_Ok)
  EndIf
  
EndProcedure

[spikey]

I've never tried this with blobs so I can't say for certain it will work but my first attempt would be:

ATTACH DATABASE the existing database to the new database: https://www.sqlite.org/lang_attach.html

You can then use an INSERT SELECT to get the database engine to copy the blobs over for you.
https://www.sqlite.org/lang_insert.html

Something like:

Code: Select all

INSERT INTO NewDatabase.BlobTable SELECT * FROM OldDatabase.BlobTable;

Adjusting the field specs as normal for INSERT and SELECT instead of using * if the table schemas don't match exactly. The number and type of columns generated by the SELECT clause must exactly match the number and type of columns expected when they reach the INSERT clause, or the query will fail. CAST if necessary. https://www.sqlite.org/lang_expr.html#castexpr

It will still take time to perform the migration based on the blob sizes but should save some overhead time because the blob data won't be passing over the PureBasic/SQLite interface, the migration will be handled exclusively by the database engine.

If your schemas/datatypes are roughly analogous you could use the same technique to perform the whole conversion if you design the queries properly...

DETACH DATABASE when finished to avoid any ambiguous schema name problems before performing any further actions.
https://www.sqlite.org/lang_detach.html

However, this method won't allow you to allocate any new ID numbers as you go. If this is significant then this won't be suitable.

[Fangbeast]

However, this method won't allow you to allocate any new ID numbers as you go. If this is significant then this won't be suitable.

Hmm, that will be a problem as the new database may already have entries and these will be appended to it.

As for attach and detach, they are setup on the master form that controls the foreign database selection from the user.

Ah well, it was a good idea. That select into clause I have used in the past, it's very useful.

I did this via a temporary table to make sure the import worked. (Lots of debug statements everywhere)

Create table Keeper_Temp(Title TEXT,Record INTEGER PRIMARY KEY AUTOINCREMENT);
INSERT INTO Keeper_Temp (Title) Select Title FROM Keeper;
DROP TABLE Keeper;
ALTER TABLE Keeper_Temp RENAME To Keeper

Etc, etc

[skywalk]

I'm a bit confused by,

this method won't allow you to allocate any new ID numbers as you go.

...Doesn't it depend on your source/target DB schema's? INSERT's to a ROWID table should increment ROWID and append the row's worth of data. UPDATE's will overwrite an existing row's worth of data. I use ATTACH to INSERT a SELECT result INTO another ROWID table and get automatic ROWID increments.
The trick is to pass 'null' for ROWID in the SELECT result.
Ex.

Code: Select all

INSERT INTO dbfile.Table1 SELECT null,* FROM dbattached.Table1 WHERE `whatever`=42;

[spikey]

I'm a bit confused by,

this method won't allow you to allocate any new ID numbers as you go.

...

Yes, I struggled to express what I really meant there - sorry. The point I was trying to make was that the method imposes some restrictions.

Fangbeast gives one of the potential problems - existing contents in the destination table may overlap by ID with imported ones, this would necessitate the allocation of new IDs for the imported rows and a retro fit to other tables in the database to keep everything in sync...

They can be overcome, but I decided not to get into all of that...

[Fangbeast]

Fangbeast gives one of the potential problems - existing contents in the destination table may overlap by ID with imported ones, this would necessitate the allocation of new IDs for the imported rows and a retro fit to other tables in the database to keep everything in sync...

Easily fixed. During the query of the original table, I save the old record id and the new record id to a linked list. When I process the attachments table, the linked list is used as a linked record lookup in the attachments table and when at attachment is saved, the AttachmentId is used as a linked Foreign Key to the new recordid of the linked MyInfo record.

"FOREIGN KEY(Attachmentid) REFERENCES MyInfo(Recordid))"

Works.