Red
Red copied to clipboard
FCO
Migration?
https://github.com/FCO/Red/issues/7#issuecomment-473249348
Has a new idea that could be used to migrations...
I think migrations should be their own thing, especially since migrations are done at the Model level, not the collection one. Collection versioning is a great idea, though!
Maybe best explained, Migrations are an operation between two versions of the same model. So how would that best work? Maybe have something that encapsulates models like a collection, but it is NOT a collection of models, but of conversion operations. A big problem would be naming conventions.
Say we have two versions of model A. How would a migration of A:ver<1.2> to A:ver<1.3> be named?
Is something like this possible?
migration A:from<1.2>:to<1.3> { ... }
If so, I have an idea to do this in a very clean manner.
no, it isn't, I think...
what about:
migration A:ver<1.3> {
method from:ver<1.2> {...}
}
Actually, that's not a bad idea. However I was hoping to use methods for individual fields. In that case, field level conversions could use another mechanism.
Try this:
# Pseudo
migration A:ver<1.3> {
# Specific field-level conversions. Key layout is:
# <from_version><model><attribute> => -> $old_model, $to_model { ... }
has %!conversions;
has CollectionA<1.2> $!old_a;
has CollectionA<1.3> $!new_a;
method from:ver<1.2> {
# Iterate over each model.
for $!old_a.^models Z $!new_a.^models -> ($old_model, $new_model) {
for $new_model.^attributes -> $attr {
with %!conversion<1.2>{$new_model}{$attr} {
$_($old_model, $new_model)
} else {
# By default, we move the value from the old model to the new.
$new_model."&{ $attr }"() = $old_model."&{ $attr }"();
}
}
}
}
}
Note that usually DBMSs provide non-standard SQL syntaxes for migrations, like:
- CREATE TABLE IF NOT EXISTS
- DROP TABLE IF EXISTS
- CREATE OR REPLACE TABLE
- ALTER TABLE IF EXISTS
- ALTER TABLE t ADD COLUMN IF NOT EXISTS, DROP COLUMN IF EXISTS, etc
And some databases allow to run CREATE/ALTER/DROP in transactions.
I believe that it would be nice to make use of these features, where available. For example the migration itself could have an on_conflict property that could be "ignore" (if not exists), "replace" (or replace), "fail".
I were wondering about migration and I think I came to something interesting...
If I have 2 versions of the same model, for example:
model Bla:ver<0.1> {
has Int $.a is column;
}
model Bla:ver<0.2> {
has Str $.b is column;
}
It’s relatively easy to say that it should create a column b of type string and drop the column a. The problem is try to guess what should be done with the data... if the content of be shoul be generated based on the old data on a, we have a problem, once we dropped a.
We could fix that explaining to the migration how to generate the data. The other migrations that I know manipulate the data using plain SQL. But we already have a way to manipulate data! The AST!
I don’t think it would be impossible to make something like this to generate the data for a new column:
method #`{or sub, idk} migrate:<a> {
“String: { .a * 3 }”
}
And it would run a:
UPDATE
my_table
SET
b = ‘String: ‘ || a * 3
Or something that would be better for that migration (@santec, please help me!)
Maybe something should be a bit different because it is possible to a new column on a table can use data from different tables.
Sent with GitHawk
@FCO:
This is why I had the %!conversions attribute.
So for something like this situation, you'd have
submethod BUILD {
%!conversions<0.2><Bla><b> = -> { $new_model<Bla>.b = $old_model<Bla>.a };
}
So %!conversions handles all special casing at the field level.
Do we need the new model? Won’t we always use only the old one?
What about?
migration MySchema:ver:<0.2> {
has Bla:ver<0.1> $.old-model1;
has Ble:ver<0.1> $.old-model2;
has Bla:ver<0.2> $.new-model1;
has Ble:ver<0.2> $.new-model2;
method Bla:<a> { “{ $!old-model1.b } & { $!old-model2.c }” }
}
And it would use the return to create the update...
Sent with GitHawk
migration MySchema:ver:<0.2>[Bla:ver<0.2>, Ble:ver<0.2>] {
has Bla:ver<0.1> $.model1;
has Ble:ver<0.1> $.model2;
method Bla:<a> { “{ $!model1.b } & { $!model2.c }” }
}
Sent with GitHawk
migration MySchema:ver:<0.2>[Bla:ver<0.2>, Ble:ver<0.2>] {
Bla:ver<0.2>.a = “{ Bla:ver<0.1>.b } & { Bla:ver<0.1>.c }”
}
Sent with GitHawk
migration MySchema:ver:<0.2>[Bla:ver<0.2>, Ble:ver<0.2>] {
method Bla:<a> { “{ Bla:ver<0.1>.b } & { Bla:ver<0.1>.c }” }
}
Sent with GitHawk
migration MySchema:ver:<0.2>[Bla:ver<0.2>, Ble:ver<0.2>] {
method Bla:<a> { “{ .Bla.b } & { .Bla.c }” }
}
and $_ is the old migration...
Now I see that it doesn't make sense to use another table here... I don't have a join here...
it should be done with relationship...
Now I think I got it!
Bla.^migration: :from<0.1>, {
.a = .b * 42 + 3;
.c = .d - .e;
}
I just started playing with migrations... now (since a185e2ff3e3557e34bdaf789a219bd1f1dc7b658) it's possible:
$ perl6 -Ilib -e '
use Red "experimental migrations";
model Bla {
has Int $.a is column;
}
model Ble {
has Int $.b is column;
}
Ble.^migration: {
.b = .a * 3
}
Ble.^migrate: :from(Bla);
Ble.^dump-migrations
'
b => (bla.a)::int * 3
please, pay attention on use Red "experimental migrations";...
Yes. That;'s not bad. It accomplishes the basics. However I would prefer if we did migration as a ClassHOW so that we can split complex operations up into encapsulated (self-contained) pieces.
You can still do it with this method, but everything has to be written out.
Give me a few days to think about ways alleviate this issue and if I find any, I will post.
The solution I started implementing do not handle:
- table rename
- model rename
- table population
- table truncation
- table deletion
Maybe a solution with a migration type with a collection of models could help with it...
(My next step is creating the migration models to save the state of a migration on the database...)
MacBook-Pro-de-Fernando:Red2 fernando$ perl6 -Ilib -MRed -e '
my $*RED-DB = database "SQLite";
my $*RED-DEBUG = True;
use Red::Migration::Migration;
use Red::Migration::Table;
use Red::Migration::Column;
Red::Migration::Table.^create-table;
Red::Migration::Column.^create-table;
say Red::Migration::Table.^create: |Red::Migration::Table.^migration-hash
'
SQL : CREATE TABLE red_migration_table(
id integer NOT NULL primary key AUTOINCREMENT,
name varchar(255) NOT NULL ,
version varchar(255) NOT NULL ,
created_at real NOT NULL ,
migration_id integer NULL references red_migration_version(id),
UNIQUE (name, version)
)
BIND: []
SQL : CREATE TABLE red_migration_column(
id integer NOT NULL primary key AUTOINCREMENT,
name varchar(255) NOT NULL ,
type varchar(255) NOT NULL ,
references_table varchar(255) NULL ,
references_column varchar(255) NULL ,
is_id integer NOT NULL ,
is_auto_increment integer NOT NULL ,
is_nullable integer NOT NULL ,
table_id integer NULL references red_migration_table(id)
)
BIND: []
SQL : INSERT INTO red_migration_table(
version,
created_at,
name
)
VALUES(
?,
?,
?
)
BIND: ["0", Instant.from-posix(<1643395558931/1058>, Bool::False), "red_migration_table"]
SQL : SELECT
red_migration_table.id , red_migration_table.name , red_migration_table.version , red_migration_table.created_at as "created-at", red_migration_table.migration_id as "migration-id"
FROM
red_migration_table
WHERE
_rowid_ = last_insert_rowid()
LIMIT 1
BIND: []
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
red_migration_column.table_id = ?
BIND: [1]
()
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
red_migration_column.table_id = ?
BIND: [1]
()
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
red_migration_column.table_id = ?
BIND: [1]
()
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
red_migration_column.table_id = ?
BIND: [1]
()
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
red_migration_column.table_id = ?
BIND: [1]
()
SQL : INSERT INTO red_migration_column(
is_auto_increment,
table_id,
is_nullable,
is_id,
type,
name
)
VALUES(
?,
?,
?,
?,
?,
?
)
BIND: [Bool::True, 1, Bool::False, Bool::True, "UInt", "id"]
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
_rowid_ = last_insert_rowid()
LIMIT 1
BIND: []
SQL : INSERT INTO red_migration_column(
is_auto_increment,
is_nullable,
table_id,
is_id,
name,
type
)
VALUES(
?,
?,
?,
?,
?,
?
)
BIND: [Bool::False, Bool::False, 1, Bool::False, "name", "Str"]
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
_rowid_ = last_insert_rowid()
LIMIT 1
BIND: []
SQL : INSERT INTO red_migration_column(
is_nullable,
table_id,
is_auto_increment,
is_id,
type,
name
)
VALUES(
?,
?,
?,
?,
?,
?
)
BIND: [Bool::False, 1, Bool::False, Bool::False, "Version", "version"]
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
_rowid_ = last_insert_rowid()
LIMIT 1
BIND: []
SQL : INSERT INTO red_migration_column(
name,
type,
is_id,
is_auto_increment,
is_nullable,
table_id
)
VALUES(
?,
?,
?,
?,
?,
?
)
BIND: ["created_at", "Instant", Bool::False, Bool::False, Bool::False, 1]
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
_rowid_ = last_insert_rowid()
LIMIT 1
BIND: []
SQL : INSERT INTO red_migration_column(
is_auto_increment,
references_table,
is_nullable,
table_id,
is_id,
name,
type
)
VALUES(
?,
?,
?,
?,
?,
?,
?
)
BIND: [Bool::False, "red_migration_version", Bool::True, 1, Bool::False, "migration_id", "UInt"]
SQL : SELECT
red_migration_column.id , red_migration_column.name , red_migration_column.type , red_migration_column.references_table as "references-table", red_migration_column.references_column as "references-column", red_migration_column.is_id as "is-id", red_migration_column.is_auto_increment as "is-auto-increment", red_migration_column.is_nullable as "is-nullable", red_migration_column.table_id as "table-id"
FROM
red_migration_column
WHERE
_rowid_ = last_insert_rowid()
LIMIT 1
BIND: []
Red::Migration::Table.new(name => "red_migration_table", version => "0", created-at => 1553303967.936673e0, migration-id => Any)
I’ve been thinking about migration and I agree with something I’ve read somewhere that says a migrations should be split into 5 parts:
- create new columns as nullable
- change the code to handle the new column or if null the old one (or the opposite)
- populate the new columns and unnulable those
- change the code, remove the handle of the old columns
- remove the old columns
So, the idea would be make Red Migration run each of this steps. This is what I thought:
model User { # please, do not do that!!!
has Str $.nick is column;
has Str $.plain-password is column;
...;
method save-password(Str $new) {
$.plain-password = $new;
$.^save
}
method auth(Str $nick, Str $pass) {
::?CLASS.^all.grep: { .nick eq $nick AND .plain-password eq $pass }
}
}
This is a website that stores it’s users password in plain text... so it would like to change it to hash it’s password...
model User { # please, do not do that!!!
has Str $.nick is column;
has Str $.hashed-password is column;
has Bool $.expired is column;
...;
method save-password(Str $new) {
if %*RED-MIGRATION<0.1.2> <= BEFORE-START {
$.plain-password = $new;
} else {
$.hashed-password = hash-pass $new;
$.expired = True;
}
$.^save
}
method auth(Str $nick, Str $pass is copy) {
$pass = hash-pass $pass if %*RED-MIGRATION<0.1.2> > CREATED-COLUMNS;
my $user = ::?CLASS.^all.grep: { .nick eq $nick AND .plain-password eq $pass };
throw “change password” if %*RED-MIGRATION<0.1.2> > CREATED-COLUMNS and &user.expired;
$user
}
}
And on the migration file:
User.^migration: {
.expired = True
}
So Red would see that it should create 2 new columns (hashed-password and expired) and create it as nullable. Before that %*RED-MIGRATION<0.1.2> would contain BEFORE-START (assuming this is the version of this migration) after that, it would return CREATED-COLUMNS. After that it would populate the new columns with the new value (in this case True on expired column for every row) and %*RED-MIGRATION<0.1.2> will return POPULATED-COLUMNSnow it can delete the old columns and set DELETED-COLUMNS on the hash.
It step is controlled by a time stamp on the database showing where it should run the next step (and what step it is now, this is from where the value of the hash is gotten).
This time stamp can have a default value changed automatically by Red or manually changed by a Red cli.
I really want to know what you think... please leave a comment!
The only problem that I see is that the model would need to contain interim attributes until the migration is complete.
From I am reading (please pardon me if my interpretation is incorrect), but it look slike User.auth has transition code in place.
I don't think it is good practice to have transition code in place. If you are going to do a migration, it should be a discrete operation, and models should be version to handle prior-migration and post-migration state.
But what about the code to handle the 2 versions of the database in the middle?
In my head, after that migration is done, you would commit a new version of your code removing the migration code...
But what about the code to handle the 2 versions of the database in the middle?
That would only be a factor during the migration. During this time, I would expect production code to be offline for the upgrade.
Do you know of any situations where this may not be the case?
In my head, after that migration is done, you would commit a new version of your code removing the migration code...
Ah. OK. In my head ( :smile: ) I would have such code already written in a branch on the development systems. So there would be the current production, with the old version of the models, and the development version with the next version of the models.
Having code that handles two versions at once may be useful, but can be confusing for devops, at the very least. It also invites bugs that can inadvertently corrupt the data.
Are you suggesting to have downtime just to run a migration? We should find a way to run the migration without downtime...