CODEWITHSUNDEEP
sqlsqlite
sashoalm
sashoalm

Reputation: 79615

Move blob field into new table (id, blob), and replace it with id from new table

I have a BLOB field that contains images of 1-2 MBs. I want to create a new table that has only 2 fields - a primary key with a reference ID and the BLOB, then replace the field that holds the BLOB with a field that holds the reference ID for the same BLOB in the new table.

I don't know SQL very well, I'm not even sure if this is possible using only SQL. Or do I need to make a C++/Python program to do it?

Note: I'm using SQLite for the database, and since they don't enforce types for the fields, I don't even need to create a new field, I can just replace the BLOB with the ID.

Upvotes: 1

Views: 318

Answers (1)

CL.
CL.

Reputation: 180172

You have to copy all blobs to the other table, and update the old field with the ID of the new row. The latter is possible with last_insert_rowid(), but only for a single row, so you have to use a mechanism that does the inserts and updates step by step.

This can be done with a trigger (and a dummy view, so that the UPDATE that triggers the copying does not actually update a table):

CREATE TABLE NewTable (ID INTEGER PRIMARY KEY, Blob);

CREATE TEMPORARY VIEW OldTable_view AS
SELECT * FROM OldTable;

CREATE TEMPORARY TRIGGER move_blob
INSTEAD OF UPDATE ON OldTable_view
BEGIN
    INSERT INTO NewTable(Blob)
    SELECT Blob FROM OldTable WHERE ID = OLD.ID;

    UPDATE OldTable
    SET Blob = last_insert_rowid()
    WHERE ID = OLD.ID;
END;

UPDATE OldTable_view SET ID = ID;

DROP VIEW OldTable_view;

Upvotes: 1

Related Questions