Migrate table by hand

edismo · 2013-10-16 20:05:10

Hi

I create my TSQLRestClientDB in initialization and Server.CreateMissingTables(0) is executed in Create.

I need migrate a table to rename/alter type/remove a field, but without losing data.
Before of CreateMissignTables i need execute:

alter table SAMPLE rename to SAMPLECOPY

And after of CreateMissingTables i need execute

insert into SAMPLE select field1, field3 from SAMPLECOPY
drop table SAMPLECOPY

I tryed to use those methods EngineExecute(), ExecuteList(), DB.ExecuteAll(), DB.Execute(), DB.ExecuteNoException(), but no results.

Can help me?

ab · 2013-10-16 22:35:10

Did you try in syndbexplorer?

edismo · 2013-10-17 12:47:07

Yes, run...
But my application must be able to know migrate alone. User does not know.
I really need to use the application to do this without any external tool.

Sorry, title "migrate by hand", better "migrate automatic", but code by hand and not automatic by mORMot.

ab · 2013-10-17 14:41:19

What are the error messages?

Do all you low-level DB stuff before launching the ORM kernel.
The ORM prepare some queries about tables at startup.
Perhaps some operations about the table won't work as expected, since there are prepared requests in the ORM kernel cache.
It is the same as "vacuum": do it before, directly with SQLite3.

edismo · 2013-10-17 18:00:38

The errors were returned: 400 - "Bad Request" and 1 - "no such table: SampleCopy".

I tryed create new instance of TSQLDataBase and run method execute('...rename...') before launching the ORM kernel, and running ok.
After Server.CreateMissingTables(0) tryed UnLock and run method execute('insert...'), but ocurred "database is locked".

Without CreateMissingTables(0) I can create table with erros.
And mORMot know create tables with types and collations correct better than me.

Is there any place where I can do this? Is it possible?

Thank AB

ab · 2013-10-17 18:27:24

Yes, SampleCopy should be part of the database model, if you want to access it from the ORM layer.
But here SampleCopy is just a temporary table, not a "true" database table...

We should handle directly by the ORM the table definition changes.
Only change handled by now is a new column (ALTER TABLE... ADD COLUMN).

Could you create a ticket for this feature request?

In the meanwhile, there is no possibility BUT to run the SQL commands by hand, before launching the ORM kernel.

edismo · 2013-10-17 18:46:24

Thank AB.

Created the tiket

martin.suer · 2013-12-15 12:36:53

Hi,

I think this is some kind of a more general topic.
Are there best practices about how to change an existing model?
I understand it's easy to add new TSQLRecord descendants and extend a model that way.

But when I need to add an additional property to an existing TSQLRecord descendant and at the same time want to continue using the already existing underlying db, how do I do that?

The same problem arises if I want to change a property name... or a Class name of a TSQLRecord descendant.

If I decide to write a migration program that could convert an existing model to a newer version of that model, other problems arise:
I can not have different versions of a model at the same time in the same program which would be necessary to read from the existing db and write to the converted db.

I am asking for the best general aproach of how to do such evolutionary conversions. Since changing a model without loosing existing data is a common scenario, how have you dealt with that in your own projects?

Changing the db manually? Would be possible I guess, but that would somehow be a real break of the ORM Idea. Is there some kind of way doing it in object pascal code? Spending quite some time with the documentation and the forum I haven't yet found anything specific to this topic.

kind regards
Martin

ab · 2013-12-15 14:31:39

Automatic db versioning is a feature on the roadmap.
See http://synopse.info/fossil/tktview?name=2438fac85c

Up to now, only adding a table or a field to the model is implemented.

mORMot Open Source

#1 2013-10-16 20:05:10

Migrate table by hand

#2 2013-10-16 22:35:10

Re: Migrate table by hand

#3 2013-10-17 12:47:07

Re: Migrate table by hand

#4 2013-10-17 14:41:19

Re: Migrate table by hand

#5 2013-10-17 18:00:38

Re: Migrate table by hand

#6 2013-10-17 18:27:24

Re: Migrate table by hand

#7 2013-10-17 18:46:24

Re: Migrate table by hand

#8 2013-12-15 12:36:53

Re: Migrate table by hand

#9 2013-12-15 14:31:39

Re: Migrate table by hand

Board footer