conditional DDL statements
Hi,
I am afraid I know the answer to this already ("not supported"), but I wanted to check with you guys. Perhaps someone has smart workaround?
Essentially what I would like to do is something like:
IF condition THEN CREATE FUNCTION ....;
As far as I know, flow control statements are only allowed inside functions/procedures. However, CREATE statements are not allowed inside functions/procedure. So I don't see any way to do what I wrote above.
Notice, I am aware of CREATE OR REPLACE. What I'm looking for is an arbitrary condition test.
Any trick? And/or, any plan to implement something in this direction?
Roberto
It is not possible in MonetDB SQL. Also not in the SQL standard. You will need to do such conditional DDL statements from an external program/script.
Normally you would create all DB objects (schemas, tables, views, functions, procedures, etc) once (as monetdb admin) and next use/call them from your application. Is it not possible in your case to create all the needed functions once upfront? Why do you need to create them conditionally?
Martin vD
On 22-04-2020 15:58, Roberto Cornacchia wrote:
Hi,
I am afraid I know the answer to this already ("not supported"), but I wanted to check with you guys. Perhaps someone has smart workaround?
Essentially what I would like to do is something like:
IF condition THEN CREATE FUNCTION ....;
As far as I know, flow control statements are only allowed inside functions/procedures. However, CREATE statements are not allowed inside functions/procedure. So I don't see any way to do what I wrote above.
Notice, I am aware of CREATE OR REPLACE. What I'm looking for is an arbitrary condition test.
Any trick? And/or, any plan to implement something in this direction?
Roberto
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Martin,
I create custom function signatures with a createdb/functions.sql script.
Now say that 20 projects are running Spinque release 1.2.3 (several Spinque releases can be based on the same official MonetDB release). I make a new release 1.2.4, where my UDF signatures change.
By simply upgrading the existing MonetDB deployments, the script won't be loaded, so I will have old signatures for new implementations, possibly breaking everything.
I don't think it's fair to say that this should be handled by an application. It has to do with internal consistency. If I change an implementation, I should be able to change the signature as part of a MonetDB upgrade, it has nothing to do with applications. Just as MonetDB allows to upgrade from a previous release with incompatible catalog. In other words, now MonetDB is allowing me to break it. I know it's not SQL standard. Still, many DB vendors have this, because it's useful.
What I did so far is to manually run that script on all 20 projects right after they are upgraded.
In a previous conversation in this channel, Sjoerd has made clear that there currently is no mechanism to run a SQL script at every MonetDB restart.
What I did recently is to tap in SQLinit() and add that possibility. For a quick PoC I did it for just the one script I need and hard-coded its name.
That works, because my script has lots of CREATE OR REPLACE FUNCTION. So every time they get recreated. I was hoping to avoid that recreation though, and that's why I was looking for conditional DDL. Then I can check the internal (Spinque) release and run recreate those functions only upon a release change.
Roberto
On Wed, 22 Apr 2020 at 18:01, Martin van Dinther < dinther@monetdbsolutions.com> wrote:
It is not possible in MonetDB SQL. Also not in the SQL standard. You will need to do such conditional DDL statements from an external program/script.
Normally you would create all DB objects (schemas, tables, views, functions, procedures, etc) once (as monetdb admin) and next use/call them from your application. Is it not possible in your case to create all the needed functions once upfront? Why do you need to create them conditionally?
Martin vD
On 22-04-2020 15:58, Roberto Cornacchia wrote:
Hi,
I am afraid I know the answer to this already ("not supported"), but I wanted to check with you guys. Perhaps someone has smart workaround?
Essentially what I would like to do is something like:
IF condition THEN CREATE FUNCTION ....;
As far as I know, flow control statements are only allowed inside functions/procedures. However, CREATE statements are not allowed inside functions/procedure. So I don't see any way to do what I wrote above.
Notice, I am aware of CREATE OR REPLACE. What I'm looking for is an arbitrary condition test.
Any trick? And/or, any plan to implement something in this direction?
Roberto
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi
You describe a very common pattern in database applcation environments, which are covered by tool specific migration scripts. (e.g. see Ruby-on-Rails which takes it to the extreme, SQLalchemy, and most web-based application frameworks)
In general, running a start-up script automatically adds limited extra functionality to the server and would severely complicate the code base to accommodate this for general use, e.g. where are the scripts located in the environment (Linux,Mac,Win), what OS access credentials are available, does it depend on OS libraries, is there proprietary code being called,.... Other systems expect full control over the environment and also rely on running scripts as part of their own code to initialize a system. This is how we use it by manipulating the .../sql/scripts directory
A migration script is typically called from a shell script as an ordinary user program. In general, such scripts could have much more preparatory work, e.g. taking backups first.
In one case I made the following simple migration step function and ran it over a directory of migration files ordered by date.
my penny
regards, Martin (the other one)
-- The template for SQALPEL migration steps
-- Migration steps are taken in the timestamp order. -- The identifiers are prefixed with a date and align to the file names.
-- Beware, each migration script is ran in isolation without explicit knowledge -- about the success of failure of previous steps.
-- call: mclient -d sqalpel -a <migrationfile>
create function migratesqalpel() returns string begin declare mid string; set mid = '20190000'; -- the migration identifer declare doit boolean; set doit = ((select count(*) from sqalpel.migrations where id =mid) = 0);
if not doit then return 'Migration 20190000 skipped'; end if; -- here comes the list of SQL actions to implement the migration
-- Finalize with recording of the last step insert into sqalpel.migrations values(mid, now()); return 'Migration 20190000 completed';
end;
select migratesqalpel();
drop function migratesqalpel();
On 22/04/2020 18:08, Martin van Dinther wrote:
It is not possible in MonetDB SQL. Also not in the SQL standard. You will need to do such conditional DDL statements from an external program/script.
Normally you would create all DB objects (schemas, tables, views, functions, procedures, etc) once (as monetdb admin) and next use/call them from your application. Is it not possible in your case to create all the needed functions once upfront? Why do you need to create them conditionally?
Martin vD
On 22-04-2020 15:58, Roberto Cornacchia wrote:
Hi,
I am afraid I know the answer to this already ("not supported"), but I wanted to check with you guys. Perhaps someone has smart workaround?
Essentially what I would like to do is something like:
IF condition THEN CREATE FUNCTION ....;
As far as I know, flow control statements are only allowed inside functions/procedures. However, CREATE statements are not allowed inside functions/procedure. So I don't see any way to do what I wrote above.
Notice, I am aware of CREATE OR REPLACE. What I'm looking for is an arbitrary condition test.
Any trick? And/or, any plan to implement something in this direction?
Roberto
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi (the other) Martin,
Thanks for that input.
If I understand well though, I think we are not talking about the same type of upgrade.
I'm not upgrading data/schemas of an application. With a new release I'm possibly upgrading internal C and MAL code. Not application data. I seems only normal to me to be able to upgrade that code completely (i.e. including the correct SQL signatures), instead of just half-way and then having to patch it with some external scripts.
I've actually done that part for our own scripts, without any location issue, OS assumption or security risk, and reusing code already available.
I understand if this is not the solution that you'd prefer in general for MonetDB, but whatever the solution is I do still think that the issue is real and should not rely on some external scripting. When starting a database with a new release, regardless of any application, it should be consistent and correct. Now it isn't.
On Wed, 22 Apr 2020 at 19:18, Martin Kersten martin.kersten@cwi.nl wrote:
Hi
You describe a very common pattern in database applcation environments, which are covered by tool specific migration scripts. (e.g. see Ruby-on-Rails which takes it to the extreme, SQLalchemy, and most web-based application frameworks)
In general, running a start-up script automatically adds limited extra functionality to the server and would severely complicate the code base to accommodate this for general use, e.g. where are the scripts located in the environment (Linux,Mac,Win), what OS access credentials are available, does it depend on OS libraries, is there proprietary code being called,.... Other systems expect full control over the environment and also rely on running scripts as part of their own code to initialize a system. This is how we use it by manipulating the .../sql/scripts directory
A migration script is typically called from a shell script as an ordinary user program. In general, such scripts could have much more preparatory work, e.g. taking backups first.
In one case I made the following simple migration step function and ran it over a directory of migration files ordered by date.
my penny
regards, Martin (the other one)
-- The template for SQALPEL migration steps
-- Migration steps are taken in the timestamp order. -- The identifiers are prefixed with a date and align to the file names.
-- Beware, each migration script is ran in isolation without explicit knowledge -- about the success of failure of previous steps.
-- call: mclient -d sqalpel -a <migrationfile>
create function migratesqalpel() returns string begin declare mid string; set mid = '20190000'; -- the migration identifer declare doit boolean; set doit = ((select count(*) from sqalpel.migrations where id =mid) = 0);
if not doit then return 'Migration 20190000 skipped'; end if; -- here comes the list of SQL actions to implement the migration -- Finalize with recording of the last step insert into sqalpel.migrations values(mid, now()); return 'Migration 20190000 completed';end;
select migratesqalpel();
drop function migratesqalpel();
On 22/04/2020 18:08, Martin van Dinther wrote:
It is not possible in MonetDB SQL. Also not in the SQL standard. You will need to do such conditional DDL statements from an external
program/script.
Normally you would create all DB objects (schemas, tables, views,
functions, procedures, etc) once (as monetdb admin) and next use/call them from your application.
Is it not possible in your case to create all the needed functions once
upfront?
Why do you need to create them conditionally?
Martin vD
On 22-04-2020 15:58, Roberto Cornacchia wrote:
Hi,
I am afraid I know the answer to this already ("not supported"), but I
wanted to check with you guys. Perhaps someone has smart workaround?
Essentially what I would like to do is something like:
IF condition THEN CREATE FUNCTION ....;
As far as I know, flow control statements are only allowed inside
functions/procedures.
However, CREATE statements are not allowed inside functions/procedure. So I don't see any way to do what I wrote above.
Notice, I am aware of CREATE OR REPLACE. What I'm looking for is an
arbitrary condition test.
Any trick? And/or, any plan to implement something in this direction?
Roberto
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On 22/04/2020 19:52, Roberto Cornacchia wrote:
Hi (the other) Martin,
Thanks for that input.
If I understand well though, I think we are not talking about the same type of upgrade.
I'm not upgrading data/schemas of an application. With a new release I'm possibly upgrading internal C and MAL code.
Our experience in changing code on a daily basis at that level has told us that there is just no silver bullet. Manual intervention is often needed to resolve conflicts between branch merges when it concerns C, MAL or even SQL syntax changes. Let alone running extensive testing to check-out for collateral damage. The situation you describe is similar to taking our latest code branch and merging it with your enhanced older version. You have to bite the bullet yourself.
The same holds for upgrading the running instances on the old code base, but at least that can be orchestrated by temporarily stopping the server, possibly with a fail-over instance to step in temporarilly. This is typically an upgrade script to avoid manual interaction.
Sorry.
Not application data. I seems only normal to me to be able to upgrade that code completely (i.e. including the correct SQL signatures),
instead of just half-way and then having to patch it with some external scripts.
I've actually done that part for our own scripts, without any location issue, OS assumption or security risk, and reusing code already available.
I understand if this is not the solution that you'd prefer in general for MonetDB, but whatever the solution is I do still think that the issue is real and should not rely on some external scripting. When starting a database with a new release, regardless of any application, it should be consistent and correct. Now it isn't.
Martin,
I see your pointn and I agree that it's in general not simple.
Only, I don't quite agree that it is the same for internal development.
If the monetdb developers change one of the built-in functions, and this needs a new SQL signature, they will use the upgrade facility to patch the database at the next start, automatically. And that's crucial, because otherwise the functionalities of the server would be broken, regardless of what data and what application uses them.
For UDFs, there is nothing like that. Unless you patch a database manually, you are going to break the server functionalities. We did without this till now, we can live without for longer, no big deal. But it is a missing feature, that's all.
On Wed, 22 Apr 2020, 21:29 Martin Kersten, martin@monetdb.org wrote:
On 22/04/2020 19:52, Roberto Cornacchia wrote:
Hi (the other) Martin,
Thanks for that input.
If I understand well though, I think we are not talking about the same
type of upgrade.
I'm not upgrading data/schemas of an application. With a new release I'm
possibly upgrading internal C and MAL code.
Our experience in changing code on a daily basis at that level has told us that there is just no silver bullet. Manual intervention is often needed to resolve conflicts between branch merges when it concerns C, MAL or even SQL syntax changes. Let alone running extensive testing to check-out for collateral damage. The situation you describe is similar to taking our latest code branch and merging it with your enhanced older version. You have to bite the bullet yourself.
The same holds for upgrading the running instances on the old code base, but at least that can be orchestrated by temporarily stopping the server, possibly with a fail-over instance to step in temporarilly. This is typically an upgrade script to avoid manual interaction.
Sorry.
Not application data. I seems only normal to me to be able to upgrade that code completely (i.e. including the correct SQL signatures),
instead of just half-way and then having to patch it with some external
scripts.
I've actually done that part for our own scripts, without any location
issue, OS assumption or security risk, and reusing code already available.
I understand if this is not the solution that you'd prefer in general
for MonetDB, but whatever the solution is I do still think that the issue is real and should not rely on some external scripting. When starting a database with a new release, regardless
of any application, it should be consistent and correct. Now it isn't.
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
Martin Kersten -
Martin Kersten -
Martin van Dinther -
Roberto Cornacchia