What is the correct way to rename a table/field without loosing data?

chula · 2014-07-24 09:05:50

Renaming table/field name in Object definition just create new table/field. Changing field type, say change Integer to RawUTF8, gave nothing happen at all.

What is the correct way to do this? I read from other post someone execute "Alter Table" before CreateMissingTables(). Is it the correct way? Thanks!

Last edited by chula (2014-07-24 09:08:44)

edwinsn · 2014-07-24 09:24:12

If you use SQLite as the backend databae, SQLite itself doesn't support renaming a field.

Assume you have TSQLOldTable, now create a new class called TSQLNewTable with the new field definitions, call CreateMissingTables(); Then read old records from TSQLOldTable and add to TSQLNewTable;

drop tale TSQLOldTable.

Then change all references to TSQLOldTable in your code to TSQLNewTable.

chula · 2014-07-25 02:40:25

edwinsn wrote:

If you use SQLite as the backend databae, SQLite itself doesn't support renaming a field.
Assume you have TSQLOldTable, now create a new class called TSQLNewTable with the new field definitions, call CreateMissingTables(); Then read old records from TSQLOldTable and add to TSQLNewTable;
drop tale TSQLOldTable.
Then change all references to TSQLOldTable in your code to TSQLNewTable.

Very thanks!

As I am new to the framework, do I have to copy the table by looping read/write progress, or there is a more effective way provided from the framework?

ab · 2014-07-25 06:27:28

This is a sqlite limitation.

chula · 2014-07-26 02:43:55

ab wrote:

This is a sqlite limitation.

I see. Thanks!

edwinsn · 2014-07-26 05:41:04

chula wrote:

edwinsn wrote:
If you use SQLite as the backend databae, SQLite itself doesn't support renaming a field.
Assume you have TSQLOldTable, now create a new class called TSQLNewTable with the new field definitions, call CreateMissingTables(); Then read old records from TSQLOldTable and add to TSQLNewTable;
drop tale TSQLOldTable.
Then change all references to TSQLOldTable in your code to TSQLNewTable.

Very thanks!
As I am new to the framework, do I have to copy the table by looping read/write progress, or there is a more effective way provided from the framework?

Maybe execute a raw "insert into...select" SQL statement using EngineExecute() is faster and less of a hassle. Just an idea, I don't remember the details, please google yourself.

ab · 2014-07-26 11:17:58

Yes, SQLite3 has the INSERT INTO .... SELECT syntax.
See http://www.sqlite.org/lang_insert.html

You have also the CREATE TABLE .. AS SELECT statement.
See http://www.sqlite.org/lang_createtable.html

Then DROP the previous table, and run ALTER TABLE ... RENAME TO ... to replace the previous table.

It should be faster to use those kind of syntax.
But only for Sqlite3 backend.

If you use external databases, pure CRUD / ORM within a BATCH process (and a transaction) is preferred.
And could be only slightly slower.

Ensure you create the TRIGGERs and INDEXs after having populated the new database.
It is most of the time faster to do so.

mORMot Open Source

#1 2014-07-24 09:05:50

What is the correct way to rename a table/field without loosing data?

#2 2014-07-24 09:24:12

Re: What is the correct way to rename a table/field without loosing data?

#3 2014-07-25 02:40:25

Re: What is the correct way to rename a table/field without loosing data?

#4 2014-07-25 06:27:28

Re: What is the correct way to rename a table/field without loosing data?

#5 2014-07-26 02:43:55

Re: What is the correct way to rename a table/field without loosing data?

#6 2014-07-26 05:41:04

Re: What is the correct way to rename a table/field without loosing data?

#7 2014-07-26 11:17:58

Re: What is the correct way to rename a table/field without loosing data?

Board footer