Detecting script vs execution in MigrationsSqlGenerator

[cincuranet]

Is there an easy way to detect whether the XxxMigrationsSqlGenerator is generating statements for execution or for script to be created? Because for script I have to generate slightly different output for user to be able to execute it without minor tweaks (basically SET TERM).

If not, it's fine. In EF6 this wasn't done either and nobody complained. As one would expect people run the migrations directly.

[ajcvickers]

@cincuranet We recommend not creating different output when generating a script as opposed to applying the migrations directly. Can you give some more details on why this is needed?

[cincuranet]

Yes I can. When the migrations are generated and directly executed, it is known what's the command. But when I generate the script, then the semicolons are, by default, delimiting commands. But semicolons inside i.e. stored procedure will break the parsing and the script is not going to be valid (and Firebird, at the moment, does not support sending multiple commands inside one "packet"). That's why Firebird has SET TERM "statement" (it's really not a statement, because server does not handle it, it's purely for client side tools to handle parsing.

That why the stored procedure in script would look like this for Firebird.

-- changing the separator to ^
set term ^;

-- the end of the procedure is signaled by ^ so the ; can be used inside
create procedure ...
begin
  foo;
  bar;
  baz;
end^

-- changing the separator back to ;
set term ;^

Yes, I know there are ways to handle it without explicit separators (like counting "begin-end" etc. levels), but none of this is supported by any - default or commercial - tool known to me supporting Firebird.

If this would be easy to do, I would like to do it. Else, meh, let's see whether somebody asks for it...

[ajcvickers]

@bricelam to follow up.

[bricelam]

SQL Server has the GO utility statements for tools to mark the end of a batch/DbCommand. Does Firebird have a similar concept for tools? This is enabled by the ISqlGenerationHelper.BatchTerminator property.

If not, we can look into adding a flag to indicate that Migrator.GenerateScript was called.

[cincuranet]

Yeah, the GO is similar concept. But for Firebird it's not hardcoded. At the beginning the `SET TERM xxx` has to be called. And only for scripts and procedural blocks. So I fail to see how the `BatchTerminator` could be used, but I might be missing something.

[bricelam]

I agree, it wouldn't work for SET TERM.

[cincuranet]

So the state is that ATM I can't detect that the script is being generated, right?

I'll leave it as it's now and once the flag is present - I suppose somewhere in 3.x timeframe - I'll update the code.

[bricelam]

Correct; there's currently no way to detect it.

[roji]

Note: this has become more critical for Npgsql - am going to need this for automatic sequence reset after seeding to avoid collision (npgsql/efcore.pg#367).

[bricelam]

@roji I can't figure out what Npgsql actually needs to do differently in the script vs at runtime. Can you elaborate?

[bricelam]

Oh I see--npgsql/efcore.pg#1157. So yours is only for idempotent too. (like SQL Server's #12911)

[bricelam]

@cincuranet You need to do it for all scripts, right? (not just idempotent)

[cincuranet]

Correct.