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
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();
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
On 22/04/2020 18:08, Martin van Dinther wrote: 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