Thread: [Firebird-net-provider] Migrations

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hi *,

is there an interest in Migrations provider (for EF) for Firebird?

--
Jiri {x2} Cincura (x2develop.com founder)
http://blog.cincura.net/ | http://www.ID3renamer.com

Hi guys,

Small update on Migrations for EF. EF had regression in 6.x (https://entityframework.codeplex.com/workitem/2683) which is now fixed. But will be included in 6.2. So I'm holding it. I'll then merge the branch, process PRs and it goes out. I don't want to release it before, because there will be a lot of whining and I just don't want to go there (and if somebody really wants it the branch is public).

-- 
Mgr. Jiří Činčura
Independent IT Specialist

What is the current status of migrations? Last change was 8 month. Do you
plan to merge it to master?

We are using branch for almost half year in our project and can give some
feedback/fixes. And want to port them to upstream.

I do.

I’m still waiting for EF 6.2 as it contains some fixes we need for proper error reporting. It’s already fixed in sources, but not yet released. It works but people would complain and given how much stupid questions I get about DDEX, I’m not going to do that.

--
Mgr. Jiří Činčura
Independent IT Specialist

From: Геннадий Забула [mailto:zab...@gm...]
Sent: Saturday, August 29, 2015 5:18 PM
To: For users and developers of the Firebird .NET providers <fir...@li...>
Subject: [Firebird-net-provider] Migrations

What is the current status of migrations? Last change was 8 month. Do you plan to merge it to master?

We are using branch for almost half year in our project and can give some feedback/fixes. And want to port them to upstream.

Can it be merged to master and released as Beta so this won't be used by
everybody?

On Saturday, 29 August 2015, Jiří Činčura <ji...@ci...> wrote:

> I do.
>
>
>
> I’m still waiting for EF 6.2 as it contains some fixes we need for proper
> error reporting. It’s already fixed in sources, but not yet released. It
> works but people would complain and given how much stupid questions I get
> about DDEX, I’m not going to do that.
>
>
>
> --
>
> Mgr. Jiří Činčura
>
> Independent IT Specialist
>
>
>
> *From:* Геннадий Забула [mailto:zab...@gm...
> <javascript:_e(%7B%7D,'cvml','zab...@gm...');>]
> *Sent:* Saturday, August 29, 2015 5:18 PM
> *To:* For users and developers of the Firebird .NET providers <
> fir...@li...
> <javascript:_e(%7B%7D,'cvml','fir...@li...');>
> >
> *Subject:* [Firebird-net-provider] Migrations
>
>
>
> What is the current status of migrations? Last change was 8 month. Do you
> plan to merge it to master?
>
> We are using branch for almost half year in our project and can give some
> feedback/fixes. And want to port them to upstream.
>

Can it be merged to master and released as Beta so this won't be used by
everybody?

On Saturday, 29 August 2015, Jiří Činčura <ji...@ci...> wrote:

> I do.
>
>
>
> I’m still waiting for EF 6.2 as it contains some fixes we need for proper
> error reporting. It’s already fixed in sources, but not yet released. It
> works but people would complain and given how much stupid questions I get
> about DDEX, I’m not going to do that.
>
>
>
> --
>
> Mgr. Jiří Činčura
>
> Independent IT Specialist
>
>
>
> *From:* Геннадий Забула [mailto:zab...@gm...
> <javascript:_e(%7B%7D,'cvml','zab...@gm...');>]
> *Sent:* Saturday, August 29, 2015 5:18 PM
> *To:* For users and developers of the Firebird .NET providers <
> fir...@li...
> <javascript:_e(%7B%7D,'cvml','fir...@li...');>
> >
> *Subject:* [Firebird-net-provider] Migrations
>
>
>
> What is the current status of migrations? Last change was 8 month. Do you
> plan to merge it to master?
>
> We are using branch for almost half year in our project and can give some
> feedback/fixes. And want to port them to upstream.
>

> Can it be merged to master and released as Beta so this won't be used by everybody?

Probably. It would be nice to know when EF6.2 will go out, so we know how long is going to be in beta state. For me it doesn't make much sense to have it in beta for say 5+ months.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

Also if you have some feedback you can discuss it here ahead of time. No problem with that.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

Ok. For now we had such issues:
1. Long names of tables causes foreign key script creation fail with error "Name exceeded implementation limitation". This caused by 31 characters limitation to almost anything not only FK. We crutched this with hashing names   with base64 convert. Ugly but works.
2. Second it is drop from support databases with pages less than 16384 because of another impl. limitation for max string field length. This breaks creating MigrationHistory table. We didn't fixed this, because started using separate database. But in near future our previous database would also need to have migrations. I think this can be crutched to use blob field only for Model field of this table.
3. Issue with sequence generators. Maybe you remember discussion about this on github. Looks like I've found good place to configurate particular field. It is in OnModelCreating Properties().Configure(x=>x.AddAnotation()) for property. There user can specify what sequence behavior does he need or provide custom.

Today I tried to add tests for all this to repo but found out that we need to add separate test infrastructure for EF tests.

-----Original Message-----
From: "Jiří Činčura" <ji...@ci...>
Sent: ‎8/‎29/‎2015 7:29 PM
To: "For users and developers of the Firebird .NET providers" <fir...@li...>
Subject: Re: [Firebird-net-provider] Migrations

Also if you have some feedback you can discuss it here ahead of time. No problem with that.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

------------------------------------------------------------------------------
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

> 1. Long names of tables causes foreign key script creation fail with error "Name exceeded implementation limitation". This caused by 31 characters limitation to almost anything not only FK. We crutched this with hashing names   with base64 convert. Ugly but works.

Maybe we can, in case it's longer than 31 characters, simply use GUID. Not nice either, though.

> 2. Second it is drop from support databases with pages less than 16384 because of another impl. limitation for max string field length. This breaks creating MigrationHistory table. We didn't fixed this, because started using separate database. But in near future our previous database would also need to have migrations. I think this can be crutched to use blob field only for Model field of this table.

You mean the PK on MigrationHistory? We can specify 16k page size as a requirement. There's not much to invent on the PK.

> 3. Issue with sequence generators. Maybe you remember discussion about this on github. Looks like I've found good place to configurate particular field. It is in OnModelCreating Properties().Configure(x=>x.AddAnotation()) for property. There user can specify what sequence behavior does he need or provide custom.

True. And with custom convention it's not even needed to specify it explicitly for every column.

> Today I tried to add tests for all this to repo but found out that we need to add separate test infrastructure for EF tests.

There's few tests I wrote. Using CF as that's easy to set up. But maybe the migrations would need deeper setup.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

1. I've used hashes from names to avoid dictionaries for mapping name-guid.
2. I think it was about Model field. More precise error I'll tell tomorrow when PC will be available. I don't want to constraint page size, because changing page size for existing databases is done only via backup-restore AFAIK. In case of several GB dbs it's not an option.
3. Yes. Default behavior maybe several pre defined and possibility to provide custom.
4. One more issue I remembered. Dbs created with Initial migration and via CreateDatabaseIfNotExists have different underlying scheme in part of names. I think this should be also fixed.

-----Original Message-----
From: "Jiří Činčura" <ji...@ci...>
Sent: ‎8/‎29/‎2015 10:08 PM
To: "For users and developers of the Firebird .NET providers" <fir...@li...>
Subject: Re: [Firebird-net-provider] Migrations

> 1. Long names of tables causes foreign key script creation fail with error "Name exceeded implementation limitation". This caused by 31 characters limitation to almost anything not only FK. We crutched this with hashing names   with base64 convert. Ugly but works.

Maybe we can, in case it's longer than 31 characters, simply use GUID. Not nice either, though.

> 2. Second it is drop from support databases with pages less than 16384 because of another impl. limitation for max string field length. This breaks creating MigrationHistory table. We didn't fixed this, because started using separate database. But in near future our previous database would also need to have migrations. I think this can be crutched to use blob field only for Model field of this table.

You mean the PK on MigrationHistory? We can specify 16k page size as a requirement. There's not much to invent on the PK.

> 3. Issue with sequence generators. Maybe you remember discussion about this on github. Looks like I've found good place to configurate particular field. It is in OnModelCreating Properties().Configure(x=>x.AddAnotation()) for property. There user can specify what sequence behavior does he need or provide custom.

True. And with custom convention it's not even needed to specify it explicitly for every column.

> Today I tried to add tests for all this to repo but found out that we need to add separate test infrastructure for EF tests.

There's few tests I wrote. Using CF as that's easy to set up. But maybe the migrations would need deeper setup.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

------------------------------------------------------------------------------
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

1. I think table names need to be preserved. Developer is reponsible for checking for that constraint. The name is defined. But I meant generated names, like PK name (if not specified). It's same for column names etc. It would be pretty confusing to later open that database and see just weird characters.
2. AFAIK you still need 16k database to be able to make PK (or maybe just 8k is enough, not 4k for sure).  
4. OK, that might be bug. 

-- 
Mgr. Jiří Činčura
Independent IT Specialist

2. According to this: https://msdn.microsoft.com/en-us/data/dn456841.aspx there might be problem with ContextKey field because it is PK. But there is a workaround that allows to decrease size of the field. Don't know why this field need to be so long.

Do I need to fill tracker issues for described problems to track progress?

-----Original Message-----
From: "Jiří Činčura" <ji...@ci...>
Sent: ‎8/‎30/‎2015 9:08 AM
To: "For users and developers of the Firebird .NET providers" <fir...@li...>
Subject: Re: [Firebird-net-provider] Migrations

1. I think table names need to be preserved. Developer is reponsible for checking for that constraint. The name is defined. But I meant generated names, like PK name (if not specified). It's same for column names etc. It would be pretty confusing to later open that database and see just weird characters.
2. AFAIK you still need 16k database to be able to make PK (or maybe just 8k is enough, not 4k for sure).  
4. OK, that might be bug. 

-- 
Mgr. Jiří Činčura
Independent IT Specialist
------------------------------------------------------------------------------
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

That’s what I’m talking about!

By default I would not make it shorter. The default value is expected and somebody might create namespace long enough to break it. When somebody needs it shorter, the history context is customizable. Half of the users don’t even want to think about it.

--
Mgr. Jiří Činčura
Independent IT Specialist

From: Геннадий Забула [mailto:zab...@gm...]
Sent: Sunday, August 30, 2015 11:16 AM
To: For users and developers of the Firebird .NET providers <fir...@li...>
Subject: Re: [Firebird-net-provider] Migrations

2. According to this: https://msdn.microsoft.com/en-us/data/dn456841.aspx there might be problem with ContextKey field because it is PK. But there is a workaround that allows to decrease size of the field. Don't know why this field need to be so long.

Do I need to fill tracker issues for described problems to track progress?
________________________________
From: Jiří Činčura<mailto:ji...@ci...>
Sent: ‎8/‎30/‎2015 9:08 AM
To: For users and developers of the Firebird .NET providers<mailto:fir...@li...>
Subject: Re: [Firebird-net-provider] Migrations
1. I think table names need to be preserved. Developer is reponsible for checking for that constraint. The name is defined. But I meant generated names, like PK name (if not specified). It's same for column names etc. It would be pretty confusing to later open that database and see just weird characters.
2. AFAIK you still need 16k database to be able to make PK (or maybe just 8k is enough, not 4k for sure).
4. OK, that might be bug.

--
Mgr. Jiří Činčura
Independent IT Specialist
------------------------------------------------------------------------------
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...<mailto:Fir...@li...>
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

Hi,

I just brought the branch up to date. Please hold your PRs for a week or
so. I'll clean it up and do some final changes. Then we'll finish it,
merge it and it'll go out (likely even before EF 6.2.0).

-- 
Mgr. Jiří Činčura
Independent IT Specialist

I see

2015-09-21 22:24 GMT+09:00 Jiří Činčura <ji...@ci...>:

> Hi,
>
> I just brought the branch up to date. Please hold your PRs for a week or
> so. I'll clean it up and do some final changes. Then we'll finish it,
> merge it and it'll go out (likely even before EF 6.2.0).
>
> --
> Mgr. Jiří Činčura
> Independent IT Specialist
>
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>

OK, so let's talk about Migrations. 

> Identity columns. 

Do you think the annotations will be better? How is the generator
creation (if it does not exists) going to be handled? Or are we going to
leave that to manual change of Up method in migration?

> 4. One more issue I remembered. Dbs created with Initial migration and via CreateDatabaseIfNotExists have different underlying scheme in part of names. I think this should be also fixed.

Do you have some more info for this.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

>Do you think the annotations will be better?
Better than what? Your concern was about different naming schemes, I
suggested how it can be resolved via column annotations and custom
name providers. I don't know other options for this.

>How is the generator creation (if it does not exists) going to be handled? Or are we going to leave that to manual change of Up method in migration?
IMO generator creation is an implementation detail that user shouldn't
bother about. Generator creation is a part of a table creation.

>Do you have some more info for this.
I'm sure that there were issues about identity columns. The current
implementation CreateDatabaseIfExists doesn't create generators in any
case, we patched sources to do that. Also, bool fields don't have
CHECK IN (0, 1) annotation and so on.
I need time to provide additional info for this.

On 30 September 2015 at 22:11, Jiří Činčura <ji...@ci...> wrote:
> OK, so let's talk about Migrations.
>
>> Identity columns.
>
> Do you think the annotations will be better? How is the generator
> creation (if it does not exists) going to be handled? Or are we going to
> leave that to manual change of Up method in migration?
>
>> 4. One more issue I remembered. Dbs created with Initial migration and via CreateDatabaseIfNotExists have different underlying scheme in part of names. I think this should be also fixed.
>
> Do you have some more info for this.
>
> --
> Mgr. Jiří Činčura
> Independent IT Specialist
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

On Thu, Oct 1, 2015, at 11:45, Геннадий Забула wrote:
> >Do you think the annotations will be better?
> Better than what? Your concern was about different naming schemes, I
> suggested how it can be resolved via column annotations and custom
> name providers. I don't know other options for this.

No. You talked about generator/identity columns. Possibly replacing the
interface.

> >How is the generator creation (if it does not exists) going to be handled? Or are we going to leave that to manual change of Up method in migration?
> IMO generator creation is an implementation detail that user shouldn't
> bother about. Generator creation is a part of a table creation.

That's definitely not. You can have one generator for all tables (and
it's even better).

> >Do you have some more info for this.
> I'm sure that there were issues about identity columns. The current
> implementation CreateDatabaseIfExists doesn't create generators in any
> case, we patched sources to do that. Also, bool fields don't have
> CHECK IN (0, 1) annotation and so on.
> I need time to provide additional info for this.

Well, the default initializers are using different path. These existed
before migrations. The code is different (and also the feature set). It
also works with EDMX and CF, while migrations are (currently) CF only.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

> No. You talked about generator/identity columns. Possibly replacing the
interface.
I think I've lose the point in this.

> That's definitely not. You can have one generator for all tables (and
it's even better).
For "Even better" I have doubts. Especially for multithreaded inserts
to different tables case.

> That's definitely not.
I'm talking about "generator creation", it should be hidden in the
provider. Though names yes, user should be allowed to pick a naming
scheme he wants.
In case "one generator for all tables" user specifies one generator
name for all tables and provider should check if it exists - nothing
to do, if not it creates new. After that it binds the generator name
to before insert trigger.

> Well, the default initializers are using different path.
Yep, I'm aware about this. First I've patched one code path. After
merge the migration feature I needed to patch another code path, and
resulting database had differences.

On 1 October 2015 at 14:13, Jiří Činčura <ji...@ci...> wrote:
> On Thu, Oct 1, 2015, at 11:45, Геннадий Забула wrote:
>> >Do you think the annotations will be better?
>> Better than what? Your concern was about different naming schemes, I
>> suggested how it can be resolved via column annotations and custom
>> name providers. I don't know other options for this.
>
> No. You talked about generator/identity columns. Possibly replacing the
> interface.
>
>> >How is the generator creation (if it does not exists) going to be handled? Or are we going to leave that to manual change of Up method in migration?
>> IMO generator creation is an implementation detail that user shouldn't
>> bother about. Generator creation is a part of a table creation.
>
> That's definitely not. You can have one generator for all tables (and
> it's even better).
>
>> >Do you have some more info for this.
>> I'm sure that there were issues about identity columns. The current
>> implementation CreateDatabaseIfExists doesn't create generators in any
>> case, we patched sources to do that. Also, bool fields don't have
>> CHECK IN (0, 1) annotation and so on.
>> I need time to provide additional info for this.
>
> Well, the default initializers are using different path. These existed
> before migrations. The code is different (and also the feature set). It
> also works with EDMX and CF, while migrations are (currently) CF only.
>
> --
> Mgr. Jiří Činčura
> Independent IT Specialist
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

On Thu, Oct 1, 2015, at 13:38, Геннадий Забула wrote:
> I'm talking about "generator creation", it should be hidden in the
> provider. Though names yes, user should be allowed to pick a naming
> scheme he wants.
> In case "one generator for all tables" user specifies one generator
> name for all tables and provider should check if it exists - nothing
> to do, if not it creates new. After that it binds the generator name
> to before insert trigger.

Well that's just part of story. You also need to name the trigger. You
need to have the body. Some people use like `Id is null` clauses. Some
also include 0 or -1 (for integers) etc. Not sure how to code this using
annotations (without implementing all possible combinations). That's why
the interface with some default implementation is so convenient.

> > Well, the default initializers are using different path.
> Yep, I'm aware about this. First I've patched one code path. After
> merge the migration feature I needed to patch another code path, and
> resulting database had differences.

I'd like to eventually re-use parts of migrations code to have (almost)
same result.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

> That's why
the interface with some default implementation is so convenient.
In ModelBuilder you can specify any object as data annotation. In this
case user can provide implementation of the interface.

On 1 October 2015 at 19:01, Jiří Činčura <ji...@ci...> wrote:
> On Thu, Oct 1, 2015, at 13:38, Геннадий Забула wrote:
>> I'm talking about "generator creation", it should be hidden in the
>> provider. Though names yes, user should be allowed to pick a naming
>> scheme he wants.
>> In case "one generator for all tables" user specifies one generator
>> name for all tables and provider should check if it exists - nothing
>> to do, if not it creates new. After that it binds the generator name
>> to before insert trigger.
>
> Well that's just part of story. You also need to name the trigger. You
> need to have the body. Some people use like `Id is null` clauses. Some
> also include 0 or -1 (for integers) etc. Not sure how to code this using
> annotations (without implementing all possible combinations). That's why
> the interface with some default implementation is so convenient.
>
>> > Well, the default initializers are using different path.
>> Yep, I'm aware about this. First I've patched one code path. After
>> merge the migration feature I needed to patch another code path, and
>> resulting database had differences.
>
> I'd like to eventually re-use parts of migrations code to have (almost)
> same result.
>
> --
> Mgr. Jiří Činčura
> Independent IT Specialist
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

On Thu, Oct 1, 2015, at 19:00, Геннадий Забула wrote:
> In ModelBuilder you can specify any object as data annotation. In this
> case user can provide implementation of the interface.

But then we're back where we started. Question is whether the global
definition for whole migration generator or per column is more
understandable and discoverable for developers.
-- 
Mgr. Jiří Činčura
Independent IT Specialist

On Thu, Oct 1, 2015, at 19:21, Jiří Činčura wrote:
> But then we're back where we started. Question is whether the global
> definition for whole migration generator or per column is more
> understandable and discoverable for developers.

And I'm not sure we two can find the answer. Wish others jumped in.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

Hi  all
Jiri asked me to kick in because we have two products.  One with one generator per column, one with one generator per database.
Pro's of generator per table are
You have the feeling of a sequence. There shouldn't be a gap, you can do "math" on it.This is how other db's have it since DBase.
Pro's of one generator per database...  
I have an orm that more or less only works fine if you assign the PK on creation of the object. Per instance I can now do a single trip to the db and do gen_id(pk, 1000) and create myself a nice pool of unique ids that I can design without additional roundtrips to the db.
It really removes all sense from PK's and that is how I think it should be.  There are huge gaps and that is perfectly explainable.  
All records inside the db have a unique key. 
If you ever need to synchronize two db's... you can start the one on 1.000.000.000, second on 2.000.000.000, ...  whatever
You don't need to manage 5 billion generators.  
Imho opinion I would never go back to generator per table.  I discover every month new goodies that make my choice for 1 generator awesome.  But I do think... for the common sense and feeling...  one table, one generator makes most sense...  even when it sucks.
Hopes this solves your debate.
Alexander

> From: ji...@ci...
> To: fir...@li...
> Date: Thu, 1 Oct 2015 19:24:00 +0200
> Subject: Re: [Firebird-net-provider] Migrations
> 
> On Thu, Oct 1, 2015, at 19:21, Jiří Činčura wrote:
> > But then we're back where we started. Question is whether the global
> > definition for whole migration generator or per column is more
> > understandable and discoverable for developers.
> 
> And I'm not sure we two can find the answer. Wish others jumped in.
> 
> -- 
> Mgr. Jiří Činčura
> Independent IT Specialist
> 
> ------------------------------------------------------------------------------
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

Thread: [Firebird-net-provider] Migrations

A powerful, cross platform, SQL database system

firebird-net-provider