FCO
Default values
It would be nice to have default values for columns - which means, defined using the DEFAULT clause in SQL.
That would be good to use the default value of the model, but also have a way to say to not using that as default.
model Bla {
has Int $.a is column = 42;
has Int $.b is column{ :ignore-default } = 13; # or any other name
}
create table bla(
a integer not null default 42,
b integer not null
)
model Bla {
has Int $.a is column = 42;
has Int $.b is column{ :ignore-default } = 13; # or any other name
has Int $.c is-column{ :default(3) };
}
create table bla(
a integer not null default 42,
b integer not null,
c integer not null default 3
)
Also it would be useful to be able to supply literal SQL as the default.
create table bla(
foo uuid primary key default uuid_generate_v4()
);
In say Postgres.
I'm not quite sure how one might express that in the model declaration though.
Except you'd need to distinguish between a string default and verbatim SQL.
I've been wondering... If I do this:
model Bla { has $.ble is column{ :default-sql<NOW()> } }
Bla.new
should the new run a SELECT NOW() on database to populate the object?
I mean:
model Bla {
has Instante $.bla is column{ :default-sql<NOW()> };
has Int $.ble is column;
has UUID $.bli is column .= new;
has UUID $.blo is column{ :default-sql<uuid_henerate_v4()> }
}
Bla.new
would
SELECT NOW() as bla, uuid_henerate_v4() as blo
I could be wrong, but I think you won't be able to handle cases where a default value references another column - or at least, not in all databases. Example, this may not work on some DBMSs:
SELECT 2000 AS price, price + (price / 100) * 20 AS taxed_price
Maybe something like this:
model Bla {
has Instante $.bla is column{ :default{ .now } };
has Int $.ble is column;
has UUID $.bli is column .= new;
has UUID $.blo is column{ :default{ .new-v4 } }
has Int $.blu is column{ :42default };
}
I'd like to be able to describe a table like:
CREATE TABLE bla (
uuid uuid DEFAULT public.uuid_generate_v4() PRIMARY KEY,
created_at timestamp with time zone DEFAULT clock_timestamp() NOT NULL
);
So the default-sql should become part of the DDL for the table. The advantage of being able to do it like this is so that non-Red applications will get the same behaviour.
Yes, in my suggestion, to create that table, you could use something like:
model Bla {
has UUID $.uuid is id{ :default{ .generate-v4() } };
has Instant $.created-at is column{ :default{ .now() } }
}
and a column of type UUID would have the generate-v4 method that returns a AST that creates the public.uuid_generate_v4() call, and the same for created-at...
what do you think?
if we do like :default-sql<public.uuid_generate_v4()> it will only work on databases that has that function with that name...
Got ya! Yep I like that. And potentially it allows for people to make extensions to support new things like that.
@jonathanstowe just to be sure, you mean this one, right? https://github.com/FCO/Red/issues/109#issuecomment-472923770
Yep. And just so we're clear we mean the same thing, I understand that the driver would proved a translate method to create the SQL to define a default of that type.
